How do I alias a database in MySQL?

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.

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:

• https://github.com/mysql/mysql-sys/blob/master/procedures/create_synonym_db.sql
• https://github.com/mysql/mysql-sys/blob/master/functions/quote_identifier.sql

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.

INNODB won't work with filesystem symlinks.