Friday, 28 June 2013

Mysql Master-Slave Replication after slave fails

1. From slave if we run mysql> mysql slave status;
   it will show last bin file slave reads from master 
 and all,so start from that bin file to sink with master to slave.

2. Set Master configuration on the Slave.
  Execute the following command on a MySQL prompt to sink slave with master:

mysql > CHANGE MASTER TO MASTER_HOST=’10.100.10.80’, MASTER_USER=’repl’, MASTER_PASSWORD=’slavepassword’, MASTER_LOG_FILE=’mysql-bin.000003’, MASTER_LOG_POS=106;

This is how you tell Slave how to connect to Master in order to replicate. Note the log coordinates. These are the coordinates you got from step 1 above.

[
Now we need to tell the slave where the master is located, which binlog file to use, and which position to start. Issue this CHANGE MASTER TO 
command on the slave server(s): (don’t forget to change the values to match your master server)

 mysql> CHANGE MASTER TO
    ->   MASTER_HOST='master IP address',
    ->   MASTER_USER='replication user',
    ->   MASTER_PASSWORD='replication user password',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000015',
    ->   MASTER_LOG_POS=540,
    ->   MASTER_CONNECT_RETRY=10;

mysql> show warnings\G

Two values to note in the slave status shows us that our CHANGE MASTER TO statement worked:

    Master_Log_File: mysql-bin.000015
    Read_Master_Log_Pos: 540
]

3. Stop MySQL

4. Start MySQL normally

Checking out that everything is OK

Having started the slave MySQL node, you can log in and issue some commands to make sure that Slave is running OK.

On mysql prompt, give the following command:

mysql> show processlist;

You can see the SQL thread that gets data from Master (in the above output is the thread with Id 2) and the SQL thread that executes the statements on Slave (in the output is the thread with Id 1).

2. mysql> show slave status;

This will display the current status on slave. Pay attention to the *_Errno and *_Error columnsNormally, you shouldn’t see anything that indicates existence of errors there.

3. On mysql prompt, give the following command

mysql> show status like ‘Slave%’;

You should see an output like the following:
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Slave_open_temp_tables     | 0     |
| Slave_retried_transactions | 0     |
| Slave_running              | ON    |
+----------------------------+-------+

Pay attention to Slave_running being with value ON.

Important note on binary log time to live

As we have said before, you can have Slave down and
re-synchronize as soon as you bring it up again.But do not put it out of service for quite long because, then it will be impossible to synchronize its content with Master. 

This is because the binary logs on Master do not leave forever.

There is the variable with name expire_logs_days that determines the number of days for automatic binary log file removal. Check this out. This should be 10, meaning that if you ever have your Slave down for 10 days or more, it will not be able to do replication as soon as you bring it up, and you will have to  everything from the beginning.

No comments:

Post a Comment