Rename the Database name in MySQL

Syntax:

  1. Rename the database and drop old one.
mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>

2. May you need to update the information schema with rename command for each table. Before executing second step please verify first.

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
'new_db.', table_name)
FROM information_schema.TABLES
WHERE table_schema = 'old_db';

Example of Rename the MySQL database name

Example:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| database           |
| sys                |
| world              |
+--------------------+
3 rows in set (0.00 sec)


-- Go the bin folder:
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin -uroot -p create test_bkp
Enter password: *********

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -u root -pPassword1 --routines world | mysql -u root -pPassword1 test_bkp
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Table   Op      Msg_type        Msg_text
test_bkp.city   histogram       status  Histogram statistics created for column 'Name'.
Table   Op      Msg_type        Msg_text
test_bkp.city   histogram       status  Histogram statistics created for column 'District'.


--Connect with mysql database with root user:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| database           |
| sys                |
| test_bkp           |
| world              |
+--------------------+
4 rows in set (0.00 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.