The SQL command to retrieve an SQL statement that will recreate a particular table in MySQL is this:
SHOW CREATE TABLE tablename;
Then run this to create an identical table in another database.
--------------------------
Restore: mysql -u [username] -p [password] [database_to_restore] < [backupfile]
Backup: mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
________________________
Backup and Restore to a Newer Version: Careful, going from MySQL 3.x to 5.x is NOT a one step.
An example set of steps: http://hisdeedsaredust.com/2009/02/mysql-version-3-to-version-5/
2007-06-04
To export the entire mysql database schema to a text file, do this:
mysqldump -p mydbname --no-data > schema.txt
note the "--no-data" option.
This is handy if you've gone and upgraded from mysql 3.x and have to track down all the timestamp fields that are now broken. (Why did the output change again?!?)
Tags: database, export, text, search, grep, field type, mysql,