am3n portfolio

Since I sometimes get replication problem, so I make several script to check replication automatically and report to me:

To check replication status when I in the console and playing with replication configuration or when I have nothing to do yet I want to see a black screen

monitorreplication.sh

#!/bin/bash
while [ 1 ]
do
        # Your code goes here
        echo ""
        date
        echo "=============================="
        mysql -u backup -pmysqlback -e "show slave status\G;"

        # Modify sleep time (in seconds) as needed below
        sleep 10
done

Another scripts to send replication status via email
(more…)

Okay it’s the second time this problem arise, so I’ll put a blog here just incase it happen again in near future. The replication on slave server just stop working with error

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.

Googling up and check several paramters:

on master server:

mysql> show master status\G;
*************************** 1. row ***************************
File: replication.011
Position: 130848689
Binlog_do_db:
Binlog_ignore_db:
1 row in set (0.00 sec)

on slave server:

mysql> show slave status\G;
*************************** 1. row ***************************
Master_Host: 10.10.105.11
Master_User: repl
Master_Port: 3306
Connect_retry: 60
Master_Log_File: replication.011
Read_Master_Log_Pos: 91831752
Relay_Log_File: dragon-relay-bin.183
Relay_Log_Pos: 3551175
Relay_Master_Log_File: replication.011
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 0
Last_error: 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: 74359528
Relay_log_space: 21023399

[root@dragon mysql]# tail -f dragon.rpxholding.com.err
081129  0:54:24 Error in Log_event::read_log_event(): 'read error',
 data_len: 148, event_type: 2 081129  0:54:24 Error reading relay
log event: slave SQL thread aborted because of I/O error 081129 0:54:24
Slave: 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. Error_code: 0

Slave IO and SQL are not running. What caused the problem  ? binlog file at master is not corrupted. That’s great and very good news. But relay log file at slave is corrupted. That’s what make replication can’t read it. Fixing the problem:

(more…)

This is happen long time ago when Mysql Replication stop due to duplicate error, Kumay has the log. But I don’t want to do a manual labor. So to skip a duplicate error on A MySQL Replication Server (SLAVE) automatically. Added this line on /etc/my.cnf

Note: Make sure you know what cause a duplicate error and does it really not important so that you can skip inserting that query.
(more…)