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
Looks like a pretty similar list of tables that I would normally truncate too Brent.
ReplyDeleteDepending on the size of it, I think you could consider tidying up sales_flat_quote. Especially of guest customers or old quotes.
Another thing to be careful about if the media has been synced with (read, stored in) the database at any point as they'll be another table to truncate.
I suppose I should have two scripts, one for a simple cleanup before a dump, and one for a good clean up if you are going to use it for development purposes
ReplyDeletemagento extension development
ReplyDeleteNow this is Informative writting, KEep up the good work.