mysqldump数据库备份参数详解

 

mysqldump备份:

mysqldump备份:

Preface

复制代码 代码如下:

复制代码 代码如下:

 

mysqldump -u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables
> 路径

mysqldump -u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables
> 路径

    We’re used to get a logical backup
set(whole instance) by simply specifying “–single-transaction” and
“–master-data” options.For innodb engine tables,we can get a consistent
backup.But the non-innodb(not supporting transaction) tables may be not
backed up consistently in MySQL 5.7.

mysqldump还原:

mysqldump还原:

 

复制代码 代码如下:

复制代码 代码如下:

Procedure

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

 

mysqldump按条件导出:

mysqldump按条件导出:

Check
and open general log(if have not enabled it yet).

复制代码 代码如下:

复制代码 代码如下:

 1 (zlm@192.168.1.101 3306)[(none)]>show variables like 'general_log';
 2 +---------------+-------+
 3 | Variable_name | Value |
 4 +---------------+-------+
 5 | general_log   | OFF   |
 6 +---------------+-------+
 7 1 row in set (0.00 sec)
 8 
 9 (zlm@192.168.1.101 3306)[(none)]>set @@global.general_log=on;
10 Query OK, 0 rows affected (0.01 sec)
11 
12 (zlm@192.168.1.101 3306)[(none)]>show variables like 'general_log';
13 +---------------+-------+
14 | Variable_name | Value |
15 +---------------+-------+
16 | general_log   | ON    |
17 +---------------+-------+
18 1 row in set (0.00 sec)

mysqldump -u用户名 -p密码 -h主机 数据库 a –where “条件语句” –no-建表>
路径
mysqldump -uroot -p1234 dbname a –where “tag=’88′” –no-create-info>
c:\a.sql

mysqldump -u用户名 -p密码 -h主机 数据库 a –where “条件语句” –no-建表>
路径
mysqldump -uroot -p1234 dbname a –where “tag=’88′” –no-create-info>
c:\a.sql

 

mysqldump按条件导入:

mysqldump按条件导入:

**Clear
the content of general log and tail it.**

复制代码 代码如下:

复制代码 代码如下:

1 [root@zlm2 09:35:07 /data/mysql/mysql3306/data]
2 #echo ''> zlm2.log
3 
4 [root@zlm2 09:35:13 /data/mysql/mysql3306/data]
5 #tail -f zlm2.log

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

 

案例:

案例:

Create test
tables.

复制代码 代码如下:

复制代码 代码如下:

 1 (zlm@192.168.1.101 3306)[(none)]>create database zlm;
 2 Query OK, 1 row affected (0.00 sec)
 3 
 4 (zlm@192.168.1.101 3306)[(none)]>use zlm;
 5 Database changed
 6 
 7 (zlm@192.168.1.101 3306)[(none)]>create table test_trx(
 8     -> id int,
 9     -> name varchar(10)
10     -> ) engine=innodb;
11 Query OK, 0 rows affected (0.01 sec)
12 
13 (zlm@192.168.1.101 3306)[zlm]>insert into test_trx values(1,'zlm');
14 Query OK, 1 row affected (0.01 sec)
15 
16 (zlm@192.168.1.101 3306)[zlm]>create table test_notrx like test_trx;
17 Query OK, 0 rows affected (0.01 sec)
18 
19 (zlm@192.168.1.101 3306)[zlm]>insert into test_notrx values(2,'aaron8219');
20 Query OK, 1 row affected (0.00 sec)
21 
22 (zlm@192.168.1.101 3306)[zlm]>alter table test_notrx engine=MyISAM;
23 Query OK, 1 row affected (0.01 sec)
24 Records: 1  Duplicates: 0  Warnings: 0
25 
26 (zlm@192.168.1.101 3306)[zlm]>show create table test_trx\G
27 *************************** 1. row ***************************
28        Table: test_trx
29 Create Table: CREATE TABLE `test_trx` (
30   `id` int(11) DEFAULT NULL,
31   `name` varchar(10) DEFAULT NULL
32 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
33 1 row in set (0.00 sec)
34 
35 (zlm@192.168.1.101 3306)[zlm]>show create table test_notrx\G
36 *************************** 1. row ***************************
37        Table: test_notrx
38 Create Table: CREATE TABLE `test_notrx` (
39   `id` int(11) DEFAULT NULL,
40   `name` varchar(10) DEFAULT NULL
41 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
42 1 row in set (0.00 sec)
43 
44 (zlm@192.168.1.101 3306)[zlm]>select * from test_trx;
45 +------+------+
46 | id   | name |
47 +------+------+
48 |    1 | zlm  |
49 +------+------+
50 1 row in set (0.00 sec)
51 
52 (zlm@192.168.1.101 3306)[zlm]>select * from test_notrx;
53 +------+-----------+
54 | id   | name      |
55 +------+-----------+
56 |    2 | aaron8219 |
57 +------+-----------+
58 1 row in set (0.00 sec)

mysql -uroot -p1234 db1 < c:\a.txt

mysql -uroot -p1234 db1 < c:\a.txt

 

mysqldump导出表:

mysqldump导出表:

Generate a
backup with mysqldump specifying common option of “–single-transaction”
together with “–master-data=2”.

复制代码 代码如下:

复制代码 代码如下:

 1 [root@zlm2 09:36:23 ~]
 2 #mysqldump --single-transaction --master-data=2 zlm > test1.sql
 3 
 4 //General log output below.
 5 2018-07-30T09:45:17.815021+01:00        4 Connect    zlm@zlm2 on  using TCP/IP
 6 2018-07-30T09:45:17.815290+01:00        4 Query    /*!40100 SET @@SQL_MODE='' */
 7 2018-07-30T09:45:17.815445+01:00        4 Query    /*!40103 SET TIME_ZONE='+00:00' */
 8 2018-07-30T09:45:17.815862+01:00        4 Query    FLUSH /*!40101 LOCAL */ TABLES
 9 2018-07-30T09:45:17.823118+01:00        4 Query    FLUSH TABLES WITH READ LOCK //Generate a FTWRL.
10 2018-07-30T09:45:17.823435+01:00        4 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
11 2018-07-30T09:45:17.823684+01:00        4 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
12 2018-07-30T09:45:17.823904+01:00        4 Query    SHOW VARIABLES LIKE 'gtid\_mode'
13 2018-07-30T09:45:17.826626+01:00        4 Query    SELECT @@GLOBAL.GTID_EXECUTED
14 2018-07-30T09:45:17.827032+01:00        4 Query    SHOW MASTER STATUS
15 2018-07-30T09:45:17.827278+01:00        4 Query    UNLOCK TABLES //Release the locks on all tables in whole instance.
16 2018-07-30T09:45:17.827480+01:00        4 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
17 2018-07-30T09:45:17.828912+01:00        4 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
18 2018-07-30T09:45:17.830942+01:00        4 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
19 2018-07-30T09:45:17.833548+01:00        4 Init DB    zlm
20 2018-07-30T09:45:17.833785+01:00        4 Query    SAVEPOINT sp
21 2018-07-30T09:45:17.833963+01:00        4 Query    show tables
22 2018-07-30T09:45:17.834252+01:00        4 Query    show table status like 'test\_notrx'
23 2018-07-30T09:45:17.834449+01:00        4 Query    SET SQL_QUOTE_SHOW_CREATE=1
24 2018-07-30T09:45:17.834582+01:00        4 Query    SET SESSION character_set_results = 'binary'
25 2018-07-30T09:45:17.834781+01:00        4 Query    show create table `test_notrx`
26 2018-07-30T09:45:17.835621+01:00        4 Query    SET SESSION character_set_results = 'utf8'
27 2018-07-30T09:45:17.835851+01:00        4 Query    show fields from `test_notrx`
28 2018-07-30T09:45:17.836362+01:00        4 Query    show fields from `test_notrx`
29 2018-07-30T09:45:17.836843+01:00        4 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_notrx`
30 2018-07-30T09:45:17.837327+01:00        4 Query    SET SESSION character_set_results = 'binary'
31 2018-07-30T09:45:17.838289+01:00        4 Query    use `zlm`
32 2018-07-30T09:45:17.838723+01:00        4 Query    select @@collation_database
33 2018-07-30T09:45:17.838908+01:00        4 Query    SHOW TRIGGERS LIKE 'test\_notrx'
34 2018-07-30T09:45:17.839457+01:00        4 Query    SET SESSION character_set_results = 'utf8'
35 2018-07-30T09:45:17.839678+01:00        4 Query    ROLLBACK TO SAVEPOINT sp
36 2018-07-30T09:45:17.839821+01:00        4 Query    show table status like 'test\_trx'
37 2018-07-30T09:45:17.840112+01:00        4 Query    SET SQL_QUOTE_SHOW_CREATE=1
38 2018-07-30T09:45:17.840248+01:00        4 Query    SET SESSION character_set_results = 'binary'
39 2018-07-30T09:45:17.840364+01:00        4 Query    show create table `test_trx`
40 2018-07-30T09:45:17.840675+01:00        4 Query    SET SESSION character_set_results = 'utf8'
41 2018-07-30T09:45:17.840828+01:00        4 Query    show fields from `test_trx`
42 2018-07-30T09:45:17.841258+01:00        4 Query    show fields from `test_trx`
43 2018-07-30T09:45:17.841774+01:00        4 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_trx`
44 2018-07-30T09:45:17.842205+01:00        4 Query    SET SESSION character_set_results = 'binary'
45 2018-07-30T09:45:17.842370+01:00        4 Query    use `zlm`
46 2018-07-30T09:45:17.842646+01:00        4 Query    select @@collation_database
47 2018-07-30T09:45:17.842814+01:00        4 Query    SHOW TRIGGERS LIKE 'test\_trx'
48 2018-07-30T09:45:17.844200+01:00        4 Query    SET SESSION character_set_results = 'utf8'
49 2018-07-30T09:45:17.844540+01:00        4 Query    ROLLBACK TO SAVEPOINT sp
50 2018-07-30T09:45:17.844803+01:00        4 Query    RELEASE SAVEPOINT sp
51 2018-07-30T09:45:17.847871+01:00        4 Quit
52 
53 //The "unlock tables" operation was executed very soon after a short period of time of getting gtid information.
54 //After the releasing lock operation,the consistency of transaction table can be guaranteed while the non-transaction table cannot be.

mysqldump -u用户名 -p密码 -h主机 数据库 表

mysqldump -u用户名 -p密码 -h主机 数据库 表

 

案例:mysqldump -uroot -p sqlhk9 a –no-data

案例:mysqldump -uroot -p sqlhk9 a –no-data

This
time merely use “–master-data=2”.

参数详解:

参数详解:

 1 [root@zlm2 09:45:17 ~]
 2 #mysqldump --master-data=2 zlm > test2.sql
 3 
 4 //General log output below.
 5 2018-07-30T10:06:41.766819+01:00        5 Connect    zlm@zlm2 on  using TCP/IP
 6 2018-07-30T10:06:41.767265+01:00        5 Query    /*!40100 SET @@SQL_MODE='' */
 7 2018-07-30T10:06:41.767495+01:00        5 Query    /*!40103 SET TIME_ZONE='+00:00' */
 8 2018-07-30T10:06:41.767852+01:00        5 Query    FLUSH /*!40101 LOCAL */ TABLES
 9 2018-07-30T10:06:41.768067+01:00        5 Query    FLUSH TABLES WITH READ LOCK //It still generate a FTWRL.
10 2018-07-30T10:06:41.768237+01:00        5 Query    SHOW VARIABLES LIKE 'gtid\_mode'
11 2018-07-30T10:06:41.770245+01:00        5 Query    SELECT @@GLOBAL.GTID_EXECUTED
12 2018-07-30T10:06:41.770659+01:00        5 Query    SHOW MASTER STATUS //No releasing lock operation here.
13 2018-07-30T10:06:41.770955+01:00        5 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
14 2018-07-30T10:06:41.773632+01:00        5 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
15 2018-07-30T10:06:41.775734+01:00        5 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
16 2018-07-30T10:06:41.777953+01:00        5 Init DB    zlm
17 2018-07-30T10:06:41.778223+01:00        5 Query    show tables
18 2018-07-30T10:06:41.778529+01:00        5 Query    show table status like 'test\_notrx'
19 2018-07-30T10:06:41.778898+01:00        5 Query    SET SQL_QUOTE_SHOW_CREATE=1
20 2018-07-30T10:06:41.779149+01:00        5 Query    SET SESSION character_set_results = 'binary'
21 2018-07-30T10:06:41.780362+01:00        5 Query    show create table `test_notrx`
22 2018-07-30T10:06:41.780722+01:00        5 Query    SET SESSION character_set_results = 'utf8'
23 2018-07-30T10:06:41.780907+01:00        5 Query    show fields from `test_notrx`
24 2018-07-30T10:06:41.781665+01:00        5 Query    show fields from `test_notrx`
25 2018-07-30T10:06:41.782447+01:00        5 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_notrx`
26 2018-07-30T10:06:41.782788+01:00        5 Query    SET SESSION character_set_results = 'binary'
27 2018-07-30T10:06:41.782945+01:00        5 Query    use `zlm`
28 2018-07-30T10:06:41.783298+01:00        5 Query    select @@collation_database
29 2018-07-30T10:06:41.783425+01:00        5 Query    SHOW TRIGGERS LIKE 'test\_notrx'
30 2018-07-30T10:06:41.784086+01:00        5 Query    SET SESSION character_set_results = 'utf8'
31 2018-07-30T10:06:41.790618+01:00        5 Query    show table status like 'test\_trx'
32 2018-07-30T10:06:41.791015+01:00        5 Query    SET SQL_QUOTE_SHOW_CREATE=1
33 2018-07-30T10:06:41.791094+01:00        5 Query    SET SESSION character_set_results = 'binary'
34 2018-07-30T10:06:41.791192+01:00        5 Query    show create table `test_trx`
35 2018-07-30T10:06:41.791285+01:00        5 Query    SET SESSION character_set_results = 'utf8'
36 2018-07-30T10:06:41.791360+01:00        5 Query    show fields from `test_trx`
37 2018-07-30T10:06:41.792328+01:00        5 Query    show fields from `test_trx`
38 2018-07-30T10:06:41.792837+01:00        5 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_trx`
39 2018-07-30T10:06:41.793036+01:00        5 Query    SET SESSION character_set_results = 'binary'
40 2018-07-30T10:06:41.793147+01:00        5 Query    use `zlm`
41 2018-07-30T10:06:41.793277+01:00        5 Query    select @@collation_database
42 2018-07-30T10:06:41.793381+01:00        5 Query    SHOW TRIGGERS LIKE 'test\_trx'
43 2018-07-30T10:06:41.794223+01:00        5 Query    SET SESSION character_set_results = 'utf8'
44 2018-07-30T10:06:41.799961+01:00        5 Quit
45 
46 //No RR isolation level and consistent snapshot this time what means the consistency of transaction table cannot be guaranteed,too.
47 //As for the non-transaction table,it will hold the FTWRL until the backup finishes.That means it's consistent in the course.
48 //The bad influence is that the locking time will be extremly long.Because option of "--master-data" will enbale "--lock-all-tables" or "-x" option.

使用mysqldump
mysqldump -u root -p your-new-password databasename [tablename] >
db.sql

使用mysqldump
mysqldump -u root -p your-new-password databasename [tablename] >
db.sql

 

比较大的表需要用优化的dump以节省内存:
mysqldump –opt database > backup-file.sql

比较大的表需要用优化的dump以节省内存:
mysqldump –opt database > backup-file.sql

This
time execute mysqldump without any option.

mysqldump工具有大量的选项,部分选项如下表:

mysqldump工具有大量的选项,部分选项如下表:

 1 [root@zlm2 10:16:20 ~]
 2 #mysqldump zlm > test3.sql
 3 
 4 //General log output below.
 5 2018-07-30T10:24:04.371650+01:00        6 Connect    zlm@zlm2 on  using TCP/IP
 6 2018-07-30T10:24:04.371651+01:00        6 Query    /*!40100 SET @@SQL_MODE='' */
 7 2018-07-30T10:24:04.371885+01:00        6 Query    /*!40103 SET TIME_ZONE='+00:00' */
 8 2018-07-30T10:24:04.372048+01:00        6 Query    SHOW VARIABLES LIKE 'gtid\_mode'
 9 2018-07-30T10:24:04.374903+01:00        6 Query    SELECT @@GLOBAL.GTID_EXECUTED
10 2018-07-30T10:24:04.376278+01:00        6 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
11 2018-07-30T10:24:04.378718+01:00        6 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
12 2018-07-30T10:24:04.380381+01:00        6 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
13 2018-07-30T10:24:04.383136+01:00        6 Init DB    zlm
14 2018-07-30T10:24:04.383512+01:00        6 Query    show tables
15 2018-07-30T10:24:04.383922+01:00        6 Query    LOCK TABLES `test_notrx` READ /*!32311 LOCAL */,`test_trx` READ /*!32311 LOCAL */ //There're two individual locks here.
16 2018-07-30T10:24:04.384267+01:00        6 Query    show table status like 'test\_notrx'
17 2018-07-30T10:24:04.384870+01:00        6 Query    SET SQL_QUOTE_SHOW_CREATE=1
18 2018-07-30T10:24:04.385115+01:00        6 Query    SET SESSION character_set_results = 'binary'
19 2018-07-30T10:24:04.385241+01:00        6 Query    show create table `test_notrx`
20 2018-07-30T10:24:04.385479+01:00        6 Query    SET SESSION character_set_results = 'utf8'
21 2018-07-30T10:24:04.385651+01:00        6 Query    show fields from `test_notrx`
22 2018-07-30T10:24:04.386440+01:00        6 Query    show fields from `test_notrx`
23 2018-07-30T10:24:04.387011+01:00        6 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_notrx`
24 2018-07-30T10:24:04.387314+01:00        6 Query    SET SESSION character_set_results = 'binary'
25 2018-07-30T10:24:04.387446+01:00        6 Query    use `zlm`
26 2018-07-30T10:24:04.387704+01:00        6 Query    select @@collation_database
27 2018-07-30T10:24:04.388009+01:00        6 Query    SHOW TRIGGERS LIKE 'test\_notrx'
28 2018-07-30T10:24:04.388492+01:00        6 Query    SET SESSION character_set_results = 'utf8'
29 2018-07-30T10:24:04.388576+01:00        6 Query    show table status like 'test\_trx'
30 2018-07-30T10:24:04.389393+01:00        6 Query    SET SQL_QUOTE_SHOW_CREATE=1
31 2018-07-30T10:24:04.389691+01:00        6 Query    SET SESSION character_set_results = 'binary'
32 2018-07-30T10:24:04.390024+01:00        6 Query    show create table `test_trx`
33 2018-07-30T10:24:04.390330+01:00        6 Query    SET SESSION character_set_results = 'utf8'
34 2018-07-30T10:24:04.392180+01:00        6 Query    show fields from `test_trx`
35 2018-07-30T10:24:04.393918+01:00        6 Query    show fields from `test_trx`
36 2018-07-30T10:24:04.394497+01:00        6 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_trx`
37 2018-07-30T10:24:04.394847+01:00        6 Query    SET SESSION character_set_results = 'binary'
38 2018-07-30T10:24:04.395108+01:00        6 Query    use `zlm`
39 2018-07-30T10:24:04.395955+01:00        6 Query    select @@collation_database
40 2018-07-30T10:24:04.396372+01:00        6 Query    SHOW TRIGGERS LIKE 'test\_trx'
41 2018-07-30T10:24:04.397040+01:00        6 Query    SET SESSION character_set_results = 'utf8'
42 2018-07-30T10:24:04.397295+01:00        6 Query    UNLOCK TABLES //The lock was released after finished backup.
43 2018-07-30T10:24:04.401336+01:00        6 Quit
44 
45 //If you don not specify any option of mysqldump,default option "--lock-tables" or "-l" will be enable.
46 //The "--lock-tables" option will add a read-only lock by executing "lock table xxx read;" individually on which table you want to backup.
47 //There's neither RR isolation leve nor consistent snapshot.Therefore,only the non-transaction table was guaranteed a consistent backup.

  选项/Option 作用/Action Performed

  选项/Option 作用/Action Performed

 

  –add-drop-table

  –add-drop-table

Summary

  这个选项将会在每一个表的前面加上DROP TABLE IF
EXISTS语句,这样可以保证导回MySQL数据库的时候不会出错,因为每次导回的时候,都会首先检查表是否存在,存在就删除

  这个选项将会在每一个表的前面加上DROP TABLE IF
EXISTS语句,这样可以保证导回MySQL数据库的时候不会出错,因为每次导回的时候,都会首先检查表是否存在,存在就删除

  • We
    cannot get a consistent backup of non-transaction table with simply
    using option of “–singel-transaction” and “–master-data”
    simultaneously.
  • If you
    merely specify the option “–master-data”.It can guarantee a
    consistent backup of non-transaction table since it will enable a
    FTWRL.That is,it will enable the option of “–lock-all-tables”(short
    option is “-x”).The global read lock on table will sustain until to
    the end of backup.
  • If you
    do not specify any option of mysqldump,the default option of
    “–lock-table”(short option is “-l”) will be enabled.That will also
    guarantee a consisten backup of non-transaction table.
  • Notice
    the above situations when backup your non-transaction tables when
    using mysqldump otherwise you’ll get an inconsistent backup.That’s
    really an awful thing.

  –add-locks

  –add-locks

 

  这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK
TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作
  
  -c or – complete_insert

  这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK
TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作
  
  -c or – complete_insert

  这个选项使得mysqldump命令给每一个产生INSERT语句加上列(field)的名字。当把数据导出导另外一个数据库时这个选项很有用。

  这个选项使得mysqldump命令给每一个产生INSERT语句加上列(field)的名字。当把数据导出导另外一个数据库时这个选项很有用。

  –delayed-insert 在INSERT命令中加入DELAY选项

  –delayed-insert 在INSERT命令中加入DELAY选项

  -F or -flush-logs
使用这个选项,在执行导出之前将会刷新MySQL服务器的log.

  -F or -flush-logs
使用这个选项,在执行导出之前将会刷新MySQL服务器的log.

  -f or -force 使用这个选项,即使有错误发生,仍然继续导出

  -f or -force 使用这个选项,即使有错误发生,仍然继续导出

  –full 这个选项把附加信息也加到CREATE TABLE的语句中

  –full 这个选项把附加信息也加到CREATE TABLE的语句中

  -l or -lock-tables 使用这个选项,导出表的时候服务器将会给表加锁。

  -l or -lock-tables 使用这个选项,导出表的时候服务器将会给表加锁。

  -t or -no-create- info

  -t or -no-create- info

  这个选项使的mysqldump命令不创建CREATE
TABLE语句,这个选项在您只需要数据而不需要DDL(数据库定义语句)时很方便。
  
  -d or -no-data 这个选项使的mysqldump命令不创建INSERT语句。

  这个选项使的mysqldump命令不创建CREATE
TABLE语句,这个选项在您只需要数据而不需要DDL(数据库定义语句)时很方便。
  
  -d or -no-data 这个选项使的mysqldump命令不创建INSERT语句。

在您只需要DDL语句时,可以使用这个选项。

在您只需要DDL语句时,可以使用这个选项。

  –opt
此选项将打开所有会提高文件导出速度和创造一个可以更快导入的文件的选项。

  –opt
此选项将打开所有会提高文件导出速度和创造一个可以更快导入的文件的选项。

  -q or -quick
这个选项使得MySQL不会把整个导出的内容读入内存再执行导出,而是在读到的时候就写入导文件中。

  -q or -quick
这个选项使得MySQL不会把整个导出的内容读入内存再执行导出,而是在读到的时候就写入导文件中。

  -T path or -tab = path
这个选项将会创建两个文件,一个文件包含DDL语句或者表创建语句,另一个文件包含数据。DDL文件被命名为table_name.sql,数据文件被命名为table_name.txt.路径名是存放这两个文件的目录。目录必须已经存在,并且命令的使用者有对文件的特权。
  
  -w “WHERE Clause” or -where = “Where clause “

  -T path or -tab = path
这个选项将会创建两个文件,一个文件包含DDL语句或者表创建语句,另一个文件包含数据。DDL文件被命名为table_name.sql,数据文件被命名为table_name.txt.路径名是存放这两个文件的目录。目录必须已经存在,并且命令的使用者有对文件的特权。
  
  -w “WHERE Clause” or -where = “Where clause “

参考国外网站

参考国外网站

NAME
       mysqldump – a database backup program

NAME
       mysqldump – a database backup program

SYNOPSIS
       mysqldump [options] [db_name [tbl_name …]]

SYNOPSIS
       mysqldump [options] [db_name [tbl_name …]]

DESCRIPTION
       The mysqldump client can be used to dump a database or a
collection of
       databases for backup or for transferring the data to another SQL
server
       (not necessarily a MySQL server). The dump contains SQL
statements to
       create the table and/or populate the table.

DESCRIPTION
       The mysqldump client can be used to dump a database or a
collection of
       databases for backup or for transferring the data to another SQL
server
       (not necessarily a MySQL server). The dump contains SQL
statements to
       create the table and/or populate the table.

       If you are doing a backup on the server, and your tables all are
MyISAM
       tables, you could consider using the mysqlhotcopy instead since
faster
       backups and faster restores can be accomplished with the latter.
See
       mysqlhotcopy(1).

       If you are doing a backup on the server, and your tables all are
MyISAM
       tables, you could consider using the mysqlhotcopy instead since
faster
       backups and faster restores can be accomplished with the latter.
See
       mysqlhotcopy(1).

       There are three general ways to invoke mysqldump:

       There are three general ways to invoke mysqldump:

       shell> mysqldump [options] db_name [tables]
       shell> mysqldump [options] –databases DB1 [DB2 DB3…]
       shell> mysqldump [options] –all-databases

       shell> mysqldump [options] db_name [tables]
       shell> mysqldump [options] –databases DB1 [DB2 DB3…]
       shell> mysqldump [options] –all-databases

       If you do not name any tables or use the –databases or
–all-databases
       option, entire databases are dumped.

       If you do not name any tables or use the –databases or
–all-databases
       option, entire databases are dumped.

       To get a list of the options your version of mysqldump
supports,
       execute mysqldump –help.

       To get a list of the options your version of mysqldump
supports,
       execute mysqldump –help.

       If you run mysqldump without the –quick or –opt option,
mysqldump
       loads the whole result set into memory before dumping the result.
This
       probably is a problem if you are dumping a big database. As of
MySQL
       4.1, –opt is enabled by default, but can be disabled with
–skip-opt.

       If you run mysqldump without the –quick or –opt option,
mysqldump
       loads the whole result set into memory before dumping the result.
This
       probably is a problem if you are dumping a big database. As of
MySQL
       4.1, –opt is enabled by default, but can be disabled with
–skip-opt.

       If you are using a recent copy of the mysqldump program to
generate a
       dump to be reloaded into a very old MySQL server, you should not
use
       the –opt or -e options.

       If you are using a recent copy of the mysqldump program to
generate a
       dump to be reloaded into a very old MySQL server, you should not
use
       the –opt or -e options.

       Before MySQL 4.1.2, out-of-range numeric values such as -inf and
inf,
       as well as NaN (not-a-number) values are dumped by mysqldump as
NULL.
       You can see this using the following sample table:

       Before MySQL 4.1.2, out-of-range numeric values such as -inf and
inf,
       as well as NaN (not-a-number) values are dumped by mysqldump as
NULL.
       You can see this using the following sample table:

       mysql> CREATE TABLE t (f DOUBLE);
       mysql> INSERT INTO t VALUES(1e+111111111111111111111);
       mysql> INSERT INTO t VALUES(-1e111111111111111111111);
       mysql> SELECT f FROM t;
       +——+
       | f    |
       +——+
       |  inf |
       | -inf |
       +——+

       mysql> CREATE TABLE t (f DOUBLE);
       mysql> INSERT INTO t VALUES(1e+111111111111111111111);
       mysql> INSERT INTO t VALUES(-1e111111111111111111111);
       mysql> SELECT f FROM t;
       +——+
       | f    |
       +——+
       |  inf |
       | -inf |
       +——+

       For this table, mysqldump produces the following data output:

       For this table, mysqldump produces the following data output:

       —
       — Dumping data for table ‘t‘
       —
       INSERT INTO t VALUES (NULL);
       INSERT INTO t VALUES (NULL);

       —
       — Dumping data for table ‘t‘
       —
       INSERT INTO t VALUES (NULL);
       INSERT INTO t VALUES (NULL);

       The significance of this behavior is that if you dump and restore
the
       table, the new table has contents that differ from the original
       contents. This problem is fixed as of MySQL 4.1.2; you cannot
insert
       inf in the table, so this mysqldump behavior is only relevant
when you
       deal with old servers.

       The significance of this behavior is that if you dump and restore
the
       table, the new table has contents that differ from the original
       contents. This problem is fixed as of MySQL 4.1.2; you cannot
insert
       inf in the table, so this mysqldump behavior is only relevant
when you
       deal with old servers.

       mysqldump supports the following options:

       mysqldump supports the following options:

       ·  –help, -?

       ·  –help, -?

          Display a help message and exit.

          Display a help message and exit.

       ·  –add-drop-database

       ·  –add-drop-database

          Add a DROP DATABASE statement before each CREATE DATABASE
statement.
          Added in MySQL 4.1.13.

          Add a DROP DATABASE statement before each CREATE DATABASE
statement.
          Added in MySQL 4.1.13.

       ·  –add-drop-table

       ·  –add-drop-table

          Add a DROP TABLE statement before each CREATE TABLE statement.

          Add a DROP TABLE statement before each CREATE TABLE statement.

       ·  –add-locks

       ·  –add-locks

          Surround each table dump with LOCK TABLES and UNLOCK TABLES
          statements. This results in faster inserts when the dump file
is
          reloaded. See Section 2.13, “Speed of INSERT Statements”.

          Surround each table dump with LOCK TABLES and UNLOCK TABLES
          statements. This results in faster inserts when the dump file
is
          reloaded. See Section 2.13, “Speed of INSERT Statements”.