MySQL Backup and Restore Using PHP

It is very important to make regular backup of your important data to keep them from data loss. There are several ways to backup and restore your MySQL database. The easiest way is by copying the database directory in:
/var/lib/mysql
or
C:\xampp\mysql\data (or MySQL installation folder if you are not using Xampp)
But you cannot do this if your database table is using InnoDB engine. Your InnoDB table will not be recognized when it is restored in another MySQL server. So I don’t recommend this way to backup your MySQL database. This tutorial will show you how to backup and restore your database safely.

Backup Databases

There are at least three ways to backup your MySQL Database:

1. Use phpMyAdmin to do the backup.
2. Run mysqldump from command line or using php system() function.
3. Execute a database backup query from PHP file.

If you want to backup your database from your application (PHP), you can do ways 2 or 3. I prefer to backup with mysqldump and I use this in all of my project.
The format of mysqldump command is like this:
$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
or
C:\xampp\mysql\bin\mysqldump.exe --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database username
• [pass] The password for your database (Note: there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [--opt] The mysqldump option

For example, to backup a database named 'Tutorial' with the username ‘root’ and password ‘toor’ to a file tutor_backup.sql, you should accomplish this command:
$ mysqldump -u root -ptoor Tutorial > tutor_backup.sql
This command will backup the 'Tutorial' database into a file called tutor_backup.sql which will contain all the SQL statements needed to re-create the database.

Backup Certain Tables

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorial' database accomplish the command below. Each table name has to be separated by space.
$ mysqldump -u root -ptoor Tutorial php_tutorials asp_tutorials > tutor_backup.sql

Backup Certain Databases

Sometimes it is necessary to back up more than one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.
$ mysqldump -u root -ptoor --databases Tutorial Articles Comments > t_a_c_backup.sql

Backup All Database

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.
$ mysqldump -u root -ptoor --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:
--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.
--no-data: Dumps only the database structure, not the contents.
--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

Restore Databases

Above we backup the Tutorial database into tut_backup.sql file. To re-create the Tutorial database you should follow two steps:
Create an appropriately named database on the target machine
Load the file using the MySQL command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
or
C:\xampp\mysql\bin\mysql.exe -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tutor_backup.sql file to the Tutorial database.
$ mysql -u root -ptoor Tutorial < tutor_backup.sql

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:
$ mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Here are my scripts to backup and restore MySQL database through PHP.

• mysql-backup-restore.zip

With this backup script, the output file can be downloaded. Just load the script from browser and save the output file. The output file is compressed in ZIP format.
The restore script gets the file through upload form. Just browse the output file that has been downloaded with backup script and click Upload button.

Please post below if you get a problem applying this tutorial.