八mysql主从复制基础 _Mysql主从

一年好景君须记，最是橙黄橘绿时。这篇文章主要讲述八mysql主从复制基础相关的知识，希望能为你提供帮助。
一、企业高可用性标准
1、全年无故障率(非计划内故障停机)
99.9%----> 0.001*365*24*60=525.6min
99.99%----> 0.0001*365*24*60=52.56 min
99.999%----> 0.0001*365*24*60=5.256 min
2、高可用架构方案
负载均衡:有一定的高可用性
LVSnginx
主备系统:有高可用性,但是需要切换,是单活的架构
KA ,MHA, MMM
真正高可用(多活系统):
mha、mha+atlas、PXC、MGR
二、主从复制简介
1、基础介绍
(1)基于二进制日志复制的
(2)主库的修改操作会记录二进制日志
(3)从库会请求新的二进制日志并回放,最终达到主从数据同步
(4)主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
2. 主从复制前提(搭建主从的过程)
(1)两台以上mysql实例 ,server_id,server_uuid不同
(2)主库开启二进制日志
(3)专用的复制用户
(4)保证主从开启之前的某个时间点,从库数据是和主库一致(补课)
(5)告知从库,复制user,passwd,IP port,以及复制起点(change master to)
(6)线程(三个):Dump threadIO threadSQL thread 开启(start slave)
3. 主从复制搭建(Classic replication)
(1)准备多实例
mkdir /data/3307/data /data/3308/data -p
mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/3308/data
cat > /data/3307/my.cnf < < EOF
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/3307/data
port=3307
socket=/data/3307/mysql.sock
server_id=7
log_bin=/data/3307/mysql-bin
binlog_format=row
log_error=/data/3307/mysql.log
[mysql]
socket=/data/3307/mysql.sock
EOF
cat > /data/3308/my.cnf < < EOF
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
server_id=8
log_bin=/data/3308/mysql-bin
binlog_format=row
log_error=/data/3308/mysql.log
[mysql]
socket=/data/3308/mysql.sock
EOF
chown -R mysql.mysql /data/3307
chown -R mysql.mysql /data/3308
mysqld --defaults-file=/data/3307/my.cnf &
mysqld --defaults-file=/data/3308/my.cnf &
mysql -S /data/3307/mysql.sock -e \'select @@server_id; \'
mysql -S /data/3308/mysql.sock -e \'select @@server_id; \'
(2)主库中创建复制用户
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@\'192.168.10.%\' identified by \'123\';
flush privileges;
select user,host from mysql.user;
(3)创建模拟数据
create database qingchen charset utf8mb4;
use qingchen
create table test1(id int,name varchar(10));
insert into test1 values (1,\'zs\');
insert into test1 values (2,\'ls\');
insert into test1 values (3,\'ww\');
insert into test1 values (4,\'zl\');
(4)备份主库并恢复到从库
mysqldump -S /data/3307/mysql.sock -A --master-data=https://www.songbingjia.com/android/2 --single-transaction-R -E --triggers > /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=\'mysql-bin.000004\', MASTER_LOG_POS=2057;

文章图片

mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/full.sql
set sql_log_bin=1;
(5)告知从库关键复制信息
ip port userpasswordbinlog position
mysql -S /data/3308/mysql.sock
CHANGE MASTER TO
MASTER_HOST=\'192.168.10.129\',
MASTER_USER=\'repl\',
MASTER_PASSWORD=\'123\',
MASTER_PORT=3307,
MASTER_LOG_FILE=\'mysql-bin.000004\',
MASTER_LOG_POS=2057,
MASTER_CONNECT_RETRY=10;
(6)开启主从专用线程
start slave ;
(7)检查复制状态
show slavestatus \\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
三、主从复制工作(过程)原理
1、原理

文章图片

(1)从库执行change master to 命令(主库的连接信息+复制的起点)
(2)从库会将以上信息,记录到master.info文件
(3)从库执行 start slave 命令,立即开启IO_T和SQL_T
(4)从库IO_T,读取master.info文件中的信息,获取IP,PORT,User,Pass,binlog的位置信息
(5)从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
(6)IO_T根据binlog的位置信息(mysql-bin.000004 , 2057),请求主库新的binlog
(7)主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T
(8)IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info
(9)IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.
(10)SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息
(11)SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息
(12)从库会自动purge回放过的relaylog,进行定期清理
补充说明:
一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.
2、线程
主:
DUMP THREAD
从:
IOTHREAD
SQL THREAD
3、文件
主:
mysql-bin.000001
从:
localhost-relay-bin.000001===> 中继日志
master.info===》主库信息记录日志

文章图片

relay-log.info===> 记录中继应用情况信息和binlog对应信息

文章图片

四、主从故障监控\\分析\\处理
1、线程相关监控
主库:
show full processlist;

文章图片

从库:
show full processlist;

文章图片

show slave status \\G

文章图片

主库相关信息监控
Master_Host: 192.168.10.129
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2327
从库中继日志的应用状态
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 590
从库复制线程有关的状态
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:
主从延时相关状态(非人为)
Seconds_Behind_Master: 0
延时从库有关的状态(人为)
SQL_Delay: 0
SQL_Remaining_Delay: NULL
GTID 复制有关的状态
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
2、主从复制故障分析
合理处理方法:
把握一个原则,一切以主库为准进行解决.
如果出现问题,尽量进行反操作
最直接稳妥办法,重新构建主从
(1)主库连接数上线,或者是主库太繁忙Too many connections
set global max_connections=10000;
(2)线程故障
SQL线程功能：
读写relay-log.info
relay-log损坏,断节,找不到
接收到的SQL无法执行
导致SQL线程故障原因分析：
版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
要创建的数据库对象,已经存在
要删除或修改的对象不存在
DML语句不符合表定义及约束时.
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error \'Can\'t create database \'db\'; database exists\' on query. Default database: \'db\'. Query: \'create database db\'
处理方法(以从库为核心的处理方案)：
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
(3)以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
一劳永逸的方法:
可以设置从库只读.
show variables like \'%read_only%\';
注意：
只会影响到普通用户，对管理员用户无效。
加中间件
读写分离
五、主从延时监控及原因
主库做了修改操作,从库比较长时间才能追上.
1、外在因素
网络
主从硬件差异较大
版本差异
主从的参数配置
从库和主库的索引不一致
2、主库
(1) 二进制日志写入不及时
[rep]> select @@sync_binlog;
sync_binlog=1
(2) 默认情况下dump_t 是串行传输binlog
主库并发事务量大,主库可以并行,传送时是串行
主库发生了大事务,由于dump_t是串行传送,导致传送日志较慢,会产生阻塞后续的事务.
解决方案:
5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
大事务拆成多个小事务,可以有效的减少主从延时.
(3) 主库极其繁忙
慢语句
锁等待
从库个数
网络延时
3、从库
(1) 传统复制(Classic)中
如果主库并发事务量很大,或者出现大事务
由于从库是单SQL线程,导致,不管传的日志有多少,只能一次执行一个事务.
解决方案:
5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
5.7 版本开始GTID之后,在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
大事务拆成多个小事务,可以有效的减少主从延时.
【八mysql主从复制基础】4、主从延时的监控
show slavestatus\\G
Seconds_Behind_Master: 0
主库方面原因的监控
主库:
mysql> show master status ;
File: mysql-bin.000004
Position: 2327
从库
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2327
从库方面原因监控:
拿了多少:
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2327
执行了多少:
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 590
Exec_Master_Log_Pos: 2327
Relay_Log_Space: 801