MySQL Backup and recovery mysqldump

Backup:
Back up a database (executed in shell, same as below):
Mysqldump-uusername-ppassword-hhostname--databases db_name >/path/db_name.sql

Backing up multiple databases
Mysqldump-uusername-hhostname-ppassword--databases db1_name db2_name db3_name >/path/multidb_name.sql

Back up all databases
Mysqldump-uusername-hhostname-ppassword--all-databases >/path/alldb_name.sql

Back up data structures only

Mysqldump-uusername-hhostname-ppassword--no-data db_name >/path/db_name.sql

Transactional-based backup

Mysqldump-uuser-hhostname-ppassword--opt--single-transaction--default-character-set=utf8--triggers db_name >/ Path/db_name.sql

Online backup
1 mysqldump-uuser-ppassword-hhostname--opt--single-transaction--default-character-set=utf8--master-data=1-- Flust-logs db_name >/path/db_name.sql

Compress backup
Mysqldump-uusername-hhostname-ppassword db_name |gzip-9 >/path/db_name.gz

Common options:
--databases db_name
--all-databases
--single-transaction (Applicable transaction table)
--master-data (hot Backup)
--trigers (also export trigger, this option is enabled by default, disable him with--skip-trigers)
--ROUTINES,-R (Export stored procedures and custom functions)
--opt (This is just a shortcut option, equivalent to adding--add-drop-tables--add-locking--create-option--disable-keys--extended-insert at the same time-- Lock-tables--quick--set-charset
Options. This option allows mysqldump to export data quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with--skip-opt. Note that if you run mysqldump, you do not specify--quick or--opt options
, the entire result set is placed in memory. Problems can occur if you export a large database. )
--no-data,-d (do not export any data, export only database table structure)
--NO-CREATE-INFO,-T (export data only, without adding a CREATE TABLE statement)
--lock-tables (which is similar to--lock-all-tables, but locks the currently exported data table instead of locking all the tables in the library all at once.) This option applies only to MyISAM tables, and if the Innodb table is available--single
-transaction option. )
--hex-blob (use hexadecimal format to export binary string fields.) This option must be used if you have binary data. The field types affected are BINARY, VARBINARY, BLOB. )
--extended-insert = True|false (by default, mysqldump turns on--complete-insert mode, so if you don't want to use it, use this option to set its value to false.) )

MySQL Restore:
Normal backup Restore

MySQL db_name </path/db_name.sql #此时的数据库必须事先手动创建好 and then in the recovery

Compress backup Restore

Gunzip </path/db_name.gz | Mysql-uusername-ppassword-hhostname db_name

Restore in MySQL
Source/root/data.back

To transfer a database to a new server
Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

Example
The recovery database must specify a database that already exists DB2
[Email protected] bin]# mysql-u root-p123 DB2 < All.sql
Mysql> Source/root/db1.sql; (Restore in MySQL)

#备份db1里的t7表
[Email protected] mysql]# mysqldump-u root-p123 db1 T7 > T7.sql
#恢复db1里的t7表
[Email protected] mysql]# mysqldump-u root-p123 DB1 T7 < T7.sql
#备份所有库
[Email protected] mysql]# mysqldump-uroot-p123--all-database > All.sql
#恢复所有库
[Email protected] mysql]# mysqldump-uroot-p123 < All.sql
#备份db1里面的tb1和tb2的表
[Email protected] mysql]# mysqldump db1 tb1 tb2-u root-p123 > Biao.sql

Related reading:

Backup MySQL database into SQL file by mysqldump under Linux http://www.linuxidc.com/Linux/2013-02/79793.htm

The MySQL database is scheduled to be backed up using mysqldump in Linux http://www.linuxidc.com/Linux/2012-03/56063.htm

Mysqldump missing-q parameter causes MySQL to be killed by Oom http://www.linuxidc.com/Linux/2013-09/90092.htm

Mysqldump and LVM Logical volume snapshots http://www.linuxidc.com/Linux/2013-09/90382.htm

MySQL Backup solution--(leveraging mysqldump and Binlog binary logs) http://www.linuxidc.com/Linux/2013-09/89975.htm

[MySQL] Make text backup with Mysqldump http://www.linuxidc.com/Linux/2013-11/92427.htm
Linux

This article is from the Linux commune website (www.linuxidc.com) Source Link: http://www.linuxidc.com/Linux/2013-11/93253.htm

MySQL Backup and recovery mysqldump