mysql 架構篇系列 3 複製運行狀態監控與選項參數說明

来源:https://www.cnblogs.com/MrHSR/archive/2018/10/29/9871519.html
-Advertisement-
Play Games

一. 概述 在上一篇中，搭建了一主一從的複製架構，這篇通過一些診斷方法來瞭解複製的運行狀態和一些選項參數說明。上次mysql主從服務關機,今天在打開mysql服務，出現了錯誤信息。 1.首先 啟動主從mysql服務 2.在從庫上執行START SLAVE， 開始複製。 3.在從庫上執行SHOW PR ...


一. 概述

　　在上一篇中，搭建了一主一從的複製架構，這篇通過一些診斷方法來瞭解複製的運行狀態和一些選項參數說明。上次mysql主從服務關機,今天在打開mysql服務，出現了錯誤信息。

　　1.首先 啟動主從mysql服務

　　2.在從庫上執行START SLAVE， 開始複製。

　　3.在從庫上執行SHOW PROCESSLIST;  slave已經連接上master, 並開始接受並執行日誌。


　　4.在從庫上執行SHOW  SLAVE STATUS，查看複製狀態

　　　　錯誤信息: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

　　5. 解決方法　

-- 在主庫上執行,記錄日誌文件號和位置，如下圖所示：
flush logs;
show master status;

  -- 在從庫上執行
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000072',MASTER_LOG_POS=154;

SLAVE START;

 　　　　從庫上再執行SHOW PROCESSLIST，顯示了二個複製進程：一個是 I/O線程，連接master,id為7。 一個是SQL線程,id為8。如下圖所示：

 

二 .複製中的各類文件

　　myql複製中涉及了兩類非常重要的日誌文件：二進位日誌文件(binlog), 中繼日誌文件(relay log)。 binlog文件會把mysql中所有數據修改操作以二進位形式記錄，包括create,drop,insert,update,delete操作等，但不記錄查詢select操作。對於二進位binlog文件格式,三種支持類型包括：Statement，Row，Mixed ( 在mysql 架構篇第一篇中有講到)。

　　2.1 從庫relay log 中繼日誌文件

　　　　從庫中繼日誌文件relay log的文件格式，內容和主庫二進位日誌文件binlog一樣，唯一的區別在於從庫上的sql線程在執行完當前中繼日誌文件relay log中的事件之後，sql 線程會自動刪除當前中繼日誌文件realy log,避免從庫上的中繼日誌文件relay log占用過多的磁碟空間。

 　　2.2 從庫 master.info和realy-log.info文件

　　　　為了保證從庫crash重啟後，從庫的I/O線程仍然能夠知道從哪裡開始複製，從庫上預設還會創建二個日誌文件master.info 和realy-log.info 用來保存複製的進度。 這兩個文件在磁碟上以文件形式記錄，位置在data目錄下。I/O線程維護master.info 中主庫二進位日誌binlog的進度。sql線程維護realy-log.info 應用中繼日誌relay log 的進度。 總結是: I/O線程關聯master.info，sql線程關聯realy-log.info。再來看下複製原理圖：

　　2.3 複製狀態信息

　　　　在從庫上通過SHOW SLAVE STATUS，能瞭解複製的狀態, 裡面的信息包含了master.info和realy-log.info的信息。

　　　　(1) master.info對應的信息如下：

Master_Host

172.168.18.201

主庫IP

Master_User

rep1

主庫上用於複製的賬號

Master_Port

3306

主庫mysql 埠

Master_Log_File

mysql-bin.000072

從庫I/O線程當前讀取主庫binlog的文件名

Read_Master_Log_Pos

514

從庫I/O線程讀取主庫binlog的位置

　　　　(2) realy-log.info對應的信息如下：

Relay_Log_File

xuegod64-relay-bin.000005

SQL線程正在應用的relay log

Relay_Log_Pos

680

SQL線程正在應用的relay log的位置

Relay_Master_Log_File

mysql-bin.000072

SQL線程正在應用的relay log對應的binlog

Exec_Master_Log_Pos

514

