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

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.

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`;

StackExchange Database Administrators Q#18057, answer score: 10