MYSQL 每 5-10 分钟重启一次

MYSQL 每 5-10 分钟重启一次

服务器配置为 CentOS、RAM:40GB、16 核。

我们甚至尝试重新安装 MYSQL 服务器。

5.7.34——MySQL

PHP 版本:7.3.27

我的mysql_error.log:

       2021-05-28T09:54:43.155971Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-05-28T09:54:43.156581Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-28T09:54:43.156623Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-05-28T09:54:43.156714Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-05-28T09:54:43.156745Z 0 [Note] IPv6 is available.
2021-05-28T09:54:43.156754Z 0 [Note]   - '::' resolves to '::';
2021-05-28T09:54:43.156762Z 0 [Note] Server socket created on IP: '::'.
2021-05-28T09:54:43.175386Z 0 [Note] Event Scheduler: Loaded 0 events
2021-05-28T09:54:43.175546Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.34'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2021-05-28T09:54:43.214578Z 2 [Warning] IP address '192.168.1.54' could not be resolved: Name or service not known
2021-05-28T09:54:43.222613Z 3 [Note] Access denied for user 'Cpanel::MysqlUtils::Unprivileged'@'localhost' (using password: NO)
2021-05-28T09:54:43.629749Z 7 [Warning] Host name 'abts-mp-dynamic-085.253.70.182.airtelbroadband.in' could not be resolved: Name or service not known
2021-05-28T09:54:43.725291Z 8 [Warning] IP address '157.34.61.0' could not be resolved: Name or service not known
2021-05-28T09:54:43.769081Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210528 15:24:43
2021-05-28T09:54:43.863610Z 10 [Warning] IP address '157.34.2.178' could not be resolved: Name or service not known
2021-05-28T09:54:43.926723Z 11 [Warning] IP address '157.34.213.66' could not be resolved: Name or service not known
2021-05-28T09:54:44.032514Z 12 [Warning] IP address '157.34.213.66' could not be resolved: Name or service not known
2021-05-28T09:54:45.316688Z 18 [Warning] IP address '157.34.57.161' could not be resolved: Name or service not known
2021-05-28T09:54:46.390635Z 24 [Warning] IP address '27.97.227.251' could not be resolved: Name or service not known
2021-05-28T09:54:58.454797Z 78 [Warning] IP address '47.247.220.170' could not be resolved: Name or service not known
2021-05-28T09:55:01.532038Z 99 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2021-05-28T09:56:35.109571Z 518 [Warning] IP address '192.168.1.9' could not be resolved: Name or service not known
2021-05-28T10:00:01.420118Z 2002 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2021-05-28T10:00:01.447711Z 2004 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2021-05-28T10:00:01.612727Z 2005 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2021-05-28T10:00:01.647162Z 2006 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2021-05-28T10:00:01.648602Z 0 [Note] Giving 12 client threads a chance to die gracefully
2021-05-28T10:00:01.648631Z 0 [Note] Shutting down slave threads
2021-05-28T10:00:03.648737Z 0 [Note] Forcefully disconnecting 8 remaining clients
2021-05-28T10:00:03.648777Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 145  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648811Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 39  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648837Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 78  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648855Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 23  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648888Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 24  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648915Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 10  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648936Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 11  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648958Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 18  user: 'wa2nukec_demo'

