vip2-day24 MYSQL 主从架构_vip部署mysql 还有主从关系吗

概念

MYSQL 集群模式

概念：

1. 在企业生产环境中，MYSQL 数据库通常会部署多台（多个实例），防止单点问题。这种同时部署多台 MySQL 的方式称为 MYSQL 集群模式。
2. MYSQL 集群模式的种类主要有 MYSQL 主从复制、MYSQL 读写分离、MYSQL Router 集群、MYSQL MHA、MYSQL DRBD、MYSQL 分库分表等。

MYSQL 主从复制

MYSQL 主从复制是 MYSQL 集群模式中应用最广泛的一种。

可解决的问题：

• 数据备份：MYSQL 主存模式通常会有两个节点，即 Master 主节点和 Slave 从节点。Slave 从节点会定时从 Master 主库同步数据，进而实现主库数据的备份。
• 读写分离：业务系统后端向 MYSQL 数据库发送读写请求时，读取数据可以在 Slave 从库中操作，写入数据可以在 Master 主库中操作。
• 单点故障：业务系统连接 MYSQL 数据库时，如果 Master 库宕机，业务系统可以连接 Slave 库，从而将故障对业务系统的影响降到最低。即冗余互备。

主从原理：

条件：

1. 至少需要两台服务器（2 个实例），其中一台为 Master 库，另一台为 Slave 库。
2. 主从数据同步是一个 异步 复制的过程，要实现复制首先需要在 Master 上开启 bin-log 日志功能，bin-log 日志用于记录在 Master 库中执行的增、删、改、更新操作的 sql 语句。
3. 整个过程需要开启三个线程，本别是 Master 开启 I/O 线程，Slave 开启 I/O 线程和 SQL 线程。

原理：

1. 在 Slave 上执行slave start，Slave IO 线程会通过在 Master 上创建的授权用户连接至 Master，并请求 Master 从指定的文件和 position 发送 bin-log 日志内容。
2. Master 接收到来自 Slave IO 线程的请求后，Master IO 线程根据 Slave 指定的 bin-log 日志和 position，返回内容给 Slave IO 线程。返回的信息中除了 bin-log 日志内容外，还有 Master 最新的 bin-log 文件名以及在 bin-log 中的下一个更新 position。
3. Slave IO 线程接收到信息后，将接收到的日志内容依次添加到 Slave 端的 relay-log 文件的末端，并将读取到的 Master 端的 bin-log 文件名和 position 记录到 master.info 文件中，以便能在下一次同步时告知 Master 从相应的 bin-log 文件及 position 返回数据。
4. Slave SQL 线程检测到 relay-log 中内容有更新时，会立即将 relay-log 中新增内容解析成在 Master 中真实执行时的 SQL 语句，并将这些 SQL 语句在 Slave 中执行。执行成功后，Slave 库与 Master 库数据同步完成。

主从异常

分析：网络不稳定，数据量过大等原因导致。

解决方案：

1. 从库忽略错误，继续执行。

   • 适用于主从库数据内容相差不大，或者数据同步要求不严格的情况。
   • 操作步骤：
     1. 停止同步：> stop slave;
     2. 跳过导致错误的 SQL 语句：> global sql_slave_skip_counter = [n]
        • n 代表忽略几条错误。
        • 此操作会导致主从差异。
     3. 启动同步：> start slave;

2. 重做主从，完全同步。

   • 适用于主从数据内容相差很大，或要求主从数据完全一致的情况。
   • 操作步骤：
     1. 为主库添加全局只读锁：> flush tables with read lock;
     2. 使用 mysqldump、xtrabackup 工具导出主库完整的数据并备份。也可使用 shell 脚本或 python 脚本实现定时备份。
     3. 将完整的数据全部导入从库。
     4. 重新配置主从同步并确保同步线程正常运行。
     5. 解除主库的只读锁：> unlock tables;

方法 2 同样适用于新增从库。

降低主从延迟

方法：

1. 采用高配的硬件设备，尤其是硬盘，可采用 SSD 固态硬盘，做 Raid 10 阵列。
2. 采用大内存服务器。
3. 主从服务器采用万兆网卡做局域网数据传输。
4. 优化 MYSQL 配置文件。
5. 采用多线程同步并行复制。
6. 优化 SQL 语句。

实例

主从架构搭建

准备阶段

1. 两台 CentOS 7 虚拟机。

2. 配置好实验环境。

3. 克隆系统。

4. 配置克隆系统。

5. 配置 yum 源。

6. 下载 rpm 包：$ wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

7. 生成源：$ rpm -ivh mysql80-community-release-el7-7.noarch.rpm

8. 更新 GPG-KEY：$ rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

9. 关闭防火墙和 selinux。

   • 关闭 selinux：先执行$ setenforce 0，再修改配置文件/etc/selinux/config

     # disabled 关闭
     # enforcing 开启
     SELINUX=disabled
     1
     2
     3

   • 关闭防火墙：

     1. 停止服务：$ systemctl stop firewalld
     2. 禁止自启：$ systemctl disable firewalld

10. 安装 mysql 8：$ yum -y install mysql-community-server

2~4 步可使用 xshell -> 工具 -> 发送键输入到 -> 所有会话，同步完成。

搭建阶段

