Magento - Syncs a live DB into a development db and updates Magento's base URLs to a localhost URL

000 - sync.md

Scripts for synching a development Magento database database with it's live counterpart.

• Windows OS
• Linux OS

001 - update.cmd

@ECHO OFF

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Imports the live site's database into the development site and updates the 
:: base URL of the dev site
::


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Variables - Modify these to your site
::
:: liveLogin & devLogin variables should be the name of your configured 
:: login-path. These need to be setup prior to script execution.
::
:: @see http://serverfault.com/questions/476228/whats-a-secure-alternative-to-using-a-mysql-password-on-the-command-line
:: @see http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
::
SET "liveDatabase=example"
SET "liveLogin=exampleLive"
SET "devDatabase=example"
SET "devLogin=exampleDev"

:: TODO: implement functionality for database prefix

:: Do Not include trailing slash or url protocol
SET "devDomain=example.localhost"

:: Include trailing slash if not blank. Omit starting slash
SET "magentoInstallDir="

SET "devIps=::1,127.0.0.1"


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::                                                                            ::   
::                  Sync Logic - Do not modify the rest                       ::
::                                                                            ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: SQL dump file name (YYYY-MM-DD_HH-MM-SS.sql)
:: See http://stackoverflow.com/q/1642677/1143274
::
FOR /f %%a IN ('WMIC OS GET LocalDateTime ^| FIND "."') DO SET DTS=%%a
SET DateTime=%DTS:~0,4%-%DTS:~4,2%-%DTS:~6,2%_%DTS:~8,2%-%DTS:~10,2%


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Dump the live database to file
::
ECHO Dumping live db structure "%liveDatabase%" into %DateTime%_STRUCTURE.sql
mysqldump --login-path=%liveLogin% --no-data %liveDatabase% > ./%DateTime%_STRUCTURE.sql

