Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
possible duplicate of How to copy complete structure of a table without BACK-UP –  RolandoMySQLDBA May 17 '12 at 14:11
    
@RolandoMySQLDBA please read the comments of the other post –  jcho360 May 17 '12 at 14:23
    
I read it carefully. Sorry for close vote. I'll try to answer this one. –  RolandoMySQLDBA May 17 '12 at 14:31
    
@RolandoMySQLDBA it's ok, please vote to close the other one –  jcho360 May 17 '12 at 14:32
1  
yes, I know, I would love to those ppl answer again the same answer to give them the point. that's why I separate the question because it wasn't fair give it the "check mark" only to one of them –  jcho360 May 17 '12 at 14:37
show 3 more comments

2 Answers 2

up vote 4 down vote accepted

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`;
share|improve this answer
    
+1 again for you, Sir !!! –  RolandoMySQLDBA May 17 '12 at 14:55
add comment

@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
;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.