远程开启mysql,一旦断开远程连接,mysql服务就停止

问题:

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

希望大家给点建议,怎么去解决这个问题。

讨论数量: 1

mysqld_safe 不出意外这个是安全启动, 你用 mysqld 启动,或者 把mysql 加入服务在开机启动

2年前 评论
PUMBAA (楼主) 2年前
kis龍 (作者) 2年前
PUMBAA (楼主) 2年前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!