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