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 是否同步生成数据库、表、数据。