Max open files:

su - mysql -c 'ulimit -aHS' -s '/bin/bash'

Mysql debug starting:

su - mysql -c 'mysqld' -s '/bin/bash'
[OR]
strace -s1000 service mysqld start

Show process list sorted by time:

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'

Log to file:

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

DirectAdmin show processlist:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e 'show processlist;'

Kill one query:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "kill QUERY_ID;"

Kill all queries by pattern:

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";\""}'

Repairing MYISAM tables:

myisamchk -r *.MYI

MySQL check

mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --all-databases

MySQL repair

mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -r --all-databases

MySQL optimize all databases

mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -o --all-databases

• ENABLE SLOW QUERY LOG:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL slow_query_log = 'ON';"

• SLOW QUERY LOG TIME:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL long_query_time = 5;"

• DISABLE SLOW QUERY LOG:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL slow_query_log = 'OFF';"

• SLOW QUERY LOG FILE:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';"

• LOG UNINDEXED QUERY

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "SET GLOBAL log_queries_not_using_indexes = 'ON';"

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/

[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

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

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

Create database:

CREATE DATABASE new_db;

Create user:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant privilegies:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Make them work:

FLUSH PRIVILEGES;

Change password:

SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('NewPASSWORD');
FLUSH PRIVILEGES;

# cat /usr/lib/systemd/system/mariadb.service | grep -i home
# Prevent accessing /home, /root and /run/user
ProtectHome=true

try changing that to false systemctl daemon-reload and start

• 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