Using oneproxy to deploy the read/write splitting of the mysql database, oneproxymysql

Using oneproxy to deploy the read/write splitting of the mysql database, oneproxymysql

Experimental System: CentOS 6.6 _ x86_64

Prerequisites: Firewall and selinux are both disabled.

Tutorial Description: In this experiment, there are a total of four hosts, with IP addresses allocated as topology.

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

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

Tutorial topology:

　　　　

Note: This experiment is an extension of the mysql-proxy experiment. Therefore, most environments and settings are the same. Steps 1 and 2 are not repeated here. For details, see: use mysql-proxy for mysql database read/write splitting

I. Preparations

Ii. Configure master-slave Replication

3. Install oneproxy

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

Tar xf oneproxy-rhel5-linux64-v5.6-ga.tar.gz-C/usr/local/cd/usr/local/oneproxyvim demo. sh success> #/bin/bash # export ONEPROXY_HOME =/usr/local/oneproxy # valgrind -- leak-check = full -- show-reachable = yes \$ {ONEPROXY_HOME}/oneproxy -- keepalive \ // automatically checks and restarts the OneProxy service -- proxy-address = 192.168.19.79: 3306 // The first listening address of the Proxy Server
-- Admin-address = 192.168.19.79: 4041 // manage the 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". If not specified, the default value is "default" -- proxy-slave-addresses = 192.168.19.74: 3306 @ server1 \ // Slave Node Address (read node) -- proxy-slave-addresses = 192.168.19.76: 3306 @ server1 \ -- proxy-user-list = test/Users \//
 Proxy user list (User Name/password). After completing step 2, you will be back to configure -- proxy-part-tables =$ {ONEPROXY_HOME}/part.txt \
-- Proxy-found-rows \ -- event-threads = 4 \ // Number of worker threads of OneProxy
-- Proxy-group-policy = server1: 2 \ // defines the routing access policy of the MySQL cluster. The configuration here is to read from the Slave. If the Slave end is unavailable, it will be read from the Master end.
-- Proxy-group-security = server1: 0 \ // defines the security access policy of the 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 and configure the password characters:

Chmod + x demo. sh. /demo. sh mysql-uadmin-pOneProxy-h192.168.19.79 -- port = 4041 // enter the management interface response> passwd 'redhat'; // view the password and write it to the demo. sh + -------- + ------------------------------------------ + | TEXT | PASSWORD | + -------- + ---------------------------------------- + | redhat | expires | + -------- + expires +

3. Create a test user on the master:

/Usr/local/mysql/bin/mysql -----------------------------------------------> grant all on *. * TO 'test' @ '192. 168.19.% 'identified BY 'redhat'; // here it is used with demo. the Proxy user list information in sh must be consistent. flush privileges;

4. Return to the oneproxy server to start the process:

Killall-9 oneproxy // first clear the previous process./demo. sh tail-f oneproxy. log // view the log

　　　　

mysql -uadmin -pOneProxy -h192.168.19.79 --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 the three mariadb servers respectively:

Master:

tcpdump -i eth0 -nn -XX ip dst 192.168.19.66 and tcp dst port 3306

Slave1:

tcpdump -i eth0 -nn -XX ip dst 192.168.19.74 and tcp dst port 3306

Slave2:

tcpdump -i eth0 -nn -XX ip dst 192.168.19.76 and tcp dst port 3306

6. Create and read databases on oneproxy:

Mysql-utest-predhat-h192.168.19.79 // connect to oneproxy

Database commands are not written here. You can see from the image that all write operations are performed on the master, and read operations are balanced to the slave.

　　　　

　　　　

　　　　

　　　　

　　　　

　　　　

Now, the experiment is successful. Thank you! If you have any questions, contact me at QQ: 82800452.