MySql之主從複製及讀寫分離

標籤：mysql replication ; linux

前言

使用MySQL Proxy和MySQL Replication實現讀寫分離

MySQL Replication可以將master的資料複製分布到多個slave上，然後可以利用slave來分擔master的讀壓力。那麼對於前台應用來說，就要考慮如何將讀的壓力分布到多個slave上。如果每個應用都需要來實現讀寫分離的演算法，一則成本太高，二來如果slave增加更多的機器，應用就要隨之修改。明顯的，如果在應用和資料庫間加一個專門用於實現讀寫分離的中介層，則整個系統的架構擁有更好的擴充性。MySQL Proxy就是這麼一個中介層代理，簡單的說，MySQL Proxy就是一個串連池，負責將前台應用的串連請求轉寄給背景資料庫，並且通過使用lua指令碼，可以實現複雜的串連控制和過濾，從而實現讀寫分離和Server Load Balancer。對於應用來說，MySQL Proxy是完全透明的，應用則只需要串連到MySQL Proxy的監聽連接埠即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗餘，在應用伺服器的串連池配置中配置到多個proxy的串連參數即可。

MySQL 5.6引入的GTID(Global Transaction IDs)使得其複製功能的配置、監控及管理變得更加易於實現，且更加健壯。

要在MySQL 5.6中使用複製功能，其服務配置段[mysqld]中於少應該定義如下選項：

binlog-format：二進位日誌的格式，有row、statement和mixed幾種類型；

需要注意的是：當設定隔離等級為READ-COMMITED必須設定二進位日誌格式為ROW，現在MySQL官方認為STATEMENT這個已經不再適合繼續使用；但mixed類型在預設的交易隔離等級下，可能會導致主從資料不一致；

log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用於啟動GTID及滿足附屬的其它需求；

master-info-repository和relay-log-info-repository：啟用此兩項，可用於實現在崩潰時保證二進位及從伺服器安全的功能；

sync-master-info：啟用之可確保無資訊丟失；

slave-paralles-workers：設定從伺服器的SQL線程數；0表示關閉多線程複製功能；

binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：啟用複製有關的所有校正功能；

binlog-rows-query-log-events：啟用之可用於在二進位日誌記錄事件相關的資訊，可降低故障排除的複雜度；

log-bin：啟用二進位日誌，這是保證複製功能的基本前提；

server-id：同一個複寫拓撲中的所有伺服器的id號必須惟一；

report-host：

The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

report-port:

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

master-info-repository:

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

relay-log-info-repository：

This option causes the server to log its relay log info to a file or a table.

log_slave_updates：

Whether updates received by a slave server from a master server should be logged to the slave‘s own binary log. Binary logging must be enabled on the slave for this variable to have any effect. 

enforce_gtid_consistency：

一、簡單主從模式配置步驟

1、配置主從節點的服務組態檔

1.1、配置master節點：

[mysqld]binlog-format=ROWlog-bin=master-binlog-slave-updates=truegtid-mode=on enforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=1report-port=3306port=3306datadir=/mydata/datasocket=/tmp/mysql.sockreport-host=master.xxx.com

1.2、配置slave節點

[mysqld]binlog-format=ROWlog-slave-updates=truegtid-mode=on enforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=11report-port=3306port=3306log-bin=mysql-bin.logdatadir=/mydata/datasocket=/tmp/mysql.sockreport-host=slave.xxx.com

2、建立複製使用者

mysql>GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘replpass‘;

說明：172.16.100.7是從節點伺服器；如果想一次性授權更多節點，可以自行根據需要修改；

3、為備節點提供初始資料集

鎖定主表，備份主節點上的資料，將其還原至從節點；如果沒有啟用GTID，在備份時需要在master使用show master status命令查看二進位記錄檔名稱及事件位置，以便後面啟動slave節點時使用。

4、啟動從節點的複製線程

如果啟用了GTID功能，則使用如下命令：

mysql> CHANGE MASTER TO MASTER_HOST=‘master.xxx.com‘, MASTER_USER=‘repluser‘, MASTER_PASSWORD=‘replpass‘, MASTER_AUTO_POSITION=1;

沒啟用GTID，需要使用如下命令：

slave> CHANGE MASTER TO MASTER_HOST=‘172.16.100.6‘,-> MASTER_USER=‘repluser‘,-> MASTER_PASSWORD=‘replpass‘,-> MASTER_LOG_FILE=‘master-bin.000003‘,-> MASTER_LOG_POS=1174;

二、半同步複製

1、分別在主從節點上安裝相關的外掛程式

master> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;

2、啟用半同步複製

在master上的設定檔中，添加

rpl_semi_sync_master_enabled=ON

在至少一個slave節點的設定檔中添加

rpl_semi_sync_slave_enabled=ON

而後重新啟動mysql服務即可生效。

或者，也可以mysql服務上動態啟動其相關功能：

master> SET GLOBAL rpl_semi_sync_master_enabled = ON;slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

3、確認半同步功能已經啟用

master> CREATE DATABASE magedudb;master> SHOW STATUS LIKE ‘Rpl_semi_sync_master_yes_tx‘;slave> SHOW DATABASES;

MySql之主從複製及讀寫分離