MySQL - 实现主从复制、主从同步功能教程1（异步复制）

作者：hangge | 2020-12-14 08:10

一、基本介绍

1，什么是 MySQL Replication?

主从复制（也称 AB 复制）允许将来自一个 MySQL 数据库服务器（主服务器）的数据复制到一个或多个 MySQL 数据库服务器（从服务器）。并且可以根据配置，选择是复制所有数据库，还是复制指定的数据库甚至指定的表。

提示：因为复制是异步的，所以从站可以不需要永久连接以接收来自主站的更新。

2，MySQL Replication 优点

横向扩展解决方案：在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
数据安全性：因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
分析：可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
远程数据分发：您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

3，MySQL Replication 原理

（1）简单来说，整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。因此要实施复制，主服务器角色的数据库必须 binarylog（二进制日志）功能，否则无法实现。

（2）MySQL 的复制有三种模式：Statement Level、Row Level、Mixed Level。复制级别的不同，会导致 Master 端二进制日志文件的生成形式的不同。

（1）Statement Level 复制

该模式是最早的复制模式，主要的流程是 Master 端将每一条会修改数据的 Query 记录下来，Slave 端在复制的时候会根据二进制文件重新执行相同的 Query。这种模式的优点是 Master 端不需要记录每一行数据的变化，二进制日志文件量小，IO 成本低，速度快。
相应的，该模式存在的缺点如下：由于记录的是执行语句，就需要额外的知道每条语句执行的上下文信息，以保证该相同的操作在 Slave 端执行时能够得到和 Master 同样的结果。但由于 MySQL 功能的不断增多，这种复制模式需要考虑的情况也就越来越多，出现 bug 的几率也就也大。从 MySQL 5.0 开始，MySQL 复制解决了大量的之前版本中出现的无法复制或复制错误的问题，但随着 MySQL 的发展，这种挑战将会日趋严峻。

（2）Row Level 复制

MySQL 开发人员意识到 Statement Level 存在的问题，于 5.1.5 开始提供 Row Level 模式。该模式的主要流程是，MySQL 二级制日志文件会将每一行数据修改都记录下来，然后在 Slave 端进行同样的修改。这种模式的优点是：日志文件不会将 SQL 语句执行的上下文记录下来，只是记录哪一条数据修改了，修改成什么样子了；这样做可以避免如某些特定情况下存储过程、trigger 的调用和触发没有被正确执行等复制问题。
同样，该模式也存在缺点：日志量的成倍增加。例如：执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。这样就大增加了复制过程的 IO 成本，导致速度下降、性能下降。

（3）Mixed Level 复制

MySQL 从 5.1.8 开始，提供 Mixed Level。该模式结合了之前两种模式的优点，规避了二者的缺点。在该模式下，MySQL 会根据执行的每一条语句来区分记录日志文件的格式。举例说明，当涉及到复杂的存储过程时，采用 Row Level，规避 Statement Level 存在的某些场景无法复制的问题；当涉及到 Alter table 等操作时，采用 Statement Level 来规避 Row Level 带来的日志量巨大的问题。

二、主从配置

1，主服务器配置（Master）

（1）首先编辑 my.cnf 配置文件：

vi /etc/my.cnf

（2）添加如下三行高亮内容，然后保存退出：

参数说明：

log-bin=mysql-bin：启用二进制日志，同时将 mysql 二进制日志取名为 mysql-bin
binlog_format=mixed：设置二进制日志的格式，这里使用 mixed
server-id=165：服务器唯一 ID，默认是 1，一般取 IP 最后一段

[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id=165

（3）然后执行命令重启 MySQL：

提示：如果是 MariaDB 则执行 service mariadb restart

service mysqld restart

（4）我们还要主服务器上为从服务器分配一个账号，这样从服务器才能到主服务器上来共享主服务器的日志文件。首先执行如下命令，进入主服务器的 mysql 界面：

mysql -uroot -p123

（5）只接着执行如下命令创建一个名为 slave 的同步账号（密码 123）

GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' IDENTIFIED BY'123';

（6）最后执行如下命令查询 master 的状态：

注意：该命令用于查看主服务器 BIN 日志的信息，执行完之后记录下这两值。然后在配置完从服务器之前不要对主服务器 MySQL 进行任何操作，因为每次操作数据库时这两值会发生改变。

show master status;

2，从服务器配置（Slave）

（1）首先编辑 my.cnf 配置文件：

vi /etc/my.cnf

（2）添加如下两行高亮内容，然后保存退出：

参数说明：

log-bin=mysql-bin：启用二进制日志，同时将 mysql 二进制日志取名为 mysql-bin
server-id=166：服务器唯一 ID，默认是 1，一般取 IP 最后一段

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

（3）然后执行命令重启 MySQL，由于我用的是 MariaDB，所以执行如下命令：

提示：如果是 MariaDB 则执行 service mariadb restart

service mysqld restart

（4）接着执行如下命令，进入从服务器的 mysql 界面：

mysql -uroot -p123

（5）然后执行如下命令配置 master：

参数解释说明：

MASTER_HOST：设置要连接的主服务器的 ip 地址
MASTER_USER：设置要连接的主服务器的用户名
MASTER_PASSWORD：设置要连接的主服务器的密码
MASTER_LOG_FILE：设置要连接的主服务器的 bin 日志的日志名称，即上面主服务器配置第 6 步得到的信息
MASTER_LOG_POS：设置要连接的主服务器的 bin 日志的记录位置，即上面主服务器配置第 6 步得到的信息（这一项不需要加引号。否则配置失败）

change master to master_host='192.168.60.165',master_user='slave',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=384;

（6）接着执行如下命令启动从服务器复制功能：

start slave;

（7）接着执行如下命令检查从服务器复制功能状态及否配置成功：

show slave status\G;

（8）如果红框的这两项（Slave_IO_Running、Slave_SQL_Running）都是 Yes，证明同步成功：

注意：两个服务器 MySQL 的 uuid 不能一样（数据库都是全新安装不会有问题，如果系统采用镜像复制的方式会造成 uuid 一样）。如果 uuid 相同会造成 Slave_IO_Running 显示为 NO，我们可以编辑 /var/lib/mysql/auto.cof 文件手动修改成不同的 uuid 即可。

3，开始测试

（1）我们在主库上创建一个测试表，并在里面添加一些数据：

（2）添加后可以看到从库也自动同步了主库的这些修改：

其他