mysql dump tables only

Question

In my database I have some tables and views. How can I export all the tables (and not the views) from my database from command line?

Answer 1

To ignore a single view from your DB for Dump:

mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view_name db > db.sql

To ignore multiple view from your Db for Dump:

mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view1 --ignore-table=db.view2 db > db.sql

NOTE: to ignore multiple views for dump use --ignore-table option multiple times.

Answer 2

The current implementation mysqldump won't create dumps without views -- and furthermore, (last time I checked) views are actually created twice -- once as a table, then the table is dropped and replaced with a view. So you can't just filter out the "CREATE VIEW" command, unless that behavior has been modified.

However, mysqldump will take a list of tables as parameters following the database name. Something like this:

mysqldump -ujoe -pmysecret joesdb posts tags comments users 

Answer 3

Backuping a single table from a database

mysqldump -uUSERNAME -pPASWORD DATABASE TABLE_NAME --host=HOST_NAME > c:\TABLE_NAME.sql

Restoring a single table from a database dump

mysql -uUSERNAME -pPASSWORD DATABASE --host=HOST_NAME < c:\TABLE_NAME.sql