MySQL Innodb mysteriously disappears

Problem Description:

Morning received Zabbix alarm, prompt host:10.10.1.2, MySQL master and slave synchronization failed.

Log on to the server to see the details.

Shell >Mysqlmysql>show slave Status\gslave I/O thread:yesslave SQL thread:noslave sql:error'Unknown Storage Engine'InnoDB"'On query. Default Database:'BAENG_TV'. Query:'UPDATE std_tv_card SET uid='135601920029371878', devicetoken='60000am1500d16972129_569c'WHERE id='220888"', Error_code:1286Slave:unknown Storage Engine'InnoDB'Error_code:1286

# This is some state information that show slave status\g see, saying that the InnoDB engine is not supported. It's not a joke, it's not the first time you run.

Shell > vim/data/mysql_data/hostname. Errversion:'5.5.28-log'Socket'/tmp/mysql.sock'Port3306Source Distribution160930  -: -: $Mysqld_safe number of processes running now:0160930  -: -: $Mysqld_safe mysqld restarted160930  7: -:Panax Notoginsenginnodb:the InnoDB Memory heap is disabled160930  7: -:Panax Notoginsenginnodb:mutexes and Rw_locks use GCC atomic builtins160930  7: -:Panax Notoginsenginnodb:compressed Tables Use Zlib1.2.3160930  7: -: -Innodb:initializing buffer pool, size =1. 0ginnodb:mmap (1098907648bytes) failed; Errno A160930  7: -: -innodb:completed initialization of buffer pool160930  7: -: -Innodb:fatal Error:cannot Allocate Memory forThe buffer pool160930  7: -: -[ERROR] Plugin'InnoDB'Initfunctionreturned error.160930  7: -: -[ERROR] Plugin'InnoDB'registration as a STORAGE ENGINE failed.160930  7: -: -[Note] Serverhostname(bind-address):'0.0.0.0'; Port3306160930  7: -: -[Note]-'0.0.0.0'resolves to'0.0.0.0';160930  7: -: -[Note] Server socket created on IP:'0.0.0.0'.160930  7: -: -[Note] Slave SQL thread initialized, starting replicationinchLog'mysql-bin.003702'At position287099739, Relay Log'./hostname-relay-bin.010674'Position2597075160930  7: -: -[Note] Slave I/O thread:connected to Master'[Email protected]:3306', Replication startedinchLog'mysql-bin.003702'At position287261852160930  7: -: the[Note] Event scheduler:loaded0Events160930  7: -: the[Note]/usr/local/mysql-5.5/bin/mysqld:ready forconnections. Version:'5.5.28-log'Socket'/tmp/mysql.sock'Port3306Source Distribution160930  7: at:Geneva[ERROR] Slave Sql:error'Unknown Storage Engine'InnoDB"'On query. Default Database:'BAENG_TV'. Query:'UPDATE std_tv_card SET uid='135601920029371878', devicetoken='60000am1500d16972129_569c'WHERE id='220888"', Error_code:1286160930  7: at:Geneva[Warning] Slave:unknown Storage Engine'InnoDB'Error_code:1286160930  7: at:Geneva[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with"SLAVE START". We stopped at log'mysql-bin.003702'Position288077147

# Mysql.err

1. 0G

# Initialize the cache pool with a size of 1G

 for the buffer pool

# Unable to allocate memory for InnoDB cache pool

' InnoDB ' function returned error.

# InnoDB Plugin init function returned error

' InnoDB ' registration as a STORAGE ENGINE failed.

# InnoDB Plugin Storage Engine registration failed

# followed by initialization Slave SQL thread initiates synchronization, I/O thread connects master.
# last hint, Slave SQL error, unknown storage engine, query statement ' UPDATE failed '.

Workaround:

 free-M

# At that time, the free memory was only 936M, while the initialized cache pool was 1G.

# See here, you should know that the fault is caused by insufficient system memory, the machine memory is 24G. It ran a MySQL Slave with some mission plans.

# Speaking of Task Scheduler, this is some PHP script that queries the data and then writes ElasticSearch to generate the index.

PS grep WC -l

# Boy!!!

# The task plan is to execute a different PHP script every 5, 10, 15 minutes, respectively. Execute, Next loop.
# that is, if you do not finish, wait until the next point in time to restart a PHP script ... So it takes up a lot of system memory.

PS grep grep grep awk ' {print $} ' Xargs Kill {}

# Kill all of these blocked processes first

 free-m             total       used       free     shared    buffers     Cachedmem:          24020       4933      19086                   0       2880-/+ buffers/cache:       1912      22107Swap:         8191           9       8182

# Memory Freed up

Shell >/etc/init.d/> mysqlmysql> show slave status\g             slave_io_running:yes            Slave_sql_running:yes        1340

# master-Slave synchronization is normal, delay let it fill up, after a while.

# Fault Elimination
# and then let the trouble no longer appear

Shell > Vim script/yiic_index.SH#!/bin/Bashlogfile='/root/script/logs/yiic.log'filepath='/DATA/GIT-WEBROOT/YIIC Index' Line=`PSAux |grep-Vgrep|grep 'YIIC Index'|WC-l 'if[$line-eq0]: Then/usr/local/php/bin/php $filepath >/dev/NULL&Else  Echo`Date "+%f%T"' $filepath >>$logfile Exit0fi# Endshell> Crontab-e*/5* * * * */root/script/logs/yiic_index.SH

# This will control the number of PHP processes.

# However, this script will appear in the zombie process.
# First execute the SH script, then put the PHP process into the background, exit the SH script, so that SH is the parent process of PHP, so the production of zombies.
# But this is controllable, if PHP executes too long, the next time Crond calls SH, PHP is not executed.
# The spawned zombie process does not have to worry, the zombie automatically dies when PHP is finished.

Shell > Crontab-e*/5 /USR/LOCAL/PHP/BIN/PHP/DATA/GIT-WEBROOT/YIIC Index >/dev/ NULL

# You can also control the PHP process execution time, as you choose

MySQL Innodb mysteriously disappears