🦑MYSQL进阶-01-MYSQL高可用架构
- 🐾MYSQL搭建主从集群

🦑MYSQL进阶-01-MYSQL高可用架构

其实在并发和大数据的背景下，数据库才是真正的木桶短板，我们做了多级缓存等等操作，都是为了提升数据的性能，当然生产很多时候切换了很多数据库类型，当然MYSQL也是中小公司的核心东西

1️⃣2️⃣3️⃣4️⃣5️⃣6️⃣7️⃣8️⃣9️⃣

🐾MYSQL搭建主从集群

1️⃣环境介绍和理论知识

IP	作用	MYSQL版本
192.168.230.134	Master	8.0.20
192.168.230.135	Slave	8.0.20

为了便于使用，两个mysql服务需要打开远程登录权限，开启方式需要在本机登录mysql，执行以下语句。

use mysql;
update user set host='%' where user='root';
flush privileges;

主从架构有什么用？

通过搭建MySQL主从集群，可以缓解MySQL的数据存储以及访问的压力。

数据安全
给主服务增加一个数据备份。基于这个目的，可以搭建主从架构，或者也可以基于主从架构搭建互主的架构。

读写分离

对于大部分的系统业务系统来说，都是读多写少的，读请求远远高于写请求。这时，当主服务的访问压力过大时，可以将数据读请求转为由从服务来分担，主服务只负责数据写入的请求，这样大大缓解数据库的访问压力。

故障转移-高可用
当MySQL主服务宕机后，可以由一台从服务切换成为主服务，继续提供数据读写功能。

对于高可用架构，主从数据的同步也只是实现故障转移的一个前提条件，要实现MySQL主从切换，还需要依靠一些其他的中间件来实现。如MMM、MHA、MGR。在一般项目中，如果数据库的访问压力没有那么大，那读写分离不一定是必须要做的，但是，主从架构和高可用架构则是必须要搭建的。

2️⃣MySQL的同步原理

MySQL服务的主从架构一般都是通过binlog日志文件来进行的。即在主服务上打开binlog记录每一步的数据库操作，然后从服务上会有一个IO线程，负责跟主服务建立一个TCP连接，请求主服务将binlog传输过来。这时，主库上会有一个IO dump线程，负责通过这个TCP连接把Binlog日志传输给从库的IO线程。接着从服务的IO线程会把读取到的binlog日志数据写入自己的relay日志文件中。然后从服务上另外一个SQL线程会读取relay日志里的内容，进行操作重演，达到还原数据的目的。我们通常对MySQL做的读写分离配置就必须基于主从架构来搭建。

MYSQL进阶-01-MYSQL高可用架构 - 图1

MySQL的binlog不光可以用于主从同步，还可以用于缓存数据同步等场景。

例如Canal，可以模拟一个slave节点，向MySQL发起binlog同步，然后将数据落地到Redis、Kafka等其他组件，实现数据实时流转。

主从搭建的2个必要条件

双方MySQL必须版本一致。至少需要主服务的版本低于从服务
两节点间的时间需要同步。

3️⃣MYSQL主从集群的搭建和测试

1.MYSQL主从集群的搭建

首先，配置主节点的mysql配置文件： /etc/my.cnf 这一步需要对master进行配置，主要是需要打开binlog日志，以及指定severId。我们打开MySQL主服务的my.cnf文件，在文件中一行server-id以及一个关闭域名解析的配置。然后重启服务。

[mysqld]
server-id=100
#开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql/mysql8
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysqldb
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

配置说明：主要需要修改的是以下几个属性：

server-id：服务节点的唯一标识。需要给集群中的每个服务分配一个单独的ID。

log_bin：打开Binlog日志记录，并指定文件名。

log_bin-index：Binlog日志文件

重启MySQL服务， service mysqld restart，然后，我们需要给root用户分配一个replication slave的权限。

#登录主数据库
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
#查看主节点同步状态：
show master status;

在实际生产环境中，通常不会直接使用root用户，而会创建一个拥有全部权限的用户来负责主从同步。

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      156 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

这个指令结果中的File和Position记录的是当前日志的binlog文件以及文件中的索引。

而后面的Binlog_Do_DB和Binlog_Ignore_DB这两个字段是表示需要记录binlog文件的库以及不需要记录binlog文件的库。目前我们没有进行配置，就表示是针对全库记录日志。这两个字段如何进行配置，会在后面进行介绍。

开启binlog后，数据库中的所有操作都会被记录到datadir当中，以一组轮询文件的方式循环记录。而指令查到的File和Position就是当前日志的文件和位置。而在后面配置从服务时，就需要通过这个File和Position通知从服务从哪个地方开始记录binLog。

