MySQL双主双从实现方式

双主双从（MM-SS）

前言

• 避免单一主服务器宕机，集群写入能力缺失

• 从 1 复制 主1 ，从 2 复制 主 2

  主 1 复制 主 2，主 2 复制主 1

  也就是 主 1 和主 2 互为主从。主1主2互为主从，

  是为了以下情景，主1挂了，主2自动升级为主数据库，当主1恢复后，主1则变成次主数据库。
  图示
  

• 四台服务器，每台服务器上安装了 mysql8 数据库

举例说明

1、在主1创建了一个mydb2数据库，从1自动“复制”主1生成数据库，

2、因为主2也是主1 的从数据库，所以主2也“复制”主1生成数据库，

3、主2有了数据库，从2自动“复制”主2生成数据库；

最后，四台服务器数据库都创建了数据库。

操作步骤

• 清理四台数据库服务器数据

• 数据库恢复到初始情况，未安装的安装，安装的清除数据

  • systemctl stop  mysqld
    

  • rm  -rf   /var/lib/mysql/*
    

  • systemctl start mysqld
    

  • grep  password  /var/log/mysqld.log
    

  • mysqladmin 修改默认密码
    

主1

• master1下的my.cnf 添加如下配置

• 清除上次实验的配置：

  这四项全部删除
  

# bin log 日志
# 开启二级制日志 并声明二进制日志名称
log-bin=/var/lib/mysql/binlog
# 服务id
server-id=1
#主从复制忽略的数据库 这些默认库都有 没有必要都拷贝 所以忽略
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#开启主从复制的数据库 如果还需要其他库就继续用这个属性 跟其他的数据库名即可
binlog-do-db=mydb2# bin log 日志格式
#STATEMENT:记录主库执行的SQL复制到从库; 调用时间函数时会导致主从数据不一致
#		   即 以sql语句的形式记录
#ROW:记录主库每一行的变化;效率低
#MIXED:修复一些主从数据不一致情况;本地变量调用还会存在问题;@@hostname#日志格式  默认statement
binlog_format=statement
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
#跳过主从复制中遇到的所有错误或指定类型的错误
slave_skip_errors=1062
#在作为从数据库时候，有写入操作也要更新二进制日志文件
log-slave-updates
#标识自增长字段每次递增的量，也就是步长 日志中的标记at2 at4
auto-increment-increment=2
#表示自增长从哪个数开始
auto-increment-offset=1
#这条语句，课程中并未出现。为增强练习环境所设置。含义是增加mysql的连接数
max_connect_errors=1000

systemctl   restart mysqld

主 2

• master2 my.cnf 添加如下配置

• 清楚之前的配置：

# bin log 日志
log-bin=/var/lib/mysql/binlog
# # 服务id
server-id=2
# #主从复制忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# #开启主从复制的数据库
binlog-do-db=mydb2
# # bin log 日志格式
# #STATEMENT:记录主库执行的SQL复制到从库; 调用时间函数时会导致主从数据不一致
# #ROW:记录主库每一行的变化;效率低
# #MIXED:修复一些主从数据不一致情况;本地变量调用还会存在问题;@@hostname
binlog_format=statement
# #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
# #跳过主从复制中遇到的所有错误或指定类型的错误
slave_skip_errors=1062
# #在作为从数据库时候，有写入操作也要更新二进制日志文件
log-slave-updates
# #标识自增长字段每次递增的量，也就是步长
auto-increment-increment=2
# #表示自增长从哪个数开始
auto-increment-offset=2

• 主1主2配置的不同地方在：server-id 和 auto-increment-offset

• systemctl   restart mysqld
  

  • 从1 slave1

  • my.cnf 添加如下配置

  • # 服务id
    server-id=3
    # 用中继日志 指定中继日志的名字位relay
    relay-log=mysql-relay
    

  • systemctl restart mysqld
    

• 从2 slave2

  • my.cnf 添加如下配置

  • # 服务id
    server-id=4
    #启用中继日志 指定中继日志名称
    relay-log=mysql-relay
    

  • # systemctl restart mysqld
    

• 创建同步账号并授权

  • 主1、主2 数据库：

    创建主主同步账号repl_user

    主从同步账号slave_sync_user

    master1和master2都分别执行一遍下面内容

    mysql> CREATE USER 'repl-user'@'192.168.145.%' IDENTIFIED WITH mysql_native_password BY 'Lixinyi@123';
    Query OK, 0 rows affected (0.03 sec)mysql> CREATE USER 'slave-sync-user'@'192.168.145.%' IDENTIFIED WITH mysql_native_password BY 'Lixinyi@123';
    Query OK, 0 rows affected (0.01 sec)mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT  ON *.* TO 'repl-user'@'192.168.145.%';Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave-sync-user'@'192.168.145.%';Query OK, 0 rows affected (0.00 sec)

  • 注释：

    WITH mysql_native_password 表示这是一个本地账号 如果有人来用这个账号登陆，那么服务器只在本地  							 寻找密码看是否匹配。

• 配置主从同步

  • 主1(M) --> 从1(S)

  • master1 -->slave1

    • 主1 mysql 查看2进制日志位置

      mysql>show master status;
      

      

• 记住 binlog 文件 和 偏移量，后面会用到

  ​

• 从1mysql (slave1)

  mysql> CHANGE MASTER TO MASTER_HOST='192.168.145.141', MASTER_USER='slave-sync-user', MASTER_PASSWORD='Lixinyi@123', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1209;
  

  mysql> start slave;
  

  mysql> show slave status \G;
  

  
  遇到不是两个 Yes （下面截图这种情况）怎么办，别慌，执行下面命令：
  

stop slave;reset master;然后再  CHANGE MASTER .....

• 主2(M) --> 从2(S)

• master2 -->slave2

  • 主2 mysql -作为主服务器

    show master status;
    

    

• • 从 2 mysql -作为从服务器

    CHANGE MASTER TO MASTER_HOST='192.168.145.142', MASTER_USER='slave-sync-user', MASTER_PASSWORD='Lixinyi@123', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=157;
    

    start slave;
    

    show slave status \G;
    

• 主1(M) --> 主2(S)

  • 主1 mysql 下

    • show master status;
      

      
      主2 mysql 下

• CHANGE MASTER TO MASTER_HOST='192.168.145.141', MASTER_USER='rep-user', MASTER_PASSWORD='Lixinyi@123', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1305;
  

• start slave;
  

• show slave status \G;
  

  

• • 主2(M) --> 主1(S)

    • 主2 mysql 下

      • show master status;
        

    • 主1 mysql 下

      • CHANGE MASTER TO MASTER_HOST='192.168.145.142', MASTER_USER='repl-user', MASTER_PASSWORD='Lixinyi@123', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=156;
        

      • start slave;
        

      • show slave status \G;
        

• 主1 mysql 下创建数据库 mydb2，创建表 books，插入一条数据：

  • create database mydb2;
    

  • create table mydb2.books(id int primary key auto_increment, name varchar(50));
    

  • insert into mydb2.books(name) values("MySQL 8");

select * from mydb2.books;

​

• 查看 主1 主2 从1 从2 是否同步生成数据库、表、数据。