Centos7 MySQL ERROR] InnoDB: Cannot allocate memory for the buffer pool

最新推荐文章于 2022-11-06 12:12:24 发布

马克88

最新推荐文章于 2022-11-06 12:12:24 发布

阅读量304

点赞数

分类专栏： Mysql等数据库

本文链接：https://blog.csdn.net/yyws2039725/article/details/109269202

版权

Mysql等数据库专栏收录该内容

24 篇文章 0 订阅

订阅专栏

最近MySQL总是崩溃，错误日志

2020-10-24 19:04:44 18128 [Note] Plugin 'FEDERATED' is disabled.
2020-10-24 19:04:44 18128 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-10-24 19:04:44 18128 [Note] InnoDB: The InnoDB memory heap is disabled
2020-10-24 19:04:44 18128 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-24 19:04:44 18128 [Note] InnoDB: Memory barrier is not used
2020-10-24 19:04:44 18128 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-24 19:04:44 18128 [Note] InnoDB: Using Linux native AIO
2020-10-24 19:04:44 18128 [Note] InnoDB: Using CPU crc32 instructions
2020-10-24 19:04:44 18128 [Note] InnoDB: Initializing buffer pool, size = 384.0M
InnoDB: mmap(412090368 bytes) failed; errno 12
2020-10-24 19:04:44 18128 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-10-24 19:04:44 18128 [ERROR] Plugin 'InnoDB' init function returned error.
2020-10-24 19:04:44 18128 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-24 19:04:44 18128 [ERROR] Unknown/unsupported storage engine: InnoDB
2020-10-24 19:04:44 18128 [ERROR] Aborting

2020-10-24 19:04:44 18128 [Note] Binlog end
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'partition'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_METRICS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_CMP'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'INNODB_TRX'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'ARCHIVE'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'BLACKHOLE'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'MyISAM'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'MRG_MYISAM'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'CSV'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'MEMORY'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'sha256_password'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'mysql_old_password'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'mysql_native_password'
2020-10-24 19:04:44 18128 [Note] Shutting down plugin 'binlog'
2020-10-24 19:04:44 18128 [Note] /www/server/mysql/bin/mysqld: Shutdown complete

2020-10-24 20:58:37 25732 [Note] Plugin 'FEDERATED' is disabled.
2020-10-24 20:58:37 25732 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-10-24 20:58:37 25732 [Note] InnoDB: The InnoDB memory heap is disabled
2020-10-24 20:58:37 25732 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-24 20:58:37 25732 [Note] InnoDB: Memory barrier is not used
2020-10-24 20:58:37 25732 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-24 20:58:37 25732 [Note] InnoDB: Using Linux native AIO
2020-10-24 20:58:37 25732 [Note] InnoDB: Using CPU crc32 instructions
2020-10-24 20:58:37 25732 [Note] InnoDB: Initializing buffer pool, size = 384.0M
2020-10-24 20:58:37 25732 [Note] InnoDB: Completed initialization of buffer pool
2020-10-24 20:58:37 25732 [Note] InnoDB: Highest supported file format is Barracuda.
2020-10-24 20:58:37 25732 [Note] InnoDB: The log sequence numbers 596318639 and 596318639 in ibdata files do not match the log sequence number 618084329 in the ib_logfiles!
2020-10-24 20:58:37 25732 [Note] InnoDB: Database was not shutdown normally!
2020-10-24 20:58:37 25732 [Note] InnoDB: Starting crash recovery.
2020-10-24 20:58:37 25732 [Note] InnoDB: Reading tablespace information from the .ibd files...
2020-10-24 20:58:37 25732 [Note] InnoDB: Restoring possible half-written data pages 
2020-10-24 20:58:37 25732 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 11046821, file name mysql-bin.000026
2020-10-24 20:58:37 25732 [Note] InnoDB: 128 rollback segment(s) are active.
2020-10-24 20:58:37 25732 [Note] InnoDB: Waiting for purge to start
2020-10-24 20:58:37 25732 [Note] InnoDB: 5.6.49 started; log sequence number 618084329
2020-10-24 20:58:37 25732 [Note] Recovering after a crash using mysql-bin
2020-10-24 20:58:37 25732 [Note] Starting crash recovery...
2020-10-24 20:58:37 25732 [Note] Crash recovery finished.
2020-10-24 20:58:37 25732 [Note] RSA private key file not found: /www/server/data//private_key.pem. Some authentication plugins will not work.
2020-10-24 20:58:37 25732 [Note] RSA public key file not found: /www/server/data//public_key.pem. Some authentication plugins will not work.
2020-10-24 20:58:37 25732 [Note] Server hostname (bind-address): '*'; port: 3306
2020-10-24 20:58:37 25732 [Note] IPv6 is available.
2020-10-24 20:58:37 25732 [Note]   - '::' resolves to '::';
2020-10-24 20:58:37 25732 [Note] Server socket created on IP: '::'.
2020-10-24 20:58:37 25732 [Note] Event Scheduler: Loaded 0 events
2020-10-24 20:58:37 25732 [Note] /www/server/mysql/bin/mysqld: ready for connections.
Version: '5.6.49-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
2020-10-24 23:54:29 25732 [Note] /www/server/mysql/bin/mysqld: Normal shutdown

2020-10-24 23:54:29 25732 [Note] Giving 0 client threads a chance to die gracefully
2020-10-24 23:54:29 25732 [Note] Event Scheduler: Purging the queue. 0 events

参考下面文章添加SWAP：

Centos7 MySQL 错误 InnoDB: Cannot allocate memory for the buffer pool

Linux上创建SWAP文件/分区

Centos7 MySQL 错误 InnoDB: Cannot allocate memory for the buffer pool

MySQL 配置了过大的内存导致无法启动:InnoDB: Cannot allocate memory for the buffer pool

Linux查看和释放swap内存

Linux内存机制以及手动释放swap和内存

Linux下创建、销毁、使用 SWAP（转）

修改linux swap空间的swappiness，降低对硬盘的缓存

Linux SWAP交换分区应该设置多大为好？

cd /var/

添加SWAP文件快，2GB
dd if=/dev/zero of=swapfile bs=1M count=2048

创建SWAP文件
/sbin/mkswap swapfile

激活SWAP文件
/sbin/swapon swapfile

出现：swapon: swapfile: insecure permissions 0644, 0600 suggested.
把swap设置成644或600权限。
chmod 600 swapfile

提示swapon: /mnt/swap Device or resource busy
需要先关闭之前的swap分区，再重新分配，关闭命令：
swapoff -a


查看SWAP信息是否正确
swapon -s
[root@iZwz96gkli5z588poe7umpZ ~]# swapon -s
Filename                                Type            Size    Used    Priority
/var/swapfile                           file    2097148 0       -2


free -m 或者 spapon -s
free -m|grep Swap|awk '{print $3}'
返回0代表未开启（绝大多数），有数代表已开启


添加到fstab文件
echo "/var/swapfile swap swap defaults 0 0" >>/etc/fstab

完事了，free -h 可查看内存和swap情况。