Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to restart Mysql Master & Slave replication ?

Posted on 2013-06-28
5
Medium Priority
?
856 Views
Last Modified: 2013-07-12
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
Comment
Question by:prsn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:multimac
ID: 39289257
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
 

Author Comment

by:prsn
ID: 39290020
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
 

Author Comment

by:prsn
ID: 39291427
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
 
LVL 7

Accepted Solution

by:
multimac earned 1500 total points
ID: 39292436
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
 

Author Comment

by:prsn
ID: 39320810
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question