Copy complete structure of a table in MySQL without using a backup?

Question

In MySQL, how can someone copy a table structure without using a backup?

       jcho360> show create table actor\G
*************************** 1. row *************************** 
           Table: actor
    Create Table: CREATE TABLE `actor` (
      `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `first_name` varchar(45) NOT NULL,
      `last_name` varchar(45) NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`actor_id`),
      KEY `idx_actor_last_name` (`last_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    jcho360> create table actor_copy select * from actor;
    Query OK, 200 rows affected (0.07 sec)
    Records: 200  Duplicates: 0  Warnings: 0




 jcho360> show create table actor_copy\G
    *************************** 1. row ***************************
           Table: actor_copy
    Create Table: CREATE TABLE `actor_copy` (
      `actor_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

when I do something like that I lose the structure, like PK, FK, index , ETC.

Answer 1

You should be able to do :

CREATE TABLE `actor_copy` LIKE `actor`;

to get the structure, complete with indexes. However Foreign Keys will still need to be created manually.

The above will give you structure only. Then you can proceed to copy the data if you wish:

INSERT INTO `actor_copy` SELECT * FROM `actor`;

Answer 2

@DTest definitely answered this one correctly.

If you want to record the table structure with the Foreign Keys in the definition, here are two ways to do it: (For this example I create the actor table in database test)

TECHNIQUE #1 : using mysqldump

MYSQL_CONN="-u... -p... -h..."
mysqldump ${MYSQL_CONN} -d test actor | sed 's/CREATE TABLE `actor`/CREATE TABLE `actor_copy`/' > actor.sql
TOP=`grep -n "CREATE TABLE" actor.sql | sed 's/:/ /' | awk '{print $1}'`
BOT=`grep -n "ENGINE=" actor.sql | sed 's/:/ /' | awk '{print $1}'`
(( DIFF = BOT - TOP + 1 ))
cat actor.sql | head -${BOT} | tail -${DIFF} > actor_copy.sql
cat actor_copy.sql

and here is the result:

[redwards@lw-lts-155 lwdba]$ MYSQL_CONN="-u... -p... -h..."
[redwards@lw-lts-155 lwdba]$ mysqldump ${MYSQL_CONN} -d test actor | sed 's/CREATE TABLE `actor`/CREATE TABLE `actor_copy`/' > actor.sql
[redwards@lw-lts-155 lwdba]$ TOP=`grep -n "CREATE TABLE" actor.sql | sed 's/:/ /' | awk '{print $1}'`
[redwards@lw-lts-155 lwdba]$ BOT=`grep -n "ENGINE=" actor.sql | sed 's/:/ /' | awk '{print $1}'`
[redwards@lw-lts-155 lwdba]$ (( DIFF = BOT - TOP + 1 ))
[redwards@lw-lts-155 lwdba]$ cat actor.sql | head -${BOT} | tail -${DIFF} > actor_copy.sql
[redwards@lw-lts-155 lwdba]$ cat actor_copy.sql
CREATE TABLE `actor_copy` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

TECHNIQUE #2 : using mysql client

MYSQL_CONN="-u... -p... -h..."
mysql ${MYSQL_CONN} -Dtest -ANe"SHOW CREATE TABLE actor\G" > actor.sql
LINES=`wc -l < actor.sql`
(( LINES2 = LINES - 2 ))
tail -${LINES2} < actor.sql | sed 's/Create Table: //' | sed 's/`actor`/`actor_copy`/' > actor_copy.sql
echo ";" >> actor_copy.sql
cat actor_copy.sql

and here is the result:

[redwards@lw-lts-155 lwdba]$ MYSQL_CONN="-u... -p... -h..."
[redwards@lw-lts-155 lwdba]$ mysql ${MYSQL_CONN} -Dtest -ANe"SHOW CREATE TABLE actor\G" > actor.sql
[redwards@lw-lts-155 lwdba]$ LINES=`wc -l < actor.sql`
[redwards@lw-lts-155 lwdba]$ (( LINES2 = LINES - 2 ))
[redwards@lw-lts-155 lwdba]$ tail -${LINES2} < actor.sql | sed 's/Create Table: //' | sed 's/`actor`/`actor_copy`/' > actor_copy.sql
[redwards@lw-lts-155 lwdba]$ echo ";" >> actor_copy.sql
[redwards@lw-lts-155 lwdba]$ cat actor_copy.sql
CREATE TABLE `actor_copy` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
;