Mysql Restore and Backup

1. Getting backup of a MySQL database using mysqldump.

Use following command line for taking backup of your MySQL database using mysqldump utility.

mysqldump –-user [user name] –-password=[password] [database name] > [dump file]   or mysqldump –u[user name] –p[password] [database name] > [dump file]

Example:

mysqldump –-user root –-password=myrootpassword db_test > db_test.sql   or   mysqldump –uroot –pmyrootpassword db_test > db_test.sql

2. Backup multiple databases in MySQL.

mysqldump –u[user name] –p[password] [database name 1] [database name 2] .. > [dump file]

Example:

mysqldump –-user root –-password=myrootpassword db_test db_second db_third > db_test.sql

3. Backup all databases in MySQL.

shell> mysqldump –u[user name] –p[password] –all-databases > [dump file]

4. Backup a specific table in MySQL.

shell> mysqldump --user [username] --password=[password] [database name] [table name] \ > /tmp/sugarcrm_accounts_contacts.sql

Example:

shell> mysqldump --user root --password=myrootpassword db_test customers \ > db_test_customers.sql

5. Restoring MySQL database.

The mysqldump utility is used only to take the MySQL dump. To restore the database from the dump file that you created in previous step, use mysql command.

shell> mysql --u [username] --password=[password] [database name] < [dump file]

Example:

shell> mysql --user root --password=myrootpassword new_db < db_test.sql