SQL線程正在應用的relay log的位置對應的主庫binlog的位置

　　　　(3) 其它信息

Connect_Retry

60

連接中斷後，重新嘗試連接的時間間隔。預設值是60秒

Slave_IO_Running

Yes

I/O線程是否被啟動併成功地連接到主伺服器上

Slave_SQL_Running

Yes

SQL線程是否被啟動

 Replicate_Do_DB

 Replicate_Ignore_DB

 Replicate_Do_Table

 Replicate_Ignore_Table

 Replicate_Wild_Do_Table

 Replicate_Wild_Ignore_Table

 

 

指明哪些庫或表在複製的時候不要同步到從庫

 Last_Error

0

SQL線程讀取日誌參數的的錯誤數量和錯誤消息

Skip_Counter

0

設置跳過sql執行步數

Relay_Log_Space

890

原有的中繼日誌結合起來的總大小

Master_SSL_Allowed

No

1) 如果允許對主伺服器進行SSL連接，則值為Yes

2) 如果不允許對主伺服器進行SSL連接，則值為No

3) 如果允許SSL連接，但是從屬伺服器沒有讓SSL支持被啟用，則值為Ignored。

 

SQL_Delay

0

一個非負整數，表示秒數，Slave滯後多少秒於master

Master_Retry_Count

86400

連接主庫失敗最多的重試次數

　　2.4  主庫 binlog 格式

-- 查看當前binlog格式
SHOW VARIABLES LIKE '%binlog_format%'

-- 全部更新
UPDATE  testbackup  SET `name`=CONCAT(`name`,'hello')
--通過mysqlbinlog檢查，可以發現記錄是按row 每行記錄的。
[root@hsr ~]# mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000072
#181029 14:33:20 server id 1  end_log_pos 1539 CRC32 0xde0ceeff     Update_rows: table id 221 flags: STMT_END_F
### UPDATE `test`.`testbackup`
### WHERE
###   @1=1
###   @2='張三2'
### SET
###   @1=1
###   @2='張三2hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=2
###   @2='李四'
### SET
###   @1=2
###   @2='李四hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=3
###   @2='五五'
### SET
###   @1=3
###   @2='五五hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=4
###   @2='趙六'
### SET
###   @1=4
###   @2='趙六hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=5
###   @2='小紅'
### SET
###   @1=5
###   @2='小紅hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=6
###   @2='小明'
### SET
###   @1=6
###   @2='小明hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=7
###   @2='田七'
### SET
###   @1=7
###   @2='田七hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=8
###   @2='小康'
### SET
###   @1=8
###   @2='小康hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=9
###   @2='小王'
### SET
###   @1=9
###   @2='小王hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=10
###   @2='小李'
### SET
###   @1=10
###   @2='小李hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=11
###   @2='小王'
### SET
###   @1=11
###   @2='小王hello'
### UPDATE `test`.`testbackup`
### WHERE
###   @1=12
###   @2='小李子1'
### SET
###   @1=12
###   @2='小李子1hello'
View Code 
-- 現在將binlog格式從row 改為 statement 格式
-- 當前會話
SET binlog_format='statement'
-- 全部更新
UPDATE  testbackup  SET `name`=CONCAT(`name`,'hello123')

　　再通過mysqlbinlog檢查，可以發現記錄是按statement語句級記錄的。

BEGIN
/*!*/;
# at 1714
#181029 14:41:21 server id 1  end_log_pos 1860 CRC32 0x9fdf71e9     Query    thread_id=2    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1540795281/*!*/;
-- 全部更新
update  testbackup  set `name`=concat(`name`,'hello123')
/*!*/;
# at 1860
#181029 14:41:21 server id 1  end_log_pos 1891 CRC32 0x4b7199d3     Xid = 579
COMMIT/*!*/;
View Code

　　總結：在binlog_format格式為row時，mysql實際上在binlog是一行行記錄數據的變更。當格式為statement時是按語句級記錄。 row格式比statement格式更能保證從庫數據的一致性（複製是記錄，而不是單純操作sql），但row格式下的binlog的日誌量很可能會增大好幾倍，在設置時需要考慮磁碟空間問題。

 

