主從復(fù)制查詢狀態(tài)+故障及延時(shí)

1. 查看配置文件

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 739
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 7
                  Master_UUID: e44991f2-b834-11e9-8c01-000c2992bac2
             Master_Info_File: /data/3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

1.1 查看線程狀態(tài)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Running"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

1.2 線程報(bào)錯(cuò)具體信息

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Last"
                   Last_Errno: 0
                   Last_Error: 
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 

1.3 主庫(kù)連接信息以及二進(jìn)制信息(master)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Master" | grep -v 'SSL'
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
        Relay_Master_Log_File: mysql-bin.000007
          Exec_Master_Log_Pos: 154
        Seconds_Behind_Master: 0
             Master_Server_Id: 7
                  Master_UUID: e44991f2-b834-11e9-8c01-000c2992bac2
             Master_Info_File: /data/3308/data/master.info
           Master_Retry_Count: 86400
                  Master_Bind: 
           Master_TLS_Version:

1.4 從庫(kù)和主庫(kù)延時(shí)的時(shí)間

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep Behind"_Master"
Seconds_Behind_Master: 0

1.5 過(guò)濾復(fù)制相關(guān)狀態(tài)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Replicate"
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB:

1.6 延時(shí)從庫(kù)的狀態(tài)信息

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Delay"
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

1.7 查看監(jiān)控GTID狀態(tài)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Gtid"
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 

1.8 中繼日志監(jiān)控

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Relay"
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
              Relay_Log_Space: 739

2. 主從復(fù)制故障

2.1 IO線程故障

  • 讀取master.info

損壞
信息錯(cuò)誤change master to信息錯(cuò)誤

  • 連接主庫(kù)

網(wǎng)絡(luò)
防火墻
主機(jī)(主庫(kù))沒有運(yùn)行
連接數(shù)上線

>> 以上問(wèn)題報(bào)錯(cuò):
Slave_IO_running: Connecting
Last_IO_Error: xxxxxxx

>> 排查方法
通過(guò)復(fù)制用戶着帽,手工連接主庫(kù),查看報(bào)錯(cuò)現(xiàn)象

修復(fù):
stop slave
reset slave all
change master to
start slave
  • 請(qǐng)求日志
master.info 復(fù)制起點(diǎn)
主庫(kù):損害移层,誤刪除等操作
  • 接收日志
relaylog損壞
>>修復(fù)
stop slave
reset slave all
change master to
start slave
  • 更新mater.info(拿到的信息存放在relay.info中)

2.2 SQL線程故障

(1) relay.info
(2) 回放relaylog中的日志

SQL語(yǔ)句為什么會(huì)失斎院病?
(1) 語(yǔ)法观话,SQL_Mode版

  • 版本差異予借,SQL_mode不一致

(2) DDL DCL DML為什么會(huì)失敗

  • create database /table 創(chuàng)建的對(duì)象已經(jīng)存在。
    從庫(kù)被提前寫入了
  • drop database errdb; 要?jiǎng)h除和修改的對(duì)象不存在
  • alter.insert update,delete

處理方法(以從庫(kù)為核心的處理方案):

方法一:
stop slave; 
set global sql_slave_skip_counter = 1;
#將同步指針向下移動(dòng)一個(gè)频蛔,如果多次不同步灵迫,可以重復(fù)操作。
start slave;
方法二:
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常見錯(cuò)誤代碼:
1007:對(duì)象已存在
1032:無(wú)法執(zhí)行DML
1062:主鍵沖突,或約束沖突

但是晦溪,以上操作有時(shí)是有風(fēng)險(xiǎn)的瀑粥,最安全的做法就是重新構(gòu)建主從。把握一個(gè)原則,一切以主庫(kù)為主.

2.3 防止從庫(kù)寫入

>> (1) 可以設(shè)置從庫(kù)只讀.
mysql[(none)]>show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)
注意:
只會(huì)影響到普通用戶三圆,對(duì)管理員用戶無(wú)效狞换。
>> (2)加中間件
讀寫分離。

