MySQL Proxy installation and basic command usage tutorial, mysqlproxy

MySQL Proxy installation and basic command usage tutorial, mysqlproxy

One of the most powerful features of MySQL Proxy is the end of "Read/Write Splitting )". The principle is to allow the primary database to process transactional queries, but to process SELECT queries from the database. Database Replication is used to synchronize changes caused by transactional queries to the slave database in the cluster.

0. Required software:

1. LUA

You can download dpa.nsysu.edu.tw/downloads/mysql-proxy/from LUA /.

You may download the source code from the MYSQL official website.

I downloaded it here:

Mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.0.tar.gz

3. REPLICATION of B and C was abolished during the test. In this way, the SQL statement shows where it comes from.

In case of a M-S (the ability to hold stop slave on SLAVE first)

1. Install
Download the compiled installation package or pre-compiled installation package. Here, use the pre-compiled version.

[@ S1.yejr.com ~] # Tar zxf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz [@ s1.yejr.com ~] # Cd mysql-proxy-0.6.0-linux-rhas4-x86 # You can see there are 2 directories [@ s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86] # lssbin share [@ s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86] # mv sbin/mysql-proxy/usr/local/sbin/[@ s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86] # ls sharemysql-proxy tutorial-constants.lua tutorial-packets.lua tutorial-rewrite.lua tutorial-warnings.luatutorial-basic.lua tutorial-inject.lua tutorial-query-time.lua tutorial-states.lua # Put the lua script under/usr/local/share, for him to [@ s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86] # mv share/mysql-proxy/usr/local/share/# delete spam Code such as symbolic connection [@ s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86] # strip/usr/local/ sbin/mysql-proxy

2. Start
Compile the startup management script:

[@s1.yejr.com ~]# vi /etc/init.d/mysql-proxy#!/bin/shexport LUA_PATH=/usr/local/share/mysql-proxy/?.luamode=$1if [ -z "$mode" ] ; then mode="start"ficase $mode in 'start')  mysql-proxy --daemon \--admin-address=:4401 \--proxy-address=:3307 \--proxy-backend-addresses=:3306 \--proxy-read-only-backend-addresses=192.168.133.232:3306 \--proxy-read-only-backend-addresses=10.10.74.61:3306 \--proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua  ;; 'stop')  killall mysql-proxy  ;; 'restart')  if $0 stop ; then   $0 start  else   echo "retart failed!!!"   exit 1  fi  ;;esacexit 0

Now let's explain the startup script:
-- Daemon is started in daemon mode.
-- Admin-address =: 4401 specifies the Management port of mysql proxy. Here, it indicates port 4401 of the local machine.
-- Proxy-address =: 3307 specifies the listener port of the mysql proxy, which can also be expressed as 127.0.0.1: 3307.
-- Proxy-backend-addresses =: 3306 specify the mysql host port
-- Proxy-read-only-backend-addresses = 192.168.1.1: 3306 specify the read-only mysql host port
-- Proxy-read-only-backend-addresses = 192.168.1.2: 3306 specify another read-only mysql host port
-- Proxy-lua-script =/usr/local/share/mysql-proxy/rw-splitting.lua specifies the lua script, where the rw-splitting script is used for read/write splitting

You can run the following command to view the complete parameters:

mysql-proxy --help-all

Run the following command to start/stop/restart mysql proxy:

[@s1.yejr.com ~]# /etc/init.d/mysql-proxy start[@s1.yejr.com ~]# /etc/init.d/mysql-proxy stop[@s1.yejr.com ~]# /etc/init.d/mysql-proxy restart

3. Trial

[@s1.yejr.com ~]# mysql -h127.0.0.1 -uroot -P3307

mysql> show processlist;

+-------+------+----------------+------+---------+------+-------+------------------+| Id  | User | Host      | db  | Command | Time | State | Info       |+-------+------+----------------+------+---------+------+-------+------------------+| 30052 | root | localhost:9656 | NULL | Query  |  0 | NULL | show processlist |+-------+------+----------------+------+---------+------+-------+------------------+

As you can see, a new connection is generated.

Test it with sysbench to see if it will fail:

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test prepare[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test run

..................Threads fairness:  events (avg/stddev):      10000.0000/0.00  execution time (avg/stddev):  23.0387/0.00

Fortunately, we didn't lose face to everyone. The rest of the tests should be done by ourselves :)

4. Miscellaneous
Mysql proxy can also implement the connection pool function, which is a weakness in many LAMP development. Therefore, with mysql proxy, you don't have to worry about the connection limit.
If you are using a rw-splitting.lua script, you 'd better modify the default values for the following two parameters:

min_idle_connections = 1max_idle_connections = 3

Articles you may be interested in:

• Mysql read/write splitting using mysql-proxy
• How to Set Up MySQL Proxy read/write separation in Ubuntu10
• How to solve the mysql proxy problem
• MySQL Proxy (another way to solve injection)