MySQL主從配置詳解

一、mysql主從原理

1. 基本介紹

MySQL 內建的復制功能是構建大型，高性能應用程序的基礎。將 MySQL 的 數億分布到到多個系統上去，這種分步的機制，是通過將 MySQL 的某一臺主機的數據復制到其它主機( Slave )上，并重新執行一遍來實現的。復制過程中一個服務器充當服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日志，并維護文件的一個索引以跟蹤日志循環。這些日志可以記錄發送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置，從服務器接收從那時起發生的任何更新，然后封鎖等等主服務器通知新的更新。

請注意當你進行復制時，所有對復制中的表的更新必須在主服務器上進行。否則，你必須要小心，以避免用戶對主服務器上的表進行的更新與對服務器上的表所進行的更新之間的沖突

2. MySQL支持的復制類型

基于語句的復制。 在主服務器上執行的 SQL 語句，在從服務器上執行同樣的語句。否則，你必須要小心，以避免用戶對主服務器上的表進行的更新與對服務器上的表所進行的更新之間的沖突，配置：binlog_format = 'STATEMENT'

基于行的復制。把改變的內容復制過去，而不是把命令在從服務器上執行一遍，從 MySQL 5.0開始支持，配置：binlog_format = 'ROW'

混合類型的復制。默認采用基于語句的復制，一旦發現基于語句的無法精確的復制時，就會采用基于行的復制,配置：binlog_format = 'MIXED'

3. mysql復制解決的問題

數據分布

負載平衡

備份

高可用性和容錯行

4. 復制是如何工作的

可以簡化為三個步驟(如下圖)：

Master 將改變記錄到二進制日志中。

Slave 將 Master 的二進制日志拷貝到它的中繼日志( Relay_log )

Slave 重做中繼日志中的事件，將改變反映它自己的數據

說明:

Master 記錄二進制的日志。在每個事務更新數據之前，Master 在二進制日志記錄這些改變。 MySQL 將事務日志的寫入二進制日志，及時事務中的語句都市交叉執行的。在事件寫入二進制日志完成后，Master 通知存儲引擎提交事務。

Slave 將 Master 的 Binary log 拷貝到它自己的中繼日志。首先 Slave 開始一個工作線程--I/O線程。I/O 線程在 Master 上打開一個連接，然后開始從二進制日志中讀取事件，如果已經連上 Master，它會并等待master產生新的事件。I/O線程就這些事件寫入中繼日志。

SQL Slave Thread ( SQL從線程)處理該過程的最后一步。SQL純種從中繼日志讀取事件，并重放其中的事件而更新 Slave 的數據。使其它與 Master 中的數據保持一致。只要該線程與 I/O 線程保持一致，中繼日志通常會位于 OS 的緩存中，所以中繼日志的開銷很小。

此處，在 Master 中也有一個工作線程，和其他 MySQL 的連接一樣，Slave 在 Master 中打開一個連接也會使得 Master 開始一個線程。復制過程有一個很重要的限制---復制在 Slave 上是串行化的，也就是說 Master 上的并行更新操作不能在 Slave 上并行操作。

5. 復制常用類型

5.1 復制的常用體系結構基本原則

每個 Slave 只能有一個 Master;

每個 Slave 只能有一個唯一的服務器ID;

每個 Master 可以有很多 Slave;

如果你設置了 log_slave_updates，Slave 可以是其他 Slave 的 Master，從而擴散 Master 的更新

MySQL 不支持多主服務器復制---即一個 Slave 可以有多個 Master，但是，通過一些簡單的組合，我們卻可以建立靈活而強大的復制體系結構。

5.2 一主多從復制架構

場景：在主庫讀取請求壓力非常大的場景下，可以通過配置一主多從復制架構實現讀寫分離，把大量對實時性要求不是特別高的讀請求通過負載均衡到多個從庫上，降低主庫的讀取壓力。在主庫出現異常宕機的情況下，可以把一個從庫切換為主庫繼續提供服務;

建議：

當 Slave 增加到一定數量時，Slave 對 Master 的負載以及網絡帶寬都會成為一個嚴重的問題。

不同的 Slave 扮演不同的作用(例如使用不同的索引，或者不同的存儲引擎)

用一個 Slave 作為備用 Master，只進行復制

用一個遠程的 Slave，用于災難恢復。

5.3 多級復制架構

