Mysql database slave replication exception solution

Here are two cases of slave replication exceptions. The cause is due to the cross engine room synchronization, slave of the engine room suddenly lost electricity caused.

Case one,

The big reason for this error is that read_master_log_pos:1028687822 's Pos number is not on the main library.

Processing method: Get this POS number of the previous POS number, the new start sync, here Note that if the row mode is not a problem. If it is mix or statement, you need to analyze the binlog, confirm the specific implementation of the POS number, or it may result in inconsistent data.

(andy:db:) [(none)] 11:18:39> show Slave status\g

1. Row ***************************

Slave_io_state:

master_host:192.168.11.24

Master_user:slave

master_port:3306

Connect_retry:10

master_log_file:mysql-bin.000342

read_master_log_pos:1028687822

relay_log_file:localhost-relay-bin.000767

Relay_log_pos:4

relay_master_log_file:mysql-bin.000342

Slave_io_running:no

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:1028687822

relay_log_space:120

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 while reading data from binary log: ' Client requested Master to start repli cation from position > file size; The "Mysql-bin.000342′at 1028687822", the last event read from "/mysql-bin.000342′at 4," last byte read F Rom './mysql-bin.000342′at 4. '

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

master_server_id:1024

Master_uuid:e9143523-c116-11e2-a8a1-0022195d25da

Master_info_file:/usr/local/mysql-6/data/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave has read all relay log; Waiting for the slave I/O thread to update it

master_retry_count:86400

Master_bind:

last_io_error_timestamp:140725 06:55:26

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:

Executed_gtid_set:

auto_position:0

1 row in Set (0.00 sec)

Case two,

This is because the relay-log of slave is damaged. You may consider using the Exec_master_log_pos:439512771pos number to restart the synchronization. Or use the Relay_log_recovery=on parameter to start.

(Root:bi:) [(none)] 10:56:01> show Slave status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:172.20.100.16

Master_user:slave

master_port:3306

Connect_retry:10

master_log_file:mysql-bin.000076

read_master_log_pos:474667384

relay_log_file:localhost-relay-bin.000137

relay_log_pos:212337426

relay_master_log_file:mysql-bin.000076

Slave_io_running:yes

Slave_sql_running:no

replicate_do_db:

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 (for your 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 are want to check the master's binary log or slave ' s relay log, you'll be able to know their of names by issuing ' show s Lave STATUS ' on this slave.

skip_counter:0

exec_master_log_pos:439512771

relay_log_space:474669681

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:0

Last_io_error:

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 (for your 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 are want to check the master's binary log or slave ' s relay log, you'll be able to know their of names by issuing ' show s Lave STATUS ' on this slave.

Replicate_ignore_server_ids:

master_server_id:1016

Master_uuid:7b6b6934-b981-11e3-bc82-c81f66de76ac

Master_info_file:/data/mysql/data/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

last_sql_error_timestamp:140725 10:54:50

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:

Executed_gtid_set:

auto_position:0

1 row in Set (0.00 sec)

[root@localhost data]# mysqlbinlog localhost-relay-bin.000137 > A.txt

Error:error in Log_event::read_log_event (): ' Read Error ', data_len:7736, event_type:31

Error:could not read entry at offset 215681738:error in log format or read Error.

Warning:the range of printed events ends with a row event or a table map event this does not have the STMT_END_F flag set . This might is because the last statement is not fully written to the log, or because your are using a-stop-position Top-datetime that refers to the middle of a statement. The event (s) from the partial statement have is not been written to output.

Summary: The reason for these two errors is that the computer room is out of power, that is, there is no crash safe.

How to get slave cras safe?

I can refer to another article

"mysql-5.6 Slave support Crash-safe"

Why didn't I open crash-safe? Because I slave is mariadb-10.0, not official, and I opened the multi-source.