Mal
By:
Mal

MySQL unexpectedly crashes

July 22, 2016 602 views
MySQL WordPress Ubuntu

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)
2 Answers

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

by Justin Ellingwood
Swap space can be used as an "overflow" area for your system when you run out of RAM. The operating system can store data that would normally be kept in RAM on the hard drive in a specially formatted file. In this guide, we'll demonstrate how to create and use one of these files in Ubuntu 14.04.

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

  1. Use a plugin called Login Lockdown, which will block multple attempts to login to your Wordpress site.

  2. 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>
Have another answer? Share your knowledge.