Copy MySQL Table

Copy MySQL table, the simple process of copying a MySQL database table will be now explained in this short tutorial. Quite often we need to check some new queries, features or simply we need perform some basic tests that can be very difficult or sometimes impossible to be taken on a live database table. Luckily MySQL can help us to copy a database table with or without indexes and triggers, a full table copy or we can simply take a simple table copy, just the data and nothing else. In this short tutorial we will guide you how to take a database table copy with just a few quick and easy steps. Please note that for our tutorial we have used MySQL 5.7 but as you know this solution can be easily applied for other MySQL versions too. Before proceeding with table copy please make sure that you have a backup somewhere for your database, always backup your database before any changes no matter how simple or safe may look.

Table of contents

Connect to MySQL service
Select MySQL database
Copy MySQL database table
Check table data

Connect to MySQL service

Let’s start with our very first step where we have to connect to our MySQL instance being locally or remote. Assuming that backups were taken, our system is up and running let’s connect to MySQL like shown in the example below by replacing mysql_username with yours. Please avoid to use the password in plain text, meaning immediately after -p parameter use it once the prompt shows up on your terminal window.

$ mysql -u mysql_username -p

Select MySQL database

Once we are connected to our MySQL database server then we can carry on and get a list of databases, let’s make sure that that database is there first:

mysql> SHOW databases;

On the next step we will have to instruct MySQL to use my_database_name where presumably our original_table resides, so we can select the the right database by using USE SQL query as shown in the example below:

mysql> USE my_database_name;

Knowing now that we are in the right place by selecting the right database we can move on to the next step where we can actually copy the database table from command line interface (shortly CLI) using just MySQL client.

Copy MySQL database table

When copying tables in MySQL we have two options as we have said in the introduction of our quick tutorial, fist option being to make a full copy of it which will include Indexed and Triggers and the second option where we can copy data only for our database table. Please be aware that if your table is large, let’s say more than 250GB, no matter what option you are opting for it will take a significant amount of time to perform the copy as we are relying purely on DiskIO, reads for selecting the data from original table and writes for inserting the data to the new table. If that is the case then a viable solution to complete the copy without any data loss would be to use screen for this task.

First option, full mysql database table copy

A full mysql database table copy without using mysqldump, mysqlpump or any other method that will basically export the data to a .sql format file would be to create a new table, select the data from the original table and insert this data to the new table. With this first option we will make sure that we will end up making a perfect copy of the table meaning that will include the Indexes and also all Triggers. So lets start to do so by creating first our new table using LIKE query which basically copies the the structure, indexes and triggers:

mysql> CREATE TABLE table_copy LIKE original_table;

Having now the new table exactly LIKE original_table we can start copying the data from the original table to the newly created one as it follows:

mysql> INSERT INTO table_copy SELECT * FROM original_table;

Depending on the amount of data this copy process can take less or longer time to completed, we will notice when this is completed as soon as we will get back access to our mysql prompt.

Second option, mysql table structure and data copy

With the second option we can actually copy only table structure and its data, so we won’t copy the indexes and triggers, this will reduce significantly our overall time to complete the table copy, so lets do that by using the next commands:

mysql> CREATE TABLE table_copy AS SELECT * FROM original_table;

Once again we will find out when the copy process is completed when we will get back access to mysql client prompt, you can actually open a second terminal window and check how the copy goes.

Check table data

No matter which method we are using we always need to check our result, good or bad that should be there, so the most simple check if the table isn’t that large would be just to perform a full select on both tables, original and new one and compare the results, this could also be quickly done via a count sql query but if you want to see the actual data a SELECT * will be our best bet like shown below:

mysql> SELECT * FROM table_copy ORDER BY id LIMIT 100;

If the results are the same on both tables then we can safely say that we have managed to successfully copy MySQL database table data using just MySQL client, a nice, clean and efficient way with our without Indexes or Triggers.