Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about how to supplement MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

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 MySQL For the construction of dual-master + semi-synchronous architecture, refer to the previous blog post: https://blog.51cto.com/12947626/2125165 this article makes a supplementary note: if mysqlA has been running for some time To add a new mysqlB and achieve master replication with mysqlA, it is necessary to 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.

After reading the above, do you have any further understanding of how to carry out the two-master supplement of MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.