MySQL笔记 | 5.在Docker中搭建主备

系列文章目录

提示：所有文章的目录
1.了解SQL的执行过程
2.Docker下搭建MySQL&查看BinLog文件
3.MySQL中涉及的锁
4.MySQL数据库设计-字段类型

---

前言

---

提示：以下是本篇文章正文内容

一、主备什么，为什么要用主备？

主备通俗的来说，就是数据复制。建立一个备库，和主数据库一模一样的数据库环境。
那主备能给我们做什么呢？

1. 准实时备份，保证数据的完整性。当主宕机后，可以进行快速切换。
2. 做读写分离，将读写压力分散。
3. 进行业务扩展。

二、主备的流程

原理：是基于二进制日志（bin log）进行数据增量同步。

1. Master 服务器会把数据变更产生的二进制日志（bin log）通过 Dump 线程发送给 Slave 服务器。
2. Slave 服务器中的 I/O 线程负责接受二进制日志，并保存为中继日志（relay log）。
3. Slave再起一个线程读取中继日志（relay log），并在Slave执行，完成同步。

各个日志的含义

三、在Docker下搭建主备环境

步骤一：新建一个备库

我们在之间的文章中已经在Docker中搭建了一个3306的数据环境，为了满足主备的需要。我们在搭建一个备库的环境。

docker run -p 3307:3306 --name mysql_slave -v /opt/mysql/slave/conf:/etc/mysql/conf.d -v /opt/mysql/slave/logs:/logs -v /opt/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

步骤二：查看是否安装成功

[root@iZ8vb7xgouhcfciakm9plqZ /]# docker ps 
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
8b3fe3977bed   mysql          "docker-entrypoint.s…"   7 seconds ago   Up 5 seconds   33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   mysql_slave
f7e9a7e047ac   mysql          "docker-entrypoint.s…"   6 days ago      Up 6 days      0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
935090dd3061   apachephp:v1   "/usr/bin/supervisord"   8 days ago      Up 8 days      0.0.0.0:8080->80/tcp, :::8080->80/tcp                  vigorous_borg
ff7f2bea7cc3   e121d5f99e1e   "/usr/bin/supervisord"   8 days ago      Up 8 days      80/tcp                                                 apache

没什么问题，确定了有两个端口的环境，3306和3307.

步骤三：查看是不是得到了不同的IP

[root@iZ8vb7xgouhcfciakm9plqZ /]# docker inspect --format='{{.NetworkSettings.IPAddress}}' f7e9a7e047ac
172.17.0.4
[root@iZ8vb7xgouhcfciakm9plqZ /]# docker inspect --format='{{.NetworkSettings.IPAddress}}' 8b3fe3977bed
172.17.0.5

已经分配了 两个Ip,Master 服务器地址是 172.17.0.4 ，Slave 服务器地址是 172.17.0.5，MySQL 的端口号都为 3306。

步骤四：进行主环境的my.cnf文件配置修改

进入Bash

[root@iZ8vb7xgouhcfciakm9plqZ /]# docker exec -it f7e9a7e047ac /bin/bash
root@f7e9a7e047ac:/# mysql -uroot -p

修改my.cnf

[mysqld]
## 设置server_id，一般设置为IP，同一局域网内注意要唯一
server_id=100  
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql  
## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=edu-mysql-bin  
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062  

root@8b3fe3977bed:/etc/mysql# vi my.cnf
bash: vi: command not found

apt-get update

apt-get install vim

步骤五：创建一个salve的用户

创建数据同步用户，为了让备库来监听

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';   
Query OK, 0 rows affected (0.00 sec)

步骤六：进入备库进行，相似的配置修改
同样的步骤操作slave
修改my.cnf配置

[mysqld]
## 设置server_id，一般设置为IP,注意要唯一
server_id=101  
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin  
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062  
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin  
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## 防止改变数据(除了特殊的线程)
read_only=1  

结束后重启容器.

步骤七：主备建立关系（核心步骤）

核心步骤成败在此一举了
Master：

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      711 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

看一下File和Position的值。
！！！重点

我们刚刚查询到了IP用上了。在从节点上执行

mysql> change master to master_host='172.17.0.4', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=711, master_connect_retry=30;
Query OK, 0 rows affected, 10 warnings (0.04 sec)

解释

master_host: Master 的IP地址
master_user: 在 Master 中授权的用于数据同步的用户
master_password: 同步数据的用户的密码
master_port: Master 的数据库的端口号
master_log_file: 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos: 从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry: 当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。

成功

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.4
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000003
          Read_Master_Log_Pos: 323
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 328
        Relay_Master_Log_File: edu-mysql-bin.000003
             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: 323
              Relay_Log_Space: 541
              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: 100
                  Master_UUID: eb9ceac6-ca92-11eb-8f75-0242ac110004
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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 row in set, 1 warning (0.00 sec)

遇到问题

1. Slave_IO_State:为空，可能是slave未执行start slave;
2. 重置Matser操作
   1. 停止已经启动的绑定,stop slave
   2. 重置绑定,reset master
   3. 执行复制主机命令,change master to master_host …
3. 异常：Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

Slave_IO_State: Connecting to master
                  Master_Host: 172.17.0.4
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 711
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
            Last_IO_Error: error connecting to master 'slave@172.17.0.4:3306' - retry-time: 30 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决方式：
错误原因：密码加密方式不支持，
在主服务器执行以下命令：
ALTER USER ‘slave’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
FLUSH PRIVILEGES;

四、主备数据不一致，怎么办？

利用GTID模式强化数据库的主备一致

配置：

-- 是否开启GTID复制功能
gtid_mode = on

-- 启动强制GTID的一致性，自动根据GTID进行同步数据,无需查找File和Pos点
enforce_gtid_consistency = 1

-- 5.7.6以上中默认TRUE
binlog_gtid_simple_recovery = 1

-- 默认开启，启动自动relay log恢复
relay_log_recovery = ON

-- 将master.info保存在表中
master_info_repository = TABLE 

-- 将relay.info保存在表中
relay_log_info_repository = TABLE

五、延迟优化

主备服务器时间，可能会因为某个事务，在数据库执行时间过长，而出现了数据延迟，我们在进行数据操作的时候，尽量避免大事务的产生，将大事务切分为小事务去执行。

我们可以升级MySQl的版本，在大于5.6之后的版本中，采用了并行复制，并行的操作，很大程度上提高了性能，减小延迟。
再加上一个延迟监控的保险，建立心跳表，去监听主从延迟的时间。

六、进行高可用架构的设计

1. 高可用设计主要分为了两种

1. 无状态，只需要进行故障转移或者剔除，常见的有NGINX
2. 有状态，我们需要从业务和数据维度去考虑

2.数据核对

高可用的设计，并不能保证万无一失，在最后我们需要去做一些兜底的操作，数据核对就很关键了

1. 在业务上，开发需要去做数据对账，比如，销售情况和总数是否相等
2. 在数据上，DBA可以做数据抽样对比，来看是否一致