Automatic restart from library crash (Mysqld got signal 11) Problem resolution

One: Problem description

Received the mail alarm today, then into the database to view the slave status, the IO process and the SQL process are no.

Mysql> Show slave status \g;*************************** 1. row*************************** Slave_io_state:master_host: Not shown here, haha Mast Er_user:replica master_port:3306 connect_retry:60 master_log_file:master1-                bin.001191 read_master_log_pos:29214749 relay_log_file:web_appdb_10-relay-bin.000663 relay_log_pos:29213639 relay_master_log_file:master1-bin.001191 Slave_io_running:no Slave_S Ql_running:no Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Igno  re_table:replicate_wild_do_table:ccda.%,eip_fileservice.% Replicate_wild_ignore_table:last_errno: 0 last_error:skip_counter:0 exec_master_log_pos:29213491 Relay_ log_space:29215212 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:NUL Lmaster_ssl_verify_server_cert:no last_io_errno:0 Last_io_error:last_sql_er rno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:01 Row in Set (0.01 sec) ER Ror:no query specified

try to start, and then check the status again, unexpectedly error, said not connected to the database.

mysql> start slave; Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status \g; ERROR 2006 (HY000): MySQL server has goneawayno connection. Trying to reconnect ... ERROR 2002 (HY000): Can ' t connect to Localmysql server through sockets '/tmp/mysql.sock ' (2) Error:can ' t connect to the Serv ER error:no query specified

Consecutive attempts, you can log in to the database, again query replication, the status of the discovery or No.

Mysql> show slave status \g; No connection. Trying to reconnect ... Connection id:1current Database: * * * NONE * * * *************************** 1. row*************************** slave_io_state:master_host:10.0.3.34 Mast Er_user:replica master_port:3306 connect_retry:60 master_log_file:master1-                bin.001191 read_master_log_pos:29214749 relay_log_file:web_appdb_10-relay-bin.000663 relay_log_pos:29213639 relay_master_log_file:master1-bin.001191 Slave_io_running:no Slave_S Ql_running:no Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Igno  re_table:replicate_wild_do_table:ccda.%,eip_fileservice.% Replicate_wild_ignore_table:last_errno: 0 last_error:skip_counter:0 exec_master_log_pos:29213491 Relay_log_space:29215426 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:NULLMaster_SSL_Verify_Server_Cert:No last_io_errno:0 last_io_error:last_sql_errno:0 Last_sql_error: Replicate_ignore_server_ids:master_server_id:01 row in Set (0.00 sec) Error:no query specified

 

The server database is automatically restarted as soon as start slave is found.

and start slave io_thread no problem, when start slave sql_thread, it will cause the database to automatically restart.

To view the error log:

