Using Oneproxy to deploy MySQL database for read-write separation

Experimental system: CentOS 6.6_x86_64

Lab Prerequisites: Firewall and SELinux are off

The experiment shows that there are 4 hosts in this experiment, such as the topology of IP assignment

Experimental software: mariadb-10.0.20 oneproxy-rhel5-linux64-v5.6-ga

: Http://pan.baidu.com/s/1jGpL2o2

Experimental topology:

　　　　

Note: This experiment is an extension of the previous Mysql-proxy experiment, so most of the environment and settings are the same, of which one or two steps are not repeated here, please refer to: using Mysql-proxy to read and write the MySQL database

First, the preparatory work

Second, configure master-slave replication

Third, installation Oneproxy

1. In this experiment 19.79 is the Onproxy server, so the software is installed on this host:

TarXF oneproxy-rhel5-linux64-v5.6-ga.Tar. gz-c/usr/local/CD/usr/local/Oneproxyvim Demo.SH---------------------------------------------------------------->#/bin/Bash#export oneproxy_home=/usr/local/oneproxy# Valgrind--leak-check=full--show-reachable=Yes ${oneproxy_home}/oneproxy--keepalive \ //automatic check and restart Oneproxy service --proxy-address=192.168.19.79:3306\//proxy Server First listener address
--admin-address=192.168.19.79:4041\  //Management port address
  --proxy-master-addresses=192.168.19.66:3306@server1 \/ / 
      Master node address (writable node), where @ is followed by the name of "Server Group" and, if not specified, defaults to "default" --proxy-slave-addresses=192.168.19.74:3306@server1 \ //slave node address (readable node) --proxy-slave-addresses=192.168.19.76:3306@server1--proxy-user-list=test/14b1772ba9874974e6ecea0745ee774930e2efa3 \/ / 
       Proxy user list (username/password), after completion of the 2nd step back to configure   --proxy-part-tables=${oneproxy_home}/part.txt \
--proxy-found-rows--event-threads=4\//oneproxy number of worker threads
--proxy-group-policy=server1:2 \//Define a routing access policy for the MySQL cluster, which is configured to read from slave and read from the master if the slave side is not available
--proxy-group-security=server1:0\ //Define secure access policy for MySQL cluster   --proxy-memory-db=root/@::test \
--proxy-memory-engine--proxy-sql-autosave=${oneproxy_home}/Sql_firewall.sql--log-file=${oneproxy_home}/Oneproxy.log--pid-file=${oneproxy_home}/oneproxy.pid

2. View the password characters and configure:

chmod+x Demo.SH ./demo.SHMySQL-uadmin-poneproxy-h192.168.19. ---port=4041                   //access to the management interface-------------------------------------------------------------------->passwd 'Redhat';//view password characters and write to demo.sh+--------+------------------------------------------+| TEXT | PASSWORD |+--------+------------------------------------------+| Redhat | 14b1772ba9874974e6ecea0745ee774930e2efa3 |+--------+------------------------------------------+

3. Create a test user on master:

/usr/local/mysql/bin/mysql----------------------------------------------->'  Test'@'192.168.19.%'redhat';      //To be consistent with the Proxy user list information in demo.sh  FLUSH privileges;

4. Go back to the Oneproxy server startup process:

killall -9 oneproxy           // Clean up the previous process first ./demo.  SHTail -F oneproxy.log          // View Log

　　　　

mysql-uadmin-poneproxy-h192.168.19. ---port=4041----------------------------------------------------------------->LIST backend;+------+--------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+ ---------+-------+---------+-------+---------+------+------+---------+| INDX | ADDRESS | TYPE | STATUS | MARKUP | Requests | degree | GROUP | Is_m | is_s | Mfile | Moffset | Dfile | Doffset | Rfile | Roffset | IO | SQL | Seconds |+------+--------------------+-----------+--------+--------+----------+--------+---------+------+------+ -------+---------+-------+---------+-------+---------+------+------+---------+|1|127.0.0.1:3306| Rw/master | Up |0|0|0| | No |  No |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL | NULL | |2|192.168.19.66:3306| Rw/master | Up |1|0|0| Server1 | No |  No |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL | NULL | |3|192.168.19.74:3306| Ro/slave | Up |1|0|0| Server1 | No |  No |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL | NULL | |4|192.168.19.76:3306| Ro/slave | Up |1|0|0| Server1 | No |  No |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL | NULL |+------+--------------------+-----------+--------+--------+----------+--------+---------+------+------+--- ----+---------+-------+---------+-------+---------+------+------+---------+

　　　　

5. Capture packets on three MARIADB servers, respectively:

Master

192.168. 19.66 3306

SLAVE1:

192.168. 19.74 3306

Slave2:

192.168. 19.76 3306

6. Create and read the database on the Oneproxy:

mysql-utest-predhat-h192. 168.19.  -       // Connect to Oneproxy

Database commands are not written here, as you can see from the picture, all writes are performed on master, and the read operation is load balanced to slave.

　　　　

　　　　

　　　　

　　　　

　　　　

　　　　

So far, the experiment is successful, thank you! If you have any questions, please contact me, qq:82800452

Using Oneproxy to deploy MySQL database for read-write separation