4

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?

Improve this question
5
  • 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... Commented Feb 15, 2015 at 6:42
  • open your database in phpmyadmin, sort tables by size, and see Commented Feb 15, 2015 at 13:06
  • 1
    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. Commented Feb 15, 2015 at 14:36
  • 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 Commented Feb 15, 2015 at 22:52
  • sales_quote tables can fill up if its a very active store: github.com/fbrnc/Aoe_QuoteCleaner Commented Feb 23, 2015 at 6:21

4 Answers 4

Reset to default
7

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'`
Improve this answer
1
  • 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". Commented Feb 15, 2015 at 14:41
7

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.

Improve this answer
0
2

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]
Improve this answer
1
  • 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 Commented Feb 15, 2015 at 8:40
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.