Home / server / Questions / 126509
Accepted
cclark
Asked: 2010-03-26 22:37:07 +0800 CST

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

  • 772

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.

mysql

2 Answers

  1. Best Answer

    Using MySQL Proxy was really one way of trying to get around the missing RENAME DATABASE functionality in MySQL. This functionality existed at one point in time but was removed when it proved to be unreliable.

    In the end I solved the problem could be solved by issuing RENAME TABLE DEV_DB_A.table1 to DEV_DB_B.table1 statements for every table. The only issue I discovered was RENAMEing tables which have triggers fails as described on the MySQL site.

    To make it easier I wrote a small python script to do the job for me. When I saw this was a problem for others I added some basic error checking and options making it easier to use and have posted it for others on github.

    • 2

  2. There are advantages to abstracting the database with something like MySQL Proxy. The main one being query manipulation without altering an application. Nevertheless, high availability and redundancy solutions are often better tackled with other technology.

    Based on your description it sounds like you want to be able to move database equipment dynamically without application changes. To accomplish this, it could be as simple as dedicating an IP alias (VIP) to your database. MySQL doesn't bind to the interface, which enables IP relocation to be done transparently to MySQL and thus your application.

    If you have additional requirements, please describe further and I can likely offer you other alternatives.

    • 0