6

Every 5~ days MySQL just shuts down. I turned on error and general logging and I haven't found anything that would suggest why it's happening.

The MySQL server was installed from a generic binary and I'm running mysqld_safe from a screen using this command:

./SQL/bin/mysqld_safe --user=mysql --basedir=/home/myname/SQL --datadir=/home/myname/SQL/dat

Screen log

root@host [/home/myname]# ./start-sql.sh
2018-06-03T11:55:36.104464Z mysqld_safe Logging to '/home/myname/SQL/mysql.err'.
2018-06-03T11:55:36.158959Z mysqld_safe Starting mysqld daemon with databases from /home/myname/SQL/dat
2018-06-05T11:52:42.896969Z mysqld_safe mysqld from pid file /home/myname/SQL/dat/host.inmotionhosting.com.pid ended
root@host [/home/myname]#

mysql.err log:

2018-06-03T11:45:47.544442Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-03T11:45:47.545210Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-06-03T11:45:47.545245Z 0 [Note] /home/myname/SQL/bin/mysqld (mysqld 5.7.21) starting as process 8949 ...
2018-06-03T11:45:47.580830Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-06-03T11:45:47.580900Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-06-03T11:45:47.580916Z 0 [Note] InnoDB: Uses event mutexes
2018-06-03T11:45:47.580929Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2018-06-03T11:45:47.580943Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-06-03T11:45:47.580955Z 0 [Note] InnoDB: Using Linux native AIO
2018-06-03T11:45:47.581460Z 0 [Note] InnoDB: Number of pools: 1
2018-06-03T11:45:47.581683Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-06-03T11:45:47.585175Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-06-03T11:45:47.607371Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-06-03T11:45:47.612355Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-06-03T11:45:47.635399Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-06-03T11:45:47.668283Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-06-03T11:45:47.668390Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-06-03T11:45:47.718557Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-06-03T11:45:47.720211Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-06-03T11:45:47.720241Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-06-03T11:45:47.720898Z 0 [Note] InnoDB: 5.7.21 started; log sequence number 2551767
2018-06-03T11:45:47.721087Z 0 [Note] InnoDB: Loading buffer pool(s) from /home/myname/SQL/dat/ib_buffer_pool
2018-06-03T11:45:47.722264Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-06-03T11:45:47.739409Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-06-03T11:45:47.739457Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3309
2018-06-03T11:45:47.739506Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2018-06-03T11:45:47.739531Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2018-06-03T11:45:47.749338Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180603  7:45:47
2018-06-03T11:45:47.770378Z 0 [Note] Event Scheduler: Loaded 0 events
2018-06-03T11:45:47.770605Z 0 [Note] /home/myname/SQL/bin/mysqld: ready for connections.
Version: '5.7.21'  socket: '/tmp/mysql.sock'  port: 3309  MySQL Community Server (GPL)
2018-06-03T11:55:36.294441Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-03T11:55:36.294564Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-06-03T11:55:36.294594Z 0 [Note] /home/myname/SQL/bin/mysqld (mysqld 5.7.21-log) starting as process 9656 ...
2018-06-03T11:55:36.301959Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-06-03T11:55:36.302005Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-06-03T11:55:36.302013Z 0 [Note] InnoDB: Uses event mutexes
2018-06-03T11:55:36.302020Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2018-06-03T11:55:36.302027Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-06-03T11:55:36.302034Z 0 [Note] InnoDB: Using Linux native AIO
2018-06-03T11:55:36.302312Z 0 [Note] InnoDB: Number of pools: 1
2018-06-03T11:55:36.302451Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-06-03T11:55:36.304545Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-06-03T11:55:36.316676Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-06-03T11:55:36.319685Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-06-03T11:55:36.332283Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-06-03T11:55:36.334251Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2551786
2018-06-03T11:55:36.334269Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2551795
2018-06-03T11:55:36.334278Z 0 [Note] InnoDB: Database was not shutdown normally!
2018-06-03T11:55:36.334285Z 0 [Note] InnoDB: Starting crash recovery.
2018-06-03T11:55:36.461420Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2018-06-03T11:55:36.461459Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-06-03T11:55:36.461546Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-06-03T11:55:36.506970Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-06-03T11:55:36.508533Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-06-03T11:55:36.508556Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-06-03T11:55:36.509156Z 0 [Note] InnoDB: Waiting for purge to start
2018-06-03T11:55:36.559333Z 0 [Note] InnoDB: 5.7.21 started; log sequence number 2551795
2018-06-03T11:55:36.559635Z 0 [Note] InnoDB: Loading buffer pool(s) from /home/myname/SQL/dat/ib_buffer_pool
2018-06-03T11:55:36.559787Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-06-03T11:55:36.561420Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-06-03T11:55:36.561444Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3309
2018-06-03T11:55:36.561472Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2018-06-03T11:55:36.561488Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2018-06-03T11:55:36.561735Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180603  7:55:36
2018-06-03T11:55:36.604058Z 0 [Note] Event Scheduler: Loaded 0 events
2018-06-03T11:55:36.604334Z 0 [Note] /home/myname/SQL/bin/mysqld: ready for connections.
Version: '5.7.21-log'  socket: '/tmp/mysql.sock'  port: 3309  MySQL Community Server (GPL)
2018-06-04T18:35:23.644361Z 2 [Note] Aborted connection 2 to db: '20XX' user: '20xx' host: 'localhost' (Got timeout reading communication packets)
2018-06-05T11:52:41.283194Z 0 [Note] Giving 0 client threads a chance to die gracefully
2018-06-05T11:52:41.283254Z 0 [Note] Shutting down slave threads
2018-06-05T11:52:41.284502Z 0 [Note] Forcefully disconnecting 0 remaining clients
2018-06-05T11:52:41.284534Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2018-06-05T11:52:41.291909Z 0 [Note] Binlog end
2018-06-05T11:52:41.311985Z 0 [Note] Shutting down plugin 'ngram'
2018-06-05T11:52:41.314941Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2018-06-05T11:52:41.314961Z 0 [Note] Shutting down plugin 'partition'
2018-06-05T11:52:41.314970Z 0 [Note] Shutting down plugin 'ARCHIVE'
2018-06-05T11:52:41.314976Z 0 [Note] Shutting down plugin 'MyISAM'
2018-06-05T11:52:41.316497Z 0 [Note] Shutting down plugin 'CSV'
2018-06-05T11:52:41.316512Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2018-06-05T11:52:41.318040Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2018-06-05T11:52:41.318058Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2018-06-05T11:52:41.318070Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2018-06-05T11:52:41.318082Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2018-06-05T11:52:41.318094Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2018-06-05T11:52:41.318106Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2018-06-05T11:52:41.318118Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2018-06-05T11:52:41.318130Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2018-06-05T11:52:41.318142Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2018-06-05T11:52:41.318154Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2018-06-05T11:52:41.318166Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2018-06-05T11:52:41.318178Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2018-06-05T11:52:41.318185Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2018-06-05T11:52:41.318191Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2018-06-05T11:52:41.318196Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2018-06-05T11:52:41.318202Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2018-06-05T11:52:41.318207Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2018-06-05T11:52:41.318213Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2018-06-05T11:52:41.318218Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2018-06-05T11:52:41.318224Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2018-06-05T11:52:41.318229Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2018-06-05T11:52:41.318235Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2018-06-05T11:52:41.318240Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2018-06-05T11:52:41.318245Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2018-06-05T11:52:41.318251Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2018-06-05T11:52:41.318256Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2018-06-05T11:52:41.318262Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2018-06-05T11:52:41.318268Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2018-06-05T11:52:41.318273Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2018-06-05T11:52:41.318279Z 0 [Note] Shutting down plugin 'InnoDB'
2018-06-05T11:52:41.334894Z 0 [Note] InnoDB: FTS optimize thread exiting.
2018-06-05T11:52:41.335924Z 0 [Note] InnoDB: Starting shutdown...
2018-06-05T11:52:41.437096Z 0 [Note] InnoDB: Dumping buffer pool(s) to /home/myname/SQL/dat/ib_buffer_pool
2018-06-05T11:52:41.439636Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 180605  7:52:41
2018-06-05T11:52:42.856844Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2551839
2018-06-05T11:52:42.861102Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2018-06-05T11:52:42.861138Z 0 [Note] Shutting down plugin 'MEMORY'
2018-06-05T11:52:42.862074Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2018-06-05T11:52:42.862097Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2018-06-05T11:52:42.862148Z 0 [Note] Shutting down plugin 'sha256_password'
2018-06-05T11:52:42.862158Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-06-05T11:52:42.869455Z 0 [Note] Shutting down plugin 'binlog'
2018-06-05T11:52:42.875418Z 0 [Note] /home/myname/SQL/bin/mysqld: Shutdown complete

