星期六, 2017-11-11 02:14 — adminshiping1

周三一早回到公司，收到报警发现一台MySQL服务器异常，登入发现无法启动。该机器仅供临时测试使用，故此没有太多配置，查看err日志中发现

130629  5:46:48 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
130629  5:46:48 InnoDB: Completed initialization of buffer pool
130629  5:46:48 InnoDB: Fatal error: cannot allocate memory for the buffer pool
130629  5:46:48 [ERROR] Plugin 'InnoDB' init function returned error.
130629  5:46:48 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130629  5:46:48 [ERROR] Unknown/unsupported storage engine: InnoDB
130629  5:46:48 [ERROR] Aborting

查看内存

$ free
             total       used       free     shared    buffers     cached
Mem:        608476     565044      43432          0       1284      53996
-/+ buffers/cache:     509764      98712Swap:            0          0          0

好吧，明显的swap问题。适当增加swap，采用 (swap 大小建议与内存一样大)

$ sudo dd if=/dev/zero of=/swapfile bs=1M count=1024 #增加1G的SWAP进去
$ sudo mkswap /swapfile  ( 或者 sudo mkswap -f /swapfile )

$ sudo swapon /swapfile
$ free
             total       used       free     shared    buffers     cached
Mem:        608476     601432       7044          0        824      88752
-/+ buffers/cache:     511856      96620
Swap:      1048572          0    1048572

此处不建议将swapon 写到/etc/fstab这里,直接写到/etc.rc.local中更为稳妥

还是添加这行到/etc/fstab 文件吧： /swapfile swap swap defaults 0 0 到 /etc/fstab

启动解决...

可以见 /node-admin/11705 有大用

来自 https://www.cnblogs.com/olinux/p/5144205.html

阿里云vps上mysql挂掉的解决办法

8条回复

用阿里云的vps用作blog服务器，系统很稳定，已经100多天一直运行正常，大概从上个月开始发现blog的mysql会有时挂掉，会收到短信通知。之前没太追究，重新启动了mysql解决的。今天上午又收到短信，已经第三次了。

查了一下日志，三次基本都是一样的：

130728  6:50:14 [Note] Plugin 'FEDERATED' is disabled.
130728  6:50:14 InnoDB: The InnoDB memory heap is disabled
130728  6:50:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130728  6:50:14 InnoDB: Compressed tables use zlib 1.2.3.4
130728  6:50:14 InnoDB: 
InnoDB: mmap(137363456 bytes) failed; errno 12
130728  6:50:14 InnoDB: Completed initialization of buffer pool
130728  6:50:14 InnoDB: Fatal error: cannot allocate memory for the buffer pool
130728  6:50:14 [ERROR] Plugin 'InnoDB' init function returned error.
130728  6:50:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130728  6:50:14 [ERROR] Unknown/unsupported storage engine: InnoDB
130728  6:50:14 [ERROR] Aborting
130728  6:50:14 [Note] /usr/sbin/mysqld: Shutdown complete

解决方法：
1) 在 /etc/mysql/my.cnf 的 mysqld 下增加下面一句：

innodb_buffer_pool_size = 64M

还要设置一下swap分区，因为我的vps是没有swap分区的，通过fdisk -l 和 1mount 看不到swap的信息，需要手动添加一下。

2) 添加swap分区的步骤：

2.1) dd if=/dev/zero of=/swapfile bs=1M count=1024
2.2) mkswap /swapfile
2.3) swapon /swapfile
2.4) 添加这行： /swapfile swap swap defaults 0 0 到 /etc/fstab

目前已经设置了swap分区，并重启了mysql，后续观察一下看看还会不会出现吧。

参考：http://stackoverflow.com/questions/10284532/amazon-ec2-mysql-aborting-start-because-innodb-mmap-x-by...

补充，经过近2个月观察，没再发生down掉的情况。

来自 http://hongjiang.info/aliyun-vps-mysql-aborting/

mysql运行一段时间后自动停止 10

140118 14:17: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.
140118 14:17:30 [Note] Plugin 'FEDERATED' is disabled.
140118 14:17:30 InnoDB: The InnoDB memory heap is disabled
140118 14:17:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140118 14:17:30 InnoDB: Compressed tables use zlib 1.2.3.4
140118 14:17:30 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
140118 14:17:30 InnoDB: Completed initialization of buffer pool
140118 14:17:30 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140118 14:17:30 [ERROR] Plugin 'InnoDB' init function returned error.
140118 14:17:30 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140118 14:17:30 [ERROR] Unknown/unsupported storage engine: InnoDB
140118 14:17:30 [ERROR] Aborting

140118 14:17:30 [Note] /usr/sbin/mysqld: Shutdown complete

最佳答案

1、根据你警告得知:   内存不够用，启动不了MySQL
2、再看看当前的内存使用情况：free -m
3、把“innodb_buffer_pool_size”改小，重启就可以了。

来自 https://zhidao.baidu.com/question/303780951548443244.html

up vote32down vote

Use 50% of available RAM to test:

