MySQL Replication

--001636c5c1f46333e40489c24bb4
Content-Type: text/plain; charset=ISO-8859-1

HI,

We have set-up MySQL Community Server 5.1.46 with Master to Slave
replication and everything appears to be working correctly, however I have a
couple of questions which I hope somebody can shed some light.

(1) When the network connection goes down between the master and slave
servers, it would appear that the updates are only sent from the master to
the slave, but not from the slave to the master when the connect is
re-established. Is this correct ?

(2) What is the situation regarding conflicts if the same master and slave
record is edited at the same time ?

Thanks for any help.

Neil

--001636c5c1f46333e40489c24bb4--

Re: MySQL Replication

2010/6/24 Tompkins Neil :
> HI,
>
> We have set-up MySQL Community Server 5.1.46 with Master to Slave
> replication and everything appears to be working correctly, however I hav=
e a
> couple of questions which I hope somebody can shed some light.
>
> (1) When the network connection goes down between the master and slave
> servers, it would appear that the updates are only sent from the master t=
o
> the slave, but not from the slave to the master when the connect is
> re-established.  Is this correct ?

In a master-slave architecture, updates are always from the master to
the slave. If you want two-way replication, that is a master-master
setup, but not recommended in general unless in a very controlled
environment.

> (2) What is the situation regarding conflicts if the same master and slav=
e
> record is edited at the same time ?

Fail :-) Whenever there is a conflict in the replication process, it
stops. You have to solve the issues manually and then start the
replication again. This usually occurs due to the
synchronous/distributed nature of the replication.

--=20
Jaime Crespo
MySQL & Java Instructor
Warp Networks


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

Re: MySQL Replication

On 24/06/2010 09:18, Tompkins Neil wrote:
> HI,
>
> We have set-up MySQL Community Server 5.1.46 with Master to Slave
> replication and everything appears to be working correctly, however I have a
> couple of questions which I hope somebody can shed some light.
>
> (1) When the network connection goes down between the master and slave
> servers, it would appear that the updates are only sent from the master to
> the slave, but not from the slave to the master when the connect is
> re-established. Is this correct ?

Yes. Replication is one-way by default. If you want two-way replication
you have to set it up explicitly with both servers simultaneously acting
as both master and slave.

> (2) What is the situation regarding conflicts if the same master and slave
> record is edited at the same time ?

You shouldn't normally edit records on the slave while it's acting as a
slave. Replication has two main functions: to provide a "hot backup" of
the master so that you can switch to the slave as the new master
instantly should the master fail, and to allow load balancing by
performing all reads on the slave (or multiple slaves) and updating only
the master (eg, where you have a web cluster with each web server having
its own MySQL instance acting as a slave from a central master updated
from your CMS).

Two-way replication is possible, but there are rarely any significant
benefits from it. If you do use two-way replication, you have to
implement locking at the application level as MySQL doesn't provide it
natively.

See the replication FAQ for more information:

http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html

Mark
--
http://mark.goodge.co.uk

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

Re: MySQL Replication

--001636c5c1f4c54cee0489c2beae
Content-Type: text/plain; charset=ISO-8859-1

Thanks for your quick response.

Basically our need for replication is because our websites access a local
MySQL database - which is fine. In our remote office, we also need to
access this MySQL database too however the connect time/ query speed is very
slow. At the moment the application in the office needs to update certain
fields (not all). Therefore I thought we'd look into using replication.

In your opinion what is the best method for us to use ?

Cheers
Neil

On Thu, Jun 24, 2010 at 9:31 AM, Mark Goodge wrote:

> On 24/06/2010 09:18, Tompkins Neil wrote:
>
>> HI,
>>
>> We have set-up MySQL Community Server 5.1.46 with Master to Slave
>> replication and everything appears to be working correctly, however I have
>> a
>> couple of questions which I hope somebody can shed some light.
>>
>> (1) When the network connection goes down between the master and slave
>> servers, it would appear that the updates are only sent from the master to
>> the slave, but not from the slave to the master when the connect is
>> re-established. Is this correct ?
>>
>
> Yes. Replication is one-way by default. If you want two-way replication you
> have to set it up explicitly with both servers simultaneously acting as both
> master and slave.
>
>
> (2) What is the situation regarding conflicts if the same master and slave
>> record is edited at the same time ?
>>
>
> You shouldn't normally edit records on the slave while it's acting as a
> slave. Replication has two main functions: to provide a "hot backup" of the
> master so that you can switch to the slave as the new master instantly
> should the master fail, and to allow load balancing by performing all reads
> on the slave (or multiple slaves) and updating only the master (eg, where
> you have a web cluster with each web server having its own MySQL instance
> acting as a slave from a central master updated from your CMS).
>
> Two-way replication is possible, but there are rarely any significant
> benefits from it. If you do use two-way replication, you have to implement
> locking at the application level as MySQL doesn't provide it natively.
>
> See the replication FAQ for more information:
>
> http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html
>
> Mark
> --
> http://mark.goodge.co.uk
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>
>

--001636c5c1f4c54cee0489c2beae--

Re: MySQL Replication

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

Hi

Regarding two-way replication what do you mean by "very
controlled environment" ? What things do I need to consider ?

Cheers
Neil

2010/6/24 Jaime Crespo Rinc=F3n

