Table Management - MySQL - page 136

... column definitions ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1
To change the storage engine for a table, use ALTER TABLE with an ENGINE specifier. For
example, to convert the mail table to use the MyISAM storage engine, use this statement:
ALTER TABLE mail ENGINE = MyISAM ;
Be aware that converting a large table to a different storage engine might take a long
time and be expensive in terms of CPU and I/O activity.
To determine which storage engines your MySQL server supports, check the output
from the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table.
4.6. Copying a Table Using mysqldump
Problem
You want to copy a table or tables, either among the databases managed by a MySQL
server, or from one server to another.
Solution
Use the mysqldump program.
Discussion
The mysqldump program makes a backup file that can be reloaded to re-create the
original table or tables:
% mysqldump cookbook mail > mail.sql
The output file mail.sql consists of a CREATE TABLE statement to create the mail table
and a set of INSERT statements to insert its rows. You can reload the file to re-create the
table should the original be lost:
% mysql cookbook < mail.sql
This method also makes it easy to deal with any triggers the table has. By default,
mysqldump writes the triggers to the dump file, so reloading the file copies the triggers
along with the table with no special handling.
In addition to restoring tables, mysqldump can be used to make copies of them, by
reloading the output into a different database. (If the destination database does not exist,
create it first.) The following examples show some useful table-copying commands.
Copying tables within a single MySQL server
• Copy a single table to a different database: