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).

[root@deaboway 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

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

搞定！

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

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