Shulou(Shulou.com)06/01 Report--

This article mainly explains the example analysis of the problem of MySQL automatic downtime, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Recently, I did a project for someone else. MySQL always stops running on the machine after a while. At first, I thought it was stopped outside, and I didn't notice it, so I started it manually. But it stopped in less than two days.

Later, I checked mysqld's log carefully:

2020-05-27T10:15:12.569342Z 0 [System] [MY-010116] [Server] / usr/libexec/mysqld (mysqld 8.0.17) starting 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) starting 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.

It shows Cannot allocate memory for the buffer pool and cannot allocate memory to the cache pool. I immediately thought that there was not enough memory. This machine is the memory of 1GB, and it is also running Nginx and PHP-FPM.

Using top to see that mysqld occupies 48% of the memory. The memory footprint is quite high.

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

Dd if=/dev/zero of=/swapfile bs=1M count=2048mkswap / swapfileswapon / swapfilesystemctl restart mysqld

We added 2GB swap space to the machine. Then restart mysqld. After using top, I found that the swap space was gradually being used.

After a nap, the mysqld service no longer stops automatically, and the memory usage has dropped to 22%. After looking at the error log, it is also empty.

Well, it looks good. Just watch it for a few more days. It should be fine if there's no problem.

After reading the above content, do you have a further understanding of the example analysis of the problem of MySQL automatic downtime? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.