一糠涛、主從復(fù)制基礎(chǔ)

企業(yè)高可用標(biāo)準(zhǔn)(全年無(wú)故障率)

99.9% 故障率:0.1% 3642460=525.6min
99.99% 故障率:0.01%3642460=52.56min
99.999% 故障率:0.001%36424*60=5.256min

1.企業(yè)級(jí)高可用方案

負(fù)載均衡：有一定的高可用性靖榕。
LVS Nginx
主備系統(tǒng):有高可用性欧聘，但是需要切換根吁，是單活的架構(gòu)
KeepAlive,MHA(日本人研發(fā)),MMM员淫，TMHA
真正高可用（多活系統(tǒng)):
MySQL NDB CLuster
Oracle RAC
Sysbase cluster
InnoDB Cluster(MGR 5.7.17),
PXC ***
MGC ***

2.主從復(fù)制簡(jiǎn)介 **

2.1 基于二進(jìn)制日志復(fù)制的

2.2 主庫(kù)的修改操作會(huì)記錄二進(jìn)制日志

2.3 從庫(kù)會(huì)請(qǐng)求新的二進(jìn)制日志并回收，最終達(dá)到主從數(shù)據(jù)同步

2.4 主從復(fù)制核心功能

輔助備份击敌，處理物理?yè)p壞
擴(kuò)展新型的架構(gòu):高可用满粗，高性能，分布式架構(gòu)等愚争。

3.主從復(fù)制的前提（主從復(fù)制的規(guī)劃,實(shí)施過程）

3.1 至少2個(gè)數(shù)據(jù)庫(kù)實(shí)例

3.2 主庫(kù)要開啟binlog,不同server_id,server_uuid

3.3 主庫(kù)要有一個(gè)專門用作復(fù)制的用戶（replication slave）

3.4 通過備份將源庫(kù)數(shù)據(jù)補(bǔ)償?shù)綇膸?kù)

3.5 告知從庫(kù),用戶名密碼,ip,port,自動(dòng)復(fù)制的起點(diǎn)映皆。

3.6需要專門的復(fù)制線程（start slave）

4.搭建主從復(fù)制

4.1 準(zhǔn)備多實(shí)例環(huán)境

[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# mysql -S /data/3307/mysql.sock
[root@db01 ~]# mysql -S /data/3308/mysql.sock

4.2 檢查 主庫(kù)binlog,不同server_id,server_uuid

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@log_bin;select @@server_id"
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "select @@log_bin;select @@server_id"

4.3 主庫(kù)創(chuàng)建復(fù)制用戶

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
[root@db01 ~]# mysql -S /data/3307/mysql.sock  -e "select user,host from mysql.user where user='repl';"
+------+----------+
| user | host     |
+------+----------+
| repl | 10.0.0.% |
+------+----------+

4.4 通過備份將源庫(kù)數(shù)據(jù)補(bǔ)償?shù)綇膸?kù)

 mysqldump  -S /data/3307/mysql.sock -A  -R -E --triggers --master-data=2 --single-transaction --max-allowed-packet=128M   >/tmp/full.sql

[root@db01 ~]# mysql -S /data/3308/mysql.sock </tmp/fuul.sql 
[root@db01 ~]# 

4.5 告知從庫(kù),用戶名，密碼,IP,port,自動(dòng)復(fù)制的起點(diǎn)

3.5 告知從庫(kù),用戶名,密碼,ip,port,自動(dòng)復(fù)制的起點(diǎn)

# change master to  如果執(zhí)行錯(cuò)誤 記得stop slave

