架构师进阶:04---集群专题之（MySQL集群：主从复制、主主复制）

作者: 北方的狼72913907
来源: 51数据库
2021-09-29

一、MySQL集群

①高可用：主主同步
②可并发：主从同步（读写分离）、分库分表
③自动扩容：部署机器好扩展

二、分库分表

分表

将数据分为多个表存储进行，解决查询速度慢的问题

分库

将数据分为多个数据库进行存储，解决高并发插入多数据的问题

分库分表

将上面两者结合在一起

三、MySQL复制

MySQL的复制在另一篇也介绍过，可以参阅：https://blog.csdn.net/qq_41453285/article/details/104381494
概念：MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将mysql的某一台主机的数据复制到其它主机（slave）上，并重新执行一遍来实现。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循坏，这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知的更新

MySQL复制解决的问题

数据分布（data distribution）

负载平衡（load balancing）

数据备份（backup），保证数据安全

高可用性与容错行（high availability and failover）

实现读写分离，缓解数据库压力

MySQL复制数据的方式

binlog_format选项：配置文件的binlog_format选项决定了MySQL复制数据时基于什么方式

MySQL目前支持三种方式：
基于SQL语句的复制（statement-based replication, SBR）

基于行的复制（row-based replication，RBR）

混合类型的复制（mixed-based replication，MBR）

详情可以参阅https://blog.csdn.net/qq_41453285/article/details/104110026中对binlog的介绍

MySQL复制的流程

如下图所示：
①master记录二进制日志：在每个事务更新数据完成之前，master在二进制日志（Binary log）记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务

②slave将master的Binary log拷贝到它自己的中继日志：slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，I/O线程将这些事件写入自己的中继日志（Relay log）

③SQL线程处理该过程的最后一步：SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作

MySQL复制的模式

主从复制：主库授权从库远程连接，读取binlog日志并更新到本地数据库的过程；主库写数据后，从库会自动同步过来（从库跟着主库变）

主主复制：主从相互授权连接，读取对方binlog日志并更新到本地数据库的过程；只要对方数据改变，自己就跟着改变

四、主从复制

主从复制的优点：
- 主库写，从库读，降压：在从服务器可以执行查询工作（即我们常说的读功能），降低主服务器压力
- 确保数据安全：在从主服务器进行备份，避免备份期间影响主服务器服务
- 提升性能：当主服务器出现问题时，可以切换到从服务器

第一步（环境介绍）

下面开始介绍主从复制，其中：
111.229.177.161：我的一台云服务器，作为主机（Master）

192.168.0.103：我的一台虚拟机，作为从机（Slave）

两台主机的MySQL版本都是5.7
mysql --version
查看Master的数据库，其有一个test数据库，数据库内有一个student表
show databases;

use test;

show tables;
下面我们就将Master端的这个test数据库同步到Slave端

第二步（设置Master端配置文件）

第一步（Master）：设置master的数据库配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改的内容有：
server-id：Master服务ID（此处我以IP地址的最后3位表示，随意你如何更改，但是不要与其他主机的server-id重复）

log-bin：开启binlog，并指定binlog的存储位置

binlog-do-db：binlog同步的数据库，此处我们设置为test，也就是binlog中只记录test数据库的信息（可选项）

binlog-ignore-db：不同步哪些数据库，此处我们设置为mysql（可选项）

还有其他很多参数，例如：
sync_binlog：默认值为0表示每执行一个事务就将binlog的内容同步到磁盘（可选项）

binlog_checknum：跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none（可选项）

binlog_format：bin-log日志文件格式，设置为MIXED可以防止主键重复（可选项）

关于这些参数就不详细介绍了，详细介绍可以参阅：https://blog.csdn.net/qq_41453285/article/details/104110026

第二步（Master）：之后重启数据库
sudo service mysql restart

第三步（Master创建新用户）

下面我们在Master上创建一个用户，做复制来用，因为Slave复制时需要制定用户

第一步（Master）：在Master上创建一个名为replication的用户，密码为123456
# 创建用户
create user replication@'%' identified by '123456';

# 授权replication用户所有权限, 并且任何地址(%)登录的replication都可以使用
# 如何要指定IP地址才可以使用这个权限，可以把%改为指定的IP
grant all privileges on *.* to replication@'%' identified by '123456' with grant option;

# 刷新权限
flush privileges;
备注：
上面我们授予了replication用户所有权限

如果我们只想授予replication复制的权限，可以将上面的grant命令设置为下面的格式，将all privileges改为“relication slave,relication client”即可

下面我们“主主演示案例”中就是用下面的格式演示

关于授权详情可以参阅：https://blog.csdn.net/qq_41453285/article/details/104055973
grant replication slave,replication client on *.* to replication@'%' identified by '123456' with grant option;
第二步（Master）：查看授权
show grants;

第四步（复制前的准备工作）

说明：
因为Master端的test数据库中有数据，而Slave端没有，如果直接进行复制，那么Master与Slave的数据就会不一致，因此为了保持数据一致，我们先将Master中的数据导出来，然后导入Slave中，之后再进行测试

