A case of non-disruptive service database migration

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.