Database Optimization – How to Reduce DB File Size

database

I'm trying to copy my live store to a different test server

Due to limit space on my test server, I need to reduce my DB file size when import DB

I got 2.5 GB DB file when I dump on live store and hope I can get less than 1 GB DB file

Which table has much space and safe to delete?

I already create and copy my live DB as other db on my live server, so it's safe to delete any tables (like customer, order)

I don't have to have all products on test serer (just few products is okay)

Is there good, safe way I can reduce DB file size for creating test store?

Best Answer

Log tables if not cleared frequently can build up and make big database sizes. As pointed out by others this should really be handled by magentos cron if log cleaning is enabled in admin, but for the purpose of reducing your backup any tables prefixed with log_ can pretty safely be truncated.

Also sales and customer data is unlikely needed in a development environment so you could skip their data in your backup.

Have a look at the magerun cli tool, specifically the db:dump command. The --strip and --compression flags can be quite useful, you should be able to reduce your dump file size significantly.

e.g ./n98-magerun.phar db:dump --compression="gzip" --strip="@development" db.sql will skip the data in all sales, customer and log tables and also gzip the sql file. Behind the scenes it will do a mysqldump command similar to this:

mysqldump --single-transaction --quick 
--ignore-table=magento_db.catalogsearch_fulltext 
--ignore-table=magento_db.catalogsearch_query 
--ignore-table=magento_db.catalogsearch_result 
--ignore-table=magento_db.core_session 
--ignore-table=magento_db.customer_address_entity 
--ignore-table=magento_db.customer_address_entity_datetime 
--ignore-table=magento_db.customer_address_entity_decimal 
--ignore-table=magento_db.customer_address_entity_int 
--ignore-table=magento_db.customer_address_entity_text 
--ignore-table=magento_db.customer_address_entity_varchar 
--ignore-table=magento_db.customer_entity 
--ignore-table=magento_db.customer_entity_datetime 
--ignore-table=magento_db.customer_entity_decimal 
--ignore-table=magento_db.customer_entity_int 
--ignore-table=magento_db.customer_entity_text 
--ignore-table=magento_db.customer_entity_varchar 
--ignore-table=magento_db.dataflow_batch 
--ignore-table=magento_db.dataflow_batch_export 
--ignore-table=magento_db.dataflow_batch_import 
--ignore-table=magento_db.dataflow_import_data 
--ignore-table=magento_db.dataflow_session 
--ignore-table=magento_db.log_url 
--ignore-table=magento_db.log_url_info 
--ignore-table=magento_db.log_visitor 
--ignore-table=magento_db.log_visitor_info 
--ignore-table=magento_db.log_visitor_online 
--ignore-table=magento_db.newsletter_problem 
--ignore-table=magento_db.newsletter_queue 
--ignore-table=magento_db.newsletter_queue_link 
--ignore-table=magento_db.newsletter_queue_store_link 
--ignore-table=magento_db.newsletter_subscriber 
--ignore-table=magento_db.newsletter_template 
--ignore-table=magento_db.report_compared_product_index 
--ignore-table=magento_db.report_event 
--ignore-table=magento_db.report_viewed_product_aggregated_daily 
--ignore-table=magento_db.report_viewed_product_aggregated_monthly 
--ignore-table=magento_db.report_viewed_product_aggregated_yearly 
--ignore-table=magento_db.report_viewed_product_index 
--ignore-table=magento_db.sales_bestsellers_aggregated_daily 
--ignore-table=magento_db.sales_bestsellers_aggregated_monthly 
--ignore-table=magento_db.sales_bestsellers_aggregated_yearly 
--ignore-table=magento_db.sales_flat_creditmemo 
--ignore-table=magento_db.sales_flat_creditmemo_comment 
--ignore-table=magento_db.sales_flat_creditmemo_grid 
--ignore-table=magento_db.sales_flat_creditmemo_item 
--ignore-table=magento_db.sales_flat_invoice 
--ignore-table=magento_db.sales_flat_invoice_comment  
--ignore-table=magento_db.sales_flat_invoice_grid 
--ignore-table=magento_db.sales_flat_invoice_item 
--ignore-table=magento_db.sales_flat_order 
--ignore-table=magento_db.sales_flat_order_address 
--ignore-table=magento_db.sales_flat_order_grid 
--ignore-table=magento_db.sales_flat_order_item 
--ignore-table=magento_db.sales_flat_order_payment 
--ignore-table=magento_db.sales_flat_order_status_history 
--ignore-table=magento_db.sales_flat_quote 
--ignore-table=magento_db.sales_flat_quote_address 
--ignore-table=magento_db.sales_flat_quote_address_item 
--ignore-table=magento_db.sales_flat_quote_item 
--ignore-table=magento_db.sales_flat_quote_item_option 
--ignore-table=magento_db.sales_flat_quote_payment 
--ignore-table=magento_db.sales_flat_quote_shipping_rate 
--ignore-table=magento_db.sales_flat_shipment 
--ignore-table=magento_db.sales_flat_shipment_comment 
--ignore-table=magento_db.sales_flat_shipment_grid 
--ignore-table=magento_db.sales_flat_shipment_item 
--ignore-table=magento_db.sales_flat_shipment_track 
--ignore-table=magento_db.sales_order_aggregated_created 
--ignore-table=magento_db.sales_order_aggregated_updated 
--ignore-table=magento_db.sales_order_tax 
--ignore-table=magento_db.sales_order_tax_item 
--ignore-table=magento_db.sales_payment_transaction 
--ignore-table=magento_db.sales_recurring_profile 
--ignore-table=magento_db.sales_recurring_profile_order 
--ignore-table=magento_db.sales_refunded_aggregated 
--ignore-table=magento_db.sales_refunded_aggregated_order 
-h'host' -u'username' -p'password' 'magento_db' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip -c  >> 'db.sql.gz'`
Related Topic