{"id":1113,"date":"2017-05-12T08:35:31","date_gmt":"2017-05-12T08:35:31","guid":{"rendered":"http:\/\/invisiblezero.net\/?p=1113"},"modified":"2024-07-22T09:21:07","modified_gmt":"2024-07-22T09:21:07","slug":"magento-2-clean-database-dump-for-development-purpose","status":"publish","type":"post","link":"http:\/\/ndthanh.com\/magento-2-clean-database-dump-for-development-purpose\/","title":{"rendered":"Magento 2 : Clean database dump for development purpose"},"content":{"rendered":"<p>As i showed you before for Magento 1, there are <a href=\"https:\/\/ndthanh.net\/magento-clean-database-dump-for-development-purpose\/\">SQL queries<\/a> that reduces database size for development enviroment. This time, I&#8217;m going to show you how to do so in Magento 2. Please take database backup prior to executing these queries!<\/p>\n<p><strong>Truncate order tables<\/strong><\/p>\n<p><!--more--><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET FOREIGN_KEY_CHECKS = 0;\nTRUNCATE TABLE `gift_message`;\nTRUNCATE TABLE `quote`;\nTRUNCATE TABLE `quote_address`;\nTRUNCATE TABLE `quote_address_item`;\nTRUNCATE TABLE `quote_id_mask`;\nTRUNCATE TABLE `quote_item`;\nTRUNCATE TABLE `quote_item_option`;\nTRUNCATE TABLE `quote_payment`;\nTRUNCATE TABLE `quote_shipping_rate`;\nTRUNCATE TABLE `reporting_orders`;\nTRUNCATE TABLE `sales_bestsellers_aggregated_daily`;\nTRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;\nTRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;\nTRUNCATE TABLE `sales_creditmemo`;\nTRUNCATE TABLE `sales_creditmemo_comment`;\nTRUNCATE TABLE `sales_creditmemo_grid`;\nTRUNCATE TABLE `sales_creditmemo_item`;\nTRUNCATE TABLE `sales_invoice`;\nTRUNCATE TABLE `sales_invoiced_aggregated`;\nTRUNCATE TABLE `sales_invoiced_aggregated_order`;\nTRUNCATE TABLE `sales_invoice_comment`;\nTRUNCATE TABLE `sales_invoice_grid`;\nTRUNCATE TABLE `sales_invoice_item`;\nTRUNCATE TABLE `sales_order`;\nTRUNCATE TABLE `sales_order_address`;\nTRUNCATE TABLE `sales_order_aggregated_created`;\nTRUNCATE TABLE `sales_order_aggregated_updated`;\nTRUNCATE TABLE `sales_order_grid`;\nTRUNCATE TABLE `sales_order_item`;\nTRUNCATE TABLE `sales_order_payment`;\nTRUNCATE TABLE `sales_order_status_history`;\nTRUNCATE TABLE `sales_order_tax`;\nTRUNCATE TABLE `sales_order_tax_item`;\nTRUNCATE TABLE `sales_payment_transaction`;\nTRUNCATE TABLE `sales_refunded_aggregated`;\nTRUNCATE TABLE `sales_refunded_aggregated_order`;\nTRUNCATE TABLE `sales_shipment`;\nTRUNCATE TABLE `sales_shipment_comment`;\nTRUNCATE TABLE `sales_shipment_grid`;\nTRUNCATE TABLE `sales_shipment_item`;\nTRUNCATE TABLE `sales_shipment_track`;\nTRUNCATE TABLE `sales_shipping_aggregated`;\nTRUNCATE TABLE `sales_shipping_aggregated_order`;\nTRUNCATE TABLE `tax_order_aggregated_created`;\nTRUNCATE TABLE `tax_order_aggregated_updated`;\nSET FOREIGN_KEY_CHECKS = 1;\n<\/pre>\n<p><strong>Truncate Customer tables<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET FOREIGN_KEY_CHECKS = 0;\nTRUNCATE TABLE `customer_address_entity`;\nTRUNCATE TABLE `customer_address_entity_datetime`;\nTRUNCATE TABLE `customer_address_entity_decimal`;\nTRUNCATE TABLE `customer_address_entity_int`;\nTRUNCATE TABLE `customer_address_entity_text`;\nTRUNCATE TABLE `customer_address_entity_varchar`;\nTRUNCATE TABLE `customer_entity`;\nTRUNCATE TABLE `customer_entity_datetime`;\nTRUNCATE TABLE `customer_entity_decimal`;\nTRUNCATE TABLE `customer_entity_int`;\nTRUNCATE TABLE `customer_entity_text`;\nTRUNCATE TABLE `customer_entity_varchar`;\nTRUNCATE TABLE `customer_grid_flat`;\nTRUNCATE TABLE `customer_log`;\nTRUNCATE TABLE `customer_log`;\nTRUNCATE TABLE `customer_visitor`;\nTRUNCATE TABLE `persistent_session`;\nTRUNCATE TABLE `wishlist`;\nTRUNCATE TABLE `wishlist_item`;\nTRUNCATE TABLE `wishlist_item_option`;\nSET FOREIGN_KEY_CHECKS = 1;\n<\/pre>\n<p><strong>Truncate Review tables<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nTRUNCATE TABLE `review`;\nTRUNCATE TABLE `review_detail`;\nTRUNCATE TABLE `review_entity_summary`;\nTRUNCATE TABLE `review_store`;\n<\/pre>\n<p><strong>Truncate Product tables<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET FOREIGN_KEY_CHECKS = 0;\nTRUNCATE TABLE `cataloginventory_stock_item`;\nTRUNCATE TABLE `cataloginventory_stock_status`;\nTRUNCATE TABLE `cataloginventory_stock_status_idx`;\nTRUNCATE TABLE `cataloginventory_stock_status_tmp`;\nTRUNCATE TABLE `catalog_category_product`;\nTRUNCATE TABLE `catalog_category_product_index`;\nTRUNCATE TABLE `catalog_category_product_index_tmp`;\nTRUNCATE TABLE `catalog_compare_item`;\nTRUNCATE TABLE `catalog_product_bundle_option`;\nTRUNCATE TABLE `catalog_product_bundle_option_value`;\nTRUNCATE TABLE `catalog_product_bundle_price_index`;\nTRUNCATE TABLE `catalog_product_bundle_selection`;\nTRUNCATE TABLE `catalog_product_bundle_selection_price`;\nTRUNCATE TABLE `catalog_product_bundle_stock_index`;\nTRUNCATE TABLE `catalog_product_entity`;\nTRUNCATE TABLE `catalog_product_entity_datetime`;\nTRUNCATE TABLE `catalog_product_entity_decimal`;\nTRUNCATE TABLE `catalog_product_entity_gallery`;\nTRUNCATE TABLE `catalog_product_entity_int`;\nTRUNCATE TABLE `catalog_product_entity_media_gallery`;\nTRUNCATE TABLE `catalog_product_entity_media_gallery_value`;\nTRUNCATE TABLE `catalog_product_entity_media_gallery_value_to_entity`;\nTRUNCATE TABLE `catalog_product_entity_media_gallery_value_video`;\nTRUNCATE TABLE `catalog_product_entity_text`;\nTRUNCATE TABLE `catalog_product_entity_tier_price`;\nTRUNCATE TABLE `catalog_product_entity_varchar`;\nTRUNCATE TABLE `catalog_product_index_eav`;\nTRUNCATE TABLE `catalog_product_index_eav_decimal`;\nTRUNCATE TABLE `catalog_product_index_eav_decimal_idx`;\nTRUNCATE TABLE `catalog_product_index_eav_decimal_tmp`;\nTRUNCATE TABLE `catalog_product_index_eav_idx`;\nTRUNCATE TABLE `catalog_product_index_eav_tmp`;\nTRUNCATE TABLE `catalog_product_index_price`;\nTRUNCATE TABLE `catalog_product_index_price_bundle_idx`;\nTRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`;\nTRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`;\nTRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_bundle_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`;\nTRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`;\nTRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_downlod_idx`;\nTRUNCATE TABLE `catalog_product_index_price_downlod_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_final_idx`;\nTRUNCATE TABLE `catalog_product_index_price_final_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_idx`;\nTRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`;\nTRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_opt_idx`;\nTRUNCATE TABLE `catalog_product_index_price_opt_tmp`;\nTRUNCATE TABLE `catalog_product_index_price_tmp`;\nTRUNCATE TABLE `catalog_product_index_tier_price`;\nTRUNCATE TABLE `catalog_product_index_website`;\nTRUNCATE TABLE `catalog_product_link`;\nTRUNCATE TABLE `catalog_product_link_attribute_decimal`;\nTRUNCATE TABLE `catalog_product_link_attribute_int`;\nTRUNCATE TABLE `catalog_product_link_attribute_varchar`;\nTRUNCATE TABLE `catalog_product_option`;\nTRUNCATE TABLE `catalog_product_option_price`;\nTRUNCATE TABLE `catalog_product_option_title`;\nTRUNCATE TABLE `catalog_product_option_type_price`;\nTRUNCATE TABLE `catalog_product_option_type_title`;\nTRUNCATE TABLE `catalog_product_option_type_value`;\nTRUNCATE TABLE `catalog_product_relation`;\nTRUNCATE TABLE `catalog_product_super_attribute`;\nTRUNCATE TABLE `catalog_product_super_attribute_label`;\nTRUNCATE TABLE `catalog_product_super_link`;\nTRUNCATE TABLE `catalog_product_website`;\nTRUNCATE TABLE `catalog_url_rewrite_product_category`;\nTRUNCATE TABLE `downloadable_link`;\nTRUNCATE TABLE `downloadable_link_price`;\nTRUNCATE TABLE `downloadable_link_purchased`;\nTRUNCATE TABLE `downloadable_link_purchased_item`;\nTRUNCATE TABLE `downloadable_link_title`;\nTRUNCATE TABLE `downloadable_sample`;\nTRUNCATE TABLE `downloadable_sample_title`;\nTRUNCATE TABLE `product_alert_price`;\nTRUNCATE TABLE `product_alert_stock`;\nTRUNCATE TABLE `report_compared_product_index`;\nTRUNCATE TABLE `report_viewed_product_aggregated_daily`;\nTRUNCATE TABLE `report_viewed_product_aggregated_monthly`;\nTRUNCATE TABLE `report_viewed_product_aggregated_yearly`;\nTRUNCATE TABLE `report_viewed_product_index`;\nSET FOREIGN_KEY_CHECKS = 1;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>As i showed you before for Magento 1, there are SQL queries that reduces database size for development enviroment. This time, I&#8217;m going to show you how to do so in Magento 2. Please take database backup prior to executing these queries! Truncate order tables SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `gift_message`; TRUNCATE TABLE `quote`;&#8230;<\/p>\n<p><a class=\"read-more\" href=\"http:\/\/ndthanh.com\/magento-2-clean-database-dump-for-development-purpose\/\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":1473,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44,60],"tags":[59,93,62],"aioseo_notices":[],"views":44,"_links":{"self":[{"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/posts\/1113"}],"collection":[{"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/comments?post=1113"}],"version-history":[{"count":1,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/posts\/1113\/revisions"}],"predecessor-version":[{"id":1270,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/posts\/1113\/revisions\/1270"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/media\/1473"}],"wp:attachment":[{"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/media?parent=1113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/categories?post=1113"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/ndthanh.com\/wp-json\/wp\/v2\/tags?post=1113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}