十三、 MySQL主从同步

目录

准备实验环境

主机名 主机IP xx
admin 192.168.4.9 客户端
node1 192.168.4.11 Master节点
node2 192.168.4.12
node3 192.168.4.13
node4 192.168.4.14
node5 192.168.4.15
  • 准备实验环境

    1. 创建5台node节点
    2. 使用scp命令将安装包拷贝至node节点
    3. 为所有node节点安装mysql服务
    4. 启动mysql服务
    5. 设置密码为'Yue3314526!'
    
  • 示例

    [root@node1 ~]# tar -xf mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar 
    [root@node1 ~]# yum -y install *.rpm
    [root@node1 ~]# systemctl start mysqld
    [root@node1 ~]# grep "password" /var/log/mysqld.log
    [root@node1 ~]# mysql -uroot -p"yb/EtXtWq3jD"
    mysql> alter user root@'localhost' identified by "Yue3314526!";
    

基本结构模式

MySQL主从同步

  • 主从同步介绍

    • 实现数据自动同步的数据结构
    • 主服务器:接受客户端访问连接
    • 从服务器:自动同步主服务器数据
  • 拓扑结构

    主服务器: 192.168.4.11
    从服务器: 192.168.4.12
    客户端: 192.168.4.9
    
    十三、 MySQL主从同步
  • 主从同步原理

    Master
      - 启动binlog日志
      
    Slave
      - Slave_IO: 复制master主机binlog日志文件里的SQL命令到本机的relay-log文件里
      - Slave_SQL: 执行本机relay-log文件里的SQL语句,实现与Master数据一致
    
    十三、 MySQL主从同步

