Mysql主從複製是一個大多數公司都會用到的一項技術，下面是原理和實現方法，如果感興趣可以多學多練，理解清楚再使用，否則後續出現問題會比較麻煩，祝工作順利Good luck!

0.企業高可用性標準 ***

0.1 全年無故障率(非計劃內故障停機) 99.9% ---->

0.001*365*24*60=525.6 min 99.99% ----> 0.0001*365*24*60=52.56 min 99.999% ----> 0.0001*365*24*60=5.256 min

0.2 高可用架構方案

負載均衡:有一定的高可用性 LVS Nginx 主備系統:有高可用性,但是需要切換,是單活的架構 KA , MHA, MMM 真正高可用(多活系統): NDB Cluster Oracle RAC Sysbase cluster , InnoDB Cluster（MGR）,PXC , MGC

1. 主從複製簡介 **

1.1. 基於二進位制日誌複製的

1.2. 主庫的修改操作會記錄二進位制日誌 1.3. 從庫會請求新的二進位制日誌並回放,最終達到主從資料同步

1.4. 主從複製核心功能: 輔助備份,處理物理損壞 擴充套件新型的架構:高可用,高效能,分散式架構等

2. 主從複製前提(搭建主從的過程) *** 2.1 兩臺以上mysql例項 ,server_id,server_uuid不同

2.2 主庫開啟二進位制日誌

2.3 專用的複製使用者

2.4 保證主從開啟之前的某個時間點,從庫資料是和主庫一致(補課)

2.5 告知從庫,複製user,passwd,IP port,以及複製起點(change master to)

2.6 執行緒(三個):Dump thread IO thread SQL thread 開啟(start slave)

