Mysql – Disable Foreign key with Mysqldump

Monday, 28. November 2011

Here is a script/flag you will need to disable foreign keys while backing up your database with mysqldump

mysql -uUSER -pPASSWORS --disable-keys DATABASE_NAME > dump.sql

Enjoy

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

Compress MySqlDump Database Output Terminal command

Thursday, 22. September 2011

If you want to compress the output of your mysqldump here is the command you will need to use.

mysqldump -uUSER -pPASSWORD DATABASE|gzip > dump.SQL.gz

You can also use bzip2 compression:

mysqldump -uUSER -pPASSWORD DATABASE|bzip2 > dump.SQL.bz2

Enjoy!