2.MySQL的組從復制
2.1 配置mastesr
[root@mysqlaa ~]# vim /etc/my.cnf
[mysqld]
server-id=10
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
log-bin=mysql-bin[root@mysqlaa ~]# /etc/init.d/mysqld restart
# 進入數據庫配置用戶權限
[root@mysql-node10 ~]# mysql -uroot -p123456# 生成專門用來做復制的用戶,此用戶是用于slave端做認證用
mysql> create user dhj@'%' identified by '123456';
mysql> grant replication slave on *.* to 'dhj'@'%'; # 對這個用戶進行授權
mysql> show master status; # 查看master的狀態
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 658 | | | |
+------------------+----------+--------------+------------------+-------------------+

[root@mysql-node10 ~]# cd /data/mysql/
[root@mysql-node10 mysql]# mysqlbinlog mysql-bin.000001 -vv # 查看二進制日志
2.2 配置salve
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password[root@mysql-node2 ~]# /etc/init.d/mysqld restart
[root@mysqlb ~]# mysql -uroot -p123456mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_USER='dhj',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=658; # 這里要去master主機中去查看一遍mysql> start slave;mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.25.254.10Master_User: dhjMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1606Relay_Log_File: mysqlb-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes # 一定要保證此參數為yesSlave_SQL_Running: Yes # 一定要保證此參數為yes

# 如果上述內容輸入錯誤可以reset重新填入信息即可
mysql> RESET SLAVE ALL;
# 測試:# 在master主機里面進行建表
[root@mysqlaa ~]# mysql -uroot -p123456mysql> create database ceshi;mysql> create table ceshi.userlist (username varchar(20) not null, password varchar(50) not null);mysql> insert into ceshi.userlist value ('dhj','123');mysql> select * from ceshi.userlist;
+----------+----------+
| username | password |
+----------+----------+
| dhj | 123 |
+----------+----------+# 在slave中查看數據是否有同步過來
[root@mysqlb ~]# mysql -uroot -p123456mysql> select * from ceshi.userlist;
+----------+----------+
| username | password |
+----------+----------+
| dhj | 123 |
+----------+----------+

2.3 當有數據時添加slave2
#完成基礎配置
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
server-id=30
datadir=/data/mysql # 指定數據目錄
socket=/data/mysql/mysql.sock # 指定套接字
default_authentication_plugin=mysql_native_password[root@mysql-node3 ~]# /etc/init.d/mysqld restart
#從master節點備份數據
[root@mysql-node1 ~]# mysqldump -uroot -p123456 ceshi > /mnt/ceshi.sql
[!NOTE]
生產環境中備份時需要鎖表,保證備份前后的數據一致
mysql> FLUSH TABLES WITH READ LOCK;
備份后再解鎖
mysql> UNLOCK TABLES;
mysqldump命令備份的數據文件,在還原時先DROP TABLE,需要合并數據時需要刪除此語句
-- -- Table structure for table `userlist` --DROP TABLE IF EXISTS `userlist`; #需要合并數據時需要刪除此語句 /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */;
[root@mysqlaa ~]# scp /mnt/ceshi.sql root@172.25.254.30:/mnt
root@172.25.254.30's password:
ceshi.sql 100% 1947 2.2MB/s 00:00# 利用master節點中備份出來的lee.sql在slave2中拉平數據
[root@mysql-node3 ~]# mysql -uroot -p123456 -e "create database ceshi;"
[root@mysql-node3 ~]# mysql -uroot -p123456 ceshi </mnt/ceshi.sql
[root@mysql-node3 ~]# mysql -uroot -p123456 -e "select * from ceshi.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
#配置slave2的slave功能#在master中查詢日志pos
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 3656 | | | |
+------------------+----------+--------------+------------------+-------------------+[root@mysqlc ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='dhj', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3656;mysql> start slave;
mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.25.254.10Master_User: dhjMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 3656Relay_Log_File: mysqlc-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
測試:
[root@mysql-node1 ~]# mysql -uroot -p123456 -e "INSERT INTO ceshi.userlist VALUES('user2','123');"[root@mysql-node2 mysql]# mysql -uroot -p123456 -e 'select * from ceshi.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+[root@mysql-node3 ~]# mysql -uroot -p123456 -e 'select * from ceshi.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
# 為slave兩臺主機開啟只讀操作+超級只讀操作(root在slave里面都不能寫數據)
# 以下僅為20主機的,30的在此不做演示
[root@mysqlb ~]# more /etc/my.cnf
[mysqld]
server-id=10
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
read-only=1
super-read-only=1[root@mysqlb ~]# /etc/init.d/mysqld restart
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: 1Master_UUID: db2d8c92-4dc2-11ef-b6b0-000c299355eaMaster_Info_File: /data/mysql/master.infoSQL_Delay: 60 ##延遲效果SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400
測試:
在master中寫入數據后過了延遲時間才能被查詢到
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中設定
# 以下僅為20,30不做演示[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
read-only=1
super-read-only=1slave-parallel-type=LOGICAL_CLOCK #基于組提交,
slave-parallel-workers=16 #開啟線程數量
master_info_repository=TABLE #master信息在表中記錄,默認記錄在/data/mysql//master.info
relay_log_info_repository=TABLE #回放日志信息在表中記錄,默認記錄在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢復功能開啟[root@mysql-node2 ~]# /etc/init.d/mysql restart# 進行測試
mysql> show processlist;
此時sql線程轉化為協調線程,16個worker負責處理sql協調線程發送過來的處理請求
[!NOTE]
MySQL 組提交(Group commit)是一個性能優化特性,它允許在一個事務日志同步操作中將多個事務的日志記錄一起寫入。這樣做可以減少磁盤I/O的次數,從而提高數據庫的整體性能。
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,這個過程中數據就丟失了
這樣的問題出現就無法達到數據的強一致性,零數據丟失