LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 09-08-2010, 07:36 AM   #1
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Rep: Reputation: 16
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
 
Old 09-08-2010, 08:14 AM   #2
jrosco
Member
 
Registered: Aug 2010
Location: Australia
Posts: 37

Rep: Reputation: 4
Have a look in the mysql log file

Code:
less /var/mysql/data/hostname.err
and see if there are any errors after doing the replication

JC
 
Old 09-08-2010, 08:27 AM   #3
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
No such file..i had tried with locate command
 
Old 09-08-2010, 09:21 AM   #4
quanta
Member
 
Registered: Aug 2007
Location: Vietnam
Distribution: RedHat based, Debian based, Slackware, Gentoo
Posts: 724

Rep: Reputation: 100Reputation: 100
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.

Last edited by quanta; 09-08-2010 at 09:24 AM.
 
Old 09-08-2010, 09:53 AM   #5
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
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
 
Old 09-08-2010, 10:07 AM   #6
quanta
Member
 
Registered: Aug 2007
Location: Vietnam
Distribution: RedHat based, Debian based, Slackware, Gentoo
Posts: 724

Rep: Reputation: 100Reputation: 100
Pay attention to the red lines:
Quote:
Originally Posted by ajayan View Post
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
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.

Last edited by quanta; 09-08-2010 at 10:24 AM.
 
1 members found this post helpful.
Old 09-08-2010, 10:28 AM   #7
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
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
 
Old 09-08-2010, 10:31 AM   #8
quanta
Member
 
Registered: Aug 2007
Location: Vietnam
Distribution: RedHat based, Debian based, Slackware, Gentoo
Posts: 724

Rep: Reputation: 100Reputation: 100
http://dev.mysql.com/doc/refman/5.0/...stingdata.html
 
Old 09-08-2010, 11:00 AM   #9
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. SpellChainz Linux - Newbie 1 06-23-2007 03:35 PM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 08:44 AM
Mysql replication pk21 Linux - Software 0 08-28-2003 05:00 AM
mysql replication cheng Linux - Software 1 08-22-2002 02:11 PM


All times are GMT -5. The time now is 03:43 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration