下面是某业务库的迁移过程, 从Source Master/Slave集群迁移至Target MHA集群.
前期准备, 配置数据从Source Master复制到Target Master, 即Target Master成为Source Master的Slave(若Target Master是新库的话, 还可添加replicate-do-db=name过滤条件).
迁移开始时, 在Source Master上执行flush no_write_to_binlog tables, 和flush tables with read lock.
在Target Master上查看延时为0时, 执行flush binary logs. 然后stop slave, 和reset slave all, 清除Source Master至Target Master的复制关系.
再后, 依次重启各应用节点, 切换至Target Master新数据源.
最后在Source Master上unlock tables, 至此迁移结束.
该过程中的主角flush tables with read lock, 其含义为Closes all open tables and locks all tables for all databases with a global read lock, 即在MySQL数据库Server层, 获取一个全局读锁.
那么配角flush no_write_to_binlog tables, 其含义为Closes all open tables, forces all tables in use to be closed, 起到加速flush tables with read lock操作完成的作用.
在数据库管理的过程中, 经常会见到flush tables with read lock的身影.
如mysqldump备份时, 通过general log会看到如下输出.
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 TABLES
25 Query FLUSH TABLES WITH READ LOCK
25 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
25 Query START TRANSACTION
25 Query SHOW VARIABLES LIKE 'gtid\_mode'
25 Query SELECT @@GLOBAL.GTID_EXECUTED
25 Query SHOW MASTER STATUS
25 Query UNLOCK TABLES
又如xtrabackup备份时, 也可见类似日志.
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在线手动切换时, 也用到了flush tables with read lock, 详见如下输出.
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.
