|
Home > Archive > MySQL ODBC Connector > October 2005 > Circular Replication
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Circular Replication
|
|
|
| Does anyone use circular replication with MySQL 4.x? For instance:
A to B
B to A
I know it's possible as long as you're carefull with your client
applications and the way they write/update the db. Just wondering if
anyone has had success or problems with this type of situation.
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Dan Nelson 2005-09-16, 11:23 am |
| In the last episode (Sep 16), Jeff said:
> Does anyone use circular replication with MySQL 4.x? For instance:
>
> A to B
> B to A
>
> I know it's possible as long as you're carefull with your client
> applications and the way they write/update the db. Just wondering if
> anyone has had success or problems with this type of situation.
Works just fine here.
--
Dan Nelson
dnelson@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
> -----Original Message-----
> From: Dan Nelson & #91;mailto:dnelson@a
llantgroup.com]
> Sent: Friday, September 16, 2005 11:34
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
>
>
> In the last episode (Sep 16), Jeff said:
> wondering if
>
> Works just fine here.
>
> --
>
> Dan Nelson
> dnelson@allantgroup
.com
>
> --
>
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=jmckeon@telaur
us.com
>
>
Am I correct in this setup process:
Server A exists
Server B to be built
Stop Server A, take snapshot, record Master info.
Start Server A
Setup server B, Install snapshot from A
Set B up as a master
Set B up as a slave to A
Set A up as a slave to B, no need for binlog file or position info as it
will start at the default 001 and pos 4
Circular replication is now running...
Did I mis anything?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Stefan Kuhn 2005-09-16, 11:23 am |
| I'm using it with four machines (geographically separate) and it works fine.
Stefan
Am Friday 16 September 2005 17:31 schrieb Jeff:
> Does anyone use circular replication with MySQL 4.x? For instance:
>
> A to B
> B to A
>
> I know it's possible as long as you're carefull with your client
> applications and the way they write/update the db. Just wondering if
> anyone has had success or problems with this type of situation.
>
> Thanks,
>
> Jeff
--
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Devananda 2005-09-16, 11:23 am |
| Jeff wrote:
>
> Am I correct in this setup process:
>
> Server A exists
> Server B to be built
>
> Stop Server A, take snapshot, record Master info.
> Start Server A
>
> Setup server B, Install snapshot from A
> Set B up as a master
> Set B up as a slave to A
>
> Set A up as a slave to B, no need for binlog file or position info as it
> will start at the default 001 and pos 4
>
> Circular replication is now running...
>
> Did I mis anything?
>
> Thanks,
>
> Jeff
>
>
>
If you are using InnoDB, then you do not need to stop server A to take a
snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html)
"The simultaneous use of --master-data and --single-transaction works as
of MySQL 4.1.8. It provides a convenient way to make an online backup
suitable for point-in-time recovery if tables are stored in the InnoDB
storage engine."
As far as setting up the replication, that looks ok. Each server needs
to have binary logging enabled, and needs to have the other server as
it's master.
Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jeff McKeon 2005-09-16, 1:23 pm |
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]=20
> Sent: Friday, September 16, 2005 11:55
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
>=20
>=20
> Jeff wrote:
> position info as=20
> If you are using InnoDB, then you do not need to stop server=20
> A to take a=20
> snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html)
>=20
> "The simultaneous use of --master-data and=20
> --single-transaction works as=20
> of MySQL 4.1.8. It provides a convenient way to make an online backup=20
> suitable for point-in-time recovery if tables are stored in=20
> the InnoDB=20
> storage engine."
>=20
> As far as setting up the replication, that looks ok. Each=20
> server needs=20
> to have binary logging enabled, and needs to have the other server as=20
> it's master.
>=20
> Regards,
> Devananda vdv
Nope, all tables are MyISAM.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Jeff McKeon
> Sent: Friday, September 16, 2005 13:19
> To: Devananda
> Cc: mysql@lists.mysql.com
> Subject: RE: Circular Replication
>
>
> online backup
> server as
>
> Nope, all tables are MyISAM.
>
There shouldn't be a problem if:
server A is ver 4.0.x
server B is ver 4.1.x
should there?
Thanks,
jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
| Sid Lane 2005-09-19, 9:23 am |
| ------ =_Part_6159_28025184
.1127138578259
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
stupid ?:
what keeps them from getting caught in a write loop? turning off=20
log_slave_updates?
I had never thought of this but is has intriging possibilities...
------ =_Part_6159_28025184
.1127138578259--
| |
| Alec.Cawley@Quantel.Com 2005-09-19, 9:23 am |
| Sid Lane <jakes.dad@gmail.com> wrote on 19/09/2005 15:02:58:
> stupid ?:
>
> what keeps them from getting caught in a write loop? turning off
> log_slave_updates?
>
> I had never thought of this but is has intriging possibilities...
Each update is marked with the unique server id of the server which
originated it. When the update returns to its originating server, it is
dropped instead of being executed. That is why every server must have a
unique id.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Sid Lane 2005-09-19, 11:23 am |
| ------ =_Part_7162_27481548
.1127144849659
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
NOW I see the violence inhierent in the system...
this has some profoundly cool possibilities...
BWAH-HA-HA-HA!!!
muchos!
------ =_Part_7162_27481548
.1127144849659--
| |
|
| > -----Original Message-----
> From: Alec.Cawley@Quantel.Com [mailto:Alec.Cawley@Quantel.Com]
> Sent: Monday, September 19, 2005 10:10
> To: jakes.dad@gmail.com
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
>
>
> Sid Lane <jakes.dad@gmail.com> wrote on 19/09/2005 15:02:58:
>
>
> Each update is marked with the unique server id of the server which
> originated it. When the update returns to its originating
> server, it is
> dropped instead of being executed. That is why every server
> must have a
> unique id.
>
> Alec
>
Actually, isn't it required that you start each server with
--log_slave_updates?
Or is that only necessary in a replication situation like this with more
than 2 servers?
A -> B -> C -> A
If it's just:
A -> B -> A
Is it necessary to start the servers with --log_slave_updates?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Devananda 2005-09-20, 8:23 pm |
| Jeff wrote:
>
>
> Actually, isn't it required that you start each server with
> --log_slave_updates?
>
> Or is that only necessary in a replication situation like this with more
> than 2 servers?
>
> A -> B -> C -> A
>
> If it's just:
>
> A -> B -> A
>
> Is it necessary to start the servers with --log_slave_updates?
>
> Thanks,
>
> Jeff
>
>
>
log-slave-updates causes the server to write to it's own binlog any
statements it read from it's master's binlog. This is necessary in
chains of servers (ie, A->B->C->A); in such chains, it is necessary that
all servers have BOTH log-bin and log-slave-updates.
With just 2 servers (A->B->A), you need log-bin, but do not need
log-slave-updates.
Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]
> Sent: Tuesday, September 20, 2005 17:13
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
>
>
> Jeff wrote:
>
> log-slave-updates causes the server to write to it's own binlog any
> statements it read from it's master's binlog. This is necessary in
> chains of servers (ie, A->B->C->A); in such chains, it is
> necessary that
> all servers have BOTH log-bin and log-slave-updates.
>
> With just 2 servers (A->B->A), you need log-bin, but do not need
> log-slave-updates.
>
> Regards,
> Devananda vdv
>
Muchos Gracias!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Bruce Dembecki 2005-09-21, 3:23 am |
| On Sep 16, 2005, at 11:07 AM, Jeff wrote:
> There shouldn't be a problem if:
>
> server A is ver 4.0.x
> server B is ver 4.1.x
>
> should there?
>
There will totally by a problem here... The 4.1 server will take the
4.0 feed without issue. The 4.1 server however puts all sorts of
information into the binary log which isn't in the original query,
like what sort of collation to use, and which character set it uses
and so on... 4.0 doesn't understand such commands and lots of things
break in this situation.
As a side note we deploy servers in pairs, with circular replication.
We did three and four server circles, but it gets messy if
replication stops somewhere, the data becomes unpredictably
inconsistent (assuming all the servers in the circle are getting
production updates). Now we do simple two way replication between a
pair, and we hang a third server off the pair somewhere just
reading... the third server we use for backups, data dumps, reports
and other non production issues. Essentially it is something like A<-
>B->C, where A and B have two way replication and C is used for
backups/reports etc... anything that changes the data happens on A or B.
We do some other black magic to manage the replication on C so it's
perpetually behind the master servers by between 15 minutes and 2
hours... that way if we have a stupid operator error or some other
data corrupting event we can stop replication on the backup server
before it executes and start from there rather than having to go back
to yesterdays backup or something.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Bruce Dembecki & #91;mailto:bruce@liv
eworld.com]
> Sent: Tuesday, September 20, 2005 23:05
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
>
>
> On Sep 16, 2005, at 11:07 AM, Jeff wrote:
>
>
> There will totally by a problem here... The 4.1 server will take the
> 4.0 feed without issue. The 4.1 server however puts all sorts of
> information into the binary log which isn't in the original query,
> like what sort of collation to use, and which character set it uses
> and so on... 4.0 doesn't understand such commands and lots of things
> break in this situation.
>
> As a side note we deploy servers in pairs, with circular
> replication.
> We did three and four server circles, but it gets messy if
> replication stops somewhere, the data becomes unpredictably
> inconsistent (assuming all the servers in the circle are getting
> production updates). Now we do simple two way replication between a
> pair, and we hang a third server off the pair somewhere just
> reading... the third server we use for backups, data dumps, reports
> and other non production issues. Essentially it is something like A<-
> backups/reports etc... anything that changes the data happens
> on A or B.
>
I assume you then need to start server B with "--Log_slave_updates"?
> We do some other black magic to manage the replication on C so it's
> perpetually behind the master servers by between 15 minutes and 2
> hours... that way if we have a stupid operator error or some other
> data corrupting event we can stop replication on the backup server
> before it executes and start from there rather than having to
> go back
> to yesterdays backup or something.
>
> Best Regards, Bruce
>
Bruce,
Thanks for the info. All my downstream servers are 4.1 and my masters
are all 4.0. They will all be brought up to the latest when I can
figure out a production schedule that will allow it.
So for now what I'll have is:
A <-> B -> C
|\
D E
Where A and B (4.0.16) are masters in a circular replication and C,D,E
(4.1.13) are backups and other read only slave servers .
The reason for needing the circular replication is we are moving our
production systems to another data center and I need to be able to
quickly switch the applications over to the new data center and then
back again if something goes south.
After that however I think I'll keep the two db servers at the new data
center in a circular replication for redundancy probably.
I am interested in how you go about doing a "delayed replication" to
protect against operator error. We've already fallen victim to that
situation here.
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Bruce Dembecki 2005-09-22, 11:23 am |
| On Sep 21, 2005, at 5:23 AM, Jeff wrote:
> I am interested in how you go about doing a "delayed replication" to
> protect against operator error. We've already fallen victim to that
> situation here.
>
The long story short is we use the fact that MySQL has the ability to
run the SQL thread and the IO thread of replication separately, and
control them individually. In practice we use cron and a whole bunch
of scripts to stop the I/O thread (the one reading from the master)
most of the time, and manage when the SQL thread replicates... eg at
4:00 cron stops the SQL thread. At 4:01 we start the I/O thread (this
can read a lot of changes very quickly from the master, so only need
a short time to catch up with all the changes). At 4:05 we stop the I/
O thread. Then we wait a few minutes to give ourselves a buffer...
then finally at 4:15 we start the SQL thread.... and repeat the cycle
every two hours.
The upshot is at the small end we are 10 minutes behind (the time
between we stop I/O at 4:05 and the time when we start SQL at 4:15),
and at the long end we are 2 hours behind (at 4:07 for example the
last query that the SQL thread could have executed came from the
master at 2:05).
Our scripts are a little more complicated to marry into our
monitoring system without setting off alerts that replication has
stopped and so on (and of course the machine that runs this speaks to
many masters using many instances of MySQL, so we need to manage this
for every instance of MySQL). We also manage things to allow an
emergency stop by having the scripts do an existence check on a
specific file, and if the file isn't there don't start any
replication processes. We then have a stop script which tells the
instances to stop whatever they are doing and deletes the file. At
that point replication can't resume until we replace the file
manually - we tie that emergency script to a TCP port and hey
presto... in the event of an emergency all someone needs to do is hit
the right tcp port on the server (telnet to it, hit it with a
browser, anything that will cause the port to see some activity) and
all the replication comes to a stop.
Also as part of our 2 hourly cycle we do a lot of binary log flushing
on the slave and the masters, so if we ever need to roll back we can
roll back to a specific point in time and only have to deal with
fixing problems in the logs form that point in time onwards. if an
operator error gets by before we can stop we can go to yesterdays
backup and only execute those binary logs from before the incident,
and then deal with the issue in question.
This process has reduced our downtime in the event of a total
database corruption from four hours to recover from yesterdays data
and be missing everything since, to 30 minutes and be only missing
the data since the last 2 hourly roll over. And it doesn't take long
to dump the last set of binary logs to a text file, find and fix/
remove the corrupting command and apply that whole log into the
database, effectively giving us almost zero lost data and back online
in no time (although when clients are screaming even 30 minutes feels
like an eternity). This is all of course so much better than the four
hour downtime we had before this system.
And there are side benefits... for example backups are easier to do
because the data isn't being changed except for a few minutes every 2
hours. Instead of co-ordinating timing scripts and locking tables and
doing dumps and so on we can do simple file system duplication of the
data directories.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > The long story short is we use the fact that MySQL has the ability to
> run the SQL thread and the IO thread of replication separately, and
> control them individually.
I'm fairly green with replication, but I have a simple cron job that
starts a PHP program that issues a "slave start", watches for the
"time behind master" to be zero seconds, then issues a "slave stop".
This repeats every 10 minutes (it takes about one minute to update 10
minutes of master data), so my slave is at most (worst case) 10
minutes behind the master. This could be done every two hours or even
once per day. I'll be setting up a second master to do this same
thing once per day to act as my daily backup. Once the daily backup
completes replication, I can flush tables and backup the database
tables to the backup device for long term backups.
What are the differences between doing this and turning the SQL and IO
threads on spearetly? Just IMO, that seems like alot of manipulation
that's not really necessary, but it's possible I'm missing something.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
|
|
| Stefan Kuhn 2005-10-27, 7:47 am |
| Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet:
> Le vendredi 16 septembre 2005 Ã_ 18:14 +0200, Stefan Kuhn a écrit :
>
> And can writes on each server in simultaneous time ?
I don't understand the question. Replication is pretty fast, but is not
guaranteed to happen in a certain time (not real-time).
Stefan
>
> --
> Raphaël 'SurcouF' Bordet
> http://debianfr.net/ | surcouf at debianfr dot net
--
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jigal van Hemert 2005-10-27, 7:47 am |
| Stefan Kuhn wrote:
> Am Thursday 27 October 2005 12:56 schrieb Rapha=C3=ABl 'SurcouF' Bordet=
:
>=20
rit :[color=darkred]
[color=darkred]
>=20
> I don't understand the question. Replication is pretty fast, but is not=
=20
> guaranteed to happen in a certain time (not real-time).
Stefan,
I think Rapha=C3=ABl wants to know if a user can use any server in the ci=
rcle=20
to update or insert records and that the changes will be replicated to=20
all other servers.
In another thread he stated that it was for maintaining student=20
information on various remote locations (a student can login into the=20
system on any of the locations).
What happens if a record is updated on two servers and the changes are=20
forwarded to the other servers in the circle? I dont' think that this=20
would occur often with student information, but both the student and the =
administration department might update a record "simultaniously" (in=20
comparison to the speed of replication with several remote locations).
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Devananda 2005-10-28, 11:23 am |
| Jigal van Hemert wrote:
> Stefan Kuhn wrote:
>
>
>
> Stefan,
>
> I think Raphaël wants to know if a user can use any server in the circle
> to update or insert records and that the changes will be replicated to
> all other servers.
>
> In another thread he stated that it was for maintaining student
> information on various remote locations (a student can login into the
> system on any of the locations).
>
> What happens if a record is updated on two servers and the changes are
> forwarded to the other servers in the circle? I dont' think that this
> would occur often with student information, but both the student and the
> administration department might update a record "simultaniously" (in
> comparison to the speed of replication with several remote locations).
>
> Regards, Jigal.
>
>
Any time you are running circular replication, it is possible for a
situation to arise where two servers receive "conflicting" updates at
nearly the same time; this can cause replication to stop on both servers
at the point where they read the other server's conflicting update from
their master's binary log.
For example, imagine 4 servers, A->B->C->D->A, and imagine that A and C
both receive an insert statement containing the same unique key.
Replication would stop when A reads C's insert from D's binary log, and
when C reads A's insert from B's binary log. When this type of conflict
happens, it can be difficult or impossible to restore data integrity
between all servers. Another example, imagine that A had received an
update like "SET $val = $val + 5" while C had received "SET $val = $val
* 2". These would not conflict - in the sense that replication would
continue uninterrupted - but data integrity would be lost.
In short, any time you are running circular replication, you have to
ensure that updates are processed in the same order on all servers, or
know that the order is irrelevant to your data integrity.
Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|