Using Mysql-proxy for MySQL read-write separation

650) this.width=650; "src=" Http://note.youdao.com/yws/res/20877/3864DBFB8D264513BA966AF24160FCE4 "alt=" Qq20150726194151.png "/>

static Detach: Writes the server address directly to the program

Advantages:
Disadvantages:
Dynamic separation: Read and write operations through a proxy server, the proxy server determines read and write operations, writes data on the primary server, and reads data from the server.

(the proxy server does not need to install MySQL)

#=======================================================

(1) using Mysql-proxy for Read and write separation

# iptables-f

( one for each master,slave,proxy,client server)

Note:(First, master-slave replication is configured.) It then begins to use Mysql-proxy for read-write separation on the proxy server. Finally in the client test )

650) this.width=650; "src=" Http://note.youdao.com/yws/res/20497/6506BFE7C3884C57B8E0A9FADCD62FE6 "alt=" Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz "title=" mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz "id= "userfile_d1akkn78a"/>

actions performed by the proxy server (10.0.199.3) :

# TAR-XVF Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

# mv Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

# vim/etc/ld.so.conf

/usr/local/mysql-proxy/lib (Import)

#  Cd/usr/local/mysql-proxy/bin

#./mysql-proxy--proxy-backend-addresses=10.0.199.1 : 3306--proxy-read-only-backend-
addresses=10.0.199.2:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua- -daemon--user=mysql--log-level=warning--log-file=/var/log/mysql-proxy.log--max-open-files=2048--event-threads= 4--proxy-address=10.0.199.3: 3306

Explanation:

--proxy-backend-addresses Specify the primary server address (write data) 10.0.199.1

--proxy-read-only-backend-addresses specified from the server address (read data) 10.0.199.2
--proxy-lua-script specifying scripts for read-write separation
--daemon Specifies to run as daemon
--user=mysql Specifies the user to run
--log-level specifying logging levels
--log-file specifying the log file location
--max-open-files Specifies the maximum number of files opened by the class, limited by the operating system
--event-threads Set the number of worker threads
--proxy-address Setting the native address and port 10.0.199.3 of the Listener (proxy server)

 

# lsof-i:3306 See if it starts

Operations performed by the master/slave server (199.1, 199.2):

# iptables-f

The proxy server is authorized on the master-slave server so that the proxy server can use this user on the master-slave server to connect the master and slave.

mysql> Grant All on * * to ' repuser ' @ ' 10.0.199.3 ' identified by ' Aixoc m ';

mysql> flush Privileges;

Repuser is any user name required to create the user on the proxy server

because the following tests are performed on the client, the authorized user is required to the user logs on to the operations database mysql > CReate user repuser;

operations performed by the client (10.0.199.4) :

Test:
the client does not have to install MySQL, but must have MySQL command
# yum install MySQL
# mysql-u repuser-h 10.0.199.3-paixocm-e "select * from Sxkj.stu"
# mysql-u repuser-h 10.0.199.3-paixocm-e "CREATE TABLE Sxkj.class (id int Primary Key,name varchar (Ten) NOT NULL)"
You can also go to MySQL to operate
view master and slave server data

The following error occurs when testing:

650) this.width=650; "src=" Http://note.youdao.com/yws/res/20699/0C8941BA7BE748CF84131C6CA1549EDF "alt=" Qq20150724175503.png "/>

Workaround:

:mysql> Select User,host,password from Mysql.user on the master-slave server;

mysql> drop user [email protected]; And then re-authorize.

This article is from the "Zhanglulu" blog, make sure to keep this source http://zhanglulu913.blog.51cto.com/10592685/1688947

Using Mysql-proxy for MySQL read-write separation