Reduce DB file size

Question

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?

• Fiasco Labs: This happens if you haven't set Magento's cron trigger "cron.sh" or "cron.php" to be run periodically (every 15min minimum) and set log cleaning up in System Config. The log_* tables get huge...
• user2857: open your database in phpmyadmin, sort tables by size, and see
• user4351: If the dump is 2.5G, use xz. This "open in phpMyAdmin thing" is really mysql -e "SELECT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME) AS tbl, TABLE_ROWS AS nrows, ((DATA_LENGTH + INDEX_LENGTH)/1024/1024) AS MiB FROM TABLES ORDER BY MiB DESC LIMIT 10;" information_schema.
• Andrew Kett: They query that @Melvyn suggests is a good way to spot large database tables. Once you have cleared out your logs this will be a good way to spot any other problem tables. For example 3rd party extensions that aren't covered by the backup scripts already suggested
• B00MER: sales_quote tables can fill up if its a very active store: github.com/fbrnc/Aoe_QuoteCleaner

Answer 1

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'`

• user4351: This is the only answer that covers "I have a 5 year old store, never archived orders or customers and a catalog with thousands of products".

Answer 2

From your magento home directory in a ssh session run php -f shell/log.php clean

This will automatically clear out the safe mysql tables on your database. Usually this is set to run through a cron job but I've noticed magento likes to neglect it.

Answer 3

Here is what I would do:

1] Truncate the log tables.
Create a script and put it in shell/housekeeping.php

<?php
doSomeHouseKeeping();
function doSomeHouseKeeping() {
 $xml = simplexml_load_file(dirname(__FILE__) . '/../app/etc/local.xml', NULL, LIBXML_NOCDATA);
 if(is_object($xml)) {
 $db['host'] = $xml->global->resources->default_setup->connection->host;
 $db['name'] = $xml->global->resources->default_setup->connection->dbname;
 $db['user'] = $xml->global->resources->default_setup->connection->username;
 $db['pass'] = $xml->global->resources->default_setup->connection->password;
 $db['pref'] = $xml->global->resources->db->table_prefix;
 $tables = array(
 'dataflow_batch_export',
 'dataflow_batch_import',
 'log_customer',
 'log_quote',
 'log_summary',
 'log_summary_type',
 'log_url',
 'log_url_info',
 'log_visitor',
 'log_visitor_info',
 'log_visitor_online',
 'index_event',
 'report_event',
 'report_viewed_product_index',
 'report_compared_product_index',
 'catalog_compare_item',
 'catalogindex_aggregation',
 'catalogindex_aggregation_tag',
 'catalogindex_aggregation_to_tag'
 );
 mysql_connect($db['host'], $db['user'], $db['pass']) or die(mysql_error());
 mysql_select_db($db['name']) or die(mysql_error());
 foreach($tables as $table) {
 @mysql_query('TRUNCATE `'.$db['pref'].$table.'`');
 }
 } else {
 exit('Unable to load local.xml file');
 }
}

And run it as:
php -f shell/housekeeping.php
Note: You can use MageRun tool but I won't recommend it for production environment.

2] Dump the mysql using gzip compression:

mysqldump -u [user] -p[pass] [database] | gzip > [dump-file-name].sql.gz

3] Import the dumped file to your destination server:

gunzip < [dump-file-name].sql.gz | -u [user] -p[pass] [database]

• Andrew Kett: I understand the hesitance to use magerun in a production environment. However the ability to disable particular commands, e.g db:drop, with a local configuration file removes most of the danger IMHO. magerun.net/released-n98-magerun-version-1-94-0

Answer 4

Immediate log.php and db methods listed here as well http://docs.nexcess.net/article/magento-database-maintenance.html#sthash.4KDCAaeT.dpuf

For general tidyness of db.

From the Magento administrator interface, go to System> Configuration.

In the left menu under Advanced, click System.

Under Log Cleaning, change Enable Log Cleaning to Yes and configure the Save Log for 15 days:

Click Save Config.