====== MYSQL cheat sheet ======

Max open files:
<code bash>
su - mysql -c 'ulimit -aHS' -s '/bin/bash'
</code>

Mysql debug starting:
<code bash>
su - mysql -c 'mysqld' -s '/bin/bash'
[OR]
strace -s1000 service mysqld start
</code>

Show process list sorted by time:
<code bash>
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e 'SELECT user, time, state, info FROM information_schema.processlist ORDER BY time, id \G'
</code>

Log to file:
<code bash>
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e 'SELECT user, time, state, info FROM information_schema.processlist ORDER BY time, id \G' > /root/mysql-processlist-$(date +%s).txt
</code>

DirectAdmin show processlist:
<code bash>
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e 'show processlist;'
</code>

Kill one query:
<code bash>
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "kill QUERY_ID;"
</code>

Kill all queries by pattern:
<code bash>
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -se 'show processlist' | grep -i MYSQL_USER | grep SELECT | awk '{print "mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e \"kill "$1";\""}'
</code>

Repairing MYISAM tables:
<code bash>
myisamchk -r *.MYI
</code>

MySQL check
<code bash>
mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --all-databases
</code>

MySQL repair
<code bash>
mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -r --all-databases
</code>

MySQL optimize all databases
<code bash>
mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -o --all-databases
</code>
====== Enabling slow queries in directadmin panel ======

  * ENABLE SLOW QUERY LOG:
<code bash>mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL slow_query_log = 'ON';"</code>
  * SLOW QUERY LOG TIME:
<code bash>mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL long_query_time = 5;"</code>
  * DISABLE SLOW QUERY LOG:
<code bash>mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL slow_query_log = 'OFF';"</code>
  * SLOW QUERY LOG FILE:
<code bash>mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';"</code>
  * LOG UNINDEXED QUERY
<code bash>mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL log_queries_not_using_indexes = 'ON';"</code>


====== my.cnf start point ======

Links:
  * https://linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/
  * http://techinfobest.com/optimize-mysql-table_open_cache/
  * https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_user_connections/

<code bash>
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
# mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "show global variables like 'local_infile';"
local-infile = 0
## Skip reverse DNS lookup. Be aware!
#skip-name-resolve
## compatability
#sql_mode =

## Log errors
# log_error = /var/lib/mysql/hostname.err
# slow_query_log = 1
# slow_query_log_file = /var/lib/mysql/hostname-slow-queries.log

##TMPFS
#tmpdir = /dev/shm

## Restrictions
max_connections = 70
max_user_connections = 30
wait_timeout = 10
interactive_timeout = 50
long_query_time = 5

##http://techinfobest.com/optimize-mysql-table_open_cache/
table_open_cache = 32768
##table_open_cache / 2 + 400
table_definition_cache = 16784
##2-3x table_open_cache
open_files_limit = 65536

max_allowed_packet = 128M
max_heap_table_size = 256M
tmp_table_size = 256M
thread_concurrency = 4 ## Number of CPUs x 2

##buffers
key_buffer_size = 256M ## 128MB for every 1GB of RAM
join_buffer_size = 8M
sort_buffer_size = 2M ## 1MB for every 1GB of RAM
read_buffer_size = 2M ## 1MB for every 1GB of RAM
read_rnd_buffer_size = 2M ## 1MB for every 1GB of RAM

##cache
query_cache_type = 0 #recomended disable /1
query_cache_size = 0 #recomended disable /32MB for every 1GB of RAM
query_cache_limit = 1M
thread_cache_size = 4

##innodb
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_buffer_pool_instances=2
</code>
====== Mysqldump all databases ======

<code bash>
mkdir -p /root/db_backup
for i in `mysql -e "show databases" -sss`; do { mysqldump ${i} --routines > /root/db_backup/${i}.sql; }; done
</code>

====== MySQL recreate clean db ======
<code bash>
service mysqld stop
mv /var/lib/mysql /var/lib/mysql_backup
mysql_install_db
chown -R mysql. /var/lib/mysql
chmod 755 /var/lib/mysql
service mysqld start
</code>

====== MySQL create database and user ======

Create database:
<code bash>
CREATE DATABASE new_db;
</code>

Create user:
<code bash>
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
</code>

Grant privilegies:
<code>
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
</code>

Make them work:
<code bash>
FLUSH PRIVILEGES;
</code>

Change password:
<code bash>
SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('NewPASSWORD');
FLUSH PRIVILEGES;
</code>

====== CentOS7 - mysql datadir linked to home ======
<code>
# cat /usr/lib/systemd/system/mariadb.service | grep -i home
# Prevent accessing /home, /root and /run/user
ProtectHome=true
</code>
try changing that to false
systemctl daemon-reload
and start

====== Links ======
  * Monitoring tools: https://www.tecmint.com/mysql-performance-monitoring/
  * Recover corrupted db: https://help.directadmin.com/item.php?id=559
  * List MyISAM & Convert: https://dba.stackexchange.com/a/50030
  * https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
  * http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/
  * https://www.percona.com/blog/2018/07/03/linux-os-tuning-for-mysql-database-performance/
  * Removing bin logs and retention: https://dba.stackexchange.com/questions/41050/is-it-safe-to-delete-mysql-bin-files
  * https://www.osradar.com/optimize-mysql-mariadb-on-linux