一 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端开启半同步功能
模拟故障: