Copy a table in MySQL

Sometimes you might need to quickly and easily create a backup copy of a table in MySQL before doing some work to the existing table. That way you can easily copy the data back from the backup table into the original table if something goes wrong. This post looks at how to create a copy of a table in MySQL and then copy the data from the original table into the copy.

Please note

This post has been revised and republished as Copy a table in MySQL with CREATE TABLE LIKE to use a simpler method of creating the new table.

Create a copy of the table

MySQL has a SQL query "SHOW CREATE TABLE" which shows you the SQL required to create a table. If we have an example table called "products" you would issue the following SQL command to get the SQL to create the table:

SHOW CREATE TABLE products

If you're running this from the MySQL command line client you'll get a result like this:

+----------+-------------------------------------------------+
| Table    | Create Table                                    |
+----------+-------------------------------------------------+
| products | CREATE TABLE `products` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------+
1 row in set (0.00 sec)

I've highlighted in red the part we want to copy and use as the SQL to execute.

So you'd copy the SQL needed to create the table and change the `products` to e.g. `products_bak` and then execute a new query to create the copy of the table:

CREATE TABLE `products_bak` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Copying the data from the original table to the new table

Now that we've created the backup table it's simply a matter of running a INSERT INTO ... SELECT query to copy the data from the original table into the copy:

INSERT INTO products_bak SELECT * FROM products

Copying the data back again

If something went wrong with your original table after you've been mucking around with it, you can then simply delete the data from the original table using TRUNCATE and then use the same query above to copy it back again:

TRUNCATE products;
INSERT INTO products SELECT * FROM products_bak

Note that depending on the size of your table this may take some time, and it's not recommended to do it on a production website.

Future posts

On Sunday I'll post a PHP script which automates this process, and next week's MySQL post will show how to do the same thing in phpMyAdmin which means you don't need to type any SQL or run any special scripts.

Make sure to subscribe to my RSS feed (see below) so you don't miss out!

Related posts:

• Show indexes for a table with MySQL
• Copy a MySQL table with phpMyAdmin
• Using SELECT REPLACE with MySQL
• Insert multiple records into MySQL with a single query
• Check if a MySQL table exists
• Using the MySQL command line tool