How to proxy to different named databases on the same server using MySQL Proxy?

Posted by cclark on Server Fault See other posts from Server Fault or by cclark
Published on 2010-03-26T06:37:07Z Indexed on 2010/03/26 6:43 UTC
Read the original article Hit count: 400

Filed under:

mysql

|

mysqlproxy

I would like to have two databases on my MySQL server:

DEV_DB_A
DEV_DB_B

However, in order to keep everyone's scripts, Query Browser settings and anything else from changing when we switch from using on DB to another I'd like to have everyone connect to DEV_DB and then use something like MySQL Proxy running a lua script which knows the currently active DB is DEV_DB_A and routes queries to there. If we restore a fresh version of the DB to DEV_DB_B or make some changes (e.g. partition a table) we can easily switch to DEV_DB_B by changing one Lua script instead of updating references everywhere.

I had hoped I might be able to symlink inside of the mysql data directory but that didn't work so it seems like MySQL Proxy is a reasonable approach.

Being new to Lua and MySQL Proxy I'm wondering if anyone else has approached the problem this way and how it worked.

© Server Fault or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about mysqlproxy

How to proxy to different named databases on the same server using MySQL Proxy?

as seen on Server Fault - Search for 'Server Fault'
I would like to have two databases on my MySQL server: DEV_DB_A DEV_DB_B However, in order to keep everyone's scripts, Query Browser settings and anything else from changing when we switch from using on DB to another I'd like to have everyone connect to DEV_DB and then use something like MySQL… >>> More
Replacement for MySQL proxy

as seen on Server Fault - Search for 'Server Fault'
We have a standard MySQL Master/Slave replication for our database. In order to use the slave server we have set up MySQL Proxy. However we have been strongly discouraged to use it as it still alpha and not very well supported. Our application is built with Drupal 7 which doesn't use very effectively… >>> More
Mysql-proxy compile in CentOS

as seen on Server Fault - Search for 'Server Fault'
Hey, While trying to compile Mysql-Poxy i get the following error. By the instructions here Libtool library used but `LIBTOOL' is undefined The usual way to define `LIBTOOL' is to add `AC_PROG_LIBTOOL' to `configure.in' and run `aclocal' and `autoconf' again. If `AC_PROG_LIBTOOL' is in `configure… >>> More
mysql-proxy got error which cause it to crash

as seen on Server Fault - Search for 'Server Fault'
I got that message and i dont know what it is means: Oct 21 12:24:26 lex mysql-proxy: 2013-10-21 12:24:26: ((error)) last message repeated 98 times Oct 21 12:24:26 lex mysql-proxy: 2013-10-21 12:24:26: ((error)) network-mysqld-packet.c.596: COM_(0x04) should not be (OK|ERR|NULL), got: 00 Oct 21 12:24:26… >>> More
MySQL proxy HA with no need to reconnect after node failure

as seen on Server Fault - Search for 'Server Fault'
I use MySQL with Galera wsrep to get synchronous replication, that part it's up and running I need to setup a kind of proxy to handle client connections. Since any node in cluster can fail, clients will not connect nodes directly, but only via proxy. Currently I use Galera Load Balancer which does… >>> More