MySQL双主单写和双主双写

推荐 MySQL 双主单写

对 Master1 配置信息修改

修改/etc/my.cnf

vim /etc/my.cnf
#添加或修改内容如下
log_bin=mysql-bin
server-id=1
sync-binlog=1
#指定哪些库不同步，其他库默认都同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#指定哪些库同步,不需要
#binlog-do-db=lanebin
#双主配置
relay_log=mysql-relay-bin
log_slave_updates=1
#双主单写可以不配置，双主双写要配置主键递增1，3，5，7
auto_increment_offset=1
auto_increment_increment=2

重启 mysql

systemctl restart mysqld

授权主库的 root 权限

#进入mysql
grant replication slave on *.* to 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%' identified by 'root';
flush privileges

查看 binlog 信息

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

对 Master2 配置信息修改

修改配置信息

vim /etc/my.cnf
#添加或修改内容如下
log_bin=mysql-bin
server-id=12
sync-binlog=1
#指定哪些库不同步，其他库默认都同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#指定哪些库同步,不需要
#binlog-do-db=lanebin
#双主配置
relay_log=mysql-relay-bin
log_slave_updates=1
##双主单写可以不配置，双主双写要配置主键递增2,4,6,8
auto_increment_offset=2
auto_increment_increment=2
#可以添加readonly

重启 mysql

systemctl restart mysqld

授权 root 权限

#进入mysql
grant replication slave on *.* to 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%' identified by 'root';
flush privileges

查看 binlog 信息

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000013
         Position: 884
     Binlog_Do_DB: 
 Binlog_Ignore_DB: information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

分别为 master1 和 master2 指定主库

进入 master1 的 mysql 界面

#指定m1的主库m2的ip端口用户密码binlog信息
mysql> change master to master_host='172.16.94.9' ,master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000013',master_log_pos=884;
mysql> start slave;
#查看是否指定主库成功
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.94.9
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 884
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

进入 master2 的 mysql 界面

#指定m2的主库m1的ip端口用户密码binlog信息
mysql> change master to master_host='172.16.94.5' ,master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=154;
mysql> start slave;
#查看是否指定主库成功
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.94.5
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试双主是否成功

在 Master1 进行操作

create detabase mymaster1;
use mymaster1;
create table test1(id int primary key auto_increment,name varchar(200))engine=innodb charset=utf8;
insert into test1(name)values('a');
insert into test1(name)values('b');

在 Master2 进行操作

use mymaster1;
insert into test1(name)values('x');
insert into test1(name)values('y');

分别查看 master1 和 master2 的数据，可以看出实现了互为主从的双主模式

#master1
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  4 | x    |
|  6 | y    |
+----+------+
4 rows in set (0.00 sec)
#master2
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  4 | x    |
|  6 | y    |
+----+------+
4 rows in set (0.00 sec)