云服务器 mysql经常自动停止挂掉重启

mysql经常自动停止挂掉重启

原文引用：https://blog.youkuaiyun.com/xuz0917/article/details/79100834

2019-09-05 22:20:19 [HikariPool-1 connection adder] [DEBUG] com.zaxxer.hikari.pool.HikariPool.createPoolEntry:487 -HikariPool-1 - Cannot acquire connection from data source
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:827)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:447)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
    at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:706)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:692)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

 优化方案如下：

1.创建 SWAP 分区【该方法亲测好使】

检查系统Swap文件信息

sudo swapon -s
free -m

检查硬盘可用空间

df -h

创建Swap文件

sudo /bin/dd if=/dev/zero of=/var/swap.1 bs=1M count=1024

激活Swap

sudo /sbin/mkswap /var/swap.1
sudo /sbin/swapon /var/swap.1

使Swap在主机重启后依然生效

sudo nano /etc/fstab
加入以下内容:
/var/swap.1 swap swap defaults 0 0

重启

reboot

2.linux下修改/etc/my.cnf文件：vi /etc/my.cnf

降低 InnoDB 缓冲区大小为 64M 或者 32M（到mysql配置文件中修改）

innodb_buffer_pool_size=64M

限制最大连接数为100，在服务器配置很低时可以继续降低

max_connections=100

重启mysql服务

systemctl restart mysqld.service

完整如下

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]

#设置默认时区
default-time-zone = '+8:00'

# 设置3306端口
port=3306

# 允许最大连接数
max_connections=15

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
# character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]

# 设置mysql客户端默认字符集
default-character-set=utf8

[client]

# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 64M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

同时修改程序中application.properties文件

增加以下内容

spring.datasource.max-active=50
#是否在自动回收超时连接的时候打印连接的超时错误
spring.datasource.log-abandoned=true
#是否自动回收超时连接
spring.datasource.remove-abandoned=true
##<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
spring.datasource.max-wait=1000
spring.datasource.test-while-idle=true
#检测数据库的查询语句
spring.datasource.validation-query=select 1 from dual
spring.datasource.test-on-borrow=true
#每隔五分钟检测空闲超过10分钟的连接
spring.datasource.min-evictable-idle-time-millis=600000
spring.datasource.time-between-eviction-runs-millis=300000
#服务器配置较低时，需要调整，连接池配置
#Hikari will use the above plus the following to setup connection pooling
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=12
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=true

applicati.properties

#Thymeleaf路径
spring.thymeleaf.prefix=classpath:/templates/
#Thymeleaf后缀
spring.thymeleaf.suffix=.html
#Thymeleaf编码
spring.thymeleaf.encoding=UTF-8
spring.thymeleaf.mode=HTML5

#JDBC数据库配置
spring.datasource.initialization-mode=never
spring.datasource.url=jdbc:mysql://49.235.145.140:3306/kpy
spring.datasource.username=root
spring.datasource.password=199489
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.max-active=50
#是否在自动回收超时连接的时候打印连接的超时错误
spring.datasource.log-abandoned=true
#是否自动回收超时连接
spring.datasource.remove-abandoned=true
##<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
spring.datasource.max-wait=1000
spring.datasource.test-while-idle=true
#检测数据库的查询语句
spring.datasource.validation-query=select 1 from dual
spring.datasource.test-on-borrow=true
#每隔五分钟检测空闲超过10分钟的连接
spring.datasource.min-evictable-idle-time-millis=600000
spring.datasource.time-between-eviction-runs-millis=300000
#服务器配置较低时，需要调整，连接池配置
#Hikari will use the above plus the following to setup connection pooling
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=12
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=true

# 服务器HTTP端口
server.port= 8080