• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 940
  • Last Modified:

How to restart Mysql Master & Slave replication ?

Hello Experts,

I have a server 1 as master and server 2 as slave . Replication has been working fine for weeks however last night server 1 was restarted and now the Slave server is not sync the databases. I know there's a solution... Do a mysqldump of all the databases then transfer it to the Slave and finally reset the "mysql-bin.00000" log files however my databases are more than 18GB and this process is a pain.

Is there a way to tell the Slave server to start running again and get the information loss during this time frame?  

Here's my slave status:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17993248
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: x.x.x.x
                  Master_User: slave_cluster
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 583884821
               Relay_Log_File: slave-relay.000013
                Relay_Log_Pos: 3830
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: databases name here
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 577004738
              Relay_Log_Space: 12111518
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)

Open in new window

0
prsn
Asked:
prsn
  • 3
  • 2
1 Solution
 
multimacCommented:
Strange, what is the new segment name after the reboot anyway?
show master status

Open in new window


You could just make a
CHANGE MASTER TO MASTER_LOG_FILE="NEWSEGEMENT", MASTER_LOG_POS=0

Open in new window

and
slave start

Open in new window

0
 
prsnAuthor Commented:
Hello multimac

When running a show master status; it shows

mysql> show master status;
+------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| File             | Position  | Binlog_Do_DB                                                                                                                                                                    | Binlog_Ignore_DB |
+------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| mysql-bin.000012 | 320644165 | all my databases names here |                  |
+------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
1 row in set (0.01 sec)

Open in new window


When you refer to "newsegment" is the mysql-bin.000012 ? The slave server has been offline for 4 days so far. Will this copy all the missing data?
0
 
prsnAuthor Commented:
multimac

I did your recommendation and the slave server is replicating once again. However the data from the pasts 4 days are not on the Slave server. I will like to know if its possible to make the Slave get that info back on the databases.

Regards,
prsn
0
 
multimacCommented:
You could copy that binlog files manually and replay it, but this will lead to more inconsistency.

I see two possibilities:

Cold-copy
slave
- Shutdown the mysql-process.
- Delete master.info and relay.log in the MySQL Data directory.

master
- Shutdown the mysql-process for a moment and use a tool like rsync to copy the Data directory to the slave.
- Restart the mysql-process
- Run "Show master status" in the mysql console and write down the new binlog filename and the position.

slave again
- Execute a change master command with the gained information and the right host and replication user, e.g.
master to master_host='10.10.10.1' , master_user='REPL', master_password='foobar', master_log_file='mysql-bin.000002', master_log_pos=1;

Open in new window

- Execute
start slave

Open in new window


Warm-copy (INNODB only) with locking
You can directly replay the dump and set the right values for the slave with a command like:
mysqldump --host=master --user=root --password=foobar --single-transaction --lock-all-tables --master-data=1 --all-databases | mysql --host=slave

Open in new window


Warm-copy without lock
You can use a tool like Percona Xtrabackup:
http://www.percona.com/doc/percona-xtrabackup/2.1/
0
 
prsnAuthor Commented:
Hello multimac

Solve my problem I had to transfer all the databases back again to the slave server and then start the replication from 0. However I didn't knew about Percona Xtrabackup I will give it a try. Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now