MySQL主从同步实践

本文记录配置MySQL主从同步的流程。

说明：

这里测试使用两个MySQL实例实现，另外，也可以基于两个数据库实现。多实例安装MySQL可以参考博文《LNMP平台搭建---MySQL安装篇(附带多实例安装步骤)》。

------------------------------------------- 华丽的分界线 -------------------------------------------

MySQL支持单向(Master --> Slave)、双向(Master <--> Master，一般不这样做)、链式级联(A --> B --> C --> D)、实时、异步复制。在复制过程中，一台服务器充当主服务器(Master)，而一个或多个的其他服务器充当从服务器(Slave)。采用MySQL主从复制，有利于数据库架构的健壮性，提升访问速度和易于维护管理。

在生产工作中，大多数应用的MySQL主从同步都是异步的复制方式，即不是严格实时的数据同步。当配置好主从复制后，所有对数据库内容的更新就必须在主服务器上进行，以避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新不一致而导致发生冲突。

那么，如何保证只写主服务器：

1. 忽略授权表方式的同步，然后对从服务器上的用户仅授权select读权限，不同步mysql库，这样就可以保证主库和从库相同的用户可以授权不同的权限。

2. 在启动权限加--read-only参数或从库的my.cnf增加read-only参数确保从库只读。read-only参数可以让slave服务器只允许来自slave服务器线程或具有SUPER权限的用户的更新，不接受来自普通用户的更新。

实际上，更多同时采用第一和第二种方式来保证应用只写主库。

如何实现MySQL主从读写分离：

1. 通过程序实现读写分离(性能、效率最佳)

通过程序设置多个连接文件轻松实现对数据库的读写分离，当select时，去连接读库的连接文件，当update、insert、delete时连接写库的连接文件。

2. 通过软件实现读写分离

Mysql-proxy, Amoebad等代理软件

3. 自己开发dbproxy

大概了解相关背景后，下面开始实践：

环境说明：

数据库版本:  MySQL5.7.19

之前安装使用的单实例安装，端口在****，其安装目录及数据目录如下所示：

已存在的作为主库Master，然后新建一个目录作为新实例的目录: mkdir data_bak。

1. 打开master的binlog功能：

然后重启数据库，再看看data目录下文件：

有logbin文件和index文件输出了。也可如下查看:

2. 新增从库实例

新建目录，并拷贝主库的配置文件，然后再修改配置文件：

修改配置文件，因为我没做级联，从库可以不开binlog，注意server-id需要唯一不能重复：

(如果从库还需要级联从库，需要打开log-bin和log-slave-updates参数开关)

初始化数据库(目录需要为空，因此我先把my.cnf复制到上级目录，初始化数据目录后在移动进来)并启动该从库：

chown -R mysql:mysql /usr/local/mysql/data_slave   记得更改目录权限为mysql

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data_slave/

cp ../my.cnf .

启动：

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data_slave/my.cnf --datadir=/usr/local/mysql/data_slave --user=mysql &

使用初始化给的默认root密码登录后再修改密码即可。

从库实例安装完成了。

3. 在主库创建一个账号，用于同步

这里仅允许本机同步，如果跨机同步，修改为对应ip即可。

replication slave为mysql同步的必须权限，此处不要授权为all。

然后flush privileges刷新权限。

4. 主库配置

主库加读锁，禁止其他用户写数据

保持该连接窗口打开，重新开启一个终端连接到服务器去备份数据:

/usr/local/mysql/bin/mysqldump -u root -p****** -A -B --events | gzip > /opt/rep.sql.gz

备份完后，主库解除读锁： unlock tables;(否则会出现创建数据库等更新操作时，没有响应，是因为表被锁住了)

附：

备份完后，建议再看看binlog状态，确认在备份期间，binlog位置没有变化，保证备份的数据是最新的。如果发生了变化，说明备份期间表没有锁到。

如果执行命令"show master status"后没有结果输出，是因为主库binlog开关没有打开或没有生效。

注意：

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

interactive_timeout = 60

wait_timeout = 60

默认的时长执行命令 "show variables like '%timeout%';" 查看。

5. 把刚刚备份的数据导入到从库：

gunzip /opt/rep.sql.gz

/usr/local/mysql/bin/mysql -u root -p***** -S /usr/local/mysql/data_slave/mysql.sock </opt/rep.sql

这里，相当于对主库做了一个全备份

6. 登录从库，配置连接主库的信息：

(注意，刚刚在主库新建rep账户时限定了ip地址连接，这里要匹配上)

然后执行start slave开启，可以看看，从库线程是否正常工作，图中圈住的地方显示YES表示从库已正常连接上主库并等待主库发送binlog了：

IO线程负责接收主库的binlog保存为relaylog，然后SQL线程负责解析relaylog为SQl语句并执行。

Seconds_Behind_Master: 0，秒数为0说明从库与主库所有数据一样，没有落后主库。

7. 测试：

在主库新建一个数据库haha：

再登录从库看看：

从库也有了haha这个数据库，顺便看看从库的relaylog文件：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data_slave/relay-bin.000006，可以看到里面有一句:create database haha;

说明从库正确同步了主库的语句并执行了。

最后，总结一下MySQL主从复制原理过程：

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服务器需要从新Binlog日志的哪个文件哪个位置开始请求新的Binlog日志内容。

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

6. 经过上面过程，就可以确保在Master端和Slave端执行了同样的SQl语句。当复制状态正常的情况下，Master端和Slave端d的数据是完全一样的。