List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:January 22 2010 10:29pm
Subject:Re: Best way to synchronize two database schemas
View as plain text  
I handle this by having each developer put the scripts into a folder named:

YYYYMMDD

Where the folder name is the date of the next scheduled release.

Under that folder each script is named YYYYMMDD_NNN_DESCRIPTION.sql

Where YYYYMMDD in this case is the date the script was created and NNN is
the sequence # of the script if there is more than one script created on a
single day.

When release time comes, we simply combine all scripts in that release
folder.

If, in the event, we have to bring a really old database up to date, we
simply combine all scripts in all folders since the last release date on
that database up to now.

-JW


On Fri, Jan 22, 2010 at 4:12 PM, Daevid Vincent <daevid@stripped> wrote:

> Huh? This makes no sense.
>
> There is one single file "UPDATES.sql" that has ALL the changes in it.
>
> In order to prefix a line, it would need to be commented. It would also
> need to be one single change per line. Highly inefficient.
>
> This still doesn't solve the fact that different developers checkout or
> update the code on different days/times. How would you automate the fact
> that b/c I did an update today, which specific UPDATE.sql commands need to
> be run since some have already been after the last update. You'd need to
> store a file somewhere with a date stamp.
>
> Now, we could split each block of SQL commands into separate files and the
> script could check file dates, but you still have to save off the last time
> you updated somewhere.
>
> Anyways, it's just easier for a developer to mentally keep track of what
> the last SQL they remember running was. And if they forgot, all they have
> to do is check their version of the database schema against what the SQL
> command wants to do. A "diff" if you will.
>
> ÐÆ5ÏÐ
> Light travels faster than sound. This is why some people appear bright
> until you hear them speak.
>
>  _____
>
> From: vegivamp@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De
> Meersman
> Sent: Friday, January 22, 2010 1:06 AM
> To: Daevid Vincent
> Cc: mysql@stripped; Price, Randall
> Subject: Re: Best way to synchronize two database schemas
>
>
> Simple: prefix the change files with yyyymmddhhmm formatted timestamps, so
> they sort correctly :-)
>
>
> On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent <daevid@stripped>
> wrote:
>
>
> 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
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

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