160429 9:09:00 [note] Event scheduler:loaded 0 events160429 9:09:00 [note]/usr/local/mysql/bin/mysqld:ready for connect Ions. Version: ' 5.5.19-log ' socket: '/tmp/mysql.sock ' port:3306 Source distribution160429 11:04:47 [Note] Slave SQL Threadini tialized, starting replication in log ' master1-bin.001191 ' at position29213491, relay log './web_appdb_10- relay-bin.000663 ' position:29213639160429 11:04:47-mysqld got signal 11; This could is because a bug. It isalso possible that this binaryor one of the libraries it was linkedagainst, corrupt improperly built,or Red. This error can also becaused by malfunctioning hardware.  We'll try our best to scrape up some infothat would hopefully help diagnosethe problem, but since we have alreadycrashed, Something is definitely wrongand this may fail. Key_buffer_size=268435456read_buffer_size=6291456max_used_connections=3max_threads=2000thread_count= 2connection_count=2it is possible this mysqld could use up tokey_buffer_size + (read_buffer_size +sort_buffer_size) *max_threads = 20764878 Kbytes of memoryhope that ' s OK; If not, decrease somevariables in the equation. Thread pointer:0x2ab2f1b54740attempting BackTrace. You can use the thefollowing information to find Outwhere Mysqld died. If you see no messagesafter this, something wentterribly wrong...stack_bottom = 0x594310e8 thread_stack0x30000/usr/local/ Mysql/bin/mysqld (my_print_stacktrace+0x33) [0x765df3]/usr/local/mysql/bin/mysqld (handle_segfault+0x36e) [ 0x4ee4fe]/lib64/libpthread.so.0[0x31a640ebe0]/usr/local/mysql/bin/mysqld (_znk9table_def15compatible_ WITHEP3THDP14RELAY_LOG_INFOP5TABLEPS5_+0X31A) [0x74c29a]/usr/local/mysql/bin/mysqld (_ZN14Rows_log_event14do_ APPLY_EVENTEPK14RELAY_LOG_INFO+0XCDC) [0x6f0d3c]/usr/local/mysql/bin/mysqld (_z26apply_event_and_update_posp9log _EVENTP3THDP14RELAY_LOG_INFO+0X14D) [0x5021ed]/usr/local/mysql/bin/mysqld[0x504b19]/usr/local/mysql/bin/mysqld ( HANDLE_SLAVE_SQL+0XC0A) [0x5061ea]/lib64/libpthread.so.0[0x31a640677d]/lib64/libc.so.6 (CLONE+0X6D) [0X31A54D49AD] Trying to get some variables. Some pointers may is invalid and cause Thedump to abort. Query (Nil): is an invalid pointerconnection ID (thread ID): 353status:not_killed the manual page athttp://dev.mysql.co M/doc/mysql/en/crashing.html containsinformation that should help you find Outwhat is causing the crash.160429 11:04:48 my Sqld_safe number ofprocesses running now:0160429 11:04:48 mysqld_safe mysqldrestarted160429 11:04:48 innodb:the InnoDB m  Emoryheap is disabled160429 11:04:48 innodb:mutexes andrw_locks use GCC atomic builtins160429 11:04:48 innodb:compressed Tablesuse zlib 1.2.3160429 11:04:48 innodb:initializing bufferpool, size = 32.0g160429 11:04:50 innodb:completedinitial  ization of buffer pool160429 11:04:50 innodb:highest supportedfile format is Barracuda.InnoDB:The log sequence number in Ibdatafiles does not matchinnodb:the log sequence number in theib_logfiles!160429 11:04:50 innodb:database is not Shu T down normally! Innodb:starting crash Recovery. innodb:reading tablespace information fromthe. ibd files ... Innodb:restoring possible half-writtendata pages from the Doublewriteinnodb:buffer ... Innodb:last MySQL binlog file position 0112571, file name./mysql-bin.048292160429 11:04:52 innodb:waiting for the back Ground threads tostart160429 11:04:53 innodb:1.1.8 started; Logsequence number 5992159806777160429 11:04:53 [note] Recovering after acrash using mysql-bin160429 11:04:53 [note] Start ing crashrecovery ... 160429 11:04:53 [Note] Crash recoveryfinished.160429 11:04:53 [Warning] Neither--relay-log nor--relay-log-index were use D So replication could break when Thismysql server acts as a slave and have his hostname changed!! Please use the '--relay-log=web_appdb_10-relay-bin ' to avoid this problem.160429 11:04:53 [Note] Event scheduler:loaded 0 Even ts160429 11:04:53 [Note]/usr/local/mysql/bin/mysqld:ready for connections. Version: ' 5.5.19-log ' socket: '/tmp/mysql.sock ' port:3306 Source distribution

For this error "Mysqld got signal 11", I check online, some say that disk space is full, some say is memory problems, there may be hardware errors, there may be relay log replay location of SQL caused.

View the statement that the relay log executes at this location:

relay_log_file:web_appdb_10-relay-bin.000663

relay_log_pos:29213639

# at 29213639#160428 21:29:32 server ID 1 end_log_pos 29213559 Query thread_id=624506 exec_time=0 error_c Ode=0set timestamp=1461850172/*!*/;/*!\c utf8mb4 *//*!*/; [email protected] @session. character_set_client=45,@ @session. collation_connection=45,@ @session. Collation_ server=33/*!*/; begin/*!*/;# at 29213707#160428 21:29:32 server ID 1 end_log_pos 29213657 table_map: ' ccda '. ' Ess_accounting_relation ' Mapped to number 311993# at 29213805#160428 21:29:32 server ID 1 end_log_pos 29213757 table_map: ' ccda '. ' Ess_account Ing_relation_1 ' mapped to number 311994# at 29213905#160428 21:29:32 server ID 1 end_log_pos 29214051 update_rows:t Able ID 311993 flags:stmt_end_f BINLOG ' Pbaivxmbaaaaygaaanndvqeaalncbaaaaaeabgnjzgeaf2vzc19hy2nvdw50aw5nx3jlbgf0aw9uaa4ddw8pdw8pdw8pdw8maxysatwapaagacwbpaasazyal gcwajya/d8= Pbaivxmbaaaazaaaad3evqeaalrcbaaaaaeabgnjzgeagwvzc19hy2nvdw50aw5nx3jlbgf0aw9uxzeadgmpdw8pdw8pdw8pdwwdfiwbpaa8aayalae8acwbl Gcwajyalgd8pw==pbaivxgbaaaajgeaagpfvqeaalncbaaaaaeadv////8q4dnl7watadeymtewmtaxmte1mtewmde2mzus5bqu5luy5luy5qy+5yet6k+ Ba0vbuwaaceakpei0puwnlryaemhhbmd5mta0nze1mtexodezmtyymhcxmtexmdexntexmtg1mduymza2mje2mqaaai3gvervegaaeoa55e8aewaxmjexmdew Mtexntexmdaxnjm1euw6los7mos7moasvuwhreivgqnfqvmuadexmtewmte1mtewotewmzgwmdg5ceakpei0puwnlryaemhhbmd5mta0nze1mtexodezmtyym Hcxmtexmdexntexmtg1mduymza2mje2mqaaai3gvervegaa '/*!*/;### UPDATE ' ccda '. ' Ess_accounting_relation ' # # # WHERE### @1= 15721785### @2= ' 1211010111511001635 ' # # # @3= ' payable voucher ' # # # @4= ' EAS ' # # # @5=null### @6= ' # # # @7= ' report Bill # # # @8= ' zhangy104715 1118131622 ' # # # @9= ' 11110115111850523062161 ' # # # @10= ' # # # @11= ' # # # @12= ' # # @13=2016-01-19 16:25:09### @14=null###   set### @1=15721785### @2= ' 1211010111511001635 ' # # # @3= ' Payables voucher ' # # @4= ' EAS ' # # # @5=null### ' 11110115110910380089 ' # # # @7= ' Quote ' # # # @8= ' zhangy1047151118131622 ' # # # @9= ' 11110115111850523062161 ' # # # @10= ' # # # @11= ' # # @12= ' # # # @13=2016- 01-19 16:25:09### @14=null

