How to Implement read/write splitting in MySQL,

How to Implement read/write splitting in MySQL,

Mysql-proxy for read/write splitting
MySQL Proxy is a simple program between your client and MySQL server. It can monitor, analyze, or change their communication. It is flexible and has no restrictions. Its common uses include load balancing, faults, query analysis, query filtering and modification.
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.
One of the more powerful functions of MySQL Proxy is to implement "read/write splitting". The basic principle is to let the primary database process transactional queries and let the slave database process SELECT queries. Database Replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

1. Install mysql-proxy
Download installation package http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/ here
If the dependency package is compiled and installed with libevent2 1.x lua 5.1.x glibc2 2.6.0 pkg-config libtool 1.5
The glibc's two-step package can be used to decompress mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz.

tar -zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

2. Create mysql master-slave Replication

master  192.168.216.133：3306slave   192.168.216.132：3306

3. Configure mysql-proxy
Create a mysql-proxy configuration file. All options in the configuration file cannot be enclosed by quotation marks.

vim /usr/local/mysql-proxy/mysql-proxy.conf

[Mysql-proxy] daemon = true # Start keepalive in future daemon mode = true # Automatically Restart log-level = debug when the process fails # Set the log level to debug, after debugging is complete, you can change to info log-file =/var/log/mysql-proxy.log # Set the log file path basedir =/usr/local/mysql-proxy # Set the Home Directory of mysql-proxy proxy-address = 192.168.216.132: 4040 # specify the mysql-proxy listening address proxy-backend-addresses = 192.168.216.20.: 3306 # Set the background master server proxy-read-only-backend-addresses = 192.168.216.132: 3306 # Set background slave server proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua # Set read/write splitting script path admin-address = 192.168.216.132: 4041 # Set the mysql-proxy management address, admin-username = admin # admin-password = admin # admin-lua-script =/usr/local/mysql-proxy/ share/doc/mysql-proxy/admin. lua # Set the lua script path in the management background. The script is not automatically defined by default.

After the mysql-proxy.conf is configured, make sure that the file has 600 permissions and that it contains a lua script
Start mysql-proxy through the configuration file

/Usr/local/mysql-proxy/bin/mysql-proxy -- plugins = admin -- defaults-file = mysql-proxy.conf -- plugins = proxy # specify the proxy plug-in, the configuration write configuration file cannot be started -- plugins = admin # specify admin plug-in -- defaults-file = mysql-proxy.conf # specify the configuration file

4. Start the test

Log on to the management address to view the current status

mysql -uadmin -padmin -h192.168.216.132 -P4041

The current status of the two backend servers is unknown because no user connects to the backend through mysql-proxy.

Mysql-proxy does not perform identity authentication for users, but submits the identity authentication to the backend server for authentication. Therefore, you need to open the permission for mysql-proxy on the backend server.

The following is the custom admin. lua

function set_error(errmsg) proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = errmsg or "error" } endfunction read_query(packet) if packet:byte() ~= proxy.COM_QUERY then set_error("[admin] we only handle text-based queries (COM_QUERY)") return proxy.PROXY_SEND_RESULT end local query = packet:sub(2) local rows = { } local fields = { } if query:lower() == "select * from backends" then fields = { { name = "backend_ndx",type = proxy.MYSQL_TYPE_LONG }, { name = "address",type = proxy.MYSQL_TYPE_STRING }, { name = "state",type = proxy.MYSQL_TYPE_STRING }, { name = "type",type = proxy.MYSQL_TYPE_STRING }, { name = "uuid",type = proxy.MYSQL_TYPE_STRING }, { name = "connected_clients",type = proxy.MYSQL_TYPE_LONG }, } for i = 1, #proxy.global.backends do local states = { "unknown", "up", "down"           } local types = { "unknown", "rw", "ro" } local b = proxy.global.backends[i] rows[#rows + 1] = { i, b.dst.name,     -- configured backend address states[b.state + 1], -- the C-id is pushed down starting at 0 types[b.type + 1],  -- the C-id is pushed down starting at 0 b.uuid,       -- the MySQL Server's UUID if it is managed b.connected_clients -- currently connected clients } end elseif query:lower() == "select * from help" then fields = { { name = "command",type = proxy.MYSQL_TYPE_STRING }, { name = "description",type = proxy.MYSQL_TYPE_STRING }, } rows[#rows + 1] = { "SELECT * FROM help", "shows this help" } rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" } else set_error("use 'SELECT * FROM help' to see the supported commands") return proxy.PROXY_SEND_RESULT endproxy.response = { type = proxy.MYSQLD_PACKET_OK, resultset = { fields = fields, rows = rows } } return proxy.PROXY_SEND_RESULT end

5. Problem Solving
(1) If the log prompts (debug) [network-mysqld.c: 1134]: error on a connection (fd:-1 event: 0). closing client connection.

You can modify only min_idle_connections = 4 and max_idle_connections = 8 in the rw-splitting.lua to increase it

(2) Adjust the character set of backend mysql if garbled characters are encountered

[mysqld] skip-character-set-client-handshake init-connect      = 'SET NAMES utf8' character_set_server  = utf8

Mysql read/write splitting using mysqlnd_ms
Mysqlnd_ms is a plug-in of mysqlnd. This plug-in provides connection saving and switching, load balancing, and read/write splitting functions. To use the read/write splitting function of mysqlnd_ms, you must use-with-mysqlnd when installing php. The function implemented by mysqlnd is that you do not need to install mysql on the php server. to compile and install php before php5.3, You need to specify the installation path of mysql through-with-mysql =/path/to/mysql.

1. Install the mysqlnd_ms Module

tar -zxvf mysqlnd_ms-1.5.2.tgz cd mysqlnd_ms-1.5.2 /usr/local/php/bin/phpize ./configure --with-php-config=/usr/local/php/bin/php-config make && make install

The following message is displayed, indicating that the following path must have the php. ini configuration.

Installing shared extensions:   /usr/local/php/lib/php/extensions/no-debug-non-zts-20121212/Installing header files:     /usr/local/php/include/php/</span>

2. Edit/usr/local/php/etc/php. ini.

extension = /usr/local/php/lib/php/extensions/no-debug-non-zts-20121212/mysqlnd_ms.so mysqlnd_ms.enable = On mysqlnd_ms.config_file = /usr/local/php/etc/mysqlnd_ms_plugin.ini

3. Create the mysqlnd_ms_plugin.ini configuration file.

{  "myapp": {    "master": {      "master_0": {        "host": "192.168.6.135",        "socket": "\/tmp\/mysql.sock"      }    "slave": {      "slave_0": {        "host": "192.168.6.136",        "port": "3306"      "slave_1": {        "host": "192.168.6.137",        "port": "3306"      "filters": {        "random": {          "sticky": "1"        }      }    }}

Here we use a mysql server with 1 master node and 2 slave nodes.
Filters is a policy that defines access to the slave server. random randomly selects a server. The strick parameter is set to 1 to direct a request to a server.

4. Test
Test with wordpress and edit the profile wp-config.php

/** MySQL HOST */define ('db _ host', 'myapp'); # This myapp is defined in mysqlnd_ms_plugin.ini.

Articles you may be interested in:

• Yii implements MySQL multi-database and read/write splitting instance analysis
• Example of using Thinkphp to implement MySQL read/write splitting
• Steps for configuring MySQL master-slave synchronization and read/write splitting
• Mysql read/write splitting (Practice)
• Mysql read/write splitting (basic)