MySQL主主同步

MySQL主主同步

主主同步原理同主从，不过是双向而已

1.修改1.4配置文件

vim /etc/my.cnf#同时开始binlog和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 = 1#定义偏移量auto_increment_offset=1auto_increment_increment=2

2.查看1.4日志POS点

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.修改1.5配置文件

vim /etc/my.cnf#同时开始binlog和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 = 1#定义偏移量auto_increment_offset = 2

4.查看1.5日志POS点

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.在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.在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.在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.在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.查看1.4同步状态

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          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.查看1.5同步状态

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:   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.在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)

创建表：

MariaDB [prince]> create table Threek(id int auto_increment primary key,name varchar(50));Query OK, 0 rows affected (0.26 sec)

插入数据

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.在1.5上插入数据

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

查看表：

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

查看数据

MariaDB [prince]> select * from Threek;+----+--------+| id | name   |+----+--------+|  1 | wukaka ||  3 | ckl    ||  5 | love   |+----+--------+3 rows in set (0.00 sec)

插入数据：

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.在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)