MySQL主从复制

目录

• 一、主从复制简介
• 二、搭建主从复制的过程
• 三、主从复制搭建
  • 3.1 环境描述
  • 3.2 修改MySQL主配置文件
  • 3.3 主库中创建复制专用用户
  • 3.4 备份主库并恢复到从库
  • 3.5 告知从库复制信息并开启专用线程
  • 3.6 检查线程状态
  • 3.7 验证主从复制
• 四、主从复制原理
  • 4.1 主从复制中涉及到的文件和线程
    • 1）线程
    • 2）文件
  • 4.2 主从复制原理
• 五、主从复制监控、分析、处理

一、主从复制简介

• 基于二进制日志恢复的；
• 主库的修改操作记录会记录二进制日志；
• 从库会请求新的二进制日志并回放，最终达到主从数据同步；
• 主从复制核心功能：辅助备份，处理物理损坏；

二、搭建主从复制的过程

1）至少两台mysql实例，server_id，server_uuid不同；
2）主库开启二进制日志功能；
3）创建专用的复制用户；
4）保证主从开启之前的某个时间点，从库数据和主库是一致的；
5）告知从库，复制user、password、IP、Port以及复制的起点；
6）从库开启从库模式（start slave）；
7）确认线程：主库（dump thread）、从库（ID thread、SQL thread）；

三、主从复制搭建

3.1 环境描述

system	hostname	IP	service
centos 7.5	db01	192.168.1.1	mysql 5.7.29（主）
centos 7.5	db02	192.168.1.2	mysql 5.7.29（从）

以上两台主机都已经搭建完整MySQL，最初的环境！

3.2 修改MySQL主配置文件

主库（192.168.1.1）
[root@db01 ~]# vim /etc/my.cnf
server_id=1      #指定server—_id，注意server_id是唯一的
log_bin=/usr/local/mysql/data/mysql-bin
#开启二进制日志功能，并指定日志存放路径及日志名称前缀
[root@db01 ~]# systemctl restart mysqld
#重启MySQL服务
从库（192.198.1.2）
[root@db02 ~]# vim /etc/my.cnf 
server_id=2          #切记不可和主库进行冲突
[root@db02 ~]# systemctl restart mysqld  

3.3 主库中创建复制专用用户

[root@db01 ~]# mysql -uroot -p123
mysql> grant replication slave on *.* to test@'192.168.1.%' identified by '123';

3.4 备份主库并恢复到从库

[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers > /opt/full.sql
#对主库进行全库备份
[root@db01 ~]# scp /opt/full.sql root@db02:/root
#将sql脚本传到从库上
[root@db02 ~]# mysql -uroot -p123 < full.sql 
#从库导入脚本
[root@db02 ~]# grep "\-- CHANGE MASTER TO" full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1310;
#从导入的脚本中获取同步日志的起点信息

3.5 告知从库复制信息并开启专用线程

[root@db02 ~]# mysql -uroot -p123
mysql> help change master to
#如果忘记这条命令建议使用help的方式获取命令格式
mysql> change master to
   master_host='192.168.1.1',              #主库的IP地址
   master_user='repl',                     #专用复制的用户名
   master_password='123',                  #用户对应的密码
   master_port=3306,                       #主库数据库监听的端口
   master_log_file='mysql-bin.000001',     #同步主库的日志文件
   master_log_pos=1310,                    #日志中的起点位置
   master_connect_retry=10;                #如果连接失败重试次数
mysql> start slave;                        #开启从库专用线程

3.6 检查线程状态

mysql> show slave status \G
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#确保IO线程、SQL线程是开启的状态

3.7 验证主从复制

[root@db01 ~]# mysql -uroot -p123
mysql> create database test01 charset utf8mb4;
mysql> use test01
mysql> create table t1(id int);
mysql> insert into t1 values (100),(200),(300);
mysql> select * from t1;
+------+
| id   |
+------+
|  100 |
|  200 |
|  300 |
+------+
#主库插入数据
[root@db02 ~]# mysql -uroot -p123 -e 'select * from test.t1;'
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+
#该数据是通过导入SQL脚本实现的
[root@db02 ~]# mysql -uroot -p123 -e 'select * from test01.t1;'
+------+
| id   |
+------+
|  100 |
|  200 |
|  300 |
+------+
#该数据是通过主从复制得到的
#从库验证数据

至此MySQL主从已经搭建完成！

四、主从复制原理

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

1）线程

主库：dump thread
从库：io thread、sql thread

2）文件

主库：
二进制日志文件（mysql-bin.000001）
从库：
中继日志文件（db02-relay-bin.000001）
主库信息记录文件（master.info）
记录中继日志文件中应用情况信息（relay-log.info）

4.2 主从复制原理

主从复制原理图：

主从复制过程：

1）change master to 时，ip port user password binlog position写入到master.info进行记录；
2）start slave 时，从库会启动IO线程和SQL线程；
3）从库IO线程，读取master.info信息，获取主库信息连接主库；
4）主库会生成一个准备DUMP线程，来响应从库；
5）从库IO线程根据master.info记录的binlog文件名和position号，请求主库DUMP线程最新日志信息；
6）主库DUMP线程检查主库的binlog日志，如果有新的，DUMP线程会将新的日志文件传送给从库的IO线程；
7）从库的IO线程将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成；
8）从库IO线程将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，从库IO线程工作完成；
9）从库SQL线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log（写入从库日志文件中）；
10）从库SQL线程回放完成之后，会更新relay-log.info文件；

细节：

1）主库一旦有新的日志生成，会发送“信号”给dump线程，从库IO线程再进行请求；
2） 从库relay-log会有自动清理的功能；

五、主从复制监控、分析、处理

主库方面

mysql> show processlist;     #查看线程列表
+----+------+------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
| Id | User | Host       | db   | Command     | Time | State                                                         | Info                  |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
|  6 | repl | db02:59320 | NULL | Binlog Dump | 3801 | Master has sent all binlog to slave; waiting for more updates | NULL                  |
|  8 | root | localhost  | NULL | Query       |    0 | starting                                                      | show full processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
#每个从库都会有一行dump相关的信息
#如果显示非以上信息,说明主从之间的关系出现了问题    
#显示的两行信息，第一行是从库，第二行是主库信息
mysql> show slave hosts;        #查看从库主机
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | b91d331a-7d83-11ea-8ec1-000c29b71bbd |
+-----------+------+------+-----------+--------------------------------------+

从库方面

mysql> show slave status \G
#主库的信息，来自于master.info文件
Master_Host: 192.168.1.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1924

#从库relay-log的执行情况，来自于relay.info文件，一般用于判断主从延迟
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 934
Relay_Master_Log_File: mysql-bin.000001

#已经执行到主库的位置信息
Exec_Master_Log_Pos: 1924

#落后主库多少秒
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: 
Executed_Gtid_Set: