Mysql-proxy Read/write separation

Mysql-proxy Read/write separation

Master-slave replication

MySQL replication can distribute master's data across multiple slave, and then use slave to share the read pressure of master.

Read/write separation

Mysql-proxy is a program between the MySQL database client and the server that supports embedding Lua. This proxy can be used to analyze, monitor, and transform communication data to support a very wide range of usage scenarios:

• Load balancing and failover processing;
• query analysis and log;
• SQL macros;
• Query rewriting;
• Execute shell command;
• Read-Write separation (master handles transactional queries; slave handles select queries);

Option settings for the Mysql-proxy command:

--admin-address=host:port            # mysql-proxy management port (default port is 4041)--proxy-address=host:port            # mysql-  Proxy listening port (the default port is 4040)--proxy-backend-addresses=host:port  # remote MySQL server address and port, you can set multiple -- proxy-read-only-backend-addresses=host:port          # remote read-only slave server address and port --proxy-skip-Profiling          # Turn off query parsing, by default --proxy-lua-script=file         # Specifies a LUA script to controlthe operation and setting of mysql-proxy --  Daemon                             # run --pid-file =                      filename # pid path in daemon mode

As a connection pool, MySQL Proxy is responsible for forwarding client connection requests to the backend database. With Lua scripting, complex connection controls and filtering can be implemented for read-write separation and load balancing.

For the client, the MySQL proxy is completely transparent, and the client only needs to connect to the listening port of MySQL proxy.

Read and write separations are implemented using the following LUA script:

-- Mysql-proxy/scripts/rw-splitting.lua -- Connection Pool if  not  Then  =1, --  default is 41, - - default is 8false< /c15>}End

Reference Documentation:

http://jan.kneschke.de/2007/8/1/mysql-proxy-learns-r-w-splitting/

Mysql-proxy Read/write separation