Enterprise-mysql read-write Separation (proxy)

MySQL Proxy

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

MySQL Proxy is such a middle-tier agent. To put it simply, MySQL Proxy is a connection pool, which is responsible for forwarding connection requests from foreground applications to the background database. Through the use of lua scripts, complex connection control and filtering can be achieved to achieve read-write separation and load balancing. For applications, MySQL Proxy is completely transparent, and applications only need to connect to the listening port of MySQL Proxy. Of course, the proxy machine may become a single point of failure, but you can use multiple proxy machines as redundancy and configure the connection parameters of multiple proxy in the connection pool configuration of the application server.

One of the more powerful features of MySQL Proxy is to achieve "read-write separation". The basic principle is to let the master database handle transactional queries and the slave libraries to handle SELECT queries. Database replication is used to synchronize changes caused by transactional queries to 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

one。 Install mysql-proxy and configure

Extract to the specified path

[root@server3~] # tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-C / usr/local

Change the name

[root@server3local] # mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

Build a catalogue

[root@server3 mysql-proxy] # mkdir etc

[root@server3 mysql-proxy] # mkdir logs

Write configuration file

[root@server3 etc] # vim mysql-proxy.conf

Add authority

[root@server3 etc] # chmod 660 mysql-proxy.conf

Change the parameters

[root@server3 mysql-proxy] # vim rw-splitting.lua

[root@server3 mysql-proxy] # pwd

/ usr/local/mysql-proxy/share/doc/mysql-proxy

Run the script to start the service

[root@server3bin] # / 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, the host master is authorized (192.168.122.11)

Mysql > grant select, update, insert on *. * to proxy@'192.168.122.%' identified by 'LH=redhat123'

(slave will synchronize authorization)

Log in to the proxy host

[root@server3] # mysql-uadmin-pwestos-P 4041-h 192.168.122.13

Check mysql master and standby status

Remotely log in to the database on the client

[root@server4] # mysql-h 192.168.122.13-uproxy-pLH=redhat123

Write data

View on the proxy host

(master status has changed)

Log in on the client again

[root@server5] # mysql-h 192.168.122.13-uproxy-pLH@redhat123

View proxy

Third, check whether data separation is achieved.

In order to clearly see the status of data transmission

[root@server3 ~] # yum install tcpdump

[root@server3 ~] # yum install-y lsof