mysql数据库宕机_mysql 数据库宕机问题解决

查看log，内容如下：

vim /var/log/mysqld.log

问题1：

2020-07-30T03:46:12.732170Z 2 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

2020-07-30T03:46:13.431987Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200730 11:46:13

运行：

shell: mysql_upgrade –host=’127.0.0.1′ –port=3306 –user=’root’ –password=”root”

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv OK

mysql.db OK

gogs.upload OK

gogs.user OK

gogs.version OK

gogs.watch OK

gogs.webhook OK

sys.sys_config OK

Upgrade process completed successfully.

Checking if update is needed.

问题2：

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

[[email protected] dea]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show global variables like ‘%timestamp%’;

+———————————+——-+

| Variable_name | Value |

+———————————+——-+

| explicit_defaults_for_timestamp | OFF |

| log_timestamps | UTC |

+———————————+——-+

2 rows in set (0.03 sec)

需要设置成：

vi /etc/my.cnf

explicit_defaults_for_timestamp=1

systemctl restart mysqld

问题3：

2020-07-31T04:06:09.312131Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.31) starting as process 28986 …

2020-07-31T04:06:09.349337Z 0 [Note] InnoDB: PUNCH HOLE support available

2020-07-31T04:06:09.349420Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2020-07-31T04:06:09.352554Z 0 [Note] InnoDB: Uses event mutexes

2020-07-31T04:06:09.352575Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier

2020-07-31T04:06:09.352581Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2020-07-31T04:06:09.352595Z 0 [Note] InnoDB: Using Linux native AIO

2020-07-31T04:06:09.353246Z 0 [Note] InnoDB: Number of pools: 1

2020-07-31T04:06:09.353479Z 0 [Note] InnoDB: Using CPU crc32 instructions

2020-07-31T04:06:09.356005Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2020-07-31T04:06:09.356081Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12

2020-07-31T04:06:09.356103Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

2020-07-31T04:06:09.356115Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

2020-07-31T04:06:09.356129Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.

2020-07-31T04:06:09.356137Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

2020-07-31T04:06:09.356145Z 0 [ERROR] Failed to initialize builtin plugins.

2020-07-31T04:06:09.356154Z 0 [ERROR] Aborting

用free -m看下

total used free shared buff/cache available

Mem: 990 857 60 1 72 26

Swap: 999 734 265

解决方法：

[inonodb]

innodb_buffer_pool_size=64MB 把这个数值改小 高版本的默认是128MB

当然，终极解决办法还是买内存更大的虚拟主机咯。

搞定！

欢迎关注我的微信公众号：

如无特殊说明，文章均为本站原创，转载请注明出处！