2.配置slave从服务

下一步，我们来配置从服务mysqls。我们打开mysqls的配置文件my.cnf，修改配置文件：

[mysqld]
# 设置3306端口
port=3306
#主库和从库需要不一致
server-id=101
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
# 设置mysql的安装目录
basedir=/usr/local/mysql/mysql8
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysqldb
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

配置说明：主要需要关注的几个属性：

server-id：服务节点的唯一标识

relay-log：打开从服务的relay-log日志。

log-bin：打开从服务的bin-log日志记录。

然后我们启动mysql的服务，并设置他的主节点同步状态。#登录从服务mysql -u root -p;

CHANGE MASTER TO
MASTER_HOST='192.168.230.134',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='master-bin.000005',
MASTER_LOG_POS=156,
GET_MASTER_PUBLIC_KEY=1;

#开启slave
start slave;
#查看主从同步状态
show slave status;
或者用 show slave status \G; 这样查看比较简洁

注意，CHANGE MASTER指令中需要指定的MASTER_LOG_FILE和MASTER_LOG_POS必须与主服务中查到的保持一致。

并且后续如果要检查主从架构是否成功，也可以通过检查主服务与从服务之间的File和Position这两个属性是否一致来确定。

MYSQL进阶-01-MYSQL高可用架构 - 图3

我们重点关注其中Slave_IO_Running和Slave_SQL_Running的两个属性，与主节点保持一致，就表示这个主从同步搭建是成功的。

从这个指令的结果能够看到，有很多Replicate_开头的属性，这些属性指定了两个服务之间要同步哪些数据库、哪些表的配置。只是在我们这个示例中全都没有进行配置，就标识是全库进行同步。后面我们会补充如何配置需要同步的库和表。

👉 如果Slave_IO_Running和Slave_SQL_Running非YES 可以看考博客

3.主从集群测试

然后我们在主服务器上创建一个数据库

mysql> create database syncdemo;
Query OK, 1 row affected (0.01 sec)

然后我们再用show databases，来看下这个syncdemo的数据库是不是已经同步到了从服务。

MYSQL进阶-01-MYSQL高可用架构 - 图4

接下来我们继续在syncdemo这个数据库中创建一个表，并插入一条数据。

