DBA（MySQL）学习-主从复制基础
2019-06-27 本文已影响0人
慕男
1. 主从复制
1.1 介绍
依赖于二进制日志的，“实时”备份的多节点架构
1.2 主从复制的前提（如何搭建主从复制）
（1）至少2个实例
（2）不同的server_id
（3）主库要开启二进制日志
（4）主库需要授权一个副库专用的复制用户
（5）主库数据备份
（6）开启专用复制线程
2. 搭建主从复制
2.1 搭建多实例
（1） 准备多个目录
mkdir -p /data/330{7,8,9}/data
（2）准备配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/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=/app/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=/app/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
（3）初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
（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=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
（5）授权
chown -R mysql.mysql /data/*
（6）启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
（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"
2.2 检测server_id
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"
2.3 检查3307（主库）的二进制日志情况
mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"
2.4 主库创建复制用户（3307）（用户=repl）
mysql [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
2.5 进行主库数据备份
mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/3307full.sql
2.6 恢复数据到从库（3308）
[root@db01 /data]# mysql -S /data/3308/mysql.sock
mysql [none]>set sql_log_bin=0;
mysql [none]>source /tmp/3307full.sql;
2.7 通知从库一些复制的信息
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='master2-bin.002',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
mysql [(none)]>start slave;
3. 主从复制工作过程
3.1 名词认识
文件：
主库：binlog
从库：relay-log（中继日志）、master.info（主库信息文件）、relay-log.info（中继日志应用信息）
线程：
主库：binlog_dump_thread （二进制投递线程）查看方法：mysql [(none)]>show processlist;
从库：IO_Thread：从库的I/O线程：请求和接受binlog
SQL_Thread：从库的SQL线程：回放日志
3.2 工作原理
image.png
（1）从库执行change master to 语句，会立即将主库信息记录到从库的master.info中
（2）从不高库执行 stat slave ，会立即生成TO_T和SQL_T
（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会更新master.info文件重置binlog位置点信息
（9）从库IO_T会将binl，写入到relay-log中
（10）SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
（11）SQL_T按照位置点往下执行relaylog日志。
（12）SQL_T执行完成后重新更新relay-log.info
（13）relay-log会有自动清理的功能。
细节：
主库发生了信息的修改，更新二进制日志完成后，会发送一个“信号”给Dump_T,Dump_T通知给IO_T线程
4. 主从复制监控及故障处理分析
4.1 主从监控
主库：show processlist;
正常状态：
mysql [(none)]>show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 19 | repl | 10.0.0.51:28599 | NULL | Binlog Dump | 1920 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 20 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
从库：show slave status\G
*************************** 1. row ***************************
主库的信息：
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51 --->主库的IP
Master_User: repl --->复制用户名
Master_Port: 3307 --->主库的端口
Connect_Retry: 10 ---> 断练之后重试次数
Master_Log_File: mysql-bin.000002 --->主库获取到的binlog文件名
Read_Master_Log_Pos: 444 --->已经获取到的binlog的位置号
从库的relaylog的信息：（relay-log.info）
Relay_Log_File: db01-relay-bin.000002 --->从库已经运行过的relaylog的文件名
Relay_Log_Pos: 320 --->从库已经运行过的relaylog的位置点
从库复制线程工作状态：
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:
从库延时主库的时间：
Seconds_Behind_Master: 0
从库线程报错详细信息：
Last_IO_Errno: 0 --->IO报错的号码
Last_IO_Error: --->IO报错的具体信息
Last_SQL_Errno: 0 --->SQL报错的号码
Last_SQL_Error: --->SQL线程报错的具体原因
延时从库：
SQL_Delay: 0 --->延时从库设定的时间
SQL_Remaining_Delay: NULL --->延时操作的剩余时间
GTID复制信息：
Retrieved_Gtid_Set: --->接收到的GTID的格式
Executed_Gtid_Set: --->执行的GTID的个数
4.2 主从故障的分析及处理
从库复制线程的状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库线程报错详细信息：
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
4.2.1 IO线程故障
（1）连接主库连接不上
出现
Slave_IO_Running: Connecting
原因：
网络不通、防火墙、
IP不对、port不对、用户名，密码不对、skip_name_reslove、连接数上限
判断于以下错误对比：
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P3308
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '10.0.0.51' is not allowed to connect to this MySQL server
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110)'
[root@db01 ~]# mysql -urepl -p111 -h 10.0.0.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'10.0.0.51' (using password: YES)
[root@db01 ~]# mysql -urepll -p123 -h 10.0.0.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repll'@'10.0.0.51' (using password: YES）
找到原因后，如何处理？
stop slave;
reset slave all ;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
（2）请求新的binlog
出现
Slave_IO_Running: No
分析：
错误1：
日志名不对
从库信息：
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 444
于全库备份文件对比
20 --
21
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444;
23
24 --
错误2：主库使用了reset master;语句
主库出现mysql [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
要想把最近的数据也能传到从库上就要从154开头的时候开始
处理方法：
处理：
mysql -S /data/3308/mysql.sock
stop slave;
reset slave all ;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
（3）写relaylog
（4）更新master.info
4.2.2 SQL线程故障
（1）读relay-log.info
读relay-log.，并执行日志
更新relay-log.info
以上文件损坏，最好是重新构建主从
（2）为什么一条SQL语句执行不成功
1.主从数据库版本差异较大
2.主从数据库配置参数不一致（例如：sql_mode等）
3.想要创建的对象已经存在
4.想要删除或修改的对象不存在
5.主键冲突
6.DML语句不符合表定义及约束时
归根揭底的原因都是由于从库发生了写入操作.
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
5. 主从延时原因分析
从库延时主库的时间
Seconds_Bxehind_Master：0 从库延时主库的时间（秒为单位）
5.1 主库方面
（1）日志写入不及时
sync_binlog=1
（2）主库并发业务较高
“分布式”架构
（3）从库太多
级联主从
对于Classic Replication：
主库是有能力并发运行事务的，但是在Dumo_T在传输日志的时候，是以事件为单元传输日志的，所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。
怎么处理：从5.6开始加入GTID，在复制时，可以将串行的传输模式变成并行的
除了GTID支持，还需要双一保证。
5.2 从库方面
Classic Replication：
SQL线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程>>>在5.6中出现了多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS，真正实现了事务级别的并发SQL
6. 延时从库
6.1 数据损坏
物理损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。
6.2 设置理念
对SQL线程进行延时设置
6.3 延时多久合适？
一般企业，3-6小时
6.4 如何设置？
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
6.5 如何使用延时从库
6.5.1 思路
mysql [(none)]>
mysql [(none)]>create database delay charset utf8mb4;
mysql [(none)]>use delay;
mysql [delay]>create table t1(id int);
mysql [delay]>insert into t1 values(1),(2),(3);
mysql [delay]>commit;
drop database delay;
发现问题啦：
（1）停止SQL线程，停止主库业务
（2）模拟SQL手工恢复relaylog到drop之前的位置点
（3）截取relaylog日志，找到起点（relay-log.info）和终点（drop操作）
（4）恢复截取的日志，验证
开始处理：
1. 停从库的SQL线程
mysql -S /data/3308/mysql.sock
mysql> stop slave sql_thread;
2. 找relaylog的起点和终点
起点：
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476
终点：
mysql> show relaylog events in 'db01-relay-bin.000002'
| db01-relay-bin.000002 | 1149 | Query | 7 | 2036 | drop database delay
3. 截取日志
[root@db01 ~]# mysqlbinlog --start-position=476 --stop-position=1149 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
4. 恢复
mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/relay.sql
7. 过滤复制
主库：（了解）
binlog_do_db
binlog_ignore_db
从库：在SQL线程回放日志时，进行控制过滤
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
例子：
只需要复制xyz库的数据到从库
[root@db01 ~]# vim /data/3308/my.cnf
replicate_do_db=xyz
systemctl restart mysqld3308
注意：如果有多个库的话，写多行即可。
8. 半同步复制
加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';
和传统复制区别：
是一个插件形式提供的功能
主库： ack_receiver
从库: ACK_send
主库的 ack_re 线程 只有接收到 从库发来的ACK确认，主库事务才能commit成功
从库的 Acksend只有等relaylog落地才能发送ACK
主库只会等10s 如果ACK还没收到的，会自动替换为异步复制。
9.GTID复制（高可用环境）---为下节做准备
9.1 重点
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1 --->强制刷新从库二进制日志：1. 高可用（MHA）2. 级联复制的中间库
9.2 清理环境
pkill mysqld
\rm -rf /data/*
\rm -rf /data/*
mkdir -p /data/mysql/data
mkdir -p /data/binlog/
chown -R mysql.mysql /data
9.3 准备配置文件
主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
9.4 初始化数据(所有节点)
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
9.5 启动数据库并创建复制用户
/etc/init.d/mysqld.server start
db01 [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
9.6 两个从库开启主从
mysql -e "change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;start slave; "
mysql -e "show slave status \G"|grep Yes
