Create MySQL query to create a table from an existing table

I have quite a large and complex table in a MySQL database 开发者_如何学JAVAthat 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

SHOW CREATE TABLE `thetable`;

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

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;

execute these queries :

If you want to copy table structure with primary key, foreign key and constrains with data...

CREATE TABLE newtableName LIKE oldTableName; 
INSERT newtableName SELECT * FROM oldTableName;

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

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 =0

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

SHOW CREATE TABLE `table_name`;

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

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.