Rename MySQL Table
Rename MySQL table, sometimes we need to rename a table during a database update or for any other solid reason. In this short step by step guide we will show you how easy is to rename one or even more MySQL tables in a database using only MySQL client, the answer to “how do I rename a mysql table” question is right here in this short article. Please bear in mind that in our tutorial we have used MySQL 5.7 to rename tables but this solution could be easily applied even for lower or higher MySQL versions. As always, before making any changes on the databases, including a simple database table renaming we would recommend to take a full backup of your database first, this could save you lot of time if anything goes wrong.
Table of contents
Connecting to MySQL server
We will begin this tutorial by connecting to our MySQL server which can be on the same machine as MySQL client or can be a remote server, so lets start by using the next command in order to get access to our database:
$ mysql -u my_username -p
Please avoid using the password in plain text, this will be recorded in system logs (history) and if your server gets compromised this could lead to data loss and also try not to use the same password for all your environments, for example same password for staging and production or development. If everything goes fine in terms of MySQL connectivity then we can carry on with our next step where we have to select the database and list the tables.
Selecting the database
Once we are connected we should check the database that we need to use for table renaming, so lets get a list of all existing databases first:
mysql> SHOW databases;
Assuming that our database called
my_database_name is listed then we can simply select it by using
use statement like shown in the example below:
mysql> USE my_database_name;
Now that we have selected the right database lets get a list of all tables by using the next SQL query:
mysql> SHOW TABLES;
Renaming MySQL database tables
Having now everything in place like the right database and the right table lets do actually a mysql database table renaming like shown in the example below where we need to rename
mysql> RENAME TABLE table_name_1 TO table_name_2;
At this point our old table called
table_name_1 shouldn’t be visible anymore if we are running
SHOW TABLES; SQL query, we have successfully managed to rename a single table but now lets try to rename multiple tables:
mysql> RENAME TABLE table_name_1 TO table_name_2, table_name_3 TO table_name_4;
Invoking once again
SHOW TABLES; query we should be able to see all our renamed database tables as expected.
This tutorial about renaming MySQL database tables fortunately ends here, is been quite short as we have promised and on top of that was quite easy.