Linux教程網 >> Linux基礎 >> 關於Linux >> 利用mycat實現基於mysql5.5主從復制的讀寫分離

利用mycat實現基於mysql5.5主從復制的讀寫分離

日期：2017/3/1 12:07:47 编辑：關於Linux

整體步驟：

1.准備好兩台服務器，一台作為主數據庫服務器，一台作為從服務器，並安裝好mysql數據庫，此處略
2.配置好主從同步
3.下載JDK配置mycat依賴的JAVA環境，mycat采用java語言開發運行依賴jre
4.配置mycat的相關文件
5.測試

一、配置mysql主從環境

MYSQL主從同步的作用
(1) 數據分布
(2) 負載平衡(load balancing)
(3) 備份
(4) 高可用性(high availability)和容錯
MYSQL主從同步的原理
關於MYSQL的主從同步，最主要的是要了解MYSQL的主從同步是如何工作的也即主從同步的原理，通過下圖能很明白的指導其工作的過程：

大致描述一下過程：從服務器的IO線程從主服務器獲取二進制日志，並在本地保存為中繼日志，然後通過SQL線程來在從上執行中繼日志中的內容，從而使從庫和主庫保持一致。主從同步的詳細過程如下：
1. 主服務器驗證連接。
2. 主服務器為從服務器開啟一個線程。
3. 從服務器將主服務器日志的偏移位告訴主服務器。
4. 主服務器檢查該值是否小於當前二進制日志偏移位。
5. 如果小於，則通知從服務器來取數據。
6. 從服務器持續從主服務器取數據，直至取完，這時，從服務器線程進入睡眠，主服務器線程同時進入睡眠。
7. 當主服務器有更新時，主服務器線程被激活，並將二進制日志推送給從服務器，並通知從服務器線程進入工作狀態。
8. 從服務器SQL線程執行二進制日志，隨後進入睡眠狀態。

MYSQL主從同步的搭建實戰
主從同步的搭建是一項比較細的技術活，前期做好了一些事情會讓你在以後的工作中減少很多工作，搭建的時候需要注意一些問題，一會搭建的時候會一邊搭建一邊介紹需要注意的問題，讓初學者能在剛開始的時候就有效的規避掉一些潛在的問題(MYSQL安裝這裡不做介紹)：
主從同步環境介紹
操作系統環境：Centos 6.5 64 bit
MYSQL版本：MYSQL 5.5.48
主服務器的IP：192.168.8.60
從服務器的IP：192.168.11.145

1.將服務器都安裝ntp服務，保持主從數據庫時間一致

8.102為時間服務器，手動同步一次
# yum install -y ntp
# ntpdate -u 192.168.8.102

2.備份8.60主數據庫

# service mysqld stop
# cp -r /data/mydata/TD_OA /home
將數據庫打包下載到本地傳送到輔助服務器上
# chown -R mysql.mysql /data/

3.在主服務器上建立同步帳號

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.11.145' IDENTIFIED BY 'pass';
mysql> FLUSH PRIVILEGES;
注意：大家在設置權限的時候不要將密碼設置過於簡單。

主服務器my.cnf配置參考：

# cat /etc/my.cnf | grep "^[^#]"
[client]
port		= 3306
socket		= /tmp/mysql.sock
[mysqld]
port		= 3306
default-storage-engine=MyISAM
max_connections=1500
socket		= /tmp/mysql.sock
skip-external-locking
skip-innodb
key_buffer_size = 4096M
max_allowed_packet = 64M
table_open_cache = 2400
open_files_limit=65535
tmp_table_size=256M
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 256
query_cache_type=0
query_cache_size= 512M
innodb_buffer_pool_size = 1024M
skip-name-resolve
wait_timeout=300
thread_concurrency = 8
innodb_buffer_pool_instances = 35
innodb_file_per_table = 1
datadir = /data/mydata
log-bin=/data/binlogs/mysql-bin
binlog-do-db=TD_OA
binlog-ignore-db = mysql
expire_logs_day=30
max_binlog_size = 200M
slow_query_log=ON
slow-query-log-file=/data/binlogs/slow_query.log
long_query_time=2
log_bin_trust_function_creators=1
binlog_format=mixed
server-id	= 60
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 512M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout

4、從服務器設置：

# mkdir /data/relaylog/
# chown -R mysql.mysql /data/relaylog
# vim /etc/my.cnf

server-id = 145 #配置serverid
relay-log = /data/relaylog/relay-bin #啟用中繼日志
replicate_wild_do_table=TD_OA.% #需要同步的數據庫表
replicate_wild_ignore_table=mysql.% #忽略的數據庫表
skip-slave-start #不自動啟動復制

從服務器配置參考：

# cat /etc/my.cnf | grep "^[^#]"
[client]
port		= 3306
socket		= /tmp/mysql.sock
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
default-storage-engine=MyISAM
max_connections=1000
open_files_limit=65535
innodb_file_per_table = 1
datadir = /data/mydata
skip-external-locking
skip-innodb
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
log-bin=/data/binlogs/mysql-bin
relay-log = /data/relaylog/relay-bin
log_bin_trust_function_creators=1
binlog_format=mixed
server-id	= 145
replicate_wild_do_table=TD_OA.%
replicate_wild_ignore_table=mysql.%
skip-slave-start
read-only=ON
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

注意：
1) server-id這一項需要認真檢查，一定不能和主服務器沖突了，不然到時候會出現莫民其妙的問題，因為同步的時候會會根據server-id做判斷，如果server-id一樣就不進行同步了，不然可能會導致死循環(主主同步或者環狀同步的時候)。
2) 有的人會感覺奇怪我這裡為什麼要使用replicate-wild-ignore-table參數，而不是用replicate-do-db或者replicate-ignore-db來過濾需要同步的數據庫和不需要同步的數據庫。這裡有幾個原因：
A. replicate-wild-ignore-table參數能同步所有跨數據庫的更新，比如replicate-do-db或者replicate-ignore-db不會同步類似
use mysql;
UPDATE test.aaa SET amount=amount+10;
B. replicate-wild-ignore-table=mysql.%在以後需要添加同步數據庫的時候能方便添加而不需要重新啟動從服務器的數據庫。因為以後很可能需要同步其他的數據庫。
如設置 replicate_do_db=test
use mysql;
update test.table1 set ......第二句將不會被執行
如設置 replicate_ignore_db=mysql
use mysql;
update test.table1 set ......
第二句會被忽略執行原因是設置replicate_do_db或replicate_ignore_db後，MySQL執行sql前檢查的是當前默認數據庫，所以跨庫更新語句被忽略。
可以使用replicate_wild_do_table和replicate_wild_ignore_table來代替
如
replicate_wild_do_table=test.%
或
replicate_wild_ignore_table=mysql.%
這樣就可以避免出現上述問題了
3) auto_increment_increment和auto_increment_offset參數，這兩個參數一般用在主主同步中，用來錯開自增值, 防止鍵值沖突。
4) --slave-skip-errors參數，不要胡亂使用這些跳過錯誤的參數，除非你非常確定你在做什麼。當你使用這些參數時候，MYSQL會忽略那些錯誤，這樣會導致你的主從服務器數據不一致。

MySQL主從復制幾個重要的啟動選項
　　(1)　 log-slave-updates
　　log-slave-updates這個參數用來配置從服務器的更新是否寫入二進制日志，這個選項默認是不打開的，但是，如果這個從服務器B是服務器A的從服務器，同時還作為服務器C的主服務器，那麼就需要開發這個選項，這樣它的從服務器C才能獲得它的二進制日志進行同步操作
　　(2)　 master-connect-retry
　　master-connect-retry這個參數是用來設置在和主服務器連接丟失的時候，重試的時間間隔，默認是60秒
　　(3)　 read-only
　　read-only是用來限制普通用戶對從數據庫的更新操作，以確保從數據庫的安全性，不過如果是超級用戶依然可以對從數據庫進行更新操作
　　(4)　 slave-skip-errors
　　在復制過程中，由於各種的原因，從服務器可能會遇到執行BINLOG中的SQL出錯的情況，在默認情況下，服務器會停止復制進程，不再進行同步，等到用戶自行來處理。
　　Slave-skip-errors的作用就是用來定義復制過程中從服務器可以自動跳過的錯誤號，當復制過程中遇到定義的錯誤號，就可以自動跳過，直接執行後面的SQL語句。
　　--slave-skip-errors=[err1,err2,…….|ALL]
　　但必須注意的是，啟動這個參數，如果處理不當，很可能造成主從數據庫的數據不同步，在應用中需要根據實際情況，如果對數據完整性要求不是很嚴格，那麼這個選項確實可以減輕維護的成本