ECHO Dumping live db data "%liveDatabase%" into %DateTime%_DATA.sql 
mysqldump --login-path=%liveLogin% --no-create-info --ignore-table=%liveDatabase%.dataflow_batch_export --ignore-table=%liveDatabase%.dataflow_batch_import --ignore-table=%liveDatabase%.catalogsearch_query --ignore-table=%liveDatabase%.catalogsearch_fulltext --ignore-table=%liveDatabase%.catalogsearch_result --ignore-table=%liveDatabase%.coupon_aggregated --ignore-table=%liveDatabase%.customer_address_entity --ignore-table=%liveDatabase%.customer_address_entity_datetime --ignore-table=%liveDatabase%.customer_address_entity_decimal --ignore-table=%liveDatabase%.customer_address_entity_int --ignore-table=%liveDatabase%.customer_address_entity_text --ignore-table=%liveDatabase%.customer_address_entity_varchar --ignore-table=%liveDatabase%.customer_entity --ignore-table=%liveDatabase%.customer_entity_datetime --ignore-table=%liveDatabase%.customer_entity_decimal --ignore-table=%liveDatabase%.customer_entity_int --ignore-table=%liveDatabase%.customer_entity_text --ignore-table=%liveDatabase%.customer_entity_varchar --ignore-table=%liveDatabase%.index_event --ignore-table=%liveDatabase%.index_process_event --ignore-table=%liveDatabase%.log_customer --ignore-table=%liveDatabase%.log_quote --ignore-table=%liveDatabase%.log_summary --ignore-table=%liveDatabase%.log_summary_type --ignore-table=%liveDatabase%.log_url --ignore-table=%liveDatabase%.log_url_info --ignore-table=%liveDatabase%.log_visitor --ignore-table=%liveDatabase%.log_visitor_info --ignore-table=%liveDatabase%.log_visitor_online --ignore-table=%liveDatabase%.newsletter_queue --ignore-table=%liveDatabase%.newsletter_queue_link --ignore-table=%liveDatabase%.newsletter_subscriber --ignore-table=%liveDatabase%.newsletter_problem --ignore-table=%liveDatabase%.newsletter_queue_store_link --ignore-table=%liveDatabase%.poll --ignore-table=%liveDatabase%.poll_answer --ignore-table=%liveDatabase%.poll_store --ignore-table=%liveDatabase%.poll_vote --ignore-table=%liveDatabase%.rating_store --ignore-table=%liveDatabase%.report_compared_product_index --ignore-table=%liveDatabase%.report_event --ignore-table=%liveDatabase%.report_viewed_product_index --ignore-table=%liveDatabase%.review --ignore-table=%liveDatabase%.review_detail --ignore-table=%liveDatabase%.review_entity_summary --ignore-table=%liveDatabase%.sales_bestsellers_aggregated_daily --ignore-table=%liveDatabase%.sales_bestsellers_aggregated_monthly --ignore-table=%liveDatabase%.sales_bestsellers_aggregated_yearly --ignore-table=%liveDatabase%.sales_invoiced_aggregated --ignore-table=%liveDatabase%.sales_invoiced_aggregated_order --ignore-table=%liveDatabase%.sales_order_aggregated_created --ignore-table=%liveDatabase%.sales_order_aggregated_updated --ignore-table=%liveDatabase%.sales_recurring_profile --ignore-table=%liveDatabase%.sales_recurring_profile_order --ignore-table=%liveDatabase%.sales_refunded_aggregated --ignore-table=%liveDatabase%.sales_refunded_aggregated_order --ignore-table=%liveDatabase%.sales_shipping_aggregated --ignore-table=%liveDatabase%.sales_shipping_aggregated_order --ignore-table=%liveDatabase%.sales_payment_transaction --ignore-table=%liveDatabase%.sales_flat_creditmemo --ignore-table=%liveDatabase%.sales_flat_creditmemo_comment --ignore-table=%liveDatabase%.sales_flat_creditmemo_grid --ignore-table=%liveDatabase%.sales_flat_creditmemo_item --ignore-table=%liveDatabase%.sales_flat_order --ignore-table=%liveDatabase%.sales_flat_order_address --ignore-table=%liveDatabase%.sales_flat_order_grid --ignore-table=%liveDatabase%.sales_flat_order_item --ignore-table=%liveDatabase%.sales_flat_order_status_history --ignore-table=%liveDatabase%.sales_flat_quote --ignore-table=%liveDatabase%.sales_flat_quote_address --ignore-table=%liveDatabase%.sales_flat_quote_address_item --ignore-table=%liveDatabase%.sales_flat_quote_item --ignore-table=%liveDatabase%.sales_flat_quote_item_option --ignore-table=%liveDatabase%.sales_flat_order_payment --ignore-table=%liveDatabase%.sales_flat_quote_payment --ignore-table=%liveDatabase%.sales_flat_quote_shipping_rate --ignore-table=%liveDatabase%.sales_flat_shipment --ignore-table=%liveDatabase%.sales_flat_shipment_item --ignore-table=%liveDatabase%.sales_flat_shipment_grid --ignore-table=%liveDatabase%.sales_flat_shipment_track --ignore-table=%liveDatabase%.sales_flat_invoice --ignore-table=%liveDatabase%.sales_flat_invoice_grid --ignore-table=%liveDatabase%.sales_flat_invoice_item --ignore-table=%liveDatabase%.sales_flat_invoice_comment --ignore-table=%liveDatabase%.sales_order_tax --ignore-table=%liveDatabase%.sales_order_tax_item --ignore-table=%liveDatabase%.sendfriend_log --ignore-table=%liveDatabase%.tag --ignore-table=%liveDatabase%.tag_relation --ignore-table=%liveDatabase%.tag_summary --ignore-table=%liveDatabase%.wishlist %liveDatabase% > ./%DateTime%_DATA.sql


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Drop the development database and create new
::
ECHO Dropping development db "%devDatabase%"
mysql --login-path=%devLogin% -e "DROP DATABASE `%devDatabase%`;"

