資料庫學習之十二：mysql主從複製

来源:https://www.cnblogs.com/cuiyongchao007/archive/2020/05/09/12861178.html
-Advertisement-
Play Games

十二、mysql主從複製 1、主從複製原理 1.主從複製的前提： 1.1兩台mysql實例（多台物理機，或者多實例） 1.2主庫要開啟二進位日誌 1.3主庫要提供複製相關用戶，replication slave,一個比較特殊的許可權。 1.4從庫需要將和主庫相差的數據，進行追加 一般情況下可以人為備份 ...


十二、mysql主從複製

1、主從複製原理

1.主從複製的前提：

1.1兩台mysql實例（多台物理機，或者多實例）

1.2主庫要開啟二進位日誌

1.3主庫要提供複製相關用戶，replication slave,一個比較特殊的許可權。

grant replication slave on * . * to repl@'10.0.0.%' identified by '123';

1.4從庫需要將和主庫相差的數據，進行追加

一般情況下可以人為備份主庫數據，恢復到從庫上。

1.5應該從恢復之後的時間點，開始自動從主庫獲取二進位日誌開始應用

需要人為告訴從庫，從哪裡開始自動開始複製二進位日誌(file+position)，另外還需要告訴從庫user,password,ip,port

2.複製中的線程及文件

2.1主庫

dump（IO） thread:在複製過程中，主庫發送二進位日誌的線程。

2.2從庫

IO thread:向主庫請求二進位日誌，並且接受二進位日誌的線程。

SQL thread:執行請求過來的二進位的線程

2.3在主庫的文件

binlog文件，主庫的二進位日誌

2.4從庫文件

relaylog：中繼日誌，存儲請求過來的二進位日誌。

master.info:

​ 1.從庫連接主庫的重要參數（user，password，ip，port）

​ 2.上次獲取過的主庫二進位日誌的位置

relay-log.info

​ 存儲從庫SQL線程已經執行過的relaylog日誌位置。

3.主從複製的工作原理

3.1從庫，IO線程，讀取master.info中的信息，獲取到連接參數（user，password，ip，port），和上次用過的主庫的binlog的位置（mysqlbin-0000,position）。

3.2IO線程使用連接到主庫，拿著上次從主庫獲取到的binlog的位置，問主庫有沒有比這個更新的二進位日誌。

3.3主庫查詢二進位日誌，並對比從庫發送過來的位置信息，如果有新的二進位日子，就通過dump thread發送給我從庫。

3.4從庫通過IO線程，接受主庫發來的二進位日誌，存儲到TCP/IP緩存中，並且返回ACK確認給主庫，這時主庫認為複製完成了，可以繼續其他工作了。

3.5從庫更新master.info，二進位日誌的為新的位置信息。

3.6從庫IO線程會將TCP/IP緩存中的日誌，存儲到relay-log中繼日誌文件中。

3.7從庫SQL線程，讀取relay-log.info，獲取到上次執行到的relay-log日誌位置，以這個位置為起點，往後繼續執行中繼日誌。

3.8SQL線程執行完成所有relay之後，會更新relay-log.info信息為新位置信息。

到此位置，一次完成的複製過程完成。

4、搭建主從複製

1、準備環境
思路：
1、兩個以上節點（多實例）
3307：master
3308：slave1
3309：slave2
2、主庫binlog開啟，從庫開啟relay-log（預設在數據目錄下生成）
vim /data/3307/my.cnf
log-bin=/data/3307/mysql-bin
binlog_format=row

3、server-id不同
[root@db02 data]# cat /data/3307/my.cnf |grep server-id
server-id=3307
[root@db02 data]# cat /data/3308/my.cnf |grep server-id
server-id=3308
[root@db02 data]# cat /data/3309/my.cnf |grep server-id
server-id=3309
4、關閉資料庫的自動功能變數名稱解析
每個節點都加入以下配置：
skip-name-resolve
5、啟動多實例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

6、主庫創建複製賬戶
連接到主庫：
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

7、從庫數據的追加
	（1）不需要追加的情況
	 主和從同時搭建的新環境，就不需要備份主庫數據，恢復到從庫了，直接從第一個binlog（mysql-bin.000001）的開頭位置（120）。
	（2）如果主庫已經工作了很長時間了，我們一般需要備份主庫數據，恢復到從庫，然後從庫從備份的時間點起自動進行複製。
