How to back up a MySQL database

MySQL is an Open Source Database that has gained a lot of popularity in the past few years and has evolved into a very stable and favorable database solution for a wide variety of business requirements. MySQL is available for several different platforms. 

Taking the back up

phpMyAdmin

One popular way to access MySQL databases especially remote MySQL databases is through the open source PHP script called phpMyAdmin.

Taking back up from phpMyAdmin is fairly simple, all you need to do is click on Databases and go to the Export tab and there you can export the data of a table of several tables including the DDL for creation of the table to a file in various formats which you can choose from.

mysqldump command

The other more traditional method of administering MySQL would be through command line. The mysqldump command dumps MySQL data onto the console by default, but we can redirect it to a file, and take backups easily.

The syntax of mysqldump is as follows:

mysqldump -u [username] -p [password] [databasename] [tablenames] > [backupfile.sql]

[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[tablenames] – the names of various tables separated by space
[backupfile.sql] - the file to which the backup should be written

eg.) mysqldump --add-drop-table -u sadmin -p password Employees employee_master employee_details> empbackup.sql

Here the database name is Employees and we are taking backup of two tables, namely employee_master and employee_details. empbackup.sql file will look something like this:

CREATE TABLE employee_master (f DOUBLE);

INSERT INTO employee_master VALUES(…);

INSERT INTO employee_master VALUES(…); …so on

Restoring backup

The data can be restored by executing the backup script, empbackup.sql using the following commands:

$ mysql -u root –p
 mysql>CREATE DATABASE Employees;
 mysql> \q
 Bye
 $ mysql -u root -p Employees < empbackup.sql