Mysql-proxy for MySQL read-write separation, load balancing

In MySQL to achieve read and write separation, load balancing, with Mysql-proxy is very easy, but large-scale processing for performance has yet to be improved, the main configuration steps are as follows:

1.1. Mysql-proxy Installation

MySQL Proxy is such a middle-tier agent, simply said, MySQL proxy is a connection pool, responsible for the front-end application of connection requests forwarded to the background database, and through the use of Lua script, can achieve complex connection control and filtering, so as to achieve read-write separation and load balancing. For the application, the MySQL proxy is completely transparent, and the application only needs to be connected to the listening port of MySQL proxy. Of course, this proxy machine may become a single point of failure, but can use multiple proxy machine as redundancy, in the Application Server connection pool configuration to multiple proxy connection parameters can be configured. It is recommended that the proxy be installed on a server with the MySQL database.

Before installing Mysql-proxy, you need to determine that you have followed the following package: Pkg-config (System comes with, do not need to install), Libevent,glib,lua, in the CentOS5.2 system already included, to see if the package has been installed, with the command rpm–q Libevent, Rpm–q glib, and Rpm–q–lua, if the version is too low or not installed, it needs to be reinstalled.

First, Mysql-proxy installation

1, libevent Installation

CentOS under the libevent version of the super-old, this no other way, can only recompile themselves, the version needs at 1.4.0 above, the higher the better, reinstall the command

Øwget http://monkey.org/~provos/libevent-1.4.9-stable.tar.gz Remote Download package

Øtar ZVFX libevent-1.4.9-stable.tar.gz

ØCD libevent-1.4.9-stable

Ø./configure

Ømake

Ømake Install

2, glib installation

CentOS comes with the glib version is also older, mysqlproxy 0.7. More than 0 needs Glib2 2.16.0 To compile successfully, so you also need to recompile glib, reinstall the command:

Øwget http://ftp.gnome.org/pub/gnome/sources/glib/2.18/glib-2.18.4.tar.gz

Øtar ZVFX glib-2.18.4.tar.gz

ØCD glib-2.18.4

Ø./configure

Ømake

Ømake Install

3. LUA Installation

The CentOS system does not bring the package, you need to download the installation, the installation command is as follows

Øwget http://www.lua.org/ftp/lua-5.1.4.tar.gz

Øtar ZVFX lua-5.1.4.tar.gz

ØCD lua-5.1.4

Before installing LUA, if the operating system is 64-bit, you need to modify the LUA-related file code to:

VI src/makefile into the file to find Cflags, at the end of which add-fpic, if not added to the 64-bit machine to compile a "relocations" error.

After modifying the file, now perform the compilation according to the operation

Ømake Linux

Ømake Install

ØCP etc/lua.pc/usr/local/lib/pkgconfig/

Important: Let Pkg-config find out where they compiled the library, execute the following instructions

Export Pkg_config_path= $PKG _config_path:/usr/local/lib/pkgconfig

LUA installation is complete.

4, Mysql-proxy source installation

Øwget http://launchpad.net/mysql-proxy/0.8/0.8.0/+download/mysql-proxy-0.8.0.tar.gz

O

Øtar ZXVF mysql-proxy-0.8.0.tar.gz

ØCD mysql-proxy-0.8.0

Ø./configure

Ømake

Ømake Install

Compile to complete executable command mysql-proxy–v view installation information

Indicates that the MYSQL-PROXY has been installed successfully. The next step is to configure it

Second, mysql-proxy configuration

1, mysql-proxy configuration needs to configure scripts, scripts can be downloaded directly from the site, execute commands

Øwget-c Http://customcode.googlecode.com/files/mysql-proxy

ØCP mysql-proxy/etc/init.d/mysql-proxy Copy the download to/etc/init.d/

This script provides support for Mysql-proxy start, stop, restart, and read-write separation and load balancing of related runs.

Format the script, or you will be prompted with an error when you run the operation, convert the command:

Ødos2unxi/etc/init.d/mysql-proxy

Øvi/etc/init.d/mysql-proxy

Ø:set Ff=unix modified into UNIX mode

Save exit

To execute the command again:

ø# chmod 755/etc/init.d/mysql-proxy Modify the file permissions

