How to Optimize a MySQL Database Using SSH?

Estimated reading: 2 minutes 67 views

You may enhance your website by optimizing the tables in your MySQL database. The optimizing function saves space, enhances I/O efficiency, and rearranges the table and index data.

This tutorial will teach you how to use SSH to optimize your tables.

Steps to Optimize Your MySQL Database Using SSH

Occasionally, using phpMyAdmin to do optimizations results in failure and a page timeout. This is because the database is far larger than what phpMyAdmin can manage. Thus, using SSH for optimization is an other approach. If you are a shell user, you can use SSH to get into your domain and run the same command that phpMyAdmin runs:

[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