DevOps Amoeba实现mysql读写分离(转)

aka-dian · November 10, 2019 · 12 hits

转载自

机器 说明
172.16.1.51 Amoeba
172.16.1.52 mysql 多实例
172.16.1.10 测试机

创建用户

首先在 172.16.1.52 服务器完成 mysql 主从复制,启用 3306,3307 多实例

12345
# 1.在主库创建一个用户        grant select,insert,update,delete on . to amoeba@'172.16.1.%' identifieentified by 'lx';# 2.在从库回收权限        REVOKE insert,update,delete on . FROM 'amoeba'@'172.16.1.%';        flush privileges;

环境支持

Amoeba 框架是基于 Java SE1.5 开发的,建议使用 java SE1.5 以上的版本

123456789
#下载安装jdkwet  http://download.oracle.com/otn-pub/java/jdk/7u80-b15/jdk-7u80-linux-x64.rpmcd /server/toolsrpm -ivh jdk-7u80-linux-x64.rpmln -s /usr/java/jdk1.7.0_80/ /usr/java/r/java/jdk1.7#加入环境变量vim /etc/profileexport JAVA_HOME=/usr/java/jdk1.7export PATH=$PATH_HOME/bin:$PATH_HOME/jre/bin:$PATH. /etc/profile

amoeba 安装

12345678910111213141516171819
#下载amoeba-mysql-binary-2.2.0.tar.gz wget http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/downloadtar xf amoeba-mysql-binary-2.2.0.tar.gz#解压安装mkdir /application/amoebatar -zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /application/amoebacd /application/amoeba[root@lamp01 amoeba]# ll总用量 60drwxr-xr-x 2 root root  4096 2月  18 09:59 benchmarkdrwxr-xr-x 2 root root  4096 2月  29 2012 bin-rw-r--r-- 1 root root  3976 8月  29 2012 changelogs.txtdrwxr-xr-x 2 root root  4096 2月  18 09:59 confdrwxr-xr-x 3 root root  4096 2月  18 09:59 lib-rw-r--r-- 1 root root 34520 8月  29 2012 LICENSE.txt-rw-r--r-- 1 root root  2031 8月  29 2012 README.html

修改配置文件

amoeba 的配置是基于 XML 的配置文件

1234567891011121314151617181920212223242526272829303132
#cd /application/amoeba/conf/#vim amoeba.xml  <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">         <!-- port -->         <property name="port">3306</property>        #修改amoeba启动端口         <!-- bind ipAddress -->         <property name="ipAddress">172.16.1.51</property>    #修改为amoeba服务器ip地址         <property name="manager">${clientConnectioneManager}</property>         <property name="connectionFactory">                 <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">                         <property name="sendBufferSize">128</property>                         <property name="receiveBufferSize">64</property>                 </bean>         </property>         <property name="authenticator">                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">                         <property name="user">amoeba</property>    #定义用户                         <property name="password">lx</property>    #定义密码                         <property name="filter">                                 <bean class="com.meidusa.amoeba.server.IPAccessController">                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>                                 </bean>                         </property>                 </bean>         </property></service>  <property name="defaultPool">master</property>            #修改amoeba指向后端节点主数据库<property name="writePool">master</property><property name="readPool">slave</property>

编辑 dbServers.xml 文件,添加 3306,3307 多实例
vim dbServers.xml

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
<dbServer name="abstractServer1" abstractive="true">     将 abstractServer 修改为 abstractServer1  <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">    <property name="manager">${defaultManager}</property>    <property name="sendBufferSize">64</property>    <property name="receiveBufferSize">128</property>    <!-- mysql port -->    <property name="port">3306</property>        #3306端口    <!-- mysql schema -->    <property name="schema">test</property>            #注意查看主从数据库是否存在test数据库    <!-- mysql user -->    <property name="user">amoeba</property>        #用户名    <!--  mysql password -->    <property name="password">lx</property>        #密码  </factoryConfig>  <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">    <property name="maxActive">500</property>    <property name="maxIdle">500</property>    <property name="minIdle">10</property>    <property name="minEvictableIdleTimeMillis">600000</property>    <property name="timeBetweenEvictionRunsMillis">600000</property>    <property name="testOnBorrow">true</property>    <property name="testOnReturn">true</property>    <property name="testWhileIdle">true</property>  </poolConfig></dbServer><dbServer name="abstractServer2" abstractive="true">       将 abstractServer 修改为 abstractServer2  <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">    <property name="manager">${defaultManager}</property>    <property name="sendBufferSize">64</property>    <property name="receiveBufferSize">128</property>    <!-- mysql port -->    <property name="port">3307</property>        #数据库3307端口号    <!-- mysql schema -->    <property name="schema">test</property>    #注意查看主从数据库是否存在test数据库    <!-- mysql user -->    <property name="user">amoeba</property>      #用户帐号    <!--  mysql password -->    <property name="password">lx</property>        #用户密码  </factoryConfig>  <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">    <property name="maxActive">500</property>    <property name="maxIdle">500</property>    <property name="minIdle">10</property>    <property name="minEvictableIdleTimeMillis">600000</property>    <property name="timeBetweenEvictionRunsMillis">600000</property>    <property name="testOnBorrow">true</property>    <property name="testOnReturn">true</property>    <property name="testWhileIdle">true</property>  </poolConfig></dbServer>

