The following is a migration process for a business library, migrating from the source Master/slave cluster to the target MHA cluster.
Prior to preparation, the configuration data is copied from source master to target master, i.e. target master becomes the source master slave (if Target master is a new library, you can add replicate-do-db =name filter conditions).
When the migration begins, the flush no_write_to_binlog tables is executed on the source master, and the flush tables with read lock.
On target master, view the delay of 0 o'clock and execute flush binary logs. Then stop slave, and reset slave All, clears the copy relationship from source master to target master.
After that, restart each application node and switch to the target master new data source.
Finally, unlock tables on source master, and this is the end of the migration.
The protagonist of the process is flush tables with read lock, meaning closes all open tables and locks all tables for all databases with a global read L Ock, which is the MySQL database server layer, gets a global read lock.
So the supporting Role flush no_write_to_binlog tables, which means closes all open tables, forces all tables on use to be closed, played to accelerate flush tables W The ith read lock operation completes the function.
In the database management process, often meet with the flush tables with read lock figure.
If you are backing up mysqldump, you will see the following output through the 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
Query FLUSH/*!40101 LOCAL */TABLES
Query FLUSH TABLES with READ LOCK
Query SET SESSION TRANSACTION isolation level repeatable READ
Query START TRANSACTION/*!40100 with consistent SNAPSHOT */
Query SHOW VARIABLES like ' Gtid\_mode '
Query SELECT @ @GLOBAL. gtid_executed
Query SHOW MASTER STATUS
Query UNLOCK TABLES
Similar logs are also visible when Xtrabackup backup.
Xtrabackup--defaults-file=/etc/my.cnf--user=abc--password=xyz--socket=/3306/mysql.sock--target-dir=./pxb_full- -backup
lock_wait_timeout=31536000 QuerySET SESSION
QueryFLUSH no_write_to_binlog TABLES
TABLES QueryFLUSH with READ LOCK
QuerySHOW MASTER STATUS
VARIABLES QuerySHOW
QueryFLUSH no_write_to_binlog ENGINE LOGS
TABLES QueryUNLOCK
The flush tables with read lock is also used for MHA online manual switching, as described in the output below.
Masterha_master_switch--conf=/etc/masterha/app2/app2.cnf--master_state=alive--orig_master_is_new_slave
...
It is better-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 21:58:41 2018-[INFO] executing FLUSH no_write_to_binlog TABLES. This could take a long time.
Wed 21:58:41 2018-[info] OK.
...
Wed 21:58:43 2018-[INFO] Locking all tables on the orig master to reject updates from everybody (including root):
Wed Feb 21:58:43 2018-[INFO] executing FLUSH TABLES with READ LOCK.
Wed 21:58:43 2018-[info] OK.
A case of non-disruptive service database migration