Synchronizing database tables and structures automatically

dougmcc1

I have multiple mySQL databases. One database is the main database and the others just mimic the same one but have different data. In other words, all the databases have the same tables and table structure but different data in the tables.

The problem is that whenever I make a change to the main database, I have to change the others too because all the databases use the same PHP scripts.

Is there a way to have all the databases update automatically whenever I change the main database? Just like how changing PHP include files affect all scripts using them, I'm wondering if there's a way to automatically update all databases at the same time after making changes to the main one.

greywire

dougmcc1 wrote:
I have multiple mySQL databases. One database is the main database and the others just mimic the same one but have different data. In other words, all the databases have the same tables and table structure but different data in the tables.

The problem is that whenever I make a change to the main database, I have to change the others too because all the databases use the same PHP scripts.

Is there a way to have all the databases update automatically whenever I change the main database? Just like how changing PHP include files affect all scripts using them, I'm wondering if there's a way to automatically update all databases at the same time after making changes to the main one.

The same problem occurs when you've made a change to the database, and then anybody using your software also needs to have those changes to the database..

I have not found a good solution to this.

What I currently do is just put all the changes I make, as I make them (IE, ALTER TABLE ... etc) into a file, and then when I am done I put them into a PHP upgrade script and run them one at a time. Sometimes there are things that need some php code other wise you could just keep them in an SQL file and run them through the mysql command.

This seems a bit clumsy but I dont know how else you can do it.

There is also the Toad for Mysql program (free) that will show you all the differences between two databases, and it will create all the SQL statements to convert one to the other.

dougmcc1

Good suggestions. I'll look into Toad as, like you said, tracking the mysql changes in notepad is a bit clumsy.

Any other suggestions are welcome.

bastien

sqlyog is another excellent tool that i use frequently

greywire

bastien wrote:
sqlyog is another excellent tool that i use frequently

Looks pretty nice. The structure sync seems a little simplistic, I wish it gave some options and for some reason the color coded diffs are unviewable. But I like that it seems much faster and lets you remote admin databases through ssh or http proxy.

Its going to be a close race between this and Toad (which is now completely free).

Shrike

http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html might be what you are looking for.

greywire

Shrike wrote:
http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html might be what you are looking for.

We need to sync the structure, or schema if you will, of the database. Not the data.

The replication you refer to here is for copying data from master to slave databases. This would be used for increasing performance, and/or for having backup servers.

A great solution to a completely different problem.. 🙂

Shrike

I appreciate that. We use Sql Server's replication services at work between primary and backup databases. However with Sql Server you can define what should be replicated. I assumed the same would go for MySQL but looking at the manual maybe not 🙂

MarkR

I recommend that you manually script each change. Not all database schema changes can be done "trivially", i.e. just with an ALTER TABLE. Some of them require a more elaborate process.

If you want any reproducability between your dev environments and your staging / production environments, changes MUST be scripted.

Some tools do exist to automatically write these scripts, but they aren't that great. Perhaps they can save a little legwork though.

Traditionally, I've written each change in its own script and date-stamped it, so that when I need to run several of these scripts they can be run in the appropriate (date) order so that things don't get done out of sequence. This can be easily automated using a shell script.

The only trick is, I made a rule that a database upgrade script must never do anything "bad" if the thing that it's doing is already done - only fail. This sometimes has made these scripts nontrivial.

Mark