Backup and Restore MySQL Database Using mysqldump

--> mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table, Lock table and INSERT into sql-statements of the source database. To restore the database, execute the *.sql file on destination database.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore MySQL database using mysqldump:

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql 

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

How To Backup MySQL database

1. Backup a single database:

This example takes a backup of sugarcrm database and dumps the output to abc.sql

# mysqldump -u root -p abc > abc.sql

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

2. Backup multiple databases:

# mysqldump -u root -p --databases database1 database2 > database1_database2.sql

3. Backup all the databases:

# mysqldump -u root -p --all-databases > /tmp/all-database.sql

4. Backup a specific table:

# mysqldump -u root -p abcdatabase usertable > /tmp/abc_user_table.sql

# mysqldump -u root -p test t1 --where="1=1 limit 100" > arquivo.sql

 

 

How To Restore MySQL database

1. Restore a database

# mysql -u root -p databasename < /tmp/all-database.sql 

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server]# mysqldump -u root -p abc | mysql \ -u root -p --host=remote-server -C abc1 [Note: There are two -- (hyphen) in front of host]

Source From: http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/