What are the common situations of MYSQL synchronous error fault handling? 01/22 Update SLTechnology News&Howtos

What are the common situations of MYSQL synchronous error fault handling?

2025-01-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces the common cases of MYSQL synchronous error fault handling, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to know about it.

Common mistakes

The three most common situations

The first: delete a record on master, but not find it on slave. Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1

Can't find record in 't1'

Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND

The event's master log mysql-bin.000006, end_log_pos 254

Second: the primary key is repeated. The record already exists in slave, and the same record is inserted on master. Last_SQL_Error: Could not execute Write_rows event on table hcy.t1

Duplicate entry'2' for key 'PRIMARY'

Error_code: 1062

Handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

Third: update a record on master, but cannot find it on slave and lose data. Last_SQL_Error: Could not execute Update_rows event on table hcy.t1

Can't find record in 't1'

Error_code: 1032

Handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263

Asynchronous semi-synchronous difference

Asynchronous replication

To put it simply, the master sends the binlog, no matter whether the slave is received or executed, this action ends.

Semi-synchronous replication

To put it simply, master sends the binlog, and slave confirms that it has been received, but whether it is finished or not, give master a signal that I have received, and this action is over. (code written by Google, official application on 5.5. )

Asynchronous disadvantage

When the write operation on master is busy, for example, the current POS point is 10, and the IO_ thread on slave receives 3. When the master goes down, it will result in data loss due to a difference of 7 points not transmitted to the slave.

Special circumstances

The relay log relay-bin of slave is corrupt. Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log

Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number

It's not a binary log file that can be used by this version of MySQL

In this case, the SLAVE is down or shut down illegally, such as power failure, motherboard burning, etc., resulting in damage to the relay log and synchronous shutdown.

Human error should be cautious: duplicate server-id exists in multiple slave

In this case, synchronization will be delayed all the time, and the above two lines of information will always appear in the error error log. The solution is to change the server-id to be inconsistent.

Slave: received end packet from server, apparent master shutdown:

Slave Slave O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000012' at postion 106

Problem handling

Delete failed

Delete a record on master, but cannot find it on slave.

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1

Can't find record in 't1'

Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND

The event's master log mysql-bin.000006, end_log_pos 254

Solution:

Because the master wants to delete a record, and the error cannot be found on the slave, the master deletes it, so the slave can skip it directly. Available commands:

Stop slave

Set global sql_slave_skip_counter=1

Start slave

If there are many such cases, you can use a script I wrote, skip_error_replcation.sh, which skips 10 errors by default (jump only for this situation, output error results in other cases, waiting for processing), this script is written with shell with reference to the mk-slave-restart principle of the maakit toolkit, and defines some of its own things functionally, not all errors are skipped. )

Primary key repetition

The record already exists in slave, and the same record is inserted on master.

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1

Duplicate entry'2' for key 'PRIMARY'

Error_code: 1062

Handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

Solution:

Use desc hcy.t1; on slave to first look at the following table structure:

Mysql > desc hcy.t1

+-+ +

+-+ +

| | id | int (11) | NO | PRI | 0 | |

+-+ +

Delete duplicate primary key

Mysql > delete from T1 where id=2

Query OK, 1 row affected (0.00 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

……

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

……

Mysql > select * from T1 where id=2

Reconfirm it on master and on slave.

Update lost

Update a record on master, but cannot find it on slave, and data is lost.

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1

Can't find record in 't1'

Error_code: 1032

Handler error HA_ERR_KEY_NOT_FOUND

The event's master log mysql-bin.000010, end_log_pos 794

Solution:

On master, use mysqlbinlog to analyze what the wrong binlog log is doing.

/ usr/local/mysql/bin/mysqlbinlog-- no-defaults-v-v-- base64-output=DECODE-ROWS mysql-bin.000010 | grep-A '10' 794

# 120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F

# UPDATE hcy.t1

# WHERE

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roombbc' / * STRING (4) meta=65028 nullable=1 is_null=0 * /

# SET

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2BTV'/ * STRING (4) meta=65028 nullable=1 is_null=0 * /

# at 794

# 120302 12:08:36 server id 22 end_log_pos 821 Xid = 60

Com _ MIT _ blank /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

On slave, look for the updated record, which should not exist.

Mysql > select * from T1 where id=2

Empty set (0.00 sec)

Then go to master to check.

Mysql > select * from T1 where id=2

+-+ +

| | id | name |

+-+ +

| | 2 | BTV |

+-+ +

1 row in set (0.00 sec)

Fill in the missing data on slave and skip the error report.

Mysql > insert into T1 values (2meme BTV')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from T1 where id=2

+-+ +

| | id | name |

+-+ +

| | 2 | BTV |

+-+ +

1 row in set (0.00 sec)

Mysql > stop slave; set global sql_slave_skip_counter=1;start slave

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

……

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

……

The relay log is corrupt

The relay log relay-bin of slave is corrupt.

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log

Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number

It's not a binary log file that can be used by this version of MySQL

Manual repair

Solution: find the synchronized binlog and POS points, and then re-synchronize so that you can have a new relay day value.

Example:

Mysql > show slave status\ G

* * 1. Row *

Master_Log_File: mysql-bin.000010

Read_Master_Log_Pos: 1191

Relay_Log_File: vm02-relay-bin.000005

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000010

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

Last_Error: Error initializing relay log position: I/O error reading the header from the binary log

Skip_Counter: 1

Exec_Master_Log_Pos: 821

Slave_IO_Running: receives the binlog information of master

Master_Log_File

Read_Master_Log_Pos

Slave_SQL_Running: perform write operation

Relay_Master_Log_File

Exec_Master_Log_Pos

To execute the written binlog and POS points shall prevail.

Relay_Master_Log_File: mysql-bin.000010

Exec_Master_Log_Pos: 821

Mysql > stop slave

Query OK, 0 rows affected (0.01 sec)

Mysql > CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=821

Query OK, 0 rows affected (0.01 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.22

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000010

Read_Master_Log_Pos: 1191

Relay_Log_File: vm02-relay-bin.000002

Relay_Log_Pos: 623

Relay_Master_Log_File: mysql-bin.000010

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

Relay_Log_Space: 778

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Ibbackup

All kinds of big tricks have been used, but slave data has been lost too much. Ibbackup (needs silver) it's your turn to make your debut.

Ibbackup hot backup tool is paid for. Xtrabackup is free and functionally the same.

The table is not locked during the Ibbackup backup, a transaction is opened during the backup (equivalent to making a snapshot), and then a point is recorded, and then the data changes are saved in the ibbackup_logfile file, and the ibbackup_logfile-changed data is then written to the ibdata during recovery.

Ibbackup only backs up data (ibdata, .ibd), and table structure .frm is not backed up.

Here is a demonstration example:

Backup: ibbackup / bak/etc/my_local.cnf / bak/etc/my_bak.cnf

Restore: ibbackup-- apply-log / bak/etc/my_bak.cnf

[root@vm01 etc] # more my_local.cnf

Datadir = / usr/local/mysql/data

Innodb_data_home_dir = / usr/local/mysql/data

Innodb_data_file_path = ibdata1:10M:autoextend

Innodb_log_group_home_dir = / usr/local/mysql/data

Innodb_buffer_pool_size = 100m

Innodb_log_file_size = 5m

Innodb_log_files_in_group=2

[root@vm01 etc] # ibbackup / bak/etc/my_local.cnf / bak/etc/my_bak.cnf

License A21488 is granted to vm01 (chunyang_he@126.com)

(--apply-log works in any computer regardless of the hostname)

Licensed for use in a computer whose hostname is' vm01'

Expires 2012-5-1 (year-month-day) at 00:00

See http://www.innodb.com for further information

Type ibbackup-license for detailed license terms,-help for help

Contents of / bak/etc/my_local.cnf:

Innodb_data_home_dir got value / usr/local/mysql/data

Innodb_data_file_path got value ibdata1:10M:autoextend

Datadir got value / usr/local/mysql/data

Innodb_log_group_home_dir got value / usr/local/mysql/data

Innodb_log_files_in_group got value 2

Innodb_log_file_size got value 5242880

Contents of / bak/etc/my_bak.cnf:

Innodb_data_home_dir got value / bak/data

Innodb_data_file_path got value ibdata1:10M:autoextend

Datadir got value / bak/data

Innodb_log_group_home_dir got value / bak/data

Innodb_log_files_in_group got value 2

Innodb_log_file_size got value 5242880

Ibbackup: Found checkpoint at lsn 0 1636898

Ibbackup: Starting log scan from lsn 0 1636864

120302 16:47:43 ibbackup: Copying log...

120302 16:47:43 ibbackup: Log copied, lsn 0 1636898

Ibbackup: We wait 1 second before starting copying the data files...

120302 16:47:44 ibbackup: Copying / usr/local/mysql/data/ibdata1

Ibbackup: A copied database page was modified at 0 1636898

Ibbackup: Scanned log up to lsn 0 1636898

Ibbackup: Was able to parse the log up to lsn 0 1636898

Ibbackup: Maximum page number for a log record 0

120302 16:47:46 ibbackup: Full backup completed!

[root@vm01 etc] #

[root@vm01 etc] # cd / bak/data/

[root@vm01 data] # ls

Ibbackup_logfile ibdata1

[root@vm01 data] # ibbackup-- apply-log / bak/etc/my_bak.cnf

License A21488 is granted to vm01 (chunyang_he@126.com)

(--apply-log works in any computer regardless of the hostname)

Licensed for use in a computer whose hostname is' vm01'

Expires 2012-5-1 (year-month-day) at 00:00

See http://www.innodb.com for further information

Type ibbackup-license for detailed license terms,-help for help

Contents of / bak/etc/my_bak.cnf:

Innodb_data_home_dir got value / bak/data

Innodb_data_file_path got value ibdata1:10M:autoextend

Datadir got value / bak/data

Innodb_log_group_home_dir got value / bak/data

Innodb_log_files_in_group got value 2

Innodb_log_file_size got value 5242880

120302 16:48:38 ibbackup: ibbackup_logfile's creation parameters:

Ibbackup: start lsn 0 1636864, end lsn 0 1636898

Ibbackup: start checkpoint 0 1636898

InnoDB: Doing recovery: scanned up to log sequence number 0 1636898

InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 0 12 3 4 5 6 7 8 9 10 11 12 13 14 15. 99

Setting log file size to 0 5242880

Ibbackup: We were able to parse ibbackup_logfile up to

Ibbackup: lsn 0 1636898

Ibbackup: Last MySQL binlog file position 0 1191, file name. / mysql-bin.000010

Ibbackup: The first data file is'/ bak/data/ibdata1'

Ibbackup: and the new created log files are at'/ bak/data/'

120302 16:48:38 ibbackup: Full backup prepared for recovery successfully!

[root@vm01 data] # ls

Ibbackup_logfile ibdata1 ib_logfile0 ib_logfile1

Copy ibdata1 ib_logfile0 ib_logfile1 to slave, copy .frm, start MYSQL, do synchronization, and that point is output above:

Ibbackup: Last MySQL binlog file position 0 1191, file name. / mysql-bin.000010

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=1191

Maatkit toolkit

Http://www.maatkit.org/

? Brief introduction

Maatkit is an open source toolkit that helps with the day-to-day management of mysql. At present, it has been acquired and maintained by Percona. Where:

Mk-table-checksum is used to detect whether the table structure and data on master and slave are consistent.

Mk-table-sync is repaired when master-slave data inconsistencies occur.

These two toolkits have no practical experience in the current network, here is only new technology discussion and academic exchange, the following shows how to use.

Http://www.actionsky.com/products/mysql-others/maatkit.jsp

[root@vm02] # mk-table-checksum hobby vm01 hcy-t 1 pendant 123456 hobby vm02

Cannot connect to MySQL because the Perl DBI module is not installed or not found.

Run 'perl-MDBI' to see the directories that Perl searches for DBI.

If DBI is not installed, try:

Debian/Ubuntu apt-get install libdbi-perl

RHEL/CentOS yum install perl-DBI

OpenSolaris pgk install pkg:/SUNWpmdbi

Prompt that the perl-DBI module is missing, so yum install perl-DBI directly.

[root@vm02 bin] # mk-table-checksum hobby vm01 hcy-t 1 pendant 123456 hobby vm02

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG

Hcy t1 0 vm02 InnoDB NULL 1957752020 0 0 NULL NULL

Hcy t1 0 vm01 InnoDB NULL 1957752020 0 0 NULL NULL

If the table data is inconsistent, the value of CHECKSUM is not equal.

Explain the meaning of the output:

DATABASE: database name

TABLE: table name

Approximate value at CHUNK:checksum

The address of HOST:MYSQL

ENGINE: table engine

COUNT: number of rows in the table

CHECKSUM: check valu

TIME: time spent

WAIT: wait time

STAT:MASTER_POS_WAIT () returns a value

Delay time of LAG:slave

If you want to filter out which tables are not equal, you can use mk-checksum-filter as a tool, just add a pipe character at the end.

[root@vm02 ~] # mk-table-checksum hype vm01 hcy mk-checksum-filter 123456 html vm02

Hcy t2 0 vm01 InnoDB NULL 1957752020 0 0 NULL NULL

Hcy t2 0 vm02 InnoDB NULL 1068689114 0 0 NULL NULL

If you know which tables are inconsistent, you can use mk-table-sync as a tool to handle them.

Note: the table is locked when mk-table-checksum is executed, and the size of the table depends on the speed of execution.

T2 table data on MASTER: T2 table data on SLAVE:

Mysql > select * from T2; mysql > select * from T2

+-+

| | id | name | | id | name | |

+-+

| | 1 | a | | 1 | a |

| | 2 | b | | 2 | b | |

| | 3 | ss | | 3 | ss |

| | 4 | asd | | 4 | asd |

| | 5 | ss | +-+ |

+-+-+ 4 rows in set (0.00 sec)

5 rows in set (0.00 sec)

Mysql >\! Hostname

Mysql >\! Hostname; vm02

Vm01

[root@vm02 ~] # mk-table-sync-- execute-- print-- no-check-slave-- transaction-- databases hcy hobby vm01 reigning upright adminery pendant 123456 hobbies vm02 recoverable adminery pendant 123456

INSERT INTO `hcy`.`t2` (`id`, `name`) VALUES ('54th,' ss') / * maatkit src_db:hcy src_tbl:t2 src_dsn:h=vm01,p=...,u=admin dst_db:hcy dst_tbl:t2

Dst_dsn:h=vm02,p=...,u=admin lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3246 user:root host:vm02*/

It works as follows: first check whether the tables of the master and slave libraries are the same, and if they are different, delete, update, insert and other operations are performed to make them consistent. The size of the table determines the speed of execution.

If C is specified, C is not used. Instead, lock

And unlock are implemented by beginning and committing transactions.

The exception is if L is 3.

If C is specified, then C is used for any

Value of L. See L.

When enabled, either explicitly or implicitly, the transaction isolation level

Is set C and transactions are started C

MySQL replication monitoring

? Original: http://www.tianfeiyu.com/?p=2062

Common error types in MySQL

1005: failed to create table

1006: failed to create database

1007: database already exists, failed to create database

1008: database does not exist, failed to delete database

1009: failure to delete database due to failure to delete database file

1010: failure to delete database due to failure to delete data directory

1011: failed to delete database file

1012: unable to read records in system table

1020: the record has been modified by another user

1021: there is not enough space left on the hard disk. Please increase the available space on the hard disk.

1022: duplicate keyword, failed to change record

1023: error occurred during shutdown

1024: read file error

1025: error occurred while changing name

1026: file write error

1032: record does not exist

1036: the datasheet is read-only and cannot be modified

1037: the system is out of memory, please restart the database or restart the server

1038: insufficient memory for sorting, please increase the sort buffer

1040: the maximum number of connections to the database has been reached. Please increase the number of available connections to the database.

1041: out of system memory

1042: invalid hostname

1043: invalid connection

1044: the current user does not have access to the database

1045: unable to connect to the database, wrong username or password

1048: field cannot be empty

1049: database does not exist

1050: datasheet already exists

1051: datasheet does not exist

1054: field does not exist

1065: invalid SQL statement, SQL statement is empty

1081: Socket connection cannot be established

1114: the datasheet is full and cannot hold any records

1116: too many data tables are open

1129: there is an exception in the database. Restart the database.

1130: failed to connect to the database, do not have permission to connect to the database

1133: database user does not exist

1141: the current user does not have access to the database

1142: the current user does not have access to the data table

1143: the current user does not have access to fields in the data table

1146: datasheet does not exist

1147: no user access to the datasheet is defined

Syntax error of 1149:SQL statement

1158: network error, read error, please check the network connection status

1159: network error, read timeout, please check the network connection status

1160: network error, write error, please check the network connection status

1161: network error, write timeout, please check the network connection status

1062: duplicate field value, failed to enter the database

1169: duplicate field value, failed to update record

1177: failed to open datasheet

1180: failed to commit transaction

1181: failed to roll back the transaction

1203: the maximum number of connections between the current user and the database has reached the database. Please increase the number of available database connections or restart the database.

1205: lock timeout

1211: the current user does not have permission to create a user

1216: failed to check foreign key constraints and update child table records

1217: foreign key constraint check failed, delete or modify master table record failed

1226: the current user has used more resources than allowed, please restart the database or restart the server

1227: insufficient permissions, you are not authorized to perform this operation

The 1235:MySQL version is too low to have this feature.

Copy monitoring script

Refer to the original text for revision.

? Original script

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

fifty-nine

sixty

sixty-one

sixty-two

sixty-three

sixty-four

sixty-five

sixty-six

sixty-seven

sixty-eight

sixty-nine

seventy

seventy-one

seventy-two

seventy-three

seventy-four

seventy-five

seventy-six

seventy-seven

seventy-eight

seventy-nine

eighty

eighty-one

eighty-two

#! / bin/bash

# check_mysql_slave_replication_status

Parasum=2

Help_msg () {

Cat $SlaveStatusFile

Echo "" > > $SlaveStatusFile

# get slave status

${MYSQL_CMD}-e "show slave status\ G" > > $SlaveStatusFile # gets the status of the salve process

# get io_thread_status,sql_thread_status,last_errno gets the following status values

IOStatus=$ (cat $SlaveStatusFile | grep Slave_IO_Running | awk'{print $2}')

SQLStatus=$ (cat $SlaveStatusFile | grep Slave_SQL_Running | awk'{print $2}')

Errno=$ (cat $SlaveStatusFile | grep Last_Errno | awk'{print $2}')

Behind=$ (cat $SlaveStatusFile | grep Seconds_Behind_Master | awk'{print $2}')

Echo "" > > $SlaveStatusFile

If ["$IOStatus" = = "No"] | | ["$SQLStatus" = = "No"]; then # determines the error type

If ["$Errno"-eq 0]; then # may be that the salve thread did not start

$MYSQL_CMD-e "start slave io_thread;start slave sql_thread;"

Echo "Cause slave threads doesnot's running,trying start slsave io_thread;start slave sql_thread;" > > $SlaveStatusFile

MailTitle= "[Warning] Slave threads stoped on $HOST_IP $HOST_PORT"

$MYSQL_CMD-e "stop slave;set global sql_slave_skip_counter=1;start slave;"

Echo "Cause slave replication catch errors,trying skip counter and restart slave;stop slave; set global sql_slave_skip_counter=1;slave start;" > > $SlaveStatusFile

MailTitle= "[Warning] Slave error on $HOST_IP $HOST_PORT! ErrNum: $Errno"

Else

Echo "Slave $HOST_IP $HOST_PORT is down!" > > $SlaveStatusFile

MailTitle= "[ERROR] Slave replication is down on $HOST_IP $HOST_PORT! ErrNum:$Errno"

If [- n "$Behind"]; then

Behind=0

Echo "$Behind" > > $SlaveStatusFile

# delay behind master determines delay time

If [$Behind-gt 300]; then

Echo `date + "% Y-%m%d% H:%M:%S" `"slave is behind master $Bebind seconds!" > > $SlaveStatusFile

MailTitle= "[Warning] Slave delay $Behind seconds,from $HOST_IP $HOST_PORT"

If [- n "$MailTitle"]; then # send an email if there is an error or the delay is greater than 300s

Cat ${SlaveStatusFile} | / bin/mail-s "$MailTitle" $Mail_Address_MysqlStatus

# del tmpfile:SlaveStatusFile

> $SlaveStatusFile

? Modified script? Only a simple arrangement was made, and the judgment that Behind was NULL was revised, but none of them were tested.

? It should be considered to increase:? Judgment of the result of repair execution; cycle repair, detection and re-repair of multiple errors?

? Cancel the temporary SlaveStatusFile file.

Errno and Behind alarms are emailed respectively, and the alarm body adds the original show slave result text.

? Add PATH to add to crontab.

? Consider periodic execution in crontab (locking to avoid execution conflicts, execution cycle selection)

? Increase the execution log?

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

fifty-nine

sixty

sixty-one

sixty-two

sixty-three

sixty-four

sixty-five

sixty-six

sixty-seven

sixty-eight

sixty-nine

seventy

seventy-one

seventy-two

seventy-three

seventy-four

seventy-five

seventy-six

#! / bin/sh

# check_mysql_slave_replication_status

# reference: http://www.tianfeiyu.com/?p=2062

Usage () {

Echo Usage:

Echo "$0 HOST PORT USER PASS"

}

[- z "$1"-o-z "$2"-o-z "$3"-o-z "$4"] & & Usage & & exit 1

HOST=$1

PORT=$2

USER=$3

PASS=$4

MYSQL_CMD= "mysql-h$HOST-P$PORT-u$USER-p$PASS"

MailTitle= "" # email subject

Mail_Address_MysqlStatus= "root@localhost.localdomain" # recipient mailbox

Time1=$ (date + "% Y%m%d%H%M%S")

Time2=$ (date + "Y-%m-%d H:%M:%S")

SlaveStatusFile=/tmp/salve_status_$ {HOST_PORT}. ${time1} # the file where the message content is located

Echo "- Begin at:" $time2 > $SlaveStatusFile

Echo "" > > $SlaveStatusFile

# get slave status

${MYSQL_CMD}-e "show slave status\ G" > > $SlaveStatusFile # gets the status of the salve process

# get io_thread_status,sql_thread_status,last_errno gets the following status values

IOStatus=$ (cat $SlaveStatusFile | grep Slave_IO_Running | awk'{print $2}')

SQLStatus=$ (cat $SlaveStatusFile | grep Slave_SQL_Running | awk'{print $2}')

Errno=$ (cat $SlaveStatusFile | grep Last_Errno | awk'{print $2}')

Behind=$ (cat $SlaveStatusFile | grep Seconds_Behind_Master | awk'{print $2}')

Echo "" > > $SlaveStatusFile

If ["$IOStatus" = "No"-o "$SQLStatus" = "No"]; then

Case "$Errno" in

# maybe slave is not started

$MYSQL_CMD-e "start slave io_thread;start slave sql_thread;"

Echo "Cause slave threads doesnot's running,trying start slsave io_thread;start slave sql_thread;" > > $SlaveStatusFile

1007 | 1053 | 1062 | 1213 | 1032 | 1158 | 1159 | 1008)

# ignore these errors

$MYSQL_CMD-e "stop slave;set global sql_slave_skip_counter=1;start slave;"

Echo "Cause slave replication catch errors,trying skip counter and restart slave;stop slave; set global sql_slave_skip_counter=1;slave start;" > > $SlaveStatusFile

MailTitle= "[Warning] Slave error on $HOST:$PORT! ErrNum: $Errno"

Echo "Slave $HOST:$PORT is down!" > > $SlaveStatusFile

MailTitle= "[ERROR] Slave replication is down on $HOST:$PORT! Errno:$Errno"

Esac

If ["$Behind" = "NULL"-o-z "$Behind"]; then

Behind=0

Echo "Behind:$Behind" > > $SlaveStatusFile

# delay behind master determines delay time

If [$Behind-gt 300]; then

Echo `date + "% Y-%m%d% H:%M:%S" `"slave is behind master $Bebind seconds!" > > $SlaveStatusFile

MailTitle= "[Warning] Slave delay $Behind seconds,from $HOST $PORT"

If [- n "$MailTitle"]; then # send an email if there is an error or the delay is greater than 300s

Cat ${SlaveStatusFile} | / bin/mail-s "$MailTitle" $Mail_Address_MysqlStatus

# del tmpfile:SlaveStatusFile

> $SlaveStatusFile

Thank you for reading this article carefully. I hope the article "what are the common situations of MYSQL synchronous error reporting and troubleshooting" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.