> 2010/6/24 Tompkins Neil :
> > HI,
> >
> > We have set-up MySQL Community Server 5.1.46 with Master to Slave
> > replication and everything appears to be working correctly, however I
> have a
> > couple of questions which I hope somebody can shed some light.
> >
> > (1) When the network connection goes down between the master and slave
> > servers, it would appear that the updates are only sent from the master
> to
> > the slave, but not from the slave to the master when the connect is
> > re-established. Is this correct ?
>
> In a master-slave architecture, updates are always from the master to
> the slave. If you want two-way replication, that is a master-master
> setup, but not recommended in general unless in a very controlled
> environment.
>
> > (2) What is the situation regarding conflicts if the same master and
> slave
> > record is edited at the same time ?
>
> Fail :-) Whenever there is a conflict in the replication process, it
> stops. You have to solve the issues manually and then start the
> replication again. This usually occurs due to the
> synchronous/distributed nature of the replication.
>
> --
> Jaime Crespo
> MySQL & Java Instructor
> Warp Networks
>
>

--0016e64698722189e70489c2c902--

Re: MySQL Replication

--000e0cd7289c8effeb0489c2d915
Content-Type: text/plain; charset=ISO-8859-1

You could have a look at the more recent 5.1 releases, those support
semi-synchronous replication iirc.


On Thu, Jun 24, 2010 at 10:50 AM, Tompkins Neil <
neil.tompkins@googlemail.com> wrote:

> Thanks for your quick response.
>
> Basically our need for replication is because our websites access a local
> MySQL database - which is fine. In our remote office, we also need to
> access this MySQL database too however the connect time/ query speed is
> very
> slow. At the moment the application in the office needs to update certain
> fields (not all). Therefore I thought we'd look into using replication.
>
> In your opinion what is the best method for us to use ?
>
> Cheers
> Neil
>
> On Thu, Jun 24, 2010 at 9:31 AM, Mark Goodge
> wrote:
>
> > On 24/06/2010 09:18, Tompkins Neil wrote:
> >
> >> HI,
> >>
> >> We have set-up MySQL Community Server 5.1.46 with Master to Slave
> >> replication and everything appears to be working correctly, however I
> have
> >> a
> >> couple of questions which I hope somebody can shed some light.
> >>
> >> (1) When the network connection goes down between the master and slave
> >> servers, it would appear that the updates are only sent from the master
> to
> >> the slave, but not from the slave to the master when the connect is
> >> re-established. Is this correct ?
> >>
> >
> > Yes. Replication is one-way by default. If you want two-way replication
> you
> > have to set it up explicitly with both servers simultaneously acting as
> both
> > master and slave.
> >
> >
> > (2) What is the situation regarding conflicts if the same master and
> slave
> >> record is edited at the same time ?
> >>
> >
> > You shouldn't normally edit records on the slave while it's acting as a
> > slave. Replication has two main functions: to provide a "hot backup" of
> the
> > master so that you can switch to the slave as the new master instantly
> > should the master fail, and to allow load balancing by performing all
> reads
> > on the slave (or multiple slaves) and updating only the master (eg, where
> > you have a web cluster with each web server having its own MySQL instance
> > acting as a slave from a central master updated from your CMS).
> >
> > Two-way replication is possible, but there are rarely any significant
> > benefits from it. If you do use two-way replication, you have to
> implement
> > locking at the application level as MySQL doesn't provide it natively.
> >
> > See the replication FAQ for more information:
> >
> > http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html
> >
> > Mark
> > --
> > http://mark.goodge.co.uk
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
> >
> >
>



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

--000e0cd7289c8effeb0489c2d915--

Re: MySQL Replication

2010/6/24 Tompkins Neil :
> Hi
>
> Regarding two-way replication what do you mean by "very
> controlled environment" ?  What things do I need to consider ?

Control at application level that you are not going to
insert/update/delete the same record on the two servers.
Even if MySQL gives some support to handle this
(auto-increment-offset, replicate-ignore-table), you should mostly
handle it at business logic (application server) layer, not in the
MySQL database. Alternatively, as Johan pointed, have a look at the
semi-synchronous replication.


--=20
Jaime Crespo
MySQL & Java Instructor
Warp Networks


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

Re: MySQL Replication

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

Hi

Just one other question. With regards the replication in MySQL 5.1 - does
it it replication the whole row of data or just the field in which the data
has been changed for the current record ?

Thanks
Neil


2010/6/24 Jaime Crespo Rinc=F3n

> 2010/6/24 Tompkins Neil :
> > Hi
> >
> > Regarding two-way replication what do you mean by "very
> > controlled environment" ? What things do I need to consider ?
>
> Control at application level that you are not going to
> insert/update/delete the same record on the two servers.
> Even if MySQL gives some support to handle this
> (auto-increment-offset, replicate-ignore-table), you should mostly
> handle it at business logic (application server) layer, not in the
> MySQL database. Alternatively, as Johan pointed, have a look at the
> semi-synchronous replication.
>
>
> --
> Jaime Crespo
> MySQL & Java Instructor
> Warp Networks
>
>

--0016e6d3e21a3964ff048a3cf6de--

Re: MySQL Replication

2010/6/30 Tompkins Neil :
> Hi
>
> Just one other question.  With regards the replication in MySQL 5.1 =
- does
> it it replication the whole row of data or just the field in which the da=
ta
> has been changed for the current record ?

MySQL 5.1 supports two replication formats: row and statement-based.
Please, have a look at the manual page:
http://dev.mysql.com/doc/refman/5.1/en/replication-formats.h tml

--=20
Jaime Crespo
MySQL & Java Instructor
Warp Networks


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