MySQL read-write separation-amoeba

Reprinted from Http://www.cnblogs.com/liuyisai/p/6009379.html

Amoeba Master profile ($AMOEBA _home/conf/amoeba.xml), which is used to configure the basic parameters of the amoeba service, such as amoeba host address, port, authentication method, user name for connection, password, number of threads, time-out, Location of other configuration files, and so on.

A database server configuration file ($AMOEBA _home/conf/dbservers.xml) that stores and configures information about the database server that the amoeba proxies, such as host IP, port, user name, password, and so on.

The segmentation rule configuration file ($AMOEBA _home/conf/rule.xml), which is used to configure the segmentation rules.

Database function configuration file ($AMOEBA _home/conf/functionmap.xml), used to configure the processing of database functions, amoeba will use the methods in this configuration file to resolve database functions.

The segmentation rule function configuration file ($AMOEBA _home/conf/rulefunctionmap.xml) is used to configure the handling of user-defined functions used in the sharding rule.

An Access rule profile ($AMOEBA _home/conf/access_list.conf) that authorizes or disables certain server IP access to amoeba.

A log spec profile ($AMOEBA _home/conf/log4j.xml) that configures the level and manner of amoeba output logs.

Environment:

Master: Sql-master 192.168.200.101/24

From: Sql-slave 192.168.200.102/24,192.168.200.103/24

Mysql-amoeba 192.168.200.106/24

Java Environment configuration: http://liang-yao.cnblogs.com/p/8448739.html

Amoeba:https://sourceforge.net/projects/amoeba/files/

Amoeba Decompression is installed

[email protected] amoeba]# LL

Total Dosage 8

DRWXRWXRWX 2 root root 63 July 5 Benchmark

DRWXRWXRWX 2 root root 166 July 5 bin

DRWXRWXRWX 2 root root 243 July 5 conf

-rwxrwxrwx 1 root root 728 July 5 jvm.properties

DRWXRWXRWX 2 root root 4096 July 5 lib

[Email protected] amoeba]# pwd

/usr/local/amoeba

Configuring read-write separation requires only configuration of Dbservers.xml and Amoeba.xml

Vim Conf/dbservers.xml

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

<! DOCTYPE amoeba:dbservers SYSTEM "DBSERVER.DTD" >

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

<!--

Each of the dbserver needs to is configured into a Pool,

If you need to configure multiple DBServer with load balancing the can is simplified by the following configuration:

Add attribute with Name virtual = "true" in DBServer, and the configuration does not allow the element with name Factoryco Nfig

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>

#Amoeba要连接的mysql数据库端口

<!--MySQL Schema--

<property name= "schema" >test</property>

#设置缺省的数据库, when connecting amoeba, the action table must explicitly specify the database name, that is, using dbname.tablename, does not support the use dbname specify the default library, because the operation will be dispatched to the various back-end DBServer

<!--mysql User--

<property name= "User" >test</property>

#设置amoeba连接后端数据库服务器的账号和密码, you need to create the user on all back-end databases and authorize the amoeba server to connect

<property name= "Password" >123456</property>

</factoryConfig>

<poolconfig class= "Com.meidusa.toolkit.common.poolable.PoolableObjectPool" >

<property name= "maxactive" >500</property> #最大连接数

<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名称

<dbserver name= "Writedb" parent= "Abstractserver" >

<factoryConfig>

<!--mysql IP--

<property name= "IpAddress" >192.168.200.101</property>

</factoryconfig># back-end writable DBServer

</dbServer>

#定义后端可读dbserver

<dbserver name= "slave1" parent= "Abstractserver" >

<factoryConfig>

<!--mysql IP--

<property name= "IpAddress" >192.168.200.102</property>

</factoryConfig>

</dbServer>

<dbserver name= "Slave2" parent= "Abstractserver" >

<factoryConfig>

<!--mysql IP--

<property name= "IpAddress" >192.168.200.103</property>

</factoryConfig>

</dbServer>

#定义一个虚拟dbserver, add read-only dbserver to this group

<dbserver name= "Slaves" 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-#调度算法, 1 for replication equalization, 2 for weights, 3 for HA

<property name= "Poolnames" >slave1,slave2</property>

</poolConfig> #slaves组成员

</dbServer>

</amoeba:dbServers>

Vim Conf/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" >

<!--Port--

<property name= "Port" >8066</property>

#监听端口

<!--bind IpAddress--

<property name= "IpAddress" >192.168.200.106</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" >user123</property>

#客户端连接amoeba所需账号密码

<property name= "Password" >123456</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 would 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>

#amoeba默认池

<property name= "Writepool" >writedb</property> #取消注释, defining a read-write pool

<property name= "Readpool" >slaves</property>

<property name= "Needparse" >true</property>

</queryRouter>

</amoeba:configuration>

Vim Jvm.properties

jvm_options= "-server-xms256m-xmx1024m-xss256k-xx:permsize=16m-xx:maxpermsize=96m" #Xss256K less than 256K does not start properly

Master-Slave Database configuration

Mysql-u root-p123456

Create user test settings password 123456

MariaDB [(None)]> grant all on * * to [e-mail protected] ' 127.0.0.1 ' identified by "Hello";

MariaDB [(none)]> use MySQL;

Modify Host Permissions

MariaDB [(None)]> Update user Set host = '% ' where user = ' test ';

MariaDB [mysql]> Select User, host from user;

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

| user | Host |

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

| Test | %             |

Refreshing the database

MariaDB [mysql]> flush Privileges;

Start:

chmod 777 Logs

./bin/launcher

Ss-ntlu

Test

mysql-uuser123-p123456-h192.168.200.106-p8066

Disconnect Mysql-master Network, find unable to write, can only query

MySQL [test]> use test;

MySQL [test]> SELECT * from Test2

Disconnect mysql-slave1, 2 network, found unable to query, only can write

MySQL read-write separation-amoeba