MySQL 8 双主设置

前言

前面已经讲到了 MySQL 8 主从复制的设置，另一种 MySQL 高可用的架构是主-主的架构。这种结构的几个数据库都是主数据库，每个数据库数据的更新都会同步到其他的数据库中。所以每个数据库都会保持最新的数据，而且每个数据库也能同时做读写操作。其中双主结构的是比较简单的。 本文以《MySQL 8 主从复制设置》 一文中的设置为基础，完成双主架构的 MySQL 的设置。

原理概述

在《MySQL 8 主从复制设置》 一文中，我们已经演示了设置一主一从架构的 MySQL 集群。我们也知道了从库和主库保持同步是从库通过监控主库的 bin 日志获得主库上的数据的变化，并且做出同步的改变而完成的。主从复制的逻辑架构如图所示

主从 MySQL 结构

双主的结构就是两个库都分别互为主-从，互相监控对方数据的变化，并且做出同样响应的改变，从而保证两个数据库的变化都会同步到对方的数据库中。他的结构如下图所示

双主 MySQL 结构

这里可以看到，和主从结构相比，双主结构多了一条从数据库 B 到数据库 A 的连接线，这条连接线是 A 库用来监控数据 B 的 bin 日志的变化，从而保证 A 库和 B 库中的变化的数据保持一致。

大家可能会有一个疑问，在双主结构中，如果 A 库数据变化了，同步到 B 库后，B 库数据也发生了变化，会在 B 库也会产生 bin 日志记录，那么 A 库监控到了后，又会根据逻辑，改变 A 库数据，产生新的 A 库 bin 日志，这样导致了两个库的死循环。

实际上并不会。如果查看 bin 日志的内容，我们会发现，bin 日志每一条都记录了这条日志产生的 server id ，系统监控到对方数据变化的 bin 日志的时候，如果发现这条日志的原始的 servier id 是自己，就不会做任何操作。所以在这种 MySQL 集群中，必须设置 server id ，并且不能相同。这个在《MySQL 8 主从复制设置》 一文中的配置信息中也有所体现。

安装准备

按照 《MySQL 8 二进制方式安装到 CentOS 7 中》 一文中的步骤在两台服务器 10.110.2.71 和 10.110.2.72 上分别安装 MySQL 8 。两个机器 MysQL 的配置粉笔如下所示

10.110.2.71 配置

[mysqld]
server-id=71
port=38965
character-set-server=utf8mb4
default-time_zone='+8:00'
lower_case_table_names=1


max_connections=3000
max_connect_errors=100000


default_authentication_plugin=mysql_native_password
datadir=/data/mysql/data
socket=/data/mysql/mysql.sock

log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/mysqld.pid

binlog_format=ROW
log_bin=/data/mysql/data/mysql-bin-71.log
expire_logs_days=30

long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/mysql/logs/slow.log

[client]
port=38965
default-character-set=utf8mb4
socket=/data/mysql/mysql.sock

[mysql]
no-auto-rehash
default-character-set=utf8mb4

10.110.2.72 配置

[mysqld]
server-id=72
port=38965
character-set-server=utf8mb4
default-time_zone='+8:00'
lower_case_table_names=1


max_connections=3000
max_connect_errors=100000


default_authentication_plugin=mysql_native_password
datadir=/data/mysql/data
socket=/data/mysql/mysql.sock

log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/mysqld.pid

binlog_format=ROW
log_bin=/data/mysql/data/mysql-bin-72.log
expire_logs_days=30

long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/mysql/logs/slow.log

[client]
port=38965
default-character-set=utf8mb4
socket=/data/mysql/mysql.sock

[mysql]
no-auto-rehash
default-character-set=utf8mb4

以上配置中需要注意的有两点，第一点是两个 MySQL 的 server-id 值不能相同，第二个是我们增加了下面的配置

binlog_format=ROW
log_bin=/data/mysql/data/mysql-bin-71.log
expire_logs_days=30

