資料庫學習之十二：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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類