How to rename a MySQL database

How to rename a MySQL database

[Preface] One of the databases needs to be backed up for business needs, and then imported into a brand new database; it is the fastest and easiest way to directly modify the name of the current database for backup. This document summarizes some methods for renaming a MySQL database;

[Environment description]

Database Version: MySQL 5.5.36

Database storage engine: INNODB

Tablespace storage: Independent tablespace

[Operation method]

Method 1: Rename the database directly (this method is not available in version 5.5 After testing)

Script: rename database db_name TO new_db_name

Method 2: Use mysqldump to back up and import the database (this method is time-consuming when the database is large)

Method 3: rename all tables in the database,

The information_schema database table records information of all tables in the database;

1. create database new_db_name; CREATE a new DATABASE
2. rename table db_name.table1 TO new_db_name.table1; RENAME all tables
3. drop database db_name; Delete the original DATABASE

When a database table contains many tables, this operation is relatively inefficient. You can perform batch modification using the following script;

Mysql-uroot-p-e "select concat ('rename table db. ', table_name,' to new_db. ', table_name,'; ') from information_schema.TABLES where TABLE_SCHEMA = 'db'; "> rename_mysql_name. SQL

Execute SQL statements

Mysql-uroot-p <rename_mysql_name. SQL batch modification;

Conclusion: The second method is the most safe, which is also one of the methods for small database backup and restoration by many companies.

The third method is fast but highly risky, which may make some views unavailable. Although the view name has changed, the referenced table in the view is still the table of the original database, therefore, when using this method for migration, you need to check the database view;

This article permanently updates the link address: