上篇文章详细介绍了MySQL数据库的单机多实例搭建,本篇文章将在上篇文章的基础上介绍MySQL主从复制过程,其中常见的复制架构有:异步复制,半同步复制及同步复制。常用的复制架构有异步复制及半同步复制!

一、常见的复制架构

1、主主复制

(1)结构图:

(2)说明:主主复制即复制的两个实例互为主从,两个库中都可以同时读和写;

(3)优点:

2、一主一从

(1)结构图:

(2)说明:指的是在两个数据库实例中,一个实例扮演着主库的角色,另一个实例扮演着从库的角色。这种方案中,从库通常用来作为备份使用,提供服务的多为主库;

(3)优点:

3、一主多从

(1)结构图:

(2)说明:指的是在多个数据库实例中,只包含了一个主库,其他实例都作为该主库的从库,这种架构是业务规模较大场景中的一种复制架构;

(3)优点:

二、主从复制的原理和过程

1、主从异步复制的原理

主库上的二进制bin-log中记录主库的所有DML操作,同时在主库上运行有一个IO线程,用于响应从库上的bin-log日志读取请求;在从库上运行有一个IO线程和一个SQL线程,IO线程会实时通过网络请求去从库上读取bin-log日志,然后写入到自身的relay-log日志文件中,同时运行在从库上的SQL线程会去解析并读取relay-log,然后在自身库上执行读取到的SQL,完成主从数据的同步,示意图如下:

2、主从同步的工作过程

(1)详细过程

(2)以上详细过程可总结为三步

三、MySQL异步复制搭建过程(单机多实例介绍,沿用上篇文章中搭建的多实例环境)

1、环境准备

2、编辑3306实例的配置文件,打开该实例的二进制日志,并修改server-id,如下

参数解释:

3、重启主库

4、备份主库的数据

参数说明:

5、登陆主库,然后创建复制账户

6、查看主库的二进制日志文件及位置点

7、将主库导出的数据导入从库中

8、修改从库的配置文件,开启relay-log日志,并设置server-id,如下

9、修改从库上的master指向,使其指向主库,并且从主库上最新的二进制日志和位置点开始同步,然后启动主从同步

注意:上述结果中Slave_IO_Running和Slave_SQL_Running都为Yes表示主从同步成功,如果为Connecting...,可以等待一会再次查看,如果为No,表示同步失败;

参数说明:

开启主从的另外一种方法是分别开启SQL线程和IO线程,如下:

10、验证,登陆主库,然后创建数据库,查看从库是否可以正常同步

11、至此,MySQL的主从复制搭建完毕。

12、主从同步中常见的问题

(1)从库的IO线程无法连接,通过"show slave status G"可以查看到具体的错误信息

原因1:在主库上创建的用户授权错误,导致从库无法远程连接主库

解决办法1:在主库上通过"show grants for ‘user‘@‘ip‘;"查看授权是否正确,如果错误,重新授权即可

原因2:如果是独立主机上的两个主从数据库实例,授权正确的情况下,可能是由于主库的防火墙拦截导致从库无法连接主库

解决办法2:关闭主库的防火墙,或者在主库所在服务器添加防火墙规则,允许从库的tcp连接

(2)从库启动的时候提示server-id冲突,导致无法同步主库上的数据

原因:主从库配置文件中的server-id相同了

解决办法:将主库可从库配置文件中的server-id改为不同,重新开启从库上的同步即可

(3)在从库上执行了创建库或者表的操作,然后在主库上又执行了一遍,导致同步错误,如下:

原因:从库上创建了库,主库上再次创建,从库会将主库上的创建过程再次应用到从库,导致从库上创建同名的库,发生错误

解决办法:停止从库,然后设置sql_slave_skip_count,使其跳过同步主库创建库的操作,从下一个操作开始同步,如下:

针对直接写从库的操作,可以再从库上创建一个普通用户,授予其部分操作权限,然后设置从库的只读,通过在从库的配置文件中增加"read-only"参数来设置。但是注意,这个参数对而且只对非super用户生效,对root用户没有任何效果。

13、再生产场景下如何保证主库上的用户可以有写权限,从库上的用户只有读权限

方法1:在设置从库同步的时候,排除对mysql系统库的同步,通过在配置文件中指定binlog_ignore_db=mysql来排除不需要同步的库,或者在配置文件中指定binlog_do_db=db_name只来同步需要同步的库,然后分别在主库上创建可以写的用户,在从库上创建只能读的用户;

方法2:在未排除任何库的情况下,先在主库上创建可以读写的用户,然后在从库中从新回收用户的写权限;

方法3:在主库和从库上创建不同的用户,然后分别授予不同的权限,使得主库只能写,从库只能读;

四、MySQL半同步搭建过程(介绍过程仍然使用单机多实例的环境)

1、定义

是介于异步复制和全同步复制之间的一种复制方式,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。

2、优缺点

(1)优点:有效的提高了数据的安全性,需要等到数据写到从库之后才返回给客户端;

(2)缺点:因为需要等待至少一个从库接收到并写入relaylog中,索引会造成一定的网络延迟,需要在网络延迟较低的环境中使用

3、搭建过程

(1)前提条件:

(2)查看主库和从库上的have_dynamic_loading变量

(3)登陆主库,在主库上安装半同步插件

注:如果想卸载半同步插件,可以使用如下命令:

(4)登陆从库,安装从库上的半同步插件

注:从库上的半同步插件,也可以使用如下命令完成卸载:

(5)查看插件是否加载成功

主库:

从库:

(6)配置并开启主库的半同步复制,然后重启主库

(7)配置并开启从库的半同步复制,然后重启从库

(8)重启从库上的IO线程

(9)查看主库和从库上的半同步复制是否在运行

登录主库查看:

登录从库查看:

上述结果表示主库和从库上的半同步复制运行正常。

(10)验证半同步复制是否正常

验证方法:正常在主库上创建一张表,会立刻返回,耗时0.1s。关闭从库的io线程,然后在主库上执行建表操作,会发现,主库上回阻塞10秒之后才会返回,而这个时间正好和主库上的rpl_semi_sync_master_timeout相同,表示半同步起作用了,主库的DDL操作需要等到从库应用完relaylog之后才返回;

至此,MySQL的半同步复制搭建完成。

4、半同步搭建中常见问题

(1)主从不能正常同步:和主从同步无法正常复制的排查方法相同

(2)不能正常安装半同步插件

原因1:可能是版本问题

解决办法1:查看MySQL实例的版本,如果版本问题,更换新版本重新安装即可

原因2:MySQL的安装目录中未包含用于半同步复制的共享库

解决办法2:找到该版本对应的半同步共享库,然后重新安装

五、全同步复制

同步复制在所有复制方案中最安全,但是性能最差,而且需要使用DRBD(分布式复制块设备)来完成数据的同步,DRBD是一种类似于"rsync+inotify"的架构,通常使用较少,几乎不用,此处不做详细介绍。

到此,MySQL的主从复制介绍完毕,主从复制是一块很大的内容,包括延迟排查,数据一致问题、快速主从搭建及主从复制的高可用,后面会继续写文章介绍,欢迎转发评论!

后续文章将更新在个人小站上,欢迎查看。

MySQL数据库运维之主从复制搭建

标签:远程连接   架构   运维   更换   只读   l数据库   总结   建表   不同的