mysql主从及amoeba读写分离

简介：

Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。

拓扑图：

搭建mysql的主从复制环境：

ip地址为192.168.150.135作为MASTER

修改my.cnf
[[email protected] ~]# vim/etc/my.cnf 
[mysqld]
port            = 3306
binlog-do-db=dragon    #同步的数据库
replicate-ignore-db=mysql    #不同步的数据库
replicate-ignore-db=information_schema
server-id= 1      #server-id要唯一
log-bin=mysql-bin
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)
mysql> create database dragon;    
Query OK, 1 row affected (0.08 sec)
mysql> grant replication slave on *.* to 'back'@'192.168.150.%' identified by '123456';
1 row in set (0.01 sec)        #添加用户"back"，SLAVE用于登录本机复制数据库日志文件
mysql> show master status;    #查看MASTER上dragon库的日志的状态信息
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 |      560 | dragon       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
exit
[[email protected] ~]# cd /usr/local/mysql/var/
[[email protected] var]# tar zcf dragon.tar.gz dragon/
scp dragon.tar.gz [email protected]:/usr/local/mysql/var/  #把数据库复制到SLAVE上

ip192.168.150.137做为SLAVE

修改my.cnf的server-id为2
[[email protected] ~]# cd /usr/local/mysql/var/
[[email protected] ~]#tar xf dragon.tar.gz    #先解压缩复制过来的数据库文件
[[email protected] ~]#mysql -uroot -pqwe123
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.150.135',master_user='back',master_password='123456',master_log_file='mysql-bin.000025',master_log_pos=560;  #使用用户"back"复制MASTER的"mysql-bin.000025"日志     
Query OK, 0 rows affected (0.03 sec)
mysql> slave start;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[[email protected] ~]# service mysqld restart    #重启SLAVE的mysql
[[email protected] ~]# mysql -uroot -pqwe123
mysql> show slave status\G  #查看SLAVE是否正常同步,主要看"Slave_IO_Running"和"Slave_SQL_Running"是否为yes
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.135
                  Master_User: back
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000025
          Read_Master_Log_Pos: 560
               Relay_Log_File: centos-server-relay-bin.000004
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000025
             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: 560
              Relay_Log_Space: 414
              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: 
1 row in set (0.00 sec)

部署amoeba:

amoeba是基于jdk环境运行的，所以要先安装jdk
[[email protected] ~]# mv jdk1.6.0_31/ /usr/local/
[[email protected] ~]# cat >/etc/profile.d/jdk.sh<<end
JAVA_HOME=/usr/local/jdk1.6.0_31
PATH=\$JAVA_HOME/bin:\$PATH
CLASSPATH=.:\$JAVA_HOME/lib/dt.jar:\$JAVA_HOME/lib/tools.jar
export JAVA_HOME PATH CLASSPATH
end
[[email protected] ~]# java -version   #测试jdk安装成功
java version "1.6.0_31"
Java(TM) SE Runtime Environment (build 1.6.0_31-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.6-b01, mixed mode)
[[email protected] ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip
[[email protected] ~]# mv amoeba-mysql-3.0.5-RC /usr/local/amoeba
[[email protected] amoeba]# vim jvm.properties  #做如下修改，不然启动amoeba会报错
#JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"

配置amoeba：

主要通过"conf"目录下的dbServer.xml和amoeba.xml设置amoeba调度mysql请求：
[[email protected] conf]#vim dbServer.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   
-->
<dbServer name="abstractServer" abstractive="true">
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="connectionManager">${defaultManager}</property>
<property name="sendBufferSize">64</property>
<property name="receiveBufferSize">128</property>
<!-- mysql port -->
<property name="port">3306</property>    #连接mysql的默认端口
<!-- mysql schema -->
<property name="schema">dragon</property>    #设置amoeba默认连接的数据库名
<!-- mysql user -->
<property name="user">amoba</property>    #amoeba连接后端mysql的帐号和密码，后端mysql必须创建
<property name="password">qwe123</property>
</factoryConfig>
<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
<property name="maxActive">500</property>    #设置最大连接数，默认是500
<property name="maxIdle">500</property>    #配置最大空闲连接数
<property name="minIdle">1</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="writedb"  parent="abstractServer">#定义一个dbserver，名字自定义，后面会用到
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.150.135</property>    #dbserver的IP address
</factoryConfig>
</dbServer>
<dbServer name="slave1"  parent="abstractServer">
<factoryConfig>
<property name="ipAddress">192.168.150.137</property>
</factoryConfig>
</dbServer>
<dbServer name="slavepool" virtual="true">    #定义一个dbserver组
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
#调度算法：1.复制均衡,2.表示权重,3.表示HA。
<property name="loadbalance">2</property>    
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1</property>  #设置dbserver成员，根据需要可加多个
</poolConfig>
</dbServer>
</amoeba:dbServers>



[[email protected] conf]#vim amoeba.xml 
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
<proxy>
<!-- service class must implements com.meidusa.amoeba.service.Service -->
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">
<property name="port">8066</property>    #amoeba负责接受请求侦听的端口
<!--
<property name="ipAddress">127.0.0.1</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="authenticateProvider">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">root</property>    #客户机连接ameba使用的帐号和密码
<property name="password">qwe123</property>
<property name="filter">
<bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
</service>
<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
<!-- proxy server client process thread size -->
<property name="executeThreadSize">128</property>
<!-- per connection cache prepared statement size  -->
<property name="statementCacheSize">500</property>
<!-- default charset -->
<property name="serverCharset">utf8</property>
<!-- query timeout( default: 60 second , TimeUnit:second) -->
<property name="queryTimeout">60</property>
</runtime>
</proxy>
<!-- 
Each ConnectionManager will start as thread
manager responsible for the Connection IO read , Death Detection
-->
<connectionManagerList>
<connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>
</connectionManager>
</connectionManagerList>
<!-- default using file loader -->
<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<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="LRUMapSize">1500</property>
<property name="defaultPool">writedb</property>    
<property name="writePool">writedb</property>    #在dbServer定义的 writedb响应写操作 
<property name="readPool">slave1</property>    #在dbServer定义的 slave1响应读操作
<property name="needParse">true</property>
</queryRouter>
</amoeba:configuration>

在后端mysql添加amobe帐号：

mysql> grant ALL ON *.* TO 'amoba'@'192.168.150.128' identified by 'qwe123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

启动amoeba：

[[email protected] ]# /usr/local/amoeba/bin/./launcer

测试读写:

[[email protected] conf]# mysql -uroot -pqwe123 -h192.168.150.128 -P8066

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

Your MySQL connection id is 714931905

Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>

mysql> use dragon;

Database changed

mysql> create table user(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> name VARCHAR(20) NOT nULL,

-> sex BOOLEAN);

Query OK, 0 rows affected (0.05 sec)

mysql> insert into user(name,sex) VALUES ('dragon',1);

Query OK, 1 row affected (0.05 sec)

mysql> insert into user(name,sex) VALUES ('dragon',1),('bbq',1);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from user;

+----+--------+------+

| ID | name | sex |

+----+--------+------+

| 1 | dragon | 1 |

| 2 | dragon | 1 |

| 3 | bbq | 1 |

+----+--------+------+

3 rows in set (0.03 sec)

转载于:https://blog.51cto.com/dragon123/1600473

mysql主从及amoeba读写分离

相关推荐