We’ll show you, How to backup and restore MySQL database. If you are a computer user then you probably know how bad is to accidentally lose some of your important data. To avoid such situations you should create a backup of your data and store the backup file in a safe place. By doing this you can easily restore the lost data and you can get back on track very quickly. Today, we will show you how to create a backup of your MySQL database and restore it later on your Linux VPS. The procedure is very easy and could be very helpful especially if you store all your website data into a single database.
Back up your MySQL database
The very first thing you need to do is to log in to your server via SSH. Once you log in, you will need to execute few commands in order to create a backup file of your database and copy the backup file on your local machine or different directory on your server. One of the commands is:
mysqldump -u username -p database-name > backup-name.sql
Important notes:
Do not forget to replace the username with your actual username, the database-name with the name of the database you want to back up and the backup-name.sql with the name of the backup file.
After you execute this command, you will be asked to enter the password for the username you are using to back up the database.
Enter the password and that’s it! You have created a backup file of your database.
For instance, if you like to create a backup file of your WordPressDB on your WordPress VPS and save it as wordpress.sql using WordPressUser as user, you can enter:
mysqldump -u WordPressUser -p WordPressDB > wordpress.sql
You should now insert the password of the WordPressUser.
Now, it is important to copy or move the backup file to a safe directory on your server or on your local machine. You can do this using the ‘rsync’ command. It is very easy, so let’s do this.
In order to copy the file to a different directory on your server use the following command:
rsync -Waq backup-name.sql /path/to/directory/
Again, do not forget to replace the backup-name.sql with the name of your backup file as well as the /path/to/directory/ with the path to the directory where you want to copy your backup file.
If you want to copy the backup-name.sql to your local machine 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-number with your SSH listening port, the username with the username you are using to connect to your server, the IP-Address with the IP address of your server, the backup-name.sql with the name of the backup file and the /path/to/local/directory with the path of the directory on your local machine where you want to save the backup file. Of course, you will have to execute this command from your local machine and you will be asked to enter the password for the username you are using to connect to your server.
Restore your MySQL database
Once you create a backup file of your database you can easily restore it in case of an accident. To restore the backup file you need to execute the following command:
mysql -u username -p database-name < backup-name.sql
Change the username, database-name and backup-name.sql with the actual values, enter your password and that’s it. You have successfully restored your database.
Of course, you don’t have to backup and restore MySQL database, if you use one of our Managed hosting services, in which case you can simply ask our expert Linux admins to back up or restore your database for you. They are available 24×7 and will take care of your request immediately. You can also try reading our guide on How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04.
PS. If you liked this post, on How to backup and restore MySQL database, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.