推荐 MySQL 双主单写

对 Master1 配置信息修改

修改/etc/my.cnf

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

重启 mysql

  1. systemctl restart mysqld

授权主库的 root 权限

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

查看 binlog 信息

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

对 Master2 配置信息修改

修改配置信息

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

重启 mysql

  1. systemctl restart mysqld

授权 root 权限

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

查看 binlog 信息

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

分别为 master1 和 master2 指定主库

进入 master1 的 mysql 界面

  1. #指定m1的主库m2的ip端口用户密码binlog信息
  2. 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;
  3. mysql> start slave;
  4. #查看是否指定主库成功
  5. mysql> show slave status \G;
  6. *************************** 1. row ***************************
  7.                Slave_IO_State: Waiting for master to send event
  8.                   Master_Host: 172.16.94.9
  9.                   Master_User: root
  10.                   Master_Port: 3306
  11.                 Connect_Retry: 60
  12.               Master_Log_File: mysql-bin.000013
  13.           Read_Master_Log_Pos: 884
  14.                Relay_Log_File: mysql-relay-bin.000002
  15.                 Relay_Log_Pos: 320
  16.         Relay_Master_Log_File: mysql-bin.000013
  17.              Slave_IO_Running: Yes
  18.             Slave_SQL_Running: Yes

进入 master2 的 mysql 界面

  1. #指定m2的主库m1的ip端口用户密码binlog信息
  2. 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;
  3. mysql> start slave;
  4. #查看是否指定主库成功
  5. mysql> show slave status \G;
  6. *************************** 1. row ***************************
  7.                Slave_IO_State: Waiting for master to send event
  8.                   Master_Host: 172.16.94.5
  9.                   Master_User: root
  10.                   Master_Port: 3306
  11.                 Connect_Retry: 60
  12.               Master_Log_File: mysql-bin.000004
  13.           Read_Master_Log_Pos: 154
  14.                Relay_Log_File: mysql-relay-bin.000002
  15.                 Relay_Log_Pos: 320
  16.         Relay_Master_Log_File: mysql-bin.000004
  17.              Slave_IO_Running: Yes
  18.             Slave_SQL_Running: Yes

测试双主是否成功

在 Master1 进行操作

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

在 Master2 进行操作

  1. use mymaster1;
  2. insert into test1(name)values('x');
  3. insert into test1(name)values('y');

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

  2. #master1
  3. mysql> select * from test1;
  4. +----+------+
  5. | id | name |
  6. +----+------+
  7. |  1 | a    |
  8. |  3 | b    |
  9. |  4 | x    |
  10. |  6 | y    |
  11. +----+------+
  12. 4 rows in set (0.00 sec)
  13. #master2
  14. mysql> select * from test1;
  15. +----+------+
  16. | id | name |
  17. +----+------+
  18. |  1 | a    |
  19. |  3 | b    |
  20. |  4 | x    |
  21. |  6 | y    |
  22. +----+------+
  23. 4 rows in set (0.00 sec)