How do I alias a database in MySQL?

问题:

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.

回答1:

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.

回答2:

INNODB won't work with filesystem symlinks.

回答3:

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