MySQL unexpectedly crashes

Question

MySQL keeps stopping daily on my server and I cannot find the apparent reason. After consulting the error.logs, I still haven’t been able to make much sense of it.

All I really have installed on the server are a few WordPress installations.

Here are the MySQL error logs.

160721  9:25:44 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160721  9:25:44 [Note] Plugin 'FEDERATED' is disabled.
160721  9:25:44 InnoDB: The InnoDB memory heap is disabled
160721  9:25:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160721  9:25:44 InnoDB: Compressed tables use zlib 1.2.8
160721  9:25:44 InnoDB: Using Linux native AIO
160721  9:25:44 InnoDB: Initializing buffer pool, size = 128.0M
160721  9:25:44 InnoDB: Completed initialization of buffer pool
160721  9:25:44 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 40817296
160721  9:25:44  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 41068976
160721  9:25:45  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
160721  9:25:45  InnoDB: Waiting for the background threads to start
160721  9:25:46 InnoDB: 5.5.47 started; log sequence number 41068976
160721  9:25:46 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160721  9:25:46 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
160721  9:25:46 [Note] Server socket created on IP: '127.0.0.1'.
160721  9:25:46 [Note] Event Scheduler: Loaded 0 events
160721  9:25:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
160721 17:50:13 [Note] /usr/sbin/mysqld: Normal shutdown

160721 17:50:13 [Note] Event Scheduler: Purging the queue. 0 events
160721 17:50:13  InnoDB: Starting shutdown...
160721 17:50:14  InnoDB: Shutdown completed; log sequence number 41400934
160721 17:50:14 [Note] /usr/sbin/mysqld: Shutdown complete

160721 17:50:15 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160721 17:50:15 [Note] Plugin 'FEDERATED' is disabled.
160721 17:50:15 InnoDB: The InnoDB memory heap is disabled
160721 17:50:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160721 17:50:15 InnoDB: Compressed tables use zlib 1.2.8
160721 17:50:15 InnoDB: Using Linux native AIO
160721 17:50:15 InnoDB: Initializing buffer pool, size = 128.0M
160721 17:50:15 InnoDB: Completed initialization of buffer pool
160721 17:50:15 InnoDB: highest supported file format is Barracuda.
160721 17:50:15  InnoDB: Waiting for the background threads to start
160721 17:50:16 InnoDB: 5.5.47 started; log sequence number 41400934
160721 17:50:16 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160721 17:50:16 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
160721 17:50:16 [Note] Server socket created on IP: '127.0.0.1'.
160721 17:50:16 [Note] Event Scheduler: Loaded 0 events
160721 17:50:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
160722  5:44:00 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160722  5:44:00 [Note] Plugin 'FEDERATED' is disabled.
160722  5:44:00 InnoDB: The InnoDB memory heap is disabled
160722  5:44:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160722  5:44:00 InnoDB: Compressed tables use zlib 1.2.8
160722  5:44:00 InnoDB: Using Linux native AIO
160722  5:44:00 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160722  5:44:00 InnoDB: Completed initialization of buffer pool
160722  5:44:00 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160722  5:44:00 [ERROR] Plugin 'InnoDB' init function returned error.
160722  5:44:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160722  5:44:00 [ERROR] Unknown/unsupported storage engine: InnoDB
160722  5:44:00 [ERROR] Aborting

160722  5:44:00 [Note] /usr/sbin/mysqld: Shutdown complete

160722  5:44:01 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160722  5:44:01 [Note] Plugin 'FEDERATED' is disabled.
160722  5:44:01 InnoDB: The InnoDB memory heap is disabled
160722  5:44:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160722  5:44:01 InnoDB: Compressed tables use zlib 1.2.8
160722  5:44:01 InnoDB: Using Linux native AIO
160722  5:44:01 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160722  5:44:01 InnoDB: Completed initialization of buffer pool
160722  5:44:01 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160722  5:44:01 [ERROR] Plugin 'InnoDB' init function returned error.
160722  5:44:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160722  5:44:01 [ERROR] Unknown/unsupported storage engine: InnoDB
160722  5:44:01 [ERROR] Aborting

