Shulou(Shulou.com)06/01 Report--

Preface

After a failover, a common problem is synchronous error reporting. Here is the recently collected error information.

Failed to delete record

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: master to delete a record, but the slave can not find an error, this situation has been deleted by the master, then the slave can directly skip.

Stop slave

Set global sql_slave_skip_counter=1

Start slave

If this happens a lot, you need to write a script specifically for this kind of error.

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

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

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

| | name | char (4) | YES | | NULL |

+-+ +

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)

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

……

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

……

1236 error, binary file missing

Due to various reasons such as erroneous deletion of binaries, the mysql-bin.000012 file of the master library is lost and the synchronization of the slave library fails.

Master_Log_File: mysql-bin.000012

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

First stop synchronization from the library

Slave stop

View the main library log file and location

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000013 | 154 | |

+-+ +

Respond to the library so that the log files and locations correspond to the master library

CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000013',MASTER_LOG_POS=154

Finally, start the slave library:

```bash

Slave start

Show slave status\ G

Master_Log_File: mysql-bin.000013

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_IO_Error:

`

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

1. 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:

2 、 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.

Common failures and solutions of MySQL master-slave replication?

1.1.1 failure 1: slave database data conflicts with master database

Show slave status; reported an error: and show slave status\ GSlave_I/O_Running:YesSlave_SQL_Running:NoSeconds_Behind_Master:NULLLast_error:Error 'Can't create database' xiaoliu'; database exists' on query. Default

Database:'xiaoliu'.query:'create database xiaoliu'

Solution 1:

Stop slave;set global sql_slave_skip_counter = 1; # move the synchronization pointer down one. If you are out of sync several times, you can repeat the operation.

Solution 2: configure it in the slave library configuration file and skip the error number that does not affect the business

Grep slave-skip / etc/my.cnfslave-skip-errors = 1032, 1062, 1007

1.1.2 causes and Solutions of Master-Slave replication delay in failed 2:MySQL

Problem 1: the master library has too many slaves, resulting in replication delay

The number of slave libraries is generally 3-5. There are too many nodes to copy, resulting in replication delay.

Problem 2: the hardware configuration of the slave library is worse than that of the master library, resulting in delay

Check the configuration of Master and Slave, which may cause replication delay due to improper configuration.

Problem 3: too many slow SQL statements

If a statement takes more than 2 seconds to execute, it needs to be optimized and adjusted.

Question 4: master-slave replication design problem

Master-slave replication is a single thread. If the write concurrency of the master library is too large, it will cause delay if it is not transferred to the slave library in time.

Later versions of MySQL can support multithreaded replication, while portals will develop their own multithreaded synchronization capabilities.

Question 5: network delay between master and slave libraries

Network devices such as master-slave library network card, network cable, connected switch and so on may become the bottleneck of replication.

Cause replication delay. In addition, master-slave replication across public networks can easily lead to master-slave replication delay.

Problem 6: high read and write pressure on the main library, resulting in replication delay

The hardware of the main library is better, and the front end of the architecture needs to add a buffer cache layer.

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.