how to export multi table in mysql?

Question

i have 10+ tables, i want to export them to another datebase. how could i do that? i tried select * from table_a, table_b into ourfile "/tmp/tmp.data", but it joined the two tables.

Answer 1

It's probably too late, but for the record:

Export an entire database:

mysqldump -u user -p database_name > filename.sql

Export only one table of the database:

mysqldump -u user -p database_name table_name > filename.sql

Export multiple tables of the database

Just like exporting one table, but keep on writing table names after the first table name (with one space between each name). Example exporting 3 tables:

mysqldump -u user -p database_name table_1 table_2 table_3 > filename.sql

Notes:

The tables are exported (i.e. written in the file) in the order in which they are written down in the command.

All of the examples above export the structure and the data of the database or table. To export only the structure, use no-data. Example exporting only one table of the database, but with no-data:

mysqldump -u user -p --no-data database_name table_name > filename.sql

Answer 2

Export mysqldump -u user -p mydatabasename > filename.sql

Import mysql -u user -p anotherdatabase < filename.sql