場景：一主多從的架構能夠解決大部分讀請求壓力特別大的場景需求，但主庫的I/O壓力和網絡壓力會隨著從庫的增加而增長，而使用多級復制架構就可以解決一主多從場景下，主庫額外的I/O和網絡壓力。 但要注意的是，多級復制場景下主庫的數據是經歷兩次才到達讀取的從庫，期間的延時比一主多從復制場景下只經歷一次復制的要大。

建議：

可能存在延時較長的風險

這種方案可以與第三方軟件結合使用，例如Slave+LVS+Keepalived 實現高可用。

5.4 雙主復制/Dual Master架構

場景：雙主/Dual Master架構適用于寫壓力比較大的場景，或者DBA做維護需要主從切換的場景，通過雙主/Dual master架構避免了重復搭建從庫的麻煩。

建議：

最大問題就是更新沖突。

可以采用MySQL Cluster，以及將Cluster和Replication結合起來，可以建立強大的高性能的數據庫平臺。

二、mysql主從配置

1.基礎環境配置

數據庫版本： mysql 5.5.48 ( Slave 版本可以大于或者等于 Maste r版本)

操作系統： CentOS 6.7 x86_64 mininal

IP地址：192.168.5.5 ( Master ) 192.168.5.9 ( Slave )

2.創建復制賬號

在 Master 的數據庫中建立一個復制賬戶，每個 Slave 使用該賬戶連接 Master 進行復制，需要 replication slave 和replication client 權限，Master 的連接信息會存儲在文本文件 master.info 文件中。(master.info文件在 Slave 的數據目錄中)

mysql>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.5.9' IDENTIFIED BY '211212';

mysql>flush privileges;

說明：創建了一個用戶名為 replication 的用戶，密碼為 211212,只允許在 192.168.5.9 這個 Slave 上登錄。

3.拷貝數據

如果是新安裝的 MySQL 主從服務器，這一步不需要。如果 Master 之前有數據，需要把 Master 的數據導出并還原到 Slave 上，要保證 Master 和 Slave 服務器中的數據是一樣的。建議導出 Master 庫的時候進行鎖表，導完后再解鎖。

#在mysql-master上執行

[root@master ~]# mysql -u root -p211212

mysql>flush tables with read lock;

[root@master ~]# mysqldump --all-databases -uroot -p211212 > all_databases.sql

[root@master ~]# mysql -uroot -p211212

mysql> unlock tables;

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | vdevops | | wh-test | +--------------------+ 5 rows in set (0.00 sec)

[root@master ~]# scp all_databases.sql root@192.168.5.9:/root

#在mysql-slave 執行

[root@minion ~]# mysql -uroot -p211212 -hlocalhost -P3306 --default-character-set=utf8 < all_databases.sql

或者 mysql -uroot -p211212

mysql>source /root/all_databases.sql;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| vdevops |

| wh-test |

+--------------------+

5 rows in set (0.04 sec)

設置主從同步前，保證master和slave的數據一致。

4.Master 服務器配置:

修改配置都需要修改 MySQL 的數據庫配置文件，默認是 /etc/my.cnf

4.1 開啟二進制日志

log-bin=mysql-bin 改成log-bin=/var/log/mysql/master-bin

增加 binlog_format=mixed

server-id = 1

expire_logs_days = 10

binlog-do-db = vdevops #需要記錄進制日志的數據庫.如果有多個數據庫可用逗號分隔,或者使用多個binlog-do-db選項

binlog-ignore-db = mysql #不需要記錄進制日志的數據庫.如果有多個數據庫可用逗號分隔,或者使用多個binlog-do-db選項

replicate-ignore-db = mysql,information_schema #不需要同步的數據庫.如果有多個數據庫可用逗號分隔,或者使用多個replicate-ignore-db選項

slave-skip-errors = all #過濾掉一些沒啥大問題的錯誤

4.2 取消 server-id 的注釋

將 server-id=1 前面的注釋去掉，如果沒有，則手動添加到 mysqld 下面。

必須要配置 server-id ,server-id 的值不能和 Slave 是一樣的。

4.3 設置需要同步的數據庫

在 [mysqld] 下面添加 binlog-do-db=vdevops

說明：binlog-ignore-db=test 表示不同步 test 數據庫，如果有多個數據庫，需要多寫幾行。

4.4 創建二進制日志目錄，授權，重啟 MySQL

#mkdir -p /data/mysql/log

chown mysql.mysql -R /data/mysql/log

[root@master mysql]# /etc/init.d/mysql restart

