Synchronizing views between MySQL databases

November 27th, 2008

How to compare and synchronize MySQL views? Recently we discovered some tricks in schema synchronization process of MySQL databases. It’s about how MySQL creates views and tables with foreign keys. I’m going to explain them and give workarounds for dbForge Studio for MySQL users.

Let’s take a small database named crm_demo for an example.

Suppose you deployed this database on two remote MySQL servers under different names: crm_demo and crm_demo_mirror.

First trick: creating view

After some time your decided to expand your crm_demo database by adding a view to it.

You create simple view on your local server by executing following statement:

MySQL expand your query and modifies it’s text to avoid ambiguous references (I guess). So here is the first trick: you need to ensure that crm_demo is the current database when creating view, otherwise MySQL will take current database and convert your view into something you don’t want. For example

But you don’t want test database you want crm_demo ! To avoid this you could have written view text as following

But if you have created database project in dbForge Studio you don’t need to worry about it because application puts USE statement at the beginning of the deployment script. Thus you can deploy project under crm_demo and crm_demo_mirror database names without having to modify source code.

Important note:
You should consider the same rule about current database when creating table with foreign key.

Second trick: synchronizing two live databases using dbForge Studio for MySQL

Now suppose you need to synchronize crm_demo with crm_demo_mirror . The fastest way to do this is to run schema comparison tool and examine database differences visually.

After doing this you see that crm_demo_mirror does not contain products_view. Oh… that’s easy! Wait a minute, here is the second trick: you press synchronize button and get … invalid view in crm_demo_mirror database. That’s because view body contains references to crm_demo database.

To synchronize databases correctly you need to do following:

  1. Export synchronization script to SQL editor
  2. Press Ctrl+H and replace all occurrences of crm_demo with crm_demo_mirror
  3. Execute script against crm_demo_mirror database

Comparison view and synchronizing it are available in dbForge Data Compare for MySQL or dbForge Studio for MySQL.

Leave a Comment