December 20, 2023

Solved MySQL stopped and it can’t be restart

Looking for how to solve MySQL stopped and it can’t be restart? This article is created to solve your misery with our step-by-step guide which anyone can do.

Not long ago our website was down which prompt me to use my Terminal console to find out what the issue might be by trying to restart MySQL:

sudo service mysql restart

It says me

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Log of journalctl -xe says:

-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Unit entered failed state.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has finished shutting down.
Apr 25 10:41:38 wordpress-512mb-ams3-01 systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has begun starting up.
Apr 25 10:41:38 wordpress-512mb-ams3-01 audit[3864]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:38 wordpress-512mb-ams3-01 kernel: audit: type=1400 audit(1493116898.796:1567): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:38 wordpress-512mb-ams3-01 audit[3864]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Apr 25 10:41:38 wordpress-512mb-ams3-01 audit[3864]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:38 wordpress-512mb-ams3-01 kernel: audit: type=1400 audit(1493116898.812:1568): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Apr 25 10:41:38 wordpress-512mb-ams3-01 kernel: audit: type=1400 audit(1493116898.812:1569): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/3864/status" pid=3864 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Apr 25 10:41:39 wordpress-512mb-ams3-01 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

And finally, /var/log/mysql/error.log says:

2017-04-25T10:45:43.868541Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-04-25T10:45:43.868576Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-04-25T10:45:43.868596Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-04-25T10:45:43.868615Z 0 [ERROR] Failed to initialize plugins.
2017-04-25T10:45:43.868629Z 0 [ERROR] Aborting

2017-04-25T10:45:43.868673Z 0 [Note] Binlog end
2017-04-25T10:45:43.868837Z 0 [Note] Shutting down plugin 'CSV'
2017-04-25T10:45:43.869563Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Then i dig dipper by checking mysql error log

tail -f /var/log/mysql/error.log

which brought out it different log where i discover i had low droplet’s memory how could this be

i use

df -h

Filesystem      Size  Used Avail Use% Mounted on
udev            978M     0  978M   0% /dev
tmpfs           199M  980K  198M   1% /run
/dev/vda1        49G   49G   26G  100% /
tmpfs           994M     0  994M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           994M     0  994M   0% /sys/fs/cgroup
/dev/vda15      105M  9.2M   96M   9% /boot/efi
/dev/loop0       56M   56M     0 100% /snap/core18/1885
/dev/loop1       56M   56M     0 100% /snap/core18/1997
/dev/loop2       31M   31M     0 100% /snap/snapd/9607
/dev/loop3       71M   71M     0 100% /snap/lxd/16922
/dev/loop4       33M   33M     0 100% /snap/snapd/11588
/dev/loop5       71M   71M     0 100% /snap/lxd/19647
tmpfs           199M     0  199M   0% /run/user/0

/dev/vda1 is where all my file is located i need to dig dipper to understand what is taking up all the space with the command line below

du -h --max-depth=1 /

it returns

0/sys
1.9G/usr
12M/opt
0/dev
16K/lost+found
4.0K/mnt
21M/root
45G/var
4.0K/srv
146M/boot
4.0K/home
4.0K/media
7.8M/etc
980K/run
du: cannot access '/proc/646047/task/646047/fd/4': No such file or directory
du: cannot access '/proc/646047/task/646047/fdinfo/4': No such file or directory
du: cannot access '/proc/646047/fd/3': No such file or directory
du: cannot access '/proc/646047/fdinfo/3': No such file or directory
du: cannot read directory '/proc/646057/task/646057/net': Invalid argument
du: cannot read directory '/proc/646057/net': Invalid argument
0/proc
1021M/snap
80K/tmp
45G/

/var is where file taking up my droplet space is located.

then i dig further to locate the main folder with the command line below

