master-slave replication sync problems.

hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.

what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:

[MASTER]
mysql> show master status;

+------------------+----------+----------------------+------ ---------------=
-----+
| mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+------------------+----------+----------------------+------ ---------------=
-----+
1 row in set (0.00 sec)

[SLAVE]
mysql> stop slave;
mysql> change master to MASTER_HOST=3D'master.domain.com',
MASTER_USER=3D'replicator', MASTER_PASSWORD=3D'xxx',
MASTER_LOG_FILE=3D'mysql-bin.000010', MASTER_LOG_POS=3D13301215;
mysql> start slave;

is this correct or is there a better way to do this?

thanks

--=20
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ =C7=
É¥Ê=87 ǝǝs noʎ 'ʇuǝɯɐן s=
ǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuo=
ɔ
ǝq s,ʇǝן ʇǝʎ
%>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )

--
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: master-slave replication sync problems.

--0016e64c276429afad048eb9b64a
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

since u r starting slave by postion beyond master, and if some of the
changes are already present on slave, it would error out with duplicate.

You need
show slave status\G;
slave stop;
show slave status\G;..wait for few min..than again
show slave status\G;....just to make sure...slave is in complete sync with
master...
Here u will see the master log file and position.
U need to use this, next time u start ur slave to sync with master.

regards
anandkl

On Thu, Aug 26, 2010 at 6:34 PM, Norman Khine wrote:

> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:
>
> [MASTER]
> mysql> show master status;
>
>
> +------------------+----------+----------------------+------ -------------=
-------+
> | mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
> mysql,information_schema |
>
> +------------------+----------+----------------------+------ -------------=
-------+
> 1 row in set (0.00 sec)
>
> [SLAVE]
> mysql> stop slave;
> mysql> change master to MASTER_HOST=3D'master.domain.com',
> MASTER_USER=3D'replicator', MASTER_PASSWORD=3D'xxx',
> MASTER_LOG_FILE=3D'mysql-bin.000010', MASTER_LOG_POS=3D13301215;
> mysql> start slave;
>
> is this correct or is there a better way to do this?
>
> thanks
>
> --
> ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ =
ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן =
sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇu=
oɔ
> ǝq s,ʇǝן ʇǝʎ
> %>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
> chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Danandkl@gmail.com
>
>

--0016e64c276429afad048eb9b64a--

Re: master-slave replication sync problems.

That is really bad, you will loose changes.

You shouldnt have to do anything when rebooting either the master or
slave. If the master is down, then the slave recieves no updates. If
the slave is down, when it comes back up it checks the master log pos
and plays thro any changes that are necessary to bring it up to date...





--
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: master-slave replication sync problems.

--0016e659f4d8d535b9048eb9e67e
Content-Type: text/plain; charset=ISO-8859-1

True,
But some times, this does not work and u need to know the master bin-log
file and position to start, so that there is no loss of data.

regards
anandkl

On Thu, Aug 26, 2010 at 6:39 PM, wrote:

> That is really bad, you will loose changes.
>
> You shouldnt have to do anything when rebooting either the master or slave.
> If the master is down, then the slave recieves no updates. If the slave is
> down, when it comes back up it checks the master log pos and plays thro any
> changes that are necessary to bring it up to date...
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--0016e659f4d8d535b9048eb9e67e--

Re: master-slave replication sync problems.

i see, so the best is to just stop slave and then check the master
status, and when the master status syncs then i start the slave?



On Thu, Aug 26, 2010 at 3:09 PM, wrote:
> That is really bad, you will loose changes.
>
> You shouldnt have to do anything when rebooting either the master or slav=
e.
> If the master is down, then the slave recieves no updates. If the slave i=
s
> down, when it comes back up it checks the master log pos and plays thro a=
ny
> changes that are necessary to bring it up to date...
>
>
>
>
>



--=20
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ =C7=
É¥Ê=87 ǝǝs noʎ 'ʇuǝɯɐן s=
ǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuo=
ɔ
ǝq s,ʇǝן ʇǝʎ
%>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )

--
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: master-slave replication sync problems.

--002215b025725e0c3b048eba8896
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Yes,
You need to note down the master bin-log file name and position on the
slave, this is a must.

regards
anandkl




On Thu, Aug 26, 2010 at 7:34 PM, Norman Khine wrote:

