mysql的复制

Wesley13
• 阅读 439

若排版紊乱可查看我的个人博客原文地址

mysql主从复制

一般网站的业务都是读多写少,而一般并发系统的瓶颈在数据库,因此这种情况下一般会采用读写分离的方式降低主库的读压力,从而提升系统的并发量,现在先不说读写分离相关技术,就说要实现读写分离对数据库有一个基本要求,即主库和从库数据一致性的问题,即使不能绝对的强一致性,至少也要达到相对的一致性,这里依赖于mysql数据库自带的一些功能和配置

原理图

图片是从网上找的一个较为形象的图片:

mysql的复制

原理解释

原理是使用binlog进行同步,binlog会记录数据库的写操作,将改变记录到binlog中,从库基本实时读取主库的binlog,然后在自身的数据库中执行这种改变。

详细过程:

  1. master将改变写到binlog文件中

  2. 启动salve,salve有一个IO(工作)线程链接master去获取改变

  3. master启动一个Binglog Dump线程用来"接待"从库的IO线程,该线程是一个守护线程,他会读取binlog中的事件发送给IO线程,注意这里如果是异步复制,那么主库并不会在这里等待而会直接给调用的client返回sql执行结果

  4. IO线程将改变写到salve的relayBinlog中

  5. salve有一个SQLThread线程读取relayBinlog文件,将写操作在salve数据库中执行,以达到数据同步的目的

  6. (可选)savve也会操作写入salve的binlog文件,用途是供给salve的salve使用,当然也可以关闭

补充:

  1. binlog记录改变的方式有3种:

    基于语句就是当数据库执行DML(insert/update/delete)语句时将语句记录到binlog中,效率较高

    基于行就是当某行数据改变时将该行数据记录到binlog中,较为精确

    混合就是两者混着用,优先使用基于语句,无法保证精确复制时使用基于行

    优劣分析可参考binlog分析

  2. 详细过程3中后续的过程

    之后若主库无改变,dmup线程则进入休眠,直到主库改变binlog发送广播时会唤醒该线程,因此有几个salve链接master,那么master上就会启动几个dump线程,当该线程负责接待的salve断掉连接时,理论上该线程会退出

  3. 还需要了解的binlog知识

    binlog的position:解决从哪开始同步,该属性代表同步位置,不可能每次全量同步,肯定是增量同步

    可以在my.cnf配置哪些库需要同步,哪些库不需要

    binlog不会让他无限去膨胀记录的,可以设置有效期和大小

    #my.cnf中有两个参数设置
        expire_logs_days = 7      #binlog保留时间7天
        max_binlog_size = 1G      #binlog大小
    
  4. 两个命令

    #查看当前正在写入的binlog文件
    show master  status;
    
    #查看binlog中的事件
    show binlog events in "mysql-bin.000007";
    

常见复制架构分析

一主一从:一般网站是用来做热备,但是程序员手抖,删除主库,从库还是会在几秒内删除,不靠谱,建议定时任务

一主三从:其中一个从库是太子(半同步),盖架构一般用来做读写分离,解决主库读压力

双主:不推荐使用,只有大量写去使用(135写1库,246写2库)

级联同步:减轻master的压力,同时达到主从同步目的,缺点是复制master的那个salve挂了,架构就崩了,即存在单点问题

环形多主:极不推荐,除非是实在解决不了写并发

一主多从搭建

配置主从复制很简单,我这里linux环境是CentOS6.6,mysql5.6,按照如下步骤操作即可,代理节点先不管他,在使用atlas做读写分离时会配置使用

环境安排

首先要保证网络环境能相互ping通,对应的防火墙端口(3306)开放

mysql的复制

安装mysql数据库

首先要在两台服务器上安装好mysql数据库,我这里分别在192.168.16.10(主),和192,168.16.20(从)上安装的mysql5.6的数据库,安装步骤可以参考我之前关于基本环境搭建的博客web常用环境搭建

配置主库

编辑要设置为主库的数据库配置文件,添加如下内容即可,对应配置含义有注释

vi /etc/my.cnf


#mysql主从复制-master配置
#需要同步的二进制数据库名;
binlog-do-db=syndb
#不同步的二进制数据库名,如果不设置可以将其注释掉;
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test
#以下参数可选----------
#binlog 格式
binlog-format=ROW
log-bin=mysql-master-bin
#slave更新时是否记录到日志中;
log-slave-updates=true

从原理上来看,从库是需要连接主库的,因此主库要给从库分配一个用于访问的数据库的账号和密码,登入mysql使用以下命令是创建一个账号

