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