MySQL主从复制
主从复制:对于实际的生产环境中,对数据的读写都是在一个服务器中,是不能满足实际的需求,通过主从复制来同步数据,在通过读写分离来提高数据库的并发负载能力
MySQL主从复制的原理:
MySQL的主机数据更新以后,将数据写入二进制日志binary log 之中,slave开始一个工作线程(I/O)I/O线程在master上打开一个普通的连接,从master的二进制日志文件读取事件,如果跟上master,它会跟上master,并且睡眠等待master 有新的事件,I/O线程将这些事件继续写入日志中
I/O线程将读到的master的日志文件写入到relay log,再由SQL线程读取日志Relay log 到MySQL的从机上面,这就是MySQL的主从复制的原理
MySQL主从复制的复制方式
MySQL的主从复制并不完美,存在着几个由来地问题,首先就是一个复制问题
基于SQL语句地复制(SBR)在服务器上执行SQL语句,在从服务器上执行同样地语句,MySQL默认采用地是基于语句地复制,执行效率高
基于行的复制(RBR)把改变的内容复制过去,而不是把命令在从服务器在执行一遍
混合模式复制(MBR)默认采用基于语句的复制,一旦发现基于语句无法精准复制时,就会采用基于行的复制
全局事务标识符(GTID)基于SQL语句的方式是最古老的方式,也是目前默认的府复制方式,
SBR方式的优缺点
SBR的优点:
历史悠久,技术成熟
binlog 文件较小
binlog 中包含所有的数据库的更新信息,可以据此来审核数据库的安全等情况
binlog 可以用于实时的还原,而不是用于复制
主从版本可能不一样,从服务器可能比主服务器版本高
SBR的缺点:
不是所欲的UPDATE语句都是能被复制的,尤其是包含不确定操作的时候复制需要进行全表扫描的UPDATE时,需要比RBR请求更多的行级锁对于一些复杂的语句,在从服务器上的耗资源会更加严重,而RBR模式下,指挥对那个发生变化的记录产生影响数据表必须和主服务器保持才行,则会导致复制出错执行复杂语句如果出错的话,会消耗更多的资源
RBR方式的优缺点:
RBR的优点
任何情况都能被复制,这对复制来说是最安全可靠的
和其他大多数数据库的系统复制技术都是一样的
多数情况下,从服务器的表如果有主键的话,复制就会快很多
RBR的缺点:
binlog大很多
复杂的回滚时binlog会产生更多的数据
主服务器执行UPDATE 时所有发生变化的记录都会写道binlog中,
无法从binlog中看到复制i二到的什么语句
MySQL主从复制的原理
mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率很比较高,下一步, 问题来了,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的,不是顺序的,成本高很多,还可能与slave上的其他查询产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个DDL卡主了,需要执行10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的DDL也需要执行10分,为什么slave会延时?”,答案是master可以并发,Slave_SQL_Running线程却不可以。
MySQL主从复制的延时时怎么产生的
当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。
MySQL主从同步延时解决方案
最简单的减少slave同步延时的方案就是在架构上做优化,尽量让主库的DDL快速执行。还有就是主库是写,对数据安全性较高。另外就是使用比主库更好的硬件设备作为slave。
环境说明:
两个Centos 7.x 的虚拟机
master_ip :192.168.10.144
slave_ip:192.168.10.145
mysql 5.6以后的复制引入一个uuid概念,各个复制结构中得保持不一样,在/var/lib/mysql文件中找到auto.cnf 文件,修改里面得uuid。
查看使得主从的uuid都是不一样的。 修改uuid以后要重启服务器
sysstemctl restart mysqld.service
实现MySQL主从复制的配置
主服务器:
开启二进制日志文件
配置唯一的server-id
获得master的二进制文件名和位置
创建一个slave和master通信的用户账号
主服务器:
配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启动slave服务
设置server-id =#自己IP的后三位
然后 systemctl restart mysqld.service 重启服务器
在主机上建立账户斌且授权
create user 'copy'@'%' identified with mysql_native_password by 'Nebula@123';
grant replication slave on *.* to 'copy'@'%';
flush privileges
查询master 的状态 show master status \G
告知从服务器的二进制文件名和位置 这里需要根据主服务器的配置来写
开启服务 start slave;
查看服务的状态 show slave status \G;
MySQL主从数据不一致,提示:slave-SQL-runing:no 的解决方式
在从服务器上:show slave status \G;查看显示不同步
解决方式:
1、先停slave
stop slave;
2、跳过错误步数,后面的步数不变
set global sql_slave_skip_counter=1
3、再启动slave
start slave;
4、查看同步状态
show slave status \G
测试主从复制是否成功
在master中创建数据库并且插入一条数据
从机上也就显示到在主机上写入的数据