Location : PHP Hosting - Web Hosting Knowledge Base - PHP MySQL Tutorial

Backup Restore MySQL Database

In cases you need to transfer a mysql database from server to server, or keep a copy of the database elsewhere, you need to generate a backup of the database, or known as 'mysql dump', there are several ways you can perform a mysql database backup :-

Note: If is a good practice to always keep a regular backup of your important data, and check periodically to ensure the backups are in good condition (able to restore).

MySQL Shell command

If you are familiar with the Linux shell command and mysql shell commands, you can quickly get a mysql dump directly at the shell prompt by issuing the following command :-

mysqldump -umysql_user -pmysql_user_password mysql_dbname > dumpfile.sql

The dumpfile.sql generated is an ASCII text file and you can use FTP client to transfer it elsewhere.

There are some configuration option that you can set along with the command, for complete list of options, please refer to the official mysqldump documentation

For restoring a mysql dump file through shell, you can issue the following command at the shell prompt :-

mysql -umysql_user -pmysql_user_password mysql_dbname < dumpfile.sql

Note that the mysql dump file is generated in SQL codes, above command simply run the SQL dump file script from the shell prompt.

phpMyAdmin

You can use the phpMyAdmin database management tool to generate a backup of your MySQL database, first login to your web hosting account control panel, select SQL Database option to bring up the database management screen, scroll down to the bottom of the screen and click on phpMyAdmin to activate the phpMyAdmin program. When phpMyAdmin is loaded, select your database from the drop down box at the left side of the screen, a screen of the selected database schema is shown, click on Export tab at the top of the screen, click Select All to select all tables and the additional SQL commands like DROP table if needed. Click OK and the mysql dump file will be generated and ready for storing in your local PC.

For restoring a database using phpMyAdmin, it is similar to executing a external SQL script. First, select the database from the drop down box from the left side of the screen, then select SQL tab, in the screen there is an Import option for importing an .sql script, select it and choose the mysql dump file from your local PC, click OK to have phpMyAdmin run the sql dump file.

Note that if your dump file is too big and exceeded the php upload limit, you will get a php upload limit error, in this case it is recommended to manually upload the mysql dump file to the hosting account, and run the script from the command prompt.