Exclude Tables With Mysqldump – Reduce the Size of Magento database backup

Wednesday, 12. October 2011

If you have ever wondered or be in need of excluding certain tables from your Mysqldump, here is snippet for you!

Basically you only have to specify the –ignore-table flag with your Mysqldump command. In order to exclude as multiple tables you will need to repeat the command as many times as the tables you want to exclude.

One important thing to remember, the –ignore-table flag expects you to specify the full table path i.e include the database name such as dbname.tablename.

Example: How to exclude Magento log table from mysqldump

mysqldump -uUSERNAME -pPASSWORD  --ignore-table=<DBASE_NAME>.log_url --ignore-table=<DBASE_NAME>.log_url_info  --ignore-table=<DBASE_NAME>.log_visitor --ignore-table=<DBASE_NAME>.log_visitor_info  --ignore-table=<DBASE_NAME>.log_customer --ignore-table=<DBASE_NAME>.log_quote --ignore-table=<DBASE_NAME>.report_event  --ignore-table=<DBASE_NAME>.index_event --ignore-table=<DBASE_NAME>.index_process_event  --ignore-table=<DBASE_NAME>.report_viewed_product_index --ignore-table=<DBASE_NAME>.dataflow_batch_export  --ignore-table=<DBASE_NAME>.dataflow_batch_import <DBASE_NAME> | gzip > <DBASE_NAME>_dump.sql.gz

Disable Foreign Key Checks AND Ignore inserts

mysqldump -uUSERNAME -pPASSWORD  --ignore-table=<DBASE_NAME>.log_url --ignore-table=<DBASE_NAME>.log_url_info  --ignore-table=<DBASE_NAME>.log_visitor --ignore-table=<DBASE_NAME>.log_visitor_info  --ignore-table=<DBASE_NAME>.log_customer --ignore-table=<DBASE_NAME>.log_quote --ignore-table=<DBASE_NAME>.report_event  --ignore-table=<DBASE_NAME>.index_event --ignore-table=<DBASE_NAME>.index_process_event  --ignore-table=<DBASE_NAME>.report_viewed_product_index --ignore-table=<DBASE_NAME>.dataflow_batch_export  --ignore-table=<DBASE_NAME>.dataflow_batch_import --disable-keys --extended-insert --insert-ignore  <DBASE_NAME> | gzip > <DBASE_NAME>_dump.sql.gz

Leave a Reply

You must be logged in to post a comment.