Importing minor DB changes from local to live server

Question

I am wondering what's the best way to import minor DB changes from local to live server.

We have local dev environment where we are adding new features to the site and once tested push them to life server.

Files are covered by git, so it's easy part.

Difficult part is that often we change some settings in the database, populate the new tables and redoing it all again on live server is very time consuming.

So last time we took latest dump of the live database, made did using Beyond Compare analyzing what tables were changed by us on local server, and imported only these tables.

It was ok, but there should be better tools for that than Beyond Compare, or better approach.

---

p.s. Initial database structures are identical, products, IDs.

The changes are minor - like adding new module, and prepopulating needed details on local.

Answer 1

I agree with Christoph's comment that you should not be doing this except through setup scripts.

If you MUST do this, instead of trying to append to your existing database - create a new one and import the entire backup from your local version.

You should always do a backup of your DB before changes like this, so do an export of your DB via SSH:

mysqldump -u(server username) -p (magento_sql_db) > (filename_for_backup).sql

You will be prompted for your server password, then be returned to a new line once the sqldump is complete.

Once you have the backup .sql file downloaded locally, delete it from your server. Next you need to export your local SQL DB to .sql and upload it to the same directory you downloaded the backup from. Then run this command:

mysql -u(server username) -p (sql_db_being_imported_to) < (local_sql_db_filename).sql

Again, you'll be prompted for password and returned to new line once import is complete. Add the same user and permissions from the original database to this new imported version.

Once you do this, update your local.xml file to point to this new database and manually flush the cache by deleting the contents of the /var/cache folder in your magento directory.

Obviously some changes will need to be made, but a lot of those can be mitigated through SQL queries.

I keep this on hand for updating the subdomain for backups of my production to the newest version of staging - when possible, always use a FQDN to prevent cookie issues:

update core_config_data
set value = replace(value, 'www', 'staging')
where path in ('web/unsecure/base_url', 'web/secure/base_url', 
'web/cookie/cookie_domain');

I'm no SQL expert, but there are plenty of other queries you can run to update information in your table quickly, instead of hunting it down and changing it manually.

This may not be ideal if you have made a lot of other changes in your DB that are untested and should only be used if you really really really need to make manual adjustments to your DB (which you shouldn't)