MySQL系列（五）：MySQL主从复制

mysql主从复制介绍

mysql支持单向、双向、链式级联、实时、异步复制。复制过程中，一台服务器充当主服务器（master），而一个或多个其他的服务器充当从服务器（slave）。复制可以是单向：M==>S，也可以是双向M<==>M，当然也可以多M环状同步等。如果设置了链式级联复制，那么，从（slave）服务器本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器。链式级联复制类似A-->B-->C-->D的复制形式。

1）单向主从同步逻辑图：

2）双向主主同步逻辑图：

3）线性级联单向双主同步逻辑图：

4）环状级联单向多主同步逻辑图：

5）环状级联单向多主多从同步逻辑图：

在当前的生产工作中，大多数应用的MySQL主从同步都是异步的复制方式，即不是严格实时的数据同步。

实时和异步：

当配置好主从复制后，所有对数据库内容的更新就必须在主服务器上进行，以避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新不一致而导致发生冲突。
那么，为什么所有的更新都要在主服务器上进行呢？我们又如何确保用户在主服务器上更新呢？

第一种防止数据写入库的方法：

生产环境中一般会采用忽略授权表方式的同步，然后对从服务器（slave）上的用户仅授权select读权限。不同步mysql库，这样我们就保证主库和从库相同的用户可以授权不同的权限。

第二种防止数据写入从库的方法：

除了上面再从库仅做select的授权外，还可以在slave服务器启动选项增加参数或在my.cnf配置文件中间read-only参数来确保从库只读，当然授权用户read-only从参数二者同时操作效果更佳，这也是我们生产环境中使用的方案。read-only参数可以让slave服务器只允许来自slave服务器线程或具体super权限用户的更新。可以确保slave服务器不接受来自普通用户的更新，slave服务器启动选项增加--read-only也是同样功能

mysql主从复制的应用场景

mysql主从复制有利于数据库架构的健壮性、提升访问速度和易于维护管理

1）主从服务器互为备份

主服务器架构的设置，可以大大的加强数据库架构健壮性，例如：当主服务器出现问题时，我们可以人工或自动切换到从服务器继续提供服务。这类似我们的nfs服务器存储数据通过inotify+rsync同步到备份的nfs非常类似，只不过mysql的同步方案，是其自带的工具。非人为的硬件，服务故障，对于人为的执行drop,delete无能为力。

2）主从服务器读写分离分担网站压力

主从服务器架构可以通过程序（php,java）或者代理软件（mysql-proxy,amoeba）对用户（客户端）的请求实现读写分离，即通过在从服务器上仅仅处理用户的select查询请求，降低用户查询响应时间及读写同时在主服务器带来的压力，对于更新的数据（update,insert,delete）乃然交给主服务器处理，确保主服务器和从服务器保持实时同步。如果网站是以非更新（以浏览为主）为业务为主的业务，如blog,www首页展示等业务，查询请求比较多，这是从服务器的读写分离负载均衡策略就很有效了，这就是传说中的读写分离数据库结构。

mysql主从复制原理过程

简单描述下mysql replication的复制过程

1）slave服务器上执行start slave，开启主从复制开关。

2）此时，slave服务器的IO线程会通过在master上授权的复制用户权限请求连接master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令时指定的）之后binlog日志内容；

3）master服务器接收到来自slave服务器的IO线程的请求后，master服务器上负责复制的IO线程根据slave服务器的IO线程请求的信息读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给slave端的IO线程。返回的信息中除了binlog日志内容外，还有本次返回日志内容后在master服务器端的新的binlog文件名称以及在binlog中的下一个指定更新位置；

4）当slave服务器的IO线程获取到来自master服务器上IO线程发送日志内容及日志文件及位置点后，将binlog日志内容依次写入slave端自身的relay log（即中继日志）文件（mysql-relay-bin.xxxxxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志是能够告诉master服务器需要从新binglog日志的那个文件那个位置开始请求新的binlog日志内容。

5）slave服务器端的SQL线程会实时的检测本地relay log中新增加的日志内容，然后及时把log文件中的内容解析成在master端曾经执行的sql语句的内容，并在自身slave服务器上按语句的顺序执行应用这些sql语句，应用完毕后清理应用过的日志。

6）经过了上面的过程，就可以确保在master端和slave端执行了同样的sql语句，当复制状态正常的情况下，master端和slave端的数据完全一样的，mysql的同步机制是有一些特殊的情况的，具体请参考官方的说明。

注意：

1、同步的时候从库有两个线程来完成（IO、SQL），主库有一个线程来完成（IO）

2、要在从库上配置连接主库的ip，用户名，账号，密码，连接的位置已经host点

3、在开启开关之前确保主从数据是一致的

4、在开关之前在主库上建立专门给从库同步的账号

5、主库要打开binlog开关，否则无法实现同步

6、从库打开开关的过程实际上就是让两个线程工作的过程

