Copying Table Structures With MySQL

We often have to get basic tables in place for our MySQL web backend. To create a new database:

[root@main gg]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8374 to server version: 3.23.54
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database data2;
Query OK, 1 row affected (0.01 sec)
mysql>

OK. We have a new database. Now, we want to clone a table from another database, so let’s connect to it and list the structure:

mysql> connect data1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id:    8394
Current database: data1
mysql> show create table table1;
.
.
.
| table1 | CREATE TABLE `table1` (
`field1` int(11) NOT NULL auto_increment,
`field2` text NOT NULL,
`field3` text NOT NULL,
PRIMARY KEY  (`field1`)
) TYPE=MyISAM |
.
.
.
1 row in set (0.00 sec)
mysql>

Now, all we have to do is connect to the other database and paste the above as a command:

mysql> connect data2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id:    8395
Current database: data2
mysql> CREATE TABLE `table1` (
->   `field1` int(11) NOT NULL auto_increment,
->   `field2` text NOT NULL,
->   `field3` text NOT NULL,
->   PRIMARY KEY  (`field1`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql>

We have now created a new database, and cloned table1.

Copying Table Structures With MySQL

Information

About