[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.51',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123',
    ->   MASTER_PORT=3307,
    ->   MASTER_LOG_FILE='mysql-bin.000003',
    ->   MASTER_LOG_POS=444,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>   /重試次數(shù)
  
vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000011', MASTER_LOG_POS=444;

4.6 啟動(dòng)主從線程

[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>start slave;

4.7檢測(cè)主從狀態(tài)

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# 

4.8 簡(jiǎn)單排錯(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: 
[root@db01 ~]# 

問題:

[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>stop slave ;
oldguo[(none)]>reset slave all;
oldguo[(none)]> CHANGE MASTER TO xxxx
oldguo[(none)]>start slave;

5. 主從復(fù)制原理

5.1 主從復(fù)制過程中涉及到的文件

5.1.1 主庫(kù):
binlog 日志
/data/3307:
mysql-bin.000001
mysql-bin.000002
5.1.2 從庫(kù):
relaylog 中繼日志
臨時(shí)存儲(chǔ)日志信息的文件
/data/3308/data
db01-relay-bin.000001
db01-relay-bin.000002
master.info 信息文件
主庫(kù)信息文件
relay-log.info 信息文件
中繼日志信息文件

5.2 主從復(fù)制中涉及到的線程

主庫(kù):

Binlog_Dump_Thread(二進(jìn)制日志投遞線程)
[root@db01 /data/3308/data]# mysql -S /data/3307/mysql.sock -e "show processlist;"

從庫(kù):

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|egrep "Running:" 
Slave_IO_Thread
Slave_SQL_Thread

6.主從復(fù)制的監(jiān)控

從庫(kù)
show slave status\G

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

mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Replicate_"

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

mysql -S /data/3308/mysql.sock  -e  "show slave status \G"|grep "Delay:"

6.1 監(jiān)控Gtid復(fù)制狀態(tài)信息

mysql -S /data/3308/mysql.sock  -e  "show slave status \G"|grep "GTID"

7主從復(fù)制故障

7.1 IO線程故障

（1）讀取master.info
損壞
信息錯(cuò)誤 change master to 信息錯(cuò)誤
（2）連接主庫(kù)
網(wǎng)絡(luò)
防火墻
主庫(kù)沒啟動(dòng)
連接數(shù)上限了

以上問題:
Slave_IO_Running:Connecting
Last_IO_Error:xxxx
排查方法:
通過復(fù)制用戶,手工連接主庫(kù),看報(bào)錯(cuò)信息轰枝。
修復(fù):
stop slave
reset slave all
change master to
start slave

(3) 請(qǐng)求日志
master.info 復(fù)制起點(diǎn)
主庫(kù):損壞捅彻，誤刪除等操作
二進(jìn)制日志滿了 清了主庫(kù)的二進(jìn)制日志 從庫(kù)數(shù)據(jù)異常。
查看從庫(kù)狀態(tài)信息鞍陨，主庫(kù)信息步淹，然后先停掉從庫(kù)stop slave;重新清掉從庫(kù)的信息reset slave all 接著再重新按著主庫(kù)的起點(diǎn)和二進(jìn)制日志號(hào)重新change master to 然后start slave啟動(dòng)從庫(kù)。最后查一下狀態(tài)诚撵，發(fā)現(xiàn)就好了缭裆。

(4)接收日志
relaylog損壞
修復(fù):
stop slave
reset slave all
change master to
start slave
(5)更新master.info

7.2 SQL線程故障 *****

(1)relay.info
(2)回放relaylog中的日志 *****
SQL語(yǔ)句為什么會(huì)失敗寿烟？
(1)語(yǔ)法,SQL_Mode
版本,sql_mode不一致
(2)DDL DML為什么會(huì)失敗
create database /table 創(chuàng)建的對(duì)象已經(jīng)存在了澈驼。
從庫(kù)被提前寫入了

處理方法（以從庫(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ù)為主。

7.3 防止從庫(kù)寫入

(1)可以設(shè)置從庫(kù)只讀參數(shù)

show variables like "%read_only%";

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF | 普通用戶
| super_read_only | OFF | 對(duì)root用戶
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.01 sec)

（2）加中間件
讀寫分離

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

8主從延時(shí) *****

8.1 什么是主從延時(shí)

主庫(kù)做的事,從庫(kù)很久才執(zhí)行漠其。

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

（1）最直觀:主庫(kù)做變更嘴高，從庫(kù)看數(shù)據(jù)狀態(tài)。
（2）Seconds_Behind_Master:0（只能證明,有或者沒有）
（3）計(jì)算日志的差異

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

8.3.1外部因素

網(wǎng)絡(luò)
硬件
主庫(kù)繁忙
版本差異
參數(shù)因素

8.3.2 內(nèi)部因素

主庫(kù):
（1）二進(jìn)制日志方面
二進(jìn)制日志落地不及時(shí)
解決方案：
sync_binlog=1
可以將binlog單獨(dú)存放高性能存儲(chǔ)中
(2)Dump_T(默認(rèn)是串行工作模式)
主庫(kù)的事務(wù)量大
主庫(kù)發(fā)生大事務(wù)

解決方法:
1.GTID模式
2.雙一的保證

如何監(jiān)控

      主庫(kù):show master status;
      從庫(kù)  show slave status\G
      Master_log_File:mysql-bin-000001
      Read_Master_Log_Pos:484

從庫(kù)

        （1）IO線程方面
                 relaylog寫入
                 解決方案:

relay_log_basename        | /data/3307/data/db01-relay-bin       |
| relay_log_index           | /data/3307/data/db01-relay-bin.index
relay_log_purge 自動(dòng)做清理

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=DTATBASE

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ù)制，并且binlog_format=row

如何監(jiān)控:
(1)監(jiān)控取了多少日志

show slave status\G


Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 444

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

6.1主從前提

6.2主從原理

6.3主從監(jiān)控

show processlist
show slave status\G
master.info
relay.info

6.4主從故障

IO
連接
binlog
SQL
從庫(kù)寫入
DML,insert,update,delete

6.5主從延時(shí)

主
dump 串行:GTID 颠印，雙一 (從庫(kù)和主庫(kù)執(zhí)行的gtid號(hào)是完全一致的) 并行
show slave status\G
Master_Master_log_Pos:1084
show master status\G
從
SQL 串行 :MTS
已經(jīng)拿過來的日志:
show slave status\G
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos:1084
已經(jīng)執(zhí)行過多:
./db01-relay-bin.000003
920
mysql-bin.000001
800