Using Mysql-proxy for read-write separation

Mysql-proxy implementing MySQL read-write separation installation configuration using binary format Mysql-proxy

• Mysql-proxy itself is not able to read and write separation, to achieve read and write separation to rely on LUA, so first check whether LUA is installed, if not installed using Yum install installed

  ~]# rpm -q lualua-5.1.4-4.1.el6.x86_64

• Create a System user

  useradd -r mysql-proxy

• Unzip the package to create a linked file

  tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

• Export Binary Program

  ~]# vim /etc/profile.d/mysql-proxy.sh内容为：export PATH=/usr/loca/mysql-proxy/bin/:$PATH~]# source  /etc/profile.d/mysql-proxy.sh

• View mysql-proxy usage

    ~]# mysql-proxy--help-all# The more important options are the following--daemon #让mysql-proxy work in the background--user= <user> #指定运行mysql Master--proxy-backend-addresses= 

• Enable Mysql-proxy

  ~]# mysql-proxy --daemon > --user=mysql-proxy > --log-level=debug > --log-file=/var/log/mysql-proxy.log > --plugins=proxy > --plugins=admin > --proxy-backend-addresses=172.25.78.2:3306 > --proxy-read-only-backend-addresses=172.25.78.3:3306 > --keepalive=true > --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua > --admin-username=admin > --admin-password=adminpass > --admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua

• To view the ports that are enabled

  ]# netstat -tan | grep :40tcp        0      0 0.0.0.0:4040                0.0.0.0:*                   LISTEN      tcp        0      0 0.0.0.0:4041                 0.0.0.0:*                   LISTEN#4041是admin管理接口的端口，4040是mysql-proxy的端口

Connection test

• Connect to the management interface on the proxy server (172.25.78.4) to see which back-end servers are

  mysql -uadmin -padminpass -h172.25.78.4 --port=4041MySQL [(none)]> SELECT * FROM backends;+-------------+------------------+---------+------+------+-------------------+| backend_ndx | address          | state   | type | uuid | connected_clients |+-------------+------------------+---------+------+------+-------------------+|           1 | 172.25.78.2:3306 | unknown | rw   | NULL |                 0 ||           2 | 172.25.78.3:3306 | unknown | ro   | NULL |                 0 |+-------------+------------------+---------+------+------+-------------------+

• To add a test user on the master node and the slave node

  MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE ON *.* TO ‘test‘@‘192.168.182.%‘ IDENTIFIED BY ‘testpass‘;

• Test on the client

    ~]# mysql-utest-ptestpass-h192.168.182.131-p4040mariadb [(none)]> SHOW DATABASES; +--------------------+| Database |+--------------------+| Information_schema | | MYDB | | S_sc_c | | Db_user | | Discuz | | Dvwa | | Hello | | MySQL | | Performance_schema | | Test | | TestDB |+--------------------+mariadb [(none)]> use S_sc_c; MariaDB [s_sc_c]> INSERT into S_1 (sname,sdept) VALUES (' H ', ' AA '); MariaDB [s_sc_c]> SELECT * from s_1;+-----+-------+-------+| Sid | sname |   Sdept |+-----+-------+-------+| 1 | HELLO |   A | | 2 | HE |   A | | 3 | H | AA |+-----+-------+-------+  

• View on Master

  MariaDB [S_SC_C]> SELECT * FROM S_1;+-----+-------+-------+| sid | sname | sdept |+-----+-------+-------+|   1 | HELLO | A     ||   2 | HE    | A     ||   3 | H     | AA    |+-----+-------+-------+

• View on slave

  MariaDB [S_SC_C]> SELECT * FROM S_1;+-----+-------+-------+| sid | sname | sdept |+-----+-------+-------+|   1 | HELLO | A     ||   2 | HE    | A     ||   3 | H     | AA    |+-----+-------+-------+

Using Mysql-proxy for read-write separation