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

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

搞定!

CentOS 7上MySQL 5.7.18莫名其妙Crash原因分析及解决

一、Centos中查看nginx、apache、php、mysql配置文件路径

1、判断apache
首先执行命令找到httpd路径
ps aux | grep httpd
如httpd路径为 /usr/local/apache/bin/httpd
然后执行以下命令
/usr/local/apache/bin/httpd -V | grep “SERVER_CONFIG_FILE”
即可找到编译时加载的配置文件路径 httpd.conf
-V 参数可以看到编译时配置的参数

继续阅读“CentOS 7上MySQL 5.7.18莫名其妙Crash原因分析及解决”