Amazon EC2，mysql 中止启动，因为 InnoDB: mmap (x bytes) failed；错误号 12

Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12

提问by pmoubed

I have set up a microinstance server on EC2 based on what I read here

我已经根据我在此处阅读的内容在 EC2 上设置了一个微型实例服务器

mysql server fails frequently and for the third time mysql server is gone. The logs only shows

mysql 服务器频繁出现故障，第三次 mysql 服务器消失了。日志只显示

120423 09:13:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120423 09:14:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120423  9:14:27 [Note] Plugin 'FEDERATED' is disabled.
120423  9:14:27 InnoDB: The InnoDB memory heap is disabled
120423  9:14:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120423  9:14:27 InnoDB: Compressed tables use zlib 1.2.3
120423  9:14:27 InnoDB: Using Linux native AIO
120423  9:14:27 InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(549453824 bytes) failed; errno 12
120423  9:14:27 InnoDB: Completed initialization of buffer pool
120423  9:14:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool
120423  9:14:27 [ERROR] Plugin 'InnoDB' init function returned error.
120423  9:14:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120423  9:14:27 [ERROR] Unknown/unsupported storage engine: InnoDB
120423  9:14:27 [ERROR] Aborting

What is really failed; errno 12? And how could I give more space/memory or whatever needed to make this fixed.

什么是真的failed; errno 12？我怎么能提供更多的空间/内存或任何需要的东西来解决这个问题。

I fix this each time by rebooting the whole system and deleting all logs and restart the mysql server. But I know something is wrong with my configuration.

我每次都通过重新启动整个系统并删除所有日志并重新启动 mysql 服务器来解决此问题。但我知道我的配置有问题。

