MySQL 學習筆記（五）

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.

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=https://www.cnblogs.com/xuliuzai/archive/2021/10/20/2 -u賬……號 -p密……碼 --databases 資料庫1 資料庫2 --single-transaction -R --triggers > /data/dbdump/db1_db2_dump.sql

執行，我們會收到一個Warning,如下：

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.

命令執行后，查看匯出的檔案，會在檔案的開頭看到以下命令:

...................................................................................................，，，，，，，，，，，，，，，
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=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='66fe6059-18c7-22e6-1d21-000c27cswbda:1908761-14';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='1ogbin-003413', MASTER_LOG_POS=999;

--
...................................................................................................，，，，，，，，，，，，，，，

可以看出，在開啟GITD的server上，執行mysqldump,將--set-gtid-purged 設定為on 或者不設定（The default value is AUTO.）時，產生的sql檔案，會顯式指明（匯入時會執行）GLOBAL.GTID_PURGED 和將當前session 設定為SQL_LOG_BIN= 0，

(5)  場景延申

假設：當前有一個mysql 集群123，一主一從，主為serverA1，從為Server B1，現在又新搭建了一個集群321， 也是一主一從（注意已搭建主從復制）一主一從，主為serverA2，從為Server B2.

目前需求是將集群123 中的部分資料庫 --資料庫2、資料庫4----同步到新集群321 中，并建立主從，

 示意圖如下:

注意四臺機器都已開啟GTID模式，

我們使用mysqldump來完成這個作業會遇到什么挑戰呢？

首先是匯出，匯出就是上面的命令，沒有帶--set-gtid-purged，接著時修改ServerA2實體的my.cnf組態檔，指定復制的庫，然后，將dump檔案copy到新實體serverA2上，

現在我們主要看看匯入時遇到的問題，

（1）匯入命令

mysql -u用……戶……名 -p密……碼 < /data/dumprestore/db1_db2_dump.sql　　

 收到報錯資訊

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解決方案,執行以下命令

reset master;

(2) 如果執行了 reset master，Server A2 和 Server B2的主從關系就會壞掉，

(3) 如果在匯入前，主從都是沒有業務資料庫的新集群，主從修復，可以都執行了 reset master，重新搭建主從，

change master to
master_host='IP地址',
master_port=埠號,
master_user='用 ……戶 ……名',
master_password='密……碼',
master_auto_position=1;

因為是剛剛reset master ,匯入時不會遇到ERROR 1840 ...,，可以將資料灌入到Server A2中，

但是，這個時候你會發現 Server A2 和 Server B2的主從已不能同步新匯入的資料，

因為你執行匯入的session,執行設定了SET @@SESSION.SQL_LOG_BIN= 0;

(4)執行時間過長

建議 nohup,后臺執行，

mysql匯入資料耗時遠遠大于mysqldump匯出耗時，測驗中，我們將大小都是60G的兩個資料庫備份還原，mysqldump 執行55分鐘，mysql匯入執行了5個小時，

(5)灌入資料后，可以通過xtraback備份還原來修復重建ServerA2 和 ServerB2的主從，

(6) 如果匯入資料時不想破壞掉ServerA2 和 ServerB2的主從關系，可以考慮，匯入前先注釋掉SET @@SESSION.SQL_LOG_BIN= 0;，再匯入，

(7) 另外，如果場景更復雜，例如新集群321 需要承接、同步來自多個集群（不僅僅是來自集群123）的資料，也可以考慮 通過傳統模式搭建主從（指定binlog檔案+位點），這個場景下，mysqldump 時，

--set-gtid-purged 設定為off，或者匯出后，考慮將SET @@SESSION.SQL_LOG_BIN= 0;和SET @@GLOBAL.GTID_PURGED='？？？？？？？？？？？？？？';注釋掉，

查看位點的命令:

head -n 100 dump檔案.sql | grep 'CHANGE MASTER TO'

標籤：其他

上一篇：Sqoop

下一篇：加法后獲取最大可能值