Re: Replication began to work not always, not right away
Posted by:
Dan Mor
Date: April 07, 2012 08:08PM
> The Host (in the PROCESSLIST) is NULL ??? Please describe the topology of your Master and Slave -- same or separate machines? TCP/IP connection? Other?
> (I don't know what to make of that. I hope the Master and Slave are not on the same machine.)
why host is NULL? I dont understand you here.
Master and Slave are different machines running with windows 2008. Thay are in defferent countries, ping (packets delay) is about 40-50 ms.
> Check the clocks on the two machines; the seem to be at least 8 seconds apart.
>(I don't think this could cause the trouble, but you probably should fix it.)
You were right: time at master for some reasons stoped to synchronize with time.windows.com, and there was a differenceof 5 seconds (not 8) with slave.
Thanks that you sight it. It helps me may be to avoid some other problems.
> SHOW MASTER STATUS; -- on Master
File|Position|Binlog_Do_DB|Binlog_Ignore_DB
bin.000003|828426549|jedif1
> SHOW SLAVE STATUS \G -- on Slave
what means "\G" ? mistake?
SHOW SLAVE STATUS returns this:
Slave_IO_State|Master_Host|Master_User|Master_Port|Connect_Retry|Master_Log_File|Read_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos|Relay_Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Replicate_Do_DB|Replicate_Ignore_DB|Replicate_Do_Table|Replicate_Ignore_Table|Replicate_Wild_Do_Table|Replicate_Wild_Ignore_Table|Last_Errno|Last_Error|Skip_Counter|Exec_Master_Log_Pos|Relay_Log_Space|Until_Condition|Until_Log_File|Until_Log_Pos|Master_SSL_Allowed|Master_SSL_CA_File|Master_SSL_CA_Path|Master_SSL_Cert|Master_SSL_Cipher|Master_SSL_Key|Seconds_Behind_Master|Master_SSL_Verify_Server_Cert|Last_IO_Errno|Last_IO_Error|Last_SQL_Errno|Last_SQL_Error|Replicate_Ignore_Server_Ids|Master_Server_Id
Waiting for master to send event|#masters IP adress#|replication|3306|60|bin.000003|828456990|UAGALAXY-1-relay-bin.000007|140902703|bin.000003|Yes|Yes|jedif1||jedif1.ibf__uag_ob_maps,jedif1.ibf_members,...#and all other tables#||||0||0|828456990|140902864|None||0|No||||||0|No|0||0|||3
> (I'm fishing for binlog/replicate-do/ignore and anything else out of the ordinary.)
MASTER SETTINGS:
sync_binlog=1
server-id=3
log-bin=bin
log_bin_trust_function_creators=1
binlog-do-db=jedif1
sql-mode="NO_AUTO_CREATE_USER"
local-infile=0
SLAVE SETTINGS:
server-id=1
replicate-do-db=jedif1
replicate-do-table=jedif1.ibf_members
replicate-do-table=jedif1.ibf__xja_members
replicate-do-table=jedif1.ibf__uag_awards
replicate-do-table=jedif1.ibf__uag_banlist
replicate-do-table=jedif1.ibf__uag_gvars
# ... and some more tables, i skip long list here ... but when I have no "replicate-do-table" fields, my problems were the same
slave-skip-errors=1062,1146
sql-mode="NO_AUTO_CREATE_USER"
> CHECK TABLE ibf__uag_banlist;
> CHECK TABLE ibf__uag_gvars;
checked. All is OK!
Rick, it is amazing! My test innoDB table ibf__uag_gvars stops working again, and see what I have done:
I always test replication work using SQLYog mysql client - this is very handy application.
And it passes queries in such format:
UPDATE `jedif1`.`ibf__uag_gvars` SET `value`='0.333' WHERE `name`='defaultShipSpeed';
after that I runs query at Slave: << SELECT value FROM ibf__uag_gvars WHERE NAME="defaultShipSpeed" >>
it returns 0.211 (old value) - replication have not done
then the idea comes to me, and I runs this query on Master:
UPDATE ibf__uag_gvars SET value=0.444 WHERE name="defaultShipSpeed";
and after that << SELECT value FROM ibf__uag_gvars WHERE NAME="defaultShipSpeed" >> returns 0.444!!!!! <-- WORKS!
SO replication IS WORKING, it only wants simple format to use? And have some bugs with complex format like `jedif1`.?
I only now recall why I set to SKIP error 1146 (Table xxx doesn't exist) earlier... because I have some errors like 'table `jedif1`.xxxx doesn't exist)' on tables I need not to replicate, but they were on slave in fact.
Edited 1 time(s). Last edit at 04/07/2012 08:12PM by Dan Mor.