MySQL Proxy is a tool to sit between the client and database. It can load balance, redirect queries, track slow queries amongst other possible uses.
http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.html
http://forge.mysql.com/wiki/MySQL_Proxy
There is a ramp up within MySQL to get MySQL Proxy into a production ready product. So it is time to check out the product and see if the stated simple out-of-the-box functionality works.
Like most of the articles here, they are a mix of past experience and exploration. Some of these products are completely new to me like MySQL Cluster and MySQL proxy. Others like Oracle standbys and silent installs using response files are just putting my knowledge on the web so others can find the short cut to the solution they are searching for.
I ran MySQL Proxy 0.6 on MySQL 5.1 on CentOS 4.4 on Amazon EC2 on a small 32 bit instance.
Comments:
The main features I wanted to test was the ability to load balancing and also failover on the lost of one node. For these tests I ran MySQL proxy on the same server as the database. However there is nothing stopping you running them on the client (which could be a webserver) or on a dedicated machine.
The load balancing as explained by the documentation is a round robin by default. You can modify this behavior by passing your own script.
The failover was also seemless. It requires some more robusting testing with Mysqlslap running to see how it handles the switchover during load.
Install:
- wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz/from/http://mirrors.24-7-solutions.net/pub/mysql/
- tar -xzvf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
- cp mysql-proxy-0.6.0-linux-rhas4-x86/sbin/mysql-proxy /sbin
- cp -R mysql-proxy-0.6.0-linux-rhas4-x86/share/* /usr/share/
Running MySQL Proxy and testing failover and load balancing.
mysql-proxy --help
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module
Application Options:
-V, --version Show version
--daemon Start in daemon-mode
--pid-file= PID file in case we are started as daemon
mysql-proxy &
[1] 2861
ps -ef|grep mysql-proxy
root 2861 2795 0 01:36 ttyp0 00:00:00 mysql-proxy
root 2865 2795 0 01:37 ttyp0 00:00:00 grep mysql-proxy
Checking with nmap what ports are listening
nslookup `hostname`
Server: 172.16.0.23
Address: 172.16.0.23#53
Non-authoritative answer:
Name: domU-12-31-35-00-1D-B1.z-2.compute-1.internal
Address: 10.255.34.63
nmap 10.255.34.63
Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 01:40 EST
Interesting ports on domU-12-31-35-00-1D-B1.z-2.compute-1.internal (10.255.34.63):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
866/tcp open unknown
3306/tcp open mysql
Connecting to the MySQL Proxy admin port 4041
mysql --host=127.0.0.1 --port=4041 --user=root -p$PASSWD
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> select * from proxy_config;
+----------------------------+----------------+
| option | value |
+----------------------------+----------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 127.0.0.1:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+----------------+
6 rows in set (0.00 sec)
From 2nd machine
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'domU-12-31-38-00-32-36.compute-1.internal';
Query OK, 0 rows affected (0.00 sec)
mysql --host=domU-12-31-35-00-1D-B1.z-2.compute-1.internal -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql --host=domU-12-31-35-00-1D-B1.z-2.compute-1.internal --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Checking the admin interface with those connections:
mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | proxy | 10 | |
| 3 | server | 10 | |
+------+--------+-------+------+
4 rows in set (0.00 sec)
Using the --proxy-backend-addresses parameter to load balance and failover
mysql-proxy --proxy-backend-addresses=10.255.34.63:3306 --proxy-backend-addresses=10.252.53.192:3306 &
[1] 5170
nmap 10.255.34.63
Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 02:05 EST
Interesting ports on domU-12-31-35-00-1D-B1.z-2.compute-1.internal (10.255.34.63):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
866/tcp open unknown
3306/tcp open mysql
Nmap run completed -- 1 IP address (1 host up) scanned in 0.259 seconds
nmap 10.252.53.192
Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 02:06 EST
Interesting ports on domU-12-31-38-00-32-36.compute-1.internal (10.252.53.192):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
870/tcp open unknown
3306/tcp open mysql
Connecting from Node 1 goes to node 1
mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
ERROR 1130 (00000): Host 'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' is not allowed to connect to this MySQL server
mysql --port=4040 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Checking the connections again via admin port
mysql --host=127.0.0.1 --port=4041 --user=root -p$PASSWD
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> select * from proxy_config;
+----------------------------+--------------------+
| option | value |
+----------------------------+--------------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 10.255.34.63:3306 |
| proxy.backend_addresses[1] | 10.252.53.192:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+--------------------+
7 rows in set (0.00 sec)
Connecting on Node 2 pointing at node 1 and we are on node 1
mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
Stopping node 1 MySQL database to test failover
service mysql.server stop
Shutting down MySQL. [ OK ]
mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
network-mysqld.c.302: connect(10.255.34.63:3306) failed: Connection refused
network-mysqld-proxy.c.3713: connecting to backend (10.255.34.63:3306) failed, marking it as down for ...
ERROR 1130 (00000): Host 'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' is not allowed to connect to this MySQL server
Note: I had to fix an issue with authenication of root@hostname.
On mysql db on 2nd node:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' identified by 'xxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
network-mysqld-proxy.c.3610: backend 10.255.34.63:3306 was down for more than 10 sec, waking it up
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
On 2nd node, point mysql at node1 and we are back on node2. Failover works!
mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye
[root@domU-12-31-38-00-32-36 ~]# nslookup `hostname`
Server: 172.16.0.23
Address: 172.16.0.23#53
Non-authoritative answer:
Name: domU-12-31-38-00-32-36.compute-1.internal
Address: 10.252.53.192
mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> exit
Bye
On node1, point at node 1 and we are going to node2
mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show processlist;
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
| 11 | root | domU-12-31-35-00-1D-B1.z-2.compute-1.internal:46276 | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
node1 connecting to node2 ip (goes to node1)
mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD
ERROR 1130 (00000): Host 'domU-12-31-38-00-32-36.compute-1.internal' is not allowed to connect to this MySQL server
Added authenication on node2!!!
grant all privileges on *.* to root@'domU-12-31-38-00-32-36.compute-1.internal' identified by 'xxxxx';
mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
Testing load balancing:
Node 2 goes to node 2 when node 1 is already connected.
mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
Node 2 goes to node 1 when node 1 is not connected.
mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)