MySQL Proxy on EC2

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:

  1. 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/
  2. tar -xzvf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
  3. cp mysql-proxy-0.6.0-linux-rhas4-x86/sbin/mysql-proxy /sbin
  4. 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)


Advertisements