MySQL主主同步

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

1.修改1.4配置文件

vim/etc/my.cnf#同时开始binlog和relayloglog-bin=mysql-binbinlog_format=mixedserver-id=1sync_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=1auto_increment_increment=2

2.查看1.4日志POS点

MariaDB[(none)]>showmasterstatus\G***************************1.row***************************File:mysql-bin.000002Position:662Binlog_Do_DB:Binlog_Ignore_DB:1rowinset(0.00sec)

3.修改1.5配置文件

vim/etc/my.cnf#同时开始binlog和relayloglog-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]>showmasterstatus\G***************************1.row***************************File:mysql-bin.000001Position:1073Binlog_Do_DB:Binlog_Ignore_DB:1rowinset(0.00sec)

5.在1.4上添加复制用户

MariaDB[(none)]>grantreplicationslave,replicationclienton*.*to'repa'@'192.168.1.5'identifiedby'slavepass';QueryOK,0rowsaffected(0.09sec)

6.在1.5上添加复制用户

MariaDB[mysql]>grantreplicationslave,replicationclienton*.*to'repb'@'192.168.1.4'identifiedby'slavepass';QueryOK,0rowsaffected(0.07sec)

7.在1.4上开始同步复制

changemastertomaster_host='192.168.1.5',master_user='repb',master_password='slavepass',master_log_file='mysql-bin.000001',master_log_pos=1073;

8.在1.5上开始同步复制

changemastertomaster_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)]>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.1.5Master_User:repbMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000001Read_Master_Log_Pos:1073Relay_Log_File:relay-log.000002Relay_Log_Pos:537Relay_Master_Log_File:mysql-bin.000001Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:1073Relay_Log_Space:829Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:3Master_SSL_Crl:Master_SSL_Crlpath:Using_Gtid:NoGtid_IO_Pos:Replicate_Do_Domain_Ids:Replicate_Ignore_Domain_Ids:Parallel_Mode:conservative1rowinset(0.00sec)

10.查看1.5同步状态

MariaDB[mysql]>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.1.4Master_User:repaMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000002Read_Master_Log_Pos:662Relay_Log_File:relay-log.000002Relay_Log_Pos:537Relay_Master_Log_File:mysql-bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:662Relay_Log_Space:829Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1Master_SSL_Crl:Master_SSL_Crlpath:Using_Gtid:NoGtid_IO_Pos:Replicate_Do_Domain_Ids:Replicate_Ignore_Domain_Ids:Parallel_Mode:conservative1rowinset(0.00sec)

11.在1.4上创建数据库及表

MariaDB[(none)]>createdatabaseprince;QueryOK,1rowaffected(0.09sec)MariaDB[(none)]>useprince;DatabasechangedMariaDB[prince]>droptableThreek;QueryOK,0rowsaffected(0.13sec)

创建表：

MariaDB[prince]>createtableThreek(idintauto_incrementprimarykey,namevarchar(50));QueryOK,0rowsaffected(0.26sec)

插入数据

MariaDB[prince]>insertintoThreek(name)values('wukaka'),('ckl'),('love');QueryOK,3rowsaffected(0.06sec)Records:3Duplicates:0Warnings:0

MariaDB[prince]>select*fromThreek;+----+--------+|id|name|+----+--------+|1|wukaka||3|ckl||5|love|+----+--------+3rowsinset(0.00sec)

12.在1.5上插入数据

MariaDB[(none)]>useprince;Databasechanged

查看表：

MariaDB[prince]>showtables;+------------------+|Tables_in_prince|+------------------+|Threek|+------------------+1rowinset(0.00sec)

查看数据

MariaDB[prince]>select*fromThreek;+----+--------+|id|name|+----+--------+|1|wukaka||3|ckl||5|love|+----+--------+3rowsinset(0.00sec)

插入数据：

MariaDB[prince]>insertintoThreek(name)values('stack'),('ayia'),('snow');QueryOK,3rowsaffected(0.06sec)Records:3Duplicates:0Warnings:0MariaDB[prince]>select*fromThreek;+----+--------+|id|name|+----+--------+|1|wukaka||3|ckl||5|love||6|stack||7|ayia||8|snow|+----+--------+6rowsinset(0.00sec)

13.在1.4上查看数据

MariaDB[prince]>select*fromThreek;+----+--------+|id|name|+----+--------+|1|wukaka||3|ckl||5|love||6|stack||7|ayia||8|snow|+----+--------+6rowsinset(0.00sec)