1. 编辑配置文件：/etc/my.cnf

   • Master 编辑内容：

     [mysqld]
     # 数据库 GID 不可重复
     server-id = 110
     # 日志文件前缀
     log-bin = mysql-bin
     1
     2
     3
     4
     5

   • Slave 编辑内容：

     [mysqld]
     # 数据库 GID 不可重复
     server-id = 111
     1
     2
     3

2. 重启数据库：$ systemctl restart mysqld

3. 查看主库 Master 状态：> show master status;

   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000001 |      477 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1
   2
   3
   4
   5

4. 查看主库日志文件：$ mysqlbinlog /var/lib/mysql/mysql-bin.000001 | more

   • at [n]：起始点为 n。
   • end_log_pos [n]：结束点为 n。

5. 登录主库创建从库连接账号：> create user 'tongbu'@'%' identified with mysql_native_password by 'WahahaAyaya123!';

6. 给账号授权：> grant replication slave on *.* to 'tongbu'@'%';

7. 查看主库 Master 状态：

   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000001 |     1573 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1
   2
   3
   4
   5

8. 登录从库设置 Master 信息：

   > change master to master_host='192.168.0.110',
   master_user='tongbu',
   master_password='WahahaAyaya123!',
   master_log_file='mysql-bin.000001',
   master_log_pos=1573;
   1
   2
   3
   4
   5

9. 从库开启同步：> start slave;

10. 查看 slave 状态信息：> show slave status\G

    -- Slave_IO_Running 和 Slave_SQL_Running 值都为 Yes 表示搭建成功
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    1
    2
    3

11. 测试：

    1. 在 Master 库创建数据库：> create database test_02;
    2. 查看 Slave 库：> show databases;

利用 binlog 还原数据

1. 创建测试数据库：> cerate database test_01;

2. 删除测试数据库：> drop database test_01;

3. 查看 binlog 文件，记录需要还原的起始点和结束点：at 554，end_log_pos 671

4. 将要恢复的内容导入到文件中：$ mysqlbinlog --start-position=554 --stop-position=671 /var/lib/mysql/mysql-bin.000001 > bak.sql

5. 恢复数据：mysql -p'WahahaAyaya123!' < bak.sql

6. 登录数据库查看内容是否恢复：

    ```sql {.line-numbers}
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test_01            |
    +--------------------+
    5 rows in set (0.00 sec)
    ```
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13

忽略同步错误

1. 删除 Master 库数据：> drop database test_02;

2. 查看 Slave 状态：> show slave status;

   -- 主从关系已被破坏
   Slave_IO_Running: Yes
   Slave_SQL_Running: No
   Last_Error: Coordinator stopped because there were error(s) in the worker(s). 
   The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at 
   master log mysql-bin.000005, end_log_pos 1108. See error log and/or 
   performance_schema.replication_applier_status_by_worker table for 
   more details about this failure or others, if any.
   1
   2
   3
   4
   5
   6
   7
   8

3. 关闭 Slave 同步：> stop slave;

4. 忽略该错误：> set global sql_slave_skip_counter = 1;

5. 启动 Slave 同步：> start slave;

6. 再次查看 Slave 状态：

   -- 主从关系已恢复
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   1
   2
   3

Slave 状态信息

解析

               Slave_IO_State: Waiting for source to send event -- I/O 线程与 Master 的连接状态
                  Master_Host: 192.168.0.110  -- Master IP 
                  Master_User: tongbu         -- Master 用户
                  Master_Port: 3306           -- Master 端监听端口
                Connect_Retry: 60             -- 超时重连间隔
              Master_Log_File: mysql-bin.000005 -- I/O 线程读取的 Master 二进制日志名 
          Read_Master_Log_Pos: 157              -- I/O 线程读取的 Master 二进制日志点位
               Relay_Log_File: centos7-111-relay-bin.000006 -- SQL 线程读取的中继日志名
                Relay_Log_Pos: 326    -- SQL 线程读取的中继日志点位
        Relay_Master_Log_File: mysql-bin.000005 -- SQL 线程执行的 Master 二进制日志名
             Slave_IO_Running: Yes    -- I/O 线程是否启动并成功连接到 Master
            Slave_SQL_Running: Yes    -- SQL 线程是否启动
              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      -- SQL_SLAVE_SKIP_COUNTER 设置的值
          Exec_Master_Log_Pos: 157
              Relay_Log_Space: 758
              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      -- SQL 线程和 I/O 线程之间的时间差，单位为秒，常用于检查主从延迟
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: 110
                  Master_UUID: f846acf1-7321-11ed-9d3f-000c29ef3be5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

查看主从延迟

mysql -u[用户名] -p[密码] -e 'show slave status\G' | grep 'Seconds_Behind_Master'
1

查看同步线程

mysql -u[用户名] -p[密码] -e 'show slave status\G' | grep -Ew 'Slave_IO_Running|Slave_SQL_Running'
1

故障排查

从库无法同步

表现：

               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.0.110
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 985
               Relay_Log_File: centos7-111-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
1
2
3
4
5
6
7
8
9
10
11
12

排查：

1. 账号错误。
2. 密码错误。
3. binlog 文件名错误。
4. position 错误。
5. Master 端口错误。
6. Master 防火墙未关闭。

解决方案：

• 问题 1~5： 核查信息，重设 slave 参数。
• 问题 6：关闭防火墙，重启 slave，问题解决。