重點針對第二種情況進行演示：
備份主庫：
mysqldump -S /data/3307/mysql.sock -A -R  --triggers --master-data=2 --single-transaction >/tmp/full.sql
sed -n '22p' /tmp/full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=325;
恢復到從庫：
mysql -S /data/3308/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

8、從庫開啟主庫：
mysql -S /data/3308/mysql.sock
help change master to

CHANGE MASTER TO
  MASTER_HOST='10.0.0.203',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=325;
開啟主從（開啟IO和SQL線程）：
start slave;
9、查看主從狀態：
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10、主從重要狀態信息介紹
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 


IO線程故障：
    1、主庫連接不上
       user、password、port、ip 錯誤
	   解決方案：
		stop  slave;	
		reset slave all;  
		change master to	
		start slave;
       防火牆
       網路不通
       skip-name-resolve
	   stop  slave;
	   start slave;
	   
    2、主庫二進位日誌丟失或損壞
		解決方案：
		stop  slave;	
		reset slave all;  
		重新備份恢復
		change master to	
		start slave;

5、SQL線程故障

SQL線程故障：
   執行relaylog日誌新事件
    1、刪除、修改對象的操作時，沒有這個對象
    2、創建對象時，對象已存在
    3、主鍵衝突
從庫做寫入操作，會導致以上問題出現
處理方法：
stop slave; 
set global sql_slave_skip_counter = 1; 
start slave;
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
但是，以上操作有時是有風險的，最安全的做法就是重新構建主從。
怎麼預防以上問題？
從庫加入配置文件
set global read_only=1;
vim /etc/my.cnf
read_only=1           ---->只能控制普通用戶

6、主從異常--主從延時過長

show slave status \G
Seconds_Behind_Master:0

預設的主從複製機制是非同步的一個過程。

主庫原因:
1、主庫做修改操作之後，才會記錄二進位日誌。
sync_binlog=0/1

If the value of this variable is greater than 0, 
the MySQL server synchronizes its binary log to disk (using fdatasync()) 
after sync_binlog commit groups are written to the binary log. 
The default value of sync_binlog is 0, which does no synchronizing to disk—in this case,
the server relies on the operating system to flush the binary log's contents from time to time as for any other file. 
A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. 
However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast)
---------------------
    1：表示：每次事務commit，刷新binlog到磁碟
    0：系統決定binlog什時候刷新到磁碟
2、主庫的壓力特別大（大事務、多事務）
3、從庫數量多，導致dump線程繁忙
-------------------
從庫原因：
1、relay-log寫入慢
2、SQL線程慢（主從硬體差異比較大）
-----------------------------
儘可能的避免主從延時
1、sync_binlog=1
2、大事務拆成小事務，多事務進行分離
3、使用多級主從，分庫分表架構
4、將binlog放到ssd或者flash上，高性能存儲
5、將relay放到ssd或者flash上
6、儘量選擇和主庫一致硬體和配置

7、主從複製高級功能--半同步複製

出發點：保證主從數據一致性的問題，安全的考慮

5.5 出現的概念，但是不建議使用，性能太差

5.6以後出現group commit 組提交功能，來提升開啟版同步複製的性能

5.7 增強半同步複製的新特性：after sync;

------
載入插件

主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

從:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否載入成功:
show plugins;

啟動:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;

從:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重啟從庫上的IO線程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在運行
主:
show status like 'Rpl_semi_sync_master_status';
從:
show status like 'Rpl_semi_sync_slave_status';
-----
補充：
rpl_semi_sync_master_timeout       | 10000
預設情況先,到達10秒鐘還沒有ack，主從關係自動切換為普通複製
如果是1主多從的半同步複製，只要有一臺落地relaylog，返回ack，這次半同步就完成了。

8、主從複製高級特性--延時從庫

會專門找一個節點，配置成延時節點，儘可能防止邏輯損壞，一般情況下這個節點會被用備份
	
我們配置的是SQL_thread的延時

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 60;

mysql>start slave;

mysql> show slave status \G
SQL_Delay: 300

取消延時：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;

9、主從複製高級功能--複製過濾

