As your website database grows in size, errors may occur from time to time. Fortunately, tools are available to quickly check and repair database tables. In this guide, we’ll walk through the steps required to verify and fix database tables stored on HostPapa VPS servers.
You will need SSH access to the server – to find out more about logging in to your server using SSH, please read this HostPapa knowledge base article: How to connect to your server via Secure Shell (SSH).
You can use SSH to connect to your server from Windows, Mac OS, and Linux computers using command line clients. Mac OS and Linux have integrated SSH support in Terminal – you can simply open a Terminal window and get started.
The Windows Command Prompt application, however, does not support SSH by default. We recommend downloading a third-party application such as PuTTY, which includes SSH support.
Back up your database
The first step is to back up your database, protecting your data from any unwanted changes. Log in to your server using SSH.
Stop the MySQL server using the following command:
service mysql stop
Type the following command, which copies the contents of your databases to a directory name based on the current time.
cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)
For greater security, move this folder to a remote server or your local computer.
Now restart the MySQL server with this command:
service mysql start
Check and repair your database tables
We use an integrated tool called mysqlcheck to verify and repair database tables while the MySQL server is running. Follow these steps to use the tool.
Type the following command:
Now type this command, replacing mydatabase with the name of the database you wish to check and repair.
You can check a specific table in the database with the following command (change mydatabase and mydatabasetable with their real-world equivalents):
mysqlcheck mydatabase mydatabasetable
If your database has errors, they’ll be reported on screen. Use the following command to repair the database tables:
mysqlcheck -r mydatabase mydatabasetable
For further questions, or if you need help, please open a support ticket from your HostPapa Dashboard. Follow this link to learn how.