如果是新建的数据库，那么可以不进行这一步（根据你的实际需求进行）

第一步（Master）：输入下面的锁定Master数据库，放置在备份数据的时候有新写入数据（这一步是可选的，你也可以不做这一步，此处我们为了好玩锁定罢了，下面我们需要调用unlock tables解锁）
flush tables with read lock;
第二步（Master）：重新开启一个Master的命令行，备份test数据库，保存到当前目录下，名为test_backup.sql
mysqldump -uroot -p --databases test > ./test_backup.sql

ls ./test_backup.sql
先不解锁Master数据库，等下复制流程建立完成之后再解锁数据库

第三步（Slave）：将Master端导出的那个test_backup.sql上传到Slave端，然后在进入Slave端数据库创建一个test数据库，再导入刚才那个test_backup.sql
# 先创建数据库, 要不然下面导入的时候会出错
create database test;

# 导入数据库文件
source ./test_backup.sql

show databases;

第五步（设置Slave端配置文件）

第一步（Slave）：设置slave的数据库配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改的内容有：
bind-address：因为我这个数据库是新安装的，所以把bin-address注释掉

server-id：Slave服务ID（此处我以IP地址的最后3位表示，随意你如何更改，但是不要与其他主机的server-id重复）

log-bin：开启binlog，并指定binlog的存储位置

replicate-do-db：指定要同步哪些数据库，此处我们设置为test（可选的。可以指定多个数据库；如果忽略这个参数，那么就复制所有数据库，除replication-ignore-db指定的除外）

replicate-ignore-db：忽略复制哪些数据库（可选的）

备注：如果只想针对数据库的某些表进行复制同步，那么可以使用replicate-wild-do-table指定。例如：
# 对test1数据库进行复制
relicate-do-db = test1
# 只对test1数据库的student表进行复制
replicate-wild-do-table = test1.student;

# 对test2数据库进行复制
relicate-do-db = test2
# 只对test2数据库的student表进行复制
replicate-wild-do-table = test2.student;
第二步（Slave）：重启MySQL
# 不知道为啥我那台机器没有restart, 所以只能用下面的方式重启了
sudo service mysql stop

sudo service mysql start

第六步（开始复制）

第一步（Master）：Master端数据库输入下面的命令，查看当前使用的binlog文件名字是什么，并且当且写入的索引为多少。可以看到分别为“mysql-bin.000002”和“154”
show master status;
第二步（Slave）：Slave进入数据库，输入下面的命令请求复制Master端，各个参数意义如下：
master_host：要复制的数据库主机的地址

master_user：复制操作中指定的用户，就是我们上面在Master中创建的replication

master_password：用户的密码

master_log_file：要复制的Master的binlog文件名，就是上面我们在Master中看到的mysql-bin.000002（一定要与上面的一直，否则命令出错）

master_log_pos：对应的SQL执行的偏移位置，就是上面我们在Master中看到的154（一定要与上面的保持一致，否则命令出错）
stop slave;

change master to master_host='111.229.177.161',master_user='replication',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;
第三步（Slave）：输入下面的命令开始同步
start slave;
第四步（Slave）：然后查看Slave端的状态
show slave status\G
第五步（Master）：因为在上面的“第四步”中我们锁定了Master不可以写入数据，只可以读，现在我们复制配置好了，因此来到Master端，输入下面的命令解锁数据库
unlock tables;

第七步（测试）

第一步（Master、Slave）：上面我们已经复制已经配置好了，在开始向Master写入数据之前，查看一下两者的数据是否一致，结果一致
use test;

select * from student;
第二步（Master）：来到Master中，删除id为2的那一条数据，然后新增一条数据
delete from student where id =2;

insert into student values(4,'Python', 20);

select * from student;
第三步（Slave）：来到Slave端，可以看到也接收到了这个数据，说明复制成功

第八步（停止复制）

第一步（Slave）：输入下面的命令停止复制，然后查看状态，可以看到两个线程都停止工作了
stop slave;

show slave status\G
第二步（Master）：现在我们来到Master端，删除id为3的数据
delete from student where id=3;

select * from student;
第三步（Slave）：来到Slave端查看，发现数据并没有删除，因为早已经断开复制关系了

附加，如果在主从环境部署一段时间后，发现主从不同步，那么可以参阅：
- https://www.cnblogs.com/kevingrace/p/6261111.html中第（4）步的第二种方法
- https://www.cnblogs.com/kevingrace/p/6261091.html

五、主主复制

主主复制解决的问题与应用场景：
- 根据主从复制的原理我们知道，一般来说Master执行写入操作，Slave只执行读操作，但是如果Slave执行了写操作，那么就会造成Master与Slave的数据不一致
- 主主复制就是两台主机之前一起同步数据，两台机器上的数据都可以保持一直

主主复制中的“自增长重复”问题

因为两台机器之间可以互相写入数据，因此可能会出现自增长重复问题