修改 bin 目录的权限

1
chmod -R 700 /Application/amoeba/bin/

1234
#vim /application/amoeba/bin/amoeba#添加DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

启动

1234567
/application/amoeba/bin/amoeba   start#查看端口[root@MySQL-master-01 conf]# lsof -i:3306COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAMEjava    19474 root   41u  IPv6 238156      0t0  TCP MySQL-master-01:39277->MySQL-master-02:mysql (ESTABLISHED)java    19474 root   53u  IPv6 238162      0t0  TCP MySQL-master-01:mysql (LISTEN)

测试

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123
#在web服务端172.16.1.10连接amoeba服务器测试[root@web01 ~]# mysql -uamoeba -plx -h 172.16.1.51Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 126729963Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.#测试在172.16.1.52主库上面创建一个表mysql> use database ruby;mysql> use ruby;Database changedmysql> create table lx (id int(10),name varchar(10),address varchar(20));Query OK, 0 rows affected (0.08 sec)#停止从库mysql> stop slave;Query OK, 0 rows affected (0.03 sec)#分别在主库和从库插入一条数据#主库插入:mysql> insert into lx values(1,'lx','master');Query OK, 1 row affected (0.01 sec)mysql> select * from lx;+------+------+---------+| id   | name | address |+------+------+---------+|    1 | lx   | master  |+------+------+---------+1 row in set (0.00 sec)#从库插入:mysql> insert into ruby.lx values(1,'lx','slave'); Query OK, 1 row affected (0.00 secmysql> select * from ruby.lx;+------+------+---------+| id   | name | address |+------+------+---------+|    1 | lx   | slave   |+------+------+---------+1 row in set (0.00 sec)#在测试服务器172.16.1.10mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || bbs                || dedecms            || lixiang            || mysql              || performance_schema || ruby               || test               || wordpress          |+--------------------+9 rows in set (0.00 sec)mysql> use ruby;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_ruby |+----------------+| lx             |+----------------+1 row in set (0.01 sec)这个时候我们查询到的数据是从库创建的数据mysql> select * from lx;+------+------+---------+| id   | name | address |+------+------+---------+|    1 | lx   | slave   |+------+------+---------+1 row in set (0.00 sec)此时我们在插入一条数据mysql> insert into ruby.lx values(33,'test33','test33');Query OK, 1 row affected (0.01 sec)#返回172.16.1.52服务器,在主库中查看是否有该条数据mysql> select * from lx;                  +------+--------+---------+| id   | name   | address |+------+--------+---------+|    1 | lx     | master  ||   33 | test33 | test33  |+------+--------+---------+2 rows in set (0.00 sec)回到172.16.1.52服务器,在从库中查看是否有该条数据mysql> select * from ruby.lx;             +------+------+---------+| id   | name | address |+------+------+---------+|    1 | lx   | slave   |+------+------+---------+1 row in set (0.00 sec)mysql> start slave;                                                #从库开启主从同步Query OK, 0 rows affected (0.00 sec)#再次回到测试服务器172.16.1.10,查看数据情况mysql> select * from ruby.lx;+------+--------+---------+| id   | name   | address |+------+--------+---------+|    1 | lx     | slave   ||    1 | lx     | master  ||   33 | test33 | test33  |+------+--------+---------+3 rows in set (0.01 sec)

至此,如果得到上面的结果则说明 mysql 数据读写分离完成,此时在回到从数据库开启主从同步 start slave

No Reply at the moment.
You need to Sign in before reply, if you don't have an account, please Sign up first.