Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
I have one DB server in my test environment and it runs one MySQL instance. We restore our prod database back to our test environment once a week. During the time of the restore the DB is offline and therefore our test environment is offline. So essentially I'm hoping to use MySQL Proxy to allow the test environment to always point at the same database instance (e.g. TST_DB). TST_DB will point to TST_DB_A until TST_DB_B is refreshed and set to the active DB by MySQL Proxy. After the next restore the active DB will again become TST_DB_A essentially eliminating downtime due to restores. –  cclark Mar 27 '10 at 1:20
    
Write a script and restore during off hours. But yes, as I recommended, moving an IP alias would accomplish what you're trying to do exactly. –  Warner Mar 27 '10 at 3:32
    
Script is already written and the process has been automated for months. The DB is so large now that starting in the very first 'off hour' it still doesn't finish before on-hours begin. Xtrabackup will get around that eventually but right now I have some schema changes around partitioning I'd like to test as well so I still want to have two DB instances I can easily switch between. If both DBs are on the same machine I'm not sure how an IP alias will help because the single MySQL daemon still runs on the same port and it is only the DB name which changes between them. –  cclark Mar 27 '10 at 13:22
    
At this point, I'd reconsider the overall approach. I still think MySQL proxy is over engineering the solution. I suspect you're probably using some sort of dump. In my development environment, I'd take one of the nightly snapshots from the production replication backups, scrub, and copy. With this sort of approach, the old db could be left and you could copy the files to a new location, copy config, stop db, move in place, and done. While there would be still brief downtime, it would be minimal if approached correctly. –  Warner Mar 27 '10 at 18:59
    
Separate boxes would still be an option if you're stuck on the previous approach. It sounds like your SLA is a bit unreasonable given that this is a development environment but I suspect that you're stuck with it, regardless. –  Warner Mar 27 '10 at 19:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.