Mysql – how to copy a table

Cloning table with indexes/triggers and data

1. First create new table with same structure. We’ll use wordpress table wp_posts for the purpose of this tutorial.

   mysql> CREATE TABLE wp_posts_bak LIKE wp_posts;
   Query OK, 0 rows affected (0.00 sec)
   

2. Now copy the data

   mysql> INSERT INTO wp_posts_bak SELECT * FROM wp_posts;
   Query OK, 2857 rows affected (0.49 sec)
   Records: 2857  Duplicates: 0  Warnings: 0
   

Cloning table without indexes/triggers (data only)

This is handy approach to take backup of a table when we don’t need indexes, etc.

mysql> CREATE TABLE wp_posts_bak2 AS (select * from wp_posts);
Query OK, 2857 rows affected (0.09 sec)
Records: 2857  Duplicates: 0  Warnings: 0