Best way to synchronize two database schemas

--_000_9269AB049ED62741A28FBF28706E31D3259594D73Cfangornccw2 kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have a two databases, one in a production environment (let's call it db_p=
rod) 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 generat=
e 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


--_000_9269AB049ED62741A28FBF28706E31D3259594D73Cfangornccw2 kv_--

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 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=gcdmg-mysql-2@m.gmane.org

RE: Best way to synchronize two database schemas

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@gmail.com [mailto:vegivamp@gmail.com] On
> Behalf Of Johan De Meersman
> Sent: Thursday, January 21, 2010 10:35 AM
> To: Price, Randall
> Cc: mysql@lists.mysql.com
> 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 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=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Best way to synchronize two database schemas

--001485e9a9e2f12c4b047dbd21c2
Content-Type: text/plain; charset=ISO-8859-1

Simple: prefix the change files with yyyymmddhhmm formatted timestamps, so
they sort correctly :-)

On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent 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@gmail.com [mailto:vegivamp@gmail.com] On
> > Behalf Of Johan De Meersman
> > Sent: Thursday, January 21, 2010 10:35 AM
> > To: Price, Randall
> > Cc: mysql@lists.mysql.com
> > 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 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=daevid@daevid.com
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001485e9a9e2f12c4b047dbd21c2--

RE: Best way to synchronize two database schemas

------=_NextPart_000_017E_01CA9B6C.F2C43A80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Huh? This makes no sense.
=20
There is one single file "UPDATES.sql" that has ALL the changes in it.
=20
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.
=20
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.
=20
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.
=20
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.
=20
ÐÆ5ÏÐ=20
Light travels faster than sound. This is why some people appear bright
until you hear them speak.

_____ =20

From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan =
De
Meersman
Sent: Friday, January 22, 2010 1:06 AM
To: Daevid Vincent
Cc: mysql@lists.mysql.com; 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 =
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@gmail.com [mailto:vegivamp@gmail.com] On
> Behalf Of Johan De Meersman
> Sent: Thursday, January 21, 2010 10:35 AM
> To: Price, Randall
> Cc: mysql@lists.mysql.com
> 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 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=3Ddaevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.be






--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel



------=_NextPart_000_017E_01CA9B6C.F2C43A80--

Re: Best way to synchronize two database schemas

--0016e6db2d81130c14047dc85c3b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 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 t=
o
> 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 th=
e
> script could check file dates, but you still have to save off the last ti=
me
> 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@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan D=
e
> Meersman
> Sent: Friday, January 22, 2010 1:06 AM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com; Price, Randall
> Subject: Re: Best way to synchronize two database schemas
>
>
> Simple: prefix the change files with yyyymmddhhmm formatted timestamps, s=
o
> they sort correctly :-)
>
>
> On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent
> 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, the=
y
> 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 GU=
I
> 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@gmail.com [mailto:vegivamp@gmail.com] On
> > Behalf Of Johan De Meersman
> > Sent: Thursday, January 21, 2010 10:35 AM
> > To: Price, Randall
> > Cc: mysql@lists.mysql.com
> > 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 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=3Ddaevid@daevid.com
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>
>


--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6db2d81130c14047dc85c3b--

Re: Best way to synchronize two database schemas

--0050450142cac92b30047dd61d97
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

As Mr. Withers also indicated, I meant frefixing the *filename* of each
change script with the date, and of course also keeping all changes in a
single (or limited set of) folder(s), so you can easily collect and
sequentially apply all of them when release time comes.

Also, it is preferable that database structure changes are handled by one
person or team, usually the database administrator(s) if you have them.

On Fri, Jan 22, 2010 at 11:12 PM, Daevid Vincent 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 t=
o
> 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 th=
e
> script could check file dates, but you still have to save off the last ti=
me
> 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@gmail.com [mailto:vegivamp@gmail.com] *On Behalf Of *Joh=
an
> De Meersman
> *Sent:* Friday, January 22, 2010 1:06 AM
> *To:* Daevid Vincent
> *Cc:* mysql@lists.mysql.com; Price, Randall
>
> *Subject:* Re: Best way to synchronize two database schemas
>
> Simple: prefix the change files with yyyymmddhhmm formatted timestamps, s=
o
> they sort correctly :-)
>
> On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent 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.ph=
p
>> -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. Thi=
s
>> file/dir is part of your repository, so as each developer checks out, th=
ey
>> 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 G=
UI
>> I've ever used, it has a feature to create the schema differences betwee=
n
>> 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@gmail.com [mailto:vegivamp@gmail.com] On
>> > Behalf Of Johan De Meersman
>> > Sent: Thursday, January 21, 2010 10:35 AM
>> > To: Price, Randall
>> > Cc: mysql@lists.mysql.com
>> > 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 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=3Ddaevid@daevid.com
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.=
be
>>
>>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0050450142cac92b30047dd61d97--

Re: Best way to synchronize two database schemas

One of the ways is to keep db scema under revision control system. And
update it every N minutes.

% crontab -l
0 * * * * mysqldump testdb --no-data > testdb_schema.sql && svn ci -m
"db schema: `date`" > /dev/null

> 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? =A0db_test has =
had a few indexes and constraints added to several tables and I need to gen=
erate a MySQL script to apply these changes to db_prod. =A0So basically I w=
ant to dump the schemas of the two database, compare, and generate the nece=
ssary script to apply to db_prod.
>
> Thanks,
> Randall Price
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Best way to synchronize two database schemas

On 1/21/10 12:03 PM, "Price, Randall" 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.

i'd use "export structure" in phpmyadmin on both dbs, then run diff on the
two export files, and write a script by hand based on the diff.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Best way to synchronize two database schemas

>> What is the best way to synchronize the database schemas? =A0db_test has=
had a
>> few indexes and constraints added to several tables and I need to genera=
te a
>> MySQL script to apply these changes to db_prod.

Ruby on Rails comes with tools to dump and load db schemas;
it's trivial to create a skeleton app and point it at your DB if you
want to try it out.

Of course, Rails also uses what are called "migrations" to make
changes to existing DBs, which keeps you out of this problem in
the first place :-)

I haven't looked, but I wouldn't be surprised if other comparable
(i.e., Rails clone) platforms have similar features.

FWIW,
--=20
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
twitter: @hassan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Best way to synchronize two database schemas

Hi,

Have you checked out our tool "Database Workbench" yet? It
includes a Schema Compare tool that generates a script.

See www.upscene.com

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com



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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org