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.
|
|