Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to restart Mysql Master & Slave replication ?

Posted on 2013-06-28
5
Medium Priority
?
879 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 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
I have written articles previously comparing SARDU and YUMI.  I also included a couple of lines about Easy2boot (easy2boot.com).  I have now been using, and enjoying easy2boot as my sole multiboot utility for some years and realize that it deserves …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

916 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