> i see, so the best is to just stop slave and then check the master
> status, and when the master status syncs then i start the slave?
>
>
>
> On Thu, Aug 26, 2010 at 3:09 PM, wrote:
> > That is really bad, you will loose changes.
> >
> > You shouldnt have to do anything when rebooting either the master or
> slave.
> > If the master is down, then the slave recieves no updates. If the slave
> is
> > down, when it comes back up it checks the master log pos and plays thro
> any
> > changes that are necessary to bring it up to date...
> >
> >
> >
> >
> >
>
>
>
> --
> ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ =
ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן =
sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇu=
oɔ
> ǝq s,ʇǝן ʇǝʎ
> %>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
> chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Danandkl@gmail.com
>
>

--002215b025725e0c3b048eba8896--

Re: master-slave replication sync problems.

Quoting Norman Khine :

> i see, so the best is to just stop slave and then check the master
> status, and when the master status syncs then i start the slave?
>
>
>

Well Im willing to hear from others experiences, but if you really
shouldnt have to do anything. If you want you can issue a stop slave
before the reboot and a start slave after, but it shouldnt make any
difference. And Im not sure what you refer to when you talk about the
master status syncing. The master will continue to increment the
binlog when changes are made regardless of whether the slave is up. If
the slave has been rebooted and is behind the master binlog pos it
should automatically sync those changes when started...




--
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: master-slave replication sync problems.

--0016e659f4d85f98b3048ebb929f
Content-Type: text/plain; charset=ISO-8859-1

Smith,
I never said, this wont work.Some times, there are chances of lossing data.

regards
anandkl



On Thu, Aug 26, 2010 at 8:48 PM, wrote:

> Quoting Norman Khine :
>
> i see, so the best is to just stop slave and then check the master
>> status, and when the master status syncs then i start the slave?
>>
>>
>>
>>
> Well Im willing to hear from others experiences, but if you really shouldnt
> have to do anything. If you want you can issue a stop slave before the
> reboot and a start slave after, but it shouldnt make any difference. And Im
> not sure what you refer to when you talk about the master status syncing.
> The master will continue to increment the binlog when changes are made
> regardless of whether the slave is up. If the slave has been rebooted and is
> behind the master binlog pos it should automatically sync those changes when
> started...
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--0016e659f4d85f98b3048ebb929f--

Re: master-slave replication sync problems.

--0-207620734-1282836343=:75305
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi,
 
The best way to use sync the master and slave is using mk-checksum tools. j=
ust google it and use the the tools for online sync of master and slave.
 
Jeetendra Ranjan
MySQL DBA

--- On Thu, 26/8/10, Norman Khine wrote:


From: Norman Khine
Subject: master-slave replication sync problems.
To: mysql@lists.mysql.com
Date: Thursday, 26 August, 2010, 6:34 PM


hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.

what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:

[MASTER]
mysql> show master status;

+------------------+----------+----------------------+------ ---------------=
-----+
| mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+------------------+----------+----------------------+------ ---------------=
-----+
1 row in set (0.00 sec)

[SLAVE]
mysql> stop slave;
mysql> change master to MASTER_HOST=3D'master.domain.com',
MASTER_USER=3D'replicator', MASTER_PASSWORD=3D'xxx',
MASTER_LOG_FILE=3D'mysql-bin.000010', MASTER_LOG_POS=3D13301215;
mysql> start slave;

is this correct or is there a better way to do this?

thanks

--=20
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ =C7=
É¥Ê=87 ǝǝs noʎ 'ʇuǝɯɐן s=
ǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuo=
ɔ
ǝq s,ʇǝן ʇǝʎ
%>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Djitendra_=
ranjan@yahoo.com


--0-207620734-1282836343=:75305--

Re: master-slave replication sync problems.

Quoting Ananda Kumar :

> Smith,
> I never said, this wont work.Some times, there are chances of lossing data.
>
> regards
> anandkl
>
>

If you have experience of this fair enough. Theres no reason it should
make any difference, as everything is based upon the binlog file and
position...




--
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: master-slave replication sync problems.

Quoting jitendra ranjan :

> Hi,
>  
> The best way to use sync the master and slave is using mk-checksum
> tools. just google it and use the the tools for online sync of
> master and slave.
>  
> Jeetendra Ranjan
> MySQL DBA
>
Yes, data integrity isnt guaranteed with MySQL replication. So if you
want to be sure good idea to use the maartkit tools...
Tho this isnt necessarily answering the original question, which is to
do with the master and slave not showing the same binlog file and log
position. If replication is working at all, these will be in sync
(assuming the changes on the master arent happening faster than
replication can keep up).




--
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: master-slave replication sync problems.

ssh to the slave
mysql -uroot -pPASSWORD -P3306 -hlocalhost

show slave status\G

If the Slave IO is NOT Running, but SQL is, then simply try to restart the
slave...

*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.10.10.45
Master_User: slave
Master_Port: 3306
...
Slave_IO_Running: No
Slave_SQL_Running: Yes

start slave; show slave status\G

Otherwise if it's a replication issue such as this, then you must skip over
the bad SQL queries one at a time till the slave syncs.

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.41
Master_User: slave
Master_Port: 3306
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: agis_core_2008
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1061
Last_Error: Error 'Duplicate key name 'id_operator'' on
query. ....

The SQL statement will give you an idea of where the master and slave went
askew. If these are recent commands you did you can guess as to how much to
increment the SKIP_COUNTER below, otherwise, you just have to do it one at
a time until they sync again.

mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

Repeat the above statements over and over until you see two YES rows.

*************************** 1. row ***************************

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

> -----Original Message-----
> From: Norman Khine [mailto:norman@khine.net]
> Sent: Thursday, August 26, 2010 6:05 AM
> To: mysql@lists.mysql.com
> Subject: master-slave replication sync problems.
>
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:
>
> [MASTER]
> mysql> show master status;
>
> +------------------+----------+----------------------+------ --
> ------------------+
> | mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
> mysql,information_schema |
> +------------------+----------+----------------------+------ --
> ------------------+
> 1 row in set (0.00 sec)
>
> [SLAVE]
> mysql> stop slave;
> mysql> change master to MASTER_HOST='master.domain.com',
> MASTER_USER='replicator', MASTER_PASSWORD='xxx',
> MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=13301215;
> mysql> start slave;
>
> is this correct or is there a better way to do this?
>
> thanks


--
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: master-slave replication sync problems.

Hello List,

On 8/26/2010 3:00 PM, Daevid Vincent wrote:
> ssh to the slave
> mysql -uroot -pPASSWORD -P3306 -hlocalhost
>
> show slave status\G
>
> If the Slave IO is NOT Running, but SQL is, then simply try to restart the
> slave...
>
> *************************** 1. row ***************************
> Slave_IO_State:
> Master_Host: 10.10.10.45
> Master_User: slave
> Master_Port: 3306
> ...
> Slave_IO_Running: No
> Slave_SQL_Running: Yes
>
> start slave; show slave status\G
>
> Otherwise if it's a replication issue such as this, then you must skip over
> the bad SQL queries one at a time till the slave syncs.
>
> *************************** 1. row ***************************
> Slave_IO_State: Waiting for master to send event
> Master_Host: 10.10.10.41
> Master_User: slave
> Master_Port: 3306
> ...
> Slave_IO_Running: Yes
> Slave_SQL_Running: No
> Replicate_Do_DB: agis_core_2008
> Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
> Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table:
> Last_Errno: 1061
> Last_Error: Error 'Duplicate key name 'id_operator'' on
> query. ....
>
> The SQL statement will give you an idea of where the master and slave went
> askew. If these are recent commands you did you can guess as to how much to
> increment the SKIP_COUNTER below, otherwise, you just have to do it one at
> a time until they sync again.
>
> mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
> slave status\G
>
> Repeat the above statements over and over until you see two YES rows.
>
> *************************** 1. row ***************************
>
> Slave_IO_Running: Yes
> Slave_SQL_Running: Yes
>
>> -----Original Message-----
>> From: Norman Khine [mailto:norman@khine.net]
>> Sent: Thursday, August 26, 2010 6:05 AM
>> To: mysql@lists.mysql.com
>> Subject: master-slave replication sync problems.
>>
>> hello,
>> i have a working master-slave replication, the problem i find is that
>> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
>> and MASTER_LOG_POS on the SLAVE.
>>
>> what is the correct way to keep the two slaves in sync even after i
>> restart the server. the way i do it now is to:
>>
>> [MASTER]
>> mysql> show master status;
>>
>> +------------------+----------+----------------------+------ --
>> ------------------+
>> | mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
>> mysql,information_schema |
>> +------------------+----------+----------------------+------ --
>> ------------------+
>> 1 row in set (0.00 sec)
>>
>> [SLAVE]
>> mysql> stop slave;
>> mysql> change master to MASTER_HOST='master.domain.com',
>> MASTER_USER='replicator', MASTER_PASSWORD='xxx',
>> MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=13301215;
>> mysql> start slave;
>>
>> is this correct or is there a better way to do this?
>>
>> thanks
>
>

To me, it appears that many of you are not fully versed in the theory of
operations for how MySQL replication actually functions. Granted, there
are two formats for replication (ROW and STATEMENT) but the general
process remains the same. Here is a nutshell summary of the process.

** on the master **

m1) The MySQL master is instructed to change some data.

m2) The data is changed and the results are committed to disk. If you
rollback the changes before you commit them, then there is nothing to
replicate. Only the InnoDB engine supports this type of rollback.

m3) The change committed in step 2 is written to the binary log

(repeat from step m1 until the Master is shutdown)


** on the slave - the IO thread **
(assuming that the slave is already configured with compatible data, a
starting position, and the proper credentials to act as a slave)

o1) The SLAVE IO thread requests information from the master's binary
logs. This information is identified by a file name and a byte offset
from the start of that file

o2) The SLAVE IO thread copies all available information from the
master's binary logs into a local copy of those logs known as the relay
logs.

(repeat from o1 until the SLAVE IO thread is stopped(by error or by
command) or the slave is shutdown)

** on the slave - the SQL thread **

s1) Once an unapplied change has been completely buffered into the relay
logs, the SLAVE SQL thread attempts to apply the change to the slave's
data.

s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the
correct format) into the slave's binary log.

(repeat from s1 until the SLAVE SQL thread is stopped (by error or by
command) or the slave is shutdown)

**
As you can tell by this very simplified process description, there is no
attempt to rectify one dataset to the other. Replication operates under
the principle that if you perform identical changes to identical sets of
data, you will end up with identical end results.

Various replication "filters" can omit certain changes from either being
replicated to or processed by the slave instance. Use these WITH EXTREME
CAUTION as they can very easily create situations where the master and
slave datasets diverge to the point that a rebuild of the slave data is
the only effective repair.

**
When you get a replication error, such as DUPLICATE KEY, it is the
administrator's responsibility to figure out why and fix it. Maybe some
user on the slave added an extra row to a table? Maybe an INSERT ...
SELECT added more rows on the slave than it did on the master? Maybe a
DELETE on the master removed less rows than it did on the slave?

Simply skipping those problems (and others) without investigation or
correction may allow the two datasets (master and slave) to diverge even
more. The human administrator is required to make a judgment call on
which version of the conflicting row is the "correct" version. Is is the
one on the table or the change coming in from the binary log? Maybe the
data on the slave is "correct" but it has been offset by a few rows
inserted a long time ago.

It's your responsibility to understand and appropriately respond to the
errors not just repeat scripted actions until the problems disappear
temporarily.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
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: master-slave replication sync problems.

On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine wrote:
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:

As one other stated, it should not lose position when you stop and
start the master or slave in a controlled manner. And also stated was
that the way you were doing it was causing a loss of data, and you'll
need to use maatkit to fix it, or if you can afford some downtime,
stop mysql on both and copy the whole database(s) from master to slave
(and reconfigure replication of course).

On the slave, I'd be curious what the result was when you did only a
"STOP SLAVE;" and then "START SLAVE;" because in my estimation,
chances are it would have picked right up where it was supposed to.

Your issue is likely that your slave has a large "slave_net_timeout"
value (default is 3600 seconds IIRC). That means your slave will take
1 hour to finally give up on the replication connection (that the
master closed on it), close the connection, and then reopen it. Lower
that setting to something smaller, I've gone as low as 30 seconds, but
I would only do that on a reliable network. Try lowering it to 300 (5
minutes) on your slave. When you restart the master, the slave should
timeout after only 5 minutes and then resume replication.

--=20
Regards...=A0 =A0 =A0 Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm.=A0 -- Marcus Aurealius

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