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）实践 http://www.linuxidc.com/Linux/2016-05/130905.htm使用Amoeba 实现MySQL DB 读写分离 http://www.linuxidc.com/Linux/2015-02/113542.htm用Amoeba实现MySQL的读写分离 http://www.linuxidc.com/Linux/2013-12/94235.htmCentOS系统 Amoeba+MySL主从读写分离配置教程 http://www.linuxidc.com/Linux/2015-10/124115.htm一、mysql服务器基于GTID主从复制的实现
1、配置主从节点的服务配置文件
master节点：

[root@master ~]# cat /etc/my.cnf |grep "^s*[^#	]*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*[^#	]*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_uuidValue: 9652c294-25d4-11e6-898b-000c2919c9d0mysql> show master statusG*************************** 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_uuidValue: 997046fa-5b8e-11e6-a7e2-000c2919c9d0mysql> start slave;mysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.172Master_User: dbsyncMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_Log_Pos: 151 Relay_Log_File: slave-relay-bin.000002Relay_Log_Pos: 363Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_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: 0Exec_Master_Log_Pos: 151Relay_Log_Space: 567Until_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 9652c294-25d4-11e6-898b-000c2919c9d0 Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400Master_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 processlistG*************************** 1. row *************************** Id: 1 User: root Host: localhost db: NULLCommand: Query Time: 0State: init Info: show processlist*************************** 2. row *************************** Id: 3 User: dbsync Host: slave:33608 db: NULLCommand: Binlog Dump GTID Time: 259State: 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上实现读操作
本文永久更新链接地址