复制准备

1）定义服务器角色

主库（mysql master）：[ip为192.168.0.30，端口为：3306]

从库（mysql slave）：[ip为192.168.0.31，端口为：3307]

提示：1、一般常规做主从复制，主从服务器多数在不同的机器上，并且监听的端口均为默认的3306。我的实验环境是克隆的虚拟机，相同端口会报错，所以我用了不同的端口。

2）数据库读法的约定

主库，也可称为master

从库，也可为slave

配置主库

首先打开binlog日志的功能，执行vi /etc/my.cnf配置文件，按如下两个参数内容修改：

[mysqld]
server-id = 1
log-bin = mysql-bin

提示：

1、上面两个参数要放在my.cnf中的[mysqld]模块下，否则会出错。

2、server-id的值使用服务器ip地址的最后8位如19，目的是避免不同机器或实例ID重复（不适合多实例）。

3、要先在my.cnf配置文件中查找相关参数，并按要求修改。不存在时添加参数，切记参数不能重复。

4、修改my.cnf配置后需要重启数据库

检查配置后的结果：

检查binlog记录

建立用于同步的账号rep

登陆master主数据库，建立用于主从复制的账号rep:

mysql> grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'yjscloud123';
mysql> flush privileges;

# replication slave 为mysql同步的必须权限，此处不要授权all.
# *.*表现所有库所有表，可以指定具体的库和表进行复制。
# 'rep'@'192.168.0.%' rep为同步账号。192.168.0.%为授权主机网段，使用了%表示允许整个192.168.0.0网段一rep用户访问。
# identified by 'yjscloud123'; yjscloud为密码，实际环境时复杂一点为好

对数据库锁表只读（当前窗口不要关掉）

备份前锁表防止有新的数据写入

mysql> flush table with read lock;

提示：这个锁表命令的时间，在不同的引擎的情况，会受下面参数的控制，锁表时，如果超过设置时间不操作会自动解锁。

默认情况下的时长为：

查看主库状态

查看备份点：查看主库状态，即当前binlog日志文件名和二进制binlog日志偏移量

show master status; #命令显示的信息要记录在案，后面的从库复制时是从这个位置开始的

导出数据库数据

打开新窗口，导出数据库数据，如果数据量很大（100G+），并且允许停机，可以停库直接打包数据文件迁移

mysqldump -uroot -pyjscloud -A -B --events|gzip >/opt/rep_bak.$(date +%F).sql.gz

注意-A 表示备份所有库。-B表示增加use DB和drop等（导库时会直接覆盖原有的）

导库后，解锁主库，恢复可写；

mysql>unlock tables;

把主库导出的mysql数据迁移到从库

这步常用命令有scp,rsync等。操作这里不再略去

从库上执行操作

设置server-id值并关闭参数

数据库的sever-id一般在LAN内是唯一的，这里的server-id要和主库及其他从库不同，注释掉从库的binlog参数配置。

执行vi /etc/my.cnf编辑my.cnf配置文件，按如下两个参数内容修改：

[mysqld]
server-id = 3
#log-bin= mysql-bin

1、上面两个参数要放在my.cnf中的[mysqld]模块下，否则会出错。

2、server-id的值使用服务器ip地址的最后8位如19，目的是避免不同机器或实例ID重复（不适合多
实例）。

3、要先在my.cnf配置文件中查找相关参数，并按要求修改。不存在时添加参数，切记参数不能重复。

4、修改my.cnf配置后需要重启数据库

有两种情况需要打开log-bin，记录数据库更新的sql语句：

a、级联同步A-->B-->C那中间的B就要开启log-bin

b、在从库做数据库备份，数据库备份必须要有备份和binlog日志，才是完整的备份

检查配置后的结果

重启从数据库
/etc/init.d/mysqld restart

还原主库导出的数据到从库

[root@slave-1 opt]# gzip -d rep_bak.2018-4-23.sql.gz   #解压目标数据库备份
[root@slave-1 opt]# mysql -uroot -pyjscloud 

mysql -uroot -pyjscloud

CHANGE MASTER TO
MASTER_HOST='192.168.0.30',     # 这是主库的ip
MASTER_PORT=3306,                  #这里是主库的端口，从库端口可以和主库不同
MASTER_USER='rep',                    # 这里是主库上建立的用于复制的用户rep
MASTER_PASSWORD='yjscloud123',     #这里是rep的密码
MASTER_LOG_FILE='mysql-bin.000005',     #这里是show master stauts时看到的二进制日志文件名称，注意不能多空格
MASTER_LOG_POS=338;         # 这里是show master status时看到的查看到的二进制日志偏移量，注意不能多空格

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes   #负责从库去主库读取BINLOG日志，并写入从库的中继日志中
Slave_SQL_Running：Yes   #负责读取并执行中继日志中的BINLOG，转换SQL语句应用到数据库