[mysql] Rename MySQL database

I created a database with the name of hrms. Now I need to change database name to sunhrm. But, It is disabled in MySQL workbench. Can I do that on the Linux server itself?

This question is related to mysql database linux rename

The answer is

In case you need to do that from the command line, just copy, adapt & paste this snippet:

mysql -e "CREATE DATABASE \`new_database\`;"
for table in `mysql -B -N -e "SHOW TABLES;" old_database`
  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"
mysql -e "DROP DATABASE \`old_database\`;"

It's possible to copy database via mysqldump command without storing dump into file:

  1. mysql -u root -p -e "create database my_new_database"
  2. mysqldump -u root -p original_database | mysql -u root -p my_new_database
  3. mysql -u root -p -e "drop database original_database"

You can create a new database exactly as the previous database existed and then drop the old database when you're done. Use the mysqldump tool to create a .sql backup of the database via mysqldump orig_db > orig_db.sql or if you need to use a username and password then run mysqldump -u root -p orig_db > orig_db.sql. orig_db is the name of the database you want to "rename", root would be the user you're logging in as and orig_db.sql would be the file created containing the backup. Now create a new, empty database with the name you want for the database. For example, mysql -u root -p -e "create database new_db". Once that's done, then run mysql -u root -p new_db < orig_db.sql. new_db now exists as a perfect copy of orig_db. You can then drop the original database as you now have it existing in the new database with the database name you wanted.

The short, quick steps without all the above explanation are:

  1. mysqldump -u root -p original_database > original_database.sql
  2. mysql -u root -p -e "create database my_new_database"
  3. mysql -u root -p my_new_database < original_database.sql
  4. mysql -u root -p -e drop database originl_database

Hope this helps and this is a reliable means to accomplish it without using some ad-hoc method that will corrupt your data and create inconsistencies.

You can do it by RENAME statement for each table in your "current_db" after create the new schema "other_db"

RENAME TABLE current_db.tbl_name TO other_db.tbl_name

Source Rename Table Syntax

Well there are 2 methods:

Method 1: A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema, and then dropping the old schema (if needed).

From Shell

 mysqldump emp > emp.out
 mysql -e "CREATE DATABASE employees;"
 mysql employees < emp.out 
 mysql -e "DROP DATABASE emp;"

Although the above method is easy, it is time and space consuming. What if the schema is more than a 100GB? There are methods where you can pipe the above commands together to save on space, however it will not save time.

To remedy such situations, there is another quick method to rename schemas, however, some care must be taken while doing it.

Method 2: MySQL has a very good feature for renaming tables that even works across different schemas. This rename operation is atomic and no one else can access the table while its being renamed. This takes a short time to complete since changing a table’s name or its schema is only a metadata change. Here is procedural approach at doing the rename:

  1. Create the new database schema with the desired name.
  2. Rename the tables from old schema to new schema, using MySQL’s “RENAME TABLE” command.
  3. Drop the old database schema.

If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too. MySQL’s “RENAME TABLE” fails if there are triggers exists on the tables. To remedy this we can do the following things :

1) Dump the triggers, events and stored routines in a separate file. This done using -E, -R flags (in addition to -t -d which dumps the triggers) to the mysqldump command. Once triggers are dumped, we will need to drop them from the schema, for RENAME TABLE command to work.

  $ mysqldump <old_schema_name> -d -t -R -E > stored_routines_triggers_events.out

2) Generate a list of only “BASE” tables. These can be found using a query on information_schema.TABLES table.

 mysql> select TABLE_NAME from information_schema.tables where 
    table_schema='<old_schema_name>' and TABLE_TYPE='BASE TABLE';

3) Dump the views in an out file. Views can be found using a query on the same information_schema.TABLES table.

mysql> select TABLE_NAME from information_schema.tables where 
   table_schema='<old_schema_name>' and TABLE_TYPE='VIEW';
 $ mysqldump <database> <view1> <view2> … > views.out

4) Drop the triggers on the current tables in the old_schema.

mysql> DROP TRIGGER <trigger_name>;

5) Restore the above dump files once all the “Base” tables found in step #2 are renamed.

mysql> RENAME TABLE <old_schema>.table_name TO <new_schema>.table_name;
$ mysql <new_schema> < views.out
$ mysql <new_schema> < stored_routines_triggers_events.out

Intricacies with above methods : We may need to update the GRANTS for users such that they match the correct schema_name. These could fixed with a simple UPDATE on mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables updating the old_schema name to new_schema and calling “Flush privileges;”. Although “method 2" seems a bit more complicated than the “method 1", this is totally scriptable. A simple bash script to carry out the above steps in proper sequence, can help you save space and time while renaming database schemas next time.

