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

This article shows you how to solve the problem of MySQL automatic restart, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Preface

The error message is as follows:

2019-07-24T01:14:53.769512Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option'--disable-partition-engine-check' to skip this check.

2019-07-24T01:14:53.769516Z 0 [Note] Beginning of list of non-natively partitioned tables

01:14:53 UTC-mysqld got signal 11

This could be because you hit a bug. It is also possible that this binary

Or one of the libraries it was linked against is corrupt, improperly built

Or misconfigured. This error can also be caused by malfunctioning hardware.

Attempting to collect some information that could help diagnose the problem.

As this is a crash and something is definitely wrong, the information

Collection process might fail.

Please help us make Percona Server better by reporting any

Bugs at http://bugs.percona.com/

Key_buffer_size=33554432

Read_buffer_size=8388608

Max_used_connections=0

Max_threads=501

Thread_count=4

Connection_count=0

It is possible that mysqld could use up to

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_threads = 4478400 K bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f486900e000

Attempting backtrace. You can use the following information to find out

Where mysqld died. If you see no messages after this, something went

Terribly wrong...

Stack_bottom = 7f4846172820 thread_stack 0x80000

/ usr/local/mysql5.7/bin/mysqld (my_print_stacktrace+0x2c) [0xed481c]

/ usr/local/mysql5.7/bin/mysqld (handle_fatal_signal+0x461) [0x7a15a1]

/ lib64/libpthread.so.0 (+ 0xf7e0) [0x7f498697c7e0]

/ usr/local/mysql5.7/bin/mysqld (_ ZN12ha_federated7rnd_posEPhS0_+0x2f) [0x12bcc3f]

/ usr/local/mysql5.7/bin/mysqld (_ ZN7handler10ha_rnd_posEPhS0_+0x172) [0x804a12]

/ usr/local/mysql5.7/bin/mysqld (_ ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x1e3) [0xe50e23]

/ usr/local/mysql5.7/bin/mysqld (_ ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x716) [0xe50196]

/ usr/local/mysql5.7/bin/mysqld (_ ZN9Log_event11apply_eventEP14Relay_log_info+0x6e) [0xe48fde]

/ usr/local/mysql5.7/bin/mysqld (_ Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x1f0) [0xe8d6f0]

/ usr/local/mysql5.7/bin/mysqld (handle_slave_sql+0x163d) [0xe9a0fd]

/ usr/local/mysql5.7/bin/mysqld (pfs_spawn_thread+0x1b4) [0x1209414]

/ lib64/libpthread.so.0 (+ 0x7aa1) [0x7f4986974aa1]

/ lib64/libc.so.6 (clone+0x6d) [0x7f4984c6bc4d]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0): is an invalid pointer

Connection ID (thread ID): 2

Status: NOT_KILLED

You may download the Percona Server operations manual by visiting

Http://www.percona.com/software/percona-server/. You may find information

In the manual which will help you identify the cause of the crash.

1. Preliminary process

When a similar situation has occurred before, it is due to insufficient memory, because there is also a corresponding prompt in the log:

Key_buffer_size=33554432read_buffer_size=8388608max_used_connections=0max_threads=501thread_count=4connection_count=0It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size) * max_threads= 4478400 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.

It is true that the physical memory of this test environment is not large, and the remaining memory is insufficient, and as a slave library of another test environment, less memory is allocated.

Similar situations have occurred in some environments before, which can be started normally after processing such as adjusting parameters and releasing memory, so try to close some temporary programs and adjust the values of the above parameters of MySQL, such as:

[mysqld] max_connections = 50

Then restart MySQL, and as a result, it keeps restarting.

The preliminary treatment was unsuccessful.

two。 Add innodb_force_recovery solution to keep restarting

Add innodb_force_recovery to the configuration file my.cnf to deal with the problem of continuous restart

[mysqld] innodb_force_recovery = 4

After adding, start MySQL again, and there is no repeated restart at this time.

Check the database log, there is a prompt [Note] InnoDB:! Innodb_force_recovery is set to 4! As follows:

Because the database can be opened at this time, an attempt was made to start the slave library, but an error was reported at this time, prompting Table 'mysql.slave_relay_log_info' is read only.

At this point, look at the error log, as follows

Therefore, when starting this time, innodb_force_recovery is set to 4, and after MySQL 5.6.15, when the value of innodb_force_recovery is greater than or equal to 4, the InnoDB table is in read-only mode. Because the information needs to be written to the table when starting replication, an error is reported at this time.

Note: because the setting is 1-3, it still does not take effect, so the value I set to 4 (more than 4) may permanently cause data file corruption. If there is a similar problem in the production environment, be sure to copy a test first and deal with it in the production environment after the test is passed. At this point, you can dump all the data, and then restore it.

3. Innodb_force_recovery parameters

Innodb_force_recovery can be set to 1-6, and large values include the effects of all previous values that are less than it.

1 (SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages. The service is forced to run even though a corrupted page is detected. Generally, you can set it to this value, and then dump exports the database table for reconstruction.

2 (SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform full purge operations, will result in crash. Block master thread and any purge thread from running. This value is used if crash occurs in the purge section.

3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

4 (SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed. Do not do this if it is likely to cause a crash. Do not perform statistical operations. This value may permanently corrupt the data file. If this value is used, the secondary index will be deleted and rebuilt in the future.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): without looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed. At this point, InnoDB even processes unfinished transactions as committed. This value may permanently corrupt the data file.

6 (SRV_FORCE_NO_LOG_REDO): roll forward is not performed. No redo log roll-forward is done during recovery. Make the database page in an obsolete state, which may cause more damage to the B-tree or other database structures.

Note:

For security, select, create, and drop operations can be performed on the table when the parameter value is greater than 0, but operations such as insert, update or delete are not allowed.

After MySQL 5.6.15, when the value of innodb_force_recovery is greater than or equal to 4, the InnoDB table is in read-only mode.

When the value is less than or equal to 3, you can use select to dump the table, either drop or create table.

Values greater than 3 after MySQL 5.6.27 also support DROP TABLE; to drop off a table if it is known in advance which table caused the crash.

If you encounter runaway rollback caused by a failed large-scale import or a large number of ALTER TABLE operations, you can kill the mysqld thread and set innodb_force_recovery = 3 to prevent rollback after the database restart. Then delete the table that caused the runaway rollback; if the data in the table is corrupted so that the entire table contents cannot be dump. Then a query with an order by primary_key desc clause may be able to dump part of the data after the corrupted part

The above content is how to solve the problem of MySQL automatic restart. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.