How to repair MySQL databases and tables hosted on VPS servers

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.

SSH login

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

Start MySQL server

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:

cd /var/lib/mysql

Now type this command, replacing mydatabase with the name of the database you wish to check and repair.

mysqlcheck mydatabase

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.

 

Was this article helpful?

Related Articles

Leave A Comment?