Monday, August 20, 2012

Pre-dump database reduction script

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