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
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 ?
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
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