ø# Chkconfig Mysql-proxy on

2. Add read/write separation, load balancer configuration file

This file mainly configures read-write detach, load balanced server configuration file, path is: etc/sysconfig/add mysql-proxy file, this file name needs to match with the file name specified in the file in/etc/init.d/mysql-proxy. It's all for mysql-proxy.

Create the Mysql-proxy file and execute the command:

Øvi/etc/sysconfig/mysql-proxy Creating a file

Add Content:

# Options to Mysql-proxy

# Do not remove--daemon

proxy_options= "-admin-address=:4040--proxy-backend-addresses=192.168.1.60:3306-- proxy-read-only-backend-addresses=192.168.1.61:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/ Mysql-proxy/rw-splitting.lua--keepalive--daemon "

Red for read-write separation configuration, only for reading

Script Interpretation Instructions

--daemon Start with daemon mode

--ADMIN-ADDRESS=:4040 Specifies the management port of the Mysqlproxy, where it represents the 4401 port of the machine;

--PROXY-ADDRESS=:3307 Specifies the listening port of the mysqlproxy, or it can be represented by 127.0.0.1:3307;

--PROXY-BACKEND-ADDRESSES=192.168.1.60:3306 specifies the port of the MySQL host, can read and write;

--PROXY-READ-ONLY-BACKEND-ADDRESSES=192.168.1.61:3306 specifies a read-only MySQL host port, with multiple specified

--PROXY-READ-ONLY-BACKEND-ADDRESSES=192.168.1.61:3306 specifies another read-only MySQL host port;

--PROXY-LUA-SCRIPT=/USR/LOCAL/SHARE/MYSQL-PROXY/RW-SPLITTING.LUA Specifies the Lua script, where the rw-splitting script is used for read-write separation

This script can also be downloaded directly from the Web:

Execute the command under Linux:

wget http://bazaar.launchpad.net/~diego-fmpwizard/mysql-proxy/bug-43424/download/head%3A/ Rwsplitting.lua-20090112150705-l9v35osiopsn0nz0-10/rw-splitting.lua

Copy the downloaded package to the path/usr/local/share/mysql-proxy/

Modify read-write detach script Rw-splitting.lua

Modify the default connection, do a quick test, do not change the number of connections to 4 o'clock to start the read and write separation

#vi/opt/mysql-proxy/scripts/rw-splitting.lua

--Connection pool

Min_idle_connections = 1,//default is 4

Max_idle_connections = 1,//default is 8

Third, start Mysql-proxy

Before entering the Mysql-proxy file created in path/etc/init.d/, execute the command:

Ømysql-proxy start|stop The Mysql-proxy service started successfully | stop

2.2 Testing
One, load balancing test:

Test server: 192.168.1.60 (mysql-proxy installed on this server); 192.168.1.61,

The Sub-table creates database Testproxy on the above server for data reading.

Adding data to the 60 database name:slave,id:1

61 adding data to the database name:master,id:1

Open two connection windows

Enter a command in 192.168.1.61

# mysql-u user-h 192.168.1.60-p-P 4010

It is primarily connected to the Mysql-proxy in 192.168.1.60, where 4040 is the Mysql-proxy management port, which is set in the configuration file.

Execute the query statement in MySQL:

The default is to request a connection to the current database, then stop the MySQL service in the current server (192.168.1.61)

Command: Service mysqld stop;

Once you stop the service, connect again

# mysql-u user-h 192.168.1.60-p-P 4010

In MySQL executes the query statement, the output result is:

This query is automatically switched to the data in the 192.168.1.60

Now that the Mysql-proxy service is connected to the 192.168.0.60 database, if you stop the MySQL service now, start 1.61 of the MySQL service, and then execute it on the 192.168.1.61 client.

# mysql-u user-h 192.168.1.60-p-P 4010

The result of the query will be the data of the 192.168.1.61 database,

The above error message indicates that you are trying to connect to another server.

Note: The connection in load balancing does not have a master-slave point, when multiple servers start at the same time, the first server started, is identified as the primary, if the main hung off, the other servers connected will be identified as the master.

When doing a read-write separation test:

Mainly for the program testing, need to be in the two database at the same time to add a query operation, so that the addition will automatically switch to the main library, read the data will be switched to the slave library.