The Percona Remote DBA team have written a script called “rename_db” that works in the following way :

[root@dba~]# /tmp/rename_db
rename_db <server> <database> <new_database>

To demonstrate the use of this script, used a sample schema “emp”, created test triggers, stored routines on that schema. Will try to rename the database schema using the script, which takes some seconds to complete as opposed to time consuming dump/restore method.

mysql> show databases;
| Database           |
| information_schema |
| emp                |
| mysql              |
| performance_schema |
| test               |

[root@dba ~]# time /tmp/rename_db localhost emp emp_test
create database emp_test DEFAULT CHARACTER SET latin1
drop trigger salary_trigger
rename table emp.__emp_new to emp_test.__emp_new
rename table emp._emp_new to emp_test._emp_new
rename table emp.departments to emp_test.departments
rename table emp.dept to emp_test.dept
rename table emp.dept_emp to emp_test.dept_emp
rename table emp.dept_manager to emp_test.dept_manager
rename table emp.emp to emp_test.emp
rename table emp.employees to emp_test.employees
rename table emp.salaries_temp to emp_test.salaries_temp
rename table emp.titles to emp_test.titles
loading views
loading triggers, routines and events
Dropping database emp

real    0m0.643s
user    0m0.053s
sys     0m0.131s

mysql> show databases;
| Database           |
| information_schema |
| emp_test           |
| mysql              |
| performance_schema |
| test               |

As you can see in the above output the database schema “emp” was renamed to “emp_test” in less than a second. Lastly, This is the script from Percona that is used above for “method 2".

# Copyright 2013 Percona LLC and/or its affiliates
set -e
if [ -z "$3" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "show create database $2\G" -sss | grep ^Create | awk -F'CHARACTER SET ' '{print $2}' | awk '{print $1}'`
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from $2"
    exit 1
echo "create database $3 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 $2 -e "show triggers\G" | grep Trigger: | awk '{print $2}'`
VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
for TABLE in $TABLES; do
    echo "rename table $2.$TABLE to $3.$TABLE"
    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $2"
    mysql -h $1 $2 -e "drop database $2"
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$3' WHERE db='$2';"
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"

In short no. It is generally thought to be too dangerous to rename a database. MySQL had that feature for a bit, but it was removed. You would be better off using the workbench to export both the schema and data to SQL then changing the CREATE DATABASE name there before you run/import it.

I used following method to rename the database

  1. take backup of the file using mysqldump or any DB tool eg heidiSQL,mysql administrator etc

  2. Open back up (eg backupfile.sql) file in some text editor.

  3. Search and replace the database name and save file.

  4. Restore the edited SQL file

For impatient mysql users (like me), the solution is:

/etc/init.d/mysql stop
mv /var/lib/mysql/old_database /var/lib/mysql/new_database 
/etc/init.d/mysql start

If your DB contains only MyISAM tables (do not use this method if you have InnoDB tables):

  1. shut down the MySQL server
  2. go to the mysql data directory and rename the database directory (Note: non-alpha characters need to be encoded in a special way)
  3. restart the server
  4. adjust privileges if needed (grant access to the new DB name)

You can script it all in one command so that downtime is just a second or two.

First backup the old database called HRMS and edit the script file with replace the word HRMS to SUNHRM. After this step import the database file to the mysql

Another way to rename the database or taking image of the database is by using Reverse engineering option in the database tab. It will create a ERR diagram for the database. Rename the schema there.

after that go to file menu and go to export and forward engineer the database.

Then you can import the database.

Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with database tag:

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str IN vs ANY operator in PostgreSQL How to execute raw queries with Laravel 5.1? How should I tackle --secure-file-priv in MySQL? How to use multiple databases in Laravel Why does Oracle not find oci.dll? How do I create a local database inside of Microsoft SQL Server 2014? How to change the default port of mysql from 3306 to 3360 Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) How to change Oracle default data pump directory to import dumpfile? Using COALESCE to handle NULL values in PostgreSQL What is Hash and Range Primary Key? How to get item count from DynamoDB? Find duplicate records in MongoDB Django Model() vs Model.objects.create() Mongodb: Failed to connect to, reason: errno:10061 How can I rename column in laravel using migration? There is already an object named in the database How to list all databases in the mongo shell? Failed to connect to mysql at with user root access denied for user 'root'@'localhost'(using password:YES) Partial Dependency (Databases) Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given... what I do wrong? ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES) The backend version is not supported to design database diagrams or tables Difference between partition key, composite key and clustering key in Cassandra? Backup/Restore a dockerized PostgreSQL database Can't connect to MySQL server on '' (10061) (2003) copy from one database to another using oracle sql developer - connection failed Best way to check for "empty or null value" ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance "CLRExtProc", status UNKNOWN getting error HTTP Status 405 - HTTP method GET is not supported by this URL but not used `get` ever? How do I to insert data into an SQL table using C# as well as implement an upload function? Postgres - Transpose Rows to Columns How to update multiple columns in single update statement in DB2 How to increase MySQL connections(max_connections)? What are OLTP and OLAP. What is the difference between them? The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Reset identity seed after deleting records in SQL Server How can I get all sequences in an Oracle database?

