audit for incorrect engine

If you use innodb engine exclusively, and want to audit your databases for any tables that might have an incorrect engine, use the command below -

mysql -u root -p  information_schema -e \
"select TABLE_SCHEMA,TABLE_NAME, ENGINE from TABLES where\
 ENGINE != 'Innodb' \
 and TABLE_SCHEMA not in ('mysql','information_schema');"

My tshirt for today

This tshirt is from a city called Weed, in the state of (where else) California. The town of Weed gets its name from the founder of the local lumber mill, Abner Weed. No sublimal messages have been hidden in the tshirt.

Cheers

a bag of tips

When you require a fake US address, use the one below. It should clear validity filters
123 Fake Street
Compton, CA - 90222

In linux, to find files modified today, use find . -mtime -1 \! -type d -exec ls -l {} \;

mysql startup debugging tip

If you are not able to start the mysql daemon repeatedly using your linux distribution init scripts and you are ready to pull out your hair in frustration, here is a tip that might help you in finding the problem.

Try running the mysqld_safe from the command line (without using the init scripts). Try running /usr/bin/mysqld_safe -v, which should spit out some debugging information.

If that fails, try calling the mysqld daemon directly from the command line, with the "-v" option . mysqld is usually present under /usr/sbin/mysqld. mysqld can be called with --print-defaults to get the command line options it would be run with.

/usr/sbin/mysqld would have been started with the following arguments: --user=mysql --pid-file=/var/run/mysqld/mysqld.pid ..... --max_binlog_size=100M

Try adding the -v option to these options to get more verbose details. When run from command line, mysqld will not detach from console and will print debugging info that might be useful in finding the cause of the error.

I suggest using 3 terminals to figure out what is going on

• one terminal with tail -f /var/log/mysql/mysqld.err
• one terminal with tail -f /var/log/messages
• one terminal where I ran the mysqld_safe / mysqld command

The init scripts are usually good for day to day work. However, sometimes the init scripts can impede a innodb crash recovery process on a large database. Some init scripts have timeout built into them and they can kill mysql while the innodb is still trying to recover its tables.