搭建MySQL集群一主一从&双主双从

男娘i 2023-02-12 05:49 13阅读 0赞

>  *  Docker部署MySQL5.X : [https://yuanyu.blog.csdn.net/article/details/105730051][https_yuanyu.blog.csdn.net_article_details_105730051]
>  *  Centos7搭建Redis单机&主从&哨兵&集群：[https://yuanyu.blog.csdn.net/article/details/117069744][https_yuanyu.blog.csdn.net_article_details_117069744]
>  *  Elasticsearch7.x&Kibana7.x部署：[https://blog.csdn.net/qq\_40794973/article/details/113354544][https_blog.csdn.net_qq_40794973_article_details_113354544]
>  *  Docker部署Canal并将消息推送到RabbitMQ：[https://yuanyu.blog.csdn.net/article/details/113062438][https_yuanyu.blog.csdn.net_article_details_113062438]
>  *  一线数据库工程师带你深入理解 MySQL：[https://www.imooc.com/read/43][https_www.imooc.com_read_43]

>  *  [https://dev.mysql.com/doc/refman/8.0/en/reset-slave.html][https_dev.mysql.com_doc_refman_8.0_en_reset-slave.html]

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

# 1 搭建一主一从 #

## 1.1 master【39.106.196.224】 ##

docker创建一个mysql容器

docker run \
    --name mysql_master \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --hostname=master \
    -p 5569:3306 \
    -di mysql:5.6

### 拷贝MySQL容器中的配置文件到宿主机
    docker cp mysql_master:/etc/mysql/mysql.conf.d/mysqld.cnf master_my.cnf
    ### 修改MySQL配置文件
    vi master_my.cnf

# 主服务器唯一ID
    server-id=1
    # 启用二进制日志
    log-bin=mysql-bin
    # 设置不要复制的数据库(可设置多个)
    binlog-ignore-db=mysql
    # 设置logbin格式
    binlog_format=STATEMENT

修改后的配置文件如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70][]

### 将文件拷贝到容器中，替换原有的my.cnf
    docker cp master_my.cnf mysql_master:/etc/mysql/mysql.conf.d/mysqld.cnf
    
    ### 进入容器内部查看是否修改成功
    docker exec -it mysql_master bash
    cat /etc/mysql/mysql.conf.d/mysqld.cnf
    exit

重启容器

docker restart mysql_master

使用远程工具连接

jdbc:mysql://39.106.196.224:5569/?serverTimezone=UTC

在主机MySQL里执行授权命令

-- 创建用户和授权写在了一起（高版本是不能这样写的）
    GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY '123456';

查询master的状态；记录下File和Position的值，执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化，或者等会再次查询

show master status;

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

## 2 slave【121.36.33.154】 ##

docker run \
    --name mysql_slave \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --hostname=slave \
    -p 5596:3306 \
    -di mysql:5.6

### 拷贝MySQL容器中的配置文件到宿主机
    docker cp mysql_slave:/etc/mysql/mysql.conf.d/mysqld.cnf slave_my.cnf
    ### 修改MySQL配置文件
    vi slave_my.cnf

# 从服务器唯一ID
    server-id=2
    # 启用中继日志
    relay-log=mysql-relay

修改后的配置文件如下

![20200524211111312.png][]

### 将文件拷贝到容器中，替换原有的my.cnf
    docker cp slave_my.cnf mysql_slave:/etc/mysql/mysql.conf.d/mysqld.cnf
    
    ### 进入容器内部查看是否修改成功
    docker exec -it mysql_slave bash
    cat /etc/mysql/mysql.conf.d/mysqld.cnf
    exit

重启容器

docker restart mysql_slave

使用远程工具连接

jdbc:mysql://121.36.33.154:5596/?serverTimezone=UTC

在从机上配置需要复制的主机

CHANGE MASTER TO
    MASTER_HOST ='39.106.196.224', -- master的ip地址
    MASTER_PORT = 5569, -- 主数据库的端口
    MASTER_USER ='sync', -- 主数据库上创建的账户
    MASTER_PASSWORD ='123456', -- 主数据库上创建的密码
    MASTER_LOG_FILE ='mysql-bin.000001', -- File
    MASTER_LOG_POS =639; -- Position

启动从服务器复制功能

start slave;

查看从服务器状态

show slave status \G

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1][]

## 1.3 验证主从 ##

######################## master上执行如下命令 ########################
    ### 创建库
    create database if not exists test default char set utf8mb4;
    use test;
    ### 创建表
    drop table if exists t1;
    create table `t1`
    (
        `id` int(10) not null auto_increment,
        `a`  varchar(255) default null,
        `b`  int(10)      default null,
        primary key (`id`)
    ) engine = innodb
      charset = utf8mb4;
    
    /**
      由于主从复制的特点@@hostname两个数据库的值是不同的，通过查询的结果判断到底是主还是从
      如果不设置主键，删除主（写）数据库的这条记录，从（读）数据库这条数据不会同步删除
     */
    insert into test.`t1` (`id`, `a`, `b`) values (1, @@hostname, '123456');

######################## 验证主从是否生效 ########################
    -- 可以发现在主从数据库查询出来的这个值是不一样的
    select * from test.`t1` where id = 1;

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

# 2 搭建双主双从 #

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求；当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 2][]

<table> 
 <tbody> 
  <tr> 
   <td>编号</td> 
   <td>角色</td> 
   <td style="width:243px;">IP 地址</td> 
   <td style="width:184px;">端口</td> 
   <td>机器名</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Master1</td> 
   <td style="width:243px;">101.200.43.221</td> 
   <td style="width:184px;">4500</td> 
   <td>master1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Slave1</td> 
   <td style="width:243px;">120.25.216.234</td> 
   <td style="width:184px;">4501</td> 
   <td>slave1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Master2</td> 
   <td style="width:243px;">121.36.33.154</td> 
   <td style="width:184px;">5000</td> 
   <td>master2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Slave2</td> 
   <td style="width:243px;">39.106.196.224</td> 
   <td style="width:184px;">5001</td> 
   <td>slave2</td> 
  </tr> 
 </tbody> 
</table>

## 2.1 Master1【101.200.43.221:4500】 ##

创建mysql容器

docker run \
    --name mysql_master1 \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --hostname=master1 \
    -p 4500:3306 \
    -di mysql:5.6

修改mysql配置文件

### 拷贝MySQL容器中的配置文件到宿主机
    docker cp mysql_master1:/etc/mysql/mysql.conf.d/mysqld.cnf master1_my.cnf
    ### 修改MySQL配置文件
    vi master1_my.cnf
    
    ### 将文件拷贝到容器中，替换原有的my.cnf
    docker cp master1_my.cnf mysql_master1:/etc/mysql/mysql.conf.d/mysqld.cnf
    
    ### 查看修改是否成功
    docker exec -it mysql_master1 /bin/bash -c 'cat /etc/mysql/mysql.conf.d/mysqld.cnf'

# 主服务器唯一ID
    server-id=1
    #启用二进制日志
    log-bin=mysql-bin
    # 设置不要复制的数据库(可设置多个)
    binlog-ignore-db=mysql
    # 设置logbin格式
    binlog_format=STATEMENT
    # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    log-slave-updates
    # 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
    auto-increment-increment=2
    # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
    auto-increment-offset=1

修改后的配置文件如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 3][]

重启

docker restart mysql_master1

远程连接

jdbc:mysql://101.200.43.221:4500?serverTimezone=UTC

在主机MySQL里创建用户并授权

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY '123456';

select host, user from mysql.user;

查询Master1的状态；分别记录下File和Position的值；执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

##  2.2 Slave1【120.25.216.234:4501】 ##

创建mysql容器

docker run \
    --name mysql_slave1 \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --hostname=slave1 \
    -p 4501:3306 \
    -di mysql:5.6

修改mysql配置文件

### 拷贝MySQL容器中的配置文件到宿主机
    docker cp mysql_slave1:/etc/mysql/mysql.conf.d/mysqld.cnf slave1_my.cnf
    ### 修改MySQL配置文件
    vi slave1_my.cnf
    
    ### 将文件拷贝到容器中，替换原有的my.cnf
    docker cp slave1_my.cnf mysql_slave1:/etc/mysql/mysql.conf.d/mysqld.cnf
    
    ### 查看修改是否成功
    docker exec -it mysql_slave1 /bin/bash -c 'cat /etc/mysql/mysql.conf.d/mysqld.cnf'

# 从服务器唯一ID
    server-id=2
    # 启用中继日志
    relay-log=mysql-relay

![20200525120433323.png][]

重启mysql容器

docker restart mysql_slave1

远程连接

jdbc:mysql://120.25.216.234:4501?serverTimezone=UTC

-- Slave1【120.25.216.234:4501】 上执行如下命令
    -- Master1【101.200.43.221:4500】
    CHANGE MASTER TO
        MASTER_HOST ='101.200.43.221', -- master1的ip地址
        MASTER_PORT = 4500, -- master1主数据库的端口
        MASTER_USER ='sync', -- master1主数据库上创建的账户
        MASTER_PASSWORD ='123456', -- master1主数据库上创建的密码
        MASTER_LOG_FILE ='mysql-bin.000001', -- File
        MASTER_LOG_POS =321; -- Position
    -- 启动从服务器复制功能
    start slave;
    -- 查看状态 Slave_IO_Running Slave_SQL_Running
    show slave status \G

![20200525135012613.png][]

## 2.3 Master2【121.36.33.154:5000】 ##

创建mysql容器

docker run \
    --name mysql_master2 \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --hostname=master2 \
    -p 5000:3306 \
    -di mysql:5.6

修改mysql配置文件

### 拷贝MySQL容器中的配置文件到宿主机
    docker cp mysql_master2:/etc/mysql/mysql.conf.d/mysqld.cnf master2_my.cnf
    ### 修改MySQL配置文件
    vi master2_my.cnf
    
    ### 将文件拷贝到容器中，替换原有的my.cnf
    docker cp master2_my.cnf mysql_master2:/etc/mysql/mysql.conf.d/mysqld.cnf
    
    ### 查看修改是否成功
    docker exec -it mysql_master2 /bin/bash -c 'cat /etc/mysql/mysql.conf.d/mysqld.cnf'

# 主服务器唯一ID
    server-id=3
    # 启用二进制日志
    log-bin=mysql-bin
    # 设置不要复制的数据库(可设置多个)
    binlog-ignore-db=mysql
    # 设置logbin格式
    binlog_format=STATEMENT
    # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    log-slave-updates
    # 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
    auto-increment-increment=2
    # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
    auto-increment-offset=2

![20200525120627868.png][]

重启mysql容器

docker restart mysql_master2

远程连接

jdbc:mysql://121.36.33.154:5000?serverTimezone=UTC

在主机MySQL里创建用户并授权

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY '123456';

查询Master2的状态；分别记录下File和Position的值；执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

##  2.4 Slave2【39.106.196.224:5001】 ##

创建mysql容器

docker run \
    --name mysql_slave2 \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --hostname=slave2 \
    -p 5001:3306 \
    -di mysql:5.6

修改mysql配置文件

### 拷贝MySQL容器中的配置文件到宿主机
    docker cp mysql_slave2:/etc/mysql/mysql.conf.d/mysqld.cnf slave2_my.cnf
    ### 修改MySQL配置文件
    vi slave2_my.cnf
    
    ### 将文件拷贝到容器中，替换原有的my.cnf
    docker cp slave2_my.cnf mysql_slave2:/etc/mysql/mysql.conf.d/mysqld.cnf
    
    ### 查看修改是否成功
    docker exec -it mysql_slave2 /bin/bash -c 'cat /etc/mysql/mysql.conf.d/mysqld.cnf'

# 从服务器唯一ID
    server-id=4
    # 启用中继日志
    relay-log=mysql-relay

![20200525120753974.png][]

重启容器

docker restart mysql_slave2

远程连接

jdbc:mysql://39.106.196.224:5001?serverTimezone=UTC

-- Slave2【39.106.196.224:5001】上执行
    -- Master2【121.36.33.154:5000】
    CHANGE MASTER TO
        MASTER_HOST ='121.36.33.154', -- master2的ip地址
        MASTER_PORT = 5000, -- master2主数据库的端口
        MASTER_USER ='sync', -- master2主数据库上创建的账户
        MASTER_PASSWORD ='123456', -- master2主数据库上创建的密码
        MASTER_LOG_FILE ='mysql-bin.000001', -- File
        MASTER_LOG_POS =321; -- Position
    -- 启动从服务器复制功能
    start slave;
    -- 查看状态 Slave_IO_Running Slave_SQL_Running
    show slave status \G

![20200525134922742.png][]

## 2.5 两个主机互相复制 ##

> **Master1【101.200.43.221:4500】上面执行**

-- Master1【101.200.43.221:4500】上面执行
    -- Master1【101.200.43.221】 复制Master2【121.36.33.154】
    CHANGE MASTER TO
        MASTER_HOST ='121.36.33.154', -- master2的ip地址
        MASTER_PORT = 5000, -- master2主数据库的端口
        MASTER_USER ='sync', -- master2主数据库上创建的账户
        MASTER_PASSWORD ='123456', -- master2主数据库上创建的密码
        MASTER_LOG_FILE ='mysql-bin.000001', -- File
        MASTER_LOG_POS =321; -- Position
    -- 启动复制
    start slave;
    -- 查看状态
    show slave status \G

![20200525134852601.png][]

**Master2【121.36.33.154:5000】上面执行**

-- Master2【121.36.33.154:5000】上面执行
    -- Master2【121.36.33.154】复制 Master1【101.200.43.221】
    CHANGE MASTER TO
        MASTER_HOST ='101.200.43.221', -- master1的ip地址
        MASTER_PORT = 4500, -- master1主数据库的端口
        MASTER_USER ='sync', -- master1主数据库上创建的账户
        MASTER_PASSWORD ='123456', -- master1主数据库上创建的密码
        MASTER_LOG_FILE ='mysql-bin.000001', -- File
        MASTER_LOG_POS =321; -- Position
    -- 启动复制
    start slave;
    -- 查看状态
    show slave status \G

![20200525134804865.png][]

## 2.6 验证双主双从 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 4][]

######################## Master1【101.200.43.221】上面执行 ########################
    ### 创建库
    create database if not exists test default char set utf8mb4;
    use test;
    ### 创建表
    drop table if exists t1;
    create table `t1`
    (
        `id` int(10) not null auto_increment,
        `a`  varchar(255) default null,
        `b`  int(10)      default null,
        primary key (`id`)
    ) engine = innodb
      charset = utf8mb4;
    
    /**
      由于主从复制的特点@@hostname两个数据库的值是不同的，通过查询的结果判断到底是主还是从
      如果不设置主键，删除主（写）数据库的这条记录，从（读）数据库这条数据不会同步删除
     */
    insert into test.`t1` (`id`, `a`, `b`) values (1, @@hostname, '123456');

######################## 验证主从是否生效 ########################
    -- 可以发现在主从数据库查询出来的这个值是不一样的
    select * from test.`t1` where id = 1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 5][]

[master1] : jdbc:mysql://101.200.43.221:4500?serverTimezone=UTC
    [slave1] : jdbc:mysql://120.25.216.234:4501?serverTimezone=UTC
    [master2] : jdbc:mysql://121.36.33.154:5000?serverTimezone=UTC
    [slave2] : jdbc:mysql://39.106.196.224:5001?serverTimezone=UTC

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

# 3 故障恢复 #

>  *  [https://www.cnblogs.com/xiao-xue-di/p/10073001.html][https_www.cnblogs.com_xiao-xue-di_p_10073001.html]
>  *  [https://blog.csdn.net/zhaofuqiangmycomm/article/details/105903564][https_blog.csdn.net_zhaofuqiangmycomm_article_details_105903564]
>  *  [https://blog.51cto.com/jschu/1964740][https_blog.51cto.com_jschu_1964740]

[https_yuanyu.blog.csdn.net_article_details_105730051]: https://yuanyu.blog.csdn.net/article/details/105730051
[https_yuanyu.blog.csdn.net_article_details_117069744]: https://yuanyu.blog.csdn.net/article/details/117069744
[https_blog.csdn.net_qq_40794973_article_details_113354544]: https://blog.csdn.net/qq_40794973/article/details/113354544
[https_yuanyu.blog.csdn.net_article_details_113062438]: https://yuanyu.blog.csdn.net/article/details/113062438
[https_www.imooc.com_read_43]: https://www.imooc.com/read/43
[https_dev.mysql.com_doc_refman_8.0_en_reset-slave.html]: https://dev.mysql.com/doc/refman/8.0/en/reset-slave.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200524210113250.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200524211111312.png]: https://img-blog.csdnimg.cn/20200524211111312.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200524212753278.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200525100813366.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200525120259124.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200525120433323.png]: https://img-blog.csdnimg.cn/20200525120433323.png
[20200525135012613.png]: https://img-blog.csdnimg.cn/20200525135012613.png
[20200525120627868.png]: https://img-blog.csdnimg.cn/20200525120627868.png
[20200525120753974.png]: https://img-blog.csdnimg.cn/20200525120753974.png
[20200525134922742.png]: https://img-blog.csdnimg.cn/20200525134922742.png
[20200525134852601.png]: https://img-blog.csdnimg.cn/20200525134852601.png
[20200525134804865.png]: https://img-blog.csdnimg.cn/20200525134804865.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200525135528835.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210610201559974.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_xiao-xue-di_p_10073001.html]: https://www.cnblogs.com/xiao-xue-di/p/10073001.html
[https_blog.csdn.net_zhaofuqiangmycomm_article_details_105903564]: https://blog.csdn.net/zhaofuqiangmycomm/article/details/105903564
[https_blog.51cto.com_jschu_1964740]: https://blog.51cto.com/jschu/1964740