CentOS下基於Amoeba實現MySQL讀寫分離

CentOS下基於Amoeba實現MySQL讀寫分離

說明：本配置基於CentOS 6.4_x86，兩台MySQL伺服器均為源碼編譯（5.6.24版本），amoeba代理為2.2.0版本

server	use	ip
master	mysql主	192.168.0.172
slave	mysql從	192.168.0.173
amoeba	將使用者請求代理至mysqlserver	192.168.0.176

Linux下MySQL主從複製（Master-Slave）與讀寫分離（Amoeba）實踐

使用Amoeba 實現MySQL DB 讀寫分離 

用Amoeba實現MySQL的讀寫分離 

CentOS系統 Amoeba+MySL主從讀寫分離配置教程 

一、mysql伺服器基於GTID主從複製的實現
1、配置主從節點的服務組態檔
master節點：

[root@master ~]# cat /etc/my.cnf |grep "^\s*[^#\t]*s"[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES basedir = /usr/local/mysqllog-bin=master-binlog-slave-updates=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=1socket=/tmp/mysql.sock

slave節點：

[root@slave data]# cat /etc/my.cnf |grep "^\s*[^#\t]*s"[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES basedir = /usr/local/mysqllog-slave-updates=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=11log-bin=mysql-bin.logsocket=/tmp/mysql.sock

2、master建立複製使用者

mysql> grant replication slave on *.* to dbsync@192.168.0.173 identified by 'syncpass';mysql> show global variables like '%uuid%'\G*************************** 1. row ***************************Variable_name: server_uuid        Value: 9652c294-25d4-11e6-898b-000c2919c9d0mysql> show master status\G*************************** 1. row ***************************             File: master-bin.000001         Position: 151     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

3、slave啟動複製線程

mysql>  change master to master_host='192.168.0.172',master_user='dbsync',master_password='syncpass',master_auto_position=1;mysql> show global variables like '%uuid%'\G*************************** 1. row ***************************Variable_name: server_uuid        Value: 997046fa-5b8e-11e6-a7e2-000c2919c9d0mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.0.172                  Master_User: dbsync                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000001          Read_Master_Log_Pos: 151               Relay_Log_File: slave-relay-bin.000002                Relay_Log_Pos: 363        Relay_Master_Log_File: master-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              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: 151              Relay_Log_Space: 567              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: 0Master_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: 1                  Master_UUID: 9652c294-25d4-11e6-898b-000c2919c9d0             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 1

3、建立一個測試書庫庫查看MySQLmaster進程，顯示已經發送二進位日誌給salve

mysql> create database reliacatedb;mysql> show processlist\G*************************** 1. row ***************************     Id: 1   User: root   Host: localhost     db: NULLCommand: Query   Time: 0  State: init   Info: show processlist*************************** 2. row ***************************     Id: 3   User: dbsync   Host: slave:33608     db: NULLCommand: Binlog Dump GTID   Time: 259  State: Master has sent all binlog to slave; waiting for binlog to be updated   Info: NULL

二、amoeba節點安裝配置
1、配置java環境

# yum install -y java-1.6.0-openjdk# vim /etc/profile.d/jdk.sh export JAVA_HOME=/usr/export PATH=$PATH:$JAVA_HOME/bin# source /etc/profile.d/jdk.sh 

2、安裝ameoba
https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/

# wget https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/download# mkdir !$mkdir /usr/local/amoeba-2.2.0# tar xvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba-2.2.0

3、修改ameoba設定檔
amoeba前端訪問配置

# vim /usr/local/amoeba-2.2.0/conf/amoeba.xml <property name="port">3306</property> # 將預設連接埠8066改為3306，便於實現前端程式串連資料庫的透明性 <property name="ipAddress">0.0.0.0</property> # 有多塊網卡時可按此設定，表示綁定任意地址，即amoeba對外訪問的IP地址 <property name="user">root</property> # 用戶端串連amoeba的代理使用的使用者  <property name="password">mypass</property> # 用戶端串連amoeba的代理使用的密碼 <property name="defaultPool">master</property> # 預設訪問節點 <property name="writePool">master</property> <property name="readPool">slave</property> # 讀寫分離配置，讀池和寫池和dbServer.xml中配置的節點相關

4、amoeba後端代理配置

# vim /usr/local/amoeba-2.2.0/conf/dbServers.xml<property name="user">root</property> # 預設串連mysql server的使用者<property name="password">pass</property> # 預設串連mysql server的密碼，以上兩項如不在下文中的dbserver中單獨定義，則直接繼承此處定義  <dbServer name="master"  parent="abstractServer">                <factoryConfig>  <property name="ipAddress">192.168.0.172</property>                </factoryConfig>  </dbServer>  <dbServer name="slave"  parent="abstractServer">                <factoryConfig>  <property name="ipAddress">192.168.0.173</property>                </factoryConfig>  </dbServer>  <dbServer name="multiPool" virtual="true"> #服務組，輪詢策略           <poolConfig   class="com.meidusa.amoeba.server.MultipleServerPool"><!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--> <property name="loadbalance">1</property>  <!-- Separated by commas,such as: server1,server2,server1 -->  <property name="poolNames">master,slave,slave,slave</property>                </poolConfig>        </dbServer>

5、環境變數配置

# vim /etc/profile.d/amoeba.sh export AMOEBA_HOME=/usr/local/amoeba-2.2.0/export PATH=$AMOEBA_HOME/bin/:$PATHsource /etc/profile.d/amoeba.sh

6、啟動amoeba

# amoeba startThe stack size specified is too small, Specify at least 160kCould not create the Java virtual machine.如果報以上錯誤，需編輯二進位指令碼# vim /usr/local/amoeba-2.2.0/bin/amoebaDEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"修改為如下DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

7、通過amoeba串連mysql

執行一些讀寫操作，在兩台mysql伺服器上使用tcpdump抓包

[root@master ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.0.172[root@slave ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.0.173

master上實現寫操作

slave上實現讀操作

本文永久更新連結地址：