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

As many companies do not have their own DBA staff to save costs, most of them are developers or operation and maintenance personnel to operate the database, but the database is the top priority. After the company reaches a certain scale, the database has an accidental accident, which is likely to make the company return to the pre-liberation period. Therefore, when the company is small, it should have its own database system and perfect database architecture. Operators should focus on optimizing and improving performance, rather than to modify the overall structure of the database.

Below I give you a summary of three database accidents and solutions for emergency use by operators. Please make an analogy reference, the types of errors are ever-changing, and the solution is immutable.

Error:

1. InnoDB: Error: Table "mysql". "innodb_table_stats" not found. problem

2. Error 'Cannot add or update a child row: a foreign key constraint fails failure

3. SQL_ERROR 1032 solution

The problem is clear, so let's solve it one by one:

1. InnoDB: Error: Table "mysql". "innodb_table_stats" not found. problem

Log the following warning message in the background of MySQL 5.6.30:

2016-05-27 12:25:27 7fabf86f7700 InnoDB: Error: Table "mysql". "innodb_table_stats" not found.

2016-05-27 12:25:27 7fabf86f7700 InnoDB: Error: Fetch of persistent statistics requested for table "hj_web". "wechat_res" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

2016-05-27 14:03:52 28585 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

2016-05-27 14:03:52 28585 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

2016-05-27 14:03:52 28585 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.~~

These tables are really new in mysql5.6.

Innodb_index_stats

Innodb_tables_stats

Slave_master_info

Slave_relay_log_info

Slave_worker_info

Solution:

Log in to the database, enter the mysql library, and execute the following SQL to delete 5 tables

Remember, it has to be drop table if exists.

Mysql > use mysql

Mysql > drop table if exists innodb_index_stats

Mysql > drop table if exists innodb_table_stats

Mysql > drop table if exists slave_master_info

Mysql > drop table if exists slave_relay_log_info

Mysql > drop table if exists slave_worker_info

After execution, you can check with show tables to see if the data in the table has been reduced compared with that before deletion. If so, you have succeeded!

After the previous step is completed, stop the database, go to the directory where the database data file is located, and delete the idb file corresponding to the above five tables, as shown below:

# / etc/init.d/mysqld stop

# cd / data/mysql/data/mysql/

# ls-l.ibd

-rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_index_stats.ibd

-rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_table_stats.ibd

-rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_master_info.ibd

-rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_relay_log_info.ibd

-rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_worker_info.ibd

# / bin/rm-rf .ibd

Restart the database, go to the mysql library, and rebuild the deleted table structure above:

# / etc/init.d/mysqld start

Mysql > use mysql

Mysql > source / data/mysql/share/mysql_system_tables.sql (this mysql_system_tables.sql is backed up from another mysql database as follows: mysqldump-u account-p password mysql > mysql_system_tables.sql)

Mysql > show tables

+-+

| | Tables_in_mysql |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | general_log |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | innodb_index_stats |

| | innodb_table_stats |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | proxies_priv |

| | servers |

| | slave_master_info |

| | slave_relay_log_info |

| | slave_worker_info |

| | slow_log |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

28 rows in set (0.00 sec)

Mysql > desc innodb_table_stats

+-+

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

+-+

| | database_name | varchar (64) | NO | PRI | NULL |

| | table_name | varchar (64) | NO | PRI | NULL |

| | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |

| | n_rows | bigint (20) unsigned | NO | | NULL |

| | clustered_index_size | bigint (20) unsigned | NO | | NULL |

| | sum_of_other_index_sizes | bigint (20) unsigned | NO | | NULL |

+-+

6 rows in set (0.00 sec)

Mysql > desc slave_master_info

+-+ +

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

+-+ +

| | Number_of_lines | int (10) unsigned | NO | | NULL |

| | Master_log_name | text | NO | | NULL |

| | Master_log_pos | bigint (20) unsigned | NO | | NULL |

| | Host | char (64) | NO | PRI | |

| | User_name | text | YES | | NULL |

| | User_password | text | YES | | NULL |

| | Port | int (10) unsigned | NO | PRI | NULL |

| | Connect_retry | int (10) unsigned | NO | | NULL |

| | Enabled_ssl | tinyint (1) | NO | | NULL |

| | Ssl_ca | text | YES | | NULL |

| | Ssl_capath | text | YES | | NULL |

| | Ssl_cert | text | YES | | NULL |

| | Ssl_cipher | text | YES | | NULL |

| | Ssl_key | text | YES | | NULL |

| | Ssl_verify_server_cert | tinyint (1) | NO | | NULL |

| | Heartbeat | float | NO | | NULL |

| | Bind | text | YES | | NULL |

| | Ignored_server_ids | text | YES | | NULL |

| | Uuid | text | YES | | NULL |

| | Retry_count | bigint (20) unsigned | NO | | NULL |