三. 刷新磁碟頻率

　　對於支持事務引擎（如innodb）來說, 每個事務提交時都需要寫binlog,對於不支持事務的引擎(例myisam)來說，每個sql語句執行完成時，都需要寫binlog。 為了保證binlog安全,mysql引入了 sync_binlog 參數來控制binlog刷新到磁碟的頻率。

　　關於sync_binlog在： mysql 開發進階篇系列 19 MySQL Server(innodb_flush_log_at_trx_commit與sync_binlog)中有介紹。

-- 查看binlog格式
SHOW VARIABLES LIKE '%sync_binlog%'

　　當sync_binlog=1時，是最安全的，當主機突然斷點，系統最多損失最近的一條事務的數據。但是在多個事務併發提交時，mysql不得不按順序處理請求，同時高頻率的刷新binlog對I/O影響明顯，很大程度影響了mysql的性能。

　　所以一般線上系統mysql的sync_binlog不會設置為1, 而是2或0,在數據安全性和更高的併發之間獲取一個平衡。

 


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

-Advertisement-
Play Games
更多相關文章
  • 深入學習Redis（5）：集群
    Redis集群實現了較為完善的高可用方案。本文將詳細介紹集群，主要內容包括：集群的作用；集群的搭建以及設計方案；集群的基本原理；客戶端訪問集群的方法；以及其他實踐中需要的集群知識。 ...
  • 大數據時代
    《大數據時代》是國外大數據研究的先河之作，本書作者維克托•邁爾•舍恩伯格被譽為“大數據商業應用第一人”，擁有在哈佛大學、牛津大學、耶魯大學和新加坡國立大學等多個互聯網研究重鎮任教的經歷，早在2010年就在《經濟學人》上發佈了長達14頁對大數據應用的前瞻性研究。 維克托•邁爾•舍恩伯格在書中前瞻性地指 ...
  • mysql 5.7.23 解壓版安裝教程
    下載mysql安裝程式 官方下載地址：http://dev.mysql.com/downloads/mysql/ 解壓下載文件，如圖 其中data和my.ini文件需要自己創建 my.ini 文件配置如下： 配置環境變數 電腦 屬性 高級系統屬性 環境變數 初始化data目錄 以管理員命令運行cmd ...
  • Mysql使用binlog恢複數據解決誤操作問題的兩種方法
    1. 基礎知識 安裝mysql5.6資料庫Mysql binlog初步理解 2. 配置mysql 開啟binlog、修改binlog模式為Row Level模式 修改mysql配置文件，在[mysqld]下增加以下內容 3. 重啟mysql資料庫 binlog開啟 生成文件/var/lib/mysq ...
  • MySQL之Innodb恢復的學習筆記
    MySQL · 引擎特性 · InnoDB 崩潰恢復過程 innodb中的 3個lsn innodb的lsn和oracle的scn一樣，是一個重要的概念。比如 在flush list中正是是使用low lsn作為鏈表的條件參考buf_page_t中的lsn_t oldest_modification ...
  • MySQL · 數據恢復 · undrop-for-innodb
    Ref：https://www.aliyun.com/jiaocheng/1109809.html 摘要： 簡介 undrop-for-innodb 是針對 innodb 的一套數據恢復工具，可以從文件級別恢復諸如：DROP/TRUNCATE table, 刪除表中某些記錄，innodb 文件被刪除 ...
  • MySQL Bug導致異常宕機的分析流程
    原文鏈接：http://click.aliyun.com/m/42521/ 摘要： 本文主要通過一個bug來記錄一下如何分析一個MySQL bug的崩潰信息。 版本:Percona 5.7.17-11 一、資料庫重啟日誌分析 terminate called after throwing an in ...
  • ORACLE閃回機制分析與研究應用
    1.查看資料庫歸檔和閃回狀態，及環境準備SQL> archive log list;SQL> select flashback_on from v$database;關閉資料庫,啟動歸檔和閃回SQL> shutdown immediate;啟動到mount模式SQL> startup mount;開 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類