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

程序员文章站 2022-06-27 21:06:22

一、MySQL集群①高可用：主主同步②可并发：主从同步（读写分离）、分库分表③自动扩容：部署机器好扩展二、分库分表分表将数据分为多个表存储进行，解决查询速度慢的问题分库将数据分为多个数据库进行存储，解决高并发插入多数据的问题分库分表将上面两者结合在一起三、MySQL复制MySQL的复制在另一篇也介绍过，可以参阅：https://blog.csdn.net/qq_41453285/article/details/104381494概念：MyS...

一、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;

第九步（演示）

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

本文地址：https://blog.csdn.net/qq_41453285/article/details/107182830

上一篇：转：初步理解一下：SOA, SOAP, Web Service, WSDL等

下一篇： Docker安装logstash，配置多管道

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

一、MySQL集群

二、分库分表

分表

分库

分库分表

三、MySQL复制

MySQL复制解决的问题

MySQL复制数据的方式

MySQL复制的流程

MySQL复制的模式

四、主从复制

第一步（环境介绍）

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

第三步（Master创建新用户）

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

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

第六步（开始复制）

第七步（测试）

第八步（停止复制）

五、主主复制

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

第一步（环境介绍）

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

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

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

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

第六步（锁定两台机器）

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

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

第九步（演示）

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

MYSQL使用经验(八)-在线主从复制集群的配置与恢复及主主配置

MYSQL使用经验(八)-在线主从复制集群的配置与恢复及主主配置

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