Syncing magento database froms development to production

This is a common situation for developers. It's much easier to modify code and schema and be assured that all is well when there is a small codebase which is thoroughly understood and doesn't have too much flexibility for UI. Of course, this is not the case with Magento, which can be quite difficult to work into automated testing and continuous integration schemes. That said, there are some knowable, testable behaviors on which you can rely.

An Overview

When dealing with local development which is merged to production, one must be assured that the schema and data changes relevant to new or changed functionality are also applied when the filesystem is updated. This is actually how Magento itself works. Module configuration files can supply a version number and can configure setup resources. This information is used to enter into a schema & data modification workflow which results in version information being added to the database. It is the consistency between file-designated version number and database-registered version number that one can / the system can infer that the database is in the appropriate state given the files present.

This means that when the new/updated module files are merged to production and the necessary conditions are met (e.g. the config cache is invalid, etc.), the database upgrade should take place. Your (proper) concern is that this process might break based on remote server-level differences, remote data differences, etc. Without a tightly-regulated integration testing process, there is some overhead.

Plan of Attack: Pick the Right Strategy

The essential activity in this area is assessing the areas of module's impact on the database. This should be straightforward with any module which is worthy of being installed; check for any of the following:

1. A system.xml file
2. Existence of install/upgrade scripts in sql or data folders
3. Existence of custom setup resource class (configured under global/resources xpath)
4. Appropriate configuration XML (version number in module config node & a setup resource under global/resources xpath)

For 1, simply review the structure and know that its effects on the database will be limited to the core_config_data table, and generally only once an admin has saved values via the GUI (noting that 1. below applies as well).

For 2 & 3, review the scripts which are set to be run. These can be divided into three general areas: 1. Configuration settings - look for setConfigData() and deleteConfigData() calls 2. Table additions and edits (new tables, adding columns, etc.) 3. EAV-related changes and additions; look for EAV setup resources 4. Non-EAV data changes: installation of new data or modification of existing data

It's a matter of feel & intuition, but gauging the level of impact on the db will allow you to determine if you should clone production data down to local dev and test the setup workflow locally, verifying it works okay, then pushing to production and re-checking (backing up always!). If the changes are wide-ranging, it may be best to take the site offline so that you can ensure that you won't lose order or customer data if you need to revert after a botched upgrade.

You generally don't ever want to push data contained in a db from dev > prod. Your schema defs should be contained in Magento sql install scripts. If you do have actual new data you want to push up to prod, you'll have to do so on a case-by-case basis. You will most likely pull down from prod > dev to test out data and configuration before running the actual case on prod.

Case - 1:

If your production server has the same data (DB) which you have in the local, then just copy the database and files to the production server and do the the following:

1) Delete the content of the folder /var

2) Change the values of the file /app/etc/local.xmlThere you can find your connection string data (database user, host and name).

3) Once you got your database uploaded, you need to make some changes.

Run this query:

SELECT * FROM core_config_data WHERE path = 'web/unsecure/base_url' OR path = 'web/secure/base_url';

you will get 2 rows. update these rows by Run this query

UPDATE core_config_data SET value = 'YOUR_NEW_LIVE_URL' WHERE path LIKE 'web/%/base_url';

That’s all.

Case - 2:

If you don't want to change the DB data's in production, then you need to install the modules via megento connect directly to the production server. And you can update the files which you have changed in Local.