There is more stable approach you can try
Here is something to remember
Whenever you run CHANGE MASTER TO
, it will erase every relay log you have. You do not want to keep relay logs of commands you have not executed any SQL on as of yet
The following is an excerpt taken from a post I made back on Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication :
Please notice that there are two sets of replication coordinates from
the Master
- (Master_Log_File,Read_Master_Log_Pos)
- (Relay_Master_Log_File,Exec_Master_Log_Pos)
There is a major difference between them
(Master_Log_File,Read_Master_Log_Pos)
tells you the last binlog statement from the Master's log file and log position that the Slave
read from the Master and placed in its Relay Logs.
(Relay_Master_Log_File,Exec_Master_Log_Pos)
tells you the last binlog statement from the Master's log file and log position that the
Slave read from the Master and placed in its Relay Logs THAT IS NEXT
TO BE EXECUTED ON THE SLAVE.
What you want are two things:
- Erase Every Binary Log You Have
- Start Collecting Binary Log Entries From the Last SQL You Successfully Executed.
In your case, you must use the second set of Replication Coordinates
Relay_Master_Log_File
Exec_Master_Log_Pos
It is easy to distrust a corrupt relay log as shown in the error message. The one that hurts the most is a corrupt Master Log. You will have to jump through hoops if that is the case. On the other hand, if one of the other situations was the reason for the corrupt relay log, the simplest and most concise approach is what I stated.
To make sure, whatever is reported for Relay_Master_Log_File
, if that particular binary log still exists on the Master, perform a mysqlbinlog on it. If it dumps in its entirety without corrupt characters, go ahead and use the second set of replication coordinates.
From my same earlier post
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.48.20.253
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000254
Read_Master_Log_Pos: 858190247
Relay_Log_File: relay-bin.066069
Relay_Log_Pos: 873918
Relay_Master_Log_File: mysql-bin.000254
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 858190247
Relay_Log_Space: 873772
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: 0
1 row in set (0.00 sec)
notice that the Replication Coordinates from SHOW SLAVE STATUS\G
for what was last executed are (mysql-bin.000254,858190247)
. The CHANGE MASTER TO
command in this case would be:
CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;
Give it a Try !!!
UPDATE 2012-09-14 16:38 EDT
If you worried about the stockpiling relay logs, just throttle the relay logs. In SHOW SLAVE STATUS\G
, there is a field called Relay_Log_Space
. That gives you the sum of all relay sizes in bytes. Did you know you could put a cap on that number ?
The option is called relay_log_space_limit.
For example, if you want to cap the total number of bytes to 10G, do the following
STEP 01) Add this to /etc/my.cnf on the Slave
[mysqld]
relay_log_space_limit = 10G
STEP 02) Run service mysql restart
on the Slave
and that's it !!!
When the oldest relay has all its entries processed, it is deleted and a new relay log is created. That gets filled until all relay logs add up to 10G. That's the only way to control runaway relay log space issues.
UPDATE 2012-09-14 18:10 EDT
SUGGESTION : If you make mysqldump backups of the data on the Slave every midnight, you could set up the following to restrict having 1TB of binary logs:
STEP 01) Add this to /etc/my.cnf on the Master
[mysqld]
expire_logs_days = 14
STEP 02) Run this query on the Master
mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 14 DAY;
STEP 03) service mysql restart
on the Master
STEP 04) Add a mysqldump backup script to a crontab on the Slave
This will make the Slave more useful and would control having excess binary logs to worry about
It's OK to max out the max_allowed_packet
to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?
First you need to know what a MySQL Packet. Page 99 of the Book
explains it in paragraphs 1-3 as follows:
MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory---a cap
on the size of the packet, which this
option accomplishes.
The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().
This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.
Compare that with the MySQL Documentation on max_allowed_packet
:
The maximum size of one packet or any generated/intermediate string,
or any parameter sent by the mysql_stmt_send_long_data() C API
function. The default is 4MB as of MySQL 5.6.6, 1MB before that.
The packet message buffer is initialized to net_buffer_length bytes,
but can grow up to max_allowed_packet bytes when needed. This value by
default is small, to catch large (possibly incorrect) packets.
You must increase this value if you are using large BLOB columns or
long strings. It should be as big as the largest BLOB you want to use.
The protocol limit for max_allowed_packet is 1GB. The value should be
a multiple of 1024; nonmultiples are rounded down to the nearest
multiple.
When you change the message buffer size by changing the value of the
max_allowed_packet variable, you should also change the buffer size on
the client side if your client program permits it. On the client side,
max_allowed_packet has a default of 1GB. Some programs such as mysql
and mysqldump enable you to change the client-side value by setting
max_allowed_packet on the command line or in an option file.
Given this information, you should be glad MySQL will expand and contract the MySQL Packet as needed. Therefore, go ahead and
Master and Slave should match in terms of who they transmit data, especially BLOB data.
UPDATE 2013-07-04 07:03 EDT
From your messages concerning the relay log, it looks like you have the following
- a corrupt relay log
- a good master log
SUGGESTION
SHOW SLAVE STATUS\G
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='(Relay_Master_Log_File from SHOW SLAVE STATUS\G)',
MASTER_LOG_POS=(Exec_Master_Log_Pos from SHOW SLAVE STATUS\G);
START SLAVE;
Running CHANGE MASTER TO
clears all relay logs and starts with a new one. You will be replicating from the Last Master BinLog Event (BinLog,Position) that executed on the Slave.
Give it a Try !!!
Best Answer
Raised the issue to Oracle Support.
The issue is a bug where Oracle development team is still working on it.
Issue: MySQL Enterprise Backup tries to backup all the binlogs and during that time, internal mechanism cant take backup of binlogs and crashes the DB. Oracle development is still working on the issue.
Workaround: Avoid taking backup of binlogs and relaylogs in both incremental and full backup.