Restoring MySQL Databases from Data Files in a VPS: A Step-by-Step Guide

MySQL offers various backup and restoration methods, allowing users to select the best approach for their backup and disaster recovery needs. Backups are crucial for upgrading MySQL installations, transferring installations to other systems, and setting up clones.

a) Install and stop the MySQL server

To install a MySQL server on your VPS, connect via SSH and run the yum install mysql-server command. Use the same MySQL version, but newer versions can be used. After installation, stop the MySQL server by entering the service mysql stop command to add it to the data folder.

b) Copy data

First, use the cp -rf /mnt/var/lib/mysql/* /var/lib/mysql command to copy old MySQL data to the new MySQL server data file location. Then, use the chown -R mysql:mysql /var/lib/mysql command to ensure file ownership is changed. Once complete, start the MySQL server by entering the service mysql start command.

c) Final checks and export

After that, use the mysqlcheck -rA command to check and repair all database tables according to the MySQL version to ensure data integrity. Finally, use the mysqldump –all-databases > all_databases.sql command to export the database to ensure complete recovery.