Home / ubuntu / Questions / 1008298
Accepted
Ionică Bizău
Asked: 2018-02-22 00:17:39 +0800 CST

Can't start MySQL server (database corruption)

  • 772

Context: my noteboook froze and I force restarted it. After restart, I can't seem to start the mysql server.

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

Then:

$ systemctl  status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Wed 2018-02-21 10:15:57 EET; 22s ago
  Process: 8611 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
  Process: 8605 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 8611 (code=exited, status=2); Control PID: 8612 (mysql-systemd-s)
    Tasks: 2 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─control
             ├─8612 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─9459 sleep 1

feb 21 10:15:57 notebook systemd[1]: Starting MySQL Community Server...
feb 21 10:15:57 notebook systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT

and:

feb 21 10:15:57 notebook audit[8611]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8611/status" pid=8611 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:15:57 notebook audit[8611]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=8611 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=0
feb 21 10:15:57 notebook audit[8611]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8611/status" pid=8611 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:15:57 notebook kernel: audit: type=1400 audit(1519200957.179:123): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8611/status" pid=8611 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:15:57 notebook kernel: audit: type=1400 audit(1519200957.179:124): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=8611 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=0
feb 21 10:15:57 notebook kernel: audit: type=1400 audit(1519200957.179:125): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8611/status" pid=8611 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:15:57 notebook systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
feb 21 10:16:23 notebook unity-scope-hom[10534]: scope.vala:247: Failed to get list of remote scopes: Request failed with error 2
feb 21 10:16:27 notebook systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
feb 21 10:16:27 notebook systemd[1]: mysql.service: Unit entered failed state.
feb 21 10:16:27 notebook systemd[1]: mysql.service: Failed with result 'exit-code'.
feb 21 10:16:27 notebook systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
feb 21 10:16:27 notebook systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has finished shutting down.
feb 21 10:16:27 notebook systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has begun starting up.
feb 21 10:16:27 notebook audit[9495]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/9495/status" pid=9495 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:16:27 notebook audit[9495]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=9495 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=0
feb 21 10:16:27 notebook audit[9495]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/9495/status" pid=9495 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:16:27 notebook kernel: audit: type=1400 audit(1519200987.677:126): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/9495/status" pid=9495 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:16:27 notebook kernel: audit: type=1400 audit(1519200987.677:127): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=9495 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=0
feb 21 10:16:27 notebook kernel: audit: type=1400 audit(1519200987.677:128): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/9495/status" pid=9495 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=128 ouid=128
feb 21 10:16:27 notebook systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT

Some relevant logs:

2018-02-21T08:05:30.279927Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-02-21T08:05:30.289022Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-02-21T08:05:30.292279Z 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-21T08:05:30.314528Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-21T08:05:30.318295Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 82335887334
2018-02-21T08:05:30.318321Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 82335916567
2018-02-21T08:05:30.324853Z 0 [Note] InnoDB: Database was not shutdown normally!
2018-02-21T08:05:30.324866Z 0 [Note] InnoDB: Starting crash recovery.
2018-02-21T08:05:30.411651Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=1579]. You may have to recover from a backup.
2018-02-21T08:05:30.411674Z 0 [Note] InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 43a34cfa0000062b0000000000000000000000132b99dd12000200000000000000000000000000010110011dffffffff0000ffffffff00000002005600000000000002cf2bb2000000010000062b002c0000062b002c000000000d552a9600000000000000000001011000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011d0b00824b048bc5686801106731546d78726f496b676a424d4f66634b497762496d4c36795401100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

What can I do to solve the problem?

I've tried to reinstall mysql-server, but that didn't solve the problem.

server mysql 17.10

3 Answers

  1. Best Answer

    I successfully recovered the data by editing /etc/mysql/my.cnf (depending on your configuration, my.cnf may have a different location) by adding:

    [mysqld]
innodb_force_recovery = 1

    And then running:

    sudo systemctl start mysql

    It didn't work for the first time and I incremented the number to 2, 3 etc, retrying to start after each configuration update. Eventually it started.

    Then I just I just commented out the innodb_force_recovery stuff and it restarted successfully (some operations are not allowed in the force recovery mode).

    This answer was very helpful.

    • 23

  2. I tried everything written above, but nothing worked for me until I dared to try the next thing, which is based on the opposite idea

    /etc/init.d/mysql stop
service mysql stop
killall -KILL mysql mysqld_safe mysqld
/etc/init.d/mysql start
service mysql start
    • 5

  3. This problem occures when your /etc/mysql/mysql.conf.d/mysqld.cnf file is wrongly set. If you recently made changes on this file, please revert to the previous one or copy the default one from here

    #
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
#expire_logs_days   = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
    • 0