2.4 其他擴(kuò)展

pt-xxx 關(guān)于主從方面的工具
檢查主從數(shù)據(jù)一致性
實(shí)現(xiàn)主從數(shù)據(jù)同步

3. 主從延時(shí)

3.1 延時(shí)

主庫(kù)做的事舟肉,從庫(kù)很久才執(zhí)行修噪。

3.2 主從延時(shí)的現(xiàn)象

(1)最直觀:主庫(kù)做變更,從庫(kù)看狀態(tài)
(2)Seconds_Behind_Master:0(只能證明路媚,有或沒有)
(3)計(jì)算日志的差異

3.3 主從延時(shí)的原因

(1)外部因素

  • 網(wǎng)絡(luò)
  • 硬件
  • 版本差異
  • 參數(shù)差異

(2)主從復(fù)制的內(nèi)部原因
主庫(kù):

  • 二進(jìn)制日志的方面

二進(jìn)制日志落地不及時(shí)

解決方案:
sync_binlog=1
可以將binlog單獨(dú)存放在高性能存儲(chǔ)中

  • Dump_T(默認(rèn)是串行工作模式)

主庫(kù)的事務(wù)量大
主庫(kù)發(fā)生大事務(wù)

解決方案:

  1. GTID模式
  2. 雙一的保證

如何監(jiān)控:

主庫(kù):
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000017 |      234 |              |                  | 06073cdc-bfd1-11e9-9a47-000c2992bac2:1-6,
c4e0cd26-b75c-11e9-b4d8-000c2992bac2:1-242 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

從庫(kù): 
show slave status\G;
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154

從庫(kù):
(1)IO線程方面
relaylog寫入
解決方案:
可以將relaylog單獨(dú)存放在高性能存儲(chǔ)中
(2)SQL線程方面(只有一個(gè),串行回放)
默認(rèn)SQL線程割按,只能逐條的回放SQL
事務(wù)并發(fā)度高或者大事務(wù)

5.6 版本加入了多SQL復(fù)制

按照庫(kù)(database)級(jí)別,進(jìn)行并發(fā)回放SQL
slave_parallel_workers=16
slave_parallel_type=DATABASE

5.7 版本進(jìn)行了多SQL復(fù)制加強(qiáng)(MTS)

真正按照事務(wù)級(jí)別磷籍,實(shí)現(xiàn)了多SQL線程回放适荣。
slave_parallel_workers=16
slave_parallel_type=logical_clock

注意:必須依賴于GTID復(fù)制

如何監(jiān)控:

(1)監(jiān)控取了多少日志
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
(2)監(jiān)控回放了多少日志
[root@db01 data]$ cat relay-log.info 
7
./db01-relay-bin.000002
320
mysql-bin.000001
154

4. 主從基礎(chǔ)小結(jié)

4.1 主從前提
4.2 主從原理 *****
4.3 主從監(jiān)控
    show master status;
    show processlist;
    show slave status\G
    master.info 
    relay.info
4.4 主從故障
    IO 
        連接 
        binlog
    SQL 
        從庫(kù)寫入
        DML,insert ,update ,delete

