How to rename a MySQL database

Source: Internet
Author: User

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:

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.