MySQL5.6主從復制搭建基于日志（binlog）

什么是MySQL主從復制

簡單來說，就是保證主SQL（Master）和從SQL（Slave）的數據是一致性的，向Master插入數據后，Slave會自動從Master把修改的數據同步過來（有一定的延遲），通過這種方式來保證數據的一致性，就是主從復制。

MySQL主從能解決什么問題

一、高可用

因為數據都是相同的，所以當Master掛掉后，可以指定一臺Slave充當Master繼續保證服務運行，因為數據是一致性的（如果當插入Master就掛掉，可能不一致，因為同步也需要時間），當然這種配置不是簡單的把一臺Slave充當Master，畢竟還要考慮后續的Salve同步Master，當然本文并不是將高可用的配置，所以這里就不多講了。

二、負載均衡

因為讀寫分離也算是負載均衡的一種，所以就不單獨寫了，因為一般都是有多臺Slave的，所以可以將讀操作指定到Slave服務器上（需要代碼控制），然后再用負載均衡來選擇那臺Slave來提供服務，同時也可以吧一些大量計算的查詢指定到某臺Slave，這樣就不會影響Master的寫入以及其他查詢.

三、數據備份

一般我們都會做數據備份，可能是寫定時任務，一些特殊行業可能還需要手動備份，有些行業要求備份和原數據不能在同一個地方，所以主從就能很好的解決這個問題，不僅備份及時，而且還可以多地備份，保證數據的安全

四、業務模塊化

可以一個業務模塊讀取一個Slave，再針對不同的業務場景進行數據庫的索引創建和根據業務選擇MySQL存儲引擎

五、高擴展（硬件擴展）

主從復制支持2種擴展方式

1、scale-up

向上擴展或者縱向擴展，主要是提供比現在服務器更好性能的服務器，比如增加CPU和內存以及磁盤陣列等，因為有多臺服務器，所以可擴展性比單臺更大

2、scale-out

向外擴展或者橫向擴展，是指增加服務器數量的擴展，這樣主要能分散各個服務器的壓力

主從復制的缺點

一、成本增加

無可厚非的是搭建主從肯定會增加成本，畢竟一臺服務器和兩臺服務器的成本完全不同，另外由于主從必須要開啟二進制日志，所以也會造成額外的性能消耗

二、數據延遲

Slave從Master復制過來肯定是會有一定的數據延遲的，所以當剛插入就出現查詢的情況，可能查詢不出來，當然如果是插入者自己查詢，那么可以直接從Master中查詢出來，當然這個也是需要用代碼來控制的

三、寫入更慢

主從復制主要是針對讀遠大于寫或者對數據備份實時性要求較高的系統中，因為Master在寫中需要更多操作，而且只有一臺寫入的Master（因為我目前只會配置一臺寫入Master，最多就是有從Master的Slave，用來在Master掛掉后替換成Master，平時不對外進行服務），所以寫入的壓力并不能被分散，當然如果直接怎么解決這個問題的話，歡迎留言指教

復制方式

MySQL5.6開始主從復制有兩種方式：基于日志（binlog）、基于GTID（全局事務標示符）。

本文只涉及基于日志binlog的主從配置

復制原理

1、Master將數據改變記錄到二進制日志(binary log)中，也就是配置文件log-bin指定的文件，這些記錄叫做二進制日志事件(binary log events)

2、Slave通過I/O線程讀取Master中的binary log events并寫入到它的中繼日志(relay log)

3、Slave重做中繼日志中的事件，把中繼日志中的事件信息一條一條的在本地執行一次，完成數據在本地的存儲，從而實現將改變反映到它自己的數據(數據重放)

要求

1、主從服務器操作系統版本和位數一致

2、Master和Slave數據庫的版本要一致

3、Master和Slave數據庫中的數據要一致

4、Master開啟二進制日志，Master和Slave的server_id在局域網內必須唯一

具體配置

硬件需求

臺或兩臺以上安裝了相同版本的MySQL

master 192.168.100.70

salve ? ?192.168.100.71

配置Master

一、安裝數據庫

二、配置my.cnf

不同的系統my.cnf路徑不同，所以我們只講解牽扯修改的地方。添加配置

