Practical record of handling MySQL automatic shutdown problems

I recently helped someone with a project and the MySQL machine kept stopping automatically after a while. At first I thought it stopped unexpectedly, so I didn't pay attention and restarted it manually. But after two days it stopped again.

Later, I carefully checked the mysqld log:

2020-05-27T10:15:12.569342Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.17) started as process 19493
2020-05-27T10:15:14.448256Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2020-05-27T10:15:14.475411Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-05-27T10:15:14.691345Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-05-27T10:15:15.677386Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.17' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution.
2020-05-27T10:15:15.951210Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/lib/mysql/mysqlx.sock' bind-address: '::' port: 33060
2020-05-27T11:26:19.955004Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.17) started as process 19757
2020-05-27T11:26:20.181302Z 0 [ERROR] [MY-012681] [InnoDB] mmap(137363456 bytes) failed; errno 12
2020-05-27T11:26:20.181360Z 1 [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
2020-05-27T11:26:20.181379Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-05-27T11:26:20.181401Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-05-27T11:26:20.181543Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-05-27T11:26:20.183642Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-05-27T11:26:20.184163Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.17) Source distribution.

The above message shows "Cannot allocate memory for the buffer pool". Cannot allocate memory for the buffer pool . I immediately thought of insufficient memory. This machine has 1GB of memory and is running Nginx and PHP-FPM.

Using top, I saw that mysqld was using 48% of the memory. The memory usage is still quite high.

Of course, upgrading the machine configuration is a better solution, but after all, funds are limited. So let's add a swap space to it first:

dd if=/dev/zero of=/swapfile bs=1M count=2048
mkswap /swapfile
swapon /swapfile
systemctl restart mysqld

Above we added 2GB of swap space to the machine. Then restart mysqld. I used top again and found that the swap space was gradually being used.

After a nap, I woke up and found that the mysqld service did not stop automatically, and the memory usage had dropped to 22%. I looked at the error log and it was empty.

Well, it looks good. Just observe for a few more days and if there is no problem, it should be fine😎😎😎.

Well, this is the end of this article about how to handle the problem of MySQL automatic shutdown. For more relevant content about MySQL automatic shutdown handling, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!