Solved

How to restart Mysql Master & Slave replication ?

Posted on 2013-06-28
5
812 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to send multiple emails at the same time in PHP 12 61
Coding C# in Linux 8 71
configure dependency in POM for new database 3 26
Can't ping New Linux Servers 40 68
Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

831 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