Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a set up of replication with master 5.1.30 and slave 5.5.16 and the replication is working good

Now i have upgraded mysql master to 5.1.47

As far as i know we have to turn off the log bin with sql_log_bin=0 before using mysql_upgrade program in order to up grade the replication setup as well

but the problem here is the binary log was not turned off while mysql_upgrade program is running

The reason i found is in 5.1 the sql_log_bin is a session variable and mysql_upgrade program runs in another session

so how to upgrade the replication as well along with the server with any breakage on replication setup.

any suggestions are really useful.....

share|improve this question

migrated from stackoverflow.com Apr 18 '13 at 16:17

This question came from our site for professional and enthusiast programmers.

1 Answer 1

The mysql_upgrade program tries to realign the mysql.user table with latest grants for that MySQL Major Release. Since you only did a minor upgrade (5.1.30 -> 5.1.47) the mysql_upgrade was needless. The grant tables for the Slave have no bearing on Replication since the Slave has to authenticate back at the Master.

Fixing MySQL Replication is just a case of repointing replication from the last know executable position and starting from there. Here is how you do this:

Run SHOW SLAVE STATUS\G and choose the following two fields

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000504
        Read_Master_Log_Pos: 326579313
             Relay_Log_File: relay-bin.081210
              Relay_Log_Pos: 326579450
      Relay_Master_Log_File: mysql-bin.000504 <--- Choose This
           Slave_IO_Running: Yes
          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: 326579313 <--- Choose That
            Relay_Log_Space: 326579450
            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)

From the above display, your would choose mysql-bin.000504 and 326579450.

Simply do the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000504',master_log_pos=326579450;
START SLAVE;

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.