List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:January 21 2010 10:31pm
Subject:RE: Best way to synchronize two database schemas
View as plain text  
Exactly what Johan said.

I keep structure like so:

developer@mypse /var/www/dart2/UPDATES $ ll
-rw-rw-rw- 1 developer developer 551097 2009-12-22 23:16
airports_city_country.sql
drwxrwxrwx 2 developer developer   4096 2010-01-21 04:51 CVS
-rw-rw-rw- 1 developer developer   3063 2009-07-15 01:40 fix_airports.php
-rw-r--r-- 1 developer developer  23414 2010-01-21 03:52
ps_access_to_mysql.sql
-rw-rw-rw- 1 developer developer  12259 2010-01-06 05:22 UPDATES.sql 

Any and all changes to DB schema are in the UPDATES/UPDATES.sql file and
each are commented with the date of the change, who did it, and why. This
file/dir is part of your repository, so as each developer checks out, they
would run the appropriate part of the script as well. I've not yet found a
good (and safe) way to automate this process.

/* 2009-06-01 [dv] fix the privileges for various users as they were all
whacked out
 * http://dev.mysql.com/doc/refman/5.0/en/grant.html
 */
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'foo'@'10.10.10.%';
...

If you're using Subversion, you may find my "Subversion Flagged Update"
script helpful...
http://daevid.com/content/examples/snippets.php

I also can vouche for SQLYog. Aside from being the absolute BEST mySQL GUI
I've ever used, it has a feature to create the schema differences between
two live databases. I've used it before to get a DEV and PROD server in
sync, so that I could then implement the above methodology.

> -----Original Message-----
> From: vegivamp@stripped [mailto:vegivamp@stripped] On 
> Behalf Of Johan De Meersman
> Sent: Thursday, January 21, 2010 10:35 AM
> To: Price, Randall
> Cc: mysql@stripped
> Subject: Re: Best way to synchronize two database schemas
> 
> The best way is to keep track of all individual changes to your
> staging environment, including fire-and-forget style scripts; and
> apply those to your production environment as needed. This is part of
> the process of change management, and generally a very good idea :-)
> 
> Lacking that, there are several tools that can generate a differential
> script to do exactly this. I don't really use them, but I seem to
> remember that SQLyog and some expensive but excellent Quest tool could
> do it.
> 
> On 1/21/10, Price, Randall <Randall.Price@stripped> wrote:
> > I have a two databases, one in a production environment 
> (let's call it
> > db_prod) and the other in a testing environments (Let's 
> call it db_test).
> >
> > What is the best way to synchronize the database schemas?  
> db_test has had a
> > few indexes and constraints added to several tables and I 
> need to generate a
> > MySQL script to apply these changes to db_prod.  So 
> basically I want to dump
> > the schemas of the two database, compare, and generate the 
> necessary script
> > to apply to db_prod.
> >
> > Thanks,
> > Randall Price
> >
> >
> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
Best way to synchronize two database schemasRandall Price21 Jan
  • Re: Best way to synchronize two database schemasJohan De Meersman21 Jan
    • RE: Best way to synchronize two database schemasDaevid Vincent21 Jan
      • Re: Best way to synchronize two database schemasJohan De Meersman22 Jan
        • RE: Best way to synchronize two database schemasDaevid Vincent22 Jan
          • Re: Best way to synchronize two database schemasJohnny Withers22 Jan
          • Re: Best way to synchronize two database schemasJohan De Meersman23 Jan
  • Re: Best way to synchronize two database schemasAlexander Kolesen23 Jan
  • Re: Best way to synchronize two database schemasfsb23 Jan
    • Re: Best way to synchronize two database schemasHassan Schroeder23 Jan
  • Re: Best way to synchronize two database schemasMartijn Tonies23 Jan