From: Daevid Vincent Date: January 22 2010 10:12pm Subject: RE: Best way to synchronize two database schemas List-Archive: http://lists.mysql.com/mysql/220367 Message-Id: <7105A00C99A948FAA86343FFF5E5FC5D@mascorp.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_017E_01CA9B6C.F2C43A80" ------=_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 =D0=C65=CF=D0=20 Light travels faster than sound. This is why some people appear bright until you hear them speak. _____ =20 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 = 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 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@stripped > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: = http://lists.mysql.com/mysql?unsub=3Dvegivamp@stripped --=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--