My replication stop
Check with
show slave status\G
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.
Check error log
100420 20:28:02 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000078' position 1112628
100420 21:26:58 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000078' at position 1112628, relay log '/usr/local/mysql/relay-bin/relay.000714' position: 002082
100420 21:27:00 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 7195, event_type: 16
100420 21:27:00 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
100420 21:27:00 [ERROR] 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
check relay log '/usr/local/mysql/relay-bin/relay.000714'
log disappear. lateset one is relay.000722
Reset relay log
Check the below information with
show slave status\G
Relay_Master_Log_File: mysql-bin.000078
Exec_Master_Log_Pos: 1996668
The command should be as below
CHANGE master TO master_log_file=Relay_Master_Log_File,master_log_pos=Exec_Master_Log_Pos
So I run this
CHANGE master TO master_log_file=mysql-bin.000078,master_log_pos=1996668
The relay_log in /usr/local/mysql/relay-bin want delete, and new relay log is populate
MYSQL Replication is resume
The above solution work fine for my server
Reference :
http://sql.dzone.com/news/troubleshooting-relay-log-corr
http://www.mysqlperformanceblog.com/2008/08/02/troubleshooting-relay-log-corruption-in-mysql/
Showing posts with label MYSQL Replication. Show all posts
Showing posts with label MYSQL Replication. Show all posts
Monday, April 26, 2010
Monday, November 09, 2009
MYSQL Replication - Features and Issue
Please make sure read Replication Features and Issues before do replication
Few problem i found / concern i raise when I make replication
1 UUID in master different from slave and it do not pop any error message.
This confirm by refer to Replication and System Functions
work around solution example:
SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);
quote : The USER(), CURRENT_USER(), UUID(), VERSION(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave.
2. Will now()function replicate correctly from master to slave? or it will generate a new date when insert data into slave?
Quote:
For NOW(), the binary log includes the timestamp. This means that the value as returned by the call to this function on the master is replicated to the slave.
Need take note this statement as well
Quote:
This can lead to a possibly unexpected result when replicating between MySQL servers in different time zones. For example, suppose that the master is located in New York, the slave is located in Stockholm, and both servers are using local time.
more refer to this link
This statement make my attention :
1. Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements are not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause.
2. Replication During a Master Crash
Setting sync_binlog=1 in the master my.cnf to prevent slave not to be able to replicate when the master comes back up from crash
more detail read here
3. query with nondeterministic
It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic
Examples of nondeterministic statements include DELETE or UPDATE statements that use LIMIT with no ORDER BY clause
Reference : Replication and the Query Optimizer
Few problem i found / concern i raise when I make replication
1 UUID in master different from slave and it do not pop any error message.
This confirm by refer to Replication and System Functions
work around solution example:
SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);
quote : The USER(), CURRENT_USER(), UUID(), VERSION(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave.
2. Will now()function replicate correctly from master to slave? or it will generate a new date when insert data into slave?
Quote:
For NOW(), the binary log includes the timestamp. This means that the value as returned by the call to this function on the master is replicated to the slave.
Need take note this statement as well
Quote:
This can lead to a possibly unexpected result when replicating between MySQL servers in different time zones. For example, suppose that the master is located in New York, the slave is located in Stockholm, and both servers are using local time.
more refer to this link
This statement make my attention :
1. Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements are not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause.
2. Replication During a Master Crash
Setting sync_binlog=1 in the master my.cnf to prevent slave not to be able to replicate when the master comes back up from crash
more detail read here
3. query with nondeterministic
It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic
Examples of nondeterministic statements include DELETE or UPDATE statements that use LIMIT with no ORDER BY clause
Reference : Replication and the Query Optimizer