#创建一个用户名为slave1密码为123456的账户,授予replication(复制)权限,且只允许该账户在ip地址192.168.16.20上使用
grant replication slave,super,reload on *.* to slave1@192.168.16.20 identified by '123456';

配置从库

编辑要设置为从库的数据库配置文件,添加如下内容即可,对应配置含义有注释

vi /etc/my.cnf


#mysql主从复制-slave配置
server-id = 2 #原有的配置文件为1,修改为2
#需要同步的二进制数据库名;
replicate-do-db=syndb
#忽略同步的数据库名称
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=personalsite
replicate-ignore-db=test
#日志名
log-bin=mysql-slave-bin

配置主库时主库为从库的链接提供了一个账号,配置从库使用该账户链接主库

change master to master_host='192.168.16.10', master_user='slave1', master_password='123456';

开启主从复制及注意点

  1. 开始时从库不需要主动创建要同步的主库的库,否则从库SqlRunning无法启动,主从开启后从库会自动创建要同步的库

  2. 重启主库及从库 (也有别的方式,重启最直接和简单)

    #前面文件目录可能略有不同,找到自己的,重启
    /etc/rc.d/init.d/mysqld restart
    
  3. 开启从库,查看从库状态(此步完成,主从复制配置完成)

    #启动slave
    mysql>start slave;
    
    #查看从库状态,只有 Slave_IO_Running和Slave_SQL_Running都为Yes才可以
    mysql>show slave status\G;
    
  4. 主从复制常用命令

    #一些主库命令
    #查看当前正在写入的binlog文件
    mysql> show master status
    
    #在主库上查看已连接的slave主机
    mysql> show slave hosts;
    
    #查看所有binlog日志
    mysql> show binary logs;
    
    #查看所有binlog 事件
    mysql> show binlog events in 'mysql-bin.000003' from 145 \G;
    
    
    #一些从库命令
    #跳过指定数量错误
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
    
    #查看 relaylog 事件
    show relaylog events in 'localhost-relay-bin.000019'
    

一主多从问题

中间数据中断,数据不同步问题,或延时不同步(写完立刻要查)

解决方案一:半同步(下面有详细介绍半同步)

                                 这里异步
异步:   插入->master binlog(数据库)--->salve  relayBinlog -->binlog(数据库)

                                 这里同步               这里异步
半同步  插入->master binlog(数据库)--->salve  relayBinlog -->binlog(数据库)

这种方案意思是至少保证一台salve接收到了数据(保证到达relayBinlog),之后才返回, 实际上这种方式很耗时,所以一主多从,如果采用半同步方式保证数据一致,只会有一台从库会做半同步,半同步的salve可以切换为master

缺点:salve挂掉,master会一直等待,可以设置超时时间1-10s之间,过了超时时间变为异步复制,看业务配置时间,配置上要安装一个插件并且5.1之后才支持

解决方案二:并行复制(mysql5.7)
该方案我并未使用和配置过,只做过简单了解,可参考如下链接
http://www.ttlsa.com/mysql/mysql-5-7-enhanced-multi-thread-salve/

解决方案2.5:代理读主库
之所以是2.5是这并不是解决复制延时问题,而是向上一层考虑,为什么不能容忍延迟?一般是因为插入后要立刻读,有延迟从从库读取不到master新插入的数据,可以强制读主库,例如360atlas中查询sql语句加/master/前缀

半同步

半同步原理

在master的dump线程向slave的IO线程发送binlog时,会阻塞master的结果提交,直到slave将改变写入relayBinlog (5.7之后可配置成阻塞事务提交) 详细过程如下:

  1. 当Slave主机连接到Master时,能够查看其是否处于半同步复制的机制。

  2. 当Master上开启半同步复制的功能时,至少应该有一个Slave开启其功能。此时,一个线程在Master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的Slave已收到此事务的所有事件,或等待超时。

  3. 当一个事务的事件都已写入其relay-log中且已刷新到磁盘上,Slave才会告知已收到。

  4. 如果等待超时,也就是Master没被告知已收到,此时Master会自动转换为异步复制的机制。当至少一个半同步的Slave赶上了,Master与其Slave自动转换为半同步复制的机制。

  5. 半同步复制的功能要在Master,Slave都开启,半同步复制才会起作用;否则,只开启一边,它依然为异步复制。

半同步配置

半同步需要满足一下几个条件 要想使用半同步复制,必须满足以下几个条件:

  1. MySQL 5.5及以上版本

  2. 变量have_dynamic_loading为YES

  3. 异步复制已经存在

确认满足后按照如下步骤进行配置(最终无需重启mysql)

