Mysql-proxy configuration for mysql read/write splitting

Example 1

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
Music 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: 3306
Slave 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 daemon later
Keepalive = true # restart automatically after a process failure
Log-level = debug # set the log level to debug. You can change it to info after debugging.
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 listening address of mysql-proxy
Proxy-backend-addresses = 192.168.216.20.: 3306 # set the backend master server
Proxy-read-only-backend-addresses = 192.168.216.132: 3306 # set the background slave server
Proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua # set the path of the read/write splitting script
Admin-address = 192.168.216.132: 4041 # set the mysql-proxy management address, which requires the parent admin plug-in
Admin-username = admin # set the logon management address user
Admin-password = admin # set the management user password
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. This configuration cannot be started when it is written to the configuration file.
-- Plugins = admin # specify the 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"
 }
End
Function 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
End
Proxy. response = {
Type = proxy. MYSQLD_PACKET_ OK,
Resultset = {
Fields = fields,
Rows = rows
 }
 }
Return proxy. PROXY_SEND_RESULT
End

5. Problems encountered

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. In case of garbled characters, you need to adjust the character set of backend mysql.

[Mysqld]
Skip-character-set-client-handshake
Init-connect = 'set NAMES utf8'
Character_set_server = utf8

Example 2

Use MySQL-Proxy for read/write splitting

The LUA script for implementing read/write splitting is simple and clear:

-- Read/write splitting
--
-- Send all non-transactional SELECT statements to a slave database
If is_in_transaction = 0 and
Packet: byte () = proxy. COM_QUERY and
Packet: sub (2, 7) = "SELECT" then
Local max_conns =-1
Local max_conns_ndx = 0

For I = 1, # proxy. servers do
Local s = proxy. servers [I]

-- Select a slave database with idle connections
If s. type = proxy. BACKEND_TYPE_RO and
S. idling_connections> 0 then
If max_conns =-1 or
S. connected_clients <max_conns then
Max_conns = s. connected_clients
Max_conns_ndx = I
End
End
End

-- We found a slave database with idle connections.
If max_conns_ndx> 0 then
Proxy. connection. backend_ndx = max_conns_ndx
End
Else
-- Send to primary database
End

Return proxy. PROXY_SEND_QUERY
Jan warned that this technique can also be used to implement other data distribution policies, such as Sharding)