MySQL dual master (master-master) supplement

Environment:

Host name	Host IP	Operating System	MySQL version
Mysqla	172.16.4.235	CentOS 6.5 2.6.32-431.el6.x86_64	Mysql-community-server-5.7.5-0.6.m15.el6.x86_64
Mysqlb	172.16.4.236	CentOS 6.5 2.6.32-431.el6.x86_64	Mysql-community-server-5.7.5-0.6.m15.el6.x86_64

MySQL dual master + semi-synchronous architecture reference previous post: http://qicheng0211.blog.51cto.com/3958621/1596131

This article adds: If the MYSQLA has been running for a while, to add a new mysqlb, with Mysqla to implement primary master replication, you must first back up the data on the Mysqla import to mysqlb, so that the data remains consistent Record the Binlog file name and position on Mysqla, perform change Mater on mysqlb, record mysqlb file name and binlog on position, and perform change mysqla on Mater.

Here are two ways to implement this:

Method One,

1) Mysqla Database lock table operation, do not allow the data to write again action

> Flush tables with read lock;

Do not exit the connection and open a different terminal to perform the backup operation.

2) backing up the Mysqla database

Mysqldump-uroot-p--single-transaction--opt--events--triggers--routines--flush-logs--master-data=2--databases db 1 DB2 > Db.sql

--flush-logs: Option to generate a new binary log file.

--master-data: The option value equals 2, the change master statement in the backed-up SQL file is commented.

--single-transaction: InnoDB table when backing up, the option--single-transaction is usually enabled to guarantee the consistency of the backup, In fact, it works by setting the isolation level for this session: REPEATABLE read to ensure that this session (dump) does not see data that has been submitted by other sessions.

3) Look at the status of the Mysqla database, and then unlock the values of the file and position entries.

> Show master status;> unlock tables;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/8D/wKiom1SdJUrBA7fOAAJle2urOEM591.jpg "title=" 1.png " alt= "Wkiom1sdjurba7foaajle2uroem591.jpg"/>

The binary log file shown is mysql-bin.000002 with a location of 120.

4) Import Mysqla data on MYSQLB

Mysql-uroot-p ' Mnvq_yog9 ' < Db.sql

5) Generate the Change master statement and execute it on the MYSQLB

> STOP SLAVE; > Change MASTER to master_host= ' 172.16.4.235 ', master_user= ' repl_user ', master_password= ' mnvq_yog9 ', Master_log_ File= ' mysql-bin.000002 ', master_log_pos=120;> START slave;> SHOW SLAVE status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/8A/wKioL1SdKKrCngloAATkjRsqgnM327.jpg "title=" 2.png " alt= "Wkiol1sdkkrcngloaatkjrsqgnm327.jpg"/>

The status of Slave_io_running and Slave_sql_running, if both are Yes, is configured successfully from the library mysqlb.

6) record the values of the mysqlb file and position entries, generate the Change master statement, and then execute on the Mysqla, starting Mysqla slave

Slightly.

Method Two,

1) Backup A's database (no lock table)

Mysqldump-uroot-p--single-transaction--opt--events--triggers--routines--flush-logs--master-data=2--databases db 1 DB2 > Db.sql

2) Use the grep command to find the name and location of the binary log

Grep-i "Change Master" Db.sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/8B/wKioL1SdK2ySy6FcAADRLSJaW1Y087.jpg "title=" 3.png " alt= "Wkiol1sdk2ysy6fcaadrlsjaw1y087.jpg"/>

The binary log file shown is mysql-bin.000002 with a location of 120.

Then execute method one of 4), 5), 6).

This article is from the "Start Linux blog" blog, reproduced please contact the author!

MySQL dual master (master-master) supplement