A case of non-disruptive service database migration

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