A tutorial on the separation of read and write in the use of Mysql _mysql

Mysql-proxy to realize read-write separation
MySQL Proxy is a simple program between your client side and the MySQL server side that can monitor, analyze, or change their communications. It uses flexible, unrestricted, common uses including load balancing, failure, query analysis, query filtering and modification, and so on.
MySQL Proxy is such a middle-tier proxy, simply put, the MySQL proxy is a connection pool, responsible for the front-end application of the connection request to the background of the database, and by using LUA script, can achieve complex connection control and filtering, so as to achieve read-write separation and load balancing. For applications, MySQL proxy is completely transparent, the application only need to connect to the MySQL proxy listening port. Of course, this proxy machine may become a single point of failure, but it is possible to use more than one proxy machine as redundancy, in the application server's connection pool configuration to multiple proxy connection parameters.
One of the more powerful features of MySQL proxy is "read and write Separation," the rationale being to have the primary database process transactional queries and to process select queries from the library. Database replication is used to synchronize changes caused by transactional queries to the libraries in the cluster.

1. Installation Mysql-proxy
Download the installation package here http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/
If the build installation dependency pack has Libevent2 1.x lua 5.1.x GLIBC2 2.6.0 pkg-config libtool 1.5
Here using GLIBC binary package decompression can be 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 a good MySQL master and slave copy

Master  192.168.216.133:3306
slave   192.168.216.132:3306

3. Configure Mysql-proxy
Create Mysql-proxy configuration file, all selections in the configuration file cannot be quoted

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

[Mysql-proxy]
 Daemon=true            #以后台守护进程方式启动
 keepalive=true           #当进程故障后自动重启
 log-level=debug         #设置日志级别为debug, can be changed into info
 log-file=/var/log/mysql-proxy.log           #设置日志文件路径
 basedir=/usr/local/mysql-proxy            when debugging is complete Set up Mysql-proxy's home directory
 proxy-address=192.168.216.132:4040     #指定mysql-proxy's listening address
 proxy-backend-addresses=192.168.216.133:3306                   #设置后台主服务器
 proxy-read-only-backend-addresses= 192.168.216.132:3306          #设置后台从服务器
 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/ Rw-splitting.lua   #设置读写分离脚本路径
 admin-address=192.168.216.132:4041    #设置mysql-proxy admin address, need parent Admin Plugin
 admin-username=admin                 #设置登录管理地址用户
 admin-password=admin                  #设置管理用户密码
 admin-lua-script=/ Usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua   #设置管理后台lua脚本路径, change the script default does not want to automatically define

After you configure mysql-proxy.conf, you need to ensure that the file's permissions are 600 and that you include a LUA script
Start mysql-proxy with configuration file

/usr/local/mysql-proxy/bin/mysql-proxy--plugins=proxy--plugins=admin--defaults-file=mysql-proxy.conf
 -- Plugins=proxy    #指定proxy插件, this configuration write configuration file cannot start
 --plugins=admin    #指定admin插件
 --defaults-file= mysql-proxy.conf      #指定配置文件

4. Start test

Login Admin Address View current status

mysql-uadmin-padmin-h192.168.216.132-p4041

Two back-end servers are currently unknown because no users are connected to the backend via Mysql-proxy

Mysql-proxy does not authenticate the user, but the authentication is delivered to the back-end server for authentication, so it is necessary to open permissions on the Mysql-proxy on the back-end server

The following is a 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 th En set_error ("[admin] we 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 * F Rom 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 does local states = {"Unknown", ' Up ', ' down '} loca L types = {"Unknown", "RW", "RO"} local B = proxy.global.backends[i] rows[#rows + 1] = {i, B.dst.name,--C
 Onfigured Backend AddressStates[b.state + 1],--the C-id is pushed down starting at 0 Types[b.type + 1],--the C-id was 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 * "*" * "to", "Supported command S ") 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, related issues to solve
(1), if the log prompts (debug) [network-mysqld.c:1134]: Error on a connection (FD:-1 event:0). Closing client connection.

You can modify the Min_idle_connections = 4 and max_idle_connections = 8 of the Rw-splitting.lua to make it larger

(2), if encountered garbled need to adjust back-end MySQL settings of the character set

[Mysqld]
 Skip-character-set-client-handshake
 init-connect      = ' Set NAMES UTF8 '
 character_set_server  = UTF8

Mysqlnd_ms to realize MySQL read-write separation
Mysqlnd_ms is a MYSQLND plug-in, which realizes the function of saving and switching, load balancing, and read and write separation. To use the Mysqlnd_ms read-write detach feature, you must use –WITH-MYSQLND when you install PHP. MYSQLND implementation of the function is that you do not need to install MySQL on the PHP server, before php5.3 compile and install PHP need to specify the MySQL installation path via –with-mysql=/path/to/mysql.

1. Install 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

There are similar prompts to record that the following path requires a configuration php.ini

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 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 "
    }}}
}

This uses 1 main 2 MySQL servers from the
Filters is the policy to define access from the server, random is a random selection of a server, Strick parameter set to 1 refers to a single request to a server

4, testing
use WordPress for testing, edit profile wp-config.php

/** MySQL Host *
 /define (' Db_host ', ' MyApp '); defined in #这的myapp是在mysqlnd_ms_plugin. ini