主庫方面控制（不建議使用）：
    白名單:只記錄白名單中列出的庫的二進位日誌
     binlog-do-db
    黑名單：不記錄黑名單列出的庫的二進位日誌
     binlog-ignore-db
	  
從庫方面控制：
show slave status\G;查看相關參數。
白名單：只執行白名單中列出的庫或者表的中繼日誌   

--replicate-do-db=test
--replicate-do-table=test.t1
--replicate-wild-do-table=test.x*
   
黑名單：不執行黑名單中列出的庫或者表的中繼日誌
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table
只複製world資料庫的數據

10、主從複製新特性--GTID複製

GTID
5.6新特性
GTID(Global Transaction ID)是對於一個已提交事務的編號，並且是一個全局唯一的編號。
它的官方定義如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
每一臺mysql實例中，都會有一個唯一的uuid，標識實例的唯一性
auto.cnf，存放在數據目錄下

重要參數：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1


gtid-mode=on			            --啟用gtid類型，否則就是普通的複製架構
enforce-gtid-consistency=true		 --強制GTID的一致性
log-slave-updates=1					--slave更新是否記入日誌

-----------------
構建1主2從的GTID複製環境：

3台虛擬機，
db02 克隆兩台虛擬機環境，分別命名為db01、db03，在生產中準備3台真實的物理機，不用多實例

要求：

1、IP地址、主機名
	 db01:10.0.0.51/24   
	 db03:10.0.0.53/24
2、清理所有之前3306的相關數據，只留軟體

db01:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

db02:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

db03:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

3、準備配置文件
規劃：
	主庫： 10.0.0.51/24
	從庫1: 10.0.0.52/24
	從庫2：10.0.0.53/24
主庫：
加入以下配置信息
db01:10.0.0.51/24
vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave1：
db02:10.0.0.52/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave2：
db02:10.0.0.53/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

-----------------
三台節點分別初始化數據：

/application/mysql/scripts/mysql_install_db --user=mysql  --basedir=/application/mysql --datadir=/application/mysql/data/ 

分別啟動三個節點mysql：
/etc/init.d/mysqld start

測試啟動情況：
mysql -e "show variables like 'server_id'"

master:51
slave:52,53

