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:

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

#!/bin/bash# assume that changing the Sakila database name to new_sakila# MyISAM directly changes the files under 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

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.

This article is from the "it--Brother" blog, please make sure to keep this source http://402753795.blog.51cto.com/10788998/1828629

Three ways to rename MySQL database