day11 MySQL-主从复制
MySQL Replication主从复制:数据物理损坏的恢复
1. 主从复制搭建体验
准备多实例环境 (3307,3308,3309),(按第一天笔记配置)
2. 主从复制搭建过程
1. 规划
主(master):10.0.0.51:3307
从(slave):10.0.0.51:3308
2. 主库中进行创建复制用户 (权限是replication slave)
//登录3307
[root@mysql-db03~]# mysql -uroot -p3307 -S /data/3307/mysql.sock
//主库授权
mysql> grant replication slave on *.* to repl@'192.168.184.%' identified by '3307';
3. 全备主库,恢复到从库(从库补之前的课)
主库备份:
[rootadb03 ~]# mysqldump -uroot -p3307 -S /data/3307/mysql.sock -A --master-data=2 -R --triggers --single-transaction > /tmp/full.sql
从库恢复:
[rootadb03 ~]# mysql -uroot -p3308 -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql;
mysql> set sql_log_bin=1;
4. 从库开启复制(自学补课)
1. 告诉从库从哪个位置号开始自动复制
//查看开始复制的位置
[rootadb03 ~]# head -30 /tmp/full.sql
-- change master to MASTER_LOG_FILE='mysg-bin.000003', MASTER_LOG_POS=1141;
根据以上信息确认从'mysg-bin.000003',POS=1141位置开始自动复制
//登录3308,查看change master to相关配置
[root@db03 ~]# mysql -uroot -p3308 -S /data/3308/mysql.sock
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='master2.example.com', --主库地址
MASTER_USER='replication', --主库授权的复制用户
MASTER_PASSWORD='bigs3cret', --主库登录密码
MASTER_PORT=3306, --主库端口号
MASTER_LOG_FILE='master2-bin.001', --上一步查询的主库binlog日志
MASTER_LOG_POS=4, --上一步查询的主库最新位置号
MASTER_CONNECT_RETRY=10; --主库宕机后,从库尝试连接的次数,默认10
//change master 介绍
change master to配置和改变slave服务器用于连接master服务器的参数,以便slave服务器读取master服务器的binlog及slave服
务器的relay log。同时也更新master info及relay log info信息库。执行该语句前如果从机上slave io及sql线程已经启动,需要先停止(执行stop slave)。
change master to后面不指定某个参数的话,该参数保留原值或默认值。所以后续如果某些参数没有更改的话,change master to
后无需带该参数,例如我们只改变了用于复制的用户密码,那么change master to只需针对MASTER_PASSWORD选项作出修改即可.
例如:
mysql> stop slave;
mysql> change master to master_password='new_password';
mysql> start slave;
--------------------------------------------
//从库3308中粘贴以下语句
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.184.91',
MASTER_USER='repl',
MASTER_PASSWORD='3307',
MASTER_PORT=3307,
MASTER_LOG_POS=1141,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_CONNECT_RETRY=10;
//启动主从
mysql> start slave;
5. 故障分析
故障分析1
(1) 查看从库的状态
mysql> show slave status \G
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
(2) 看具体报错
Last_IO_Error: error connecting to master 'repl@192.168.184.91:3307' - retry-time: 10 retries: 2
(3) 手工模拟连接(自动连接失败,则采取手工连接)
[root@db01 ~]# mysql -urepl -p3307 -h 192.168.184.91 -P 3307
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'mysql-db03' (using password: YES)
(4) 解决
在配置文件中加入配置跳过解析
[root@mysql-db03~]# vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
server_id=7
port=3307
skip_name_resolve
[client]
socket=/data/3307/mysql.sock
//3308、3309一样的操作
[root@mysql-db03~]# vim /data/3308/my.cnf
[root@mysql-db03~]# vim /data/3309/my.cnf
//杀进程重启
pkill mysqld
重新启动数据库实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
(注:&表示分别执行此行命令)
(5) 再次检查从的状态,两个yes
故障分析2
//上面问题没有解决,登录3308时报错
[root@mysql-db01~]# mysql -uroot -p -S /data/3308/mysql.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
解决办法:
//修改3308的主配置文件/data/3308/my.cnf,在[mysqld]下添加skip-grant-tables,保存即可。
[root@mysql-db01~]# vim /data/3308/my.cnf
[mysqld]
skip-grant-tables
[client]
socket=/data/3308/mysql.sock
//重启3308-mysql:
[root@mysql-db01~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
//进入登录3308
mysql -u root -p
不用输入密码,直接回车(出现Enter Password 也一样直接回车,即可登陆成功)
//输入use mysql,修改root的密码:
update user set authentication_string=password(‘新密码’) where user=‘root’;
flush privileges;
//再次重启3308-mysql:
[root@mysql-db01~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
//测试是否成功登陆
mysql -u root -p
Enter Password>‘新密码’
可以登录啦!!!
//查看线程状态
主从复制
1. 主从复制知识点:
1.二进制日志管理回顾
2.传统备份方案回顾及缺陷
3.主从复制简介
4.主从复制原理 ******
5.主从复制搭建实践 ****
6.主从复制状态监控 ******
7.主从复制基本故障处理 ******
8.主从复制的架构演变 ***
9.多级主从架构实践 ***
10.主主复制实践 *****
11.主从复制高级功能 ******
12.新特性GTID ******
2. 铺垫:
数据库损坏?
物理损坏 (天灾人祸) ?
坏道、磁头、磁盘、文件系统损坏只读 格式化 dd 、数据文件(删除、头部损坏、坏页)。
逻辑损坏 (大部分人为的) ?
误删除、误修改。
高可用?
业务7*24小时不间断提供服务
3. 企业架构方案:
(1) 负载均衡LB:分担负载,偏向于性能,也有一定的高可用性
(2) 主备系统(HA):KA,主从复制衍生架构(MHA,Mycat)
(3) 真正高可用架构系统(无切换动作,多活系统(多台设备同时工作的,可实现平滑切换))
MySQL 5.7 ,8.0 InnoDB Cluster
MYSQL NDB Cluster
Oracle RAC
Sybase CLuseter
全年高可用率 (无故障率)
3个9:(1-99,9%)*365*24=8.76小时,表示该系统在连续运行1年时间里最多可能的业务中断时间是8.76小时.
4个9:(1-99.99%)*365*24=0.876小时=52.6分钟,表示该系统在连续运行1年时间里最多可能的业务中断时间是52.6分钟
5个9:(1-99.999%)*365*24*60=5.26分钟,表示该系统在连续运行1年时间里最多可能的业务中断时间是5.26分钟
4. 为什么要用主从复制:
4.1 辅助备份
解决物理损坏
4.2 演变高可用架构
在主库发生故障时,自动进行故障转移,对于应用透明
4.3 演变为高性能架构
读写分离
分布式
5. 主从复制 Replication(重要 ! ! ! )
1. 介绍
基于主库二进制日志实时复制到备库
2. 原理
(1) 前提
1)多台节点(多实例)
2)server_id要不同(3307主是7,3308从是8)
3)主库开启binlog
4)主库提供复制用户(Repl)
grant replication slave on *.* to repl@'192.168.184.%' identified by '3307';
5)从库需要"补课":通过备份主库,恢复到从库(补的是之前缺的那部分课)
6)通知从库:复制使用的用户,密码,IP,port,复制的文件和起点(补课完成后,跟着课堂开始学习),用到的命令
为:change master to
7)NTP时间同步
8)开启主从复制后,自动启动从库的两个线程: IO线程 ,SQL线程
start slave;
(2) 复制工作原理
1. 文件:
M:
binlog:记录主库的数据变化
S:
relay_log:中继日志,存储从主库请求的二进制日志的存储位置
master.info:存储用户,密码,IP,port,记录上次请求过的binlog位置,下次打开就能自动连接
relay_log.info:记录了上次SQL线程执行过的relaylog的位置点(从库请求到主库的binlog日志后,需要执行一遍日志才能真正与主库同步,如果遇到机器关机,就需要记录执行过的位置点,方便开机继续执行,relay-log.info就记录了该位置点)
2. 线程:
M:
Dump(IO)thread(投递线程):
主库发送二进制日志给从库的线程
S:
IO thread(接收线程):
请求binlog,接收binlog的线程
SOL thread(执行线程):
执行relay_log日志的线程
3. 主从复制原理文字说明:
1.从库动作
1) 从库IO线程,查看master.info信息,获取IP、port、user、password、file、pos
2) 通过 IP、port、user、password,连接到主库
3) 拿着 file(mysgl-bin.@000003),pos(120),请求主库
2. 主库查看(show master status)后判断,有新的binlog(mysgl-bin.000003,800)
3. 通过Dump线程读取binlog,从3号文件的120(120-800)开始发送二进制日志给从库
4. 从库IO线程,接收binlog日志
5. 接收的binlog日志放到TCPIP缓存中
6. IO线程回复一个ACK确认给dump线程,主库收到后,主库此次复制工作就完成了
7. 同时IO线程更新master.info信息,file.pos被更新为最新请求的值
8. TCPIP缓存数据,IO再将缓存写到relay-log.0000*中
9. 从库SQL线程,读取relay-log.info获取上次执行过的位置点
10. 在relay-log找到该位置点的日志进行执行,使从库与主库实现真正同步
11. 执行完,再更新一遍relay-log.info信息.
12. 自动清理relay-log.0000*中SQL线程已经完全执行完的日志文件
3. 主从复制搭建
略.
4. 主从复制监控
1. 从库相关信息:
>show slave status \G
Slave_IO_State: Connecting to master
Master_Host: 192.168.184.93
Master_User: repl
Master_Port: 3307
Connect_Retry: 10 //重复的次数
Master_Log_File: mysg-bin.00002 //主库binlog号码
Read_Master_Log_Pos: 325 //主库binlog位置号,此号码体现了和真正3307主库是否同步
Relay_Log_File: mysql-db03-relay-bin.000006 //从库中继日志号码
Relay_Log_Pos: 4 //从库中继日志位置号
Relay_Master_Log_File: mysg-bin.00002 //当前从库中继日志来源于主库mysg-bin.00002
Slave_IO_Running: Yes //从库两个线程的状态
Slave_SQL_Running: Yes
2. 主库相关信息:
show processlist;
show master status;
master.info记录的:
Master_Host: 10,0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Rea_Master_Log Pos: 248
以上两行,结合主库的show master status;此号码体现了和真正3307主库是否同步
3. 从库relay-log的信息
(1) relay-log.info 内容
Relay_Log_File: dbol-relay-bin.000008
Relay_Log_Pos: 411
//以下内容,反应的是从库的relay日志文件对应的主库的binlog文件名
Relay_Master_Log File: mysql-bin.000003
//从库线程状态:
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:
(2) 线程故障说明:
Last_IO_Errno: 1045 //错误代号
Last_IO_Error: error connecting to master 'repl@192.168.184.93:3307' - retry-time: 10 retries: 4
Last_SQL_Errno: 0
Last_SQL_Error:
(3) master-info文件位置:
Master_Info_File: /data/3308/data/master.info
(4) 延时复制有关的:
SQL_Delay: 0
SQL_Remaining_Delay: NULL
(5) 从库延时的时间:
Seconds_Behind_Master: NULL
5. 线程管理
(1) 启动和关闭两个线程
start slave;
stop slave;
(2) 单独关闭一个线程
stop slave io_thread;
stop slave sql_thread;
(3) 单独启动一个线程
start slave io_thread;
start slave sql_thread;
//应用场景
需要重新change master to的时候
stop slave;
reset slave all; ---> 重置master.info
change master to
start slave;
6. 主从复制故障 ******
1. IO线程故障
(1) 连接主库时出现的故障
user,password,ip,port
网络不通,防火墙
master 没启动
master 连接数上限
master 压力太大(cpu压力测试工具模拟)
skip_name_resolve (主从在一台机器)
例子1:模拟故障
从库:
stop slave;
reset slave all;
查看主库binlog位置信息
show master status;
| mysql-bin.000003 | 248 |
从库重新构建主从
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER=' repl',
MASTER_PASSWORD='1234',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=248,
MASTER_CONNECT_RETRY=10;
处理过程:
stop slave;
reset slave all;
CHANGE MASTER TO
例子2:模拟故障_连接数上线
//从库 (3308):
# mysql -S /data/3308/mysql.sock
> stop slave;
//主库修改参数 (3307):
# mysql -S /data/3307/mysql.sock
> set global max_connections=3;
> show variables like '%connect%';
> show processlist;
> kill 3;
//模拟多个连接到主库:
mysql -S /data/3307/mysql.sock
mysql -S /data/3307/mysql.sock
mysql -S /data/3307/mysql.sock
mysql -S /data/3307/mysql.sock
//从库测试连接主库报错
[rootadb01 ~]# mysql -S /data/3307/mysql.sock
ERROR 1040 (HYO): Too many connections
//从库启动主从:
start slave;
//排查过程:
mysql -urepl -p123 -h 10.0.0.51 -P3307
//解决方案:
max_connections
(2) 请求,接收binglog时出现的故障
binlog文件
找不到
损坏了
断节了
例子1:故障模拟
//主库:
reset master; //清理主库的所有日志
create database oldboy; //创建数据库oldboy
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:'could notfind next log; the first event 'mysgl-bin.000003' at 248, the last event read from/data/3307/data/mysql-bin.000003' at 248, the last byte read fromat 248.1/data/3307/data/mysgl'bin .000003 at 248.'
//处理方案:从新开始搭建
stop slave;
reset slave all;
//使用备份恢复,重新初始化数据
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.184.93',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_POS=325,
MASTER_LOG_FILE='mysg-bin.00002',
MASTER_CONNECT_RETRY=10;
//启动slave
> start slave;
日志写入relay-log
读写master.info
2. SQL线程故障
(1) 无法读写relay-log.info
(2) relay-log损坏,断节,找不到
(3) 接收到的SQL无法执行
1.要创建的数据库对象,已经存在
2.要删除或修改的对象不存在
3.DML语句不符合表定义及约束时
//主从复制设定好后,主库写的操作结果会同步到从库
故障模拟:
//从库创建数据库db
>create database db;
//主库也创建db数据库,主从库查看新建库,也没问题
>create database db;
>show databases;
>use db;
>create table t1(id int);
>show tables;
//从库:
>use db;
>show tables;
:报错
从库查看主从同步:
>show slave status \G;
查看SQL线程报错信息:
//故障处理
解决方法1:
# 以下是有风险的操作:
处理方法(以从库为核心的处理方案):
> stop slave; //临时停止同步开关
> set global sql slave skip counter=1; //将同步指针向下移动一个,跳过此条语句操作,如果多次不同
步,可以重复操作
> start slave;
etc/my.cnf
slave-skip-errors = 1032,1062,1007
但是,以上操作有时是有风险的,最安全的做法就是重新构建主从
把握一个原则,一切以主库为主.
解决方法2:
//分析:
归根揭底的原因都是由于从库发生了写入操作,读写分离后,主库负责写入,从库负责读取
思路:一个万金油的解决方案:将从库设定为只读库
//临时生效:
#set global read_only=1;
//永久生效:
#vim /etc/my.cnf
read only=1
(注意:以上权限只能对普通用户生效)
//设置管理员用户从库只读:
管理员read_only
innodb_read_only=1
3. 主从复制延时过高 *******
主库做了一个变更,从库很久才追上
(1)主库写binlog不及时 (将SQL语句写入到binlog不及时,导致没有binlog可复制)
//控制binlog从内存写入磁盘的控制开关是否开启
每次事务提交都立即刷新binlog到磁盘 (双一标准中的其一)
//打开开关
sync_binlog=1
//每次事务提交不立即写入磁盘,靠操作系统判断什么时候写入
sync_binlog=0
(2) dump线程多比较繁忙导致的,系统资源压力大
从库越多,压力越大
(3) IO线程过于繁忙导致阻塞
大事务------>拆成小事务(比如100万行数据分成10份执行)
事务量大---->group commit(积攒数据到一定的量后再处理,组团过河)
(4) SQL线程慢(Classic replication)
1)默认只有一个SQL线程,从库中的事务都是一个一个来执行的
Classic:SQL线程穿行处理,一个一个执行
gtid:MySQL5.7提供了gtid,可以实现SQL线程并行处理数据
实现并行复制的约束条件:要并行处理的数据必须来自不同库,同一个主库过来的实现不了并行复制
2)如果主库的
a.并发事务数很多
b.大事务
都会造成从库延时
多线程复制 (多sql线程),有局限性,针对不同库的事务进行并发,在有些情况下可以解决a.并发事务
数很多;对于大事务问题,只能在主库方面,将大事务拆成小事务
show slave status \G;
Seconds Behind Master;
自己了解以下工具:
pt-heartbeat
6. 延时从库
1. 普通的主从复制可能存在不足?
逻辑损坏的问题:
主从复制环境,比较擅长解决物理损坏
逻辑损坏怎么办? 采用延时从库
2. 延时从库
(1) 延时从库概念:
从库落后于主库一段时间(主库里出现了误操作,为了避免从库也进行误操作,可以让从库延时一
段时间于主库,从库发现此误操作,并跳过此操作)
SQL线程延时:数据已经写入relaylog中了,控制SQL线程"慢点"执行,比如3个小时后才执行。
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
因为做了延时从库,意味着客户读数据就要慢3个小时,所以做了延时的从库需要单独使用一台服务器,比较
浪费资源,企业中按需上延时从库。
(2) 延时从库上配置:
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300; //单位秒
mysql>start slave;
//查看设置
mysql> show slave status \G;
SQL_Delay: 300
(3) 延时从库使用
(1) 主库误操作,怎么使用延时从库
1) 停止主库业务,避免主库产生更多的数据
2) 立即停止从库SQL线程,改为手动截取数据到从库,跳过误操作部分
>stop slave sql_thread;
(2) 手工模拟sql线程工作,并截止到误操作之前
读取relay-log.info,获取到上次执行到的位置,作为继续执行relay-log的起点
分析relay-log内容,获取到误操作的位置点,截取误操作之前和之后日志,恢复到从
库,分两部分恢复
(3) 将恢复完的从库切换为主库,开启业务
(4) 将原主库设定为从库使用(掉个个)
3. 故障模拟
(1) 开启从库的延时
(2) 模拟数据及故障
# mysql -S /data/3307/mysql.sock
> create database delay charset utf8; //创建数据库
> use delay;
> create table t1 (id int); //创建表t1
> insert into t1 values(1),(2),(3);
> commit;
> drop database delay; //模拟误操作,删除delay库
(3) 停从库的sql线程
stop slave sql_thread;
(4) 手工模拟sql线程工作,并截取误操作之前的数据进行恢复
1. 读取relay-log.info
> show slave status \G;
Relay Log File: db01-relay-bin.000002
Relay Log Pos:374
2. 找到误删除的位置 (relay-log.info截取只看pos开始位置点那一列,不管结束点)
>show relaylog events in 'db01-relay-bin.000002';
db01-relay-bin.000002 | 804 | Query | 7 | 1103 | drop database delay
3. 截取relaylog (命令是一行)
mysglbinlog --start-position=374 --stop-position=804 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
(注意:binlog截取日志要看开始点与结束点;relay-log只看开始点:某个开始点到另一个开始点)
4. 恢复relaylog
# mysql -S /data/3308/mysql.sock
> set sql_log_bin=0;
> source /tmp/relay.sql;
> set sql_log_bin=1;
5.查看被误删的数据库是否已经恢复
6.从库替换为主库
> stop slave; //从库已变成主库,停止原先的主从复制
> reset slave all; //清除从库原先的信息
7.恢复原来主库为主从状态
7. 过滤复制
将主库划分成份,分别进行复制
实现过滤复制思路一:从主库角度分析
通过二进制日志是否记录,来控制过滤复制(了解下即可)
主库参数:
> show master status;
Binlog_Do_DB //白名单
Binlog_Ignore_DB //黑名单
实现过滤复制思路二:从从库角度分析(推荐)
通过一些开关,只复制某些库或表的二进制日志
从库参数:
Replicate_Do_DB;
Replicate_Ignore_DB;
Replicate_Do_Table;
Replicate_Ignore_Table;
Replicate_Wild_Do_Table;
Replicate_Wild_Ignore_Table;
对库的:
> show slave status \G;
对库的 (常用):
Replicate_Do_DB; //只复制某些库的日志(白名单)
Replicate_Ignore_DB; //跳过某些库的日志,不复制(黑名单)
书写格式:
一个库名一行,不能用逗号隔开
对表的:
Replicate_Do_Table;
Replicate_Ignore_Table;
模糊的(正则之类的):
Replicate_Wild_Do_Table;
Replicate_Wild_Ignore_Table;
----------------------------------------
案例:
/将3309开启过滤复制,只复制world数据库数据(对库的)
(1)修改配置文件
#vim /data/3309/my.cnf
replicate_do_db=world
replicate_do_db=oldboy
重启3309 (先关闭,再启动)
# mysqladmin -S /data/3309/mysql.sock shutdown
# mysqld_safe --defaults-file=/data/3309/my.cnf &
(2) 构建主从环境
1) 备份主库数据 (3307)并恢复到3309
备份3307库数据到/tmp/full.sql
# mysqldump -S /data/3307/mysgl.sock -A --master-data=2 --single-transaction >/tmp/full.sql
登录3309
# mysql -S /data/3309/mysgl.sock
还原备份数据到3309
> set sql_log bin=0;
> source /tmp/full.sql
2) 开启主从:
登录3309
# mysql -S /data/3309/mysgl.sock
复制如下语句到3309
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.184.93',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_POS=120,
MASTER_LOG_FILE='mysg-bin.00003',
MASTER_CONNECT_RETRY=10;
启动主从复制
> start slave;
查看过滤信息,确认过滤成功
> show slave status \G;
(3) 测试过滤复制
对库:放在白名单里的库,主库里对该库操作,从库同步,主库对白名单外的库操作,从库不同步
对表:同上
8. 半同步复制-主从一致性问题
------半同步复制,MGR增强半同步复制 (5.7.17版本以上),PXC (percona)架构等
(1) 半同步复制概念
问题:主从数据不一致
当从库中TCPIP缓存还未写入到relaylog之前就宕机了,缓存中的数据丢失,导致主从数据不一致,事务丢失。
解决思路:
主从复制原理是从库将还原数据放到TCPIP缓存后,从库IO线程会回复给主库一个ACK确认,因此,是不是可以考虑等TCPIP缓存将数据写入到relaylog中后,再给主库回复ACK确认,此方法称为:半同步复制.
核心理念就是:什么时候返回ACK的问题
出发点是:保证主从数据一致性的问题,安全的考虑
5.5 版本出现的概念,但是不建议使用,性能太差
5.6 以后出现 group commit 组提交功能,来提升开启版同步复制的性能
5.7更加完善了,在group commit基础上出现了MGR
5.7的增强半同步复制的新特性: after commit; after sync;
--------------------------------------
(2) 半同步复制实现
基于插件来实现的
主:
>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';
9. 主从切换及数据补偿
(1) Classic Replication
Failover (主从切换或故障转移) 所涉及到的数据补偿问题
概念:
主库宕机了,但从库不一定及时获取到最新的数据,如果从库切换成主库使用,会涉及到最新的这部分数据缺失的问题,因此需要进行数据补偿,所使用:GTID
GTID解决数据补偿的原理
在主从复制环境中,主库发生过的事务,在全局都是由唯一 GTID记录的,更方便Failover和数据补偿,利用了gtid的全局编号进行截取恢复
描述环境:
1. 1主2从的wordpress 生产环境,(db01(M) db02(s1) db03(s2))
2. wordpress应用默认是连接到db01这个主库的
3. db01岩机
4. wordpress应用是没有能力和责任监控到db01宕机。
5. 人为的实时监控到db01宕机。
6. 选择一个新的主库 (New Master),原则更加接近主库数据状态的从库
7. 选择方法:在每个从库重运行
//GTID
问题:
worpress将数据存储在主库上,主库负责存储,从库负责备份、分析、读取数据,当从库备份的数据与主库差了一部分时,就需要进行数据补偿。
解决思路:
查看从库中master-log.info的位置号,与主库的当前最新位置号进行对比,就可以确定从库缺少了哪一部分数据,再进行补偿修复,之后将应用的数据存储切换到从库上,进行使用。
1.监控问题: zabbix 1-10分钟才发现问题
2.数据补偿问题
判断缺失部分的binlog (前提:主库SSH和binlog是完整的)
3.应用切换到新的主库 (failover)
数据补偿原理图:M宕机后,S1作为新主,M和S2为从
(2) GTID 复制
(5.6新特性,5.7主流)
1. GTID概念
GTID 全局事务编号(Global Transaction ID) 是对于一个已提交事务的编号,并且是一个全局唯一的编号。(之所以不采用reliy_log编号恢复,是因为太复杂了,所以新加了gtid编号机制)
它的官方定义如下:
GTID = source_id:transaction_id (事务id)
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
//实际使用中是server_uuid
2. 什么是 sever_uuid,和 server-id 的区别?
server_uuid是自动生成的,server_id是手工设置的
server_uuid是每次重新初始化数据,或者人工删除auto.cnf文件,再次重启就会生成新的server_uuid
:29指的是事务的编号,mysql要求编号必须是连续的,不能断点,表现方式:
:29
:1-29
:29-100
3. 重要参数介绍
gtid-mode=on/off
enforce-gtid-consistency=true
Log-slave-updates=1
gtid-mode=on --启用gtid类型,否则就是普通复制架构,默认是关闭的,必开参数
enforce-gtid-consistency=true --强制GTID的一致性,必开参数
Log-slave-updates=1 --slave更新是否记入二进制日志 (binlog) ,主要在双主和高可用中必须要加
//gtid三个重要参数查看命令
>show variables like '%gtid%';
>show variables like '%updates%'
4. GTID复制配置过程
(顺便准备MHA环境,1主2从)
1、虚拟机环境准备
db01 : 10.0.0.51
db02 : 10.0.0.52
db03 : 10.0.0.53
防火墙关闭,主机名,修改hosts文件(三台都要做)
#getenforce
disabled
2、清理环境(三台都要做)
\rm -rf /application/mysql/data/*
\rm -rf /data/mysql/*
杀掉数据库(三台都要做)
#pkill mysqld
3、准备配置文件(三台都要做)
主库db01:
加入以下配置信息
#cat > /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock
eof
slave1(db02):
#cat > /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock
eof
slave2(db03):
#cat > /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock
eof
4、初始化数据(三台都要做)
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/
5、启动数据库(三台都要做)
/etc/init.d/mysqld start
//报错:没有错误日志文件
#touch log-error=/var/log/mysql.log
#chown -R mysql.mysql /var/log/mysql.log
6、构建主从
master:51
slave:52,53
51:在主库上创建repl用户并授权
>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
52\53:从库填写相关信息
>change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123’,
MASTER_AUTO_POSITION=1; //基于gtid,自动判断文件名和位置号
//启动主从复制
>start slave;
//查看从库状态
>show slave status \G;
//最后三行查看gtid状态
行1:接受到的gtid号
行2:已经执行过的gtid号
7、查看gtid是否搭建成功
>create database oldboy;
>show master status;
事务编号变为了:1-2
//从库52/53也便变成了:1-2
>show slave status \G;
5. GTID从库误写入操作 处理
注意:传统的复制模式可以跳过误操作,但gtid不能跳过事务,因为gtid认为编号必须是要连续不断的应用,可使用如下方法。
gtid从库避免重蹈主库误操作的方法:注入空事物
注入空事物的方法:
先找到出错的事务,报错的事务':1-3',就是3号事务
stop slave;
set gtid_next='8c49d7ec-7e78-11e8-9638-000c29ca725d:3';
begin;commit; //空事务,从开始到结束,啥也不干
set gtid_next='AUTOMATIC'; //自动请求跳过之后的日志
start slave;
这里的xxxxx:N 也就是你的slave sql_thread报错的GTID,或者说是你想要跳过的GTID。
最好的解决方案: 重新构建主从环境
6. GTID 复制和普通复制的区别(面试问题)
(1) 在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便Failover和数据补偿,开启gtid编号后,原想的binlog position号依然存在
(2) 额外功能参数 (3个)
(3) change master to 的时候不再需要binlog、文件名和position号
(4) 在复制过程中,从库不再依赖master.info文件中记录的file_name和position号,而是直接读取最后一个relaylog的 GTID号
(5) mysqldump备份时,默认会将备份中包含的事务操作,以以下方式:
# cat /tmp/full.sql //会在主库备份的full.sql文件里自动备注哪部分已经复制了
SET @@SESSION.SQL_LOG_BIN=0; //临时关闭日志记录
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-3'; //告诉从库跳过1-3个GTID号的事务,自动从4开始往后复制
告诉从库,我的备份中已经有以上事务,你就不用运行了,直接从下一个GTID开始请求binlog就行.