下面是某業(yè)務(wù)庫(kù)的遷移過(guò)程, 從Source Master/Slave集群遷移至Target MHA集群.

成都創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站制作、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶(hù)于互聯(lián)網(wǎng)時(shí)代的鄯善網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

前期準(zhǔn)備, 配置數(shù)據(jù)從Source Master復(fù)制到Target Master, 即Target Master成為Source Master的Slave(若Target Master是新庫(kù)的話, 還可添加replicate-do-db=name過(guò)濾條件).

遷移開(kāi)始時(shí), 在Source Master上執(zhí)行flush no_write_to_binlog tables, 和flush tables with read lock.

在Target Master上查看延時(shí)為0時(shí), 執(zhí)行flush binary logs. 然后stop slave, 和reset slave all, 清除Source Master至Target Master的復(fù)制關(guān)系. 

再后, 依次重啟各應(yīng)用節(jié)點(diǎn), 切換至Target Master新數(shù)據(jù)源.

最后在Source Master上unlock tables, 至此遷移結(jié)束.

該過(guò)程中的主角flush tables with read lock, 其含義為Closes all open tables and locks all tables for all databases with a global read lock, 即在MySQL數(shù)據(jù)庫(kù)Server層, 獲取一個(gè)全局讀鎖.

那么配角flush no_write_to_binlog tables, 其含義為Closes all open tables, forces all tables in use to be closed, 起到加速flush tables with read lock操作完成的作用.

在數(shù)據(jù)庫(kù)管理的過(guò)程中, 經(jīng)常會(huì)見(jiàn)到flush tables with read lock的身影.

如mysqldump備份時(shí), 通過(guò)general log會(huì)看到如下輸出.

mysqldump --login-path=mytest --default-character-set=utf8mb4 --single-transaction --routines --triggers --events --quick --force --master-data=2 --hex-blob --all-databases > full.sql

25 Query FLUSH /*!40101 LOCAL */ TABLES

25 Query FLUSH TABLES WITH READ LOCK

25 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

25 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

25 Query SHOW VARIABLES LIKE 'gtid\_mode'

25 Query SELECT @@GLOBAL.GTID_EXECUTED

25 Query SHOW MASTER STATUS

25 Query UNLOCK TABLES

又如xtrabackup備份時(shí), 也可見(jiàn)類(lèi)似日志.

xtrabackup --defaults-file=/etc/my.cnf --user=abc --password=xyz --socket=/3306/mysql.sock --target-dir=./pxb_full --backup

34 QuerySET SESSION lock_wait_timeout=31536000

34 QueryFLUSH NO_WRITE_TO_BINLOG TABLES

34 QueryFLUSH TABLES WITH READ LOCK

34 QuerySHOW MASTER STATUS

34 QuerySHOW VARIABLES

34 QueryFLUSH NO_WRITE_TO_BINLOG ENGINE LOGS

34 QueryUNLOCK TABLES

MHA在線手動(dòng)切換時(shí), 也用到了flush tables with read lock, 詳見(jiàn)如下輸出.

masterha_master_switch --conf=/etc/masterha/app2/app2.cnf --master_state=alive --orig_master_is_new_slave

...

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.4.33(192.168.4.33:3307)? (YES/no): yes

Wed Feb 28 21:58:41 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..

Wed Feb 28 21:58:41 2018 - [info]  ok.

...

Wed Feb 28 21:58:43 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):

Wed Feb 28 21:58:43 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..

Wed Feb 28 21:58:43 2018 - [info]  ok.