Enterprise-MySQL read/write separation (proxy)

MySQL Proxy

MySQL Proxy is a simple program that is located between your client side and the MySQL server to monitor, analyze, or change their communications. It is flexible, unlimited, and common uses include: load balancing, failure, query analysis, query filtering and modification, and so on.

mysql Proxy is such a middle-tier agent, simply said, MySQL Proxy is a Span style= "FONT-SIZE:18PX;" > connection pool Read and write separation redundancy Application Server The connection pool configuration for

one of the more powerful features of MySQL Proxy is the realization of "read and write Separation", the basic principle is to let the primary database processing Transactions query, let the select query be processed from the library. Database replication is used to synchronize changes caused by transactional queries to the slave libraries in the cluster.

Experiment

Experimental environment

Server1 192.168.122.11 Master

Server2 192.168.122.12 Slave

Server3 192.168.122.13 Proxy

I. Installing the MYSQL-PROXY and configuring

2. Unzip to the specified path

   [Email protected]~] #tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-c/usr/local

4. Change the name
   [[Email protected]]# mysql-proxy-0.8.5-linux-el6-x86-64bit/mysql-proxy

6. Build Catalog

   [Email protected] mysql-proxy]# mkdir etc
   [Email protected] mysql-proxy]# mkdir logs

8. Write config file

   [Email protected] etc]# vim mysql-proxy.conf

10. Plus permissions

    [Email protected] etc]# chmod 660 mysql-proxy.conf

12. Parameter change

    [Email protected] mysql-proxy]# vim Rw-splitting.lua
    [Email protected] mysql-proxy]# pwd
    /usr/local/mysql-proxy/share/doc/mysql-proxy

14. Run the script to open the service

    [Email protected]]#/usr/local/mysql-proxy/bin/mysql-proxy--plugins=admin--plugins=proxy--defaults-file=/usr/ Local/mysql-proxy/etc/mysql-proxy.conf

    Check Port

    Netstate-antlup

    
    

Second, host Master for authorization (192.168.122.11)

Mysql>grant Select, UPDATE, insert on *. * to [email protected] ' 192.168.122.% ' identified by ' lh=redhat123 ';

(Slave will be authorized synchronously)

Login in Proxy Host

[Email protected] ~]# mysql-uadmin-pwestos-p 4041-h 192.168.122.13

View MySQL master, standby status

Telnet to the database on the client

[Email protected] ~]# mysql-h 192.168.122.13-uproxy-plh=redhat123

Write Data

View in Proxy host

(Master status has changed)

Log on again at the client

[Email protected] ~]# mysql-h 192.168.122.13-uproxy [email protected]

View Proxy

Third, check whether to achieve data separation

To see the data transfer status clearly

[email protected] ~]# Yum install tcpdump

[email protected] ~]# Yum install-y lsof

Enterprise-MySQL read/write separation (proxy)