Mysql – thesqldump skip one table

MySQL

I'm running a cronjob to backup our system using mysqldump. The database contains 90 or so tables. One of these tables is HUGE and every once in a while causes the dump to fail.

From the manual I see that you can specify specific tables to dump

shell> mysqldump [options] db_name [tbl_name ...]

This got me thinking. What if I have two jobs, one for dumping the huge table, and one for all the others. To accomplish this it would be nice if I could to something like

shell> mysqldump -u backupuser -p database huge_table > db_huge_table.sql
shell> mysqldump -u backupuser -p database --skip huge_table > db_rest.sql

Unfortenately I'm not seeing such and option. I could of course explicitly state the 90 tables, but that just seems like a mess.

Another option would be a script of some sort, but before checking that route I'll try this resource.

MySQL is 5.1.61 on CentOS 6.2

Best Answer

You can use the --ignore command line switch

   ยท   --ignore-table=db_name.tbl_name

       Do not dump the given table, which must be specified using both the
       database and table names. To ignore multiple tables, use this
       option multiple times. This option also can be used to ignore
       views.

someting like 

shell> mysqldump -u backupuser -p database --ignore-table=database.huge_table > db_rest.sql
Related Topic