amoeba搭建及读写分离测试-白红宇

amoeba搭建及读写分离测试

阅读量：6819 次

发布时间：2019-06-26

本文共 14868 字，大约阅读时间需要 49 分钟。

mysql主库服务器--192.168.107.16

[root@master ~]# cat /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin

log=/tmp/mysql.log

character-set-server=utf8

mysqladmin -u root -p 123456

mysql -uroot root -p123456

grant all on *.* to test_user@'%' identified by '1234';

mysql从库服务器--192.168.107.17

[root@slave1 ~]# cat /etc/my.cnf

[mysqld]

server-id=2

log-bin=mysql-bin

log=/tmp/mysql.log

character-set-server=utf8

mysqladmin -u root -p 123456

mysql -uroot root -p123456

grant all on *.* to test_user@'%' identified by '1234';

show master status

stop slave;

change master to master_host='192.168.107.16', master_user='repl_user', master_password='copy', master_log_file='mysql-bin.000001', master_log_pos=106;#注意IP、master_log_file、master_log_pos必须跟主库一样

start slave;

show slave status\G确保Slave_IO_Running: Yes和Slave_SQL_Running: Yes方为正常。

amoeba配置--192.168.107.10

下载amoeba-mysql-binary-2.2.0.tar.gz

mkdir /usr/local/amoeba

tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba

cd /usr/local/amoeba/conf

1、[root@localhost conf]# cat amoeba.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<proxy>

<!--

-->

<property name="manager">${clientConnectioneManager}</property>

</bean>

</property>

<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>

</bean>

</property>

</bean>

</property>

</service>

<!-- default value: random number

-->

<property name="manager">${clientConnectioneManager}</property>

</property>

</service>

</runtime>

</proxy>

<!--

Each ConnectionManager will start as thread

manager responsible for the Connection IO read , Death Detection

-->

<property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>

<!--

default value is avaliable Processors

-->

</connectionManager>

<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!--

default value is avaliable Processors

-->

</connectionManager>

</connectionManagerList>

<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>

</dbServerLoader>

<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>

<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>

</bean>

</property>

<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>

<property name="defaultPool">master</property>

<property name="writePool">master</property>

<property name="readPool">slave1</property>

</queryRouter>

</amoeba:configuration>

2、[root@localhost conf]# cat dbServers.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<proxy>

<!--

-->

<property name="manager">${clientConnectioneManager}</property>

</bean>

</property>

<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>

</bean>

</property>

</bean>

</property>

</service>

<!-- default value: random number

-->

<property name="manager">${clientConnectioneManager}</property>

</property>

</service>

</runtime>

</proxy>

<!--

Each ConnectionManager will start as thread

manager responsible for the Connection IO read , Death Detection

-->

<property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>

<!--

default value is avaliable Processors

-->

</connectionManager>

<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!--

default value is avaliable Processors

-->

</connectionManager>

</connectionManagerList>

<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>

</dbServerLoader>

<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>

<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>

</bean>

</property>

<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>

<property name="defaultPool">master</property>

<property name="writePool">master</property>

<property name="readPool">slave1</property>

</queryRouter>

</amoeba:configuration>

[root@localhost conf]# cat dbServers.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">

<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

<!--

Each dbServer needs to be configured into a Pool,

If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:

add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig

such as 'multiPool' dbServer

-->

<property name="manager">${defaultManager}</property>

</factoryConfig>

</poolConfig>

</dbServer>

</factoryConfig>

</dbServer>

</factoryConfig>

</dbServer>

<property name="poolNames">master,slave1</property>

</poolConfig>

</dbServer>

</amoeba:dbServers>

3、[root@localhost conf]# cat rule.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">

<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">

</tableRule>

</amoeba:rule>

4、修改/usr/local/amoeba/bin/amoeba把DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"改为DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

启动/usr/local/amoeba/bin/amoeba start &

netstat -tunlp|grep amoeba查看是否有8066和另一个端口存在。

5、测试mysql -uroot -proot -h192.168.107.10 --port 8066若显示以下信息一般即为正常

[root@localhost conf]# mysql -uroot -proot -h192.168.107.10 --port 8066

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 498117230

Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

6、amoeba执行创建数据库和插入、更新、删除、查询等操作，master和slave1服务器均开启日志跟踪

master上的日志：

tail -f /tmp/mysql.log

150104 23:54:36 373 Init DB abc

373 Query set names latin1

373 Query show databases

373 Query show tables

150104 23:54:50 373 Query show tables

150104 23:57:00 373 Query describe mytable

150104 23:58:08 373 Query insert into mytable values ('brian','male','1982-12-29')

150104 23:58:15 373 Query describe mytable

150105 0:00:12 373 Query delete from mytable where name='brian'

150105 0:01:55 373 Query insert into mytable values ('brian','male','1982-12-29')

150105 0:02:08 373 Query insert into mytable values ('bin','female','1985-12-29')

150105 0:02:43 374 Connect test_user@192.168.107.10 on test

150105 0:02:55 374 Query set names latin1

374 Query update mytable set sex='f' where name='brian'

150105 0:03:17 374 Init DB abc

374 Query show databases

374 Query show tables

150105 0:03:22 374 Query update mytable set sex='f' where name='brian'

150105 0:05:46 374 Query alter mytable drop brian

150105 0:05:54 374 Query alter table mytable drop brian

150105 0:06:44 374 Query alter table mytable drop name

slave1上的日志：

150104 23:55:02 84 Init DB abc

84 Query select * from mytable

150104 23:55:30 85 Connect root@localhost on

85 Query select @@version_comment limit 1

150104 23:55:42 85 Query grant all on *.* to test_user@'%' identified by '1234'

150104 23:55:46 85 Quit

150104 23:56:40 84 Query select @@version_comment limit 1

150104 23:58:08 8 Query insert into mytable values ('brian','male','1982-12-29')

150104 23:58:26 84 Query select * from mytable

150105 0:00:12 8 Query delete from mytable where name='brian'

150105 0:00:14 84 Query select * from mytable

150105 0:01:55 8 Query insert into mytable values ('brian','male','1982-12-29')

150105 0:02:08 8 Query insert into mytable values ('bin','female','1985-12-29')

150105 0:02:43 86 Connect test_user@192.168.107.10 on test

150105 0:03:17 86 Query set names latin1

86 Query SELECT DATABASE()

150105 0:03:22 8 Query update mytable set sex='f' where name='brian'

150105 0:03:45 86 Init DB abc

86 Query select * from mytable

150105 0:06:44 8 Query alter table mytable drop name

可以看出所有的select查询操作均在slave1执行，而其他创建更新修改操作均在master上操作（slave1上有同样记录是因为从库同步了主库的数据）

搭建成功。

转载于:https://blog.51cto.com/qiujiebin2010/1604137

你可能感兴趣的文章

枚举 POJ 1753 Flip Game

method="post" 用户名和密码不显示在网址里

查看>>

LeetCode----8. String to Integer (atoi)(Java)

查看>>

JSP标签

查看>>

Python--day65--母版和继承的基本使用

查看>>

在python 3.6的eclipse中，导入from lxml import etree老是提示，Unresolved import：etree的错误...

查看>>

经纬度计算距离

查看>>

Linux 在添加一个新账号后却没有权限怎么办

查看>>

React 源码剖析系列－不可思议的 react diff

Number of Connected Components in an Undirected Graph

查看>>

BZOJ 3143 游走（高斯消元）

查看>>