How to install and test MySQL Proxy Lua

MySQL Proxy LuaThe installation and testingMySQL ProxyIt is a simple program between your client and MySQL server. It can monitor, analyze, or change their communication. It is flexible and has no restrictions. Its common uses include load balancing, faults, query analysis, query filtering and modification.

MySQL Proxy is such a middle-layer Proxy. In short, MySQL Proxy is a connection pool that forwards connection requests from foreground applications to the background database.LuaScripts can implement complex Connection Control and filtering to achieve read/write splitting and load balancing. For applications, MySQL Proxy is completely transparent, and the application only needs to connect to the listening port of MySQL Proxy. Of course, the proxy machine may become a single point of failure, but multiple proxy machines can be used as redundancy. You can configure the connection parameters of multiple proxies in the connection pool configuration of the application server.

One of the more powerful functions of MySQL Proxy is to implement "read/write splitting". The basic principle is to let the primary database process transactional queries and let the slave database process SELECT queries. Database Replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

From Baidu encyclopedia

I focus on Server Load balancer and failover. By working with MASTER-SLAVE replication and Server Load balancer (SLAVE) with MySQL, you can also implement 'read-write detaching '.

I. Installation

By referring to a bunch of documents on the Internet, the source code installation still fails. I will study it later. Turn to binary installation, including LUA5.1

The operating system is CentOS 5.5 32-bit. Download MySQL Proxy 0.8.2 from the official website.

Is:

 
 

  
  
 # wget -c http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.2-linux-rhel5-x86-32bit.tar.gz/
  
  
 from/ftp://ftp.stu.edu.tw/pub/Unix/Database/Mysql/  
  
  
# tar mysql-proxy-0.8.2-linux-rhel5-x86-32bit.tar.gz -C /usr/local  
  
  
# mv  mysql-proxy-0.8.2-linux-rhel5-x86-32bit   mysql-proxy 
 
 

Add path

 
 

  
  
 #vim ~/.bash_profile  
  
  
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin  
  
  
export PATH 
 
 

Ii. Configuration

Download the Written service script:

 
 

  
  
#wget -c http://customcode.googlecode.com/files/mysql-proxy  
  
  
# cp mysql-proxy   /etc/init.d/mysql-proxy 
 
 

Modify

 
 

  
  
# vim /etc/init.d/mysql-proxy  
  
  
PROXY_PATH=/usr/local/mysql-proxy/bin 
 
 

Change to unix Mode

 
 

  
  
:set fileformat=unix 
  
  
# chmod 755 /etc/init.d/mysql-proxy    
  
  
# chkconfig mysql-proxy on   
 
 

Modify configuration file

 
 

  
  
# vi /etc/sysconfig/mysql-proxy    
  
  
# options to mysql-proxy   
  
  
# do not remove --daemon   
  
  
PROXY_OPTIONS="--proxy-backend-addresses=192.168.234.133:3306 --proxy-backend-addresses=192.168.234.132:3306  --daemon" 
 
 

For other configurations, refer to the official documentation.

 
 

  
  
http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-configuration.html 
 
 

Grant permissions to two mysql instances

 
 

  
  
grant all on *.* to "root" @ "192.168.234.131";  
  
  
flush privileges; 
 
 

Start the service

 
 

  
  
service mysql-proxy start 
 
 

Iii. Test

1) Load Balancing/etc/sysconfig/mysql-proxy is configured

 
 

  
  
PROXY_OPTIONS="--proxy-backend-addresses=192.168.234.133:3306 --proxy-backend-addresses=192.168.234.132:3306  --daemon" 
 
 

The Database Server creates the same table t1 with different data,

 
 

  
  
use test;  
  
  
CREATE TABLE `t1` (  
  
  
 `iCode` int(11) NOT NULL AUTO_INCREMENT,  
  
  
  `name` varchar(50) DEFAULT NULL,  
  
  
    PRIMARY KEY (`iCode`)  
  
  
);  
  
  
132  
  
  
insert into t1 (name) values ('master');  
  
  
133  
  
  
insert into t1 (name) values ('slave'); 
 
 

Open multiple windows and run

 
 

  
  
mysql -h 192.168.234.131 -u  -p -P 4042 -e"select  name from test.t1;" 
 
 

You can see different results. mysql-prxoy will allocate the query to the mysql server.

2) failover

Use 1 configuration to stop mysql on 132

 
 

  
  
service mysqld stop 
 
 

Run:

 
 

  
  
mysql -h 192.168.234.131 -u  -p -P 4042 -e"select  name from test.t1;" 
 
 

You can still query data normally. Only data on 133MySQL can be queried.

3) read/write splitting

Read/write splitting depends onLUAScript, the configuration file is changed:

 
 

  
  
PROXY_OPTIONS="--proxy-backend-addresses=192.168.234.133:3306 --proxy-read-only-backend-addresses=192.168.234.132:3306 \  
  
  
 
  
  
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  --keepalive  --daemon" 
 
 

133 is writable, 132 is read-only, use rw-splitting.lua for read/write splitting scripts

You also need to configure masternodes)-Slave132) for replication.

Summary: DetailsMySQL Proxy LuaThe content of the installation and testing tutorials has been introduced. I hope this article will help you!