We have implemented MySQL read and write separation, all of the current write operations are all on the Master master server, to avoid the data of different steps, in the program to do the test, the connection server address is: 192.168.1.60 port=4040 (. Net Connection database Web. config connection string)

This must be followed by the upper port number, the specified connection is the Mysql-proxy proxy server, in the code to write inserts, query statements, test, you will find a different read and write operation of the database.

In addition, all read operations are distributed to other slave from the server to share the database pressure.

Summary: In the above environment, Mysql-proxy and Mysql-master, mysql-slave three servers have a single point of failure. If the availability requirements are high, a single point of vulnerability is absolutely not allowed. In order to avoid mysql-proxy single point of vulnerability there are two ways, one way is mysql-proxy with keepalived do two machines, another way is to install Mysql-proxy and application services on the same server , in order to avoid mysql-master single point of failure can use Drbd+heartbear to do dual machine, to avoid mysql-slave single point of failure to increase the number of mysql-slave can, Because the Mysql-proxy will automatically shield the back-end of the mysql-slave that has failed.

Appendix:

Mysql configuration file:

1 [mysqld]

2datadir=/var/lib/mysql

3socket=/var/lib/mysql/mysql.sock

4user=mysql

5 #server-id= 2

6 log-bin= Mysql-bin

7 #master-host= 192.168.1.60

8 #master-user= User

9 #master-password= 123456

#master-port= 3306

One #Disabling symbolic-links is recommended to prevent assorted security risks

12symbolic-links=0

13log-slow-queries = Var/lib/mysql/slow-query.log

14back_log=1000

15key_buffer_size = 300M

16max_allowed_packet = 4M

17thread_stack = 256K

18tmp_table_size = 200M

19sort_buffer_size = 32M

20read_buffer_size = 4M

21read_rnd_buffer_size = 16M

22join_buffer_size = 8M

23myisam_sort_buffer_size = 64M

24table_cache= 1024

120 thread_cache_size=

26query_cache_size = 64M

27tmp_table_size = 256M

28max_connections = 1024

29thread_cache_size = 80

30max_connect_errors = 10000000

31wait_timeout=30//Sleep connection is terminated after more than 10 seconds of sleep time

32thread_concurrency= 8

33innodb_flush_log_at_trx_commit = 1

34innodb_log_buffer_size = 2M

35innodb_thread_concurrency=8

36record_buffer = 16M

37interactive_timeout = 30

38skip_name_resolve//Suggested Open

39binlog_format=mixed//recommend turning on to avoid log errors during transactional operations

Toenable the InnoDB Plugin, uncomment the 2 next lines

41#ignore-builtin-innodb

42#plugin-load=innodb=ha_innodb_plugin.so

43

# toenable innodb-related information_schema tables

# jointhe following options to above directive

innodb_trx=ha_innodb_plugin.so;

innodb_locks=ha_innodb_plugin.so;

innodb_cmp=ha_innodb_plugin.so;

innodb_cmp_reset=ha_innodb_plugin.so;

innodb_cmpmem=ha_innodb_plugin.so;

innodb_cmpmem_reset=ha_innodb_plugin.so;

52

53[mysqld_safe]

54log-error=/var/log/mysqld.log

55pid-file=/var/run/mysqld/mysqld.pid

56

57

[MySQL]

59default-character-set=gb2312

Common MY.CNF configuration files

MySQL Slow log query

Parse the SQL query statement to open the log_slow_queries parameter in MySQL

Open by adding log-slow-queries= in My.cnf var/lib/mysql/slow-query.log

Mate parameter Long_query_time =10 (in seconds) only a query that is more than 10s logs the log

MySQL appears in a lot of concurrent "unauthenticated user x.x.x.x:2501 None Connect Reading from net"

Information, you need to add the Skip-name-resolve parameter to the MY.CNF configuration file,

It will prevent MySQL Server from DNS parsing of external connections, and using this option can eliminate the time for DNS resolution for MySQL.

However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address, otherwise MySQL will not be able to handle the connection request properly, that is, it cannot be accessed using the localhost hostname

Mysql-proxy for MySQL read/write separation, load balancing (RPM)