Magento: Database Synchronization between Production, Production, and Development

Question

Today I read about database synchronization in Magento.

One thing that I am facing right now is that you need to synchronize during development and during loading into production. Now, assuming that the change package will consist of changes to the database and code, below will be my understanding of the model workflow (I do not currently use the stage server), so this is excluded in this example):

• Synchronize dev database from production DB
• Make a working copy of the code on the dev machine
• Make changes and test them on the dev server.
• Accept the changes and submit them to the svn repository
• Tap Maintenance.flag on the production server and prepare for updates (this completely eliminates the problems of synchronization with users interacting with real-time data that will be changed soon).
• Trunking branches and deploying the repository to a production server
• Synchronize dev back to production database and test changes

So, points No. 1 and 7 I do not quite understand when I work with Magento:

• What needs to be synchronized and what is not?
  • It seems ridiculous to me to synchronize order and customer information so that I do not.
  • I would like the product schema and data to be synchronized, although, obviously, any administrator changes, module changes, etc. How to do it?
• How about the ability to sync? (MySql dumps, import / export, etc.)
  • I am currently using Navicat 10 Premium, which has the structure and functions of data synchronization (I have not experimented yet, but they look like a huge help).

Therefore, I do not need specific features here (but they will help). More or less, I want to know what works for you and how happy you are with this system.

Answer 1

I am using phpunit to create dev db. I wrote a short script that fetches XML data from a live database, and I used it on a table, dragging something sensitive and deleting. I don’t need it. The schema for my dev database never changes and never restores. Only data is deleted and recreated every phpunit run.

It may not be the right solution for everyone, because Dev will never be well synchronized to the stage / production, but I do not need to do this.

The main advantage is how little data I need for dev db. This is about 12,000 xml lines and processes 30 different tables. Some small main tables are preserved because I do not write them, and many tables are empty because I do not use them.

The database is a representative sample and is very small. Not enough to edit as a text file and only a few seconds to fill up every time I run the tests.

Here's what it looks like at the top of every PHPUnit test. There is good documentation for PHPUnit and DbUnit

<?php require_once dirname(dirname(__FILE__)) . DIRECTORY_SEPARATOR . 'top.php'; require_once "PHPUnit/Extensions/Database/TestCase.php"; class SomeTest extends PHPUnit_Extensions_Database_TestCase { /** * @return PHPUnit_Extensions_Database_DB_IDatabaseConnection */ public function getConnection() { $database = MY_DB $hostname = MY_HOST $user = MY_USER $password = MY_PASS $pdo = new PDO("mysql:host=$hostname;dbname=$database", $user, $password); return $this->createDefaultDBConnection($pdo, $database); } /** * @return PHPUnit_Extensions_Database_DataSet_IDataSet */ public function getDataSet() { return $this->createXMLDataSet(dirname(dirname(__FILE__)) . DIRECTORY_SEPARATOR . 'Tests/_files/seed.xml'); } } 

So, now you only need the initial file that DbUnit reads to re-populate your database every time unit tests are called.

Start by copying your complete database twice. One of them will be your dev database, and the second will be your "primordial" database, which you can use to upload XML data if you encounter key problems.

Then use something like my xml dumper again in the prisine database to get your xml dumps and start creating your semester file.

 generate_flat_xml.php -tcatalog_product_entity -centity_id,entity_type_id,attribute_set_id,type_id,sku,has_options,required_options -oentity_id >> my_seed_file.xml 

Edit the seed file to use only what you need. The small size of dev db means that you can analyze the differences just by looking at your database compared to text files. Not to mention that it is much faster than less data.

Answer 2

if you are using CE version:

• ditch svn and use git :)

• never synchronize the database; prepare database updates as file update files.

  • there are 3 sites dev, stage, live
  • the live database is copied to the scene and dev if necessary
  • make all your administrator changes in real time and just copy the entire database line by line

this way you never have to synchronize the database +, if you make all configuration changes using extension extension scripts, you can coldly load magento into the new database structure wherever you want, without losing the data structure.