Posted on

comparing core_config_data

Find core_config_data differences between a production and development db.

SELECT path, value FROM (
SELECT path, value FROM your_dev_db.core_config_data
UNION ALL
SELECT path, value FROM your_prod_db.core_config_data
) tbl
GROUP BY path, value
HAVING count(*) = 1
ORDER BY path


query("select * from core_config_data where path in (
'admin/url/custom',
'admin/url/use_custom',
'carriers/ups/access_license_number',
'carriers/ups/allowed_methods',
'carriers/ups/dest_type',
'carriers/ups/dest_type',
'carriers/ups/negotiated_active',
'carriers/ups/negotiated_active',
'carriers/ups/password',
'carriers/ups/username',
'dev/css/merge_css_files',
'dev/js/merge_files',
'payment/authorizenet/cgi_url',
'payment/authorizenet/cgi_url_td',
'payment/authorizenet/login',
'payment/authorizenet/merchant_email',
'payment/authorizenet/test',
'payment/authorizenet/trans_key',
'payment/cashondelivery/active',
'payment/paypal_direct/cctypes',
'payment/paypal_direct/payment_action',
'payment/paypal_direct/sort_order',
'paypal/general/business_account',
'paypal/wpp/api_password',
'paypal/wpp/api_signature',
'paypal/wpp/api_username',
'paypal/wpp/sandbox_flag',
'shipping/origin/postcode',
'shipping/origin/region_id',
'system/currency/installed',
'web/cookie/cookie_domain',
'web/cookie/cookie_path',
'web/secure/base_js_url',
'web/secure/base_media_url',
'web/secure/base_skin_url',
'web/secure/base_url',
'web/unsecure/base_js_url',
'web/unsecure/base_media_url',
'web/unsecure/base_skin_url',
'web/unsecure/base_url')");

foreach ($result as $row) {
echo "update `core_config_data` set `value` = '" . $row['value'] . "' where `path`='" . $row['path'] . "';" . PHP_EOL;
}
?>

Posted on

mysqldump from CLI select tables

Suppose you need to take just a few tables from a database, like all the customer tables from a magento install. You just need to set up a few shell variables and you can mysqldump away! You’ll need your mysql database user name and password.

$:DBTODUMP=dbname_here
$:SQL="select group_concat(table_name separator ' ') from information_schema.tables where table_schema='$DBTODUMP' and table_name like 'customer%'"
$:TBLIST=`mysql -u db_user -pPASSWORD -AN -e"$SQL"`
$:mysqldump -u db_user -pPASSWORD $DBTODUMP $TBLIST > customer_tables.sql
Posted on

mysqldump to gz

mysqldump --single-transaction -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz

the next thing you’re gonna ask …

scp username@hostname:/path/to/file/from/root/dumpfilename.sql.gz ./dumpfilename.sql.gz

and after that …


sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql

and if you can’t create InnoDB tables and the create statements contain ROW_FORMAT

sed -ie 's/ROW_FORMAT=FIXED//g'