數(shù)據(jù)庫(kù)的模式中我開(kāi)啟了gtid：

mysql> show variables like '%gtid%';

+----------------------------------+-----------+

| Variable_name | Value |

+----------------------------------+-----------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

+----------------------------------+-----------+

我現(xiàn)在數(shù)據(jù)庫(kù)中有一world的庫(kù)，并且在庫(kù)中有一個(gè)country表，現(xiàn)在進(jìn)行備份時(shí)會(huì)提示如下警告：

[root@smiletest data]# mysqldump -uroot -p -R -e --triggers --master-data=2 --single-transaction world country >/tmp/countryno.sql

Enter password:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

我們來(lái)對(duì)比下加了 --set-gtid-purged=OFF和不加的區(qū)別

countryno.sql是沒(méi)有加--set-gtid-purged=OFF

[root@smiletest data]# mysqldump -uroot -p -R -e --triggers --master-data=2 --single-transaction world country >/tmp/countryno.sql

countryyes.sql是加--set-gtid-purged=OFF

[root@smiletest data]# mysqldump -uroot -p -R -e --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF world country >/tmp/countryyes.sql

Enter password:

沒(méi)有加--set-gtid-purged=OFF的里面會(huì)多幾條語(yǔ)句

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='e024c334-8b64-11e9-80dc-fa163e4bfc29:1-761734';

現(xiàn)在我們進(jìn)行導(dǎo)入剛沒(méi)有加--set-gtid-purged=OFF備份的/tmp/countryno.sql語(yǔ)句

mysql> show master status;

+------------------+----------+--------------+------------------+-----------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-----------------------------------------------+

| mysql-bin.000013 | 85019 | | | e024c334-8b64-11e9-80dc-fa163e4bfc29:1-761735 |

+------------------+----------+--------------+------------------+-----------------------------------------------+

1 row in set (0.00 sec)

mysql> source /tmp/countryno.sql

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-----------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-----------------------------------------------+

| mysql-bin.000013 | 85019 | | | e024c334-8b64-11e9-80dc-fa163e4bfc29:1-761735 |

+------------------+----------+--------------+------------------+-----------------------------------------------+

1 row in set (0.00 sec)

結(jié)論發(fā)現(xiàn)，gtid事務(wù)和 Position都沒(méi)有增加

現(xiàn)在我們進(jìn)行導(dǎo)入剛加--set-gtid-purged=OFF備份的/tmp/countryyes.sql語(yǔ)句

mysql> drop table country;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-----------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-----------------------------------------------+

| mysql-bin.000013 | 112669 | | | e024c334-8b64-11e9-80dc-fa163e4bfc29:1-761742 |

+------------------+----------+--------------+------------------+-----------------------------------------------+

1 row in set (0.00 sec)

mysql> source /tmp/countryyes.sql

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-----------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-----------------------------------------------+

| mysql-bin.000013 | 139929 | | | e024c334-8b64-11e9-80dc-fa163e4bfc29:1-761747 |

+------------------+----------+--------------+------------------+-----------------------------------------------+

1 row in set (0.00 sec)

mysql>

結(jié)論發(fā)現(xiàn)，gtid事務(wù)和 Position都增加了

結(jié)論

加了--set-gtid-purged=OFF時(shí)，在會(huì)記錄binlog日志，如果不加，不記錄binlog日志，所以在我們做主從用了gtid時(shí)，用mysqldump備份時(shí)就要加--set-gtid-purged=OFF，否則你在主上導(dǎo)入恢復(fù)了數(shù)據(jù)，主沒(méi)有了binlog日志，同步則不會(huì)被同步。

總結(jié)

以上是生活随笔為你收集整理的mysql开启gtid dump_mysqldump关于--set-gtid-purged=OFF的使用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。