my.cnf settings

[mysqld]
log_error=/home/myname/SQL/mysql.err
general_log_file=/home/myname/SQL/mysql.log
general_log=1
performance-schema=0
max_connections=100
max_user_connections=75
query_cache_size=32M
skip-external-locking
key_buffer_size=32M
max_allowed_packet=268435456
table_open_cache=128
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=16M
innodb_file_per_table=1
tmp_table_size=32M
max_heap_table_size=32M
thread_cache_size=4
default-storage-engine=MyISAM
bind-address=0.0.0.0
port=3001
wait_timeout = 60000
interactive_timeout = 60000

I've been struggling with this issue for a month or so now. I cannot find any hint as to why it is happening. Any advice is appreciated.

Improve this question
2
  • What does your ./start-sql.sh script look like?
    – dbdemon
    Jun 12, 2018 at 9:09
  • @dbdemon basically just this: ./SQL/bin/mysqld_safe --user=mysql --basedir=/home/myname/SQL --datadir=/home/myname/SQL/dat
    – new Objekt
    Jun 12, 2018 at 9:45

2 Answers 2

Reset to default
2

Sometimes MySQL shutdowns automatically, for example, if it finds InnoDB corruption, but based on your error log, that seems what I would expect from a normal shutdown, which can be because:

  • A sigint signal was sent do the mysqld process
  • A client connected requested the shutdown start (e.g. mysqladmin shutdown)
  • You pressed Cntrl+c on your screen session

