解决AWS上时常崩溃的MySQL

本文分享如何解决AWS上时常崩溃的MySQL,查看错误日志,提示表崩溃,以为修复下就可以,但问题依旧,最后通过为AWS实例创建一个交换空间(swap space)彻底解决了该问题。

1. 问题描述

最近,网站经常提示“建立数据库连接时出错”,第一反应就是mysql数据库出问题了,果真如此,因为phpmyadmin后台都登录不了,提示如下错误:

#2002 Cannot log in to the MySQL server

Connection for controluser as defined in your configuration failed.

想到的解决的办法是,重启mysql服务,如下:

$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                                                                                                [ OK ] 
 * Checking for tables which need an upgrade, are corrupt or were not closed cleanly.

也的确有效,博客可以正常搜索,phpmyadmin也能登录了。但很快,mysql又崩溃了。

2. Table is marked as crashed and should be repaired

于是就想着查看下mysql错误日志文件。在mysql配置文件(vim /etc/mysql/my.cnf)查看错误日志文件的存放地方:

log_error = /var/log/mysql/error.log

打开错误日志文件,在文件的末尾看了这两行:

150904 14:33:44 [ERROR] /usr/sbin/mysqld: Table './wordpress/wp_redirection_404' is marked as crashed and should be repaired
150904 14:33:44 [Warning] Checking table:   './wordpress/wp_redirection_404'

可见是wp_redirection_404表出问题,解决方法:登录phpmyadmin后台管理(http://sparkandshine.net/phpmyadmin),选择wp_redirection_404,执行Repair table操作,如下图所示:

mysql_repair_table

好了,现在可以了。

3. InnoDB: cannot allocate memory for the buffer pool

但MySQL很快又崩溃了,再次查看错误日志文件,看到如下内容:

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

150904 15:01:47 [Note] /usr/sbin/mysqld: Shutdown complete

在网上搜了下,最后在这里找到了解决方法,即为AWS实例创建一个交换空间(swap space)。进行如下操作:

$ sudo dd if=/dev/zero of=/swapfile bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 15.0239 s, 71.5 MB/s

$ sudo mkswap /swapfile
Setting up swapspace version 1, size = 1048572 KiB
no label, UUID=c6e62d14-65e9-422d-ab18-14833437cf17

ubuntu@ip-172-31-24-13:~$ sudo swapon /swapfile

#将下一行加入到文件/etc/fstab的末尾
/swapfile swap swap defaults 0 0 #加到文件/etc/fstab末尾,(机器重启,自动启用swapfile)

参考资料:

[1]StackOverflow: Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12
[2]Blog: Amazon EC2 Micro Instance Swap Space – Linux

专题: AWS上建立wordpress站点 (7/8)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

One thought on “解决AWS上时常崩溃的MySQL