How to Move a MySQL Table to Another Database

Moving a table from one MySQL database to another is a straightforward process, but it’s crucial to approach it methodically to avoid data loss. By following the key steps below, you can ensure that your data is transferred accurately and securely.

How to export and import using SQL commands?

First, connect to your MySQL server and actively select the source database:

USE source_database;

Then, back up the table you want to move:

CREATE TABLE new_database.table_name AS SELECT * FROM original_table_name;

This command actively copies the original table into the new database. Before running this command, make sure the new_database exists. If not, create it:

CREATE DATABASE new_database;

Once you have successfully copied the table to the new database and verified its integrity, you can delete the original table from the source database:

DROP TABLE original_table_name;

Using mysqldump and mysql commands

Alternatively, use the mysqldump command to export the table, then use the mysql command to import it into the target database:

First, export the table from the source database:

mysqldump -u username -p source_database table_name > table_name.sql

After exporting, import the table into the new database:

mysql -u username -p new_database < table_name.sql

After confirming the table’s successful import into the new database, you can safely remove the original table from the source database.

Considerations

• Always check that you have the necessary permissions in both the source and target databases before starting.
• Verify the data’s integrity in the new database after the transfer to ensure all data is intact and uncorrupted.
• If the table has dependencies, like foreign keys or triggers, manage those separately.
• Always back up your data before deleting the original table to prevent unintended data loss.

Adhering to these steps ensures a smooth transition when moving a table from one MySQL database to another, minimizing the risk of data loss and ensuring data integrity.