Back up the record first, and then manually update it from the library to see if it was an error.

UPDATE ' ccda '. ' Ess_accounting_relation '

SET attachid= ' 11110115110910380089 '

WHERE id = 15721785;

It turns out that you can update from the library as well.

Later I wanted to look at the table structure, resulting in an error:

Mysql> Show create table ' CCDA '. ' Ess_accounting_relation ' \g;

ERROR 144 (HY000): Table './ccda/ess_accounting_relation_1 ' is marked as crashed and last (automatic?) Repair failed

ERROR:

No query specified

Mysql> Select COUNT (*) fromccda.ess_accounting_relation_1;

ERROR 144 (HY000): Table './ccda/ess_accounting_relation_1 ' is marked as crashed and last (automatic?) Repair failed

Strange, just can update it, but now can not access the normal.

Then, try to fix the problem table:

Check table ccda.ess_accounting_relation_1;

Repair table ccda.ess_accounting_relation_1;

After the repair succeeds, look at the Ccda.ess_accounting_relation table structure and find that the table is a consolidated table, ess_accounting_relation_1 is the MyISAM engine:

CREATE TABLE ' ess_accounting_relation ' (

......)

Engine=mrg_myisam DEFAULT Charset=utf8 insert_method=lastunion= (' ess_accounting_relation_1 ')

Originally thought to repair the table success, start slave, it is normal. The result will still cause the database to restart.

At this time, again check ess_accounting_relation_1, is also normal, no show crashes.

I experimented with skipping the table from the library (with change master to or set global Sql_slave_skip_counter=n), and did not cause a restart from the library when performing other table operations.

I experimented with adding parameters from the Library configuration file: Replicate_ignore_table=ccda.ess_accounting_relation filtered out the table, restarted the database, and then start slave, without causing a reboot from the library.

Finally, I ventured to try to manipulate Ess_accounting_relation_1 's data directly in the main library (provided that the above parameter replicate_ignore_table has been commented out) and found that the corresponding data was not being restarted when it was applied from the library.

So the problem is with this Mrg_myisam storage engine.

This table, in fact, every day there are update, but why the recent emergence of this problem, it is not known. Because the amount of sub-table data referenced by this table is too large? The table is about 16 million data. I don't know.

Two: Cause of error

A consolidated table of the MRG_MYISAM storage engine that references a MyISAM engine's child table, which causes the slave database to restart automatically, and occasionally the child table crashes.  

This estimate is a bug of MySQL. Click to open link

Three: Solutions

Since the Ess_accounting_relation table data is only derived from the Ccda.ess_accounting_relation_1 table, there is actually no sense of merging, and, by understanding the discovery, the MyISAM table is updated frequently. MyISAM easy to crash, and does not support row locks, it is recommended to change the ccda.ess_accounting_relation_1 to InnoDB storage engine (you can mysqldump back up the table, and then in the backup file to change MyISAM to InnoDB), Remove ccda.ess_accounting_relation and rename ccda.ess_accounting_relation_1 to Ccda.ess_accounting_relation.

--For Mrg_myisam introduction, please refer to http://blog.csdn.net/yabingshi_tech/article/details/51320701

--note: MySQL version 5.5.19

Automatic restart from library crash (Mysqld got signal 11) Problem resolution