51:
grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';
52\53:
change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;
start slave;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux下3種常用的網路測速工具
    大家好，我是良許。 不管你用的是什麼操作系統，網速都是你非常關心的一個性能指標，畢竟，誰都不想看個視頻結果網速卡到你懷疑人生。本文介紹三個 Linux 命令行下的網路測速工具，讓你隨時隨地知道你的網路狀況。 fast 是 Netflix 提供的一項服務，它不僅可以通過命令行來使用，而且可以直接在 W ...
  • Linux全面指令
    Linux常用命令大全（非常全！！！） 最近都在和Linux打交道，感覺還不錯。我覺得Linux相比windows比較麻煩的就是很多東西都要用命令來控制，當然，這也是很多人喜歡linux的原因，比較短小但卻功能強大。我將我瞭解到的命令列舉一下，僅供大家參考： 系統信息 arch 顯示機器的處理器架構 ...
  • 進程概念介紹
    linux進程,這塊太難了,太多命令,太多新概念.作為初學者戰戰兢兢.同時也在匍匐前進. ...
  • ARM32 內核記憶體佈局
    Linux內核在啟動時會列印出內核記憶體空間的佈局圖，下麵是ARM Vexpress平臺列印出來的記憶體空間佈局圖： 這部分信息列印是在mem_init()函數中實現的。 編譯器在編譯目標文件並且鏈接完成之後，就可以知道內核映像文件最終的大小，接下來打包成二進位文件，該操作由 控制，其中也劃定了內核的內 ...
  • sed 命令備忘
    常用選項: -i #直接修改目標文件內容. -n #取消預設輸出. 實例: #刪除 sed -i '/content/d' temp.txt #刪除中temp.txt匹配content內容的行. sed -i '/^$/d' temp.txt #刪除文件中所有空行. sed -i 'nd' temp ...
  • EF如何分頁最大優化
    public static class QueryableExtension { public static IQueryable<T> Paged<T>(this IQueryable<T> query, Expression<Func<T, object>> expression, OrderB ...
  • 資料庫學習之十四：讀寫分離 Atlas架構
    十四、讀寫分離 Atlas架構 1、安裝軟體 2、修改配置 3、啟動atlas 4、測試 5、管理atlas 6、擴展——atlas 自動分表 ...
  • 資料庫學習之十三：mysql高可用配置
    十三、mysql高可用 1、普通主從複製架構存在的不足 高可用？ 業務不間斷的工作。 用戶的體驗不出來業務斷點。 普通主從環境，存在的問題： 2、企業高可用解決方案： MMM（過時） MHA（目前推薦） PXC、Galera Cluster（出現很多年，企業很少用） 5.7.17 MGR 、Inno ...
一周排行
    -Advertisement-
    Play Games
  • 如何基於surging跨網關跨語言進行緩存降級
    基於.NET Framework 4.8 開發的深度學習模型部署測試平臺，提供了YOLO框架的主流系列模型，包括YOLOv8~v9，以及其系列下的Det、Seg、Pose、Obb、Cls等應用場景，同時支持圖像與視頻檢測。模型部署引擎使用的是OpenVINO™、TensorRT、ONNX runti... ...
  • 十年沉澱，重啟開發之路
    十年沉澱，重啟開發之路 十年前，我沉浸在開發的海洋中，每日與代碼為伍，與演算法共舞。那時的我，滿懷激情，對技術的追求近乎狂熱。然而，隨著歲月的流逝，生活的忙碌逐漸占據了我的大部分時間，讓我無暇顧及技術的沉澱與積累。 十年間，我經歷了職業生涯的起伏和變遷。從初出茅廬的菜鳥到逐漸嶄露頭角的開發者，我見證了 ...
  • C#.NET體系圖文概述—2024最全總結
    C# 是一種簡單、現代、面向對象和類型安全的編程語言。.NET 是由 Microsoft 創建的開發平臺，平臺包含了語言規範、工具、運行，支持開發各種應用，如Web、移動、桌面等。.NET框架有多個實現，如.NET Framework、.NET Core（及後續的.NET 5+版本），以及社區版本M... ...
  • 班級擂臺（光榮）榜 - 極簡教育小工具
    前言 本文介紹瞭如何使用三菱提供的MX Component插件實現對三菱PLC軟元件數據的讀寫，記錄了使用電腦模擬，模擬PLC，直至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1. PLC開發編程環境GX Works2，GX Works2下載鏈接 https:// ...
  • web server apache tomcat11-31-websocket
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • day31-jQuery
    1、jQuery介紹 jQuery是什麼 jQuery是一個快速、簡潔的JavaScript框架，是繼Prototype之後又一個優秀的JavaScript代碼庫（或JavaScript框架）。jQuery設計的宗旨是“write Less，Do More”，即倡導寫更少的代碼，做更多的事情。它封裝 ...
  • aardio爬蟲) 實戰篇：逆向有道翻譯web介面
    前言 之前的文章把js引擎(aardio封裝庫) 微軟開源的js引擎(ChakraCore))寫好了，這篇文章整點js代碼來測一下bug。測試網站：https://fanyi.youdao.com/index.html#/ 逆向思路 逆向思路可以看有道翻譯js逆向（MD5加密，AES加密）附完整源碼 ...
  • 面試官：這就是你理解的Java多線程基礎？
    引言 現代的操作系統（Windows，Linux，Mac OS）等都可以同時打開多個軟體（任務），這些軟體在我們的感知上是同時運行的，例如我們可以一邊瀏覽網頁，一邊聽音樂。而CPU執行代碼同一時間只能執行一條，但即使我們的電腦是單核CPU也可以同時運行多個任務，如下圖所示，這是因為我們的 CPU 的 ...
  • Python文本統計與分析從基礎到進階
    掌握使用Python進行文本英文統計的基本方法，並瞭解如何進一步優化和擴展這些方法，以應對更複雜的文本分析任務。 ...
  • Redis多數據源，看這篇就夠了
    背景 Redis多數據源常見的場景： 分區數據處理：當數據量增長時，單個Redis實例可能無法處理所有的數據。通過使用多個Redis數據源，可以將數據分區存儲在不同的實例中，使得數據處理更加高效。 多租戶應用程式：對於多租戶應用程式，每個租戶可以擁有自己的Redis數據源，以確保數據隔離和安全性。 ...
所有分類