I'm trying to figure out why MySQL keeps shutting down every night. Specifically, MySQL restarts itself every night (also unknown reason), but fails on starting itself back up and aborts. Some days it restarts itself successfully and everything works, but most days it doesn't so I've had to make a script to restart it every morning. Around 8-9AM UTC every morning mySQL restarts itself twice, but fails on the 2nd restart. It fails on the start up because it can't find some .-.work init file and I'm not sure why.
There are 2 cases I noticed with MySQL every night:
- Restarts Successfully. But will says it executes the init file, or not at all.
- Restarts fail. In this case it's always because it can't find the init file.
Summary info:
- MySQL restarts itself every morning from 8-9AM UTC
- MySQL restarting success: Stop > Start > Stop > Start
- MySQL restarting fail: Stop > Start (Abort/failure) > Stop
Additional info:
- CentOS 6.8
- MySQL 5.7.17
MySQL failing because of init file:
2018-02-25T08:45:09.782610Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-02-25T08:45:09.784928Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.17-log) starting as process 11221 ...
2018-02-25T08:45:09.798909Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-02-25T08:45:09.798938Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-02-25T08:45:09.798960Z 0 [Note] InnoDB: Uses event mutexes
2018-02-25T08:45:09.798971Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2018-02-25T08:45:09.798992Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-02-25T08:45:09.799007Z 0 [Note] InnoDB: Using Linux native AIO
2018-02-25T08:45:09.799444Z 0 [Note] InnoDB: Number of pools: 1
2018-02-25T08:45:09.799600Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-02-25T08:45:09.801717Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-02-25T08:45:09.816978Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-02-25T08:45:09.821022Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-02-25T08:45:09.846219Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-25T08:45:10.072617Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-02-25T08:45:10.072727Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-02-25T08:45:10.093784Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-02-25T08:45:10.094893Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-02-25T08:45:10.094908Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-02-25T08:45:10.095372Z 0 [Note] InnoDB: Waiting for purge to start
2018-02-25T08:45:10.145560Z 0 [Note] InnoDB: 5.7.17 started; log sequence number 28375479994
2018-02-25T08:45:10.145846Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-02-25T08:45:10.145941Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-02-25T08:45:10.162499Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180225 1:45:10
2018-02-25T08:45:10.181824Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2018-02-25T08:45:10.186627Z 0 [Warning] CA certificate ca.pem is self signed.
2018-02-25T08:45:10.188420Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2018-02-25T08:45:10.188488Z 0 [Note] IPv6 is`enter code here` not available.
2018-02-25T08:45:10.188521Z 0 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2018-02-25T08:45:10.188545Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2018-02-25T08:45:10.304549Z 0 [ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.
2018-02-25T08:45:10.305926Z 0 [ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.
2018-02-25T08:45:10.311827Z 0 [Note] Event Scheduler: Loaded 0 events
2018-02-25T08:45:10.311944Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2018-02-25T08:45:10.311957Z 0 [Note] Beginning of list of non-natively partitioned tables
2018-02-25T08:45:10.880682Z 0 [Note] End of list of non-natively partitioned tables
2018-02-25T08:45:10.880729Z 0 [Note] Execution of init_file '/var/lib/mysql/.-.work.gjy3b8Gov_chCz89' started.
2018-02-25T08:45:10.880806Z 0 [ERROR] /usr/sbin/mysqld: File '/var/lib/mysql/.-.work.gjy3b8Gov_chCz89' not found (Errcode: 2 - No such file or directory)
2018-02-25T08:45:10.880816Z 0 [ERROR] Aborting
I know the db restarting itself every night is another issue to look into, but I'm not quite sure where to start with this whole problem.Why does it look for that file? How is it being generated? How do I fix it? Any and all help would be appreciated.
(Edit) Reposted here to better meet topic qualifications.
(Edit 2) Here's the my.cnf file:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps = UTC
general_log
general_log_file = /var/log/general_query.log
log_error_verbosity = 3
max_allowed_packet = 256M
slow_query_log_file = /var/log/slow-queries.log
slow-query-log
