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 |
一、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/mysql log-bin=master-bin log-slave-updates=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=1 socket=/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/mysql log-slave-updates=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 server-id=11 log-bin=mysql-bin.log socket=/tmp/mysql.sock
2、master创建复制用户
mysql> grant replication slave on *.* to [email protected] identified by 'syncpass'; mysql> show global variables like '%uuid%'\G *************************** 1. row *************************** Variable_name: server_uuid Value: 9652c294-25d4-11e6-898b-000c2919c9d0 mysql> 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-000c2919c9d0
mysql> 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: 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: 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: 13、创建一个测试书库库查看MySQLmaster进程，显示已经发送二进制日志给salve
mysql> create database reliacatedb;
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 3
User: dbsync
Host: slave:33608
db: NULL
Command: 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/:$PATH source /etc/profile.d/amoeba.sh
6、启动amoeba
# amoeba start The stack size specified is too small, Specify at least 160k Could not create the Java virtual machine. 如果报以上错误，需编辑二进制脚本 # vim /usr/local/amoeba-2.2.0/bin/amoeba DEFAULT_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上实现读操作 
相关推荐
IBMRational 2020-01-28
vitasfly 2016-05-22
lifeison 2019-05-08
lglovejava 2016-10-12
mysql00 2014-08-02
cnzou 2014-08-02
kuwoyinlehe 2014-07-25
小树不倒我不倒 2013-11-28
frank0 2008-10-14
wensonlee 2018-03-30
debugjoker 2018-01-03
tufeiax 2017-08-13
zhangjunguo00 2016-10-17
xkorey 2016-05-01
lapiggy 2015-02-16
lulujaylulu 2014-01-14