MySQL Master Master sync

MySQL Master Master sync

Master Master Synchronization principle and master-slave, but two-way only

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/93/05/wKiom1kHAY_yRJrVAAAuVUdwrZA178.png "title=" Aa4.png "alt=" Wkiom1khay_yrjrvaaauvudwrza178.png "/>

1. Modify the 1.4 configuration file

vim/etc/my.cnf# at the same time start binlog and relay Loglog-bin=mysql-binbinlog_format=mixedserver-id = 1sync_master_info = 1sync_binlog = 1 Innodb_support_xa = onrelay_log= Relay-logrelay_log_index=relay-log.indexskip_slave_start = ONsync_relay_log = 1sync_relay_log_info = # defines the offset auto_increment_offset=1auto_increment_increment=2

2. View 1.4 Log POS points

MariaDB [(None)]> Show Master status\g*************************** 1. Row *************************** file:mysql-bin.000002 position:662 binlog_do_db:binlog_ignore_db: 1 row in Set (0.00 sec)

3. Modify the 1.5 configuration file

vim/etc/my.cnf# at the same time start binlog and relay Loglog-bin=mysql-binbinlog_format=mixedserver-id = 3sync_master_info = 1sync_binlog = 1innodb_support_xa = onrelay_log= Relay-logrelay_log_index=relay-log.indexskip_slave_start = ONsync_relay_log = 1sync_relay_log_info = # definition Offset Auto_increment_offset = 2

4. View 1.5 log POS points

MariaDB [mysql]> Show Master status\g*************************** 1.  Row *************************** file:mysql-bin.000001 position:1073 binlog_do_db:binlog_ignore_db: 1 row in Set (0.00 sec)

5. Add a replication user on 1.4

MariaDB [(None)]> Grant replication Slave,replication Client on * * to ' Repa ' @ ' 192.168.1.5 ' identified by ' slavepass '; Query OK, 0 rows affected (0.09 sec)

6. Add a replication user on 1.5

MariaDB [mysql]> Grant Replication Slave,replication Client on * * to ' REPB ' @ ' 192.168.1.4 ' identified by ' slavepass '; Query OK, 0 rows affected (0.07 sec)

7. Start synchronous replication on 1.4

Change Master to master_host= ' 192.168.1.5 ', master_user= ' REPB ', master_password= ' Slavepass ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=1073;

8. Start synchronous replication on 1.5

Change Master to master_host= ' 192.168.1.4 ', master_user= ' Repa ', master_password= ' Slavepass ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=662;

9. View 1.4 Synchronization Status

mariadb [(None)]> show slave status\g*************************** 1. row                  Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.1.5                   Master_User: repb                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001          &nbsP read_master_log_pos: 1073                Relay_Log_File: relay-log.000002                 Relay_Log_Pos: 537         relay_master_log_file: mysql-bin.000001              Slave_IO_Running: Yes             slave_sql_running: yes               replicate_do_db:           replicate_ignore_db:             Replicate_Do_Table:         replicate_ignore_table:       replicate_wild_ do_table:  &nbsp replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 1073               Relay_Log_Space: 829               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            master_ssl_allowed: no            master_ssl_ca_file:            master_ Ssl_ca_path:               master_ Ssl_cert:             master_ssl_cipher:                 master_ssl_key :          seconds_behind_master: 0master_ssl_verify_server_ cert: no                 last_io_errno: 0                 Last_IO_Error:                 last_sql_errnO: 0               last_sql_ error:   replicate_ignore_server_ids:               Master_Server_Id: 3                Master_SSL_Crl:             Master_SSL_Crlpath:                     Using_Gtid: No                   Gtid_IO_Pos:        Replicate_Do_Domain_Ids:   Replicate_Ignore_Domain_Ids:                  Parallel_Mode:  Conservative1 row in set  (0.00 sec) 

10. View 1.5 synchronization Status

mariadb [mysql]> show slave status\g*************************** 1. row *                 Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.1.4                   Master_User: repa                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002            read_master_log_pos: 662                Relay_Log_File: relay-log.000002                 Relay_Log_Pos: 537         relay_master_log_file: mysql-bin.000002              Slave_IO_Running: Yes             slave_sql_running: yes               replicate_do_db:           replicate_ignore_db:             Replicate_Do_Table:         replicate_ignore_table:       replicate_wild_ Do_table:  &nbSp replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 662               Relay_Log_Space: 829               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            master_ssl_ca_file:            master_ Ssl_ca_path:               master_ Ssl_cert:             master_ssl_cipher:                 master_ssl_key :          seconds_behind_master: 0master_ssl_verify_server_ cert: no                 last_io_errno: 0                 Last_IO_Error:                 last_sql_errno:&Nbsp;0               last_sql_error :   replicate_ignore_server_ids:               Master_Server_Id: 1                Master_SSL_Crl:             Master_SSL_Crlpath:                     Using_Gtid: No                   Gtid_IO_Pos:        Replicate_Do_Domain_Ids:   Replicate_Ignore_Domain_Ids:                  parallel_mode: conservative1  row in set  (0.00 SEC) 

11. Create a database and table on 1.4

MariaDB [(None)]> CREATE database Prince; Query OK, 1 row affected (0.09 sec) MariaDB [(none)]> use prince;database changedmariadb [prince]> drop table Threek; Query OK, 0 rows affected (0.13 sec)

To create a table:

MariaDB [prince]> CREATE TABLE threek (ID int auto_increment primary key,name varchar (50)); Query OK, 0 rows affected (0.26 sec)

Inserting data

MariaDB [prince]> INSERT into Threek (name) VALUES (' Wukaka '), (' Ckl '), (' Love '); Query OK, 3 rows affected (0.06 sec) Records:3 duplicates:0 warnings:0

MariaDB [prince]> SELECT * from threek;+----+--------+| ID |  Name |+----+--------+| 1 |  Wukaka | | 3 |  Ckl | | 5 | Love |+----+--------+3 rows in Set (0.00 sec)

12. Insert Data on 1.5

MariaDB [(None)]> use prince;database changed

View table:

MariaDB [prince]> show tables;+------------------+| Tables_in_prince |+------------------+| Threek |+------------------+1 row in Set (0.00 sec)

View data

MariaDB [prince]> SELECT * from threek;+----+--------+| ID |  Name |+----+--------+| 1 |  Wukaka | | 3 |  Ckl | | 5 | Love |+----+--------+3 rows in Set (0.00 sec)

Insert data:

mariadb [prince]> insert into threek  (name)  values (' stack '), (' Ayia '), (' Snow ') ; query ok, 3 rows affected  (0.06 sec) records: 3  duplicates:  0  warnings: 0mariadb [prince]> select * from threek;                                          +----+--------+| id | name   |+----+--------+|  1 |  wukaka | |   3 | ckl    | |   5 | love   | |   6 | stack  | |   7 | ayia   | |   8 | snow   |+----+--------+6 rows in set  (0.00  Sec

13. View data on 1.4

MariaDB [prince]> SELECT * from threek;+----+--------+| ID |  Name |+----+--------+| 1 |  Wukaka | | 3 |  Ckl | | 5 |  Love | | 6 |  Stack | | 7 |  Ayia | | 8 | Snow |+----+--------+6 rows in Set (0.00 sec)

This article is from the "take a deep Breath again" blog, make sure to keep this source http://ckl893.blog.51cto.com/8827818/1920974

MySQL Master Master sync