Moving tables across databases

You can rename a table by executing the RENAME TABLE statement.

For the following illustrations to work, create sample tables and databases

mysql> CREATE DATABASE prod;
mysql> CREATE TABLE prod.audit_log (id int NOT NULL, msg varchar(64));
mysql> CREATE DATABASE archive;

How to do it...

For example, if you want to rename the audit_log table audit_log_archive_2018, you can execute the following:

mysql> USE prod;
Database changed

mysql> RENAME TABLE audit_log TO audit_log_archive_2018;
Query OK, 0 rows affected (0.07 sec)

If you want to move the table from one database to an other, you can use dot notation to specify the database name. For example, if you want to move the audit_log table from the database named prod to the database named archive, execute the following:

mysql> USE prod
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> SHOW TABLES;
+---------...