Three methods of renaming mysql Database

When I went to the interview not long ago, I was asked how to change the database name of the table of the Innodb engine. At that time, I only answered how to change the MyISAM. I was defeated by some detailed questions.

If it means MyISAM, then you can go directly to the database directory mv.

Innodb does not work at all. If you have tested it yourself, you will be prompted that the relevant table does not exist.

The first method:

RENAME database olddbname TO newdbname

This is available in versions 5.1.7 to 5.1.23, but it is not officially recommended and there is a risk of data loss.

The second method:

1. Create a database that needs to be changed to a new name.

2.mysqldum exports the database to be renamed

3. Delete the original old library (determine if it is really needed)

Of course, this method is safe, but if the amount of data is large, it will be time-consuming. Alas, I didn't even think of this method at that time, and I really wanted to die.

The third method:

I'm going to use a script here. It's very simple. I'm sure everyone can understand it.

Copy the code

#! / bin/bash# assume that you can change the name of the sakila database to new_sakila# MyISAM and directly change the files in the database directory mysql-uroot-p123456-e 'create database if not exists new_sakila'list_table=$ (mysql-uroot-p123456-Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='sakila'") for table in $list_tabledo mysql-uroot-p123456-e "rename table sakila.$table to new_sakila.$table" done

Rename table, the command to change the table name, is used here, but if the new table name is followed by a database name, the table from the old database will be moved to the new database, so this method is safe and fast.