Backup and Restore a MySQL Database

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
2.) Using the phpMyAdmin method
3.) Using a Cron and PHP Script for automated/periodic backups

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)
2.) To backup, you will be using mysqldump command.

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:

/YOUR/SERVER/PATH/NAMEOFYOURSQLFILE.sql

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.

Illustration:

Supposing you have the following database credentials that you would like to backup:

username: peter
password: abc123
hostname: localhost
databasename: databasexyz
path where you would like to save the backup: /home/peter
name of the backup SQL file: peterdatabase.sql

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:

password: `helloP*eter

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:

username: peterssh
hostname: peter@example.org
Local computer Ubuntu desktop path: /home/peterlocal/Desktop

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@peter@example.org:/home/peter/peterdatabase.sql /home/peterlocal/Desktop

If your SSH server is using Port 7896 then use the -P parameter:

scp -P 7896 peterssh@peter@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”.
b.) Under “Create New Database”, assign the same database name as the previous deleted database.
c.) Click “Create Database”.
d.) Scroll down and find “Add User to Database”. Assign the same user name which has full access rights to the previous database deleted. Then click “Add”.
e.) Check “All Privileges”.
i.) Click “Make changes”.

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
MySQL username: peter
MySQL password: abc123
MySQL hostname: localhost

Here is the location of the clean backup in your remote server: /home/peter/peterdatabase.sql
If your backup is not yet in your server, but is stored on your local desktop, upload it first to your hosting server using SCP, for example:

scp -P <portnumber> /home/peterlocal/Desktop/peterdatabase.sql peterssh@peter@example.org:/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).