20

I'm looking for a way to alias a database in MySQL. The reason is to be able to rename a live, production database without bringing the system down. I figure I can alias the database to the new name, change and deploy the code connecting to it at my leisure, and eventually remove the old alias.

If there's a better way to accomplish this please let me know.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Schwern
  • 153,029
  • 25
  • 195
  • 336

4 Answers4

10

Use the code referenced below to create synonyms.

The easiest way to do this which will work across storage engines (eg: InnoDB, MyISAM, etc) is to use a "Synonym" for all access to a database.

These links will show you the stored procedures you need in order to accomplish this.

NOTES: You should remove the "sys." from the "sys.quote_identifier" (should just be "quote_identifier") in 'create_synonym_db.sql'.

Code:

To use:

This will make a matching access schema for 'CURRENT_DB' which will allow all standard SQL functionality via referencing 'FAKE-DB_NAME'.

CALL create_synonym_db('CURRENT_DB', 'FAKE_DB_NAME');

To Remove a synonym:

This will drop the synonym, but not drop the original database (tested on Maria 10.10.0 Series).

DROP DATABASE FAKE_DB_NAME;

To accomplish your needs, you would have all app tiers communicate with 'FAKE_DB_NAME', and, on the need to do the form of maintenance you're discussing, you'd remove the current synonym and replace it with one pointing to the other database.

So, once loading those stored prodecures for the correct maintenance account (whatever MySQL account you use for such maintenance), you'd do the following:

(1) Create a Synonym:

CALL create_synonym_db('NormalDB', 'FAKE_DB_NAME');

(2) Use Database as normal by synonym:

  • All SQL code referring to database by 'FAKE_DB_NAME'

(3) Make your temp/backup/replacment DB with whatever methods you've chosen.

(4) Switch all app tier SQL code to the Temp DB

DROP DATABASE FAKE_DB_NAME;
CALL create_synonym_db('TempDB', 'FAKE_DB_NAME');

(5) Perform whatever maintenance you have planned on 'NormalDB'

(6) Switch all app tier SQL code to the Temp DB

These two transaction should be near instantaneous.

DROP DATABASE FAKE_DB_NAME;
CALL create_synonym_db('NormalDB', 'FAKE_DB_NAME');

This will allow you to seamlessly swap in any database without changing code on the app tier.

IMPORTANT: Keep in mind, when you "switch over" to 'TempDB', any changes made to it will not be in 'NormalDB' that you effectively took "offline" (from the app tier) for maintenance.

Robert Mauro
  • 566
  • 8
  • 17
9

https://dev.mysql.com/doc/refman/5.7/en/symbolic-links-to-databases.html says

MySQL does not support linking one directory to multiple databases.

You can use symbolic links to link a database directory to some other location, for example outside the datadir. 

$ cd /var/lib/mysql
$ ln -s /other/dir/mydatabase .

But you can't use symbolic links to make one database directory an "alias" for another MySQL database:

$ cd /var/lib/mysql
$ ln -s `pwd`/mydatabase1 mydatabase2  # WRONG

The reason is that InnoDB retains database names and other metadata inside its own data dictionary, stored in the tablespace file. If you confuse it, you won't get what you want.

MySQL doesn't have any DDL syntax for aliasing a database.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

INNODB won't work with filesystem symlinks.

user175725
  • 29
  • 1
  • I haven't tried it to confirm, but it might work if you use InnoDB with the file-per-table option, instead of a shared tablespace. – Bill Karwin Oct 05 '10 at 15:45
  • It works fine if the link is in **datadir** ie `/var/lib/mysql`. You do unfortunately also get a broken version of the long named db. – TimP May 30 '12 at 12:13
  • If you set the innodb_file_per_table this does not make the database directory more moveable. You still cannot move it outside **datadir**. – TimP May 30 '12 at 15:24
0

On Unix, the way to symlink a database is first to create a directory on some disk where you have free space and then to create a soft link to it from the MySQL data directory.

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

MySQL does not support linking one directory to multiple databases. Replacing a database directory with a symbolic link works as long as you do not make a symbolic link between databases. Suppose that you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1:

shell> cd /path/to/datadir
shell> ln -s db1 db2

The result is that, or any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one client updates db1.tbl_a and another client updates db2.tbl_a, problems are likely to occur.

To determine the location of your data directory, use this statement:

SHOW VARIABLES LIKE 'datadir';

Source: http://dev.mysql.com/doc/refman/5.1/en/symbolic-links.html

Ubuntu/Debian example

Warning: Not working with MyISAM tables

1) Determine the location of your data directory:

echo "SHOW VARIABLES LIKE 'datadir';" | mysql -u root -p

2) Go to location of your data directory and stop mysql service:

sudo su
service mysql stop
cd path/to/datadir

3) Create a soft link:

ln -s current_db db_alias

4) Fix permisions:

chown -R mysql:mysql db_alias

5) Start mysql:

service mysql start
Eduardo Cuomo
  • 17,828
  • 6
  • 117
  • 94