2021-05-28T10:00:03.648988Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2021-05-28T10:00:03.649158Z 0 [Note] Binlog end
2021-05-28T10:00:03.650855Z 0 [Note] Shutting down plugin 'ngram'
2021-05-28T10:00:03.650870Z 0 [Note] Shutting down plugin 'partition'
2021-05-28T10:00:03.650874Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2021-05-28T10:00:03.650877Z 0 [Note] Shutting down plugin 'ARCHIVE'
2021-05-28T10:00:03.650880Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2021-05-28T10:00:03.650896Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2021-05-28T10:00:03.650943Z 0 [Note] Shutting down plugin 'MyISAM'
2021-05-28T10:00:03.650960Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2021-05-28T10:00:03.650965Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2021-05-28T10:00:03.650969Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2021-05-28T10:00:03.650973Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2021-05-28T10:00:03.650976Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2021-05-28T10:00:03.650983Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2021-05-28T10:00:03.650995Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2021-05-28T10:00:03.651013Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2021-05-28T10:00:03.651021Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2021-05-28T10:00:03.651034Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2021-05-28T10:00:03.651043Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2021-05-28T10:00:03.651049Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2021-05-28T10:00:03.651061Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2021-05-28T10:00:03.651065Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2021-05-28T10:00:03.651070Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2021-05-28T10:00:03.651081Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2021-05-28T10:00:03.651092Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2021-05-28T10:00:03.651095Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2021-05-28T10:00:03.651100Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2021-05-28T10:00:03.651111Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2021-05-28T10:00:03.651122Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2021-05-28T10:00:03.651125Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2021-05-28T10:00:03.651128Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2021-05-28T10:00:03.651133Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2021-05-28T10:00:03.651141Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2021-05-28T10:00:03.651146Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2021-05-28T10:00:03.651153Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2021-05-28T10:00:03.651158Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2021-05-28T10:00:03.651161Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2021-05-28T10:00:03.651165Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2021-05-28T10:00:03.651172Z 0 [Note] Shutting down plugin 'InnoDB'
2021-05-28T10:00:03.651228Z 0 [Note] InnoDB: FTS optimize thread exiting.
2021-05-28T10:00:03.651298Z 0 [Note] InnoDB: Starting shutdown...
2021-05-28T10:00:03.751412Z 0 [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/mysql/ib_buffer_pool
2021-05-28T10:00:03.759679Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 210528 15:30:03
2021-05-28T10:00:07.921158Z 0 [Note] InnoDB: Shutdown completed; log sequence number 25652060862711
2021-05-28T10:00:07.922788Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-05-28T10:00:07.922803Z 0 [Note] Shutting down plugin 'MEMORY'
2021-05-28T10:00:07.922809Z 0 [Note] Shutting down plugin 'CSV'
2021-05-28T10:00:07.922836Z 0 [Note] Shutting down plugin 'sha256_password'
2021-05-28T10:00:07.922838Z 0 [Note] Shutting down plugin 'mysql_native_password'
2021-05-28T10:00:07.922935Z 0 [Note] Shutting down plugin 'binlog'
2021-05-28T10:00:07.951529Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2021-05-28T10:00:09.097619Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-28T10:00:09.097714Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2021-05-28T10:00:09.097859Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.34) starting as process 19839 ...
2021-05-28T10:00:09.229060Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-05-28T10:00:09.229109Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-05-28T10:00:09.229113Z 0 [Note] InnoDB: Uses event mutexes
2021-05-28T10:00:09.229116Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-05-28T10:00:09.229119Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-05-28T10:00:09.229130Z 0 [Note] InnoDB: Using Linux native AIO
2021-05-28T10:00:09.230206Z 0 [Note] InnoDB: Number of pools: 1
2021-05-28T10:00:09.230301Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-05-28T10:00:09.236964Z 0 [Note] InnoDB: Initializing buffer pool, total size = 68G, instances = 32, chunk size = 128M
2021-05-28T10:00:12.172317Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-05-28T10:00:12.633187Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-05-28T10:00:12.644614Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-05-28T10:00:13.944852Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-05-28T10:00:13.944945Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-05-28T10:00:14.332308Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-05-28T10:00:14.333329Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-05-28T10:00:14.333346Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-05-28T10:00:14.333658Z 0 [Note] InnoDB: Waiting for purge to start
2021-05-28T10:00:14.383804Z 0 [Note] InnoDB: 5.7.34 started; log sequence number 25652060862711
2021-05-28T10:00:14.383962Z 0 [Note] InnoDB: Loading buffer pool(s) from /home/mysql/mysql/ib_buffer_pool
2021-05-28T10:00:14.384093Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-05-28T10:00:14.386753Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-05-28T10:00:14.386772Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-05-28T10:00:14.387703Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-28T10:00:14.387762Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-05-28T10:00:14.387900Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-05-28T10:00:14.387943Z 0 [Note] IPv6 is available.
2021-05-28T10:00:14.387956Z 0 [Note]   - '::' resolves to '::';
2021-05-28T10:00:14.387965Z 0 [Note] Server socket created on IP: '::'.
2021-05-28T10:00:14.412269Z 0 [Note] Event Scheduler: Loaded 0 events
2021-05-28T10:00:14.412407Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.34'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2021-05-28T10:00:14.453360Z 3 [Note] Access denied for user 'Cpanel::MysqlUtils::Unprivileged'@'localhost' (using password: NO)
2021-05-28T10:00:14.769372Z 5 [Warning] IP address '192.168.1.54' could not be resolved: Name or service not known
2021-05-28T10:00:14.816631Z 6 [Warning] IP address '47.247.220.170' could not be resolved: Name or service not known
2021-05-28T10:00:14.944144Z 8 [Warning] IP address '157.34.61.0' could not be resolved: Name or service not known
2021-05-28T10:00:15.150894Z 11 [Warning] IP address '157.34.213.66' could not be resolved: Name or service not known
2021-05-28T10:00:15.466660Z 12 [Warning] IP address '27.97.227.251' could not be resolved: Name or service not known
2021-05-28T10:00:15.707301Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210528 15:30:15
2021-05-28T10:00:16.097762Z 2 [Warning] Host name 'abts-mp-dynamic-085.253.70.182.airtelbroadband.in' could not be resolved: Name or service not known
2021-05-28T10:00:22.149749Z 46 [Warning] IP address '157.34.2.178' could not be resolved: Name or service not known
2021-05-28T10:00:35.547443Z 113 [Warning] IP address '157.34.57.161' could not be resolved: Name or service not known
2021-05-28T10:00:37.329999Z 124 [Warning] IP address '192.168.1.9' could not be resolved: Name or service not known

