[Advanced ]Mysqldump, a database backup/restore utility

I will try to cover all possible methods to take backup and restore backup using mysqldump utility.


How to take database backup using mysqldump
mysqldump -u[username] -p[password] [database name] > dum_file_name.sql
e.g
mysqldump -uroot -ppassword mysql > mysql.sql

How to take backup of multiple databases at the same time

mysqldump -u[username] -p[password] [database 1 name]..[database 2 name]
e.g

mysql -uroot -ppassword DB1 DB2 DB3

How to take backup all the databases
mysqldump -u[username] -p[password] -all-databases > [dumpfile.sql]

How to take backup of specific table of a database

mysqldump -u[username] -p[password] [database name] [table Name] > dumpFIle.sql

e.g.

mysqldump -uroot -ppassword mysql user > user.sql

How to backup of table structures:

mysqldump -d -uroot -ppassword database01 > dumpfile.sql

How to restore backup
Database backup can be restored using mysqldump as well as mysql command.

mysql -u[user] -p[password] database name < dumpfile.sql

e.g.
mysql -uroot -ppassword mysql < mysql.sql

I hope you find this article helpful.

Suggestions are always welcome :)

How to take database backup in MySQL

Q. How to take database backup in MySQL

I will explain different options of taking database backup in MySQL server. Let support I have database 'TestDB' which I want to take backup of with different options.

# mysqldump -uroot -ppassword TestDB >TestDB.sql

If your database contains procedures and function then use -R flag and the modified command would be as follows;

# mysqldump -uroot -ppassword TestDB -R > TestDB.sql

What if you only want to take backup of table structures then commands will look as given below.


# mysqldump -uroot -ppassword -d TestDB > TestDB.sql