8、主从复制基础

2021-02-26 本文已影响0人一个反派人物

1. 介绍

两台或以上数据库实例，通过二进制日志，实现数据的“同步”关系

2 主从复制前提（搭建过程）

需要2台以上数据库实例，时间同步，网络通畅，Server_id不同，区分不同角色（主库、从库）
主库开启binlog，建立专用复制用户
保证主从开启之前的某个时间点，从库数据是和主库一致(补课)
告知从库：复制user、passwd、IP port 以及复制起点(change master to)
丛库开启专用的复制线程(三个):Dump thread、IO thread、SQL thread 开启(start slave)

3 主从搭建

使用mysql多实例搭建，3307为主库，3308和3309为从库

3.1 数据库多实例准备

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309

3.2 检查多实例的server_id要求不同

mysql -uroot -p123 -S /data/3307/mysql.sock -e "select @@server_id;"
mysql -uroot -p123 -S /data/3308/mysql.sock -e "select @@server_id;"
mysql -uroot -p123 -S /data/3309/mysql.sock -e "select @@server_id;"

3.3 检查主库binlog开启

mysql -uroot -p123 -S /data/3307/mysql.sock -e "select @@log_bin"
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

3.4 主库建立复制用户

建立复制用户repl，并授予用户replication slave权限，拥有该权限的账号才能进行主从复制。

[none]>create user repl@'10.0.0.%' identified by '123';
[none]>grant replication slave on *.* to repl@'10.0.0.%' ;

3.5 主库备份恢复到从库

主库全备

mysqldump -uroot -p123 -S /data/3307/mysql.sock -A --master-data=2 --single-transaction > /tmp/full.sql

从库恢复到主库全备时的状态

mysql -S /data/3308/mysql.sock -uroot -p123 < /tmp/full.sql
mysql -S /data/3309/mysql.sock -uroot -p123 < /tmp/full.sql

3.6 从库配置复制信息

#从库中执行
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=2129,
  MASTER_CONNECT_RETRY=10;

#参数解释
  MASTER_HOST：          主库的IP地址或域名
  MASTER_USER：          主库上用于复制的账户
  MASTER_PASSWORD：      主库上复制账户的密码
  MASTER_PORT：          主库的端口号
  MASTER_LOG_FILE：      从库已经记录的主库binlog文件，也就是主库全备时的binlog文件，是主从复制的起点文件
  MASTER_LOG_POS：       从库已经记录的主库position号，也就是主库全备时的position号，是主从复制的起点位置
  MASTER_CONNECT_RETRY： 连接失败的重试次数

其中MASTER_LOG_FILE和MASTER_LOG_POS两个信息需要从全备文件中获取

[root@db01 ~]$ grep "\-- CHANGE MASTER TO" /tmp/full.sql

3.7 从库中启动专用复制线程

[(none)]>start slave;

3.8 验证主从状态

[root@db01 3307]$ mysql -S /data/3308/mysql.sock -uroot -p123 -e "show slave status\G;" | grep "Running:"
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#需要 Slave_IO_Running 和 Slave_SQL_Running 都为Yes状态

3.9 改变配置后的复制状态重置

如果从库的复制信息改变可以运行一下命令进行主从复制重置

[(none)]>stop slave;
[(none)]>reset slave all;
[(none)]>CHANGE MASTER TO
...;
[(none)]>start slave;

3.10 主从状态错误

3.10.1 Slave_IO_Running: No

查看从服务器的错误日志

2021-02-24T11:30:33.053002Z 14 [ERROR] Slave I/O for channel '': The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. Error_code: 1593

原因：因为主服务器开启了GTID，从服务器没有开启。从服务器也开启GTID后，状态恢复

4 主从复制原理

4.1 主从复制涉及的文件和线程

4.1.1 线程

主库：

Binlog_dump_Thread：
- 作用：用来接收从库的请求，并且投递binlog给从库，启用几个从库开启几个线程
- 查看：mysql> show processlist;

从库：

IO Thread
- 作用：请求binlog，接收binlog
- 查看：mysql> show slave status
SQL Thread
- 作用：回放relay-log
- 查看：mysql> show slave status

4.1.2 文件

主库：

主库binlog文件：mysql-bin.000001

从库：

relay-log文件：

文件名：db01-relay.000001
作用：存储从主库接收的binlog
位置：默认存储在数据目录下，可手工定义参数relay_log_basename=/data/3308/data/db01-relay-bin，从而指定文件位置和前缀名称

master.info：

作用：记录连接主库的信息，已经接收到的binlog位置点信息
位置：默认存储在数据目录下，可手工定义参数master_info_repository=FILE/TABLE来指定信息存在文件中或存在表中

relay-log.info：

作用：记录从库已经回放到的relay-log的位置点
位置：默认存储在数据目录下，可手工定义参数relay_log_info_repository=FILE/TABLE来指定信息存在文件中或存在表中

4.2 主从复制原理图

文字说明：

S：执行Change master to，IP、Port、USER、Password、binlog位置信息写入到master.info文件，执行start slave（启动SQL、IO线程）
S：连接主库
M：分配Dump_Thread，专门和S的IO_Thread通讯，启用几个从库开启几个线程
S：IO线程请求新binlog日志
M：Dump线程，接收请求，截取binlog日志返回给S的IO线程
S：IO线程接收binlog，日志放在TCP/IP缓存中，此时网络层面返回ACK给主库。主库工作完成。
与第8步一起进行
S：IO线程将接收的binlog最终写入到relay-log当中，并更新master.info文件的binlog位置信息。IO线程工作结束
S：SQL线程读取relay-log.info，获取上次执行到的位置点
S：SQL线程向后执行新的relay-log
S：SQL线程再次更新relay-log.info

小细节：

S：relay-log 参数relay_log_purge=ON，定期删除应用过的relay-log
M：Dump线程实时监控主库binlog的变化，如果有新变化，发信号给从库。

5 主从监控

5.1 主库方面

mysql> show processlist;
mysql> show slave hosts;

5.2 从库方面

mysql> show slave status \G;

#主库相关信息，来自master.info
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 194

#从库的relay-log执行情况，来自于relay-log.info，一般用于判断主从延时
#目前执行到db01-relay-bin.000008的Position 407，对应主库mysql-bin.000004的Position 194
               Relay_Log_File: db01-relay-bin.000008
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000004
          Exec_Master_Log_Pos: 194
        Seconds_Behind_Master: 0

#从库线程状态，具体报错信息
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 

#过滤复制相关信息，只复制主库的某些库
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 

#延时从库的配置信息
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

#GTID相关复制信息
           Retrieved_Gtid_Set: 802c2d06-7673-11eb-a360-000c2951bbce:12
            Executed_Gtid_Set: 802c2d06-7673-11eb-a360-000c2951bbce:1-12

6 主从管理命令

6.1 从库线程管理

#启动所有线程
mysql> start slave;
#关闭所有线程
mysql> stop slave;
#单独控制sql线程
mysql> start slave sql_thread;
mysql> stop slave sql_thread;
#单独控制io线程
mysql> start slave io_thread;
mysql> stop slave io_thread;

6.2 解除从库身份

mysql> reset slave all;