User Tools

Site Tools


mysql

MYSQL cheat sheet

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

Enabling slow queries in directadmin panel

  • 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';"

my.cnf start point

Links:

[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

Mysqldump all databases

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

MySQL recreate clean db

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

MySQL create database and user

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;

CentOS7 - mysql datadir linked to home

# 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

Links

mysql.txt · Last modified: 2019/04/26 01:47 by dreiggy