3. 主從複製搭建(Classic replication) *** 3.1 清理主庫資料 rm -rf /data/3307/data/*

3.2 重新初始化3307 mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/33 07/data

3.3 修改my.cnf ,開啟二進位制日誌功能 [root@db01 3307]# vim /data/3307/my.cnf log_bin=/data/3307/data/mysql-bin

3.4 啟動所有節點 [root@db01 3307]# systemctl start mysqld3307 [root@db01 3307]# systemctl start mysqld3308 [root@db01 3307]# systemctl start mysqld3309 [root@db01 3307]# ps -ef |grep mysqld mysql 3684 1 4 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf mysql 3719 1 7 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf mysql 3754 1 8 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf [root@db01 3307]# mysql -S /data/3307/mysql.sock -e "select @@server_id" [root@db01 3307]# mysql -S /data/3308/mysql.sock -e "select @@server_id" [root@db01 3307]# mysql -S /data/3309/mysql.sock -e "select @@server_id"

3.5 主庫中建立複製使用者 [root@db01 3307]# mysql -S /data/3307/mysql.sock db01 [(none)]>grant replication slave on *.* to repl@"10.0.0.%" identified by "123"; db01 [(none)]>select user,host from mysql.user;

3.6 備份主庫並恢復到從庫 [root@db01 3307]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/backup/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=653; [root@db01 3307]# mysql -S /data/3308/mysql.sock db01 [(none)]>source /backup/full.sql

3.7 告知從庫關鍵複製資訊 ip port user password binlog position [root@db01 3307]# mysql -S /data/3308/mysql.sock db01 [mysql]>help change master to CHANGE MASTER TO MASTER_HOST="10.0.0.51", MASTER_USER="repl", MASTER_PASSWORD="123", MASTER_PORT=3307, MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=653, MASTER_CONNECT_RETRY=10; 3.8 開啟主從專用執行緒 start slave ;

3.9 檢查複製狀態 db01 [mysql]>show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes

4. 主從複製的原理 (Classic Replication)*****

4.1 主從中設定到的檔案和執行緒

4.1.1 執行緒 主: DUMP THREAD 從: IO THREAD SQL THREAD

4.1.2 檔案 主: mysql-bin.000001 從: db01-relay.000001 ===>中繼日誌 master.info ===》主庫資訊記錄日誌 relay-log.info ===> 記錄中繼應用情況資訊

4.2 主從複製原理

主從複製原理描述： 1.change master to 時，ip pot user password binlog position寫入到master.info進行記錄

2. start slave 時，從庫會啟動IO執行緒和SQL執行緒

3.IO_T，讀取master.info資訊，獲取主庫資訊連線主庫

4. 主庫會生成一個準備binlog DUMP執行緒，來響應從庫

5. IO_T根據master.info記錄的binlog檔名和position號，請求主庫DUMP最新日誌

6. DUMP執行緒檢查主庫的binlog日誌，如果有新的，TP(傳送)給從從庫的IO_T

7. IO_T將收到的日誌儲存到了TCP/IP 快取，立即返回ACK給主庫 ，主庫工作完成

8.IO_T將快取中的資料，儲存到relay-log日誌檔案,更新master.info檔案binlog 檔名和postion，IO_T工作完成

9.SQL_T讀取relay-log.info檔案，獲取到上次執行到的relay-log的位置，作為起點，回放relay-log

10.SQL_T回放完成之後，會更新relay-log.info檔案。

11. relay-log會有自動清理的功能。 細節：

1.主庫一旦有新的日誌生成，會發送“訊號”給binlog dump ，IO執行緒再請求

5. 主從故障監控\分析\

5.1 執行緒相關監控 主庫: show full processlist; 每個從庫都會有一行dump相關的資訊 HOSTS: db01:47176 State: Master has sent all binlog to slave; waiting for more updates 如果現實非以上資訊,說明主從之間的關係出現了問題 從庫: db01 [(none)]>show slave status \G *************************** 1. row *************************** 主庫相關資訊監控 Master_Host: 10.0.0.51 Master_User: repl Master_Port: 3307 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 444 從庫中繼日誌的應用狀態 Relay_Log_File: db01-relay-bin.000002 Relay_Log_Pos: 485 從庫複製執行緒有關的狀態 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 過濾複製有關的狀態 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 主從延時相關狀態(非人為) Seconds_Behind_Master: 0 延時從庫有關的狀態(人為) SQL_Delay: 0 SQL_Remaining_Delay: NULL GTID 複製有關的狀態 Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 5.2 主從複製故障分析

5.2.1 IO 5.2.1.1 連線主庫 (1) 使用者 密碼 IP port Last_IO_Error: error reconnecting to master "[email protected]:3307" - retry-time: 10 retries: 7 [root@db01 ~]# mysql -urepl -p123333 -h 10.0.0.51 -P 3307 ERROR 1045 (28000): Access denied for user "repl"@"db01" (using password: YES) 原因: 密碼錯誤 使用者錯誤 skip_name_resolve 地址錯誤 埠 處理方法 stop slave reset slave all change master to start slave 主庫連線數上線,或者是主庫太繁忙 show slave staus \G Last_IO_Errno: 1040 Last_IO_Error: error reconnecting to master "[email protected]:3307" - retry-time: 10 retries: 7 處理思路: 拿複製使用者,手工連線一下 [root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1040 (HY000): Too many connections 處理方法: db01 [(none)]>set global max_connections=300; (3) 防火牆,網路不通

5.2.1.2 請求二進位制日誌 主庫缺失日誌 從庫方面,二進位制日誌位置點不對 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: "could not find next log; the first event "mysql-bin.000001" at 154, the last event read from "/data/3307/data/mysql-bin.000002" at 154, the last byte read from "/data/3307/data/mysql-bin.000002" at 154." 注意: 在主從複製環境中,嚴令禁止主庫中reset master; 可以選擇expire 進行定期清理主庫二進位制日誌 解決方案: 重新構建主從

5.2.2 SQL 執行緒故障 SQL執行緒功能： (1)讀寫relay-log.info (2)relay-log損壞,斷節,找不到 (3)接收到的SQL無法執行 導致SQL執行緒故障原因分析：

1.版本差異，引數設定不同，比如：資料型別的差異，SQL_MODE影響

2.要建立的資料庫物件,已經存在

4.DML語句不符合表定義及約束時. 歸根揭底的原因都是由於從庫發生了寫入操作. Last_SQL_Error: Error "Can"t create database "db"; database exists" on query. Default database: "db". Query: "create database db" 處理方法(以從庫為核心的處理方案)：

方法一： stop slave; set global sql_slave_skip_counter = 1;#將同步指標向下移動一個，如果多次不同步，可以重複操作。start slave;

方法二： /etc/my.cnf slave-skip-errors = 1032,1062,1007 常見錯誤程式碼: 1007:物件已存在 1032:無法執行DML 1062:主鍵衝突,或約束衝突 但是，以上操作有時是有風險的，最安全的做法就是重新構建主從。把握一個原則,一切以主庫為主. 一勞永逸的方法:

(1) 可以設定從庫只讀. db01 [(none)]>show variables like "%read_only%"; 注意： 只會影響到普通使用者，對管理員使用者無效。

(2)加中介軟體 讀寫分離。

6. 主從延時監控及原因 ***** 主庫做了修改操作,從庫比較長時間才能追上。 6.1 外在因素 網路 主從硬體差異較大 版本差異 引數因素

6.2 主庫 (1) 二進位制日誌寫入不及時 [rep]>select @@sync_binlog; (2) CR的主從複製中,binlog_dump執行緒,事件為單元,序列傳送二進位制日誌(5.6 5.5) 1. 主庫併發事務量大,主庫可以並行,傳送時是序列

2. 主庫發生了大事務,由於是序列傳送,會產生阻塞後續的事務. 解決方案:

1. 5.6 開始,開啟GTID,實現了GC(group commit)機制,可以並行傳輸日誌給從庫IO

2. 5.7 開始,不開啟GTID,會自動維護匿名的GTID,也能實現GC,我們建議還是認為開啟GTID 3. 大事務拆成多個小事務,可以有效的減少主從延時.

6.3 從庫 SQL執行緒導致的主從延時 在CR複製情況下: 從庫預設情況下只有一個SQL,只能序列回放事務SQL

1. 主庫如果併發事務量較大,從庫只能序列回放

2. 主庫發生了大事務,會阻塞後續的所有的事務的執行 解決方案: 1. 5.6 版本開啟GTID之後,加入了SQL多執行緒的特性,但是隻能針對不同庫(database)下的事務進行併發回放. 2. 5.7 版本開始GTID之後,在SQL方面,提供了基於邏輯時鐘(logical_clock),binlog加入了seq_no機制, 真正實現了基於事務級別的併發回放,這種技術我們把它稱之為MTS(enhanced multi-threaded slave).

3. 大事務拆成多個小事務,可以有效的減少主從延時.

小結

1. 主從複製原理

2. 主從複製故障

3. 主從延時：group commit

1、原理：mysql開啟binlog日誌記錄功能，然後主mysql上的操作都會記錄到binlog,其他mysql同步這個binlog 就行了。

01前言

文章一切從簡是米兜的一貫作風，太長的文章閱讀起來感覺就費勁。如何使用mysql主從配置？我們就從簡單的例子看一看，更加詳細的配置可以自行google，這裡過一下主從配置且能你快速上手。

MySQL主從複製工作原理圖，過一下：

主伺服器上面的任何修改都會儲存在二進位制日誌Binary log裡面，從伺服器上面啟動一個I/O thread（實際上就是一個主伺服器的客戶端程序），連線到主伺服器上面請求讀取二進位制日誌，然後把讀取到的二進位制日誌寫到本地的一個Realy log裡面。從伺服器上面開啟一個SQL thread定時檢查Realy log，如果發現有更改立即把更改的內容在本機上面執行一遍。

兩臺伺服器做配置測試：虛擬機器中的 ubuntu (192.168.128.131) 和宿主機 xp (192.168.128.1)

分別安裝有 mysql。最好版本一樣（我的不一樣）

機子都 ping 得通

配置 mysql

#vi /etc/mysql/my.cnf

[mysqld]

log-bin=mysql-bin #[必須]啟用二進位制日誌

server-id=1 #[必須]伺服器唯一ID，預設是1，一般取IP最後一段

檢查 mysql 是否允許遠端連線

bind-address = 0.0.0.0 #這樣表示允許所有網段連線

進入 mysql 終端

#mysql -uroot -proot

新建 mysql 使用者（從伺服器用到此賬號配置）backup

mysql> GRANT REPLICATION backup ON *.* to "backup"@"192.168.128.%" identified by "backup";

重新整理一下許可權表

mysql> flush privileges;

重啟 mysql

#service mysql restart

進入 mysql 終端檢視 master 狀態

mysql> show master status;

記錄 file 和 position 的值，從伺服器配置用到。這個時候不要去動主資料庫，會影響 position 的值。

配置 mysql 的 my.ini 並重啟

[mysqld]

log-bin=mysql-bin #[可選]啟用二進位制日誌

server-id=2

登入 mysql 終端後執行

mysql> change master to master_host="192.168.128.131",

master_user="backup",

master_password="backup",

master_log_file="mysql-bin.000002",

master_log_pos=612;

啟動 slave 從庫

mysql> start slave;

檢視 slave 從庫

mysql> show slave status\G;

Slave_IO_Running 和 Slave_SQL_Running 必須都為 Yes 才算成功。

ubuntu mysql

mysql> create database test_repl;

然後檢視 xp mysql 如果出現 test_repl 庫則說明主從複製配置成功

mysql> show databases;

這只是最基本的配置，還有很多配置命令就不折騰了。測試時發現之前庫不一致時我在主庫裡面新增一張表，而從庫沒有那個庫，導致從庫 slave 會報錯，執行這個命令後重啟從庫就可以跳過這個錯誤。

#SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 跳過一個事務