ECHO Creating development db "%devDatabase%"
mysql --login-path=%devLogin% -e "CREATE DATABASE `%devDatabase%`;"
    

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::    
:: Import dumped sql file into dev database
::
ECHO Importing "%DateTime%_STRUCTURE.sql" into development db "%devDatabase%"
mysql --login-path=%devLogin% %devDatabase% < .\%DateTime%_STRUCTURE.sql
ECHO Importing "%DateTime%_DATA.sql" into development db "%devDatabase%"
mysql --login-path=%devLogin% %devDatabase% < .\%DateTime%_DATA.sql


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Update various Magento settings
::
ECHO Updating various settings on development database "%devDatabase%"...

 
ECHO Setting base urls to "http://%devDomain%/%magentoInstallDir%"
mysql --login-path=%devLogin% --database=%devDatabase% -e "UPDATE `core_config_data` SET `value` = 'http://%devDomain%/%magentoInstallDir%' WHERE `path` IN ('web/unsecure/base_url', 'web/secure/base_url');"

ECHO Setting cookie domain to %devDomain%
mysql --login-path=%devLogin% --database=%devDatabase% -e "UPDATE `core_config_data` SET `value` = '%devDomain%' WHERE `path` = 'web/cookie/cookie_domain';"

ECHO Turning on Mgt_DeveloperToolbar
mysql --login-path=%devLogin% --database=%devDatabase% -e "UPDATE `core_config_data` SET `value` = '1' WHERE `path` = 'mgt_developertoolbar/mgt_developertoolbar/active';"

ECHO Setting development IPs to "%devIps%"
mysql --login-path=%devLogin% --database=%devDatabase% -e "UPDATE `core_config_data` SET `value` = '%devIps%' WHERE `path` = 'mgt_developertoolbar/mgt_developertoolbar/allow_ips';"

ECHO Turning off all cache types
mysql --login-path=%devLogin% --database=%devDatabase% -e "UPDATE `core_cache_option` SET `value` = 0 WHERE 1;"


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: We're done here
::
ECHO Sync complete

002 - update.sh

#!/bin/bash

# Imports the live site's database into the development site and updates the base URL of the dev site

# Variables - Modify these to your site
dbHost="example.com" # assumes both databasses are on same host

liveUsername='example_live'
livePassword='example_live'
liveDatabase='example_live'

devUsername='example_dev'
devPassword='example_dev'
devDatabase='example_dev'

devBaseUrl="http://example.localhost/"

devIps="::1" # Localhost


##
# Sync Logic - Do not modify the rest
##

# Temp SQL file will be the current unix epoch timestamp
tempSqlFile="$(date +"%s").sql"

# Dump the live database to file
echo "Dumping \"$liveDatabase\" into temp file \"$tempSqlFile\""
mysqldump --user=$liveUsername --password=$livePassword --host=$dbHost $liveDatabase > ./$tempSqlFile

# Drop the development database
echo "Dropping development database \"$devDatabase\" and recreating fresh database \"$devDatabase\""
mysql --user=$devUsername --password=$devPassword --host=$dbHost $devDatabase -e "
    DROP DATABASE \`$devDatabase\`;
    CREATE DATABASE \`$devDatabase\`;
"


# Import dumped sql file into dev database
echo "Importing \"$tempSqlFile\" into \"$devDatabase\""
mysql --user=$devUsername --password=$devPassword --host=$dbHost $devDatabase < ./$tempSqlFile

# Remove the temp sql file
echo "Deleting $tempSqlFile"
rm ./$tempSqlFile

# Update various Magento settings
# TODO: Need to handle cookie domain name as well
echo "Updating base urls on \"$devDatabase\" to \"$devBaseUrl\""
echo "Turning on Mgt_DeveloperToolbar"
echo "Setting development IPs to \"$devIps\""
echo "Turning off all cache types"
mysql --user=$devUsername --password=$devPassword --host=$dbHost $devDatabase -e "
    UPDATE \`core_config_data\` SET \`value\` = '$devBaseUrl' WHERE \`path\` IN ('web/unsecure/base_url', 'web/secure/base_url');
    UPDATE \`core_config_data\` SET \`value\` = '1' WHERE \`path\` = 'mgt_developertoolbar/mgt_developertoolbar/active';
    UPDATE \`core_config_data\` SET \`value\` = '$devIps' WHERE \`path\` = 'mgt_developertoolbar/mgt_developertoolbar/allow_ips';
    UPDATE \`core_cache_option\` SET \`value\` = 1 WHERE 1;
"

echo 'Sync Process Complete';
# Done!