160722  5:44:01 [Note] /usr/sbin/mysqld: Shutdown complete

160722  5:50:30 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160722  5:50:30 [Note] Plugin 'FEDERATED' is disabled.
160722  5:50:30 InnoDB: The InnoDB memory heap is disabled
160722  5:50:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160722  5:50:30 InnoDB: Compressed tables use zlib 1.2.8
160722  5:50:30 InnoDB: Using Linux native AIO
160722  5:50:30 InnoDB: Initializing buffer pool, size = 128.0M
160722  5:50:30 InnoDB: Completed initialization of buffer pool
160722  5:50:31 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 41616363
160722  5:50:31  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 46858752
InnoDB: Doing recovery: scanned up to log sequence number 48741839
160722  5:50:31  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
160722  5:50:32  InnoDB: Waiting for the background threads to start
160722  5:50:33 InnoDB: 5.5.47 started; log sequence number 48741839
160722  5:50:33 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160722  5:50:33 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
160722  5:50:33 [Note] Server socket created on IP: '127.0.0.1'.
160722  5:50:33 [Note] Event Scheduler: Loaded 0 events
160722  5:50:33 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.47-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

gndo · Answer

The following log entry means you’re running of memory

160722  5:44:00 InnoDB: Fatal error: cannot allocate memory for the buffer pool

You either need a larger droplet, or you need to create and enable a swap file

Adam Robertson · Answer

In addition to creating a swap, you might also consider doing two other things:

Use a plugin called Login Lockdown, which will block multple attempts to login to your Wordpress site.
Add the following to your .htaccess file in your root web folder:

# 6G FIREWALL/BLACKLIST
# @ https://perishablepress.com/6g/