我的 /etc/my.cnf 文件:

#[mysqld_safe]
#log-error=/var/log/mysql/mysql_error.log

[mysqld]
performance-schema=0
port=3306
#datadir=/data/mysql
datadir=/home/mysql/mysql
socket=/var/lib/mysql/mysql.sock

innodb_file_per_table=1
max_allowed_packet = 256M
max_connect_errors = 1000000 
symbolic-links=0

key_buffer_size=2G
log-error=/var/log/mysql/mysql_error.log
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1


#pid-file=/var/run/mysqld/mysqld.pid
#innodb_buffer_pool_size=134217728
innodb_buffer_pool_size=65G
open_files_limit=30000
#default-storage-engine=MyISAM

sql_mode = ""
max_connections=3000
innodb_rollback_on_timeout=1
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
#tmp_table_size=15M
max_heap_table_size=48M
innodb_buffer_pool_instances=32
wait_timeout = 28800
innodb_log_file_size=12G

#bind-address    = 0.0.0.0


back_log = 512
thread_cache_size = 500
thread_stack = 192k
interactive_timeout = 180
#wait_timeout = 382


#server-id=6305508
#log_bin=mysql-bin
#log_error=mysql-bin.err
#binlog_ignore_db=wa2nukec_adminlite

#[client]
#port=3306
#socket=/var/lib/mysql/mysql.sock

答案1

我怀疑您的 max_allowed_pa​​cket 和大型 TEXT/BLOB 字段的存在。

我之前回答过两个由于max_allowed_pa​​cket太小导致mysql崩溃的问题。

MySQL 服务器已消失,阻碍了导入大型转储 InnoDB 表 SELECT 返回 ERROR 2006 (HY000):MySQL 服务器已消失(断电后)我建议将 max_allowed_pa​​cket 提高到最大值,即 1GB

如果您有 TEXT/BLOB 字段,则您可能输入了太大的 TEXT/BLOB 字段,无法通过 MySQL 数据包或 InnoDB 日志文件。您可能需要将 innodb 日志文件的大小调整为最大值,即 2047M。以下是操作方法:

步骤 01)将其添加到 /etc/my.cnf

[mysqld] max_allowed_pa​​cket=1G innodb_log_file_size=2047M 注意:在 Google Cloud 中,您不能输入 1G,必须使用值 1073741824

步骤 02)停止 mysql 服务

步骤 03) rm -f /var/lib/mysql/ib_logfile[01]

步骤 04)启动 mysql 服务

相关内容