浅谈MYSQL之级联复制(附带实验)

最新推荐文章于 2023-09-14 16:33:05 发布

lpb2019

最新推荐文章于 2023-09-14 16:33:05 发布

阅读量1.1k

点赞数 2

分类专栏： Mysql

本文链接：https://blog.csdn.net/lpb2019/article/details/103321214

版权

Mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

浅谈MYSQL之级联复制

级联复制

原理：级联结构就是通过减少直接从属于 Master 的 Slave 数量，减轻 Master 的压力，分散复制请求，从而提高整体的复制效率。
级联复制的应用场景
- 跨机房复制,A—>B–|-->C（C跨机房）,A挂,B提升为主,此时C不用做change master,缺点是复制延迟大,无法避免
- 库的拆分,某库压力很大,可以使用级联复制将其独立出去
中间从服务器需要注意的参数设置
缺点：
- 这种架构可以降低主库的负载，但是存在的问题是由于存在了多级复制，而主从复制由于是异步复制存在延迟问题，故最底层的从库会延迟更大，并且延迟随着级联层次的增大而增大，故如果读服务不能容忍太大的数据延迟，则不能采用这种架构模式。

[mysqld] 
log_bin                           / 开启二进制日志
log_slave_updates  
/ log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，
/ 但是，如果这个从服务器B是 服务器A的从服务器，同时还作为服务器C的主服务器，
/ 那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作

实验：实现级联复制

结构图如下
在这里插入图片描述

准备工作
- 三台干净环境的主机
- 时间同步完成
- 防火墙关闭
- SELINUX关闭
设置主MASTER配置

vim /etc/my.cnf
[mysqld]
server_id=1    / IP地址最后一位，局域网唯一ID
log-bin        / 二进制日志
systemctl start mariadb
/  创建复制权限账户
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.%.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       401 |
+--------------------+-----------+
1 row in set (0.00 sec)

二级级联机器172.20.54.2配置

vim /etc/my.cnf
[mysqld]
server_id=2
log-bin
log-slave-updates=1      / 从服务器写入二进制日志参数

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.20.54.1',
    ->   MASTER_USER='repliuser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000001',
    ->   MASTER_LOG_POS=401,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.54.1
                  Master_User: repliuser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 401
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 401
              Relay_Log_Space: 827
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: No query specified
/ 给三号机器建立访问账号权限
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.54.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec).
/ 查看二进制日志 POS点
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+

第三台机器设置

vim /etc/my.cnf
[mysqld]
server-id=3

systemctl start mariadb

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.20.54.2',
    ->   MASTER_USER='repliuser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000001',
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> START SLAVE;

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.54.2
                  Master_User: repliuser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 402
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 1
          Exec_Master_Log_Pos: 402
              Relay_Log_Space: 1270
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

ERROR: No query specified

测试过程

/ 在1号master主机
MariaDB [(none)]> CREATE DATABASE TESTDB1;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TESTDB1            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
/ 2号级联SLAVE

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TESTDB1            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
/ 3号SLAVE机器

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TESTDB1            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)