Mysql-proxy for MySQL read/write splitting and load balancing

It is easy to use MySQL-proxy to implement read/write splitting and load balancing in MySQL. However, large-scale processing still needs to be improved in terms of performance. The main configuration steps are as follows:

1.1. Install mysql-proxy

MySQL proxy is such a middle-layer proxy. In short, MySQL proxy is a connection pool that forwards connection requests from foreground applications to the background database and uses Lua scripts, complex Connection Control and filtering can be implemented to achieve read/write splitting and load balancing. For applications, MySQL proxy is completely transparent, and the application only needs to connect to the listening port of MySQL proxy. Of course, the proxy machine may become a single point of failure, but multiple proxy machines can be used as redundancy. You can configure the connection parameters of multiple proxies in the connection pool configuration of the application server. We recommend that you do not install the proxy on a server with the MySQL database.

Before installing mysql-proxy, make sure that you have included the following packages: PKG-config (which comes with the system and does not require installation), libevent, glib, and Lua in centos5.2, check whether the package has been installed and changed. Run the command rpm-Q libevent, rpm-Q glib, and rpm-Q-Lua. if the version is too low or not installed, reinstall the package.

1. Install mysql-proxy

1. libevent Installation

The libevent version that comes with centos is very old. There is no other way to re-compile it by yourself. The version must be later than 1.4.0. The higher the version, 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. Install glib

The glib version that comes with centos is also relatively old. mysqlproxy 0.7.0 or above requires glib2 later than .0 to be compiled successfully. Therefore, you also need to re-compile glib and re-install 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 contain this package. You need to download and install the package. 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 file code:

VI src/makefile go to the file to find the cflags, and add-FPIC to the end of the file. If not, a "relocations" error occurs during compilation on 64-bit machines.

After modifying the file, compile the file as needed.

Ø make Linux

Ø make install

Cp etc/Lua. PC/usr/local/lib/pkgconfig/

Important: Let PKG-config find the library you have compiled and execute the following command

Export pkg_config_path = $ pkg_config_path:/usr/local/lib/pkgconfig

Lua installation is complete.

4. Install mysql-proxy source code

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

Ø

Tar zxvf mysql-proxy-0.8.0.tar.gz

CD mysql-proxy-0.8.0

Ø./configure

Ø make

Ø make install

Run the mysql-proxy-V command to view the installation information after compilation.

The MySQL-proxy has been installed successfully. Next, configure it.

Ii. mysql-proxy configuration

1. configure a script for MySQL-proxy configuration. The script can be downloaded directly from the website and executed.

Wget-C http://customcode.googlecode.com/files/mysql-proxy

CP mysql-proxy/etc/init. d/MySQL-proxy copy the downloaded file to/etc/init. d/

This script supports mysql-proxy startup, stop, restart, read/write splitting, and Server Load balancer.

Convert the script format. Otherwise, an error is prompted during the operation. The conversion command is as follows:

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

VI/etc/init. d/MySQL-proxy

Ø: Set FF = change UNIX to Unix Mode

Save and exit

Run the following command:

# Chmod 755/etc/init. d/MySQL-proxy modify the File Permission

Ø  # chkconfig mysql-proxy on  

2. Add read/write splitting and Server Load balancer configuration files

This file is mainly used to configure the server configuration file for read/write splitting and load balancing. The path is: ETC/sysconfig/Add the mysql-proxy file. The file name must be the same as/etc/init. d/MySQL-Proxy: the code in the file specifies the same file name. Mysql-proxy is used here.

Create the mysql-proxy file and run the following command:

VI/etc/sysconfig/MySQL-proxy File Creation

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 indicates the read/write splitting configuration, which is only used for read

Script description

-- Daemon is started in daemon mode.

-- Admin-address =: 4040 specifies the Management port of mysqlproxy. Here, it indicates port 4401 of the local machine;

-- Proxy-address =: 3307 specifies the listening port of mysqlproxy, which can also be expressed as 127.0.0.1: 3307;

-- Proxy-backend-addresses = 192.168.1.60: 3306 specifies the port of the MySQL host, which can be read and written;

-- Proxy-read-only-backend-addresses = 192.168.1.61: 3306 specifies the read-only MySQL host port and multiple

-- Proxy-read-only-backend-addresses = 192.168.1.61: 3306 specify 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 splitting

This script can also be directly downloaded online:

Run the following command in 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/usr/local/share/MySQL-proxy /.

Modify read/write splitting script rw-splitting.lua

Modify the default connection and perform a quick test. If the number of connections is not modified, the read/write splitting is started only when the number of connections reaches 4.

# Vi/opt/MySQL-proxy/scripts/rw-splitting.lua

-- Connection Pool

Min_idle_connections = 1, // The default value is 4.

Max_idle_connections = 1, // The default value is 8.

Iii. Start mysql-proxy

Go to the mysql-proxy file created in the path/etc/init. d/and run the following command:

Mysql-proxy START | stop indicates that the mysql-proxy service is successfully started. | stop

2.2 Test 1. Server Load balancer test:

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

