1

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.

  • 2
    Actually, you shouldn't - at all - be doing what you seem to do. Modifying the database structure (creating or altering tables or columns) is a task for a setup scripts. Populating fields or tables with initial/updated data is a task for data scripts. See also inchoo.net/magento/… – Christoph Farnleitner Feb 9 '18 at 21:08
  • 1
    @ChristophFarnleitner, we have set-up scripts for our extensions. All default settings are auto set/new tables after created. I am talking about transferring user populated data. For example you installed event module and on test server, website owner created 500 events (to save time and not do that on live) - how would you transfer them to live? Set-up script will create table for this module on live server, but not transfer 500 events that were created by owner on test server. – MployBy Feb 10 '18 at 1:01
  • I see, this is a slightly different topic than I did assume then. However, I would not dare to step down from my point saying that you just shouldn't touch the database directly. But, did you consider creating an XML-based export for your module by looping through all data you want to share with the other database? At the live env you than could populate the database again using a data script. – Christoph Farnleitner Feb 10 '18 at 12:10
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.