By optimizing your MySQL database tables you can make improvements to your website. The optimizing function helps in reorganizing the table and index data, reducing space and improving I/O efficiency.
In this guide, you will learn to optimize your tables via SSH.
Steps to Optimize Your MySQL Database Using SSH
Sometimes if you run an optimization via phpMyAdmin, it leads to failure and the page ultimately times out. The reason behind this is the large size of database which the phpMyAdmin can’t handle. So, the alternative method is to optimize via SSH. If you are a shell user, you can SSH into your domain and perform the following command which is the same function run on phpMyAdmin:
[server]$ mysql -u username -ppassword -h hostname databasename -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $1 ";"}' | mysql -u username -ppassword -h hostname databasename
In the above command, replace the variables with your actual database information:
- username – database username
- password – user’s password
- hostname – database hostname that you set up and is active
- databasename – the database’s name in which you’re running the command
You can get the log of the command into a file for viewing by adding the below to the end of the command:
>> results.txt
Using a cron job to Optimize
Using a shell script and/or cron job using mysqlcheck, you can also cleanup overhead which checks, repairs, and optimizes tables. It’s best to run a monthly cron job to do this for you. The cron job might look as below:
#!/bin/sh /usr/bin/mysqlcheck -o -v -u USER -p PASSWORD -h MYSQL.EXAMPLE.COM DATABASENAME;
The items in all-caps need to be replaced with the actual credentials for your database.
Repairing via SSH
A command can be run to repair a table instead of optimizing it:
[server]$ mysql -u username -ppassword -h hostname databasename -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "repair table " $1 ";"}' | mysql -u username -ppassword -h hostname databasename
In this way, you can optimize a MySQL database using SSH.
