Shulou(Shulou.com)06/01 Report--

The following is the migration process of a service library from the Source Master/Slave cluster to the Target MHA cluster.

In the early preparation, the configuration data is copied from the Source Master to the Target Master, that is, the Target Master becomes the Slave of the Source Master (if the Target Master is a new library, you can also add replicate-do-db=name filter condition).

At the start of migration, flush no_write_to_binlog tables, and flush tables with read lock on Source Master.

When the delay is 0 on the Target Master, flush binary logs. Then stop slave, and reset slave all, clearing the copy relationship from Source Master to Target Master.

Then, restart each application node in turn and switch to the new data source Target Master.

Finally, unlock tables on the Source Master, and the migration ends.

Flushing tables with read lock means Closes all open tables and locks all tables for all databases with a global read lock.

Then the supporting role flush no_write_to_binlog tables, meaning Closes all open tables, forces all tables in use to be closed, plays the role of accelerating the completion of flush tables with read lock operations.

In the process of database management, we often see flush tables with read lock.

For example, when mysqldump backup, you will see the following output through 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 /*! 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

Another example is xtrabackup backup, also visible similar logs.

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 online manual switching, also used flush tables with read lock, see the following output.

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.