Also my `my.cnf' is like below :

我的“my.cnf”也如下所示：

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
# max_allowed_packet=500M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


innodb_buffer_pool_size         = 512M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

回答by Bohr

I met the same problem when I tried to run a wordpress on my micro instance without RDS.

当我尝试在没有 RDS 的微型实例上运行 wordpress 时，我遇到了同样的问题。

Adding a Swap page solved the problem for me.

添加一个交换页面为我解决了这个问题。

You can follow this to setup the swap page:

您可以按照此设置交换页面：

http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html

http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html

If it still doesn't work for you, consider using the RDS service.

如果它仍然不适合您，请考虑使用 RDS 服务。

===============================================

================================================

The link to the blog sometimes fails. I copied the content below for the record. Credit goes to the blog author Pedram Moubed:

博客链接有时会失败。我复制了以下内容以作记录。归功于博客作者Pedram Moubed：

Amazon EC2 Micro Instance Swap Space - Linux

Amazon EC2 微型实例交换空间 - Linux

I have a Amazon EC2 Linux Micro instance. Since Micro instances have only 613MB of memory, MySQL crashed every now and then. After a long search about MySQL, Micro Instance and Memory Managment I found out there is no default SWAP space for Micro instance. So if you want to avoid the crash you may need to setup a swap space for your micro instance. Actually performance wise is better to enable swap.

我有一个 Amazon EC2 Linux Micro 实例。由于微型实例只有 613MB 的内存，MySQL 时不时崩溃。经过对 MySQL、微实例和内存管理的长时间搜索，我发现微实例没有默认的 SWAP 空间。因此，如果您想避免崩溃，您可能需要为您的微型实例设置一个交换空间。实际上，在性能方面最好启用交换。

Steps below show how to make a swap space for your Micro instance. I assume you have AWS Account with a Micro instance running.

以下步骤显示了如何为您的 Micro 实例创建交换空间。我假设您有一个运行 Micro 实例的 AWS 账户。

1. Run dd if=/dev/zero of=/swapfile bs=1M count=1024
2. Run mkswap /swapfile
3. Run swapon /swapfile
4. Add this line /swapfile swap swap defaults 0 0to /etc/fstab

1. 跑 dd if=/dev/zero of=/swapfile bs=1M count=1024
2. 跑 mkswap /swapfile
3. 跑 swapon /swapfile
4. 将此行添加/swapfile swap swap defaults 0 0到/etc/fstab

Step 4 is needed if you would like to automatically enable swap file after each reboot.

如果您想在每次重新启动后自动启用交换文件，则需要执行第 4 步。

Some useful command related to SWAP space:

一些与 SWAP 空间相关的有用命令：

$ swapon -s   
$ free -k

$ swapoff -a
$ swapon  -a

References:

参考：

1. http://www.thegeekstuff.com/2010/08/how-to-add-swap-space/
2. http://cloudstory.in/2012/02/getting-the-best-out-of-amazon-ec2-micro-instances/
3. http://cloudstory.in/2012/02/adding-swap-space-to-amazon-ec2-linux-micro-instance-to-increase-the-performance/
4. http://aws.amazon.com/ec2/instance-types/

1. http://www.thegeekstuff.com/2010/08/how-to-add-swap-space/
2. http://cloudstory.in/2012/02/getting-the-best-out-of-amazon-ec2-micro-instances/
3. http://cloudstory.in/2012/02/adding-swap-space-to-amazon-ec2-linux-micro-instance-to-increase-the-performance/
4. http://aws.amazon.com/ec2/instance-types/

回答by wfbarksdale

I had this problem too on an Amazon EC2 micro instance. I tried decreasing inno_db's memory usage by adding the following to /etc/my.cnf

我在 Amazon EC2 微型实例上也遇到了这个问题。我尝试通过添加以下内容来减少 inno_db 的内存使用量/etc/my.cnf

innodb_buffer_pool_size = 64M

That didn't work, I tried dropping it down to 16M and it still didnt work. Then I realized that the instance had basically zero free memory. So I tried restarting apache

那没有用，我尝试将其降低到 16M，但仍然无效。然后我意识到该实例的空闲内存基本上为零。所以我尝试重新启动apache

sudo system httpd restart
sudo system mysqld restart

And everything worked fine. Maybe another solution is to configure apache to not eat up so much memory somehow.

一切正常。也许另一种解决方案是将 apache 配置为不以某种方式占用这么多内存。

回答by Eric J.

It looks like you are requesting 128Mof memory for the innodb_buffer_pool_size in the my.cfg file you show in the post, but MySQL thinks you are asking for 512Mof memory:

看起来您在帖子中显示的 my.cfg 文件中为 innodb_buffer_pool_size请求128M内存，但 MySQL 认为您请求的是512M内存：

Initializing buffer pool, size = 512.0M

初始化缓冲池，大小 = 512.0M

A few lines down, the error message tells you MySQL will not start because it cannot reserve enough (512M) memory for the InnoDB buffer pool:

向下几行，错误消息告诉您 MySQL 将无法启动，因为它无法为 InnoDB 缓冲池预留足够的 (512M) 内存：

Fatal error: cannot allocate memory for the buffer pool

致命错误：无法为缓冲池分配内存

That begs three questions:

这就引出了三个问题：

1. How much memory is on your instance? Should there be enough memory to accommodate the 512M InnoDB is trying to grab for the buffer pool, plus everything else MySQL allocates, plus your application(s), plus the operating system?
2. Why is InnoDB trying to take more than you think it should?
3. Why is MySQL restarting anyhow?

1. 你的实例有多少内存？是否应该有足够的内存来容纳 512M InnoDB 试图为缓冲池获取的内存，以及 MySQL 分配的所有其他内容，以及您的应用程序和操作系统？
2. 为什么 InnoDB 试图采取比您认为应该的更多的措施？
3. 为什么 MySQL 会重新启动？

You can answer 1.

你可以回答1。

As to 2., there are a few different places MySQL option files can be located. Subsequently found files override options specified in previously found files. See

至于 2.，有几个不同的地方可以找到 MySQL 选项文件。随后找到的文件覆盖先前找到的文件中指定的选项。看

http://dev.mysql.com/doc/refman/5.5/en/option-files.html

http://dev.mysql.com/doc/refman/5.5/en/option-files.html

Issue 3. could be due to an out of memory condition that occurs sometime after startup. You should see an indication of that further back in the logs if that is the case.

问题 3. 可能是由于启动后某个时间发生的内存不足情况。如果是这种情况，您应该会在日志中进一步看到该指示。

Finally, but somewhat unrelated, are you using EBS backed instances? That's generally highly recommended for database servers (actually, for any instance barring special circumstances). For more on that see

最后，但有些无关，您是否使用 EBS 支持的实例？对于数据库服务器，通常强烈建议这样做（实际上，对于任何实例，除非特殊情况）。有关更多信息，请参阅

https://stackoverflow.com/a/3630707/141172

https://stackoverflow.com/a/3630707/141172

回答by Joel

For me, this exactly problem was rectified by adding a swap volume to my EC2 instance. My services were simply consuming all the memory on the box, and would crash. Not something I was used to, being a RedHat/CentOS admin for years - Anaconda does a LOT of work that the free Ubuntu EC2 instance does not.

对我来说，通过向我的 EC2 实例添加交换卷来纠正这个问题。我的服务只是消耗了盒子上的所有内存，并且会崩溃。我不习惯，多年来一直是 RedHat/CentOS 管理员 - Anaconda 做了很多免费的 Ubuntu EC2 实例没有的工作。

I simply created a 2Gb volume through the web console, attached it to my instance, and did "mkswap /dev/[whatever]", edited /etc/fstab, and the crashing stopped.

我只是通过 Web 控制台创建了一个 2Gb 卷，将其附加到我的实例，然后执行“mkswap /dev/[whatever]”，编辑 /etc/fstab，并且崩溃停止了。

These instances do NOT install like a media-based OS install that most of us are used to - it's stripped bare with no packages, no proper filesystem, and things like AppArmor, which cause all kinds of problems if you aren't aware of it and/or don't know how to configure it.

这些实例不像我们大多数人习惯的基于媒体的操作系统安装那样安装 - 它被剥离，没有包，没有适当的文件系统，以及 AppArmor 之类的东西，如果你不知道它会导致各种问题和/或不知道如何配置它。

回答by Ranjeet Ranjan

The problem is that the server does not have enough memory to allocate for MySQL process. There are a few solutions to this problem.

问题是服务器没有足够的内存来分配给 MySQL 进程。这个问题有几个解决方案。

(1) Increase the physical RAM. Adding 1GB of additional RAM will solve the problem. (2) Allocate SWAP space. Digital Ocean VPS instance is not configured to use swap space by default. By allocating 512MB of swap space, we were able to solve this problem. To add swap space to your server, please follow the following steps:

(1) 增加物理内存。添加 1GB 的额外 RAM 将解决该问题。(2)分配SWAP空间。Digital Ocean VPS 实例默认没有配置为使用交换空间。通过分配 512MB 的交换空间，我们能够解决这个问题。要将交换空间添加到您的服务器，请按照以下步骤操作：

## As a root user, perform the following:
# dd if=/dev/zero of=/swap.dat bs=1024 count=512M
# mkswap /swap.dat
# swapon /swap.dat
## Edit the /etc/fstab, and the following entry.
/swap.dat      none    swap    sw      0       0 

Reduce the size of MySQL buffer pool size

减小 MySQL 缓冲池大小

## Edit /etc/my.cnf, and add the following line under the [mysqld] heading.
[mysqld]
innodb_buffer_pool_size=64M

Also please check your Disk Space. Make sure you have sufficient space.

另外请检查您的磁盘空间。确保您有足够的空间。

df-h

回答by jonnyjandles

EASY ANSWER:

简单回答：

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

DETAILED ANSWER:

详细回答：

This is an important question especially for people who are using a very small VPS, say 1GB of RAM or less. If MySQL is dropping out, it may be a problem with your server configuration (Apache | nginx) or MySQL configuration. DOS attacks may cause an increased spike in system resource usages (see image). The end result is that MySQL process gets shutdown by the Kernel. For a long-term solution should look at optimizing your Apache or MySQL configurations.

这是一个重要的问题，特别是对于使用非常小的 VPS 的人来说，比如 1GB 的 RAM 或更少。如果 MySQL 退出，则可能是您的服务器配置 (Apache | nginx) 或 MySQL 配置有问题。DOS 攻击可能会导致系统资源使用量激增（见图）。最终结果是 MySQL 进程被内核关闭。对于长期解决方案，应该考虑优化您的 Apache 或 MySQL 配置。

There are several other discussions Stack Overflow those topics as well as the MySQL manual and Percona Blog:

还有其他几个讨论 Stack Overflow 这些主题以及 MySQL 手册和 Percona 博客：

MySQL Manual - How MySQL Uses Memory:

MySQL 手册 - MySQL 如何使用内存：

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

Percona - Best Practices for Configuring Optimal MySQL Memory Usage:

Percona - 配置最佳 MySQL 内存使用的最佳实践：

https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/

https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/

How to Optimize MySQL Performance Using MySQLTuner:

如何使用 MySQLTuner 优化 MySQL 性能：

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

Apache Memory Usage Configuration:

Apache 内存使用配置：

https://serverfault.com/questions/254436/apache-memory-usage-optimization

https://serverfault.com/questions/254436/apache-memory-usage-optimization

Apache Manual on Performance Tuning:

Apache 性能调优手册：

https://httpd.apache.org/docs/2.4/misc/perf-tuning.html

https://httpd.apache.org/docs/2.4/misc/perf-tuning.html

Tuning Apache Server:

调整 Apache 服务器：

https://www.linode.com/docs/web-servers/apache-tips-and-tricks/tuning-your-apache-server/

https://www.linode.com/docs/web-servers/apache-tips-and-tricks/tuning-your-apache-server/

However, with respect to your original question, yes, you can script a temporary solution that checks if MySQL service is loaded and active and will restart MySQL if it is not loaded and active.

但是，对于您最初的问题，是的，您可以编写一个临时解决方案来检查 MySQL 服务是否已加载并处于活动状态，如果未加载并处于活动状态，则将重新启动 MySQL。

You did not mention what operating system you are using. That would help to give you a specific command. I will give you an example for CentOS linux.
Look at the following output of the command systemctl status mysql. You can see at the top that the service is loadedand active.

你没有提到你使用的是什么操作系统。这将有助于为您提供特定命令。我会给你一个 CentOS linux 的例子。
查看命令的以下输出systemctl status mysql。您可以在顶部看到该服务已加载并处于活动状态。

[root@centos-mysql-demo ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-06-18 18:28:18 UTC; 924ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3350 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3273 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3353 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3353 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jun 18 18:28:11 centos-mysql-demo systemd[1]: Starting MySQL Server...
Jun 18 18:28:18 centos-mysql-demo systemd[1]: Started MySQL Server.

If the service is not loaded, then a command such as:

如果服务未加载，则使用如下命令：

systemctl status mysqld || systemctl restart mysqld 

will do the trick of re-starting the process. You couldcron that:

将重新开始这个过程。你可以cron：

* * * * * systemctl status mysqld || systemctl restart mysqld

However, in the case that mysql is loaded, but the service is not active, your cron will do nothing. So, you should use a more detailed command such as:

但是，在 mysql 已加载但服务未激活的情况下，您的 cron 将不执行任何操作。因此，您应该使用更详细的命令，例如：

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

In this case, if the service is loadedbut inactivesuch as the state that a DOS attack can leave your mysql service, the command will also restart mysql. Using the --quietflag just specifies the command only to return a status code, not output anything to the screen. If you ommit the --quietflag you will see a status output of either activeor inactive.

在这种情况下，如果服务已加载但处于非活动状态，例如 DOS 攻击可以离开您的 mysql 服务的状态，该命令还将重新启动 mysql。使用该--quiet标志仅指定命令仅返回状态代码，而不向屏幕输出任何内容。如果省略该--quiet标志，您将看到状态输出为active或inactive。

You may also create some swap space to add more available RAM resources to your server such as:

您还可以创建一些交换空间来为您的服务器添加更多可用的 RAM 资源，例如：

sudo dd if=/dev/zero of=/swapfile count=2096 bs=1MiB
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
swapon --show
swapon --summary
free -h

回答by cloud_geek

Use anyone of the following solution:

使用以下任何一种解决方案：

1. Increase the physical RAM. Adding 1GB of additional RAM will solve the problem.

2. Allocate SWAP space by using the config changes below:

1. 增加物理内存。添加 1GB 的额外 RAM 将解决该问题。

2. 使用以下配置更改分配 SWAP 空间：

config

配置

dd if=/dev/zero of=/extraswap bs=1024 count=512M
mkswap  /extraswap 
swapon  /extraswap 
## Edit the /etc/fstab, and the following entry.
/extraswap      none    swap    sw      0       0