List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:January 22 2010 10:12pm
Subject:RE: Best way to synchronize two database schemas
View as plain text  
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



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