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
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.
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 =0
SHOW CREATE TABLE `table_name`;
you can export the structure of the table from phpmyadmin to a sql file...
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.