重啟服務使之生效
# service mysqld restart

5.主服務器狀態

在主服務器執行查看狀態，記錄binlog日志的文件名及位置參數
mysql> show master status;

從服務器配置

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.60',MASTER_USER='repluser',MASTER_PASSWORD='replpass2014',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=691;
mysql> START SLAVE;


mysql> show slave status\G
*************************** 1. row ***************************
       Slave_IO_State: 
 Master_Host: 192.168.8.60
 Master_User: repluser
 Master_Port: 3306
Connect_Retry: 60
      Master_Log_File: mysql-bin.000006
  Read_Master_Log_Pos: 691
       Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000006
     Slave_IO_Running: No
    Slave_SQL_Running: No
      Replicate_Do_DB: 
  Replicate_Ignore_DB: 
   Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
  Last_Errno: 0
  Last_Error: 
Skip_Counter: 0
  Exec_Master_Log_Pos: 691
      Relay_Log_Space: 107
      Until_Condition: None
       Until_Log_File: 
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
      Master_SSL_Cert: 
    Master_SSL_Cipher: 
       Master_SSL_Key: 
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
       Last_SQL_Errno: 0
       Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
     Master_Server_Id: 6

遇到這樣的錯誤如：“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"等或由於清數據導致主從庫不同步了

解決辦法如下：
先進入slave中執行:"slave stop;"來停止從庫同步；
再去master中執行:"flush logs;"來清空日志；
然後在master中執行:"show master status;"查看下主庫的狀態，主要是日志的文件和position；
然後回到slave中，執行:"CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000006',MASTER_LOG_POS=107;"，文件和位置對應master中的;
最後在slave中執行:"slave start;"來啟動同步。

狀態OK：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.60
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 6022
Relay_Log_File: relay-bin.000005
Relay_Log_Pos: 3733
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: TD_OA.%
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 6022
Relay_Log_Space: 3883
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 60

如果備份的時候停止了從服務器，需要重新啟動從服務器的復制
mysql> slave start;

二、mycat的安裝及部署

1.下載mycat1.4版本

https://github.com/MyCATApache/Mycat-download
# tar xf Mycat-server-1.4-release-20151019230038-linux.tar.gz
# mv mycat /usr/local/

2.部署jdk環境

檢查現有版本JDK
# java -version

查找java相關文件並卸載
# rpm -qa | grep java
tzdata-java-2013g-1.el6.noarch
java-1.7.0-openjdk-1.7.0.45-2.4.3.3.el6.x86_64
libvirt-java-0.4.9-1.el6.noarch
libvirt-java-devel-0.4.9-1.el6.noarch
java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64


卸載openjdk1.7
# rpm -e --nodeps java-1.7.0-openjdk-1.7.0.45-2.4.3.3.el6.x86_64
 rpm -qa | grep java
tzdata-java-2013g-1.el6.noarch
libvirt-java-0.4.9-1.el6.noarch
libvirt-java-devel-0.4.9-1.el6.noarch
java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64
# java -version
java version "1.6.0_28"
OpenJDK Runtime Environment (IcedTea6 1.13.0pre) (rhel-1.66.1.13.0.el6-x86_64)
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)
卸載openjdk1.6
# rpm -e --nodeps java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64
# java -version
-bash: /usr/bin/java: No such file or directory


安裝jdk7
# rpm -ivh jdk-7u79-linux-x64.rpm

將java命令加入到環境變量中
vim /etc/profile
//添加內容
JAVA_HOME=/usr/java/jdk1.7.0_79
JRE_HOME=/usr/java/jdk1.7.0_79/jre
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
export    JAVA_HOME JRE_HOME PATH CLASSPATH

