MySQL master master synchronization

MySQL master master synchronization

The principle of master-master synchronization is the same as that of master-slave, but it is two-way.

1. Modify 1.4 configuration file

Vim / etc/my.cnf# starts both 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 = define offset auto_increment_offset=1auto_increment_increment=2

two。 View the 1.4-day log POS point

MariaDB [(none)] > show master status\ gateway * 1. Row * * File: mysql-bin.000002 Position: 662Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

3. Modify 1.5 configuration file

Vim / etc/my.cnf# starts both 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 = define offset auto_increment_offset = 2

4. View the 1.5-day log POS point

MariaDB [mysql] > show master status\ gateway * 1. Row * * File: mysql-bin.000001 Position: 1073 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

5. Add replication users 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 replication users 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\ gateway * 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 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: 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\ gateway * 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: 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: 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 databases and tables 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)

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)

Insert data

MariaDB [prince] > insert into Threek (name) values ('wukaka'), (' ckl'), ('love'); 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 | +-+ 3 rows in set (0.00 sec)

twelve。 Insert data on 1.5

MariaDB [(none)] > use prince;Database changed

View the 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 +-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)