mysql的主从复制
前言:名词理解
热备:双机热备是应用于服务器的一种解决方案,其构造思想是主机和从机通过TCP/IP网络连接,正常情况下主机处于工作状态,从机处于监视状态,一旦从机发现主机异常,从机将会在很短的时间之内代替主机,完全实现主机的功能。 冗余:多余的重复或啰嗦内容(包括信息、语言、代码、结构、服务、软件、硬件等等)均称为冗余。冗余有两层含义,第一层含义是指多余的不需要的部分,第二层含义是指人为增加重复部分,其目的是用来对原本的单一部分进行备份,以达到增强其安全性的目的,这在信息通信系统当中有着较为广泛的应用。 容灾:容灾系统是指在相隔较远的异地,建立两套或多套功能相同的IT系统,互相之间可以进行健康状态监视和功能切换,当一处系统因意外(如火灾、地震等)停止工作时,整个应用系统可以切换到另一处,使得该系统功能可以继续正常工作。容灾技术是系统的高可用性技术的一个组成部分,容灾系统更加强调处理外界环境对系统的影响,特别是灾难性事件对整个IT节点的影响,提供节点级别的系统恢复功能。
一.mysql主从复制
1.主从复制主库的操作
1.主库要开启二进制日志
2.主库的server_id和从库不同
3.主库要有提供从库连接的用户
4.主库确定binlog名称和位置点
5.获取其他主库信息:ip,port,user,password
2.主从复制从库的操作
1.从库需要配置与主库不同的server_id
2.同步主库的所有数据
3.从库填写主库信息进行同步(change master to)
4.从库开启IO线程和SQL线程
3.主从复制原理
1.主库配置server_id和binlog
2.主库授权用户
3.主库查看binlog名字及位置点
4.通过change master to语句告诉从库主库的ip,port,user,password,file,pos
5.从库开启IO线程和SQL线程(start slave; )
6.从库通过IO线程和change master to获取的信息连接主库
7.从库连接主库后,会去询问主库的dump线程,是否有比change master to时更新的binlog文件和位置点
8.主库接收到从库请求后,比较一下binlog信息,如果有就将最新数据通过dump线程给从库IO线程(使用ACK)
9.从库通过IO线程接收到主库发来的binlog事件,存储到TCP/IP缓存中(使用ACK)
10.从库IO线程也会返回ACK更新master.info文件里面的binlog文件名和位置点(使用ACK)
11.TCP/IP缓存中的内容会存到relay-log中
12.SQL线程会读取relay-log.info,获取上一次执行到的位置点,与中继日志对比,执行最新的relay-log日志
13.SQL线程执行完relay-log最新的数据后,更新relay-log.info,记录本次更新到的位置点
4.主从涉及到的文件
1)主库
1.主库binlog:记录主库发生过的修改事件
2.dump线程:对比binlog是否更新,并且将新数据的二进制日志传送给从库
2)从库
1.IO线程:连接主库,接收主库发来的新数据的binlog日志
2.SQL线程:执行主库传过来的新数据的binlog日志
3.relay-log(中继日志):存储所有主库传过来的binlog事件
4.master.info:记录上次请求到的主库binlog名字和位置点
5.relay-log.info:记录上一次执行relay-log到的位置,下次从记录的位置开始向下继续执行
二、主从复制搭建实战
1.主库操作:
给主库个密码 mysqladmin -uroot password '123'
1)配置主库配置文件
[ root@db01 ~]
[ mysqld]
server_id= 1
log_bin= mysql-bin
[ root@web03 ~]
2)创建主从复制用户
[ root@db01 ~]
mysql> grant replication slave on *.* to slave@'192.168.100.%' identified by 'oldboy123' ;
这里必须 *.* 不能指定库授权,因为 replication slave 是全局的
3)查看主库binlog信息
mysql> show master status;
2. 从库操作:
1)修改配置文件
[ root@db02 ~]
[ mysqld]
server_id= 2
[ root@db02 ~]
2)配置主从同步
[ root@db02 ~]
mysql> change master to
-> master_host= '192.168.100.111' ,
-> master_user= 'slave' ,
-> master_password= 'oldboy123' ,
-> master_log_file= 'mysql-bin.000001' ,
-> master_log_pos= 1357;
start slave;
show slave status\G
3.添加数据库主从(数据库运行中做主从)
1.准备纯净的从库环境
2.主库全备数据
mysqldump -uroot -p123 --all-databases > /backup/full.sql
mysqldump -uroot -p123 --triggers --master-data= 2 --single-transaction --all-databases > /backup/full2.sql
--master-data= 2 备份文件中 change master 是有注释的
--single-transaction 此选项会将隔离级别设置为:REPEATABLE READ。并且随后再执行一条START TRANSACTION语句,让整个数据在dump过程中保证数据的一致性,这个选项对InnoDB的数据表很有用,且不会锁表。
3.将热备数据传达从库
scp /backup/full.sql root@从库IP:/backup/
4.模拟主库运行,有数据写入
insert into oldboy.jiaoyi values( 3,'lhd' ,600) ;
5.从库导入全备数据
方法一:mysql> source /backup/full.sql
方法二:mysql -uroot -p1 < /backup/full.sql
6.查看主库sql文件中的位置点(如果是打点备份的话)
[ root@db02 scripts]
-- CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.000001' , MASTER_LOG_POS= 786574;
7.从库配置主从
mysql> change master to
-> master_host= '192.168.100.111' ,
-> master_user= 'slave' ,
-> master_password= 'oldboy123' ,
-> master_log_file= 'mysql-bin.000001' ,
-> master_log_pos= 786337;
8.开启SQL线程和IO线程
start slaves;
9.查看主从状态
show slave status;
4.验证多个从库server_id相同,主从是否有问题
1.配置第二个从库
[ root@db01 ~]
[ mysqld]
server_id= 2
2.启动数据库
systemctl start mysql
3.将主库的全备发送到从库
scp root@192.168.100.111/backup/full2.sql /backup
4.在从库执行sql文件
mysql < /backup/full2.sql
5.查看sql文件中的位置点
[ root@db02 scripts]
-- CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.000001' , MASTER_LOG_POS= 786574;
6.在从库上执行change master
change master to
master_host= '192.168.100.111' ,
master_user= 'slave' ,
master_password= 'oldboy123' ,
master_log_file= 'mysql-bin.000001' ,
master_log_pos= 786574;
7.开启SQL线程和IO线程
start slave;
8.查看主从复制的状态
show slave status\G
三、主从复制基本故障处理
reset slave all;
1.IO线程问题
1.检测网络:
[ root@db01 ~]
2.检测端口:
[ root@db01 ~]
[ root@db01 ~]
3.检测账号,密码:
grant replication slave on *.* to slave@'192.168.100.%' identified by 'oldboy123' ;
mysql -uslave -poldboy123 -h127.0.0.1 -P 3307
4.查看binlog是否存在
show master status;
5.反向解析
ERROR 1045 ( 28000) : Access denied for user 'root' @'db01' ( using password: YES)
skip_name_resolve
2.SQL线程问题
1.主库有的数据,从库没有
主库:a库
从库:没有a库
2.从库有的数据,主库没有
从库:a库
主库:要创建a库
3.主库与从库数据库结构有差别
mysql> stop slave;
mysql> set sql_slave_skip_counter= 1;
mysql> start slave;
[ root@db01 ~]
slave-skip-errors= 1032,1062,1007
1.要求,主从复制之前,主库和从库的数据保证一致.
2.在从库上设置 只读:set read-only= 1;
(做读写分离时使用,但是做MHA会出现提升为主库时,主库只读,后面会讲专门做读写分离的Atlas)
四、延时从库
我们说用延时从库可以做备份,主库执行删除的时候,从库还没有删除,可以把表数据拿出来恢复回去
企业中一般会延时3-6小时
1.延时从库配置方法
1)已经有主从的情况下
1.停止主从
mysql> stop slave;
2.设置延时为180秒
mysql> CHANGE MASTER TO MASTER_DELAY = 180;
3.开启主从
mysql> start slave;
4.查看状态
mysql> show slave status \G
SQL_Delay: 180
5.主库创建数据,会看到从库值变化,创建的库没有创建
SQL_Remaining_Delay: 170
2)没有主从复制的情况下
1.修改主库,从库配置文件
server_id
开启binlog
2.保证从库和主库的数据一致
3.执行change语句
change master to
master_host= '192.168.100.111' ,
master_user= 'slave' ,
master_password= 'oldboy123' ,
master_log_file= 'mysql-bin.000001' ,
master_log_pos= 2752,
master_delay= 180;
3)延时从库停止方法
1.停止主从
mysql> stop slave;
2.设置延时为0
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
3.开启主从
mysql> start slave;
2.思考:延时到底是在哪里延时的
思考:IO线程还是SQL线程做的手脚?
create database ttt;
mysqlbinlog --base64-output= decode-rows -vvv db03-relay-bin.000002
总结:
延时从库是在SQL线程做的手脚,IO线程已经把数据放到relay-log里了.
SQL线程在执行的时候,会延迟你设定的时间长度.
3.使用延时从库恢复数据
1)场景
总数据量级500G,正常备份去恢复需要1.5-2小时
1)配置延时3600秒
mysql> CHANGE MASTER TO MASTER_DELAY = 3600;
2)主库
drop database db;
3)怎么利用延时从库,恢复数据?
2)环境准备
1.进行每日的全备
mysqldump -uroot -p123 -A -R --triggers --master-data= 2 –single-transaction > /backup/full.sql
2.调整延时从库延时时间为10分钟
stop slave;
CHANGE MASTER TO MASTER_DELAY = 600;
start slave;
3.主库写入新数据
create database yanshi;
use yanshi;
create table yanshi( id int) ;
insert into yanshi values( 1) ,( 2) ,( 3) ,( 4) ;
create database yanshi2;
use yanshi2;
create table yanshi2( id int) ;
insert into yanshi2 values( 1) ,( 2) ,( 3) ,( 4) ;
3)模拟删除数据
1.删除一个库,可以是之前的,也可以是刚创建的
drop database world;
4)使用延时从库恢复数据
1.停止从库sql线程
stop slave sql_thread;
2.查看状态
show slave status;
3.备份从库数据
mysqldump -uroot -p123 -B world > /backup/congku.sql
4.截取一下relay-log
1)确认起点
[ root@db02 data]
./mysqld-relay-bin.000002
283
2)确认终点
[ root@db02 data]
drop语句之前
3)截取数据
[ root@db02 data]
5.将从库全备的数据与relaylog截取数据拷贝到主库
scp /backup/yanshi.sql 192.168.100.111:/backup/
scp /backup/congku.sql 192.168.100.111:/backup/
6.将数据导入主库
mysql < source /backup/yanshi.sql
mysql < source /backup/congku.sql
7.开启从库的sql线程
start slave sql_thread;
五、半同步复制
mysql主从本身是异步复制
IO线程将数据写入TCP/IP以后就不管了,当TCP缓存返回ACK的时候,IO线程会继续去主库拿数据
从MYSQL5.5开始,支持半自动复制。之前版本的MySQL Replication都是异步(asynchronous)的,主库在执行完一些事务后,是不会管备库的进度的。如果备库不幸落后,而更不幸的是主库此时又出现Crash(例如宕机),这时备库中的数据就是不完整的。简而言之,在主库发生故障的时候,我们无法使用备库来继续提供数据一致的服务了。
半同步复制(Semi synchronous Replication)则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题,安全的考虑。
半同步复制是IO线程拿完数据,会等待SQL线程执行完以后再去拿数据,而且还会影响主库性能,他会阻塞主库数据的写入,如果SQL要执行十分钟,那么IO线程就一直等待,并且不让主库写入数据
半同步,是在IO线程上做的手脚,数据写入到TCP/IP缓存中,不返回ACK,直到SQL线程执行完之后,才返回ACK
ACK是一个确认字符
半同步其实是mysql自带的一个和插件(很少有人用)
1.效率低
2.影响主库的性能
3.设置一个超时时间:过了这个超时时间,会恢复到异步复制
5.5 出现概念,但是不建议使用,性能太差
5.6 出现group commit 组提交功能,来提升开启半同步复制的性能
5.7 更加完善了,在group commit基础上出现了MGR
5.7 的增强半同步复制的新特性:after commit; after sync;
1.半同步复制开启方法
1)主库
[ root@db01 ~]
mysql> show global variables like 'have_dynamic_loading' ;
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so' ;
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
[ root@db01 ~]
[ mysqld]
rpl_semi_sync_master_enabled= 1
rpl_semi_sync_master_timeout= 1000
mysql> show variables like'rpl%' ;
mysql> show global status like 'rpl_semi%' ;
2)从库
[ root@mysql-db02 ~]
show slave status\G
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so' ;
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> stop slave io_thread;
mysql> start slave io_thread;
[ root@mysql-db02 ~]
[ mysqld]
rpl_semi_sync_slave_enabled = 1
3)测试半同步
六、过滤复制
我们在授权主从用户的时候必须使用
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123' ;
这里必须 *.* 不能指定库授权,因为 replication slave 是全局的,不用则报错
企业里或许有只想同步某一个库的需求,可能是因为环境多为了节省资源
企业里很多环境
1.开发环境:开发开发完自己测试
2.测试环境:
1)性能测试
2)功能测试
4.预发布环境(beta,内测不删档)、灰度环境、沙盒环境
5.生产环境
除了生产和预发布其他的环境都是虚拟机测试用的
测试环境有很多游戏,我就想一个从库同步一种游戏,还有合服,建新服,其实就是一个库或者一个表而已
创建角色总是提示改昵称已被创建,就是因为姓名也是唯一键
1.确认三台机器主从状态
show slave status\G
2.过滤复制两种方式
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 262 | 白名单 | 黑名单 | |
+------------------+----------+--------------+------------------+-------------------+
1)黑名单
不记录黑名单列出的库的二进制日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table
2)白名单
只执行白名单中列出的库或者表的中继日志
--replicate-do-db= test
--replicate-do-table= test.t1
--replicate-wild-do-table= test.t2
3.配置过滤复制
1)主库创建两个库
create database wzry;
create database yxlm;
2)从库1配置
[ root@db02 ~]
[ mysqld]
replicate-do-db= wzry
[ root@db02 ~]
show slave status;
3)从库2配置
[ root@db03 ~]
[ mysqld]
replicate-do-db= yxlm
[ root@db03 ~]
show slave status;
4)主库创建表测试
use wzry;
create table dianxin1;
use yxlm;
create table wangtong1;
5)从库查看表
4.尝试把白名单配置到主库
[ root@db01 ~]
[ mysqld]
replicate-do-db= yxlm
[ root@db01 ~]
5.过滤复制总结:
在主库上设置白名单:只记录白名单设置的库或者表,相关的SQL语句到binlog中
在主库上设置黑名单:不记录黑名单设置的库或者表,相关的SQL语句到binlog中
在从库上设置白名单:IO线程会拿所有的binlog,但是SQL线程只执行白名单设置的库或者表相关的SQL语句
在从库上设置黑名单:IO线程会拿所有的binlog,但是SQL线程不执行黑名单设置的库或者表相关的SQL语句
replicate-do-db= test
replicate-do-table= test.t1
replicate-do-table= test.c2
replicate-do-table= test.b3
replicate-do-table= test.a4
replicate-wild-do-table= test.t*