Questions with linux tag:

grep's at sign caught as whitespace How to prevent Google Colab from disconnecting? "E: Unable to locate package python-pip" on Ubuntu 18.04 How to upgrade Python version to 3.7? Install Qt on Ubuntu Get first line of a shell command's output Cannot connect to the Docker daemon at unix:/var/run/docker.sock. Is the docker daemon running? Run bash command on jenkins pipeline How to uninstall an older PHP version from centOS7 How to update-alternatives to Python 3 without breaking apt? How to post raw body data with curl? Copy Files from Windows to the Ubuntu Subsystem How to use local docker images with Minikube? Can Windows Containers be hosted on linux? gradlew command not found? ssh connection refused on Raspberry Pi Composer: file_put_contents(./composer.json): failed to open stream: Permission denied Curl : connection refused boto3 client NoRegionError: You must specify a region error only sometimes gzip: stdin: not in gzip format tar: Child returned status 1 tar: Error is not recoverable: exiting now sudo: docker-compose: command not found How to upgrade pip3? How can I remove jenkins completely from linux Linux Command History with date and time MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" What is difference between arm64 and armhf? How to redirect output of systemd service to a file Retrieve last 100 lines logs Failed to find Build Tools revision 23.0.1 Run an Ansible task only when the variable contains a specific string What does `set -x` do? How to edit a text file in my terminal Starting a shell in the Docker Alpine container How to run SUDO command in WinSCP to transfer files from Windows to linux Fail during installation of Pillow (Python module) in Linux How to install Android SDK on Ubuntu? How do I delete virtual interface in Linux? What is the default root pasword for MySQL 5.7 Docker command can't connect to Docker daemon How to find files modified in last x minutes (find -mmin does not work as expected) Can I use Homebrew on Ubuntu? Pycharm and sys.argv arguments Ubuntu: OpenJDK 8 - Unable to locate package Fork() function in C Amazon Linux: apt-get: command not found Ionic android build Error - Failed to find 'ANDROID_HOME' environment variable Ubuntu: Using curl to download an image Docker error response from daemon: "Conflict ... already in use by container" Curl command without using cache Can't get private key with openssl (no start line:pem_lib.c:703:Expecting: ANY PRIVATE KEY)

Questions with rename tag:

Gradle - Move a folder from ABC to XYZ How to rename a component in Angular CLI? How do I completely rename an Xcode project (i.e. inclusive of folders)? How to rename a directory/folder on GitHub website? How do I rename both a Git local and remote branch name? Convert row to column header for Pandas DataFrame, Renaming files using node.js Replacement for "rename" in dplyr Rename multiple columns by names Rename specific column(s) in pandas Renaming Column Names in Pandas Groupby function Rename multiple files in cmd windows batch file rename Rename MySQL database Renaming columns in Pandas In a Git repository, how to properly rename a directory? rename the columns name after cbind the data Changing capitalization of filenames in Git Renaming a branch in GitHub How to move files using FTP commands Rename all files in a folder with a prefix in a single command Changing column names of a data frame How to rename a table in SQL Server? Re-order columns of table in Oracle Renaming branches remotely in Git JavaScript: Object Rename Key How do I rename a MySQL schema? How do I create batch file to rename large number of files in a folder? Rename a file in C# Renaming files in a folder to sequential numbers Changing file extension in Python Is it possible to move/rename files in Git and maintain their history? Renaming a directory in C# Rails: How can I rename a database column in a Ruby on Rails migration? Append date to filename in linux Renaming part of a filename How do I rename the extension for a bunch of files? Rename a file using Java Rename multiple files based on pattern in Unix Batch Renaming of Files in a Directory How to rename with prefix/suffix? How do I rename a column in a database table using SQL? How do I rename all folders and files to lowercase on Linux? How do I quickly rename a MySQL database (change schema name)?