Read/write splitting practices: mysql

[System Environment] ubuntu12.0464bit [STEP] Download mysql-proxyftp: mirror.switch.chmirrormysqlDownloadsMySQL-Proxymysql-proxy-0.8.4.tar.gz installation dependency package apt-getinstalllibevent-devapt-getinstalllua5.1-devapt-getinstalllibglib2.0-dev Solution

[System Environment] ubuntu12.04 64bit [step] Download mysql-proxy ftp://mirror.switch.ch/mirror/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4.tar.gz installation dependency package apt-get install libevent-dev apt-get install lua5.1-dev apt-get install libglib2.0-dev Solution

[System environment]

Ubuntu12.04 64bit

[STEP]

Download mysql-proxy

Ftp://mirror.switch.ch/mirror/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4.tar.gz

Install dependency packages

Apt-get install libevent-dev

Apt-get install lua5.1-dev

Apt-get install libglib2.0-dev

Decompress mysql-proxy-0.8.4.tar.gz to get the mysql-proxy-0.8.4 and enter the Directory

./Configure -- prefix =/usr/local/mysql-proxy -- with-mysql =/usr/local/mysql/

Note that the compilation depends on mysql, so install mysql first, otherwise the compilation fails.

Make & make install

Next, copy the lua script to the installation directory.

Cp lib/rw-splitting.lua/usr/local/mysql-proxy/

Modify the rw-splitting.lua content and find the following

-- Connection pool
If not proxy. global. config. rwsplit then
Proxy. global. config. rwsplit = {
Min_idle_connections = 4,
Max_idle_connections = 8,


Is_debug = false
}
End

Replace "4" and "8" with "1", because read/write splitting is enabled only when the client connection is greater than the configured 4. Otherwise, the client reads and writes data from the master. After the value is changed to "1", there are several connections on the client, read/write splitting takes effect after several queries.

Cp lib/admin. lua/usr/local/mysql-proxy/

Create the/usr/local/mysql-proxy/mysql-proxy.cnf configuration file in the installation directory, as shown below

[Mysql-proxy]
Admin-username = test
Admin-password = mima
Keepalive = true
Proxy-backend-addresses = 192.168.1.101: 3306
Proxy-read-only-backend-addresses = 192.168.1.93: 3306
Proxy-lua-script =/usr/local/mysql-proxy/rw-splitting.lua
Admin-lua-script =/usr/local/mysql-proxy/admin. lua
Log-file =/data/log/mysql-proxy.log
Log-level = debug

The username used by admin-username to access the proxy. This requires that all backend databases use the same set of username and password for access.

Admin-password

It is very useful to find a parameter in keepalive to keep mysql-proxy disconnected and reconnected.

Proxy-backend-addresses master db (master) Address, readable and writable

Proxy-read-only-backend-addresses from db (slave) Address, read-only

Proxy-lua-script: lua script address for executing read/write splitting

Admin-lua-script used to verify the user name and password

Log-file log address

Log-level log level. debug indicates debugging.

Start

/Usr/local/mysql-proxy/bin/mysql-proxy -- defaults-file =/usr/local/mysql-proxy/mysql-proxy.cnf &

Test

Mysql-utest-pmima-P4040

The default port is 4040.

After the connection, you can test the SQL command.

[Read/write splitting]

To see the effect of read/write splitting, if replication synchronization exists between the master and slave databases, it needs to be switched off. Otherwise, the master database will be synchronized to the slave immediately after modification, so that the differences between the two databases are not seen, you do not know the database from which you read the data.

Make sure that the synchronization has been disabled and the client has established several more connections. In this way, the master node is connected by default at the beginning, and the subsequent connection query will be forwarded to slave. Try to modify the value of a database table, and then query the value. If you check the value several times, you will find that the value you modified has not been modified because the database has been read.