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

First, the realization principle

Master master replication means that data can be changed in both MySQL hosts, and the other host will change accordingly. Smart, you may have figured out how to make it happen. Yes, it would be nice to merge the two master-slave replicas organically. It's just that we need to pay attention to some problems when configuring, for example, duplicate primary keys, server-id can not be repeated, and so on.

Master master replication-> mutual master slave

Second, realize the function

The two MySQL are not only each other's slave library, but also the master library. This scheme not only achieves the pressure diversion of traffic, but also solves the problem of "single point of failure". If there is any failure, there is another set of services available.

III. Architectural model

IV. Configuration file

-- 192.168.18.5 [mysqld] server-id=1 # any natural number n, as long as the two MySQL hosts are not duplicated. Log-bin=mysql-bin # enables binary log log_bin_index = mysql-bin.index relay_log = mysql-relay-bin relay_log_index = mysql-relay-bin.index read_only = 1 skip_slave_start = 1 auto_increment_increment=2 # step value auto_imcrement. Generally, if there are n master MySQL, enter the starting value of n auto_increment_offset=1 #. Generally fill in the nth main MySQL. This is the first master MySQL binlog-ignore=mysql # ignore mysql library binlog-ignore=information_schema # ignore information_ schema library slave-skip-errors = all-- 192.168.18.6 [mysqld] server-id = 2 log_bin = mysql-bin log_bin_index = mysql-bin.index Relay_log = mysql-relay-bin relay_log_index = mysql-relay-bin.index read_only = 1 skip_slave_start = 1 log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 2 # This is different from the 18.5 configuration slave-skip-errors = all

Restart MySQL after configuration

/ etc/my.cnf configuration of two databases

V. Mutual authorization of access

Execute authorization root remote 18.6MySQL [(none)] > GRANT REPLICATION SLAVE ON *. * TO 'root'@'192.168.18.6' IDENTIFIED BY' 1qaz2wsx authorization on 0 rows affected, 1 warning (0.06 sec) MySQL [(none)] > flush privileges;Query OK, 0 rows affected (0.05 sec) 18.6 execute authorization root remote 18.5MySQL [(none)] > GRANT REPLICATION SLAVE ON *. * TO 'root'@'192.168.18.5' IDENTIFIED BY' 1qaz2wsx' Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [(none)] > flush privileges;Query OK, 0 rows affected (0.03 sec)

View the binary file name and location on 18.5

Tell the file name and location of 18.5 on 18.6

MySQL [(none)] > CHANGE MASTER TO

-> MASTER_HOST='192.168.18.5'

-> MASTER_USER='root'

-> MASTER_PASSWORD='1qaz2wsx'

-> MASTER_LOG_FILE='mysql-bin.000003'

-> MASTER_LOG_POS=154

This completes the master-slave mode.

To complete the master, on the contrary, 18.6 should also tell the binary file name and location of 18.5.

View on 18.6

Execute on 18.5

MySQL [(none)] > CHANGE MASTER TO

-> MASTER_HOST='192.168.18.6'

-> MASTER_USER='root'

-> MASTER_PASSWORD='1qaz2wsx'

-> MASTER_LOG_FILE='mysql-bin.000005'

-> MASTER_LOG_POS=154

Query OK, 0 rows affected, 2 warnings (0.31 sec)

Then turn on start slave respectively.

View slave status

At this point, the master master replication is complete.

Before configuring the master, the mysql library had better be consistent, that is, the initialization mode, if the library is inconsistent, it will lead to an error.

Reference link

Error reference link

Configure reference links

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.