百木园mysqldump 与 --set-gtid-purged 设置
(1) mysqldump
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 5.7.31) PROCESS if the --no-tablespaces option is not used.
mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. You can clone databases for development and DBA work, or produce slight variations of an existing database for testing. It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on. For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly.
(2) --set-gtid-purged=value
This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@GLOBAL.gtid_purged statement to the output. This option may also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded. The following table shows the permitted option values.
The default value is AUTO.
| Value | Meaning |
| OFF | Add no SET statement to the output. |
| ON | Add a SET statement to the output. An error occurs if GTIDs are not enabled on the server. |
| AUTO | Add a SET statement to the output if GTIDs are enabled on the server. |
A partial dump from a server that is using GTID-based replication requires the --set-gtidpurged={ON|OFF} option to be specified. Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology. Use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.
(3) --set-gtid-purged 与 导出文件中SET @@SESSION.SQL_LOG_BIN=0
The --set-gtid-purged option has the following effect on binary logging when the dump file is reloaded:
• --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.
• --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.
• --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you are backing up (that is, if AUTO evaluates to ON).
(4) 举例说明
在开启GTID模式的实例上执行mysqldump，假如导出命令如下:
/usr/local/mysql/bin/mysqldump --master-data=2 -u账……号 -p密……码 --databases 数据库1 数据库2 --single-transaction -R --triggers > /data/dbdump/db1_db2_dump.sql
来源：https://www.cnblogs.com/xuliuzai/p/15426872.html
图文来源于网络，如有侵权请联系删除。