How to backup and restore MySQL database


If you are a computer user then you might know how bad is to deliberately lose some of your important data. To avoid such situations you should back up your data and store the backup file in a safe place. By doing this, you can easily restore lost data and you can get back on track very quickly. Today, we will show you how to make a backup of your MySQL database and restore it later on your Linux VPS. This procedure is very easy and can be very helpful especially if you keep all Your website data into a single database.


Backing up a MySQL database. The first thing you need to do is to log into your server via SSH. After you login, you will need to run a few commands to create a file backup of your database and copy the backup file on your local computer or to a different directory on your server. One of the command is:

# mysqldump -u username -p database-name > backup-name.sql

Important note: do not forget to replace the name with the actual user name, database-name with the name of the database who wish to make a backup and backup file name with URL-name.sql. After you run this command, you are prompted to enter the password for the username you use to make a backup of the database.

Enter a password and that’s it! You have created a backup of your database file. For example, if you want to create a backup file of your WordPressDB on Your VPS WordPress and save it as URwordpress.sql using wordpressuser as a user, you can enter:

# mysqldump -u WordPressUser -p WordPressDB > wordpress.sql

Now you need to enter the password from wordpressuser. Now, it is important to copy or move a file backup to a secure directory on the server or your local computer. You can do this by using the command ‘ rsync ‘. It is very easy, so let’s do this. To copy the files to a different directory on Your server using the following command:

# rsync -Waq backup-name.sql /path/to/directory/

Once again, do not forget to replace URL-name.sql with the name of Your backup file and/path/to/directory/with the path to the directory where you want to copy Your backup file. If you want to copy URbackup-name.sql to your local computer, you can use the command:

# rsync -Waq -e ‘ssh -p port-number’ username@IP-Address:/backup-name.sql /path/to/local/directory

Here, you need to change the port numbers with Your SSH listening port, username with your username that you use to connect to your server, IP-Address with the IP address of your server, URL-name.sql with backup and file name/path/to/local/directory with directory path on your local computer where you want to save the backup file. Of course, you must run this command from your local computer and you will be prompted to enter the password for the username you use to connect to your server.

Restore MySQL database After you create Your database backup files, you can easily restore it in case of an accident. To restore a backup file you must run the following command:

# mysql -u username -p database-name < backup-name.sql

mysql -u username -p database-naChange the user name, the database name and the actual value of URbackup-name.sql, enter your password and that’s it. You have successfully restored your database.e < backup-name.sql