| | Ssl_crl | text | YES | | NULL |

| | Ssl_crlpath | text | YES | | NULL |

| | Enabled_auto_position | tinyint (1) | NO | | NULL |

+-+ +

23 rows in set (0.00 sec)

Explain that the tables are all normal. If you look at the mysql error log again, you will find that there is no error log about these five tables.

2. Error 'Cannot add or update a child row: a foreign key constraint fails troubleshooting

Early in the morning, a slave library suddenly reported a failure: SQL thread interrupt!

View the slave library status:

Mysql > show slave status\ G

Slave_IO_State: Waiting for master to send event

Master_Log_File: mysql-bin.026023

Read_Master_Log_Pos: 230415889

Relay_Log_File: relay-bin.058946

Relay_Log_Pos: 54632056

Relay_Master_Log_File: mysql-bin.026002

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1452

Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (zabbix.trigger_discovery, CONSTRAINT c_trigger_discovery_2 FOREIGN KEY (parent_triggerid) REFERENCES triggers (triggerid) ON DELETE CASCADE)' on query. Default database: 'zabbix'. Query: 'insert into trigger_discovery (triggerdiscoveryid,triggerid,parent_triggerid,name) values (1677 inodes is less than 26249 on volume 22532 on volume {# FSNAME}'), (1678 26250 on volume 22532 on volume {# FSNAME}'), (1679 26251 on volume 2253 on volume {# FSNAME}')'

Exec_Master_Log_Pos: 54631910

Focus on error messages, positioning problems, the problem is: Cannot add or update a child row:a foreign key constraint fails, the foreign key involved is: c_trigger_discovery_2

What is the definition of this foreign key?

The error message is also listed:

Trigger_discovery, CONSTRAINTc_trigger_discovery_2FOREIGN KEY (parent_triggerid) REFERENCEStriggers (roomerid`) ON DELETE CASCADE

I see. There is a foreign key association between column parent_triggerid in table trigger_discovery and column triggerid in table triggers. Now there is a problem with data insertion.

Then why is there a problem?

Continue to read the wrong newspaper, the error begins here:

Insert into trigger_discovery (triggerdiscoveryid,triggerid,parent_triggerid,name) values (1677 FSNAME 26249 22532 on volume free inodes is less than 20% {# FSNAME}')

The value of the column parent_triggerid corresponding to the above foreign key is 22532. Is there a problem with this value in the table triggers?

Let's go to the table triggers to check:

Slave library

Mysql > select * from triggers where triggerid=22532

Empty set (0.00 sec)

Main library

Mysql > select * from triggers where triggerid=22532

+- -- +

| | triggerid | expression | description | url | status | value | priority | lastchange | comments | error | templateid | type | value_flags | flags |

+- -- +

| | 22532 | {23251} SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; # Skip a transaction |

Mysql > start slave

Next, there is the problem of inconsistent master-slave data. You can use pt-table-checksum to check the inconsistent data, and then synchronize it. The specific steps are as follows:

Execute in the main library:

Mysql > GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON. TO 'USER'@'MASTER_HOST' identified by' PASSWORD'

Note: these permissions are necessary to create a user, otherwise an error will be reported.

Shell >. / pt-table-checksum-host='master_host'-user='user'-password='password'-port='port'-databases=zabbix-ignore-tables=ignore_table-recursion-method=processlist

Note: (1) because there are too many tables involved, it is found that many tables have foreign key association, which is complicated, and because it is a monitoring table, it does not matter if some tables are lost, so the larger tables without foreign key association are excluded with the ignore-tables option, and other tables are compared. If the tables are relatively few, specify directly-- TABLES.

(2) if recursion-method is not set, it will report an error: Diffs cannot be detected because no slaves were found. There are four parameters: processlist/hosts/dsn/no, which is used to determine whether to find slave is show full processlist, show slave hosts or directly give slave information. The specific usage is detailed in another essay pt-table-checksum introduction.

Shell >. / pt-table-sync-- print-- replicate=percona.checksums hobbies mastery hostgrad recursion-method=processlist-- recursion-method=processlist > pt.log

Note: it is best to use-- print, do not directly use-- execute, otherwise, if something goes wrong, it will be even more troublesome. Print out the statement that is executed directly and execute it from the library.

Transfer the pt.log to the slave database, execute it directly, and then do a consistency check on the master database again. If there is any inconsistent data, remember to log in to mysql to empty the checksums table, and then check the synchronization again until there is no inconsistent data.

Of course, if there are repeated inconsistencies in the master-slave data, it is necessary to first examine the causes of the inconsistencies.

3. SQL_ERROR 1032 solution

Reason:

In the test environment of master and master synchronization, because the business side does not follow the principle of writing only one point at a time, a piece of data is deleted on A database and updated on B library at the same time.

Due to asynchronism and network delay, the update event of B first arrives at the A side for execution, resulting in the A side can not find this record, so the SQL_THREAD reports an error of 1032 and the master-slave synchronization stops.

Error description:

1032 error of master-slave synchronization in MySQL, which generally means that the data to be changed does not exist and the log extracted by SQL_THREAD cannot be applied to report an error, resulting in synchronization failure.

(Update, Delete, Insert a piece of data that has been delete).

1032 of the error itself has no impact on data consistency, the biggest impact is that synchronization failed and synchronization stopped.

If the master (master and slave) has a synchronization failure, check it out as soon as possible and start to solve it. Because it is not synchronized, it will cause inconsistency in reading data. Synchronization should be resumed at the first time

Minimize the impact on business. Then specific analysis of the reasons for non-synchronization, manual or automatic repair of data, and do pt-table-checksum data consistency check.

At present, the business is generally used as master and master synchronization. because master and master synchronization is updated asynchronously, there is the problem of update conflict, and it is easy to cause SQL ERROR 1032 errors. This should be solved on the business side.

Make sure that only one point in the database is updated at the same time, similar to a single point of write. Our solution is to write an underlying database call library, which may involve updating conflicts, calling this library.

In the configuration file, database An and B with 2 points are provided to ensure that library An is updated all the time, and if library An is not available, update library B.

In addition, if it is a scenario that requires high data consistency, such as money, it is recommended to use PXC (strong consistency, true synchronous replication).

Solution:

MySQL5.6.30 version, binlog mode is ROW.

Show slave status\ G, you can see the following error:

Slave_SQL_Running: NO

Last_SQL_Errno: 1032

Last_SQL_Error: Worker 3 failed executing transaction''at master log mysql-bin.000003, end_log_pos 440267874

Could not execute Delete_rows event on table db_test.tbuservcbgolog; Can't find record in 'tbuservcbgolog', Error_code: 1032

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

As you can see from above, it is the SQL_ thread error, error number 1032. Is when the event of a row of data in the delete db_test.tbuservcbgolog table is applied, because of this data

Error because it doesn't exist. The location of this event in the primary server Master binlog is mysql-bin.000003, end_log_pos 440267874. (of course, you can use the Relay of Slave from the server

Search in log. For more information, please see the end.)

Method 1: skip error Event

Skip this error (event) and return the master-slave synchronization to normal. (or N event, one skipping)

Stop slave

Set global sql_slave_skip_counter=1

Start slave

Method 2: skip all 1032 errors

Change the my.cnf file and add under Replication settings:

Slave-skip-errors = 1032

And restart the database, then start salve.

Note: because you want to restart the database, it is not recommended unless there are too many error events.

Method 3: restore deleted data

According to the error message, find the data event SQL with mysqlbinlog and execute it manually in reverse. For example, delete is changed to insert.

In this case, the location of this event in the primary server Master binlog is mysql-bin.000003, end_log_pos 440267874.

1) use mysqlbinlog tool to find out 440267874 events

/ usr/local/mysql-5.6.30/bin/mysqlbinlog-- base64-output=decode-rows-vv mysql-bin.000003 | grep-A 20 '440267874'

Or / usr/local/mysql-5.6.30/bin/mysqlbinlog-base64-output=decode-rows-vv mysql-bin.000003-stop-position=440267874 | tail-20

Or usr/local/mysql-5.6.30/bin/mysqlbinlog-- base64-output=decode-rows-vv mysql-bin.000003 > decode.log

(or add the parameter-d,-- database=name to further filter)

# 160923 20:01:27 server id 1223307 end_log_pos 440267874 CRC32 0x134b2cbc Delete_rows: table id 319 flags: STMT_END_F

# DELETE FROM db_99ducj.tbuservcbgolog

# WHERE

# @ 1 million 10561502 / INT meta=0 nullable=0 is_null=0 /

# @ 2mm 1683955 / INT meta=0 nullable=0 is_null=0 /

# @ 3room90003 / INT meta=0 nullable=0 is_null=0 /

# @ 4room0 / INT meta=0 nullable=0 is_null=0 /

# @ 5 minutes 2016-09-23 17 02purl 24'/ DATETIME (0) meta=0 nullable=1 is_null=0 /

# @ 6=NULL / DATETIME (0) meta=0 nullable=1 is_null=1 /

# at 440267874

The above are the retrieved results. The transaction statement is: delete from db_99ducj.tbuservcbgolog where @ 1 million 10561502 and @ 2 million 1683955.

Among them @ 1 @ 2 @ 3. Correspond to the column names of table tbuservcbgolog, respectively, and fill in the above.

We can reverse this SQL to turn deleter into Insert, manually execute the Insert SQL on the slave library, and then restart slave will be fine.

Note: find event SQL http://www.tuicool.com/articles/6RvUnqV through Relay Log

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.