This tutorial will illustrate different techniques to backup and restore MySQL databases, depending on which method you prefer to use:
1.) Using the Secure Shell - SSH or command line method
The first two are manual ways of doing backup and restoration, but they are important to you as a beginner so that you will know the basic commands and procedure. This tutorial is applicable to the latest version of MySQL which at the time of this writing is version 5.
Using the Secure Shell - SSH Command Line Method
Backup a Database
You can backup and restore a database using the SSH command line method provided your hosting supports SSH. Follow these steps:
1.) Login to your SSH server using command line (terminal if you are using Linux)
The standard syntax is below:
mysqldump -uUSERNAME -pPASSWORD -hHOSTNAME DATABASENAME > /YOUR/SERVER/PATH/NAMEOFYOURSQLFILE.sql
You will simply replace USERNAME, PASSWORD, HOSTNAME and DATABASENAME with the database credentials that you need to backup. The backup will be saved in your own website server at the path provided by:
Then the name of the SQL file depends on NAMEOFYOURSQLFILE.
There should no space between -u and USERNAME, -p and PASSWORD, -h and HOSTNAME. If you have spaces it won't work.
Supposing you have the following database credentials that you would like to backup:
The mysqldump command that would be entered into the SSH bash prompt would be:
mysqldump -upeter -pabc123 -hlocalhost databasexyz > /home/peter/peterdatabase.sql
After you press enter to execute the command, the backup will be generated. If you browse to this path: /home/peter, then you should see peterdatabase.sql in there.
One common problem is the presence of back ticks in the password, which can prevent the execution of mysqldump using the command line method. Supposing the password above is changed to:
The password contains a back tick, so the mysqldump won't execute; the solution is to enclose the password with single quotes in this manner:
mysqldump -upeter -p'`helloP*eter' -hlocalhost databasexyz > /home/peter/peterdatabase.sql
The above command will now execute. If you have accidentally entered a mysqldump command without single quotes in the password containing back ticks, you can bring back the bash prompt by pressing Control-D.
You might want to download this database directly to your computer (Ubuntu desktop for example). To do so, you simply need to use the SCP command (secure copy). Supposing you have the following SSH access:
Then the SCP syntax to copy the MySQL database to your Ubuntu desktop (you will need to exit all current SSH sessions first and enter this command in your Ubuntu terminal):
scp peterssh@email@example.com:/home/peter/peterdatabase.sql /home/peterlocal/Desktop
If your SSH server is using Port 7896 then use the -P parameter:
scp -P 7896 peterssh@firstname.lastname@example.org:/home/peter/peterdatabase.sql /home/peterlocal/Desktop
P should be in upper case and its OK to have some space between -P and the port number. This will work also in Windows OS with SSH and SCP functionality enabled.
The main advantage of using SSH command line method as compared to GUI method like phpMyAdmin is that you can dump a very large database without getting any timeout errors. It is also secure since the communication between your computer and the server is encrypted.
Restoring a Database
Restoring a database is also very important. Supposing your current database is corrupted, hacked or deleted. After cleanup, you need to restore a clean database from your backup done by following the steps in the previous section. You can do this very easily with secure shell. Again one of the main advantages is that you can restore very big databases without receiving any timeout errors.
Supposing your database is deleted and does not exist anymore; it needs to be re-created in your hosting control panel first before you can restore the database. The following steps use Cpanel:
1.) Login to your hosting control panel.
a.) Go to Databases and click “MySQL databases”.
Now that the databases has been re-created, you can restore the clean database.
2.) Supposing you have the following information needed to restore the database:
MySQL databasename: databasexyz
Here is the location of the clean backup in your remote server: /home/peter/peterdatabase.sql
scp -P <portnumber> /home/peterlocal/Desktop/peterdatabase.sql peterssh@email@example.com:/home/peter
Put it above the public_html (not accessible by public or browser).
3.) Finally, login to your SSH server using terminal (command-line). After logging in, issue the following command to restore the clean database:
mysql -upeter -pabc123 -hlocalhost databasexyz < /home/peter/peterdatabase.sql
4.) After pressing enter, your website will work normally, since a clean database has now been restored.
Tip: You can restore a database using the above command anytime, and it will overwrite the current database (in case the database is not deleted and still exists).
blog comments powered by Disqus