4.5 查看 Master 狀態

mysql > show master status;

記錄下 File 和 Postition，下面配置 Slave 的時候需要用。

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000001 | 107 | vdevops | mysql |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

#File master-bin.000001

#Position 107

5.Slave 服務器配置

修改配置都需要修改 MySQL 的數據庫配置文件，默認是 /etc/my.cnf

5.1 修改 server-id 和開啟中繼日志

將 server-id=1 改成 server-id=2

relay-log=/data/mysql/log/relay-log

5.2 創建中繼日志，授權以及重啟

mkdir -p /data/mysql/log

chown mysql.mysql -R /data/mysql/log

/etc/init.d/mysql restart

5.3 查看中繼日志啟動狀態

mysql>show global variables like '%relay%';

5.4 連接 Master 服務器

mysql>change master to master_host='192.168.5.5',master_user='replication',master_password='211212',master_log_file='master-bin.000001',master_log_pos=107;

選項：

master_host：Master 服務器IP

master_user：Master 服務器授權用戶，也就是 Master 前面創建的那個用戶

master_password：Master 服務器授權用戶對應的密碼

master_log_file：Master binlog 文件名

master_log_pos：Master binlog 文件中的 Postion 值

更多的選項可以看:http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

說明：使用剛剛在 Master 創建的用戶連接，log_file 和 log_pos 就是使用剛剛在 Master 上執行 show master status; 執行出來的結果中的File和Position

5.5 手動啟動復制線程

mysql> start slave;

5.6 查看主從狀態

mysql>show slave status \G;

說明：如果 Last_SQL_Error 沒有錯誤提示以及 Salve 中的 Exec_Master_Log_Pos 值和 Master 中的 show master status; 中的 Postition 值是一樣的，這樣的話，MySQL 主從復制應該是成功的。

# Slave_IO_Running: Connecting

# Slave_SQL_Running: Yes

如果出現：Connecting可能是File值改變導致的，因為在master上面修改配置文件重啟之后，shou master status;master bin-log參數初始值已經改變。 解決：

1、網絡不通

2、密碼不對

3、pos不對

4、主機防火墻未關閉 #本人這個次錯誤就是這個原因，有點傻 .登錄master，先把master鎖表，flsuh tables with read lock;然后show master status;得到最新的File文件值和Position值，然后登錄slave，stop slave;重新連接master： change master to master_host='192.168.5.5',master_user='replication',master_password='211212',master_log_file='master-bin.000005',master_log_pos=107; 切換master，unlock tables; #service iptables stop #到此，登錄slave，show slave status \G

能看到SLAVE_IO和SLAVE_SQL兩個進程已經成功運行。

6. 測試

在 Master 數據庫中執行sql語句操作，觀察 Slave 是否同步，如果同步則說明配置成功。

登錄master：

mysql -u root -p211212

mysql>use vdevops;

mysql>create table master_test(id int(4) not null,type char(20) not null);

mysql> insert into master_test values (001,'vdevops');

登錄slave，查看數據是否已經同步

mysql -u root -p211212

mysql>use vdevops;

mysql> select * from master_test;

+----+---------+

| id | type |

+----+---------+

| 1 | vdevops |

+----+---------+

1 row in set (0.00 sec)

#可以進行刪表操作查看主從同步變化

7. 注意事項

主庫和從庫的數據庫名必須相同;

主庫和從庫的復制可以精確到表，但是在需要更改主庫或從庫的數據結構時需要立刻重啟slave;

不能在mysql配置文件里直接寫入master的配置信息，需要用change master命令來完成;

指定replicate_do_db必須在my.cnf里配置，不能用change master命令來完成;

如果不及時清理，日積月累二進制日志文件可能會把磁盤空間占滿，可以在配置文件里加上expire_logs_days=7，只保留最近7天的日志，建議當slave不再使用時，通過reset slave來取消relaylog;

寫一個監控腳本，用來監控 Slave 中的兩個"yes"，如果只有一個"yes"或者零個，就表明主從有問題。

三、MySQL 主從一致性檢查

1.介紹

主從一致性主要是通過 Percona-Toolkit 這個工具來實現的，Percona Toolkit 是一組高級的命令行工具，用來管理 MySQL 和系統任務，主要功能包括:

驗證主節點和復制數據的一致性

有效的對記錄進行歸檔

找出重復的索引

總結 MySQL 服務器

從日志和 tcpdump 中分析查詢

