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!