Data Syncronisation For Web Developers

[anti]

Before I start, I should say that I know virtually nothing about databases….

Our website is developed locally (ie. php files edited, graphics changed, plugins installed, etc), whilst the 'live site' is still active (Eg. users are posting forum threads, comments, news, etc, uploading/attaching files, and updating their profiles. My ftp client has a compare/syncronisation tool. It displays two windows (local and remote) and puts an icon next to each changed file, new files and deleted files. I scroll through the list checking it and amending where necessary. Once satisfied, I do a two-way sync so that both the remote and local sites are the same – the files that the users have attached get downloaded, and the files which I added or edited in the local installation get uploaded. There is a timestamp/filesize conflict-rules feature, but usually conflicts are avoided when I go through the list of changes manually.

I was hoping to also do this for the database, but cannot understand why an equivelent 'compare' tool doesn't seem to exist? I tried Navicat first (because their self-promotion says it is the best). It is not good – I cannot trust it at all; everything I did corrupted my database. As far as I'm concerned it is an awful program.

So now I'm trying MySQLYog and in general it seems much better than Navicat, especially for non-experts. However, the sync tool does not work how I expected it to. What I need is to see a split window with the structure of my local and remote databases in. I need to see which tables and rows are different from each other and have the opportunity to decide which one to keep (ie. has a table been written to by users, or is the difference due to a plugin or some content I added locally?).

However, there is no such 'compare' view or 'preview'. I can't see which tables have become different, and I can't see why they are different. So when setting up the syncronisation all I can do is select the tables by 'blind' guesswork – which ones I expect I've changed locally, and which ones I think the users may have changed by using the website. Without the computer doing a proper, accurate comparison of table differences, this method is quickly going to end in tears.

Is there a reason why SQL tools can't work in the same way that the ftp client handles the file syncs? Is there any advice regarding my workflow when developing a website locally, but also trying to keep the live-site active as much as possible? I'm sure this must be a global problem for most website developers?

Thanks.

[anti]

Ah… I just found the 'structure comparison' tool. This is sooo close to what I need!

If only it could compare the table data as well as the structure, and display which tables were different

(even perhaps letting me see the contents of the tables??!), and then let me choose which version

(remote or local) to keep and which to overwrite during two-way sync.

As it is, the structure comparison tool does not flag tables which have different data.

But this is what thousands of web developers need! If mySQLYog could do this for 50$, it would probably

make you a millionaire within a year because every single web developer on the planet would want this tool.

Please, does anyone have advice about what I am trying to achieve?

BTW, the ftp client I use which does remote/local file comparisons is called TurboFTP.

As far as I know, it is the only one which has such a comparison tool.

Lots compare single directories, but this can compare and sync an entire website structure.

[peterlaursen]

You should use the DATA synchronisaton tool .. not the STRUCTURE synchronisation tool .. to synchronize data for the databases on the remote and local server.

Additionally you should use a FTP synchronise tool to synchronize file attachments.

You may study this article on SQLyog data sync:

http://webyog.com/en/sqlyog_data_sync.php

and here you will find FAQs about data sync and the SJA in general:

http://webyog.com/faq/category/11/mysql-da…ronization.html

http://webyog.com/faq/category/25/sqlyog-job-agent-sja.html

Please study a little .. and feel free to ask specific questions!

[anti]

Thanks for your reply, but unfortunately there is no info there that I didn't already know.

However, I apologise because my second post seems to have been confusing.

Here's a summary of the above posts:

I have a good ftp client which can compare and sync my local and remote files.

I am now looking for a tool with similar functionality to syncronise my databases.

Although I've only tried it for a few hours, I think SQLYog cannot do what I need.

The data syncronisation works, but it lacks a comparison feature. Website developers

need to know which local and remote tables have been changed and what those changes

are BEFORE choosing the tables for sync and in which direction they should syncronised.

It would be great if the SQLYog data sync tool looked like the structure comparison tool.

And allowed the user to see the table differences and choose which ones to sync and which direction.

There are not many programs which can do this comparison/syncronisation job. If you extended the

capability of the SQLYog data sync tool, there are only about another three programs which can do it.

[peterlaursen]

we will of course take your comments 'ad notam'. We also will add features to data sync in the future. Requests and comments are always welcome.

However comparing data may be useful for a few changed rows – but what would you do if x*1000 or x*10.000 rows (or even x*100.000 rows) had changed/had been inserted/deleted since last sync? Would you be able to compare everything before allowing the sync to proceed?

I think rather the solution is to add more 'conflict resolving rules' (our task) and to structure how the databases are used (users'/DBadmin's/application programmer's task).

[peterlaursen]

but I understand that you want to be able to 'preview changes'?

I can add such 'feature request' to our issue tracker, but it will not be priority for some time to come!

[anti]

Have you ever worked with a Content Management System like Joomla, e107, drupal, etc?

We are only talking about between 60 tables (e107) and 200 tables (Joomla).

Most website developers have a local version (using WAMP) for development.

And they have their live site which is being used all the time.

Suppose our web developer finds a new gallery plugin. He wouldn't install it on the live site in case it broke the site. Instead he'd install it on the local site, and test it thoroughly. Installing it means there are new files and new database tables created in the local installation. After testing for a few days the developer is happy that it works. He now has these options:

1/

He has not kept a track of which files and database changes the plugin made (and his hacks, and any other changes he made to settings, including uninstalling his old gallery), so he has to upload his entire local site (files and database) to the live site. However, this will overwrite all the new files and database content that his users have published whilst he was testing the plugin. So it is not possible to do this.

2/

He has to manually monitor all changes (files and database) that the plugin makes whilst he is testing it. Once testing is over, he has to manually, selectively upload ONLY the changes to his live site so he does not overwrite the files and database content that users have published whilst he was testing the plugin. Doing this manually without a comparison tool is extremely difficult and is error-prone.

3/

He closes the live site for the weekend, replacing his local site with the entire live site before he installs and tests the plugin. He can then upload the entire local site (files and database) and over-write the live site after testing because no changes have been made to the live site. However, he does a lot of development on the site and it is unfeasible to keep closing the live site every night/weekend – the users need access to it.

4/

He keeps a track of everything he does whilst testing, and repeats the installation, set-up and hacks exactly on the live site, effectively doubling the amount of work – everything he does to his local site, he then has to manually duplicate on the remote site.

All four of these options are a major pain, but this is actually how all these web developers are currently having to manage their CMS sites.

However, now that the web developer has found an ftp client which can compare the local and remote files, he can easily and automatically upload the new plugin files to the live site and be certain that he is not overwriting any of the files published by the users. At the same time he can automatically download all the files that the users published during that time, so that both local and remote sites now have exactly the same file system. Fantastic!

BUT can he find a tool to do the same with his databases? Is there a tool that will allow him to see the tables side by side and automatically highlight the differences between them? Can he simply mark the new 'plugin' tables for upload, and mark all the remote tables which have changed (forums, comments, articles, shoutbox, userblogs, user galleries, etc.) for download?

Apparently not!

These two seem close:

http://www.sqlmanager.net/products/mysql/d…creenshots/1893

http://www.dswsoft.com/sqlbalance.php

This seemed close, until I tried it: http://www.dbconvert.com/convert-foxpro-to-mysql-sync.php

And my suggestion is that if mySQLYog could help solve this comparison/syncronisation problem for all the thousands of people using CMS website packages, in the absence of any decent competition, you'd vastly increase your userbase, fame and income.

[peterlaursen]

Could you explain what prevents this developer to

1) first run a structure sync from test machine to production machine

2) and next run a datasync

