Mysql 集群技术

一 Mysql 在服务器中的部署方法

1.1 在Linux下部署mysq

二 mysql的组从复制

2.1 配置mastesr

2.2 配置salve

测试：

2.3 当有数据时添加slave2

测试

2.4 延迟复制

延迟复制时用来控制sql线程的，和i/o线程无关

这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的

是日志已经保存在slave端了，那个sql要等多久进行回放

#在slave端
mysql> STOP SLAVE SQL_THREAD;
 mysql> CHANGE MASTER TO MASTER_DELAY=60;
 mysql> START SLAVE SQL_THREAD;
 mysql> SHOW SLAVE STATUS\G;
 Master_Server_Id: 1
 Master_UUID: db2d8c92-4dc2-11ef-b6b0-000c299355ea
 Master_Info_File: /data/mysql/master.info
 SQL_Delay: 60           
##延迟效果
SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for more 
updates
 Master_Retry_Count: 86400

2.5 慢查询日志

慢查询，顾名思义，执行很慢的查询

当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个 SQL语句就是需要优化的

慢查询被记录在慢查询日志里

慢查询日志默认是不开启的

如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                           |
+---------------------+----------------------------------+
| slow_launch_time   | 2                               |
| slow_query_log     | OFF                             |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)

开启慢查询日志

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SET long_query_time=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES like "long%";
+-----------------+----------+
| Variable_name   | Value   |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                           |
+---------------------+----------------------------------+
| slow_launch_time   | 2                               |
| slow_query_log     | ON                               | ##慢查询日志开启
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log     #慢查询日志
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started 
with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time                 Id Command   Argument

测试慢查询

mysql> select sleep (10);
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started 
with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time                 Id Command   Argument
# Time: 2024-07-29T17:04:07.612704Z
# User@Host: root[root] @ localhost [] Id:     8
# Query_time: 10.000773 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1722272647;
select sleep (10);

2.6 mysql的并行复制

查看slave中的线程信息

默认情况下slave中使用的是sql单线程回放

在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重

开启MySQL的多线程回放可以解决上述问题

在slaves中设定
[root@mysql-node2 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=2
 gtid_mode=ON
 enforce-gtid-consistency=ON
 slave-parallel-type=LOGICAL_CLOCK           
slave-parallel-workers=16                   
master_info_repository=TABLE                
在/data/mysql//master.info
 relay_log_info_repository=TABLE             
在/data/mysql/relay-log.info
 relay_log_recovery=ON                       
#基于组提交，
#开启线程数量
#master信息在表中记录，默认记录
#回放日志信息在表中记录，默认记录
#日志回放恢复功能开启
[root@mysql-node2 ~]# /etc/init.d/mysql start
 Starting MySQL. SUCCESS

2.7 原理刨析

三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作， 一个主库线程，两个从库线程。

        二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以 将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之 后，再将锁释放掉。

        从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库 的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。         从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同 步。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。

步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化 的，而且重启后从接入点开始复制。

具体操作

1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及 信息

2.master端在设定好binlog启动后会开启binlog dump的线程

3.master端的binlog dump把二进制的更新发送到slave端的

4.slave端开启两个线程，一个是I/O线程，一个是sql线程，

i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地 磁盘

sql线程读取本地relog中继日志进行回放

5.什么时候我们需要多个slave？

当读取的而操作远远高与写操作时。我们采用一主多从架构

数据库外层接入负载均衡层并搭配高可用机制

2.8 架构缺陷

主从架构采用的是异步机制

master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测

master端直接保存二进制日志到磁盘

当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave

master出现问题slave端接管master，这个过程中数据就丢失了

这样的问题出现就无法达到数据的强一致性，零数据丢失

三 半同步模式

3.1半同步模式原理

1.用户线程写入完成后master中的dump会把日志推送到slave端

2.slave中的io线程接收后保存到relaylog中继日志

3.保存完成后slave向master端返回ack

4.在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎

5.在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok

3.2 gtid模式

当为启用gtid时我们要考虑的问题

在master端的写入时多用户读写，在slave端的复制时单线程日志回放，所以slave端一定会延迟与 master端

这种延迟在slave端的延迟可能会不一致，当master挂掉后slave接管，一般会挑选一个和master延迟日 志最接近的充当新的master

那么为接管master的主机继续充当slave角色并会指向到新的master上，作为其slave

这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之 间差多少

 vim /etc/my.cnf

 mysql> show slave status\G;
 *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
 Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 580
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 768c6b91-4c01-11ef-a514-000c299355ea
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more 
updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 1        #功能开启
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
 1 row in set (0.00 sec)

3.3.启用半同步模式

在slave端开启半同步功能

模拟故障：

四 mysql高可用之组复制 (MGR)

五 mysql-router（mysql路由）

六 mysql高可用之MHA

6.2 MHA部署实施

6.2.1 搭建主两从架构

6.2.2安装MHA所需要的软件

6.2.3 MHA的故障切换