Three ways to rename MySQL database

Shortly before the interview, was asked to InnoDB engine table How to change the database name, then I only answered the MyISAM how to operate, by some details of the problem defeated, really fucked.

If it represents MyISAM then you can go directly to the database directory MV.

InnoDB completely not, you have tested, you will be prompted that the related table does not exist.

The first method:

1	RENAME database olddbname TO newdbname

This is 5.1.7 to 5.1.23 version can be used, but the official is not recommended, there will be a risk of loss of data

The second method:

1. Create a database that needs to be changed to a new name.
2.mysqldum exporting the database to be renamed
3. Delete the original old library (determine if you really need it)
Of course, although this method is safe, but if the amount of data is large, it will be time-consuming, hey, at that time even this method did not think, really want to die of impulse.

The third method:

I use a script here, very simple, I believe everyone can read

Copy Code

1234567891011

#!/bin/bash# 假设将sakila数据库名改为new_sakila# MyISAM直接更改数据库目录下的文件即可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

The rename table is used here, but if the new table name is appended with the database name, the old database table will be moved to the new database, so this method is safe and fast.

Instance configuration:

 CatWeifeng_rename.SH #!/bin/bash# assume that the Weifeng database name is changed to Weifeng_testmysql-uroot-p123456-e'CREATE database if not exists weifeng_test'list_table=$ (mysql-uroot-p123456-nse"Select table_name from INFORMATION_SCHEMA. TABLES where table_schema= ' Weifeng '")  forTableinch$list _table DoMySQL-uroot-p123456-e"Rename table Weifeng. $table to Weifeng_test. $table" Done

Three ways to rename MySQL database