Pre-dump database reduction script

This entry was posted on October 01, 2012 by Brent W Peterson

Big databases are always a problem and it is handy to clean up your database before you are going to do a db dump. This is most handy when you have to move the dump from one server to another.

The syntax for you file will be something like this

truncate dataflow_batch_export;
truncate dataflow_batch_import;
truncate log_customer;
truncate log_quote;
truncate log_summary;
truncate log_summary_type;
truncate log_url;
truncate log_url_info;
truncate log_visitor;
truncate log_visitor_info;
truncate log_visitor_online;
truncate report_viewed_product_index;
truncate report_compared_product_index;
truncate report_event;
truncate report_viewed_product_aggregated_daily;
truncate report_viewed_product_aggregated_monthly;
truncate report_viewed_product_aggregated_yearly;
truncate sendfriend_log;

Then you want to save this as a sql file. (cleandb.sql)

Then you can run it against your current database (After you make a backup) like this:

First I would take a current backup of your database like this

mysqldump -p'password' -u user -h hostaddress databasename | gzip > backup.sql.gz

Then run the clean script

mysql -p'password' -u user -h hostaddress databasename < cleandb.sql