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!