4.5 主從延時(shí) 
    主
        dump 串行 : GTID,雙一 并行
        show slave status\G 
        Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 1084       
        
        show master status \G
        
    從
        SQL 串行  : MTS 
        已經(jīng)拿過(guò)來(lái)的日志: 
        show slave status\G 
        Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 1084
        已經(jīng)執(zhí)行過(guò)的:
        ./db01-relay-bin.000003
        920
        mysql-bin.000001
        800
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末现柠,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子弛矛,更是在濱河造成了極大的恐慌够吩,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,729評(píng)論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件丈氓,死亡現(xiàn)場(chǎng)離奇詭異周循,居然都是意外死亡,警方通過(guò)查閱死者的電腦和手機(jī)万俗,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,226評(píng)論 3 399
  • 文/潘曉璐 我一進(jìn)店門湾笛,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人闰歪,你說(shuō)我怎么就攤上這事嚎研。” “怎么了库倘?”我有些...
    開封第一講書人閱讀 169,461評(píng)論 0 362
  • 文/不壞的土叔 我叫張陵临扮,是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我教翩,道長(zhǎng)杆勇,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,135評(píng)論 1 300
  • 正文 為了忘掉前任饱亿,我火速辦了婚禮蚜退,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘彪笼。我一直安慰自己钻注,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,130評(píng)論 6 398
  • 文/花漫 我一把揭開白布杰扫。 她就那樣靜靜地躺著队寇,像睡著了一般膘掰。 火紅的嫁衣襯著肌膚如雪章姓。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 52,736評(píng)論 1 312
  • 那天识埋,我揣著相機(jī)與錄音凡伊,去河邊找鬼。 笑死窒舟,一個(gè)胖子當(dāng)著我的面吹牛系忙,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播惠豺,決...
    沈念sama閱讀 41,179評(píng)論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼银还,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼风宁!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起蛹疯,我...
    開封第一講書人閱讀 40,124評(píng)論 0 277
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤戒财,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后捺弦,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體饮寞,經(jīng)...
    沈念sama閱讀 46,657評(píng)論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,723評(píng)論 3 342
  • 正文 我和宋清朗相戀三年列吼,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了幽崩。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,872評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡寞钥,死狀恐怖慌申,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情凑耻,我是刑警寧澤太示,帶...
    沈念sama閱讀 36,533評(píng)論 5 351
  • 正文 年R本政府宣布,位于F島的核電站香浩,受9級(jí)特大地震影響类缤,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜邻吭,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,213評(píng)論 3 336
  • 文/蒙蒙 一餐弱、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧囱晴,春花似錦膏蚓、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,700評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至枯芬,卻和暖如春论笔,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背千所。 一陣腳步聲響...
    開封第一講書人閱讀 33,819評(píng)論 1 274
  • 我被黑心中介騙來(lái)泰國(guó)打工狂魔, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人淫痰。 一個(gè)月前我還...
    沈念sama閱讀 49,304評(píng)論 3 379
  • 正文 我出身青樓最楷,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子籽孙,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,876評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容

  • 一烈评、緣起 mysql主從復(fù)制,讀寫分離是互聯(lián)網(wǎng)用的非常多的mysql架構(gòu)犯建,主從復(fù)制最令人詬病的地方就是础倍,在數(shù)據(jù)量較...
    lxqfirst閱讀 1,366評(píng)論 0 3
  • 一、GTID的概述: 1胎挎、全局事物標(biāo)識(shí):global transaction identifieds沟启。 2、GTI...
    z這只是z閱讀 1,271評(píng)論 0 3
  • MySQL Replication 主從復(fù)制(也稱 AB 復(fù)制)允許將來(lái)自一個(gè)MySQL數(shù)據(jù)庫(kù)服務(wù)器(主服務(wù)器)的...
    運(yùn)維開發(fā)_西瓜甜閱讀 64,549評(píng)論 8 65
  • mysql主從復(fù)制 主從復(fù)制慨述 構(gòu)建大型犹菇,高性能應(yīng)用程序的基礎(chǔ)主服務(wù)器復(fù)制負(fù)責(zé)更新德迹,且將更新寫入二進(jìn)制日志文件,...
    肖金光xjg閱讀 893評(píng)論 0 1
  • 摘要:本節(jié)將集中討論下面三種GTID更新的時(shí)機(jī)揭芍,這部分相當(dāng)重要胳搞,后面的故障案列會(huì)和這節(jié)有關(guān)寻仗。下面先來(lái)看一下他們的定...
    暖夏未眠丶閱讀 1,329評(píng)論 0 1