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
Share this article: share on Google+ share on facebook share on linkedin tweet this submit to reddit

Comments

Click here to write/view comments