問題發生時收集重要的系統信息。

現在，使用這個工具來完成一致性檢查和數據同步。

官網是:https://www.percona.com/software/mysql-tools/percona-toolkit

2.安裝 Percona-Toolkit 工具

安裝的方式有多種，可以編譯安裝，也可以通過包管理器安裝，為了方便，直接下載 rpm 包，然后yum進行安裝。

文件下載地址：https://www.percona.com/doc/percona-toolkit/2.2/installation.html

wget percona.com/get/percona-toolkit.rpm

yum install percona-toolkit.rpm -y

或者

wget percona.com/get/percona-toolkit.tar.gz

tar zxvf percona-toolkit.tar.gz && cd percona-toolkit

3.一致性檢查

3.1 注意點：

需要創建一個既能登錄主庫，也能登錄從庫，而且還能同步數據庫的帳號。

只能指定一個host,必須為主庫的IP

在檢查時會向表加S鎖

運行之前需要從庫的同步IO和SQL進程是YES狀態

3.2 創建檢查帳號

mysql>grant select,process,super,replication slave on *.* to 'mysql_check'@'x.x.x.x' identified by '123456';

mysql>flush privileges;

3.3 執行檢測(在master上操作)

pt-table-checksum --nocheck-replication-filters --databases=dy_qqopen --replicate=test.checksums --create-replicate-table --host=192.168.5.5 --port 3306 -umysql_check -pLooking_s123 --recursion-method=hosts

參數說明：

--nocheck-replication： 不檢查復制過濾器，建議啟動。后面可以用--databases來指定需要檢查的數據庫。

--databases：指定需要被檢查的數據庫，多個則用逗號隔開

--tables：指定需要被檢查的標，多個用逗號隔開。

--replicate：把checksum的信息寫入到指定表中，建議直接寫到被檢查的數據庫當中。

--create-replicate-table：創建存放檢查信息的數據表，第一次運行需要加，以后就不需要添加了。

--recursion-method：查找 Slave 的方式，有三種, processlist, dsn, hosts

h：Master地址

u：mysql的檢測用戶的用戶名

p：mysql檢測用戶的密碼

P：3306,mysql的端口號

執行結果：

參數說明：

TS: 完成檢查的時間

ERRORS：檢查時發生錯誤和警告的數量

DIFFS：0 表示一致，1 表示不一致，當指定 --no-replicate-check 時，會一直為0，當指定 --replicate-check-onley 則會顯示不同的信息

ROWS：表的行數。

CHUNKS：被劃分到表中的塊的數目

SKIPPED：由于錯誤或警告過大，則跳過塊的數目。

TIME：執行的時間。

TABLES：被檢查的表名

說明：如果出現不一致的情況，就需要進行修復了。

3.4 命令錯誤

問題1： 執行命令時提示 "Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information."錯誤

解決方法： 在從服務器的 /etc/my.cnf 配置文件中添加兩行，report_host=192.168.5.9(slave_ip) report_port=3306(slave_port)。然后重啟 MySQL 服務

4. 不一致數據修復

4.1 介紹

使用 pt-table-sync 這個命令來修復。 pt-table-sync 命令介紹：高效的同步 MySQL 表之間的數據，他可以做單向和雙向的表數據。也可以同步單個表，也可以同步整個庫。它不同步表結構、索引、或任何其他模式對象。所以在修復一致性之前需要保證他們表存在。

4.2 修復不一致的數據

pt-table-sync --print --user=mysql_check --password=Looking_s123 h=192.168.5.5,D=dy_qqopen,t=qq_task h=192.168.5.9

參數說明：

--print：打印出修復語句，如果需要修復，將 --print 改成 --execute，會直接執行修復語句，但是建議先使用 print 看一下修復語句，以保證不會出錯。

--user：MySQL 檢測用戶的用戶名

--password：MySQL 檢測用戶的密碼

D：目標數據庫

t：數據表

h：兩個h,第一次出現的是 Master 的 IP ,第二次出現的是 Slave 的地址。

四、MySQL 主從錯誤處理

1. 說明：

解決和處理主從錯誤這個是最重要的，比配置更更要。提高處理問題的能力，要熟悉原理，多處理積累，多學習其他網友的處理方式。出現錯誤都會在 Last_SQL_Error 中顯示錯誤，一般根據錯誤提示進行處理，如果不太清楚，可以谷歌查詢一下，不過操作完之后，同步正常后，一定要核對一下數據是否一致。