配置主从同步(一主一从)

  • 具体要求如下

    数据库服务器"192.168.4.11"配置为主数据库服务器
    数据库服务器"192.168.4.12"配置为从数据库服务器
    客户端"192.168.4.9"测试配置
    
  • 配置主服务器192.168.4.11

    启动binlog日志、授权用户、查看binlog日志信息
    
    # 启动binlog日志
    [root@node1 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=11
    log_bin=masert11
    
    [root@node1 ~]# systemctl restart mysqld
    [root@node1 ~]# mysql -uroot -p'Yue3314526!' -e "show master status"
    [root@node1 ~]# ls /var/lib/mysql/masert11.*
    
    # 添加授权用户
    mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
    
    
  • 配置从服务器192.168.4.12

    - 设置server_id
    - 确保与主服务器数据一致
    - 指定主库信息
    - 启动slave程序
    - 查看状态信息
    
    • 设置server_id

      [root@node2 ~]# vim /etc/my.cnf
      [mysqld]
      server_id=12
      
      [root@node2 ~]# systemctl restart mysqld
      
    • 确保与主服务器数据一致

      在主服务器上备份数据,将备份文件拷贝给从服务器
      在从服务器使用备份文件恢复数据
      在从服务器查看备份文件中的binlog日志信息
      
      • 主库备份数据

        [root@node1 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb.sql
        [root@node1 ~]# scp /root/alldb.sql root@192.168.4.12:/opt/
        
        # --master-data 记录当前备份数据对应的日志消息
        # 大概就是记录当前时间的偏移量,防止搭建期间插入的数据未能记录
        
      • 从服务器恢复数据

        [root@node2 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb.sql
        [root@node2 ~]# mysql -uroot -p'Yue3314526!' -e "select count(*) from db2.user"
        
        [root@node2 ~]# grep "masert" /root/db2.sql 
        CHANGE MASTER TO MASTER_LOG_FILE='masert11.000001', MASTER_LOG_POS=441;
        # --master-data记录了一个"MASTER_LOG_POS"的偏移量,主从同步将从偏移量"MASTER_LOG_POS"开始同步,防止缺漏
        
    • 指定主服务器信息

      mysql> change master to
          -> master_host="192.168.4.11",				# 主库IP地址
          -> master_user="repluser",					# 主库授权用户
          -> master_password="123qqq...A",			# 授权用户密码
          -> master_log_file="masert11.000001",		# 主库日志文件
          -> master_log_pos=441;						# 日志偏移量
      
      # 这个偏移量,参考db2.sql中记载的'MASTER_LOG_POS'偏移量,或者备份时定位
      
      mysql> start slave;			# 启动slave进程
      
      # Master的信息会自动保存到'/var/bin/mysql/master.info'文件
      # 若更改主库信息时,应先执行'stop slave;',修改后 再执行'start slave;'
      # mysql> stop slave;		# 停止slave进程
      # mysql> change master to master_log_file="masert11.000002";	# 修改一条
      # mysql> start slave;		# 开启slave进程
      
    • 查看Slave状态信息

      mysql> show slave status \G;
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 192.168.4.11		# 主服务IP
                        Master_User: repluser			# 用户
                        Master_Port: 3306				# 主服务器端口
                      Connect_Retry: 60
                    Master_Log_File: masert11.000001
                Read_Master_Log_Pos: 441
                     Relay_Log_File: node2-relay-bin.000002
                      Relay_Log_Pos: 319
              Relay_Master_Log_File: masert11.000001
                   Slave_IO_Running: Yes			# 重点 必须是Yee
                  Slave_SQL_Running: Yes			# 重点 必须是Yes
      
      
    • 排错

      Slave_IO_Running: No	# 查看下面错误字段Last_IO_Error的提示
      
    • 相关文件(存放在数据库目录下)

      master.info					# 主库配置信息(主库的配置相关信息)
      relay-log.info				# 中继日志配置信息
      主机名-relay-bin.xxxxxx	  # 中继日志
      主机名-relay-bin.index		  # 索引文件
      
      删除这四个文件可以使从服务器的身份切换为独立服务器
      # 特殊情况,可以删除这四个文件(重启mysqld),进行重新配置(例: 中继日志与中继日志配置文件不符)
      
      [root@node2 ~]# cat /var/lib/mysql/master.info
      
  • 验证主从同步配置

    • 在主服务器添加授权用户给客户端连接使用

      mysql> create database db3;
      mysql> grant all on db3.* to admin@"%" identified by "123qqq...A";
      mysql> grant select,insert on db2.* to admin@"%";
      
      # 如果之前实验有建立重复的admin用户,删掉
      mysql> drop user admin@"192.168.4.%";
      
    • 客户端使用授权用户连接主服务器,访问数据

      [root@node2 ~]# mysql -h'192.168.4.11' -uadmin -p'123qqq...A'
      
      mysql> show grants;
      
      mysql> insert into db2.user(username) values("lisi");
      mysql> select * from db2.user where username="lisi";
      
      
    • 在从服务器主机查看数据(能看到和主服务器同样的数据)

      [root@node2 ~]# mysql -uroot -p'Yue3314526' -e "select * from db2.user"
      
      # 在实验环境中,出现了偏移量与文件不符合情况,重建主服务器的binlog日志,并重新配置从服务器的偏移量解决
      # Read_Master_Log_Pos 与 Position 应该是相同的
      

扩展结构类型

MySQL一主多从结构

  • 拓扑结构

    从 < -- 主 -- > 从
            ↓
            从
    
    客户端: admin(192.168.4.9)
    主服务器: node1(192.168.4.11)
    从服务器1: node2(192.168.4.12)
    从服务器2: node3(192.168.4.13)
    
  • 构建思路

    配置主服务器
      - 启动binlog日志、授权用户、查看binlog日志信息
    配置从服务器
      - 设置server_id
      - 确保与主服务器数据一致
      - 指定主库信息
      - 启动slave程序
      - 查看状态信息
    
  • 实现过程

    基于一主一从结构,再添加一个从服务器即可,不需要过多操作
    这里就不进行详细说明了
    # 但是你需要手动实现一遍
    
    [root@node1 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb_two.sql
    [root@node1 ~]# scp /root/alldb_two.sql root@192.168.4.13:/opt/
    
    [root@node3 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=13
    [root@node3 ~]# systemctl restart mysqld
    [root@node3 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb_two.sql
    
    [root@node3 ~]# vim /opt/alldb_two.sql
    CHANGE MASTER TO MASTER_LOG_FILE='masert11.000001', MASTER_LOG_POS=1422;	# 查看偏移量信息
    
    [root@node3 ~]# mysql -uroot -p'Yue3314526!'
    # ----- 进入mysql操作 -----
    
    mysql> change master to
        -> master_host="192.168.4.11",
        -> master_user="repluser",
        -> master_password="123qqq...A",
        -> master_log_file="masert11.000001",
        -> master_log_pos=1422;
    
    mysql> start slave;
    mysql> show slave status \G;
    
  • 验证测试

    客户端访问主服务器(11)进行增删改查验证
    在slave(12,13)可以查看到同样的数据
    

MySQL主从从结构

  • 拓扑结构

    主 <-- 从 <-- 从
    
    客户端: admin(192.168.4.9)
    主服务器: node1(192.168.4.11)
    从服务器1: node2(192.168.4.12)
    从服务器2: node4(192.168.4.14)
    
    # node2这这个结构中即做node4节点的主服务器,也要做node1节点的从服务器
    # 该实验沿用之前的一主一从配置进行扩展
    
    
  • 配置主服务器(node2)

    [root@node2 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=12
    log_bin=master12
    log_slave_updates		# 允许级联更新(主从从必须指定)
    
    [root@node2 ~]# systemctl restart mysqld
    
    # 添加授权用户(由于node2是node1的完全备份后恢复库,所以该步骤可以省略)
    # mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
    
    mysql> show master status;
    
    [root@node2 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb.sql
    [root@node2 ~]# scp alldb.sql root@192.168.4.14:/opt/
    
  • 配置从服务器(node4)

    [root@node4 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=14
    [root@node4 ~]# systemctl restart mysqld
    
    [root@node4 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb.sql 
    
    # 查看主(node2)节点的日志偏移量
    [root@node2 ~]# mysql -uroot -p'Yue3314526!' -e "show master status"
    Position: 154
    
    # ---------- 登录node4MySQL服务进行操作 ------------
    
    mysql> change master to
        -> master_host="192.168.4.12",
        -> master_user="repluser",
        -> master_password="123qqq...A",
        -> master_log_file="master12.000001",
        -> master_log_pos=154;
    
    mysql> start slave;
    mysql> show slave status \G;
    
  • 验证效果

    1. 对node1数据进行增删改查,然后对node2数据和node4数据节点数据查看进行验证
    
    2. 在node1服务添加访问数据的授权
    mysql> grant all on db_tes.* to yueyue@"%" identified by "123qqq...A";
    
    3. 客户端远程连接node1,访问数据
    [root@admin ~]# mysql -uyueyue -h"192.168.4.11" -p'123qqq...A'
    mysql> show grants;
    mysql> show databases;
    mysql> create database db_tes;
    mysql> create table db_tes.a(id int);
    mysql> insert into db_tes.a values(10);
    
    4. 客户端远程连接node2,node4,查看数据和表的情况,会看到和node1一样的数据
    mysql> select * from db_tes.a;
    

MySQL双主结构(互为主从)

  • 要求

    把数据库node5和node6配置为MySQL主主结构
    
    步骤如下
      - 创建两台新的虚拟机 并配置IP地址 node5("192.168.4.15/24"),node6("192.168.4.16/24")
      - 分别在2台数据库服务器安装软件、启动服务、管理初始登录密码
      - 配置数据库服务node5
      - 配置数据库服务node6
      - 把node6配置为node5的从服务器
      - 把node5配置为node6的从服务器
      - 分别在2台服务器查看slave进程的状态信息
    
    # 注意 克隆镜像之后,MySQL实现主从必须保证uuid的不相同 (MySQL5.6之后引入了uuid的概念)
    	- mysql> show variables like '%server_uuid%';
    	- vim /var/lib/mysql/auto.cnf 
    # 如果uuid相等导致报错,可以手动修改一下(长度不变)
    
  • 分别为node5与node6开启binlog日志

    [root@node5 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=15
    log_bin=master15
    [root@node5 ~]# systemctl restart mysqld
    
    [root@node6 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=16
    log_bin=master16
    [root@node6 ~]# systemctl restart mysqld
    
  • 配置node6为node5的从服务器

    [root@node5 ~]# mysql -uroot -p'Yue3314526!'
    
    # 查看node5上MySQL的偏移量
    mysql> show master status;
    
    # node5服务器创建授权账户
    mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
    
    [root@node6 ~]# mysql -uroot -p'Yue3314526!'
    
    mysql> change master to
        -> master_host="192.168.4.15",
        -> master_user="repluser",
        -> master_password="123qqq...A",
        -> master_log_file="master15.000001",
        -> master_log_pos=154;
    
    mysql> start slave;
    
    mysql> show slave status \G
    
  • 配置node5为node6的从服务器

    mysql> show master status;
    mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
    
    [root@node5 ~]#  mysql -uroot -p'Yue3314526!'
    
    mysql> change master to
        -> master_host="192.168.4.16",
        -> master_user="repluser",
        -> master_password="123qqq...A",
        -> master_log_file="master16.000001",
        -> master_log_pos=154;
    
    mysql> start slave;
    
    mysql> show slave status \G;
    

主从同步复制模式

类型

  • 异步复制模式(默认)Asynchronous replication

    主库执行完一次事务后,立即将结果返给客户端,并不关心从库是否已经接收并处理。
    
  • 全同步复制模式Fully synchronous replication

    当主库执行完一次事务,且所有从库都执行了该事务后才将结果返回给客户端。
    
    关键字: MySQL组同步
    
  • 半同步复制模式Semisynchronous replication

    介于异步复制和全同步复制之间。
    主库在执行完一次事务后,等待至少一个从库接收到并写到"relay log"中才将结果返回给客户端。
    
    半同步复制功能由模块提供
    

半同步复制模式

  • 说明

    该实验沿用了之前的MySQL双主结构(互为主从)
    因为每个node服务器既是主库,又是从库,所以下列模块配置都要进行配置
    
  • 查看是否允许动态加载模块

    # 默认允许
    mysql> show variables like "have_dynamic_loading";
    
  • 命令行加载插件

    使用数据库管理员root用户访问服务

    # install plugin 插件类型 SONAME "插件名"
    
    # 主服务器上执行
    mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";
    
    # 从服务器上执行
    mysql> install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";
    
    mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
     - plugin_status: ACTIVE
     - plugin_status: ACTIVE
    
  • 启用半同步复制

    半同步复制,默认是关闭的

    # 主服务器执行
    mysql> set global rpl_semi_sync_master_enabled=1;
    
    # 从服务器执行
    mysql> set global rpl_semi_sync_slave_enabled=1;
    
    mysql> show variables like "rpl_semi_sync_%_enabled";
    
  • 永久启用半同步复制(写入配置文件)

    修改主配置文件/ect/my.cnf

    [mysqld]下方添加

    # 该配置是当一个服务器即为主又为从的情况下进行的配置方法
    
    plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    rpl_semi_sync_master_enabled=1;
    rpl_semi_sync_slave_enabled=1;
    
    # 配置完成后,可以重启MySQL服务 然后查看相关配置状态进行判断
      - show variables like "rpl_semi_sync_%_enabled";
    
    # 该配置是在主从分开的情况下分别对应情况写入配置文件
    
    # 主服务器
    plugin-load=rpl_semi_sync_master=semisync_master.so
    rpl_semi_sync_master_enabled=1;
    
    # 从服务器
    plugin-load=rpl_semi_sync_slave=semisync_slave.so
    rpl_semi_sync_slave_enabled=1;
    
上一篇:MySQL主从


下一篇:基于STM32的0.96寸OLED显示屏显示数据