Solved

How to restart Mysql Master & Slave replication ?

Posted on 2013-06-28
5
802 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now