MySQL---DBA---捌（主从复制基础）

一. 主从复制简介

  1. 主从复制是基于二进制日志进行复制
  2. 主库的修改操作都会记录二进制日志
  3. 从库会请求新的二进制日志并回放，最终达到主从数据同步
  4. 主从复制核心功能：辅助备份，处理物理损坏，扩展新型架构，高可用，高性能的分布式架构

二. 主从复制前提（搭建主从规划）

  1. 两台以上mysql实例。server_id,server_uuid不同
  2. 主库开启二进制日志
  3. 主库要有专用的复制用户（replication slave）
  4. 保证主从开始之前的某个时间点，从库和主库数据是一样的（备份主库资源到从库）
  5. 告知从库，主库的复制用户，password，IP，port，以及复制起点（change master to等信息）
  6. 保证线程开启（三个）：Dump thread ， IO thread ， SQL thread（start slave;）

三. 主从复制搭建（Classic replication）

3.1 实现多实例环境

1.1 MySQL的多实例管理
1.1.1 准备多个目录
mkdir -p /data/330{7,8,9}/data
1.1.2 准备配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
1.1.3 初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
1.1.4 systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf
1.1.5 授权
chown -R mysql.mysql /data/*
1.1.6 启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
1.1.7 验证多实例
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"

3.2 主从环境搭建

1. 启动2个多实例环境
systemctl start mysqld3307 （主）
systemctl start mysqld3308  （从）

2. 登录mysql实例进行检查
mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock

3. 检查主从server_id，以及主库二进制日志是否开启
mysql -S /data/3307/mysql.sock -e "select @@server_id; select @@log_bin;"
mysql -S /data/3308/mysql.sock -e "select @@server_id;"

4. 主库3307创建复制用户
grant replication slave on *.* to repl@'10.0.0.5%' identified by '123';
select user,host  from mysql.user where user='repl';

5. 备份主库资源告知从库
主库备份：mysqldump -uroot -p456  -A  -R -E --triggers --master-data=2 --single-transaction >/tmp/full.sql
再推送到从库
从库激活：1. mysql -S  /data/3308/mysql.sock  </tmp/full.sql
         2. mysql -S /data/3308/mysql.sock
            mysql> set sql_log_bin=0;
            mysql> source /tmp/full.sql


6. 告知从库关键信息（change master to），从库操作
起点: vim /tmp/full.sql 
显示：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=445;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',   #主库的ip
  MASTER_USER='repl',   #主库的复制用户
  MASTER_PASSWORD='123',   #复制用户密码
  MASTER_PORT=3307,   #主库端口
  MASTER_LOG_FILE='mysql-bin.000001',   #起点二进制文件
  MASTER_LOG_POS=445,   #起点position号
  MASTER_CONNECT_RETRY=10;

7. 开启主从专用线程（从库操作）
start slave;
监测：mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep Running:
显示：Slave_IO_Running: Yes
Slave_SQL_Running: Yes

四. 主从复制文件，线程，原理

4.1 主从复制涉及到的文件

1. 主库：
---binlog二进制文件（mysql-bin.000001）

2. 从库：
---relay log中继日志（db01-relay-bin.000001）,存储请求的binlog
---master.info信息文件（change master to），存放的是主库复制用户信息
---relay-log.info信息文件（上次已经回放relay的位置点）存放中继日志信息

4.2 主从复制涉及到的线程

1. 主库：
---Binlog_Dump_Thread：二进制日志投递线程（show processlist;）查看

2. 从库：
---Slave_IO_Thread
---Slave_SQL_Thread

4.3 主从复制原理

image.png
1.change master to 时，ip pot user password binlog position写入到master.info进行记录
2. start slave 时，从库会启动IO线程和SQL线程
3.IO_T，读取master.info信息，获取主库信息连接主库
4. 主库会生成一个准备binlog DUMP线程，来响应从库
5. IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6. DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
7. IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK(网络层面)给主库 ，主库工作完成
8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10.SQL_T回放完成之后，会更新relay-log.info文件。
11. relay-log会有自动清理的功能。
细节：
12.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求获取新数据

五. 主从复制监控

5.1 主从状态监控

1. 主库： show processlist;

2. 从库：show slave status \G;（显示所有主从有关信息）

（1）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Running:"
线程状态：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


（2）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Last"
线程报错信息：
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 

（3）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Master"
显示主库master各种信息：
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 445

（4）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Relay_log"
显示relay_log中继日志信息：
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 445

（5）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Seconds_Behind_Master:"
显示非人为操作的延时信息（需要避免）
Seconds_Behind_Master: 0

（6）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Delay: "
显示延时从库状态信息（认为设置）
SQL_Delay: 0
SQL_Remaining_Delay: NULL

（7）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Replicate "
显示过滤复制相关信息
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 

（8）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Gtid "
显示GTID复制相关状态信息
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

5.2 日志量监控

  1. 实际上就是如何判定主库二进制文件master_log与从库接收的二进制文件relay_log是不是同步执行的
  2. 主库： show master status;
    从库： show slave status \G;
    判断其中master_log与relay_log。
    其中master_log是IO线程向主库请求的二进制日志信息
    而relay_log是SQL线程进行回放的二进制日志。对比这两个即可
  3. 直接在从库（cat relay-log.info）文件，里边存放着回放日志，其中的relay-bin对应着mysql-bin。这两个相当于是一样的。直接那mysql-bin的数据与上述（ show slave status \G;）命令结果的Master_Log数据进行对比，数据相同则日志同步，不同则说明有差异。

六. 主从复制故障分析

6.1 IO线程故障（由功能参考）

1 .读取master.info（change master to...）
2. 连接主库：网络，防火墙，主库没启动，连接数上线等
总括：以上问题都会有Slave_IO_Running:Connection的线程错误，同时Last_IO_Error:xxx（会显示一些错误）
解决1：连接密码，用户，地址，端口等报错
从库：
---mysql -urepl  -p123 -h 10.0.0.51  -P3307

stop slave;   #首先停止线程
reset slave all;   #重置（之前操作都排除，谨慎操作）
CHANGE MASTER TO....   #重新告知从库主库信息
start slave;   #再次开启线程

解决2：连接数上限（Too Many  Connections）
set global max_connections = 300;（修改连接数上限）

3. 请求日志错误
故障原因：
---主库确实日志，1236的报错
---从库方面二进制日志位置点不对

注意：
在主从复制环境中，严令禁止主库中reset master;  可以选择expire进行定期清理主库二进制日志

解决：
若主库reset master重置了二进制日志，他的文件就会从000001开始，name之前change master to的信息则会不存在，发生错误，需要重新告知
stop slave;   #首先停止线程
reset slave all;   #重置（之前操作都排除，谨慎操作）
CHANGE MASTER TO....   #重新告知从库主库信息
start slave;   #再次开启线程

4. 接收日志
故障原因：
relay-log损坏，短节，找不到
解决方案：
重新change master to

5. 更新master.info
解决：重新搭建主从

6.2 SQL线程故障（由功能参考）

1. SQL线程功能：
---读写relay-log.info
---relay-log损坏，短节，找不到
---接收到的SQL无法执行

2. 导致SQL线程故障原因分析
---版本差异，参数设定不同，数据类型差异，SQL_MODE影响
---要创建的数据库对象已经存在
---要删除或者修改的对象不存在
---DML语句不符合表定义以及约束
归根揭底是因为从库发生了写入操作：
Last_SQL_Error:Error 'Can't create database  'db';database  exits'  on query
Default database: 'db'  Query: 'create database db'

3. 解决方法：
方法一：刷写同步
若从库和主库的二进制文件不一致，可以进行一下命令
stop slave;
set global sql_skip_counter = 1;   #可以一直往下刷，直到和主库一致
start slave;

方法二：修改配置
从库（/etc/my.cnf）
slave-skip-errors = 1032,1062,1007
---1032: 无法执行DML
---1062：主键冲突或约束冲突
---1007：对象已经存在
但是以上操作会有风险，最安全的做法就是重新搭建主从，把握一个原则，一切以主库为主

方法三：（一劳永逸）
1. 设置从库只读：
show variables like '%read_only%';
flush table with read lock;
set global read_only=on;
set global super_read_only=on;
注意：只会影响普通用户，对管理员用户无效

2. 加中间件：
读写分离

七. 主从延时

1. 主从延时简介
---主库做什么，从库就跟着做什么，但从库是在主库执行后的一段时间之后才开始执行的（非人为操作）
是我们应该避免的现象。

2. 主从复制现象
（1）最直观：主库做变更操作，从库看数据状态
（2）监控命令：只能证明是否有延时
命令：mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Seconds_Behind_Master:"
显示：Seconds_Behind_Master: 0
（3）计算二进制日志差异变化。

3. 主从延时原因
（1）外在因素
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容