Building a high-performance web-mysql read/write splitting practice

A complete mysql read/write splitting environment consists of the following parts:

• Application Client
• Database proxy
• Database Cluster

In this practice, the application client connects to the back-end database proxy Based on c3p0. Database proxy is responsible for managing the routing policies for the client to actually access the database, and adopts the open-source framework amoeba. The database cluster adopts the replication solution of mysql master-slave. The structure of the entire environment is as follows:

Detailed steps

1. Set up the MySQL master-slave Environment

1) install mysql (5.0.45) on host1 (10.20.147.110) and host2 (10.20.147.111) respectively. For specific installation methods, see the official documentation.

2) configure the master

First, edit/etc/my. cnf and add the following Configuration:

Log-bin = mysql-bin # slave performs replication based on this log-bin
Server-id = 1 # master id
Binlog-do-db = amoeba_study # specific database used for master-slave

Then add a User Dedicated to replication:

Mysql> grant replication slave on *. * TO repl@10.20.147.111 identified by '2013 ';

Restart mysql to make the configuration take effect:

/Etc/init. d/mysqld restart

Finally, check the master Status:

3) Configure slave

First, edit/etc/my. cnf and add the following Configuration:

Server-id = 2 # slave id

After the configuration takes effect, configure the connection to the master:

Mysql> CHANGE MASTER
-> MASTER_HOST = '10. 2017147.110 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = '123 ',
-> MASTER_LOG_FILE = 'mysql-bin.000003 ',
-> MASTER_LOG_POS = 161261;

MASTER_HOST is the ip address of the master, and MASTER_USER and MASTER_PASSWORD are the users we just added on the master. MASTER_LOG_FILE and MASTER_LOG_POS correspond to the information in the master status.

Finally Start slave:

Mysql> start slave;

4) Verify that the master-slave construction takes effect

View the log (/var/log/mysqld. log) of the Server Load balancer instance ):

100703 10:51:42 [Note] Slave I/O thread: connected to master 'repl @ 10.20.147.110: 100', replication started in log 'mysql-bin.000003 'at position 3306

If you see the above information, it indicates that the building is successful. If you have any problems, you can use this log to find the cause.

2. Set up database proxy

In this case, the database proxy uses amoeba. For more information, see the official documentation.

1) install amoeba

Download amoeba (1.2.0-Ga) and unzip it locally (D:/opensource/amoeba-mysql-1.2.0-GA) to complete installation

2) Configure amoeba

First configure the proxy connection and connection information with each backend MySQL Server (D:/opensource/amoeba-mysql-1.2.0-GA/CONF/amoeba. XML ):

<Server> <! -- Port bound to the proxy server --> <property name = "port"> 8066 </property> <! -- IP address bound to the proxy server --> <! -- <Property name = "ipAddress"> 127.0.0.1 </property> --> <! -- Proxy server net IO Read thread size --> <property name = "readThreadPoolSize"> 20 </property> <! -- Proxy server client process thread size --> <property name = "clientSideThreadPoolSize"> 30 </property> <! -- Mysql server data packet process thread size --> <property name = "serverSideThreadPoolSize"> 30 </property> <! -- Socket Send and receive BufferSize (unit: K) --> <property name = "netBufferSize"> 128 </property> <! -- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). --> <property name = "tcpNoDelay"> true </property> <! -- Username for external verification --> <property name = "user"> root </property> <! -- Password for external verification --> <property name = "password"> root </property> </server>

The above is the connection configuration provided by the proxy to the client.

<Dbserverlist> <dbserver name = "server1"> <! -- Poolableobjectfactory implementation class --> <factoryconfig class = "com.meidusa.amoeba.mysql.net. mysqlserverconnectionfactory"> <property name = "manager"> defaultmanager </property> <! -- Real MySQL database port --> <property name = "Port"> 3306 </property> <! -- Real MySQL database IP --> <property name = "IPaddress"> 10.20.147.110 </property> <property name = "schema"> amoeba_study </property> <! -- Username used to log on to MySQL --> <property name = "user"> root </property> <! -- Password used to log on to MySQL --> <property name = "password"> </property> </factoryconfig> <! -- Objectpool implementation class --> <poolconfig class = "com.meidusa.amoeba.net. poolable. poolableobjectpool "> <property name =" maxactive "> 200 </property> <property name =" maxidle "> 200 </property> <property name =" minidle "> 10 </ property> <property name = "minevictableidletimemillis"> 600000 </property> <property name = "timebetweenevictionrunsmillis"> 600000 </property> <property name = "testonborrow"> true </Property> <property name = "testwh Ileidle "> true </property> </poolconfig> </dbserver> <dbserver name =" server2 "> <! -- Poolableobjectfactory implementation class --> <factoryconfig class = "com.meidusa.amoeba.mysql.net. mysqlserverconnectionfactory"> <property name = "manager"> defaultmanager </property> <! -- Real MySQL database port --> <property name = "Port"> 3306 </property> <! -- Real MySQL database IP --> <property name = "IPaddress"> 10.20.147.111 </property> <property name = "schema"> amoeba_study </property> <! -- Username used to log on to MySQL --> <property name = "user"> root </property> <! -- Password used to log on to MySQL --> <property name = "password"> </property> </factoryconfig> <! -- Objectpool implementation class --> <poolconfig class = "com.meidusa.amoeba.net. poolable. poolableobjectpool "> <property name =" maxactive "> 200 </property> <property name =" maxidle "> 200 </property> <property name =" minidle "> 10 </ property> <property name = "minevictableidletimemillis"> 600000 </property> <property name = "timebetweenevictionrunsmillis"> 600000 </property> <property name = "testonborrow"> true </Property> <property name = "testwhileidle"> true </property> </poolconfig> </dbserver> </dbserverlist>

 

