MySQL 学习 - Replication集群 - 搭建 - 双主双从

落日映苍穹つ 2023-05-30 15:57 20阅读 0赞

## 前言 ##

我们这里说的是双主双从集群搭建，当然这个是看你实际业务，说白了一点

两个主节点双向同步，A集群挂掉时，B集群因为双向同步，立即可以开始工作，实现高可用

## 基本原理 ##

具体流程

1.  主库将变更写入到主库的binlog中
2.  从库的IO进程读取主库binlog内容存储到Relay Log日志中
3.  从库的SQL进程读取Relay Log日志中内容在从库中重放

![基本原理][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]

Master 与 Slave 是单向同步的，如果我们想要双向同步，可以让双方互为主从关系  
![双向同步][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]

## 理想架构 ##

MyCat相关博文请查看 [MyCat 技术大纲][MyCat]

**本文只着重讲解双主双从模式配置，其他都差不多，或者关注我其他博文**

当然你不想那么麻烦，完全可以只做 RP集群，那个A集群与B集群双向同步，出问题的时候，使用Keepalived

或者云主机的以购买云商的负载均衡服务（比如阿里云的SLB）

![组织架构][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]

## 配置主从数据库服务器参数 ##

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>log_bin</td> 
   <td>启动二进制日志，配置路径就为存储路径<br>如配置 <code>mysql-bin</code> 那就会放入datadir 并以这个为前缀</td> 
  </tr> 
  <tr> 
   <td>server-id</td> 
   <td>在mysql集群中每个id都不一致，建立建议使用服务器IP后两段作为ID<br>比如192.168.10.68 ID可以命名为 1068</td> 
  </tr> 
  <tr> 
   <td>relay_log</td> 
   <td>启动 relay_log，配置路径就为存储路径<br>如配置 <code>relay-bin</code> 那就会放入datadir 并以这个为前缀</td> 
  </tr> 
  <tr> 
   <td>relay_only</td> 
   <td>不能执行写操作的，不管有没有 save权限，但supper用户依旧可以，如:root<br>好处是：避免误操作写入从服务器，导致主从数据不一致</td> 
  </tr> 
  <tr> 
   <td>super_read_only</td> 
   <td>super用户也不可以执行写操作，Mysql&gt;=5.7</td> 
  </tr> 
  <tr> 
   <td>skip_slave_start</td> 
   <td>在slave服务器重启后，不会自动启动复制链路<br>正常情况我们需要检查没有问题后，手动启动</td> 
  </tr> 
  <tr> 
   <td>master_info_repository</td> 
   <td>存储到数据库表中</td> 
  </tr> 
  <tr> 
   <td>relay_log_info_repository</td> 
   <td>将主从同步的信息，存储到innodb的表中</td> 
  </tr> 
  <tr> 
   <td>log-slave-updates</td> 
   <td>通常，从服务器不会将从主服务器收到的任何更新记录到其自己的二进制日志中。此选项告诉从服务器将其SQL线程执行的更新记录到其自己的二进制日志中。为了使该选项生效，</td> 
  </tr> 
 </tbody> 
</table>

## 流程说明 ##

## 环境配置 ##

<table> 
 <thead> 
  <tr> 
   <th>主机名</th> 
   <th>IP</th> 
   <th>角色</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>m1</td> 
   <td>192.168.105.65</td> 
   <td>Master</td> 
  </tr> 
  <tr> 
   <td>s1</td> 
   <td>192.168.105.66</td> 
   <td>Slave</td> 
  </tr> 
  <tr> 
   <td>m2</td> 
   <td>192.168.105.75</td> 
   <td>Master2</td> 
  </tr> 
  <tr> 
   <td>s2</td> 
   <td>192.168.105.76</td> 
   <td>Slave2</td> 
  </tr> 
 </tbody> 
</table>

mysql配置文件 my.cnf

[client]
    socket	= /var/run/mysqld/mysqld.sock
    
    [mysqld]
    pid-file = /var/run/mysqld/mysqld.pid
    socket	= /var/run/mysqld/mysqld.sock
    datadir	= /var/lib/datadir
    basedir = /var/lib/mysql
    # 数据字符集
    character_set_server = utf8
    # 允许远程访问的IP地址
    bind-address = 0.0.0.0
    # 跳过DNS解析
    skip-name-resolve
    symbolic-links= 0

启动Docker

`要注意一点，不要开启 binlog日志，否则双主双向同就会报冲突`

docker run -d -p 3306:3306 --name my-mysql \
    -v $PWD/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
    -v $PWD/db_backup:/data/db_backup \
    -v $PWD/mysql:/var/lib/mysql \
    -v $PWD/datadir:/var/lib/datadir \
    -e MYSQL_ROOT_PASSWORD=123456 \
    mysql:5.7.27

## 创建备份用户 ##

> 请在两个Master节点上执行，分别创建，也就是本文中演示环境的 m1，m2

进入docker

docker exec -it my-mysql bash

登录mysql

mysql -uroot -p123456

首先创建一个用户

create user 'backup'@'%' identified by '123456';

进行授权

`相比全量备份，多了一个 replication slave`

grant select,reload,lock tables,replication slave,replication client,show view,event,process,file on *.* to 'backup'@'%';

查看

show grants for 'backup'@'%';

## 配置MySQL ##

退出docker

docker stop my-mysql && docker rm my-mysql

mysql配置文件 my.cnf

vim my.cnf

[client]
    socket	= /var/run/mysqld/mysqld.sock
    
    [mysqld]
    pid-file = /var/run/mysqld/mysqld.pid
    socket	= /var/run/mysqld/mysqld.sock
    datadir	= /var/lib/datadir
    basedir = /var/lib/mysql
    log_bin = mysql-bin
    relay_log = relay-bin
    server-id = 1111
    # 数据字符集
    character_set_server = utf8
    # 允许远程访问的IP地址
    bind-address = 0.0.0.0
    # 跳过DNS解析
    skip-name-resolve
    symbolic-links= 0

M1 节点

server-id = 10565
    log-slave-updates = on

S2 节点

server-id = 10566

M2 节点

server-id = 10575
    log-slave-updates = on

S2 节点

server-id = 10576

四个节点启动docker

## Slave节点 ##

--------------------

> 两台Slave节点上操作，也就是本章演示环境 s1,s2

进入docker

docker exec -it my-mysql bash

登录mysql

mysql -uroot -p123456

停止slave

stop slave;

**s1节点** \- 修改链路

change master to
    master_host='192.168.105.65',
    master_port=3306,
    master_user='backup',
    master_password='123456';

**s2节点** \- 修改链路

change master to
    master_host='192.168.105.75',
    master_port=3306,
    master_user='backup',
    master_password='123456';

启动

start slave;

查看链路状态

show slave status \G;

Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

## 建议Master，双向同步 ##

> 根据上文 我们已经创了两个集群，A集群(m1 -> s1) B集群(m2 -> s2)

为了实现高可用，我们需要建议 m1 -> m2 之间的双向同步

m1操作

stop slave;

change master to
    master_host='192.168.105.75', -- 注意这个目标地址是m2的
    master_port=3306,
    master_user='backup',
    master_password='123456';

start slave;

show slave status \G;

Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

m2操作

stop slave;

change master to
    master_host='192.168.105.65', -- 注意这个目标地址是m2的
    master_port=3306,
    master_user='backup',
    master_password='123456';

start slave;

show slave status \G;

Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

## 测试双向同步 ##

这里我们m1，m2，双向同步已经好了，我们登录这两个任意一个进入docker

docker exec -it my-mysql bash

登录mysql

mysql -uroot -p123456

创建数据库，创建测试表

create database test;

use test;

CREATE TABLE `student` (
      `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `student_name` varchar(64) NOT NULL COMMENT '学生名字',
      PRIMARY KEY (`student_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO student(`student_id`, `student_name`) VALUES (1, 'A');
    INSERT INTO student(`student_id`, `student_name`) VALUES (2, 'B');
    INSERT INTO student(`student_id`, `student_name`) VALUES (3, 'C');
    INSERT INTO student(`student_id`, `student_name`) VALUES (4, 'D');

其他三个节点查询

use test;

select * from student;

+------------+--------------+
    | student_id | student_name |
    +------------+--------------+
    |          1 | A            |
    |          2 | B            |
    |          3 | C            |
    |          4 | D            |
    +------------+--------------+

### Slave 节点收尾工作 ###

--------------------

退出Docker容器 `如果用Docker的话`

exit

加入只读属性，避免程序员误写入数据，导致主从同步失败

vim my.cnf

[mysqld]
    
    # 主从配置中，加入这两个只读属性
    read_only = on
    super_read_only = on

重启mysql `这里说说哈，Docker你就直接重启容器就行`

service mysql restart

测试从节点是否限制插入数据

INSERT INTO student(`student_id`, `student_name`) VALUES (5, 'E');

ERROR 1290 (HY000): Unknown error 1290

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191111105719591.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191111105730753.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[MyCat]: https://xiamen.blog.csdn.net/article/details/103006792
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191111105805211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70