Note that there are no errors here:

2018-06-05T11:52:41.283194Z 0 [Note] Giving 0 client threads a chance to die gracefully
2018-06-05T11:52:41.283254Z 0 [Note] Shutting down slave threads

, and that it says it is trying to shutdown gracefully, which would not happen if there was any anomaly. Also you can write to the logs, which would prevent errors from being logged in.

Maybe some cron job or session killing long-running processes? Something that sends a sigint unwillingly? I would monitor the processes and try to identify the origin of the shutdown (processes running just before the shutdown signal). Is there a reason to run it as a screen or it was just for debugging?

Improve this answer
4
  • No particular reason for running in a screen. I'll need to find a better solution before I go live with this site. This is running on an inmotionhosting vps so there is a lot of unknowns with what is going on in the background. If this were just running on one of my systems it would be easier to nail down everything. Is there any way to log the reason for a shutdown? I have a Tomcat server running on their as well and it has never randomly shut off so I doubt "long running processes" is the issue.
    – new Objekt
    Jun 12, 2018 at 19:30
  • You can try enabling the general log to log every query executed and/or trace signal received by the process: ibm.com/developerworks/community/blogs/aimsupport/entry/…
    – jynus
    Jun 12, 2018 at 19:39
  • I will try the traces in that article. General log had nothing when I tried it before. Thanks.
    – new Objekt
    Jun 12, 2018 at 19:47
  • discovered anything? have the same issue
    – Marco Marsala
    Mar 23 at 7:07
1

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=30  # from 4  refman indicates formula is 8 + (max_connections/10), be liberal let the machine breathe.

Good luck.

For additional assistance, chk my profile clk Network Profile for contact info.

Improve this answer
1
  • @newObjekt How is your system running now? Looking forward to providing FREE My Top 10 Global Variable Suggestions as well.
    – Wilson Hauck
    Jun 20, 2018 at 15:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.