>Magento Tip: Reduce Magento Database Size

Magento Tip: Reduce Magento Database Size

Normally the project that we have to back up a Magento Database, we often overtake all of databases.

During the backup progress, we found that there were unnecessary tables to import, which doesn’t make the site affected however it takes so long times for you. Actually we only need to export the table structure without database.

So we would like to introduce the way to optimize the process exporting database by skipping log table.

Magento 1

For Magento 1, the tables listed below may be ignored

  • dataflow_batch_export
  • dataflow_batch_import
  • index_process_event
  • log_customer
  • log_quote
  • log_summary
  • log_summary_type
  • log_url log_url_info
  • log_visitor
  • log_visitor_info
  • log_visitor_online
  • report_event
  • core_session
  • index_event
  • catalogsearch_fulltext
  • catalog_product_index_eav
  • catalog_product_index_price
  • report_compared_product_index
  • report_viewed_product_index
  • catalog_category_product_index

Note: Make sure that the site doesn’t have any URL Rewrite customized (manual add via magento admin), you can ignore the core_url_rewrite table. When you’re re-imported, you only need reindex to Magento auto generate data for this table.

mysqldump -u user -ppassword --no-data  magento_db > magento_backup.sql && mysqldump -u user -ppassword --no-create-info --ignore-table=magento_db.dataflow_batch_export --ignore-table=magento_db.dataflow_batch_import --ignore-table=magento_db.index_process_event --ignore-table=magento_db.log_customer --ignore-table=magento_db.log_quote --ignore-table=magento_db.log_summary --ignore-table=magento_db.log_summary_type --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.report_event    --ignore-table=magento_db.core_session --ignore-table=magento_db.index_event --ignore-table=magento_db.catalogsearch_fulltext --ignore-table=magento_db.catalog_product_index_eav --ignore-table=magento_db.catalog_product_index_price --ignore-table=magento_db.report_compared_product_index --ignore-table=magento_db.report_viewed_product_index --ignore-table=magento_db.catalog_category_product_index 

Magento 2

Same as Magento 1, Magento 2 has the tables ignored also:

  • report_event
  • report_viewed_product_index
  • customer_visitor
  • customer_log
  • session
  • report_compared_product_index
  • catalog_category_product_index
  • catalog_product_index_eav
  • catalog_product_index_price
  • catalogsearch_fulltext_scope1
  • customer_grid_flat
  • design_config_grid_flat
  • importexport_importdata
mysqldump -u user -ppassword --no-data  magento_db > magento_backup.sql && mysqldump -u user -ppassword --no-create-info  --ignore-table=magento_db.report_event --ignore-table=magento_db.report_viewed_product_index --ignore-table=magento_db.customer_visitor --ignore-table=magento_db.customer_log --ignore-table=magento_db.session --ignore-table=magento_db.report_compared_product_index --ignore-table=magento_db.catalog_category_product_index --ignore-table=magento_db.catalog_product_index_eav --ignore-table=magento_db.catalog_product_index_price --ignore-table=magento_db.catalogsearch_fulltext_scope1 --ignore-table=magento_db.customer_grid_flat  --ignore-table=magento_db.design_config_grid_flat --ignore-table=magento_db.importexport_importdata

Note: When we backup database by importing, we need to reindex site in order that functions work normal.

Hope the article helful to you!

< Previous Post
Next Post >