mysql 的版本是 5.7 centOS 的版本是 7.7
采用 securityCRT 远程连接服务器,在保持连接的状态下,mysql 服务正常使用,一旦断开远程连接就出现 mysql 停服,期间尝试过后台启动,启动的命令为:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
但是并没有解决问题。
其中我的 my.cnf 如下:
[client]
port=3306
socket=/usr/local/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysqld]
port=3306
socket=/usr/local/mysql/data/mysql.sock
skip-slave-start
skip-external-locking
key_buffer_size=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
query_cache_size=32M
max_allowed_packet=16M
myisam_sort_buffer_size=128M
tmp_table_size=32M
table_open_cache=512
thread_cache_size=8
wait_timeout=86400
interactive_timeout=86400
max_connections=600
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency=32
#isolation level and default engine
default-storage-engine=INNODB
transaction-isolation=READ-COMMITTED
server-id=1739
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/hostname.pid
#open performance schema
log-warnings
sysdate-is-now
binlog_format=ROW
log_bin_trust_function_creators=1
log-error=/usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
expire_logs_days=7
innodb_write_io_threads=16
relay-log=/usr/local/mysql/relay_log/relay-log
relay-log-index=/usr/local/mysql/relay_log/relay-log.index
relay_log_info_file=/usr/local/mysql/relay_log/relay-log.info
log_slave_updates=1
gtid_mode=OFF
enforce_gtid_consistency=OFF
# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
#other logs
#general_log=1
#general_log_file=/usr/local/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/usr/local/mysql/data/slow_log.err
#for replication slave
sync_binlog=500
#for innodb options
innodb_data_home_dir=/usr/local/mysql/data/
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_log_group_home_dir=/usr/local/mysql/arch
innodb_log_files_in_group=4
innodb_log_file_size=1G
innodb_log_buffer_size=200M
#根据生产需要,调整pool size
innodb_buffer_pool_size=2G
#innodb_additional_mem_pool_size=50M #deprecated in 5.6
tmpdir=/usr/local/mysql/tmp
innodb_lock_wait_timeout=1000
#innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_locks_unsafe_for_binlog=1
#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on
#case-sensitive file names and separate tablespace
innodb_file_per_table=1
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet=128M
[mysql]
no-auto-rehash
default-character-set=utf8mb4
[mysqlhotcopy]
interactive-timeout
[myisamchk]
key_buffer_size=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
日志中也没有明显的报错信息:
cat hostname.err
2021-09-13T08:48:42.272927Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2021-09-13T08:48:42.273140Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-13T08:48:42.273187Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-09-13T08:48:42.273229Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.28-log) starting as process 6343 ...
2021-09-13T08:48:42.311783Z 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
2021-09-13T08:48:42.311889Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-09-13T08:48:42.311907Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-13T08:48:42.311913Z 0 [Note] InnoDB: Uses event mutexes
2021-09-13T08:48:42.311918Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-09-13T08:48:42.311923Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-09-13T08:48:42.311929Z 0 [Note] InnoDB: Using Linux native AIO
2021-09-13T08:48:42.312562Z 0 [Note] InnoDB: Number of pools: 1
2021-09-13T08:48:42.312723Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-09-13T08:48:42.314892Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
2021-09-13T08:48:42.474454Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-09-13T08:48:42.505920Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-09-13T08:48:42.519536Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-13T08:48:42.606341Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-09-13T08:48:42.606438Z 0 [Note] InnoDB: Setting file '/usr/local/mysql/data/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-09-13T08:48:42.626251Z 0 [Note] InnoDB: File '/usr/local/mysql/data/ibtmp1' size is now 12 MB.
2021-09-13T08:48:42.627272Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-09-13T08:48:42.627288Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-09-13T08:48:42.627515Z 0 [Note] InnoDB: Waiting for purge to start
2021-09-13T08:48:42.677753Z 0 [Note] InnoDB: 5.7.28 started; log sequence number 2628925
2021-09-13T08:48:42.677967Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/data/ib_buffer_pool
2021-09-13T08:48:42.678216Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-09-13T08:48:42.679887Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210913 16:48:42
2021-09-13T08:48:42.694857Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-09-13T08:48:42.694895Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-09-13T08:48:42.696620Z 0 [Warning] CA certificate ca.pem is self signed.
2021-09-13T08:48:42.696732Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-09-13T08:48:42.697000Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-13T08:48:42.697078Z 0 [Note] IPv6 is available.
2021-09-13T08:48:42.697100Z 0 [Note] - '::' resolves to '::';
2021-09-13T08:48:42.697131Z 0 [Note] Server socket created on IP: '::'.
2021-09-13T08:48:42.710009Z 0 [Note] Event Scheduler: Loaded 0 events
2021-09-13T08:48:42.710204Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.28-log' socket: '/usr/local/mysql/data/mysql.sock' port: 3306 MySQL Community Server (GPL)
2021-09-13T08:48:51.621376Z 0 [Note] Giving 0 client threads a chance to die gracefully
2021-09-13T08:48:51.621411Z 0 [Note] Shutting down slave threads
2021-09-13T08:48:51.621425Z 0 [Note] Forcefully disconnecting 0 remaining clients
2021-09-13T08:48:51.621444Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2021-09-13T08:48:51.621589Z 0 [Note] Binlog end
2021-09-13T08:48:51.624725Z 0 [Note] Shutting down plugin 'ngram'
2021-09-13T08:48:51.624753Z 0 [Note] Shutting down plugin 'ARCHIVE'
2021-09-13T08:48:51.624758Z 0 [Note] Shutting down plugin 'partition'
2021-09-13T08:48:51.624762Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2021-09-13T08:48:51.624767Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2021-09-13T08:48:51.624772Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2021-09-13T08:48:51.624776Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2021-09-13T08:48:51.624780Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2021-09-13T08:48:51.624784Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2021-09-13T08:48:51.624788Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2021-09-13T08:48:51.624791Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2021-09-13T08:48:51.624795Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2021-09-13T08:48:51.624799Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2021-09-13T08:48:51.624802Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2021-09-13T08:48:51.624806Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2021-09-13T08:48:51.624810Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2021-09-13T08:48:51.624814Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2021-09-13T08:48:51.624818Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2021-09-13T08:48:51.624821Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2021-09-13T08:48:51.624825Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2021-09-13T08:48:51.624829Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2021-09-13T08:48:51.624833Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2021-09-13T08:48:51.624837Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2021-09-13T08:48:51.624840Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2021-09-13T08:48:51.624844Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2021-09-13T08:48:51.624848Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2021-09-13T08:48:51.624852Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2021-09-13T08:48:51.624855Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2021-09-13T08:48:51.624859Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2021-09-13T08:48:51.624863Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2021-09-13T08:48:51.624867Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2021-09-13T08:48:51.624870Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2021-09-13T08:48:51.624874Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2021-09-13T08:48:51.624878Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2021-09-13T08:48:51.624882Z 0 [Note] Shutting down plugin 'InnoDB'
2021-09-13T08:48:51.625005Z 0 [Note] InnoDB: FTS optimize thread exiting.
2021-09-13T08:48:51.625126Z 0 [Note] InnoDB: Starting shutdown...
2021-09-13T08:48:51.725390Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/mysql/data/ib_buffer_pool
2021-09-13T08:48:51.725963Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 210913 16:48:51
2021-09-13T08:48:53.307968Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2628953
2021-09-13T08:48:53.310332Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-09-13T08:48:53.310363Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2021-09-13T08:48:53.310406Z 0 [Note] Shutting down plugin 'MEMORY'
2021-09-13T08:48:53.310421Z 0 [Note] Shutting down plugin 'CSV'
2021-09-13T08:48:53.310431Z 0 [Note] Shutting down plugin 'MyISAM'
2021-09-13T08:48:53.310444Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2021-09-13T08:48:53.310451Z 0 [Note] Shutting down plugin 'sha256_password'
2021-09-13T08:48:53.310456Z 0 [Note] Shutting down plugin 'mysql_native_password'
2021-09-13T08:48:53.310669Z 0 [Note] Shutting down plugin 'binlog'
2021-09-13T08:48:53.318419Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete