MySQL Bugs: #49197: MySQL SwitchOver mechanisam not works.

Bug #49197	MySQL SwitchOver mechanisam not works.
Submitted:	30 Nov 2009 8:38	Modified:	1 Dec 2009 6:57
Reporter:	Amit Bora	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I have a successful setup like Master and slave replication. 
But in case of master failure, I need slave become a master. And when master gets up, it has to be configured as slave.

As given in link http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html
 
When Master is up again, you must issue on it the same CHANGE MASTER TO as that issued on Slave 2 and Slave 3, so that Master becomes a slave of S1 and picks up each Web Client writes that it missed while it was down.

I have fired a CHANGE MASTER on master (when gets up) but not working properly?
Details steps done given in Repeate section.
Thanks
Amit

How to repeat:
Consider
y.y.y.y - MASTER MACHINE
x.x.x.x - SLAVE MACHINE

Configure The Master

1. Add this 
	
	# SERVER SECTION

	[mysqld]

	#Path to the database root
	datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

	binlog-do-db=mysql_repl
	server-id=1
	log-bin="C:/MySQL_Binary_Log/"

2. Restart MySQL

3. Then we log into the MySQL database as root and create a user with replication privileges:

	c:\> mysql -u root -p
	Enter password:

	mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'x.x.x.x.' IDENTIFIED BY 'password';

	mysql>  FLUSH PRIVILEGES;

	mysql>  USE mysql_repl;

	mysql>  FLUSH TABLES WITH READ LOCK;

	mysql>  SHOW MASTER STATUS;

	The last command will show something like this:
	+---------+----------+--------------+------------------+
	| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
	+---------+----------+--------------+------------------+
	| .000003 |      106 | mysql_repl   |                  |
	+---------+----------+--------------+------------------+

4. Exit mysql

5. C:\>mysqldump -u root -ppassword --opt mysql_repl > mysql_repl.sql

6. Finally we have to unlock the tables in mysql_repl :

	mysql -u root -p
	Enter password:
	UNLOCK TABLES;
	exit;

Configure The Slave

1. On the slave we first have to create the database mysql_repl:

	mysql -u root -p
	Enter password:

	mysql> 	CREATE DATABASE exampledb;
	mysql>	exit;

2. Copy SQL dump of mysql_repl as "mysql_repl.sql" to the slave, then it is time now to import the SQL dump into our newly created mysql_repl on the slave:

	mysql> 	mysql -u root -p mysql_repl < "C:\mysql_repl.sql"

3. Now we have to tell MySQL on the slave that it is the slave, that the master is x.x.x.x, 
   and that the master database to watch is mysql_repl. Therefore we add the following lines to C:\my.ini:

	server-id=2
	master-host=y.y.y.y
	master-user=slave_user
	master-password=secret
	master-connect-retry=60
	replicate-do-db=exampledb

4. Restart MySQL.

5. Finally, we must do this:

	mysql -u root -p
	Enter password:
	
	mysql>	SLAVE STOP;

	mysql> CHANGE MASTER TO MASTER_HOST='x.x.x.x', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='.000003', MASTER_LOG_POS=106;

6. Now all that is left to do is start the slave. Still on the MySQL shell we run
	
	mysql>	START SLAVE;
	mysql>	exit;

=======================================================================
When master goes down. 

I did following steps on Slave - 

1. Stop Slave
2. RESET MASTER.

When  MASTER gets up I did following steps on MASTER (To become slave )

CHANGE MASTER TO MASTER_HOST='x.x.x.x', MASTER_USER='slave_user', MASTER_PASSWORD='password';

Howevere afer doing this nothing is replicated to slave (y.y.y.y) when update done on MAster (x.x.x.x)

Thank you for the report.

This looks like not a bug, but wrong setup for me. Do you have option log-bin in x.x.x.x configuration file? What shows SHOW SLAVE STATUS\G running on y.y.y.y after switch?

This is the output after I executed the below steps: 

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: .000013
          Read_Master_Log_Pos: 106
               Relay_Log_File: ps4743-relay-bin.000009
                Relay_Log_Pos: 242
        Relay_Master_Log_File: .000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mysql_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: 106
              Relay_Log_Space: 398
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.11 sec)

mysql> show master status \G
*************************** 1. row ***************************
            File: ps4743-bin.000001
        Position: 208
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Thank you for the feedback.

You have Replicate_Do_DB rule, probably this matters. Please send us binlog which was not replicated.

Trying to insert recoed which is not replicated to slave(y.y.y.y)

Attachment: BinLogFile_NotReplicated.zip (application/x-zip-compressed, text), 440 bytes.

Trying to insert record which is not replicated to slave(y.y.y.y)

Attachment: .000001 (application/octet-stream, text), 206 bytes.

I am not understood the way I need to follow.

I will explore What I am doing.

1. I have setup working as Master - Slave both side bin--log is created. 
2. I shutdown the Master Server.
3. Running the command on Slave (x.x.x.x) as
   STOP SLAVE -> RESET MASTER.

4. starting y.y.y.y (Master) -> STOP SLAVE -> 
   CHANGE MASTER TO MASTER_HOST='x.x.x.x', MASTER_USER='slave_user', 
   MASTER_PASSWORD='password';

Is this the correct way of changing the master(when gets up) to become a slave?

I don't made any changes in my.ini file of both. Kept as it is with initial setting done. Down it matters?

Thank you for the feedback.

Binary log file is correct and should be replicated having information provided.

But you have "Master_Log_File: .000013" in SHOW SLAVE STATUS while sent file named ".000001" Also you issued MASTER_LOG_FILE='.000003', so it is expected log file .000001 is not replicated. Please check if this is just wrong names you sent to us or you really mixed these logs.

I think Meanwhile I done RESET MASTER so previous log is deleted.

Please have a look into the attached file for info.

New configuration information.

Attachment: CommandLog.rtf (application/rtf, text), 5.12 KiB.

Binary Log

Attachment: .000002 (application/octet-stream, text), 306 bytes.

At Last I came to know that I have not created user on SLAVE(which is now master) machine to replicate data on master(which is now slave of slave).

The problem is solved. Thanks for the prompt reply each time.

Thank you for the update.

Closed as "not a bug".