Table Management - MySQL - page 137

% mysqldump cookbook mail > mail.sql
% mysql other_db < mail.sql
To dump multiple tables, name them all following the database name argument.
• Copy all tables in a database to a different database:
% mysqldump cookbook > cookbook.sql
% mysql other_db < cookbook.sql
When you name no tables after the database name, mysqldump dumps them all. To
also include stored routines and events, add the --routines and --events options
to the mysqldump command. (There is also a --triggers option, but it's unneeded
because, as mentioned previously, mysqldump dumps triggers with their associated
tables by default.)
• Copy a table, using a different name for the copy:
• Dump the table:
% mysqldump cookbook mail > mail.sql
• Reload the table into a different database that does not contain a table with that
name:
% mysql other_db < mail.sql
• Rename the table:
% mysql other_db
mysql> RENAME mail TO mail2;
Or, to move the table into another database at the same time, qualify the new
name with the database name:
% mysql other_db
mysql> RENAME mail TO cookbook.mail2;
To perform a table-copying operation without an intermediary file, use a pipe to connect
the mysqldump and mysql commands:
% mysqldump cookbook mail | mysql other_db
% mysqldump cookbook | mysql other_db
Copying tables between MySQL servers
The preceding commands use mysqldump to copy tables among the databases managed
by a single MySQL server. Output from mysqldump can also be used to copy tables from
one server to another. Suppose that you want to copy the mail table from the cook
book database on the local host to the other_db database on the host other-
host.example.com . One way to do this is to dump the output into a file:
% mysqldump cookbook mail > mail.sql