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_FileExec_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
Step 01
On ServerB, run the following commands
STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
FLUSH TABLES;
Step 02
On ServerC, run SHOW SLAVE STATUS\G
Repeat running SHOW SLAVE STATUS\G until Seconds_Behind_Master is 0
Then, run SET GLOBAL innodb_max_dirty_pages_pct = 0;
Step 03
On ServerB, run SHOW SLAVE STATUS\G
For the sake of example, let say SHOW SLAVE STATUS\G looks like this:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.68.253
Master_User: replusername
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.003202
Read_Master_Log_Pos: 577991837
Relay_Log_File: relay-bin.010449
Relay_Log_Pos: 306229695
Relay_Master_Log_File: mysql-bin.003202
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: 577991837
Relay_Log_Space: 306229695
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
Please note the following:
Master_Host (10.64.68.253)Master_User (replusername)Relay_Master_Log_File (mysql-bin.003202)Exec_Master_Log_Pos (577991837)
Step 04
On ServerC, run the following:
STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages = 0;
FLUSH TABLES;
CHANGE MASTER TO
MASTER_HOST='10.64.68.253',
MASTER_PORT=3306,
MASTER_USER='replusername',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.003202',
MASTER_LOG_POS=577991837;
START SLAVE;
Step 05
On ServerC, run SHOW SLAVE STATUS\G
If Seconds_Behind_Master is a Number, CONGRATULATIONS !!!
Step 06
On ServerB, run SET GLOBAL innodb_max_dirty_pages_pct = 90;
On ServerC, run SET GLOBAL innodb_max_dirty_pages_pct = 90;
Give it a Try !!!
CAVEAT
If the majority of your data is MyISAM, ignore all commands that change innodb_max_dirty_pages_pct.
Best Answer
Oracle say: This is most probably due to table corruption. See the error number is 0, which indicates success, not actual error. So, this means that read from file was successful and OS returned 0, but actual read data length is different from expected data length. This is unlikely to be a problem on MySQL, but most likely to be a problem on OS including file system.
InnoDB writes data to data file per page. So, size of data file is always multiple of page size. In general, writes against a file is atomic. So, data file will never be non-multiple of page size, if OS works properly. If it's not multiple of page size, InnoDB will read incomplete page at the end of data file, then it results in this error.
Recommended solutions are: Take following actions to mend or prevent this problem:
Run fsck and check if the file system is flawless. Replace suspicious disk. Ensure that the filesystem is transactional. (such as NTFS and ext4) Check if journaling setting of filesystem is fine. Change block size of filesystem to same as InnoDB page size (16KB by default) Upgrade OS to the latest version.