//使配置立即生效
# source /etc/profile
//查看PATH
# echo $PATH
/usr/local/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/java/jdk1.7.0_79/bin:/usr/java/jdk1.7.0_79/jre/bin

3、配置mycat中的jdk文件路徑

# cd /usr/local/mycat/conf/
# vim wrapper.conf
# Java Application
wrapper.java.command=/usr/java/jdk1.7.0_79/bin/java

4、讀寫分離配置(重點配置)：

# cp schema.xml schema.xml.bak
# vim schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
select user()

password="pass">

(1)
這裡的oldboy就是我們所宣稱的數據庫名稱，必須和server.xml中的用戶指定的數據庫名稱一致。添加一個dataNode="dn1"，是指定了我們這個庫只有在dn1上，沒有分庫。

(2)
這裡只需要改database的名字，TD_OA就是你真是的數據庫上的數據庫名，可根據自己的數據庫名稱修改。

(3) writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

需要配置的位置：
balance="1" writeType="0" switchType="1"

balance
1、balance=0 不開啟讀寫分離機制，所有讀操作都發送到當前可用的writehostle .

2、balance=1 全部的readhost與stand by writeHost 參與select語句的負載均衡。簡單的說，雙主雙從模式(M1àS1,M2àS2，並且M1和M2互為主備)，正常情況下，M1，S1，S2都參與select語句的復雜均衡。

3、balance=2 所有讀操作都隨機的在readhost和writehost上分發

writeType
負載均衡類型，目前的取值有3種：
1、writeType="0", 所有寫操作發送到配置的第一個writeHost。
2、writeType="1"，所有寫操作都隨機的發送到配置的writeHost。
3、writeType="2"，不執行寫操作。

switchType
1、switchType=-1 表示不自動切換
2、switchType=1 默認值，自動切換
3、switchType=2 基於MySQL 主從同步的狀態決定是否切換
(4)

注意：
這裡配置的是讀寫服務器的IP地址和端口訪問，訪問的用戶名和密碼；而且一定要先在客戶端測試OK了以後在進行配置。

5、將mycat命令加入到配置文件中

# vim /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
# source /etc/profile

6、創建管理用戶

主庫上對mycat用戶授權如下：

用戶：mycat 密碼：pass 端口：3306

權限：insert,delete,update,select

命令：grant insert,delete,update,select on TD_OA.* to mycat@'192.168.8.%' identified by 'pass';
flush privileges;

從庫上mycat_r用戶授權如下：
用戶：mycat_r 密碼：pass 端口：3306
權限： select

grant select on TD_OA.* to mycat_r@'192.168.8.%' identified by 'pass';
flush privileges;

提示：由於主庫和從庫是同步mysql數據庫復制的，所以從庫上的mycat用戶會自動和主庫的一致，即無法實現只讀select的授權
revoke insert,update,delete on TD_OA.* from mycat@'192.168.8.%'; #<=回收insert update delete 權限

修改mycat配置文件
vim /usr/local/mycat/conf/server.xml

pass
TD_OA

pass
TD_OA
true

注意：

①這裡配置的是可以連接主庫的兩個用戶

用戶：mycat 密碼：pass 給予此用戶TD_OA數據庫增刪改查的權限。

用戶：mycat_r 密碼：pass 給予此用戶TD_OA數據庫讀的權限。

②這裡的TD_OA，不一定是你數據庫上的真實庫名，可以任意指定，只要接下來和schema.xml的配置文件的庫名統一即可。

7、啟動mycat

# mycat console #<=通過console命令啟動mycat，這樣方便提取信息
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
wrapper | JVM exited while loading the application.
jvm 1 | Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: oawebserver002: oawebserver002: Temporary failure in name resolution
解決：編輯Hosts文件將主機名和IP綁定
# vim /etc/hosts
192.168.8.60 oawebserver002
# mycat restart使配置生效

在客戶端連接mysql主庫服務器：

# yum install -y mysql
# mysql -umycat -ppass -h192.168.8.60 -P8066