You can decrease the innodb_buffer_pool_size very low to see if it helps:

#/etc/my.cnf 
innodb_buffer_pool_size = 1M

A rule of thumb is to set innodb_buffer_pool_size to 50% of available RAM for your low memory testing. This means you start the server and everything except MySQL InnoDB. See how much RAM you have. Then use 50% of that for InnoDB.

To try many low-memory settings at once:

下面的有大用有大大用

[SOLVED] MySQL Crash with Fatal error: cannot allocate memory for the buffer pool

posted on 2014-05-23

We have a dozen websites mostly running Wordpress, and one Drupal website on Digital Ocean with a 1GB instance. The server was running flawlessly for 6 months, and then the MySQL starts to crash every few days unexpectly with the following error in the mysql error log.

140416 11:37:24 mysqld_safe Number of processes running now: 0
140416 11:37:24 mysqld_safe mysqld restarted
140416 11:37:24 [Note] Plugin 'FEDERATED' is disabled.
140416 11:37:24 InnoDB: The InnoDB memory heap is disabled
140416 11:37:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140416 11:37:24 InnoDB: Compressed tables use zlib 1.2.3
140416 11:37:24 InnoDB: Using Linux native AIO
140416 11:37:24 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
140416 11:37:24 InnoDB: Completed initialization of buffer pool
140416 11:37:24 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140416 11:37:24 [ERROR] Plugin 'InnoDB' init function returned error.
140416 11:37:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140416 11:37:24 [ERROR] Unknown/unsupported storage engine: InnoDB
140416 11:37:24 [ERROR] Aborting

Upon trying to restart the MySQL service, we are getting the following error:

# service mysqld start
ERROR 2002 (HY000): Can't connect to local MySQL server 
through socket '/var/run/mysqld/mysqld.sock' (111)

Solution:

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

(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:

## 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

(3) Reduce the size of MySQL buffer pool size

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

Restart mysql and you're good to go. You'll have to continuously monitor MySQL process for a week or two to ensure that the server is running without memory allocation problem.

TAGS:

sql

mysql

Add new comment

Comments

Thank you.

Permalink On Thu, 10/16/2014 - 12:49

Thank you.
I did have this problem exactly on DigitalOcean Droplet and solved it with the help of this article.

By Alex (not verified)

# dd if=/dev/zero of=/swap

Permalink On Sat, 11/01/2014 - 17:45

# dd if=/dev/zero of=/swap.dat bs=1024 count=512M

should be 512k, thanks

By scott lu (not verified)

Great article - saved me

Permalink On Wed, 11/19/2014 - 09:09

Great article - saved me loads of time.

Cheers dude!

By TomVoxsen (not verified)

Wow, 512g swap!!!! Must be a

Permalink On Thu, 12/04/2014 - 11:20

Wow, 512g swap!!!! Must be a crazy WordPress install.

By sudopeople (not verified)

I think this is greate and

Permalink On Tue, 01/06/2015 - 23:34

I think this is greate and easy article but i am done something wrong
dd if=/dev/zero of=/swap.dat bs=1024 count=512M , i just used this comment it take long time so i cancel the running process and it shows me :
^C10054281+0 records in
10054281+0 records out
10295583744 bytes (10 GB) copied, 154.276 s, 66.7 MB/s
where can i find the copied files and how can i remove that, thanks.

By dev (not verified)

Add new comment

来自 http://www.webtrafficexchange.com/solved-mysql-crash-fatal-error-cannot-allocate-memory-buffer-pool

普通分类:

mysql

You are here

MySQL测试环境遇到 mmap(xxx bytes) failed; errno 12解决方法 InnoDB: Initializing buffer pool, size = 128.0M Fatal error: cannot allocate memory for the buffer pool 有大用有大大用有大大大用

阿里云vps上mysql挂掉的解决办法

mysql运行一段时间后自动停止 10

Use 50% of available RAM to test:

[SOLVED] MySQL Crash with Fatal error: cannot allocate memory for the buffer pool

Solution:

Comments

Thank you.

# dd if=/dev/zero of=/swap

Great article - saved me

Wow, 512g swap!!!! Must be a

I think this is greate and

Add new comment

友情链接

搜索表单

用户登录

You are here

MySQL测试环境遇到 mmap(xxx bytes) failed; errno 12解决方法 InnoDB: Initializing buffer pool, size = 128.0M Fatal error: cannot allocate memory for the buffer pool 有大用 有大大用 有大大大用

mysql运行一段时间后自动停止 10

Use 50% of available RAM to test:

[SOLVED] MySQL Crash with Fatal error: cannot allocate memory for the buffer pool

Solution:

Comments

Thank you.

# dd if=/dev/zero of=/swap

Great article - saved me

Wow, 512g swap!!!! Must be a

I think this is greate and

Add new comment

友情链接

MySQL测试环境遇到 mmap(xxx bytes) failed; errno 12解决方法 InnoDB: Initializing buffer pool, size = 128.0M Fatal error: cannot allocate memory for the buffer pool 有大用有大大用有大大大用