Backup and Restore a MySQL Database

This is a beginner tutorial that will show you how to perform MySQL backups and restoration.

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).

{mospagebreak title=MySQL Back Up and Restore using phpMyAdmin GUI Method}

Using the phpMyAdmin GUI Method

For those that are not comfortable with the command line method and do not have SSH access, you can still use phpMyAdmin to do backups and database restoration. This is the GUI method and is done using a web browser.

Take note that it has some limitations if you have a very large database; you might have import and export issues because it might timeout. Also, it is recommended that you check if your phpMyAdmin session in the browser is encrypted. Make sure it uses HTTPS in the browser address bar.

Backup Databases

1.) Log-in to your hosting control panel and click phpMyAdmin.
2.) On the left, you can see a list of databases in your phpMyAdmin. If you have more than one database, you can see them all. Click the database name that you would like to backup.
3.) You will then see the actual database tables and details about the database. Now click “Export”.
4.) Under “Export”, click “Select all”.
5.) Select the “SQL” radio button.
6.) Scroll down and make sure “Save as file” is checked.

The following are the recommended settings for phpMyAdmin when doing backups: http://www.php-developer.org/screenshot/backupphpmyadminoptions.jpg

7.) Click “Go” to download the backup to your computer.

Restoring Databases

Supposing you will use the same restoration example as the command line method, but want to restore the database using phpMyAdmin, these are the steps to follow:

1.) Follow the Step 1 procedure discussed in the “Restoring Database – command line method” example (re-creating the database and re-assigning the users).

2.) Once the database has been re-created, it can be accessed using phpMyAdmin. Click phpMyAdmin in the hosting control panel.

3.) Click the database on the left. This database does not yet contain any tables since it is empty. Click “Import”.

4.) Under “File to Import”, browse to the clean backup database (done in the backup section) and open it. Leave the other settings at their default value. Make sure the format of the imported file is SQL.

This is the recommended settings for phpMyAdmin when restoring a database: http://www.php-developer.org/screenshot/restortedatabasephpmyadmin.jpg

5.) Finally click “Go”. The clean backup will be uploaded to your server and phpMyAdmin will restore it. If you have a large database (2MB to 50MB), it might timeout (varies depending on web host).

6.) If you see “Import has been successfully finished”. The restoration process is complete.

Tip: If you have a corrupted database that still exists, you cannot overwrite it with a clean database using the above procedure (starting from Step 2 to Step 6) while there are still tables in the database because it will result in the following error:

MySQL said: Documentation
#1062 – Duplicate entry

You need to drop all of the database tables first (not the database itself). This is done by going to “Structure”, and then if you see the entire list of tables, scroll down and check “Check All”. Once all tables are selected, beside it you will see “With selected”, select “Drop”. Then click "Yes". Once it is empty, execute Step3 to Step 6 again.

{mospagebreak title=Automating MySQL Backups with Cron and PHP}

Using a Cron and a PHP Script

Now that you have a basic understanding of how to do backups and restoration of MySQL databases, it’s time to automate the backup process. One of the simplest methods is to use a PHP script to backup a MySQL database, then use your hosting cron feature to periodically run the PHP script depending on your backup needs (daily, weekly, or monthly).

A sample PHP script to get started is here: http://www.php-developer.org/php-script-to-backup-mysql-database-using-hosting-cron/. The key feature is that it will allow you to backup up to 4 MySQL database in your server using cron. Then the database backups are saved in your server specified by the $full_serverpath_to_backup variable.

For details, you can download the script, extract and test. Read the details inside cronmysqlbackup.php, those commented lines provide a good introduction for beginners. The actual steps are illustrated in Step 11 inside cronmysqlbackup.php and it is:

1.) Define your website MySQL login credentials such as username, password, hostname, database name, and so forth.

2.) Define the path to your cronmysqlbackup folder as defined by $full_serverpath_to_backup. You can optionally add other databases aside from your main website database.

3.) Decide how many days will you delete the old MySQL files. The unit is in days.

4.) Finally upload the cronmysqlbackup folder with cronmysqlbackup.php and .htaccess to the path above your web root.

5.) Enable hosting cron to automatically run this script at intervals you choose e.g. weekly, monthly

The path that you have uploaded should be the same path as what you have declared in $full_serverpath_to_backup

The concept of implementation for that script relies on mysqldump command, as you can see in these lines:

$command1 = "mysqldump -u$username1 -p$password1 -h$hostname1 $database1 > $MySQLbackupfile1";
system($command1, $result1);
echo $result1;

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort