mysqldump

mysqldump的常见用法
1.全库备份
mysqldump --all-databases --master-data=2 --single-transaction --triggers --routines .-events >all databases.sql

注意，--master-data=2 --single-transaction 只能保证 InnoDB 表的备份一致性，无法保证非事务表(如MyISAM)的备份一致性。如果要保证后者的备份一致性，只能指定 --lock-all-tables 加全局读锁。但全局读锁的影响较大，一般不建议使用。这也从另一个角度说明了为什么要在线上禁用非事务表。

2.备份指定库
mysqldump --databases db1 db2 --result-file=db1 db2.sql

在备份指定库时，默认会开启 --opt 选项。这样，在备份每个数据库时，会对该数据库下的所有表执行锁表操作。
LOCK TABLES t1’READ /*!32311 LOCAL */,t2’ READ /*!32311 LOCAL */

3.压缩备份
mysqldump --all-databases --master-data=2 --single-transaction  gzip > all databases.gz

通过管道的方式使用 gzip 进行压缩，除了 gzip，也可指定其他压缩算法。

4.备份指定表
备份指定表的写法比较灵活，如对 db1 中的 t1和 2 表进行备份，常用的备份方式有:
（1）mysqldump --databases db1 --tables t1 t2 > mysql_backup.sql
mysqldump db1 t1 t2 > mysql_backup.sql
mysqldump db1 --tables t1 t2 > mysql_backup.sql

在备份指定表时，要注意以下两点。
（1）不支持 db_name.tbl_name 这种写法。例如:
mysqldump --tables db1.t1> mysql_backup.sql
mysqldump: Got error: 1049: Unknown database 'db1.t1' when selecting the database

（3）无法对不同数据库中的多张表同时进行备份，如 db1.t1 和 db2.t3。

5.忽略指定表
mysqldump --databases db1 db2 --ignore-table=db1.t1 --ignore-table=db2.t3 > mysql backup.sql

注意，--ignore-table 后面只能接一张表，如果要忽略多张表，需指定多次。之前提到过，使用--tables 无法对不同数据库中的多张表进行备份，此时可通过 -ignore-table 间接实现，但总的来说，还是比较麻烦。对于类似需求，更推荐使用 mydumper或MySQL Shell Dump 来实现。

6.导出表的部分数据
mysqldump db1 t1 --where='id=1'> db1_t1.sql

id=1 是过滤条件，对应的导出语句为
SELECT /*!40001 SQL_NO_CACHE*/ * FROMt1WHERE id=1

7.备份文件
mysql < mysql_backup.sql

通过nysqldump生成的备份数据一般是通过mysql客户端来恢复的。除了上述命令,也可通过 source命令来恢复。
mysql> source mysql_backup.sql

8.导入压缩文件
gunzip < all_databases.gz l mysql

导人之前，先通过 gunzip 进行解压

9.边备份，边导入
mysgldump --databases db1 l mysql -h 192.168.244.128 -uroot -p123456
通过管道，可以实现边备份、边导人的效果。

 

 

mysqldump常用参数
1.过滤相关
-A，--all-databases
备份所有数据库。默认不会备份 information_schema、performance_schema 和 sys库。-B，--databases

-B，--databases
备份指定库。如果要备份多个库，库之间需用空格隔开。

--tables
备份指定表。

--ignore-table=db_name.tbl_name
忽略指定表的备份。

-E，--events
备份定时器。使用该选项，会通过 SHOW EVENTS 命令生成 CREATE EVENT 语句。

-R， --routines
备份存储过程和自定义函数。

--triggers
备份触发器，默认开启。可通过 --skip-triggers 将其关闭。

--no-data
只备份表结构，不备份数据。

-W， --where=name
只导出满足 WHERE条件的数据。因为 mysqldump的底层实现是 SELECT * FROM TABLE NAME，所以可通过 WHERE子句设置过滤条件。

2.DDL相关
--add-drop-database
在 CREATE DATABASE 操作之前，添加 DROP DATABASE 操作。默认不会添加。

--add-drop-table
在CREATE TABLE操作之前，添加 DROP TABLE操作。该选项是默认开启的。指定 --skip-add-drop-table则不会添加。

-n，--no-create-db
在执行全库备份( --all-databases )或部分库备份(--databases )时,默认会添加 CREATE DATABASE操作，而使用该选项则不会添加。

-t，--no-create-info
在导出数据之前，默认都会添加 CREATE TABLE操作，而使用该选项则不会添加。

--replace
使用REPLACE INTO代替 INSERT INTO，例如:REPLACE INTOt1 VALUES (1,'db1.t1'),(2,'db1.t1');

--insert-ignore
使用INSERT IGNORE代替 INSERT INTO。

3.复制相关
--master-data[=value]
执行 SHOW MASTER STATUS，获取一致性备份时的 binlog 位置点信息。获取到的位置点信息会通CHANGE MASTER TO语句写入备份文件。
CHANGE MASTER TO MASTER_LOG_FILE'mysql-bin.000002'，MASTER_LOG_POS=156;
这个位置点可用来搭建从库，以上是将 --master-data 设置为 1的效果
若将 --master-data 设置为 2，则会在该语句前加上注释。例如: 
-- CHANGE MASTER TO MASTER LOG FILE='mysgl-bin.000002，MASTERLOG_POS=156;
为了避免在导人备份文件时，CHANGE MASTER TO语句对目标实例造成不必要的影响，一般建议将-master-data 设置为 2。
注意,如果只指定 --master-data,而没有指定 --single-transaction，则会隐式开启 --Lock-all-tables。
从MySQL 8.0.26开始，建议使用 --source-data，而不是 --master-data，否则备份文件中会有warning，导人时会报错。
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead

--dump-slave[=value]
同--master-data 的作用类似，只不过这里获取的位置点是 SHOW SLAVE STATUS 中的 Relay_Master.Log_File 和 Exec_MasterLog_Pos，在对从库进行备份时可指定该选项。该选项会在备份开始前关闭SQL线程，直到备份结束才开启。
从MySQL 8.0.26开始， -dump-slave 被弃用，取而代之的是 --dump-replica

--include-master-host-port
在使用 --dump-slave 时，生成的 CHANGE MASTER TO语中只有 MASTER_LOG_FILE 和 MASTER_LOG_POS如果指定了 --include-master-host-port，则会同时加上对应主库的IP 和端口。
CHANGE MASTER TO MASTER_HOST='192.168.244.10',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000005'MASTER LOG POS=493;
从 MySQL 8.0.26 开始，--include-master-host-port 被弃用，取而代之的是 --include-sourcehost-port。

--set-gtid-purged=value
指定是否在备份文件中添加 SET @QGLOBAL.GTID_PURGED 操作，可选值有 ON、COMMENTED、OFFAUTO。默认为 AUTO，即目标实例如果开启了 GTID，则会添加，反之则不会添加。若设置为 COMMENTED，则该操作会以注释的形式添加。
注意，在 SET @@GLOBAL.GTID_PURGED 操作之前，会将 SQL LOG BIN 设置为 0。
SET @MYSQLDUMP_TEMP_LOG_BIN = @SESSION.SQL_LOG BIN;SET @SESSION.SQL_LOG_BIN= 0;
-- GTID state at the beginning of the backup
SET @GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'bd6b3216-4d6-11ec-b76f-000c292c1f7b:1-3';

4.事务相关
--single-transaction
在备份开始前，会执行以下操作，获取事务表的一致性快照，从而保证事务表在整个备份期间的一致性。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
注意，该选项只适用于事务表。对于非事务表(如 MyISAM)，则无法保证一致性。

-x,--lock-all-tables
直到备份结束才释放。如果数据库中的非事务表比较多，又要保证备在备份开始前加全局读锁，份的一致性，就可使用该选项。但在加锁期间，只能读、不能写，这一点尤其需要注意。

-l,--lock-tables
在备份每个数据库前，会通过以下操作锁定该数据库下的所有表，这样就能保证这个数据库备份的一致性。
LOCK TABLES*t1’READ /*!32311 LOCAL */，t2’ READ /*!32311 LOCAL */

在使用此选项时需注意以下两点
（1）使用 --lock-tables 备份数据库，只能保证每个数据库的一致性，不能保证整个实例的备份的一致性。
（2）--lock-tables 和 --single-transaction 是互斥的，因为 LOCK TABLES 操作会导致事务隐式提交。同时指定两者不会报错，此时--single-transaction 会覆盖 --lock-tables。

--add-locks
在INSERT操作之前添加锁表操作。
LOCK TABLES t1 WRITE;
/*!40000 ALTER TABLE t1DISABLE KEYS */;
INSERT INTO t1(id,name') VALUES (1,'db1.t1'),(2,'db1.t1');
/*!40000 ALTER TABLEt1”ENABLE KEYS */;
UNLOCK TABLES;

5.性能相关
--opt该选项等同于 -add-drop-table、 --add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-charset、--disable-keys。默认开启。

-q,--quick
如果指定了 --quick,则会通过 mysql_use_result()获取结果集，反之则通过 mysql_store_result()获取。
if (quick)
  res = mysql_use_result(mysql);
else
res = mysql_store_result(mysql);

两者的区别是,mysql_store_result() 会一次性读取结果集，然后缓存在客户端中，而 mysql_useresult()返回的只是一个游标，后续需通过 mysql_fetch_row() 逐行读取数据。因为整个结果集需要在客户端中维护，所以相对来说，mysql_store_result() 比 mysql_use_result() 需要更多的内存和更大的开销。如果结果集很大，客户端还存在内存溢出的风险。mysql_use_result()每次只需为一行数据分配内存，而且不用为整个结果集设置复杂的数据结构，所以相对来说速度更快。

-K，--disable-keys
数据在导人前，先禁用索引，等导入后，再开启索引
/*!40000 ALTER TABLE t1’ DISABLE KEYS */;
INSERT INTO"t1’ VALUES (1,'db1.t1'),(2,'db1.t1');
/*!40000 ALTER TABLE“t1’ ENABLE KEYS */;

这样，可提升数据的导人速度。只不过这两个命令只适用于 MyISAM 存储引擎。

-e，--extended-insert
将多行数据放到一个INSERT 操作中。
INSERT INTO 't1’VALUES (1,'db1.t1'),(2,'db1.t1');
如果禁用该选项，则一个INSERT 操作只有一行数据

6.其他重要选项
--flush-privileges
在备份完 mysql库后，会添加 FLUSH PRIVILEGES 操作。默认不会添加。

--set-charset
将SET NAMES character_set 操作添加到输出中。指定 -skip-set-charset 则不会添加。这里的字符集由 --default-character-set 指定。在 MySQL8.0中，default-character-set 默认为 utf8mb4.

-c，--complete-insert
在INSERT操作中指定列名
INSERT INTO 't1’ (id, "name") VALUES (1,db1,t1'),(2,db1.t1');

-r file_name, --result-file=file_name
指定备份文件。