Here are some quick and helpful MySQL commands that can be run to optimize, repair, or analyze a database from the command line. These commands use the mysqlcheck client, which performs MySQL table maintenance such as repairing, optimizing, checks, and analysis.
One of the great benefits of the mysqlcheck client is that you are not required to stop your server to perform the operations, but note that your server must be started for the following commands to work. That means, for most sites, you can run these at any time or setup them up in the crontab to run regularly.
I find myself sometimes needing some quick reminders, as was such the case recently, so I figured I create a new post on some of the MySQL commands that I frequently use from the command line.
*Note: The commands shown are targeted towards a Linux OS, but it could just as easily be a Windows OS, by modifying the command accordingly.
Optimize Single Database: This command will optimize a single database, as specified in the command argument.
./mysqlcheck -o database_name
Optimize All Databases: This command will optimize all of the databases within your MySQL installation.
./mysqlcheck -o -A ./mysqlcheck -o --all-databases
Analyze Single Database: This command will analyze the tables in a single database, as specified in the command argument.
./mysqlcheck -a database_name
Analyze All Databases: This command will analyze the tables in all databases within your MySQL installation.
./mysqlcheck -a -A ./mysqlcheck -a --all-databases
Repair Single Database: This command will repair the tables in a single database, as specified in the command argument.
./mysqlcheck -r database_name
Repair All Databases: This command will repair the tables in all databases within your MySQL installation.
./mysqlcheck -r -A ./mysqlcheck -r --all-databases
Additional options for using mysqlcheck can be found here: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html