场景分析：
有一个test表，其有一个ID字段为auto_increment类型

停掉A机器，B在ID字段上插入一条数据，导致ID加1

停掉B机器，A在ID字段上插入一条数据，导致ID也加1

然后同时启动A和B，发现主键ID重复，因此报错

解决方法：
MySQL提供了两个配置文件选项，一个为auto_increment_offset，另一个为auto_increment_increment
auto_increment_offset：表示自增长字段的值从多少开始

auto_increment_increment：自增长字段每次递增的值

因此，我们可以在A和B两台机器中这样配置选项，从而导致不会出现“自增长重复问题”，例如：
在A中将auto_increment_offset设置为1、将auto_increment_increment设置为2。这样的话，其自增长值就为1、3、5......以此类推

在B中将auto_increment_offset设置为2、将auto_increment_increment设置为2。这样的话，其自增长值就为2、4、6......以此类推
# A中的配置
auto_increment_offset = 1
auto_increment_increment = 2

# B中的配置
auto_increment_offset = 2
auto_increment_increment = 2

下面开始主主复制实战演示

第一步（环境介绍）

现在我们有两台机器：
192.168.0.101（就当做是Master吧）

192.168.0.102（就当做是Slave吧）

两台主机的MySQL版本都是5.7
mysql --version

第二步（配置Master的配置文件）

修改MySQL配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
?修改的内容有：
server-id：Master服务ID（此处我以IP地址的最后3位表示，随意你如何更改，但是不要与其他主机的server-id重复）

log-bin：开启binlog，并指定binlog的存储位置

binlog-ignore-db：不同步哪些数据库，此处我们设置为mysql（可选项）

此处我们没有设置binlog-do-db参数，表示复制所有数据库

auto-increment-offset：在上面介绍过了，此处从1开始

auto-increment-increment：在上面介绍过了
server-id = 101
log-bin = /var/log/mysql/mysql-bin.log
log-ignore-db= mysql, information_schema
auto-increment-increment = 2
auto-increment-offset = 1
修改的内容有：配置完成之后重启服务器
sudo service mysql restart
?

第三步（配置Slave的配置文件）

修改MySQL配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改的内容有：
server-id：Master服务ID（此处我以IP地址的最后3位表示，随意你如何更改，但是不要与其他主机的server-id重复）

log-bin：开启binlog，并指定binlog的存储位置

binlog-ignore-db：不同步哪些数据库，此处我们设置为mysql（可选项）

此处我们没有设置binlog-do-db参数，表示复制所有数据库

auto-increment-offset：在上面介绍过了，此处从2开始

auto-increment-increment：在上面介绍过了
server-id = 102
log-bin = /var/log/mysql/mysql-bin.log
log-ignore-db= mysql, information_schema
auto-increment-increment = 2
auto-increment-offset = 2
修改的内容有：配置完成之后重启服务器
sudo service mysql restart

第四步（在Master上创建用户并授权）

下面我们在Master上创建一个新用户，名为repl，密码为123456，用来做复制使用
# 创建用户
create user repl@'%' identified by '123456';

# 为repl用户授予relication slave,relication client两个权限
grant relication slave,relication client on *.* to repl@'%' identified by '123456' with grant option;

# 刷新权限
flush privileges;
输入下面的命令查看权限表
show grant;

第五步（在Slave上创建用户并授权）

与上面一样的，下面我们在Slave上创建一个新用户，名为repl，密码为123456，用来做复制使用
# 创建用户
create user repl@'%' identified by '123456';

# 为repl用户授予relication slave,relication client两个权限
grant relication slave,relication client on *.* to repl@'%' identified by '123456' with grant option;

# 刷新权限
flush privileges;
输入下面的命令查看权限表
show grant;

第六步（锁定两台机器）

为了防止在复制之前有新的数据写入，我们将两台机器同时用下面的命令锁定
flush tables with read lock;

第七步（Slave开启复制操作）

第一步（Master）：Master查看一下当前Master的状态，结果为
File为master-bin.000001

Position为158
show master status;
第二步（Slave）：先解锁Slave端，下面开始复制
unlock tables;
第三步（Slave）：输入下面的命令开始请求复制
slave stop;

change master to master_host='192.168.0.101',master_user='repl',master_password='123456',master_log_file='master-bin.000001',master_log_pos=158;

start slave;
第四步（Slave）：查看当前复制状态
show slave status;

第八步（Master开启复制操作）

第一步（Slave）：Slave查看一下当前Master的状态，结果为
File为master-bin.000001

Position为256
show master status;
第二步（Master）：先解锁Master端，下面开始复制
unlock tables;
第三步（Master）：输入下面的命令开始请求复制
slave stop;

change master to master_host='192.168.0.102',master_user='repl',master_password='123456',master_log_file='master-bin.000001',master_log_pos=256;

start slave;
第四步（Master）：查看当前复制状态
show slave status;

第九步（演示）

在两端分别操作数据库，都会相应的更改