+--------------------+
4 rows in set (0.00 sec)
For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:
# mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql
Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.
# grep -i "Current database:" /tmp/bugs_sugarcrm.sql
-- Current Database: `mysql`
-- Current Database: `sugarcrm`
3. Backup all the databases:
The following example takes a backup of all the database of the MySQL instance.
# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql
4. Backup a specific table:
In this example, we backup only the accounts_contacts table from sugarcrm database.
# mysqldump -u root -ptmppassword sugarcrm accounts_contacts \ > /tmp/sugarcrm_accounts_contacts.sql
4. Different mysqldump group options:
–opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
–compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks
How To Restore MySQL database
1. Restore a database
In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore.
# mysql -u root -ptmppassword
mysql> create database sugarcrm;
Query OK, 1 row affected (0.02 sec)
# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.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 -ptmppassword sugarcrm | mysql \ -u root -ptmppassword --host=remote-server -C sugarcrm1 [Note: There are two -- (hyphen) in front of host]
本文出自 “迈小步、不停步！” 博客