MySQL Forums
Forum List  »  Replication

Problem with replication
Posted by: Anatoli Marinov
Date: September 14, 2005 02:34AM

Hi,
I have a 3 mysql servers that work toghater. One of them is a master and other 2 servers are slaves and replicate all queries from master. I have a script which monitor master and if master falls I switch one of other servers to master and make "change master to new master ..." for other slave server. It works fine but when i try to return old master like a slave to new master because it have to sync data I make this:

to old master which i try to set it to slave connected to current master:
stop slave;
reset master;
reset slave;
change master to master_host='<new_master>', master_user='' .......
start slave;

and replication begins ...
after some time this slave server stop replication and all looks fine. I haven`t error in master log file or slave log file but the data in master and slave not equal (~20 000 rows missing :) from slave )

if I make same operation to other slave : stop slave; reset slave; change master .............
it sync to the end and not have any problems

my tables is:

CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL auto_increment,
`cash` int(11) NOT NULL default '0',
`user` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`)
)


after replication master table status is:
mysql> show table status\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 185662
Avg_row_length: 23
Data_length: 4446552
Max_data_length: 4294967295
Index_length: 1524736
Data_free: 0
Auto_increment: 185663
Create_time: 2005-09-14 10:33:01
Update_time: 2005-09-14 10:35:11
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
| 185662 |
+-----------+
1 row in set (0.00 sec)


after replication slave (old_master) table status is:

mysql> show table status\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 163405
Avg_row_length: 23
Data_length: 3921512
Max_data_length: 4294967295
Index_length: 1342464
Data_free: 0
Auto_increment: 185663
Create_time: 2005-09-14 10:42:47
Update_time: 2005-09-14 10:45:19
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
| 163405 |
+-----------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.70.82
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 22620921
Relay_Log_File: toli-relay-bin.000001
Relay_Log_Pos: 19926471
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: repl
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 22620921
Relay_Log_Space: 19926471
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

In this case i never join old master like slave to the replication network. Every from other nodes I can stop and return back but only old master is broken forever :)
Why it hapens?
Everibody know how I can repaire old master and join it to the replication network like a slave.

Best Regards

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with replication
2787
September 14, 2005 02:34AM
1722
September 14, 2005 03:09AM
1856
September 14, 2005 06:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.