Two useful methods to Back Up and Restore MySQL Database

When you create a database, it means that you can store anything in your database. It is very important to create regular backups of your data to protect it from any data loss problem.

I have explained here two useful methods to backup and restore the data in your MySQL database.

Backup & Restore MySQL database by using mysqldump:

“mysqldump” command connects to the MySQL server and creates an SQL dump file. This dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

 $ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

You can change the values as per your database requirements like replace [uname] with your required username. Same applied for all values.

You can also take backup of certain tables of your database:

$ mysqldump --opt -u [uname] -p[pass] [dbname] [table1name] [table2name] > [backupfile.sql]

Lets assume all the fields:

[uname]: adam
[dbname]: employees
[table1name]: employees_info
[table2name]: employees_salary
[backupfile.sql]: emp_backup.sql

Now the command will write like this:

$ mysqldump -u adam -p employees employees_info employees_salary > emp_backup.sql

You can also backup multiple databases at once by using --database option followed by the list of databases you would like to backup. In this command the database names has to be separated by space. In case, If you wanted to backup all the databases in the server at once then you can do it by using the --all-databases option. You can also use some other options in mysqldump command like: --add-drop-table, --no-data, --add-locks.

Backup & Restore MySQL database by using PHPMyAdmin:

Follow these steps to backup your database by using PHPMyAdmin:

• Open phpMyAdmin.
• Click the database name button in the list on the left of the screen & select your database that you want to backup.
• Now Click to Export link.
• In the Export area, choose all of the tables by clicking the Select All link
• In the SQL options area, click the right options.
• Now select Save as file option & as well as all corresponding compression options and then click the 'Go' button.

Restoring your database by following steps:

• Open phpMyAdmin.
• Create a database and select it by clicking the database name in the list on the left of the screen.
• Click the SQL link from where you can either type in SQL commands, or upload your SQL file.
• Click on browse button to find the database file.
• Click Go button. This will upload the backup, execute the SQL commands and re-create your database.

Conclusion: You can use any of the above methods for Mysqldump & phpmyadmin to backup & restore your database. There is one disadvantage of Mysqldump i.e. this command lock the tables. If your table is big in size then mysqldump can lock out users for a long period of time. Phpmyadmin has no such issues.