Installing MySQL Proxy On CentOS 5 (FINAL) x86_64
This tutorial explains how you can install MySQL Proxy on a CentOS 5 (x86_64) system. MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.
On a fresh minium Centos 5 final x86_64 install:
yum install gcc.x86_64 libevent.x86_64 libevent-devel.x86_64 readline.x86_64 readline-devel.x86_64 ncurses.x86_64 ncurses-devel.x86_64 glib2.x86_64 glib2-devel.x86_64
cd /usr/local/src/
wget
http://www.lua.org/ftp/lua-5.1.3.tar.gztar zxvf lua-5.1.3.tar.gz
cd lua-5.1.3
make linux
make install
wget
http://dev.mysql.com/get/Downloads/MySQL-Cluster-6.2/mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/tar xzvf mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23.tar.gz
ln -s mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23 mysql
PATH=$PATH:/usr/local/mysql/bin
export PATH
Edit your .profile to make this permanent:
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:/usr/local/mysql/bin:$HOME/bin
export PATH
unset USERNAME
wget
http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.1.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/tar zxvf mysql-proxy-0.6.1.tar.gz
cd mysql-proxy-0.6.1
./configure LDFLAGS="-lm -ldl" LUA_CFLAGS="-I/usr/local/include/" LUA_LIBS=/usr/local/lib/liblua.a
make
make install
Let's create a sample LUA script so you can see some logs.
mkdir /var/log/mysql-proxy/
mkdir -p /usr/local/mysql/lua-scripts/
vi /usr/local/mysql/lua-scripts/simple-log.lua
(see:
http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=3Script modified to get IP and to use proxy.connection.server.thread_id.)
local log_file = '/var/log/mysql-proxy/mysql.log'
local fh = io.open(log_file, "a+")
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)
fh:write( string.format("%s %6d -- %s :IP %s :USER: %s\n",
os.date('%Y-%m-%d %H:%M:%S'),
proxy.connection.server.thread_id,
query,
proxy.connection.client.address,
proxy.connection.client.username))
fh:flush()
end
end
Now start up your proxy using the variable --proxy-backend-addresses to point the proxy at your servers.
/usr/local/sbin/mysql-proxy --proxy-lua-script=/usr/local/mysql/lua-scripts/simple-log.lua --proxy-backend-addresses=192.168.1.33:3306 --proxy-backend-addresses=192.168.1.34:3306 --daemon
192.168.1.33 and 192.168.1.34 are the MySQL nodes that the proxy will be connecting to.
Allow connections for the proxy through your firewall:
### ALLOWED TO CONNECT TO MYSQL PROXY
###
### LOCAL ADMINS
-A INPUT -s SRC-IP -d DST-IP -p tcp -m state --state NEW -m tcp --dport 4040 -j ACCEPT
Where DST-IP is my proxy server and SRC-IP is my local box (client machine).
Now from your local box (not the mysql-proxy server) try and connect to the backend databases through the proxy ( user with relevent permissions must exist in the db).
mysql -u dba_admin -p -h PROXY-SERVER -P 4040
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.1.23-ndb-6.2.15
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Imap_Forms |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> quit
Bye
N.B. The proxy uses the port 4040 instead of 3306.
Test the mysql-proxy admin interface from the mysql-proxy server:
mysql -u root -p -h 127.0.0.1 -P 4041
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
+------+--------+-------+------+
3 rows in set (0.00 sec)
mysql>quit
bye
ที่มา :
http://www.howtoforge.com/installing-mysql-proxy-on-centos5-x86_64แนะนำ อีกทางเลือก
หัวข้อ: MySQL Proxy On CentOS 5 (อ่าน 14046 ครั้ง)