du -hs /var/*

it returns

2.1M/var/backups
88M/var/cache
4.0K/var/crash
43G/var/lib
4.0K/var/local
0/var/lock
375M/var/log
88K/var/mail
4.0K/var/opt
0/var/run
60K/var/snap
1.5M/var/spool
44K/var/tmp
6.9G/var/www

du -hs /var/lib/* you can continue to expand my adding the folder path

12K/var/lib/AccountsService
36K/var/lib/PackageKit
36K/var/lib/apache2
113M/var/lib/apt
4.1M/var/lib/aspell
4.0K/var/lib/boltd
408K/var/lib/cloud
3.1M/var/lib/command-not-found
8.0K/var/lib/dbus
4.0K/var/lib/dhcp
12K/var/lib/dictionaries-common
8.0K/var/lib/digitalocean
37M/var/lib/dpkg
24K/var/lib/emacsen-common
172K/var/lib/fail2ban
652K/var/lib/fwupd
4.0K/var/lib/git
16K/var/lib/grub
16K/var/lib/initramfs-tools
8.0K/var/lib/ispell
4.0K/var/lib/landscape
52K/var/lib/letsencrypt
8.0K/var/lib/logrotate
4.0K/var/lib/man-db
91M/var/lib/mecab
4.0K/var/lib/misc
40G/var/lib/mysql
4.0K/var/lib/mysql-files
4.0K/var/lib/mysql-keyring
4.0K/var/lib/mysql-upgrade
2.7G/var/lib/mysql_bkp
4.0K/var/lib/os-prober
28K/var/lib/pam
88K/var/lib/php
4.0K/var/lib/plymouth
40K/var/lib/polkit-1
44K/var/lib/postfix
4.0K/var/lib/private
4.0K/var/lib/python
4.4M/var/lib/redis
8.0K/var/lib/shim-signed
316M/var/lib/snapd
8.0K/var/lib/sudo
544K/var/lib/systemd
4.0K/var/lib/tpm
4.0K/var/lib/ubuntu-advantage
4.0K/var/lib/ubuntu-release-upgrader
284K/var/lib/ucf
4.0K/var/lib/unattended-upgrades
12K/var/lib/update-manager
20K/var/lib/update-notifier
608K/var/lib/usbutils
8.0K/var/lib/vim

192K/var/lib/mysql/#ib_16384_0.dblwr
8.2M/var/lib/mysql/#ib_16384_1.dblwr
164K/var/lib/mysql/#innodb_temp
4.0K/var/lib/mysql/auto.cnf
101M/var/lib/mysql/binlog.000288
101M/var/lib/mysql/binlog.000289
101M/var/lib/mysql/binlog.000290
101M/var/lib/mysql/binlog.000291
101M/var/lib/mysql/binlog.000292
101M/var/lib/mysql/binlog.000293
101M/var/lib/mysql/binlog.000294
101M/var/lib/mysql/binlog.000295
101M/var/lib/mysql/binlog.000296
101M/var/lib/mysql/binlog.000297
101M/var/lib/mysql/binlog.000298
101M/var/lib/mysql/binlog.000299
101M/var/lib/mysql/binlog.000300
101M/var/lib/mysql/binlog.000301
101M/var/lib/mysql/binlog.000302
101M/var/lib/mysql/binlog.000303
101M/var/lib/mysql/binlog.000304
101M/var/lib/mysql/binlog.000305
101M/var/lib/mysql/binlog.000306
101M/var/lib/mysql/binlog.000307
101M/var/lib/mysql/binlog.000308
101M/var/lib/mysql/binlog.000309
101M/var/lib/mysql/binlog.000310
101M/var/lib/mysql/binlog.000311
101M/var/lib/mysql/binlog.000312
101M/var/lib/mysql/binlog.000313
101M/var/lib/mysql/binlog.000314
101M/var/lib/mysql/binlog.000315
10GB/var/lib/mysql/binlog.000316
82M/var/lib/mysql/binlog.000317
101M/var/lib/mysql/binlog.000318
101M/var/lib/mysql/binlog.000319
101M/var/lib/mysql/binlog.000320
101M/var/lib/mysql/binlog.000321
101M/var/lib/mysql/binlog.000322
101M/var/lib/mysql/binlog.000323
101M/var/lib/mysql/binlog.000324
101M/var/lib/mysql/binlog.000325
101M/var/lib/mysql/binlog.000326
101M/var/lib/mysql/binlog.000327
101M/var/lib/mysql/binlog.000328
101M/var/lib/mysql/binlog.000329
101M/var/lib/mysql/binlog.000330
101M/var/lib/mysql/binlog.000331
101M/var/lib/mysql/binlog.000332
101M/var/lib/mysql/binlog.000333
101M/var/lib/mysql/binlog.000334
101M/var/lib/mysql/binlog.000335
101M/var/lib/mysql/binlog.000336
101M/var/lib/mysql/binlog.000337
101M/var/lib/mysql/binlog.000338
101M/var/lib/mysql/binlog.000339
101M/var/lib/mysql/binlog.000340
101M/var/lib/mysql/binlog.000341
101M/var/lib/mysql/binlog.000342
101M/var/lib/mysql/binlog.000343
101M/var/lib/mysql/binlog.000344
31M/var/lib/mysql/binlog.000345
124K/var/lib/mysql/binlog.000346
252K/var/lib/mysql/binlog.000347
92K/var/lib/mysql/binlog.000348
176K/var/lib/mysql/binlog.000349
152K/var/lib/mysql/binlog.000350
4.0K/var/lib/mysql/binlog.000351
4.0K/var/lib/mysql/binlog.000352
96K/var/lib/mysql/binlog.000353
4.0K/var/lib/mysql/binlog.000354
4.0K/var/lib/mysql/binlog.000355
4.0K/var/lib/mysql/binlog.000356
4.0K/var/lib/mysql/binlog.000357
124K/var/lib/mysql/binlog.000358
4.0K/var/lib/mysql/binlog.000359
4.0K/var/lib/mysql/binlog.000360
4.0K/var/lib/mysql/binlog.000361
4.0K/var/lib/mysql/binlog.000362
4.0K/var/lib/mysql/binlog.000363
4.0K/var/lib/mysql/binlog.000364
4.0K/var/lib/mysql/binlog.000365
4.0K/var/lib/mysql/binlog.000366
4.0K/var/lib/mysql/binlog.000367
4.0K/var/lib/mysql/binlog.000368
4.0K/var/lib/mysql/binlog.000369
48K/var/lib/mysql/binlog.000370
4.0K/var/lib/mysql/binlog.000371
4.0K/var/lib/mysql/binlog.000372
101M/var/lib/mysql/binlog.000373
101M/var/lib/mysql/binlog.000374
101M/var/lib/mysql/binlog.000375
9.6M/var/lib/mysql/binlog.000376
101M/var/lib/mysql/binlog.000377
101M/var/lib/mysql/binlog.000378
101M/var/lib/mysql/binlog.000379
101M/var/lib/mysql/binlog.000380
64K/var/lib/mysql/binlog.000381
141M/var/lib/mysql/binlog.000382
43M/var/lib/mysql/binlog.000383
1.1G/var/lib/mysql/binlog.000384
816M/var/lib/mysql/binlog.000385
1.1G/var/lib/mysql/binlog.000386
197M/var/lib/mysql/binlog.000387
4.0K/var/lib/mysql/binlog.index
4.0K/var/lib/mysql/ca-key.pem
4.0K/var/lib/mysql/ca.pem
4.0K/var/lib/mysql/client-cert.pem
4.0K/var/lib/mysql/client-key.pem
4.0K/var/lib/mysql/conerstonenews.pid
0/var/lib/mysql/debian-5.7.flag
8.0K/var/lib/mysql/ib_buffer_pool
48M/var/lib/mysql/ib_logfile0
48M/var/lib/mysql/ib_logfile1
12M/var/lib/mysql/ibdata1
12M/var/lib/mysql/ibtmp1
36K/var/lib/mysql/mysql
37M/var/lib/mysql/mysql.ibd
4.0K/var/lib/mysql/mysql_upgrade_info
1.6M/var/lib/mysql/performance_schema
4.0K/var/lib/mysql/private_key.pem
4.0K/var/lib/mysql/public_key.pem
4.0K/var/lib/mysql/server-cert.pem
4.0K/var/lib/mysql/server-key.pem
100K/var/lib/mysql/sys
27M/var/lib/mysql/undo_001
27M/var/lib/mysql/undo_002
400M/var/lib/mysql/wordpress

i discover the solution binlog expire_logs_days taking space and here

How to purge MySQL binary log (binlogfile) to free disk space.

log to mysql with root user and password

mysql -u root -p

it returns

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 57091
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

use this command to purge binlog

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

boom error logs are cleared

check droplet diskspace again

du -h --max-depth=1 /

it returns

0/sys
1.9G/usr
12M/opt
0/dev
16K/lost+found
4.0K/mnt
21M/root
21G/var
4.0K/srv
146M/boot
4.0K/home
4.0K/media
7.8M/etc
980K/run
du: cannot access '/proc/646908/task/646908/fd/4': No such file or directory
du: cannot access '/proc/646908/task/646908/fdinfo/4': No such file or directory
du: cannot access '/proc/646908/fd/3': No such file or directory
du: cannot access '/proc/646908/fdinfo/3': No such file or directory
0/proc
1021M/snap
80K/tmp
24G/

Wow now i have 25GB which is fair considering i’m using wordpress

Way forward

you will need to set MySQL binary log files expiry duration so it cloud automatically clear it to prevent same issue over and over again and here is how to do that

mysql -u root -p

it returns

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 57091
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

use this command line

1	expire_logs_days=3

In MySQL 8.0, use binlog_expire_logs_seconds instead, where the default value is 2592000 seconds (30 days). In this example, we reduce it to only 3 days (60 seconds x 60 minutes x 24 hours x 3 days):

12	mysql> SET GLOBAL binlog_expire_logs_seconds = (60*60*24*3);mysql> SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);

SET PERSIST will make sure the configuration is loaded in the next restart. Configuration set by this command is stored inside /var/lib/mysql/mysqld-auto.cnf.

In conclusion always endeavor to check your diskspace first when ever you mysql refuse to start

If the log files are listed here, they are still maintained by MySQL and should be removed using the PURGE BINARY LOGS command detailed earlier.

Resources:

• MySQL 5 Reference Manual

The above tutorial was inspired by my Webhosting platform Digital Ocean one of the best affordable hosting platforms out there get $100 in credit over 60 days using my referral link and stay tuned as I will be covering loads of more interesting tutorial on managing your website effectively

If you enjoy this tutorial and you find it helpful don’t forget to share and leave a comment especially those who encounter issues and I will be gladly available to sort your problem you can also hire us