If you have setup a new Magento installation to match your live website and want to move accross the old orders and your customer data then follow the following steps.
I prefer to import my database tables via shell rather than using PHPMyAdmin as it has a tendency to hang if you have a lot of data
Exporting the Tables from your live database
To export the correct tables simply export any tables which begin with sales_ and customers_:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mg_customer_address_entity mg_customer_address_entity_datetime mg_customer_address_entity_decimal mg_customer_address_entity_int mg_customer_address_entity_text mg_customer_address_entity_varchar mg_customer_eav_attribute mg_customer_eav_attribute_website mg_customer_entity mg_customer_entity_datetime mg_customer_entity_decimal mg_customer_entity_int mg_customer_entity_text mg_customer_entity_varchar mg_customer_form_attribute mg_customer_group |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
mg_sales_bestsellers_aggregated_daily mg_sales_bestsellers_aggregated_monthly mg_sales_bestsellers_aggregated_yearly mg_sales_billing_agreement mg_sales_billing_agreement_order mg_sales_flat_creditmemo mg_sales_flat_creditmemo_comment mg_sales_flat_creditmemo_grid mg_sales_flat_creditmemo_item mg_sales_flat_invoice mg_sales_flat_invoice_comment mg_sales_flat_invoice_grid mg_sales_flat_invoice_item mg_sales_flat_order mg_sales_flat_order_address mg_sales_flat_order_grid mg_sales_flat_order_item mg_sales_flat_order_payment mg_sales_flat_order_status_history mg_sales_flat_quote mg_sales_flat_quote_address mg_sales_flat_quote_address_item mg_sales_flat_quote_item mg_sales_flat_quote_item_option mg_sales_flat_quote_payment mg_sales_flat_quote_shipping_rate mg_sales_flat_shipment mg_sales_flat_shipment_comment mg_sales_flat_shipment_grid mg_sales_flat_shipment_item mg_sales_flat_shipment_track mg_sales_invoiced_aggregated mg_sales_invoiced_aggregated_order mg_sales_order_aggregated_created mg_sales_order_aggregated_updated mg_sales_order_status mg_sales_order_status_label mg_sales_order_status_state mg_sales_order_tax mg_sales_order_tax_item mg_sales_payment_transaction mg_sales_recurring_profile mg_sales_recurring_profile_order mg_sales_refunded_aggregated mg_sales_refunded_aggregated_order mg_sales_shipping_aggregated mg_sales_shipping_aggregated_order |
You will want to export them with the ‘Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement’ checked.
Once you have exported these files from PHPMyAdmin you will need to open up the files and add the following line to the beginning of the file:
|
1 |
SET foreign_key_checks = 0; |
And this line to the very end of the file:
|
1 |
SET foreign_key_checks = 1; |
This disables the foreign key checks for the duration of the import, it might not be recommended normally but as the target database is “empty” it fine to do.
Importing via Shell
Upload your sql file to your server and then navigate to it in your shell panel, once there run the following command (replacing the place holders with your settings):
|
1 |
mysql -u db_username -p db_name < your_sql_file_name.sql |
you will then be prompted for the password, enter it and hit return and the file will be imported into the database. Just to ensure everything comes in smoothly I recommend clearing the cache and then log out of your target Magento store and log back in and you should find your dashboard is now up to date with your latest orders and customers.
