Mysql replication failed after mysql reboot
 

I had setup Mysql replication on Ubuntu 10.04.1 LTS server.At the time of configuration on Master the command

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 318 | | |
+------------------+----------+--------------+------------------+

i had updated slave with following details
ie,
CHANGE MASTER TO MASTER_HOST='192.168.1.20', MASTER_USER='repl', MASTER_PASSWORD='redhat', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=318;

Now every things successfully.All updates were successfully replicated to Client.

then i have rebooted mysql server (Master),but after reboot it seems MASTER_LOG_FILE, MASTER_LOG_POS were changed.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 191| | |
+------------------+----------+--------------+------------------+


So i am forced to update Slave details again

CHANGE MASTER TO MASTER_HOST='192.168.1.20', MASTER_USER='repl', MASTER_PASSWORD='redhat', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=191;

How can i avoid updating master details on slave after mysql reboot or Is there any option to auto update.

Advanced Thanks,
Ajayan

Have a look in the mysql log file

and see if there are any errors after doing the replication

JC

No such file..i had tried with locate command

You only need execute 'CHANGE MASTER...' command when the Slave did not catch up with the Master. Otherwise, the Slave will automatically replicate if you config correctly.
Please give us the output of 'SHOW SLAVE STATUS\G;' after restarting MySQL Master. Post the my.cnf file also on both of Master and Slave.

Thanks for you attention..
Master My.cnf
************
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

Slave my.cnf
************
server-id = 2
expire_logs_days = 10
max_binlog_size = 100M


Here is my show slave status

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 192.168.1.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 187
Relay_Log_File: lucid-relay-bin.000002
Relay_Log_Pos: 332
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 187
Relay_Log_Space: 487
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error: error reconnecting to master
'repl@192.168.1.20:3306' - retry-time:
60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Thanks,
Ajayan

Pay attention to the red lines:
Put the master-host, master-port, master-password into my.cnf on the Slave, you won't need execute 'CHANGE MASTER...' once you restart MySQL master.

Still its showing error..
Is there any thing to removed from Slave database or Slave setting.i mean is there any table exist in the slave with old setting...

can u please advise me to what are the additional steps to be done on Master/Slave?

Thanks,
Ajayan

Thanks for Your Great Help..


Now its successfully replicating.I haven't restarted Mysql slave after mysql master restart.Now i had tried after slave restart.It works fine..Thank you again for your valuable answers..I have also planning to implement fail-over with replication...

My initial plan is to use heartbeat failover s/w.Any suggestion/Any helpful docs..

Thanks
Ajayan