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

This article mainly gives you a brief introduction to the detailed method of MySQL dual-master master-master configuration. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the detailed method of MySQL dual-master master-master configuration can bring you some practical help.

Environment:

Hostname Host IP operating system

MysqlA 172.18.252.221 CentOS 6.5 2.6.32-431.el6.x86_64mysqlB 172.18.252.222

MySQL version mysql-community-server-5.7.5-0.6.m15.el6.x86_64

This article makes a supplementary note: if mysqlA has been running for some time, if you want to add a new mysqlB to achieve master and master replication with mysqlA, you must first copy the data on mysqlA and import it into mysqlB to keep the data consistent. Record the file name and Position of binlog on mysqlA, execute CHANGE MASTER TO on mysqlB, record the file name and Position of binlog on mysqlB, and execute CHANGE MASTER on mysqlA. (the data imported to B should be full backup of mysqlA + binlog) there are two implementation methods: method 1, 1) lock the table operation on the mysqlA database so that the data is no longer written > flush tables with read lock Do not quit the connection, open another terminal to perform backup operation. 2) backup mysqlA database mysqldump-- uroot-p-- single-transaction-- opt-- events-- triggers-- routines-- flush-logs-- master-data=2-- databases db1 db2 > db.sql-- flush-logs: option to generate a new binary log file (scroll the binary file so that its starting position is complete)-- mater-data: the option value is equal to 2 The CHANGE MASTER statement is commented in the backed-up sql file. -- single-transaction: when backing up the InnoDB table, the option-- single-transaction is usually enabled to ensure the consistency of the backup. In fact, it works by setting the isolation level of this session to: REPEATABLE READ, to ensure that when this session (mysqldump), you will not see the data submitted by other sessions. 3) look at the status of mysqlA database, record the values of File and Position items and unlock them; show master status; unlock tables;4) Import mysqlA data on mysqlB: > mysql-uroot-p 'repl'

STOP SLAVE; > CHANGE MASTER TO MASTER_HOST='172.18.252.221', MASTER_USER='repl_user',MASTER_PASSWORD='repl', MASTER_LOG_FIEL='mysql-bin.000002', MASTER_LOG_POS='120'; > START SLAVE; > SHOW SLAVE STATUS\ G Slave_IO_Running and Slave_SQL_Running status. If both are Yes, slave mysqlB is configured successfully. 6) record the values of the binlogFile and Position items of mysqlB, generate the CHANGE MASTER statement, then perform the above operation on mysqlA (first stop slave;...), and finally start slave.

Method 2.

1) back up A's database (do not lock the table)

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

2) use grep-I "change master" db.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120

OK, the binary log file shown in the result is mysql-bin.000002, and the location is 120.

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

The detailed method of MySQL dual-master master-master configuration will stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.