How to rename MySQL schema - the proper way

John D K -

Renaming a MySQL schema depends on several constraints:

  • database size;
  • number of tables;
  • database engine - InnoDB or MyISAM (storage settings are different);
  • tools that you have at your side;

Also renaming can be done in several ways;

  • renaming
  • create new schema
  • rename tables
  • drop old schema
  • using dump
  • dump also can be used in some cases for small databases
  • export and import via tool
  • rename with tool

Manual rename of schema name

In short"

  1. Create a new schema
  2. Create a rename script:
SELECT GROUP_CONCAT('RENAME TABLE testnew.', table_name, ' TO test.', table_name SEPARATOR '; ')
FROM information_schema.TABLES 
WHERE table_schema='testnew';
  1. Run the output
  2. Delete old database

For example let say that we want to rename testnew to test (which has 3 tables):

  • create new schema:
CREATE DATABASE `test` /*!40100 COLLATE 'utf8_bin' */;
  • prepare renaming script by:
SELECT GROUP_CONCAT('RENAME TABLE testnew.', table_name, ' TO test.', table_name SEPARATOR '; ')
FROM information_schema.TABLES 
WHERE table_schema='testnew';
  • Rename tables by running the result:
RENAME TABLE testnew.table1 TO test.table1; RENAME TABLE testnew.table2 TO test.table2; RENAME TABLE testnew.test TO test.test
  • drop old schema(optional):
DROP DATABASE `testnew`;

Note: In case of views or triggers, you will need to move them as well. Check the section: Export and import(in case of views and triggers)

Rename MyISAM schema

In case of MyISAM tables you can do this steps:

  • Stop the MySQL server
  • Rename the database folder
  • Start the MySQL server

Example (Linux - Ubuntu 16):

  • Go to MySQL folder:
cd /var/lib/mysql/
  • Stop MySQL
sudo service mysql stop
  • Change schema name:
mv testnew test
  • Restart MySQL
sudo service mysql start

Note: this way might not work with InnoDB.

Rename with Linux shell script

#!/bin/bash

mysqlconn="mysql -u root -p -S /var/lib/mysql/mysql.sock -h localhost"
olddb=testnew
newdb=test

#$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'")

for name in $params; do
      $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
done;

#$mysqlconn -e "DROP DATABASE $olddb"

Rename with Linux command shell

This way will use traditional MySQL command(renaming testnew to test). If you need to change the host add -h option : -h localhost. -e (or --execute) option in order to execute a statement directly from the command line .

mysqldump -u root -p testnew > testnew_dump.sql
mysql -u root -p -e "CREATE DATABASE test"
mysql -u root -p  test < testnew_dump.sql
mysql -u root -p  -e "DROP DATABASE testnew"

Renaming with tool

The simplest and faster way for me is using free tools like HeidiSQL or Dbeaver

HeidiSQL

You can very easily connect to a database using and SSH tunnel so it's able also to change database remotely.

  • install Heidi
  • it's available for Windows, Linux, MacOS
  • it can be installed as portable
  • right click on the schema
  • Edit - ALT + ENTER
  • Change the name
  • Press OK

Note: In case of views or triggers, you will need to move them as well. Check the section: Export and import(in case of views and triggers)

Dbeaver

  • install Dbeaver
  • it's available for Windows, Linux, MacOS
  • it can be installed as portable
  • right click on the schema
  • Rename - F2
  • Change the name
  • Press OK

Note: In case of views or triggers, you will need to move them as well. Check the section: Export and import(in case of views and triggers)

Export and import(in case of views and triggers)

If you want to rename schema with triggers and views you can do it by export and import. This option is better for small databases:

HeidiSQL

  • right click on the schema
  • Export database as SQL
  • Choose:
  • create table
  • create schema
  • data insert
  • output destination : clipboard, SQL file etc
  • rename schema name in the output SQL
  • Run the SQL