这个配置第一行指定了 MySQL bin 日志的格式是原始格式。这个是 MySQL 8 的默认值，但是以前的版本不是，所有这里明确指出了。配置的第三行指定了 bin 日志过期的时间是 30 天，也就是只保存最近 30 天的 bin 日志。第二行不是很有必要，只是为了自定义一下 bin 日志文件的前缀。

然后分别启动两个数据库，按照提示修改数据库初始的 root 用户密码，就可以使用这两个单机的 MySQL 了。

双主配置

创建复制用户

上面的步骤完成后，我们得到了两个单独的 MySQL，他们之间还没有联系，现在我们要将这两台服务器配置成为 双主结构。
首先用 root 用户连接上两个 MySQL 的控制台，然后在两台机器上分别设置一个用来复制数据的用户。

mysql>create user 'repl'@'10.110.2.%' identified by 'reple_password';
mysql>grant replication slave on *.* to 'repl'@'10.110.2.%';
mysql>

这里第一个语句创建一个密码为 'reple_password' 在网段 10.110.2.% 上能够访问的数据库用户 repl 。第二句给这个用户授予到主从复制的权限

10.110.2.72 从库设置

首先在 10.110.2.71 上查看当前 bin 日志状态。

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| mysql-bin-71.000002 |     962  |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在 10.110.2.72 上设置从库，并启动主从复制

mysql> change master to
    -> master_host='10.110.2.71',
    -> master_port=38965,
    -> master_user='repl',
    -> master_password='replpassword',
    -> master_log_file='mysql-bin-71.000002',
    -> master_log_pos=962 ;
mysql>start slave;
mysql>

在 10.110.2.72 上查看从库状态

ysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.110.2.71
                  Master_User: repl
                  Master_Port: 38965
                Connect_Retry: 60
              Master_Log_File: mysql-bin-71.000002
          Read_Master_Log_Pos: 962
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 319
        Relay_Master_Log_File: mysql-bin-71.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: 4035
              Relay_Log_Space: 1431
              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: 0
Master_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: 71
                  Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

这说明10.110.2.72 作为从库已经准备好接受主库 10.110.2.71 的变化并同步数据了。

10.110.2.71 从库设置

首先在 10.110.2.72 上查看当前 bin 日志状态。

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| mysql-bin-72.000002 |     962  |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在 10.110.2.71 上设置从库，并启动主从复制

mysql> change master to
    -> master_host='10.110.2.72',
    -> master_port=38965,
    -> master_user='repl',
    -> master_password='replpassword',
    -> master_log_file='mysql-bin-72.000002',
    -> master_log_pos=962 ;
mysql>start slave;
mysql>

在 10.110.2.71 上查看从库状态

ysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.110.2.72
                  Master_User: repl
                  Master_Port: 38965
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 962
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 319
        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: 4035
              Relay_Log_Space: 1431
              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: 0
Master_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: 71
                  Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

这说明10.110.2.71 作为从库已经准备好接受主库 10.110.2.72 的变化并同步数据了。

验证

72 同步 71 验证

首先，在 71 上做如下的操作

mysql> create database test1;
mysql> create user 'test1'@'%' identified by 'test1_paswd';
mysql> grant all privileges on test1.*  to 'test1'@'%';
mysql> crea table test (id bigint not null,name varchar(16),PRIMARY KEY(id));
mysql>user test1;
mysql>insert into test values(1,'name1');
mysql>insert into test values(2,'name2');
mysql>select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | name1   |
|  2 | name2   |
+----+------+
2 rows in set (0.00 sec)
mysql>

然后在 10.110.2.72 上执行如下操作

mysql>user test1;
mysql>select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | name1   |
|  2 | name2   |
+----+------+
2 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| test1            | %           |
| repl             | 10.110.2.%  |
| mysql.infoschema | localhost   |
| mysql.session    | localhost   |
| mysql.sys        | localhost   |
| root             | localhost   |
+------------------+-------------+
6 rows in set (0.00 sec)

