Create MySQL query to create a table from an existing table

Question

I have quite a large and complex table in a MySQL database that would be a pain to have to build a query for manually to recreate if anything went wrong.

Is there someway I can get MySQL or some other tool/script I can use to get a Query made automatically that would recreate the structure of the table?

Ideally if you suggested a tool/script it would be for Linux

Answer 1

you can export the structure of the table from phpmyadmin to a sql file...

Answer 2

You can use the mysqldump tool to export the structure and data of a mysql table.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Answer 3

SHOW CREATE TABLE `thetable`;

Also any backup method like mysqldump should give you an option to save structure as well as data.

Answer 4

SHOW CREATE TABLE `table_name`;

Answer 5

I definitely agree with other folks:
with mysqldump you get a dump of your table (option --tables), let's say dumpMyTable.sql
then if you want to load this dump on a new schema:

mysql -uuser_name -ppassword MyNewSchema < dumpMyTable.sql

if you want to load it in the same schema but in a new table you will have to edit the dumpMyTable.sql file and change the name of the table manually. If the dump file is very big, I recommend the use of the "sed" command

Answer 6

You may use SqlYog for this purpose. Solution is just 2 clicks away from this tool. You may use DataBase menu for this purpose. You can also copy only the schema or the whole data and both to even different servers.

Answer 7

from http://dev.mysql.com/doc/refman/5.0/en/create-table.html

to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;