主库配置
  1. 安装插件

    #安装插件
    mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
    
    #查看是否安装成功
    mysql> select * from mysql.plugin;
    
  2. 设置环境

    #开启半同步复制
    SET GLOBAL rpl_semi_sync_master_enabled = 1;
    
    #主节点等待备节点返回确认信息的超时时间单位毫秒,超过这个时间后半同步复制变为异步复制
    SET GLOBAL rpl_semi_sync_master_timeout = 5000;
    
    #查看是否启动(有一个on即可)
    show status like '%semi_sync%';
    
  3. 配置文件
    编辑mysql配置问价

     vi /etc/my.cnf
    

    在该文件中追加如下内容:

    #半同步复制-master配置
    
    #开启半同步复制
    rpl_semi_sync_master_enabled=1
    
    #主节点等待备节点返回确认信息的超时时间单位毫秒,超过这个时间后半同步复制转变成异步复制
    rpl_semi_sync_master_timeout=5000
    
从库配置
  1. 安装插件

    #安装插件
    mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
    
    #查看是否安装成功
    mysql> select * from mysql.plugin;
    
  2. 设置环境

    #开启半同步复制
    mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
    
    #重启slave上的IO线程,重启后slave会在master上注册为半同步复制的slave角色
    mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
    
    ##查看是否启动(有一个on即可)
    mysql>  show status like '%semi_sync%';
    
  3. 配置文件 (到此半同步配置完成,无需重启mysql)
    编辑mysql配置文价

     vi /etc/my.cnf
    

    在该文件中追加如下内容:

    #半同步复制-slave配置
    #开启半同步复制
    rpl_semi_sync_master_enabled=1
    
半同步测试

完成以上配置后在主库同步的数据库中删除一条数据,可以看到执行时间很短,只有0.009s,执行完毕后从库该条数据也已删除,说明主从复制模式没有问题

mysql的复制

关闭从库,在从库上执行 stop slave; ,然后再在主库删除一条数据,会发现执行时间为5.002s,这是主库在等待从库响应写入relay-log,等待了5s(我们配置的),转为异步复制执行

mysql的复制

由此实验基本证明半同步开启成功

半同步问题

客户端事务在存储引擎层提交后,在得到从库确认的过程中,主库宕机了,此时,可能的情况有两种

1.事务还没发送到从库上

此时,客户端会收到事务提交失败的信息,客户端会重新提交该事务到新的主上,当宕机的主库重新启动后,以从库的身份重新加入到该主从结构中,会发现,该事务在从库中被提交了两次,一次是之前作为主的时候,一次是被新主同步过来的。

2.事务已经发送到从库上

此时,从库已经收到并应用了该事务,但是客户端仍然会收到事务提交失败的信息,重新提交该事务到新的主上。

解决方案:升级到mysql5.7
mysql5.7提供了新的半同步机制,即在主库提交事务到存储引擎层之前,阻塞,直到从库返回响应信息或超时

补充: mysql5.7对复制的优化

  1. 如上问题,mysql5.7对半同步提供了AFTER_SYNC模式解决after_commit导致的master crash主从间数据不一致问题

  2. 支持发送binlog和接受ack的异步化
    5.6版本的半同步复制,dump thread 承担了两份不同且又十分频繁的任务:传送binlog 给slave ,还需要等待slave反馈信息,而且这两个任务是串行的,dump thread 必须等待 slave 返回之后才会传送下一个 events 事务。
    5.7版本的半同步复制中,独立出一个 ack collector thread ,专门用于接收slave 的反馈信息。这样master 上有两个线程独立工作,可以同时发送binlog 到slave ,和接收slave的反馈。

  3. Binlog 互斥锁改进
    旧版本半同步复制在主提交binlog的写会话和dump thread读binlog的操作都会对binlog添加互斥锁,导致binlog文件的读写是串行化的,存在并发度的问题。 MySQL 5.7 对binlog lock进行了以下两方面优化: 1. 移除了dump thread对binlog的互斥锁 2. 加入了安全边际保证binlog的读安全

参考链接

MySQL传统复制与GTID复制原理及操作详解
MySQL半同步复制
MySQL5.6半同步复制配置及实验
MySQL 5.7 深度解析: 半同步复制技术

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Opencv中Mat矩阵相乘——点乘、dot、mul运算详解
Opencv中Mat矩阵相乘——点乘、dot、mul运算详解2016年09月02日00:00:36 \牧野(https://www.oschina.net/action/GoToLink?urlhttps%3A%2F%2Fme.csdn.net%2Fdcrmg) 阅读数:59593
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这