Techie Zone

In this article we will learn on how we can perform following operations using MySQL.
* Backup Database using MySQL
* Backup specific tables using MySQL
* Backup Multiple Database using MySQL
* Backup Database Structure using MySQL
* Restoring Backup using MySQL

MySQL comes with an powerful console command called “mysqldump”. With this command we can achieve all the operation listed above. To check whether you have mysqldump command just type mysqldump in your command prompt you will get following output.

	[root@Hitesh ~]# mysqldump
	Usage: mysqldump [OPTIONS] database [tables]
	OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
	OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
	For more options, use mysqldump --help

If you dont see this prompt check for where you have installed the mysql on your system.

Backup Database using MySQL:
The syntax for taking backup of mysql database is:

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

o [username] – this is your database username
o [password] – this is the password for your database
o [databasename] – the name of your database
o [backupfile.sql] – the file to which the backup should be written.

Example:

 	mysqldump -u root -p password employee > /tmp/dbback.sql

Here we have taken the full backup for all the tables inside employee database and all the sql statements are being written inside /tmp/dbback.sql. Full backup means it will have both the insert statements and create statements required for creating and populating the tables.

Regular Expression in MySQL

Backup specific tables using MySQL:
The syntax for taking backup of specific mysql table is:

    mysqldump -u [username] -p [password] [databasename] [tablename1, tablename2 ...] > [backupfile.sql]

o [username] – this is your database username
o [password] – this is the password for your database
o [databasename] – the name of your database
o [tablename] – the name of your table existing inside the database selected
o [backupfile.sql] – the file to which the backup should be written.

Example:

 	mysqldump -uroot -p password employee salary> /tmp/dbback.sql

Here we have taken the backup for salary table inside employee database.

Backup Multiple Database using MySQL:
To take backup of multiple databases you will have to use “–databases” options provided with mysqldump, every name specified after “–databases” will be considered as the database name you wish to take the backup. Note that if you taking the backup for multiple database than you cannot take the backup for individual tables.
The syntax for taking backup of multiple mysql table is:

    mysqldump -u [username] -p [password] -- databases [databasename1] [databasesname2] > [backupfile.sql]

o [username] – this is your database username
o [password] – this is the password for your database
o [databasename1] – the name of your database
o [databasename2] – the name of your database
o [backupfile.sql] – the file to which the backup should be written.

Example:

 	mysqldump -uroot -p password --databases employee products> /tmp/dbback.sql

Backup Database Structure using MySQL:
In all the above cases we have discussed will add all the Data Manipulation Statements, if we only want Data Definition Statements then we will have to use –no-data options with mysqldump.
The syntax for taking backup of database structure is:

    mysqldump --no-data -u [username] -p [password] [databasename1] > [backupfile.sql]

o [username] – this is your database username
o [password] – this is the password for your database
o [databasename1] – the name of your database
o [backupfile.sql] – the file to which the backup should be written.

Example:

 	mysqldump --no-data -uroot -p password employee > /tmp/dbback.sql

Restoring Backup using MySQL:
For restoring the data we will be using mysql command. The syntax for the same would be

mysql -uroot -p password employee < /tmp/dbback.sql

Here we are restoring the dbdack.sql to employee database

Custom Search

Popular Articles:

• Column count doesn’t match value count in MySql
• Installing mytop on Linux
• MySql – Cross Join Queries
• Understanding MySQL Joins
• MySql – Create Foreign Key
• MySql Batch Insert/Update in Java
• Database Class in PHP5
• MySql Prepared Statement in PHP
• MySql Batch Insert Using PHP
• MySql – Working With Stored Procedure