Three ways to rename MySQL database

I went for an interview not long ago and was asked how to change the database name of the Innodb engine table. At that time, I only answered how to change MyISAM and was defeated by some detailed questions. It's really fucking.

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

Innodb is not possible at all. If you have tested it yourself, it will prompt that the relevant table does not exist.

The first method:

RENAME database olddbname TO newdbname

This can be used in versions 5.1.7 to 5.1.23, but it is not recommended by the official, as there is a risk of losing data.

The second method:

1. Create a database that needs to be changed to a new name.
Export the database to be renamed
3. Delete the original old library (determine if it is really needed)
Of course, although this method is safe, if the data is large, it will be time-consuming. Alas, I didn’t even expect this method at that time, and I really had the urge to die.

The third method:

I'll use a script here, it's very simple, I believe everyone can understand it

#!/bin/bash
 # Suppose you change the sakila database name to new_sakila
 # MyISAM can 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_table
do
    mysql -uroot -p123456 -e "rename table sakila.$table to new_sakila.$table"
done

The command to rename table and change the table name is used here, but if the database name is added after the new table name, the table of the old database will be moved to the new database. Therefore, this method is safe and fast.