(and possible also a FTP sync to sync files if required)

.. that also is what a lot of web developers are doing!

The point I see is that also for data sync you want to preview and accept every change?

[anti]

> 1) first run a structure sync from test machine to production machine

> 2) and next run a datasync

> .. that also is what a lot of web developers are doing!

But how are they doing it? Surely they can't possibly just be choosing the tables they think may have changed, and blindly clicking 'execute'? Certainly for myself, I'd need to see which tables differ so I can select the correct ones to begin with, and then in the case of conflicts I'd need to be able to see why they were different (ie. compare the row data) and choose which table should overwrite the other (would it be possible to even decide which row(s) should be overwritten???).

> The point I see is that also for data sync you want to preview and accept every change?

Yes. To be of any real use to non-database experts, the sync tool would need to build a visual tree of the compared database objects, marking differences by using different colours or icons. It would also need to allow viewing of the data in the rows of changed tables, and provide options to either copy or not, and to change the direction of sync (ie. in the case of a conflict, to be able to choose which table gets overwritten).

Currently the sync tool does not work the way I expected it to, but as I said I know nothing about databases (neither do 80% of the people building sites with CMS packages). Maybe what I'm requesting is not possible due to the limitations of mySQL, but most people would not feel safe blindly choosing tables which they think needed syncing, and then just executing the command without a visual review of what was going to be done or any control over the process.

I hope you don't think I am criticising SQLYog too much. It is by far the best database software I have seen for mySQL, and I will be buying it anyway, even without the features I require in the datasync tool – it is faster, more convenient and safer than importing/exporting from two instances of phpmyadmin. And lets not even mention Navicat, the database destoyer.

Thanks for your input so far into this subject.

[Author]

Posts