0 like 0 dislike
567 views

Please log in or register to answer this question.

2 Answers

0 like 0 dislike
answered by (13.4k points)  
#!/usr/bin/env bash
 
##############################################################################
# Performs Mysql Database Maintenance Operations:
#   - Check
#   - Optimize
#   - Analyze
#
# WARNING:
#   These operations will cause LOCKS, so be cautious on a production
#   server!
#
# Based on:
#   - MySQL Maintenance Tasks for InnoDB with MySQL 5.1
#     http://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/
##############################################################################
 
set -e
 
mysql_username="root"
mysql_password="password"
mysql_hostname="localhost"
mysql_database_name="testdb"
 
echo
echo "Checking db \"$mysql_database_name\" for integrity errors..."
echo "-----------------------------------------------------------------------"
mysqlcheck -h $mysql_hostname -u $mysql_username -p$mysql_password --check --databases $mysql_database_name
 
echo
echo "Optimizing db \"$mysql_database_name\" to reclaim unused space..."
echo "-----------------------------------------------------------------------"
mysqlcheck -h $mysql_hostname -u $mysql_username -p$mysql_password --optimize --databases $mysql_database_name
 
echo
echo "Analyzing db \"$mysql_database_name\" to rebuild and optimize indexes..."
echo "-----------------------------------------------------------------------"
mysqlcheck -h $mysql_hostname -u $mysql_username -p$mysql_password --analyze --databases $mysql_database_name
 
echo
echo "Finished."
echo

 

0 like 0 dislike
answered by (13.4k points)  

Optimize, Repair, and Analyze a MySQL Database Using the Command Line

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.

01

./mysqlcheck -o database_name

Optimize All Databases: This command will optimize all of the databases within your MySQL installation.

01

02

./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.

01

./mysqlcheck -a database_name

Analyze All Databases: This command will analyze the tables in all databases within your MySQL installation.

01

02

./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.

01

./mysqlcheck -r database_name

Repair All Databases: This command will repair the tables in all databases within your MySQL installation.

01

02

./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

Related questions

0 like 0 dislike
6 answers
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
...