The above is the configuration information of the proxy and the backend mysql database servers. For detailed configuration, see the notes.

Finally, configure the read/write splitting policy:

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"><property name="LRUMapSize">1500</property><property name="defaultPool">server1</property><property name="writePool">server1</property><property name="readPool">server2</property><property name="needParse">true</property></queryRouter>

 

From the above configuration, we will find that the write operation route is to server1 (master), and the read operation route is to server2 (slave)

3) Start amoeba

Run D:/openSource/amoeba-mysql-1.2.0-GA/amoeba. bat in the command line:

Log4j: WARN log4j config load completed from file: D:/openSource/amoeba-mysql-1.2.0-GA/conf/log4j. xml
Log4j: WARN ip access config load completed from file: D:/openSource/amoeba-mysql-1.2.0-GA/conf/access_list.conf
2010-07-03 09:55:33, 821 INFO net. ServerableConnectionManager-Server listening on 0.0.0.0/0.0.0.0: 8066.
Iii. Client call and Test

1) Write a client call program

The specific program details will not be detailed, but it is just the most common jdbc database operation program based on mysql driver.

2) Configure database connection

The client is based on c3p0. The specific data source configuration is as follows:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"destroy-method="close"><property name="driverClass" value="com.mysql.jdbc.Driver" /><property name="jdbcUrl" value="jdbc:mysql://localhost:8066/amoeba_study" /><property name="user" value="root" /><property name="password" value="root" /><property name="minPoolSize" value="1" /><property name="maxPoolSize" value="1" /><property name="maxIdleTime" value="1800" /><property name="acquireIncrement" value="1" /><property name="maxStatements" value="0" /><property name="initialPoolSize" value="1" /><property name="idleConnectionTestPeriod" value="1800" /><property name="acquireRetryAttempts" value="6" /><property name="acquireRetryDelay" value="1000" /><property name="breakAfterAcquireFailure" value="false" /><property name="testConnectionOnCheckout" value="true" /><property name="testConnectionOnCheckin" value="false" /></bean>

 

It is worth noting that the client only needs to connect to the proxy, which has nothing to do with the actual database. Therefore, the jdbcUrl, user, and password configurations correspond to the configuration information exposed by amoeba.

3) Call and Test

Insert a data entry: insert into zone_by_id (id, name) values (20003, 'name _ 20003 ')

View the logs on the master machine/var/lib/mysql/mysql_log.log:

100703 11:58:42 1 Query set names latin1
1 Query set names latin1
1 Query SET character_set_results = NULL
1 Query SHOW VARIABLES
1 query show collation
1 query set autocommit = 1
1 query set SQL _mode = 'strict _ trans_tables'
1 query show variables like 'tx _ isolation'
1 Query show full tables from 'amoeba _ study 'LIKE 'probablynot'
1 Prepare [1] insert into zone_by_id (id, name) values (?,?)
1 Prepare [2] insert into zone_by_id (id, name) values (?,?)

1 Execute [2] insert into zone_by_id (id, name) values (20003, 'name _ 100 ')

It is known that the write operation occurs on the master machine.

View the log/var/lib/MySQL/mysql_log.log on the slave machine:

100703 11:58:42 2 query insert into zone_by_id (ID, name) values (20003, 'name _ 20003 ')

We know that slave has executed this statement synchronously.

Then query the data: Select T. name from zone_by_id t where T. ID = 20003.

View the log/var/lib/MySQL/mysql_log.log on the slave machine:

100703 12:02:00 33 query set names Latin1
33 prepare [1] Select T. name from zone_by_id t where T. ID =?
33 prepare [2] Select T. name from zone_by_id t where T. ID =?

33 execute [2] Select T. name from zone_by_id t where T. ID = 20003

Read operation on slave

In addition, you can view the log/var/lib/MySQL/mysql_log.log on the server Load balancer and find that this statement is not executed on the master.

The above verification shows that the simple master-slave construction and practice are effective.