Re: Synchronize two mysql servers (single user, only one server (but not both) can be changed at any given time)
- Original Message - From: Peng Yu pengyu...@gmail.com Suppose I have two servers (one on mac and one on ubuntu), at any time I only change one server but not both servers. After I have done I think that probably it is OK to synchronize these two servers at the database file level using something like unison. However, this will It's not generally a good idea to copy database files across platforms, let alone possibly different versions of the mysql server. You probably want to set up master-master replication, which should work fine across platforms. If you are sure that you'll never change the (same) data on both sides concurrently, there is pretty much nothing that can go wrong. Have a look at the online documentation at mysql.com, it should be your first stop for everything :-) -- 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=arch...@jab.org
Re: Synchronize two mysql servers (single user, only one server (but not both) can be changed at any given time)
As Johan describes, replication is the way. If that's not an option due to connectivity between the hosts you could arrange for a logical dump to be copied and restored. I would certainly opt for replication in your situation. A On Tue, Oct 18, 2011 at 7:02 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Peng Yu pengyu...@gmail.com Suppose I have two servers (one on mac and one on ubuntu), at any time I only change one server but not both servers. After I have done I think that probably it is OK to synchronize these two servers at the database file level using something like unison. However, this will It's not generally a good idea to copy database files across platforms, let alone possibly different versions of the mysql server. You probably want to set up master-master replication, which should work fine across platforms. If you are sure that you'll never change the (same) data on both sides concurrently, there is pretty much nothing that can go wrong. Have a look at the online documentation at mysql.com, it should be your first stop for everything :-) -- 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=eroomy...@gmail.com
Synchronize two mysql servers (single user, only one server (but not both) can be changed at any given time)
Hi, There are some webpages on synchronizing two mysql server, some of them are old and some of them are for situations that are not exactly same as mine. So I ask how to syncrhonize two mysql servers again and hope to get the best solution to my particular requirement. Suppose I have two servers (one on mac and one on ubuntu), at any time I only change one server but not both servers. After I have done changes on A, I want to propagate the changes to B. After I have done changes on B, I want to propagate the changes to A. I think that probably it is OK to synchronize these two servers at the database file level using something like unison. However, this will be slow when the database file gets bigger. But I'm wondering what is the best way to synchronize mysql databases in the scenario that I just mentioned. -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to synchronize two database schemas
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 dae...@daevid.com 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:* vegiv...@gmail.com [mailto:vegiv...@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 mmddhhmm formatted timestamps, so they sort correctly :-) On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent dae...@daevid.comwrote: Exactly what Johan said. I keep structure like so: develo...@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: vegiv...@gmail.com [mailto:vegiv...@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 randall.pr...@vt.edu 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
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? 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=arch...@jab.org
Re: Best way to synchronize two database schemas
On 1/21/10 12:03 PM, Price, Randall randall.pr...@vt.edu 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=arch...@jab.org
Re: Best way to synchronize two database schemas
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. 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, -- Hassan Schroeder hassan.schroe...@gmail.com twitter: @hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=arch...@jab.org
Re: Best way to synchronize two database schemas
Simple: prefix the change files with mmddhhmm formatted timestamps, so they sort correctly :-) On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent dae...@daevid.com wrote: Exactly what Johan said. I keep structure like so: develo...@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: vegiv...@gmail.com [mailto:vegiv...@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 randall.pr...@vt.edu 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=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@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
RE: Best way to synchronize two database schemas
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: vegiv...@gmail.com [mailto:vegiv...@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 mmddhhmm formatted timestamps, so they sort correctly :-) On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent dae...@daevid.com wrote: Exactly what Johan said. I keep structure like so: develo...@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: vegiv...@gmail.com [mailto:vegiv...@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 randall.pr...@vt.edu 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=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@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
Re: Best way to synchronize two database schemas
I handle this by having each developer put the scripts into a folder named: MMDD Where the folder name is the date of the next scheduled release. Under that folder each script is named MMDD_NNN_DESCRIPTION.sql Where MMDD 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 dae...@daevid.com 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: vegiv...@gmail.com [mailto:vegiv...@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 mmddhhmm formatted timestamps, so they sort correctly :-) On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent dae...@daevid.com wrote: Exactly what Johan said. I keep structure like so: develo...@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: vegiv...@gmail.com [mailto:vegiv...@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 randall.pr...@vt.edu 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
Best way to synchronize two database schemas
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
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.pr...@vt.edu 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=arch...@jab.org
RE: Best way to synchronize two database schemas
Exactly what Johan said. I keep structure like so: develo...@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: vegiv...@gmail.com [mailto:vegiv...@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 randall.pr...@vt.edu 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=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to synchronize two databases
Dear Friends, I have two databases with the same name and table structure but the content is different, I need to synchronize them i mean the data inside one database need to be updated with the other one leaving the etries which are similar. Let me give a single example: DB1:table1 Rows like : userid name Ist row 1 abhishek IInd row 2 jain III row 3 rahul DB2:table1 Rows like : userid name Ist row 1 abhishek IInd row 2 amitabh III row 3 vijay Now i want like : Rows like : userid name Ist row 1 abhishek IInd row 2 jain III row 3 rahul IV row 4 amitabh V row 5 vijay NOTE: Here DB1 = database names , table1 = table name . How can i do that via mysql or that i need to prepare a script for this can anyone help. Regards, Abhishek Jain
Re: How to synchronize two databases
You may want to consider automating the synchronization of your databases via replication. Check out the Replication chapter in the MySQL manual (chapter 6 in the MySQL 5.0 manual). I don't know if Replication can cope with changes to the table structure - I've never played with Replication - but the manual should tell you whether this is a problem or is handled well by MySQL. -- Rhino - Original Message - From: abhishek jain [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, May 07, 2006 11:41 AM Subject: How to synchronize two databases Dear Friends, I have two databases with the same name and table structure but the content is different, I need to synchronize them i mean the data inside one database need to be updated with the other one leaving the etries which are similar. Let me give a single example: DB1:table1 Rows like : userid name Ist row 1 abhishek IInd row 2 jain III row 3 rahul DB2:table1 Rows like : userid name Ist row 1 abhishek IInd row 2 amitabh III row 3 vijay Now i want like : Rows like : userid name Ist row 1 abhishek IInd row 2 jain III row 3 rahul IV row 4 amitabh V row 5 vijay NOTE: Here DB1 = database names , table1 = table name . How can i do that via mysql or that i need to prepare a script for this can anyone help. Regards, Abhishek Jain No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to synchronize two databases
Check this out http://dev.mysql.com/tech-resources/articles/syncman/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup/synchronize tables/db web-local server
Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
On Wed, 9 Feb 2005 13:10:16 +0200, Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Have you thought about using mysqldump piped to the local DB? For example: mysqldump -h server_name_or_ip -u username -ppassword db_name table1 [table2 ...] mysql -u localuser -p db_name This is (I believe) shown in the on-line manual under mysqldump. It fulfills my needs. You could even set this up in cron (scheduled tasks). HTH Coz -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
On Wed, 9 Feb 2005 15:48:12 +0200, Paul Godard [EMAIL PROTECTED] wrote: On Wed, 9 Feb 2005 13:10:16 +0200, Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Have you thought about using mysqldump piped to the local DB? For example: mysqldump -h server_name_or_ip -u username -ppassword db_name table1 [table2 ...] mysql -u localuser -p db_name This is (I believe) shown in the on-line manual under mysqldump. It fulfills my needs. You could even set this up in cron (scheduled tasks). Hi Thanks for the suggestion. Do I have to execute that command shell on my local server (because of firewall my local server is not on line)? Is it possible to trigger mysqldump from a php script? The idea is to have a button on the local site to sync the local db to the web db. -- The piped mysqldump can work in either direction as mysql will also accept a host parameter and php can run shell commands. However, I guess your firewall could be an issue if it prevents connections to the mysql port. There was a thread I believe on the PHP list about doing a mysqldump and emailing the dump file and then processing that dump file on the recipients box. Coz -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
The mySQL command should read : mysql -u username -p[password] db_name dumpfile Note that you can give the password on the command line but it is best to get into the habit of just providing -p and typing in the password when prompted. BTW, when doing the mysqldump for tables that already exist in the destination database use --add-drop-table this will effectively drop the existing table before importing the new data. Coz On Wed, 9 Feb 2005 17:52:52 +0200, Paul Godard [EMAIL PROTECTED] wrote: The piped mysqldump can work in either direction as mysql will also accept a host parameter and php can run shell commands. However, I guess your firewall could be an issue if it prevents connections to the mysql port. There was a thread I believe on the PHP list about doing a mysqldump and emailing the dump file and then processing that dump file on the recipients box. Thanks again I couldn't find the thread but I manage to save the dump file on my local server (at the same location as the php script) by running a mysqldump on my local server to fetch a table on the web server. mysqldump -h server_ip -u username -p password db_name table1 filename.sql However I couldn't manage to create and insert data on the local server (the table1 being not there of course). I mean the part below didn't work. mysql -u localuser -p local password db_name I also tried to run mysql directly but with no success. mysql -e source filename.sql db_name or mysql db_name filename.sql However the operation worked via phpMyAdmin (read text file) directly. My conclusion is the firewall is not a problem but for some reason the syntax of the read dump part is not correct... Any idea why? -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
SQLyog Enterprise's Database Sync Tool is the answer for your queries. www.webyog.com Rgds, Karam --- Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
It is very easier with mysql replication concipt, this automatic replication from Master server to Slave server and real time. I wonder how you do not know about this. http://dev.mysql.com/doc/mysql/en/replication.html Love kumar .. [EMAIL PROTECTED] wrote: SQLyog Enterprise's Database Sync Tool is the answer for your queries. www.webyog.com Rgds, Karam --- Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation.
Re: backup/synchronize tables/db web-local server
Well I know about it and I am working on MySQL Replication, the point is that you dont have control over replicatation when your MySQL server is installed by your ISP on shared server, In that case it is simply not possible :) Moreover, the simple setup I have, I prefer this then the nittygritties of replication. Agreed, if you have a heavy duty setup then the only answer is MySQL replication. Karam --- love [EMAIL PROTECTED] wrote: It is very easier with mysql replication concipt, this automatic replication from Master server to Slave server and real time. I wonder how you do not know about this. http://dev.mysql.com/doc/mysql/en/replication.html Love kumar .. [EMAIL PROTECTED] wrote: SQLyog Enterprise's Database Sync Tool is the answer for your queries. www.webyog.com Rgds, Karam --- Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation. __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Synchronize Databases: 2 Locations
This may not be entirely a database question, but it certainly involves MySQL databases. BACKGROUND: I'm working with a small business that hosts their own web site for order taking/managing orders via internet. It only consists of one DSL line, one web server (apache/bind/tomcat) and one database server (MySQL). There is no backup or redundancyso if DSL or power goes down, so does the site. This has been an accepted risk by the company. I would like to propose a backup system, but am hung up on one issue. If I were serving static pages, I would see no problem with putting a web server at another location (location_2) on a different DSL carrier. But with a database involved, that throws a twist in it. If I put at location_2 a web server and database server, I could code to keep the databases synched up while both DSL lines are up. And if one location goes down, certainly the other location could take over (I would accept losing open sessions on downed server). My question is though, what happens when the downed loaction comes back up? My databases will be out of synch. Even if I ran only one database which was reached by both locations, if the location with the database goes down, it all goes down. QUESTION: So my question is, how do other shops keep things synched up? I can understand in a big company, they likely have multiple servers/internet pipes, and when one line/server goes down, the other servers talk across the LAN. In my case though, location _2 could be a few miles, if not 100 miles awayto be potentially connected only by DSL. Is this more of a networking question? Or is this potentially solved by an app server synching things when the downed location is back up? I can't see multiple DSL lines into each location working, because a power outage (which has happened, by the way) would break the system. Does anyone want to offer up a scenario or two of what they have used/seen? Thanks for any advice. Rob Mazur - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Synchronize only database structures
On Thu, May 16, 2002 at 11:32:48AM -0400, Lance Lovette wrote: I have partitioned my data such that each client has its own database (clientdb1, clientdb2, ... clientdbN). All the databases have the same structure (same tables, columns, etc.). This design works great except for one caveat - when I make a change to the database structure I have to make the same change to all the client databases. Before I roll up my sleeves I thought I'd ask if there is an easy way to synchronize the structure of databases so I can make my changes to clientdb1 and then apply the changes to clientdb2...N. Not really. You'll need to write a Perl/Python/whatever script to automate the process. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Synchronize only database structures
I have partitioned my data such that each client has its own database (clientdb1, clientdb2, ... clientdbN). All the databases have the same structure (same tables, columns, etc.). This design works great except for one caveat - when I make a change to the database structure I have to make the same change to all the client databases. Before I roll up my sleeves I thought I'd ask if there is an easy way to synchronize the structure of databases so I can make my changes to clientdb1 and then apply the changes to clientdb2...N. Thanks! Lance sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Distributed MySQL : How to synchronize ?
Hi
I have built and am testing a php module that is capable of this.
I have an ultra high availability project where it must survive a server or
network failure and switch seamlessly to the backup server.
All queries are executed through a db abstraction class. It basically does
the same as replication, but is live.
If the query changes the data (i.e. UPDATE, INSERT etc) then it attempts to
connect to the slave, if it fails to connect, it saves the query in a local
table and executes it when it can connect.
The normal flow is .
execute query on master
if (connect to slave)
{
execute query saved in slave db on master db (this will only happen if
there has been a master--slave connection failure or the master was
offline, and synchronises the master with the slave)
execute query saved in master db on slave db (this will only happen if
there has been a master--slave break in communication or the slave was
offline. This synchronises the slave.)
delete or mark as executed, all saved queries
execute current query on slave
}
else
{
//if it cannot connect
save query to local table (which will be executed when connection can be
established)
}
I am still testing to see if there are any odd effects, when there is an
intermittent fault so a user session is spread over both machines, for
example.
The same process could be adapted to cover multiple slaves, with the master
collecting all the saved queries from the slaves and then executing them.
Perhaps you could timestamp the queries saved during a master outage so that
they are execute in the correct order.
Food for thought, be interested to hear how you get on.
Let me know if I can help
Peter
---
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: 10 May 2002 23:07
To: Christophe Demange
Cc: [EMAIL PROTECTED]
Subject: Re: Distributed MySQL : How to synchronize ?
On Fri, May 10, 2002 at 04:51:15PM +0200, Christophe Demange wrote:
Hello,
I have a question:
We want to real-time synchronize a MySQL database part with a
distant database. We have a central database containing all the
data. This database must share several parts (records) with the
local databases. All transactions made in the central database must
be replicated to the corresponding local database. This will ensure
to have the data available while the network is down. In the same
time some transactions can be made in the local database, this
includes to have a dual-way replication mechanism.
Hmmm.
I have searched replication documentation and articles and I have
only found master-slave relationship. We think this master-slaves
relationship doesn't exactly fit our needs.
It probably doesn't, but...
Please could you explain us what is the best solution to use for
this architecture.
Since you asked, here's a thought that'll require a bit of research
and testing on your end. It might get you some of the way there.
Rather than write records to a single table on the master, break
things out into several identically structured tables. The create a
MERGE table out of them. Run your write queries against the
underlying table appropriate for the record.
That *may* allow the slaves to replicate only the data they need. I
say may because I don't know enough about your application to make
that judgement.
The only problem arises in the getting updates made on the slaves back
to the master. A slave in MySQL can only have one master, so that's a
more difficult problem.
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.47-max: up 92 days, processed 2,403,910,165 queries
(301/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Distributed MySQL : How to synchronize ?
Hi, I wonder (i don't tested) if is possible to start two session of mysqld on each machine which work on the same database : one for master and one for slave. MACHINE1MACHINE2 1.Master Session - 1.Slave Session 2.Slave Session - 2.Master Session | | DataBase DataBase ...else ,in my opinion, i consider is necessary to develop a server application to solve full duplex replication. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Christophe Demange [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, May 11, 2002 1:07 AM Subject: Re: Distributed MySQL : How to synchronize ? On Fri, May 10, 2002 at 04:51:15PM +0200, Christophe Demange wrote: Hello, I have a question: We want to real-time synchronize a MySQL database part with a distant database. We have a central database containing all the data. This database must share several parts (records) with the local databases. All transactions made in the central database must be replicated to the corresponding local database. This will ensure to have the data available while the network is down. In the same time some transactions can be made in the local database, this includes to have a dual-way replication mechanism. Hmmm. I have searched replication documentation and articles and I have only found master-slave relationship. We think this master-slaves relationship doesn't exactly fit our needs. It probably doesn't, but... Please could you explain us what is the best solution to use for this architecture. Since you asked, here's a thought that'll require a bit of research and testing on your end. It might get you some of the way there. Rather than write records to a single table on the master, break things out into several identically structured tables. The create a MERGE table out of them. Run your write queries against the underlying table appropriate for the record. That *may* allow the slaves to replicate only the data they need. I say may because I don't know enough about your application to make that judgement. The only problem arises in the getting updates made on the slaves back to the master. A slave in MySQL can only have one master, so that's a more difficult problem. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 92 days, processed 2,403,910,165 queries (301/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Distributed MySQL : How to synchronize ?
Hello, I have a question: We want to real-time synchronize a MySQL database part with a distant database. We have a central database containing all the data. This database must share several parts (records) with the local databases. All transactions made in the central database must be replicated to the corresponding local database. This will ensure to have the data available while the network is down. In the same time some transactions can be made in the local database, this includes to have a dual-way replication mechanism. I have searched replication documentation and articles and I have only found master-slave relationship. We think this master-slaves relationship doesn't exactly fit our needs. Please could you explain us what is the best solution to use for this architecture. Thank you for your support. _ Christophe DEMANGE Project Manager GAEL Consultant Cité Descartes 18, rue Albert Einstein 77420 Champs-sur-Marne France mailto:[EMAIL PROTECTED] http://www.gael.fr tel +33-(0)1 64 73 99 55 fax +33-(0)1 64 73 51 60 _ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Distributed MySQL : How to synchronize ?
On Fri, May 10, 2002 at 04:51:15PM +0200, Christophe Demange wrote: Hello, I have a question: We want to real-time synchronize a MySQL database part with a distant database. We have a central database containing all the data. This database must share several parts (records) with the local databases. All transactions made in the central database must be replicated to the corresponding local database. This will ensure to have the data available while the network is down. In the same time some transactions can be made in the local database, this includes to have a dual-way replication mechanism. Hmmm. I have searched replication documentation and articles and I have only found master-slave relationship. We think this master-slaves relationship doesn't exactly fit our needs. It probably doesn't, but... Please could you explain us what is the best solution to use for this architecture. Since you asked, here's a thought that'll require a bit of research and testing on your end. It might get you some of the way there. Rather than write records to a single table on the master, break things out into several identically structured tables. The create a MERGE table out of them. Run your write queries against the underlying table appropriate for the record. That *may* allow the slaves to replicate only the data they need. I say may because I don't know enough about your application to make that judgement. The only problem arises in the getting updates made on the slaves back to the master. A slave in MySQL can only have one master, so that's a more difficult problem. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 92 days, processed 2,403,910,165 queries (301/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
synchronize??
Hi, I have two servers (one remotely hosted, and one locally) that I would like to have the same database on for backup purposes. I would like the two databases to synchronize automatically and all the time. Is there currently any way to do this? Remote server: FreeBSD running MySQL 3.23.40 Local Server: WinXP with phpTriad and MySQL 3.23.32 (I am changing this server to foxserv with latest MySQL tomorrow) Databases have same name and user/password combos Thanks, Rich --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.314 / Virus Database: 175 - Release Date: 1/11/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: synchronize??
In the last episode (Jan 15), Rich Foreman said: I have two servers (one remotely hosted, and one locally) that I would like to have the same database on for backup purposes. I would like the two databases to synchronize automatically and all the time. Is there currently any way to do this? Remote server: FreeBSD running MySQL 3.23.40 Local Server: WinXP with phpTriad and MySQL 3.23.32 (I am changing this server to foxserv with latest MySQL tomorrow) Databases have same name and user/password combos Replication should do what you need. http://www.mysql.com/doc/R/e/Replication.html -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
synchronize??
Hi! Rich == Rich Foreman [EMAIL PROTECTED] writes: Rich Hi, Rich I have two servers (one remotely hosted, and one locally) that I would like Rich to have the same database on for backup purposes. Rich I would like the two databases to synchronize automatically and all the time. Rich Is there currently any way to do this? Yes, as long as the updates are not conflicting. In this case you 'just' need set up both servers as 'masters' and then set them to be a slave of the other. Please consult the MySQL replication chapter in the manual for how to do this. Rich Remote server: FreeBSD running MySQL 3.23.40 Rich Local Server: WinXP with phpTriad and MySQL 3.23.32 (I am changing this Rich server to foxserv with latest MySQL tomorrow) Rich Databases have same name and user/password combos Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to synchronize two database in two machine?
On Fri, Oct 26, 2001 at 05:52:58PM +0800, sj wrote: hi, I have two machine, install mysql, create same database. I want these two database can be the same at any time. in other word, if one table be inserted a record in one machine, then the same table in another machine can be inserted same record also. Read about REPLICATION in the on-line manual. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 52 days, processed 1,160,979,792 queries (258/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to synchronize two database in two machine?
hi, I have two machine, install mysql, create same database. I want these two database can be the same at any time. in other word, if one table be inserted a record in one machine, then the same table in another machine can be inserted same record also. Who can tell me how to realize this function? thanks advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
synchronize database structure
Hi, I am looking for an easy way to synchronize two mysql-databases (only the sql-structure). I want to compare two structure dumps, find out the differences and get alter table statements as result. CU Dorthe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
