Info2soft use cookies to help you have a superior and more admissible browsing experience on our website. Privacy Policy
Loading...
By:
In real-world database management, it’s common to face situations where you need to copy a MySQL table from one database to another. Maybe you’re migrating data from a development environment to production, creating a safety backup, or simply sharing a specific dataset across multiple applications. Whatever the reason, knowing how to copy tables efficiently between databases can save you hours of manual work and prevent costly errors.
When managing large systems or multiple projects, developers often maintain several databases — for example, one for testing, one for staging, and another for live production. In these cases, being able to copy MySQL tables between databases ensures data consistency and smooth transitions between environments. It also helps when reorganizing data, merging systems, or exporting a subset of information for analytics.
In this guide, you’ll learn the most effective methods to copy a table from one MySQL database to another. It will be divided into on the same server or across different servers. We’ll cover both command-line and graphical approaches, from quick SQL statements like CREATE TABLE … SELECT to powerful tools like mysqldump and MySQL Workbench — all designed to help you transfer data safely and efficiently.
When both the source and target databases are on the same MySQL server, you can use native SQL statements. Try the methods below.
“CREATE TABLE … SELECT” statement is one of the fastest ways to copy a table between databases on the same MySQL server. It creates a new table in the target database and immediately fills it with data from the source table.
Syntax:
CREATE TABLE IF NOT EXISTS destination_db.new_table
SELECT * FROM source_db.existing_table;
Example:
To copy the full “offices” table from the “classicmodels” sample database to a “testdb” database:
CREATE TABLE IF NOT EXISTS testdb.offices_quick
SELECT * FROM classicmodels.offices;
✅ Pros:
⚠️ Cons:
This method is best for quick copies of a table and its data, test snapshots, temporary backups, and scenarios where indexes and formal constraints aren’t required.
When you need an exact structural replica of the source table, including all indexes, primary keys, unique constraints, foreign keys, and column-level attributes, use the two-step LIKE + INSERT approach. This method produces a structurally identical table in the target database.
Step 1. Duplicate the table structure
First, create an empty new table with the same schema as the source table:
CREATE TABLE IF NOT EXISTS destination_db.new_table
LIKE source_db.existing_table;
Step 2. Copy all data into the new table
Second, insert all rows from the source table into the newly created structure:
INSERT INTO destination_db.new_table
SELECT * FROM source_db.existing_table; Example:
To create a full structural copy of “offices” in “testdb” and load all data:
-- Create empty table with full structure (indexes, keys, constraints)
CREATE TABLE IF NOT EXISTS testdb.offices_full
LIKE classicmodels.offices;
-- Copy all data from source to destination
INSERT INTO testdb.offices_full
SELECT * FROM classicmodels.offices;
✅ Pros:
⚠️ Cons:
It is best for production-level copies, staging environment setup, merging, or updating existing tables.
In some cases, you may only want a subset of data, instead of copying every row. Both methods above support row-level filtering with a WHERE clause.
Example with “CREATE TABLE … SELECT”.
Copy only offices located in the USA from “classicmodels” to “testdb”:
CREATE TABLE IF NOT EXISTS testdb.offices_usa
SELECT *
FROM classicmodels.offices
WHERE country = 'USA';
Example with “CREATE TABLE … LIKE + INSERT”
For a structurally complete table with filtered data:
CREATE TABLE IF NOT EXISTS testdb.offices_usa_structured
LIKE classicmodels.offices;
INSERT INTO testdb.offices_usa_structured
SELECT *
FROM classicmodels.offices
WHERE country = 'USA';
This is best for creating region-specific data subsets, test data sampling, incremental data copies, and isolating data for individual development teams.
Step 1. Select the source table.
Step 2. Click the “Operations” button.
Step 3. Under “Copy table to (database.table), choose the content you want to copy, such as table structure only, the data only, or both.
Step 4. Enter a name for the new table and click Go.
✅ Pros:
⚠️ Cons:
When the source and destination databases run on separate MySQL servers, physical hosts, or cloud instances, use the methods in this section.
The “mysqldump” command-line tool is perfect when you need to copy tables between different servers or when dealing with large datasets. It exports the table as SQL statements, which can then be imported into another database.
Prerequisites:
Step 1. Export the Table from the source Database
Run mysqldump on your working machine to export the source table to a .sql dump file. This file will contain the full CREATE TABLE definition and all INSERT statements for the table data.
Syntax:
mysqldump -u [source_user] -p -h [source_host] -P [source_port] [source_database] [table_name] > [dump_file.sql]
Example:
To export a customers table from an e-commerce database on a remote production server:
mysqldump -u source_user -p -h source-db.example.com ecommerce customers > customers_dump.sql
When prompted, enter the password for “source_user”. The tool will generate “customers_dump.sql” in your working directory.
Step 2. Import the Dump File into the Destination Database
Use the MySQL client to execute the SQL dump file on the destination server. This will recreate the table with its full schema and populate it with all exported rows.
Syntax:
mysql -u [dest_user] -p -h [dest_host] -P [dest_port] [dest_database] < [dump_file.sql]
Example:
To import the “customers_dump.sql” file into an “ecommerce_staging” database on a local staging server:
mysql -u dest_user -p -h localhost ecommerce_staging < customers_dump.sql
Enter the destination user’s password when prompted. The MySQL client will create the customers table and insert all exported records.
✅ Pros:
⚠️ Cons:
This is best for production-grade table migrations between separate server instances, copies between databases with different user accounts and permission models, transfer across different MySQL versions, etc.
For those who prefer graphical tools, MySQL Workbench provides user-friendly interfaces to copy MySQL tables between servers.
Step 1. Right-click the table you want to copy. Select “Table Data Export Wizard”.
Step 2. Browse and choose your CSV file, and click “Next”.
Step 3. Choose the target database. Check “Create New Table”, and you can rename the table. Click “Next”.
Step 4. Check the import settings, and you can reconfigure them. Click “Next”.
Step 5. Follow the prompts to finish and process the operation.
✅ Pros:
⚠️ Cons:
For teams managing multiple databases or large-scale MySQL environments, continuous synchronization between MySQL databases is necessary. you can turn to a professional database replication software – i2Stream. This solution offers a powerful, automated way to copy and synchronize tables and data between different MySQL databases in real time.
Info2soft i2Stream provides log-based change data capture (CDC) for real-time database synchronization. It captures database changes from transaction logs and replicates them to the target database without requiring application changes.
This solution captures data changes (such as inserts, updates, and deletes) from the source MySQL database and applies them automatically to the target database. This ensures both databases remain synchronized, even while transactions are ongoing.
Key Advantages of i2Stream
You can click the button below to request a 60-day free trial.
Choosing the right method to copy a MySQL table from one database to another depends on your environment, the size of the data, and the frequency of updates. For one-off tasks, built-in SQL commands are sufficient. For ongoing, real-time, or enterprise-level replication, investing in a robust solution like Info2soft‘s i2Stream ensures data integrity, reduces manual overhead, and keeps multiple databases in sync effortlessly.
· Enterprise & Mid-market Customers Worldwide
· Support team available to assist you throughout your trial
· Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