# 6G:[QUERY STRINGS]
<IfModule mod_rewrite.c>
	RewriteEngine On
	RewriteCond %{QUERY_STRING} (eval\() [NC,OR]
	RewriteCond %{QUERY_STRING} (127\.0\.0\.1) [NC,OR]
	RewriteCond %{QUERY_STRING} ([a-z0-9]{2000}) [NC,OR]
	RewriteCond %{QUERY_STRING} (javascript:)(.*)(;) [NC,OR]
	RewriteCond %{QUERY_STRING} (base64_encode)(.*)(\() [NC,OR]
	RewriteCond %{QUERY_STRING} (GLOBALS|REQUEST)(=|\[|%) [NC,OR]
	RewriteCond %{QUERY_STRING} (<|%3C)(.*)script(.*)(>|%3) [NC,OR]
	RewriteCond %{QUERY_STRING} (\\|\.\.\.|\.\./|~|`|<|>|\|) [NC,OR]
	RewriteCond %{QUERY_STRING} (boot\.ini|etc/passwd|self/environ) [NC,OR]
	RewriteCond %{QUERY_STRING} (thumbs?(_editor|open)?|tim(thumb)?)\.php [NC,OR]
	RewriteCond %{QUERY_STRING} (\'|\")(.*)(drop|insert|md5|select|union) [NC]
	RewriteRule .* - [F]
</IfModule>

# 6G:[REQUEST METHOD]
<IfModule mod_rewrite.c>
	RewriteCond %{REQUEST_METHOD} ^(connect|debug|delete|move|put|trace|track) [NC]
	RewriteRule .* - [F]
</IfModule>

# 6G:[REFERRERS]
<IfModule mod_rewrite.c>
	RewriteCond %{HTTP_REFERER} ([a-z0-9]{2000}) [NC,OR]
	RewriteCond %{HTTP_REFERER} (semalt.com|todaperfeita) [NC]
	RewriteRule .* - [F]
</IfModule>

# 6G:[REQUEST STRINGS]
<IfModule mod_alias.c>
	RedirectMatch 403 (?i)([a-z0-9]{2000})
	RedirectMatch 403 (?i)(https?|ftp|php):/
	RedirectMatch 403 (?i)(base64_encode)(.*)(\()
	RedirectMatch 403 (?i)(=\\\'|=\\%27|/\\\'/?)\.
	RedirectMatch 403 (?i)/(\$(\&)?|\*|\"|\.|,|&|&amp;?)/?$
	RedirectMatch 403 (?i)(\{0\}|\(/\(|\.\.\.|\+\+\+|\\\"\\\")
	RedirectMatch 403 (?i)(~|`|<|>|:|;|,|%|\\|\s|\{|\}|\[|\]|\|)
	RedirectMatch 403 (?i)/(=|\$&|_mm|(wp-)?config\.|cgi-|etc/passwd|muieblack)
	RedirectMatch 403 (?i)(&pws=0|_vti_|\(null\)|\{\$itemURL\}|echo(.*)kae|etc/passwd|eval\(|self/environ)
	RedirectMatch 403 (?i)\.(aspx?|bash|bak?|cfg|cgi|dll|exe|git|hg|ini|jsp|log|mdb|out|sql|svn|swp|tar|rar|rdf)$
	RedirectMatch 403 (?i)/(^$|mobiquo|phpinfo|shell|sqlpatch|thumb|thumb_editor|thumbopen|timthumb|webshell)\.php
</IfModule>

# 6G:[USER AGENTS]
<IfModule mod_setenvif.c>
	SetEnvIfNoCase User-Agent ([a-z0-9]{2000}) bad_bot
	SetEnvIfNoCase User-Agent (archive.org|binlar|casper|checkpriv|choppy|clshttp|cmsworld|diavol|dotbot|extract|feedfinder|flicky|g00g1e|harvest|heritrix|httrack|kmccrew|loader|miner|nikto|nutch|planetwork|postrank|purebot|pycurl|python|seekerspider|siclab|skygrid|sqlmap|sucker|turnit|vikspider|winhttp|xxxyy|youda|zmeu|zune) bad_bot
	<limit GET POST PUT>
		Order Allow,Deny
		Allow from All
		Deny from env=bad_bot
	</limit>
</IfModule>

# 6G:[BAD IPS]
<Limit GET HEAD OPTIONS POST PUT>
	Order Allow,Deny
	Allow from All
	# uncomment/edit/repeat next line to block IPs
	# Deny from 123.456.789
</Limit>

# Block Russian Referrer Spam
RewriteEngine on
RewriteCond %{HTTP_REFERER} ^http://.*ilovevitaly\.com/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*ilovevitaly.\.ru/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*ilovevitaly\.org/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*ilovevitaly\.info/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*iloveitaly\.ru/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*econom\.co/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*savetubevideo\.com/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*kambasoft\.com/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*buttons\-for\-website\.com/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*semalt\.com/ [NC,OR]
RewriteCond %{HTTP_REFERER} ^http://.*darodar\.com/ [NC]
RewriteRule ^(.*)$ – [F,L]



# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>

# END WordPress


    #prevents access to wp-config
    <Files wp-config.php>
    order allow,deny
    deny from all
    </Files>


    #prevents directory browsing
    Options All -Indexes

       # Protect the .htaccess
    <files .htaccess="">
    order allow,deny
    deny from all
    </files>

    <Files ~ "^.*\.([Hh][Tt][Aa])">
    order allow,deny
    deny from all
    satisfy all
    </Files>

# protect xmlrpc
<Files xmlrpc.php>
	Order Deny,Allow
	Deny from all
	##if you need to allow someone access for pingbacks
	##Allow from 123.456.789 ##yourserver
	##Allow from 321.654.987 ##yourotherserver
</Files>

Report this

MySQL unexpectedly crashes

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today