MySQL 之通过锁库实现主从复制及测试实例

前面在介绍 Redis 集群的文章中，讲述了构建 Redis 集群的目的是为了缓解 Redis 读写的压力，那么构建 MySQL 实现一个主从模式也是为了将读写分离，缓解单个数据库的压力。

0、关于 binlog

在数据库主从模式里，需要用到的日志记录是 binlog，binlog 有以下三种模式，

一种是基于 SQL 语句的，statement-based replication，记录的是你执行修改数据库的 SQL 语句。

一种是基于 行 数据的，row-based replication，记录的是每一行修改的数据结果，但这种方式会造成大量日志内容的问题，比如说 alter 语句更改全表某个字段的值，会把整张表的数据都记录下来。

还有一种是混合模式复制，就是综合了以上两种，mixed-based replication，会根据修改自动选择以上两种方式。

我们选用的是第三种， 混合模式复制。

而因为主从复制的实现靠的是 binlog，而 binlog 写入的数据是数据的变化，在表行级别，而非全表全库的数据。

因此，主从之间必须有基于某个时间点的相同的数据基础，只有达到这个条件才能够实现主从之间完全的复制。

1、配置介绍以及数据准备

我这边准备了两台服务器，都是 Ubuntu 18.04，MySQL 版本都是 5.7。

192.168.31.222 上的 MySQL 作为主库，

192.168.31.218 上的 MySQL 作为从库，在《高性能MySQL》这本书上称尽量避免称其为从库，应该叫 复制备库，这里不作计较。

进行主从复制的流程一般是这样的：在一台数据库上有了很多数据，为了缓解它的压力，我们在另一台数据库上重新建一个数据库，把原有数据库复制过去，其后进行同步。

为了防止在同步的过程中，有新的数据写入主库，所以需要暂时对原有数据库进行锁库的一个操作。

主库数据准备：

在主库建立一个数据库和一张表，并向其中插入几条数据，模拟真实环境，执行如下程序：

建立数据库：

CREATE DATABASE NewTest DEFAULT CHARACTER SET='utf8';

建立一张表：

CREATE TABLE test_table(id int not null auto_increment primary key, random_uuid varchar(36), created_timeed datetime);

向其中插入几条数据：

INSERT INTO NewTest.test_table (id, created_time) values(1, '2020-04-10 00:00:00'),(2, '2020-04-11 00:00:00'),(3, '2020-04-12 00:00:00');

至此，我们的数据准备完结。

2、主库设置

首先，我们需要给从库建立一个用于备份的用户，并赋予权限，以下是执行语句：

grant replication slave on *.* to 'hunter'@'192.168.31.218' identified by '123456';

其中 hunter 是从库登录的 用户名， 123456 是密码，这两个数据后面在从库连接的时候会用上。

然后执行以下语句，刷新权限：

flush privileges;

配置文件设置

我的 MySQL 的配置文件地址是在：

/etc/mysql/mysql.conf.d/mysqld.cnf

对上述文件进行编辑， 添加或者修改如下几行：

log_bin = mysql-bin	  #这是日志名称
binlog_format=mixed  #这个就是前面讲到的 binlog记录形式
server-id = 222     #这里取得是服务器ip的最后一个
binlog_do_db        = NewTest     #这个是选择备份的数据库，如有多个，可重复设置

以上文件设置好以后，重启数据库，使配置生效：

sudo service mysql restart

锁定主库，防止初始同步期间新写入数据，导致主从不同步

锁定主库，使用下面语句：

flush tables with read lock;

把主库数据拷贝出来：

mysqldump -uroot -p123456 NewTest > NewTest.sql

把这个数据库文件拷贝到从库服务器，稍后导入使用。

解锁主库：

unlock tables;

查看主库状态：

show master status;

如果看到如下图片，

说明已经从锁定主库那个节点开始了备份信息。

记下这张图片的File和Position值，后面从库指向主库的时候，会需要指向这个节点。

3、从库设置

把主库锁定前的数据库信息导入到从库，也就是前面我们 dump 出来的数据库备份。

在从服务器，也就是 218 先创建该数据库，然后导入：

CREATE DATABASE NewTest DEFAULT CHARACTER SET='utf8';

mysql -uroot -p123456 NewTest < NewTest.sql

进入从库，将从库指向主库备份开始的地方：

change master to 
master_host='192.168.31.222',
master_user='hunter',	#这个是前面开放的用户名和下面的密码
master_password='123456',
master_log_file='mysql-bin.000004',	#binlog 的 File 名称和下面的备份节点
master_log_pos=483;

从库开始备份：

在 MySQL执行：

start slave;

查看备份状态：

show slave status\G;	#\G是将输出信息格式化

当我们看到如下两个变量，都是Yes则表明主从复制已经开始了：

在主库查看进程信息，可以看到有一条来自从库服务器的连接，那就是从库在实时备份。

在主库输入：

show processlist \G; 

显示：

这个时候，在主库操作数据库表，插入、更新、删除、新建一张表，都会在从库看到相应的记录。

那么，以上就是通过锁库实现 MySQL 主从复制的全过程啦！