可以看到在 10.110.2.71 服务器上创建用户，数据库，表，数据，以及用户权限都被同步到 72 数据库上了。

71 同步 72 验证

首先在 10.110.2.72 上执行如下操作

mysql>user test1;
mysql>insert into test values(3,'name1');
mysql>insert into test values(4,'name2');
mysql>select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | name1  |
|  2 | name2  |
|  3 | name3  |
|  4 | name4  |
+----+------+
4 rows in set (0.00 sec)
mysql> create database test2;
mysql> create user 'test2'@'%' identified by 'test2_paswd';
mysql> grant all privileges on test2.*  to 'test2'@'%';
mysql> use test2';
mysql> crea table test (id bigint not null,name varchar(16), sex int,PRIMARY KEY(id));
mysql>insert into test values(1,'name1',0);
mysql>insert into test values(2,'name2',1);
mysql>select * from test;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  1 | name1 |    0 |
|  2 | name2 |    1 |
+----+-------+------+
2 rows in set (0.00 sec)

然后在 10.110.2.71 上执行如下操作

mysql>user test1;
mysql>select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | name1  |
|  2 | name2  |
|  3 | name3  |
|  4 | name4  |
+----+------+
4 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| test1            | %           |
| test2            | %           |
| repl             | 10.110.2.% |
| mysql.infoschema | localhost   |
| mysql.session    | localhost   |
| mysql.sys        | localhost   |
| root             | localhost   |
+------------------+-------------+
7 rows in set (0.00 sec)

mysql>user test2;
mysql>select * from test;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  1 | name1 |    0 |
|  2 | name2 |    1 |
+----+-------+------+
2 rows in set (0.00 sec)

从这里可以看到，10.110.2.72 数据库上指定的增加用户、用户赋权、创建数据库，创建表，增加数据等操作都同步到 71 数据库上了。

后记

上面详细的说明了双主结构 MySQL 集群的设置，这种结构不需要区分主从，客户端可以随便连接任何一个数据进行操作。唯一的一个问题是在一个数据库上插入了一条数据，会有一个较小的延迟同步到另一个数据库上，这个时候如果查询落在第二个数据库上，则会找不到要查询的数据。这个在插入数据后，保证这次操作时，查询该数据的时候，数据查询的请求发送给插入时的数据库即可。

同样的，我们可以看到如果有多个数据库，将多个数据库的主从关系设置成为一个首尾相连的环，那么就可以设置成为多主结构的 MySQL 集群。当然了，这种多主结构随着成员的增多，复制的链条变长，同时其中一个节点宕机，会导致整个集群数据的问题。对运维的压力是比较大的。这个不在这里讨论了。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,802评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,109评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,683评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,458评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,452评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,505评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,901评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,550评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,763评论 1赞 296
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,556评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,629评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,330评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,898评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,897评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,140评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,807评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,339评论 2赞 342

推荐阅读更多精彩内容

  • MySQL优化
    一、MySQL优化 MySQL优化从哪些方面入手： （1）存储层（数据） 构建良好的数据结构。可以大大的提升我们S...
    宠辱不惊丶岁月静好阅读 2,410评论 1赞 8
  • MySQL数据备份和还原
    虽然不能恢复百分百，至少能将损失降到最低。 有个问题测试： 主从同步时，主库网络断开，binlog dump线程...
    kun_zhang阅读 3,024评论 0赞 6
  • Mysql 主从复制实战(学习笔记十三)
    https://www.cnblogs.com/along21/p/8011596.html https://bl...
    SkTj阅读 3,120评论 1赞 4
  • 一生何求
    今天推荐的题目：什么是成功的人生？成功二字很好理解，获得预期的结果，达到目的。我们做的每件事不是成功就是失败，如果...
    绿兔子000阅读 283评论 2赞 1
  • 209
    鸴鸣阅读 479评论 5赞 12