MySQL Troubleshooting

MySQL Troubleshooting

InnoDB Disabled

Run this command in SSH as root to show the current engines:

 mysql -e "show engines;"

If you run a mysqcheck and get something like the following on a number of tables:

Error    : Incorrect information in file: './halp_blah/example_innodb.frm'
error    : Corrupt

The tables are not actually corrupt, they are just InnoDB tables and InnoDB is disabled.

Locked Tables

http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

If you see MANY locks on a table (or tables, specifically MyISAM not InnoDB), the option low_priority_updates may help. If it is enabled, selects will get higher priority than updates and waiting updates will not stall all new selects to the table until they are executed.

low_priority_updates = 1 

Post-Upgrade - MySQL does not start

If you get the following error message why trying to start MySQL 5.x after an upgrade:

Starting MySQL
Couldn't find MySQL manager or server [FAILED]

Then edit /etc/my.cnf and comment out the following line:

basedir=/var/lib

Then restart MySQL.

MySQL Top Resources Script

 mysqladmin processlist | cut -d"|" -f3 | sort | uniq -c | sort -n | tail -n 8

MySQL Datadir Migration

 mkdir -p /home/mysql
 chown mysql:mysql /home/mysql
 rsync -avHl /var/lib/mysql/ /home/mysql/
 /etc/init.d/{crond,mysql} stop
 rsync -avHl /var/lib/mysql/ /home/mysql/
 mv /var/lib/mysql /var/lib/mysql.bak
 ln -sf /home/mysql /var/lib/mysql

If /var is full

 rsync -avHlT /home/rtmp$$ /var/lib/mysql/ /home/mysql/

Post-migration

 [mysqld]
 datadir=/home/mysql
 #basedir=/var/lib/mysql

