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
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類