Create a database testproxy for table sharding on the server above for Data Reading.

Add data name: slave, ID: 1 to database 60

61 add data name: Master, ID: 1 to the database

Open two connection windows

Enter the command in 192.168.1.61

# Mysql-u user-h 192.168.1.60-p 4010

Connect to 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 request is the connection to the current database. Next, stop the MySQL service on the current server (192.168.1.61 ).

Command: Service mysqld stop;

After the service is stopped, connect again

# Mysql-u user-h 192.168.1.60-p 4010

Run the query statement in MySQL and the output result is:

This query is automatically switched to data in 192.168.1.60.

Currently, the mysql-proxy service is connected to the database 192.168.0.60. If the MySQL service is stopped, start MySQL 1.61 and run it on the client 192.168.1.61.

# Mysql-u user-h 192.168.1.60-p 4010

The query result is the data of the database 192.168.1.61,

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

Note: There are no master-slave connections in Server Load balancer. When multiple servers are started at the same time, the server that is started first is marked as the master. If the master fails, other connected servers are identified as the master.

During the read/write splitting test:

It is mainly for program testing. You need to add queries to both databases at the same time. In this way, the addition will automatically switch to the master database, and the data read will switch to the slave database.

MySQL read/write splitting has been implemented. Currently, all write operations are performed on the master server to avoid data synchronization. During testing in a program, the connection server address is 192.168.1.60 Port = 4040 (. net to connect to the database web. config connection string)

You must keep up with the port number and specify the connection to the mysql-proxy server. Write the insert and query statements in the Code for testing. Different databases with different read/write operations will be found.

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

Conclusion: In the above environment, the three servers, MySQL-proxy, MySQL-master, and MySQL-slave, all have single point of failure. If the availability requirements are high, single point of failure is absolutely not allowed. There are two ways to avoid the single point of failure (spof) of MySQL-Proxy: mysql-proxy and keepalived, another method is to install mysql-proxy and Application Service on the same server. To avoid single point of failure of MySQL-master, you can use drbd + heartbear as the dual-host; avoid mysql-SLAVE single point of failure and add multiple mysql-slave instances, because MySQL-proxy will automatically block mysql-slave instances with backend faults.

Appendix:

MySQL configuration file:

1 [mysqld]

2 datadir =/var/lib/MySQL

3 socket =/var/lib/MySQL. Sock

4 user = 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

10 # Master-Port = 3306

11 # disabling symbolic-links is recommended to prevent assorted Security Risks

12symbolic-links = 0

13log-slow-queries = var/lib/MySQL/slow-query.log

14 back_log = 1000

15 key_buffer_size = 300 m

16max_allowed_packet = 4 m

17thread_stack = 256 K

18tmp_table_size = 200 m

19sort_buffer_size = 32 m

20read_buffer_size = 4 m

21read_rnd_buffer_size = 16 m

22join_buffer_size = 8 m

23myisam_sort_buffer_size = 64 m

24table_cached = 1024

25 maid = 120

26query_cache_size = 64 m

27 tmp_table_size = 256 m

28 max_connections = 1024

29thread_cache_size = 80

30max_connect_errorrs = 10000000

31wait_timeout = 30 // If the sleep connection sleep lasts for more than 10 seconds, the connection is terminated.

32thread_concurrency = 8

33innodb_flush_log_at_trx_commit = 1

34innodb_log_buffer_size = 2 m

35innodb_thread_concurrency = 8

36record_buffer = 16 m

37interactive_timeout = 30

38skip_name_resolve // It is recommended to open

39binlog_format = mixed // we recommend that you enable this feature to avoid log errors during transaction operations.

40 # toenable the InnoDB plugin, uncomment the 2 next lines

41 # ignore-builtin-InnoDB

42 # Plugin-load = InnoDB = ha_innodb_plugin.so

43

44 # toenable InnoDB-related information_schema tables

45 # jointhe following options to abve Directive

46; innodb_trx = ha_innodb_plugin.so

47; innodb_locks = ha_innodb_plugin.so

48; innodb_cmp = ha_innodb_plugin.so

49; innodb_cmp_reset = ha_innodb_plugin.so

50; innodb_cmpmem = ha_innodb_plugin.so

51; innodb_cmpmem_reset = ha_innodb_plugin.so

52

53 [mysqld_safe]

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

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

56

57

58 [MySQL]

59default-character-set = gb2312

Common my. CNF configuration files

MySQL slow log query

Analyze the SQL query statement to open the log_slow_queries parameter in MySQL.

Open by adding log-Slow-queries = var/lib/MySQL/slow-query.log in my. CNF

In combination with the long_query_time = 10 (in seconds) parameter, only queries over 10 s will record this log.

 

MySQL has a large number of "unauthenticated user x. x: 2501 none connect reading from net"

You must add the skip-name-resolve parameter to the my. CNF configuration file,

It will prohibit MySQL server from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution.

However, if this option is enabled, IP addresses are required for all remote host connection authorization. Otherwise, MySQL cannot process connection requests normally, that is, access by using the localhost host name is not allowed.