mysql> use syncdemo;
Database changed
mysql> create table demoTable(id int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into demoTable value(1);
Query OK, 1 row affected (0.01 sec)

MYSQL进阶-01-MYSQL高可用架构 - 图5

从上面的实验过程看到，我们在主服务中进行的数据操作，就都已经同步到了从服务上。这样，我们一个主从集群就搭建完成了。

👉 主从复制的问题，从库写入的数据不会同步到主库上面，是单方面的进行同步，所以一般不会使用从库写入数据

4️⃣MYSQL主从同步扩展

1、全库同步与部分同步

之前提到，我们目前配置的主从同步是针对全库配置的，而实际环境中，一般并不需要针对全库做备份，而只需要对一些特别重要的库或者表来进行同步。那如何针对库和表做同步配置呢？

首先在Master端：在my.cnf中，可以通过以下这些属性指定需要针对哪些库或者哪些表记录binlog

#需要同步的二进制数据库名
binlog-do-db=masterdemo
#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days  = 7
#不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys

然后在Slave端：在my.cnf中，需要配置备份库与主服务的库的对应关系。

#如果salve库名称与master库名相同，使用本配置
replicate-do-db = masterdemo 
#如果master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]
replicate-rewrite-db = masterdemo -> masterdemo01
#如果不是要全部同步[默认全部同步]，则指定需要同步的表
replicate-wild-do-table=masterdemo01.t_dict
replicate-wild-do-table=masterdemo01.t_num

配置完成了之后，在show master status指令中，就可以看到Binlog_Do_DB和Binlog_Ignore_DB两个参数的作用了。

mysql> show master status;
+-------------------+----------+--------------+--------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                           | Executed_Gtid_Set |
+-------------------+----------+--------------+--------------------------------------------+-------------------+
| master-bin.000006 |      156 | masterdemo   | information_schema,performation_schema,sys |                   |
+-------------------+----------+--------------+--------------------------------------------+-------------------+
1 row in set (0.00 sec)

2、读写分离

我们要注意，目前我们的这个MySQL主从集群是单向的，也就是只能从主服务同步到从服务，而从服务的数据表更是无法同步到主服务的。

MYSQL进阶-01-MYSQL高可用架构 - 图6

所以，在这种架构下，为了保证数据一致，通常会需要保证数据只在主服务上写，而从服务只进行数据读取。这个功能，就是大名鼎鼎的读写分离。但是这里要注意下，mysql主从本身是无法提供读写分离的服务的，需要由业务自己来实现。这也是我们后面要学的ShardingSphere的一个重要功能。

到这里可以看到，在MySQL主从架构中，是需要严格限制从服务的数据写入的，一旦从服务有数据写入，就会造成数据不一致。并且从服务在执行事务期间还很容易造成数据同步失败。

如果需要限制用户写数据，我们可以在从服务中将read_only参数的值设为1( set global read_only=1; )。这样就可以限制用户写入数据。但是这个属性有两个需要注意的地方：

1、read_only=1设置的只读模式，不会影响slave同步复制的功能。所以在MySQL slave库中设定了read_only=1后，通过 "show slave status\G" 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；

2、read_only=1设置的只读模式， 限定的是普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作。在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；如果需要限定super权限的用户写数据，可以设置super_read_only=0。另外 如果要想连super权限用户的写操作也禁止，就使用”flush tables with read lock;”，这样设置也会阻止主从同步复制！

3、其他集群方式

    我们到这里搭建出了一个一主一从的MySQL主从同步集群，具有了数据同步的基础功能。而在生产环境中，通常会以此为基础，根据业务情况以及负载情况，搭建更大更复杂的集群。

    例如为了进一步提高整个集群的读能力，可以扩展出一主多从。而为了减轻主节点进行数据同步的压力，可以继续扩展出多级从的主从集群。

     为了提高整个集群的高可用能力，可以扩展出多主的集群。 我们也可以扩展出**互为主从**的互主集群甚至是环形的主从集群，实现MySQL多活部署。 搭建互主集群只需要按照上面的方式，在主服务上打开一个slave进程，并且指向slave节点的`binlog`当前文件地址和位置。

MYSQL进阶-01-MYSQL高可用架构 - 图7

我们这里是使用的最为传统的Binlog方式搭建集群，是基于日志记录点的方式来进行主从同步的。在这个实验中，Executed_Grid_Set一列，实际上就是另外一种搭建主从同步的方式，即GTID搭建方式。GTID的本质也是基于Binlog来实现的主从同步，只是他会基于一个全局的事务ID来标识同步进度。这个GTID全局事务ID是一个全局唯一、并且趋势递增的分布式ID策略。我们这里就不再去搭建了。

4、GTID同步集群

👉 CSDN GTID搭建博客

上面我们搭建的集群方式，是基于Binlog日志记录点的方式来搭建的，这也是最为传统的MySQL集群搭建方式。而在这个实验中，可以看到有一个Executed_Grid_Set列，暂时还没有用上。实际上，这就是另外一种搭建主从同步的方式，即GTID搭建方式。这种模式是从MySQL5.6版本引入的。

MYSQL进阶-01-MYSQL高可用架构 - 图8

GTID的本质也是基于Binlog来实现主从同步，只是他会基于一个全局的事务ID来标识同步进度。GTID即全局事务ID，全局唯一并且趋势递增，他可以保证为每一个在主节点上提交的事务在复制集群中可以生成一个唯一的ID 。在基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。

他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要在my.cnf中修改一些配置。

在主节点上：

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server_id=单独设置一个
binlog_format=row

在从节点上：

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
server_id=单独设置一个

然后分别重启主服务和从服务，就可以开启GTID同步复制方式。

5、集群扩容

集群扩容业务要停下来，在大半夜，肯定是要锁表的，所以一般公司有经验的的架构师，都会提前规划好，或者DBA在搞年底PKI 什么容灾演练的时候会用到。

我们现在已经搭建成功了一主一从的MySQL集群架构，那要扩展到一主多从的集群架构，其实就比较简单了，只需要增加一个binlog复制就行了。

但是如果我们的集群是已经运行过一段时间，这时候如果要扩展新的从节点就有一个问题，之前的数据没办法从binlog来恢复了。这时候在扩展新的slave节点时，就需要增加一个数据复制的操作。

MySQL的数据备份恢复操作相对比较简单，可以通过SQL语句直接来完成。具体操作可以使用mysql的bin目录下的mysqldump工具。

[root@n1 bin]# ./mysqldump -uroot -p --all-databases > ralphbackup.sql
#输入密码

MYSQL进阶-01-MYSQL高可用架构 - 图9

通过这个指令，就可以将整个数据库的所有数据导出成backup.sql，然后把这个backup.sql分发到新的MySQL服务器上，并执行下面的指令将数据全部导入到新的MySQL服务中。

mysql -u root -p < backup.sql
#输入密码

这样新的MySQL服务就已经有了所有的历史数据，然后就可以再按照上面的步骤，配置Slave从服务的数据同步了。

5️⃣半同步复制

1.理解半同步复制

     到现在为止，我们已经可以搭建MySQL的主从集群，互主集群，但是我们这个集群有一个隐患，就是有可能会丢数据。这是为什么呢？这要从MySQL主从数据复制分析起。

    MySQL主从集群默认采用的是一种**异步复制**的机制。主服务在执行用户提交的事务后，写入`binlog`日志，然后就给客户端返回一个成功的响应了。而`binlog`会由一个dump线程异步发送给Slave从服务。

MYSQL进阶-01-MYSQL高可用架构 - 图10

    由于这个发送`binlog`的过程是**异步**的。主服务在向客户端反馈执行结果时，是不知道`binlog`是否同步成功了的。这时候如果主服务宕机了，而从服务还没有备份到新执行的`binlog`，那就有可能会丢数据。

    那怎么解决这个问题呢，异步肯定是有一个时间差的存着的，这就要靠MySQL的**半同步复制机制**来保证数据安全。

     半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。

MYSQL进阶-01-MYSQL高可用架构 - 图11

    这种半同步复制相比异步复制，能够有效的提高数据的安全性。但是这种安全性也不是绝对的，他只保证事务提交后的binlog**至少传输到了一个从库**，并且**并不保证从库应用这个事务的binlog是成功的**。另一方面，半同步复制机制也会造成一定程度的延迟，这个延迟时间最少是一个TCP/IP请求往返的时间。**整个服务的性能是会有所下降的**。而当从服务出现问题时，主服务需要等待的时间就会更长，要等到从服务的服务恢复或者请求超时才能给用户响应。

2.搭建半同步复制集群

我看到了绿厂生产环境搭建了

     半同步复制需要基于特定的扩展模块来实现。而mysql从5.5版本开始，往上的版本都默认自带了这个模块。这个模块包含在`mysql`安装目录下的`lib/plugin`目录下的`semisync_master.so`和semisync_slave.so两个文件中。需要在主服务上安装`semisync_master`模块，在从服务上安装`semisync_slave`模块。

-rwxrwxrwx 1 mysql mysql  1646120 Mar 26  2020 semisync_master.so
-rwxrwxrwx 1 mysql mysql   750408 Mar 26  2020 semisync_slave.so

首先我们登陆主服务，安装semisync_master模块：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)

mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

这三行指令中：

第一行是通过扩展库来安装半同步复制模块，需要指定扩展库的文件名。

第二行查看系统全局参数，rpl_semi_sync_master_timeout就是半同步复制时等待应答的最长等待时间，默认是10秒，可以根据情况自行调整。
    在第二行查看系统参数时，最后的一个参数`rpl_semi_sync_master_wait_point`其实表示一种半同步复制的方式。
第三行则是打开半同步复制的开关。

半同步复制有两种方式，一种是我们现在看到的这种默认的AFTER_SYNC方式。这种方式下，主库把日志写入binlog，并且复制给从库，然后开始等待从库的响应。从库返回成功后，主库再提交事务，接着给客户端返回一个成功响应。

而另一种方式是叫做AFTER_COMMIT方式。他不是默认的。这种方式，在主库写入binlog后，等待binlog复制到从库，主库就提交自己的本地事务，再等待从库返回给自己一个成功响应，然后主库再给客户端返回响应。

然后我们登陆从服务，安装smeisync_slave模块

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set, 1 warning (0.01 sec)

mysql> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

slave端的安装过程基本差不多，不过要注意下安装完slave端的半同步插件后，需要重启下slave服务。

6️⃣主从架构的数据延迟问题

也可以加中间件处理.

在我们搭建的这个主从集群中，有一个比较隐藏的问题，就是这样的主从复制之间会有延迟。这在做了读写分离后，会更容易体现出来。即数据往主服务写，而读数据在从服务读。这时候这个主从复制延迟就有可能造成刚插入了数据但是查不到。当然，这在我们目前的这个集群中是很难出现的，但是在大型集群中会很容易出现。

出现这个问题的根本在于：面向业务的主服务数据都是多线程并发写入的，而从服务是单个线程慢慢拉取binlog，这中间就会有个效率差。所以解决这个问题的关键是要让从服务也用多线程并行复制binlog数据。

MySQL自5.7版本后就已经支持并行复制了。可以在从服务上设置slave_parallel_workers为一个大于0的数，然后把slave_parallel_type参数设置为LOGICAL_CLOCK，这就可以了。

mysql> show global variables like 'slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+
2 rows in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slave_parallel_type = "LOGICAL_CLOCK";
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'slave_parallel%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 0             |
+------------------------+---------------+
2 rows in set (0.00 sec)