[mysqld]
#設置server_id，一般設置為IP,注意要唯一
server_id=100  
#復制過濾：也就是指定哪個數據庫不用同步（mysql庫一般不同步）
binlog-ignore-db=mysql  
#開啟二進制日志功能，可以隨便取，最好有含義（關鍵就是這里了）
log-bin=edu-mysql-bin  
#為每個session 分配的內存，在事務過程中用來存儲二進制日志的緩存
binlog_cache_size=1M  
#從復制的格式（mixed,statement,row，默認格式是statement）
binlog_format=mixed  
#二進制日志自動刪除/過期的天數。默認值為0，表示不自動刪除。
expire_logs_days=7  
#跳過主從復制中遇到的所有錯誤或指定類型的錯誤，避免slave端復制中斷。
#如：1062錯誤是指一些主鍵重復，1032錯誤是因為主從數據庫數據不一致
slave_skip_errors=1062

配置完成后重啟mysql

關于復制過濾:
復制過濾可以讓你只復制服務器中的一部分數據，有兩種復制過濾：
1、在Master上過濾二進制日志中的事件
2、在Slave上過濾中繼日志中的事件。
復制類型:
1、基于語句的復制
在Master上執行的SQL語句，在Slave上執行同樣的語句。MySQL默認采用基于語句的復制，效率比較高。一旦發現沒法精確復制時，會自動選著基于行的復制
2、基于行的復制
把改變的內容復制到Slave，而不是把命令在Slave上執行一遍。從MySQL5.0開始支持
3、混合類型的復制
默認采用基于語句的復制，一旦發現基于語句的無法精確的復制時，就會采用基于行的復制

三、創建數據同步用戶

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

這里主要是要授予用戶REPLICATION SLAVE權限和REPLICATION CLIENT權限

配置Slave

一、安裝數據庫

二、配置my.cnf

[mysqld]
#設置server_id，一般設置為IP,注意要唯一
server_id=101  
#復制過濾：也就是指定哪個數據庫不用同步（mysql庫一般不同步）
binlog-ignore-db=mysql  
#開啟二進制日志功能，以備Slave作為其它Slave的Master時使用
log-bin=edu-mysql-slave1-bin  
#為每個session 分配的內存，在事務過程中用來存儲二進制日志的緩存
binlog_cache_size=1M  
#主從復制的格式（mixed,statement,row，默認格式是statement）
binlog_format=mixed  
#二進制日志自動刪除/過期的天數。默認值為0，表示不自動刪除。
expire_logs_days=7  
#跳過主從復制中遇到的所有錯誤或指定類型的錯誤，避免slave端復制中斷。
#如：1062錯誤是指一些主鍵重復，1032錯誤是因為主從數據庫數據不一致
slave_skip_errors=1062  
#relay_log配置中繼日志
relay_log=edu-mysql-relay-bin  
#log_slave_updates表示slave將復制事件寫進自己的二進制日志
log_slave_updates=1  
#防止改變數據(除了特殊的線程)
read_only=1

如果Slave為其它Slave的Master時，必須設置bin_log.配置完成后重啟mysql.

完成Master和Slave鏈接

一、初始化數據
保證Master和Slave除不同步的數據庫，其他庫的數據一致
二、查詢Master狀態
在Master中執行

mysql> show master status\G
*************************** 1. row ***************************File: edu-mysql-bin.000001Position: 120Binlog_Do_DB: Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

記錄下返回結果的File列和Position列的值

三、Slave中設置Master信息

在Slave中執行

change master to master_host='192.168.100.70', master_user='slave', master_password='123456', master_port=3306, \
master_log_file='edu-mysql-bin.000001', master_log_pos=120, master_connect_retry=30;#命令解釋:
#Master的IP地址
master_host=’192.168.100.70′ #用于同步數據的用戶（在Master中授權的用戶）
master_user=’slave’ #同步數據用戶的密碼
master_password=’123456′ #Master數據庫服務的端口
master_port=3306 #指定Slave從哪個日志文件開始讀復制數據（Master上執行命令的結果的File字段）
master_log_file=’edu-mysql-bin.000001′ #從哪個POSITION號開始讀（Master上執行命令的結果的Position字段）
master_log_pos=120#當重新建立主從連接時，如果連接建立失敗，間隔多久后重試。單位為秒，默認設置為60秒，同步延遲調優參數。
master_connect_retry=30

四、查看主從同步狀態

在Slave中執行命令