It should then be safe to restart MySQL:

 /etc/init.d/{crond,mysql} start
 rm -rf /var/lib/mysql.bak
 rm -f /home/mysql/*.pid
 touch /home/mysql/$(hostname).pid

Database Repair

Run this first to determine which tables are corrupted:

 mysqlcheck -Asc

Repair Corrupted Database (REPAIR)

 mysqlcheck -r <database name>

Repair Corrupted Database (With FRM)

mysqlcheck -r <database name> --use-frm

Repair ALL Databases and Reindex Tables

/etc/init.d/{crond,mysql} stop cd /var/lib/mysql/ find . -type f -name '*.MYI' -exec myisamchk -fUp '{}' \; /etc/init.d/{crond,mysql} start

InnoDB Thread Issues

This is most likely a ulimit issue in /etc/init.d/mysql. You can usually find a 'ulimit -s' somewhere in there, and replace the number after it with a number that works for you. If not, add it near the top.

 ulimit -s 8192

Slow Query Log

touch /var/lib/mysql/slow.log
chown mysql. /var/lib/mysql/slow.log

In the my.cnf file under the mysqld section add this:

log-slow-queries=/var/lib/mysql/slow.log

Then restart mysql and you have a slow query log.

If you want to specify the number of seconds that indicates a long or slow query, use this line in /etc/my.cnf :

long_query_time = 5

Optimization Scripts

MySQL Tuning Primer

 wget -O /root/tuning-primer.sh http://day32.com/MySQL/tuning-primer.sh
 chmod +x /root/tuning-primer.sh
 /root/tuning-primer.sh 

MySQL Tuner

 wget -O /root/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl
 chmod +x /root/mysqltuner.pl
 /root/mysqltuner.pl

Downgrading

Downgrading MySQL

• Grab a list of the currently installed mysql packages on your server

 rpm -qa | grep -i mysql-

• Make Backups

 mkdir /home/sqlbackup/
 for i in $(mysql -e "show databases;" | sed 's/Database//') ; do mysqldump $i > /home/sqlbackup/$i.sql ; done

cPanel users

If you are running cPanel on your server, Edit /var/cpanel/cpanel.config/, and set mysql-version to your desired MAJ.MIN (eg. 5.0) and

 rm -f /root/.cpanel/datastore/*_mysql*
 

You can run /scripts/mysqlup --force on cPanel servers to upgrade MySQL automatically, but you'll still need to run EasyApache to use the new mysql shared libraries, since this script wipes the old ones out, breaking php-mysql.

• Check to see that the new version is installed

 rpm -qa | grep -i mysql-

• Stop MySQL and CRON

killall -9 crond
# on cPanel servers, killall -9 tailwatchd
/etc/init.d/mysql stop

• Move MySQL install to timestamped directory

 mv /var/lib/mysql /home/mysql.$(date +%s)

• Reinstall

 mkdir /var/lib/mysql
 mysql_install_db
 mv /root/.my.cnf /root/.my.cnf.old
 service mysql restart

• Import everything

 cd /home/temp/sqldump
 for i in *.sql ; do mysqladmin create ${i%.*} ; done
 for i in *.sql ; do mysql ${i%.*} < $i ; done ; done
 mv /root/.my.cnf.old /root/.my.cnf
 service mysql restart

cPanel users will want to run EasyApache in SSH via /scripts/easyapache (recommended in screen)

Upgrading MySQL

 mysqlcheck -r --all-databases
 cd /backup
 mkdir mysqldumps
 cd /backup/mysqldumps
 for i in $(mysql -e "show databases;" | cut -d ' ' -f2 | grep -v Database); do \
   mysqldump $i > $i.sql; \
 done

Notice: If you are upgrading from 4.0->5.[01] or from 4.1->5.1, incrementally upgrade from 4.x->5.0, then run a mysqlcheck -r --all-databases during the upgrade to the next version, otherwise it might cause incompatibility issues

Configuration File Formulas

Calculating Minimum Memory Needed

 min_memory_needed = global_buffers + (thread_buffers * max_connections)

Configuration Variables

 max_connections         = 150
 max_user_connections    = (150 / #users)
 key_buffer_size         = (RAM_MB / 3) # 64MB for every 1GB of RAM
 table_cache             = opened_tables + (opened_tables/sec increment)
 sort_buffer_size	  = 1MB for every 1GB of RAM
 read_rnd_buffer_size    = 1MB for every 1GB of RAM
 read_buffer_size        = 1MB for every 1GB of RAM
 thread_cache            = increase until Threads_created stops increasing
 tmp_table_size          = if Created_tmp_disk_tables = 0, you're good, otherwise double along with max_heap_table_size
 query_cache_size        = recommend query_cache_type=1, 32M per 1G RAM, VPSes max 8MB per 512M RAM
 max_seeks_for_key       = 100

Premade Configs

VPS

<syntaxhighlight lang="ini"> [mysqld] max_connections = 50 safe-show-database skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 128 thread_cache = 2 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M query_cache_size=8M max_heap_table_size = 32M wait_timeout = 30 interactive_timeout = 150

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 16M sort_buffer_size = 10M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 16M sort_buffer_size = 10M read_buffer = 2M write_buffer = 2M </syntaxhighlight>

Server with 1G-2G RAM

<syntaxhighlight lang="ini"> [mysqld] max_connections = 50 safe-show-database skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M query_cache_size= 32M max_heap_table_size = 48M tmp_table_size = 48M thread_cache_size = 8 wait_timeout = 30 interactive_timeout = 300

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 96M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 96M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M </syntaxhighlight>

Server with 3G-8G RAM

<syntaxhighlight lang="ini"> [mysqld] max_connections = 64 safe-show-database skip-locking key_buffer = 192M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M query_cache_size= 96M max_heap_table_size = 64M tmp_table_size = 64M thread_cache_size = 16 wait_timeout = 45 interactive_timeout = 600

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 128M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 128M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M </syntaxhighlight>

Server with 8G+ RAM

<syntaxhighlight lang="ini"> [mysqld] max_connections = 128 safe-show-database skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 1024 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M query_cache_size = 128M max_heap_table_size = 64M tmp_table_size = 64M thread_cache_size = 32 wait_timeout = 120 connect_timeout = 10 interactive_timeout = 1200

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 256M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 256M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

[mysqlhotcopy] interactive-timeout </syntaxhighlight>

MySQL Troubleshooting is part of a series on administration.