My MySQL crashed many times in a month

Question

My website (Wordpress) sometime stoped working with below error message"

cannot connect to Datatabse

I checked the log file of MySQL and I found that crash-info as below:

---------- 
2021-01-21  0:44:59 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-21  0:44:59 0 [Note] InnoDB: Uses event mutexes
2021-01-21  0:44:59 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-21  0:44:59 0 [Note] InnoDB: Number of pools: 1
2021-01-21  0:45:00 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-01-21  0:45:00 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-01-21  0:45:00 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-21  0:45:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-21  0:45:00 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=215993122
2021-01-21  0:45:07 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-21  0:45:07 0 [Note] InnoDB: Uses event mutexes
2021-01-21  0:45:07 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-21  0:45:07 0 [Note] InnoDB: Number of pools: 1
2021-01-21  0:45:07 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-01-21  0:45:07 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-01-21  0:45:07 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-21  0:45:07 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-21  0:45:07 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=215993122
2021-01-21  0:50:02 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-21  0:50:02 0 [Note] InnoDB: Uses event mutexes
2021-01-21  0:50:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-21  0:50:02 0 [Note] InnoDB: Number of pools: 1
2021-01-21  0:50:02 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-01-21  0:50:02 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-01-21  0:50:02 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-21  0:50:02 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-21  0:50:02 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=215993122
2021-01-21  0:50:02 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2021-01-21  0:50:02 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-01-21  0:50:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-01-21  0:50:02 0 [Note] InnoDB: Setting file '/opt/lampp/var/mysql/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-01-21  0:50:02 0 [Note] InnoDB: File '/opt/lampp/var/mysql/ibtmp1' size is now 12 MB.
2021-01-21  0:50:02 0 [Note] InnoDB: Waiting for purge to start
2021-01-21  0:50:02 0 [Note] InnoDB: 10.4.11 started; log sequence number 215993131; transaction id 221150
2021-01-21  0:50:02 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-01-21  0:50:02 0 [Note] InnoDB: Loading buffer pool(s) from /opt/lampp/var/mysql/ib_buffer_pool
2021-01-21  0:50:02 0 [Note] Server socket created on IP: '::'.
---------- 

I restarted MySQL and my website worked well. The version of my MySQL is: Distrib 10.4.11-MariaDB, for Linux (x86_64) Ubuntu verion 20.

This situation appeared few times for a month ago.

I did search the solution in some posts before, but still cannot fix this issue,

MySQL server crashes at least 2 times a week

Wordpress + PHP+ apache +mysql, mysql crash every 1/ month

Is anyone stuck in this case, and knows how to fix it?

Answer 1

Start MySQL on XAMPP Control panel.

Check the My SQL Error log “mysql_error.log” by clicking on the Logs button on XAMPP control panel.

Go to the “data” directory in the MySQL database. I installed XAMPP on D: drive on my computer & the MySQL “data” directory location of my computer was “/opt/lampp/var/mysql/”. You may have a different location.

Take Backup of MySQL “data” Folder

First of all, you should create a backup of the “data” folder using any compression software.

Give a name like “data_backup.zip” or any type of compression you wish. I used WinRAR compression software to compress & backup MySQL “data” folder.

Rename the “data” folder

Rename the “data” folder to “data-oldfiles”. This is very important to rename the data directory to any new directory name. Create a new “data” folder

Create a new folder and give the folder name as “data“

To solve the problem we need to create a new “data” directory in the mysql database. Copy content from “backup” folder

Go to the “backup” folder and copy all files.

Paste the files from backup folder to data folder

Now start the MySQL database from XAMPP.

Now, your MySQL database will start properly without showing any error.

Transfer all MySQL projects Database, Data file & Log files

If you have many database which was used for various projects, then you have to transfer all database from “data-oldfiles” folder to “data” folder.

Copy all databases from the data-old files and paste to the data folder.

Now you have to copy the data file “ibdata1” & all log files “ib_logfile0, ib_logfile1 ” from the data-old files folder to the data folder. If you have many id_logiles then copied all of them.

MySQL error This may be due to a blocked port, missing dependencies, improper privileges, a crash, or a shutdown by another method. Start MySQL from XAMPP

Now Start MySQL from XAMPP.

Go to phpMyAdmin to check all databases are available & working.

Answer 2

From Dom comment, and the instruction of expert @Kemmut from WordPress.

https://wordpress.org/support/topic/my-website-down-related-to-mysql-crashed-many-times-in-a-month/#post-13963272

I’ve checked all logs from my LAMPP since last month (PHP, MySQL and Apache).

I summarize the reason of MySQL crashed many times as below:

1. Your are right, in my case, the reason is OS Killed MySQL service because it took a lot of Memory (exceed my server capabilities: 1G RAM).

   MySQL error log:

   2021-01-21  0:50:02 0 [Note] InnoDB: Setting file '/opt/lampp/var/mysql/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
   2021-01-21  0:50:02 0 [Note] InnoDB: File '/opt/lampp/var/mysql/ibtmp1' size is now 12 MB.
   

   PHP error_log:

   [mpm_prefork:error] [pid 11556] (12)Cannot allocate memory: AH00159: fork: Unable to fork new process
   [mpm_prefork:error] [pid 11556] (12)Cannot allocate memory: AH00159: fork: Unable to fork new process
   mmap() failed: [12] Cannot allocate memory
   mmap() failed: [12] Cannot allocate memory
   

2. The reason make MySql took a lot of MEM is: A lot of the unexpected scanning requests came from unknown server (Since last month, my server got the biggest number was 27 request/sec from a single IP !!! ).

   Apache access_log

   "POST /bbs/index.php HTTP/1.1" 302 -
   "POST /forum/index.php HTTP/1.1" 302 -
   "POST /forums/index.php HTTP/1.1" 302 -
   "POST /cgi-bin/php?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758
   "POST /cgi-bin/php5?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758
   "POST /cgi-bin/php-cgi?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758
   "POST /cgi-bin/php.cgi?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758
   "POST /cgi-bin/php4?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758
   "POST /%62%61%73%65/%70%6F%73%74%2E%70%68%70 HTTP/1.1" 302 -
   "GET /webdav/ HTTP/1.1" 302 -
   "GET /%69%73%70%69%72%69%74/%69%6D/%75%70%6C%6F%61%64%2E%70%68%70 HTTP/1.1" 302 -
   "GET /help.php HTTP/1.1" 302 -
   "GET /java.php HTTP/1.1" 302 -
   "GET /_query.php HTTP/1.1" 302 -
   "GET /test.php HTTP/1.1" 302 
   

3. To deal with this situation, there are some free Linux scripts, that based on iptables and ufw from this article that can be used to block that kind of junk request.

   https://vivustandard.com/fix-mysql-stops-or-crashes-randomly/

   • Shell script: block_ip.sh
   • Shell script: remove_ip.sh
   • Shell script: run_anti_ddos.sh

Answer 3

16M is too small for innodb_buffer_pool_size; try 50M. 1GB VM is quite tiny.

Also lower max_connections to 10.