3

I have an Ubuntu 16.04 server with ISPConfig 3.1.

For some reason the server was working too much on a process with high load, so I powered off and restarted the machine. After that, Mysql stopped working and couldn't be restarted anymore.

I checked the error log at /var/log/mysql/error.log but it's empty.

When I tried to restart the service and I got the following messages:

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

systemctl status mysql.service:

● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
Active: failed (Result: exit-code) since jue 2019-08-22 18:58:05 -04; 41s ago
Docs: man:systemd-sysv-generator(8)
Process: 12020 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
Process: 12056 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

journalctl -xe:

ago 22 18:58:32 servidor1 mysqld[12230]: /usr/sbin/mysqld[0x955d0c]
ago 22 18:58:32 servidor1 mysqld[12230]: /usr/sbin/mysqld[0x93b08e]
ago 22 18:58:32 servidor1 mysqld[12230]: /usr/sbin/mysqld[0x93cd7f]
ago 22 18:58:32 servidor1 mysqld[12230]: /usr/sbin/mysqld[0xa1dd9d]
ago 22 18:58:32 servidor1 mysqld[12230]: /usr/sbin/mysqld[0xa670b6]
ago 22 18:58:32 servidor1 mysqld[12230]: /usr/sbin/mysqld[0x9ba375]
ago 22 18:58:32 servidor1 mysqld[12230]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f8ba90256ba]
ago 22 18:58:32 servidor1 mysqld[12230]: x86_64/clone.S:111(clone)[0x7f8ba84cc41d]
ago 22 18:58:32 servidor1 mysqld[12230]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
ago 22 18:58:32 servidor1 mysqld[12230]: information that should help you find out what is causing the crash.


ago 22 18:58:52 servidor1 dovecot[904]: auth-worker(12400): Error: sql(dmiranda@comercialkod.cl,152.172.252.195): Password query failed: Not connected to database
ago 22 18:58:52 servidor1 dovecot[904]: auth: Error: auth worker: Aborted PASSV request for dmiranda@comercialkod.cl: Lookup timed out
ago 22 18:58:52 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:58:53 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:58:54 servidor1 dovecot[904]: imap-login: Aborted login (auth failed, 1 attempts in 62 secs): user=<dmiranda@comercialkod.cl>, method=PLAIN, rip=152.172.252.19
ago 22 18:58:58 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:59:01 servidor1 CRON[12687]: pam_unix(cron:session): session opened for user root by (uid=0)
ago 22 18:59:01 servidor1 CRON[12686]: pam_unix(cron:session): session opened for user root by (uid=0)
ago 22 18:59:01 servidor1 CRON[12688]: (root) CMD (/usr/local/ispconfig/server/cron.sh 2>&1 | while read line; do echo `/bin/date` "$line" >> /var/log/ispconfig/cron.log
ago 22 18:59:01 servidor1 CRON[12689]: (root) CMD (/usr/local/ispconfig/server/server.sh 2>&1 | while read line; do echo `/bin/date` "$line" >> /var/log/ispconfig/cron.l
ago 22 18:59:01 servidor1 CRON[12686]: pam_unix(cron:session): session closed for user root
ago 22 18:59:23 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:59:25 servidor1 su[12004]: pam_unix(su:session): session closed for user root
ago 22 18:59:25 servidor1 sudo[12003]: pam_unix(sudo:session): session closed for user root
ago 22 18:59:27 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:59:28 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:59:33 servidor1 dovecot[904]: auth-worker(12673): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server throug
ago 22 18:59:34 servidor1 sudo[12790]: surempresa : TTY=pts/20 ; PWD=/home/surempresa ; USER=root ; COMMAND=/bin/su
ago 22 18:59:34 servidor1 sudo[12790]: pam_unix(sudo:session): session opened for user root by (uid=0)
ago 22 18:59:34 servidor1 su[12809]: Successful su for root by root
ago 22 18:59:34 servidor1 su[12809]: + /dev/pts/20 root:root
ago 22 18:59:34 servidor1 su[12809]: pam_unix(su:session): session opened for user root by (uid=0)
ago 22 18:59:34 servidor1 su[12809]: pam_systemd(su:session): Cannot create session: Already running in a session

However, I managed to restart the service after I tried the solution from this post:

Can't start MySQL server (database corruption)

So I set:

[mysqld]
innodb_force_recovery = 9

But, it doesn't work well since some database tables are now read only.

What should I do instead? Thank you.

DavidM
  • 131
  • 4

1 Answers1

0

For anyone reading this because their MySQL database crashed and is in need of (or using) innodb_force_recovery, be aware that you should start from innodb_force_recovery=1 and slowly work your way up until MySQL starts. If you are able to dump your tables with an innodb_force_recovery value of ``3 or below, then you can be sure that all of your data, stored procedures, triggers, and views are intact.

In some cases you may need to use 4 or above. Be aware that once you’re at this level, there is a high probability of data corruption. The highest innodb_force_recovery value is 6 and all higher values are treated the same as 6. If you’re here, I sincerely hope you have a recent backup to restore from.

Because a corrupt InnoDB table should never again be trusted, the correct way to handle this situation is to drop the database and restore. This will rebuild all of the files.

From the command line:

  1. Dump the database (if you don’t have a recent backup to rebuild from): 
    sudo mysqldump --routines --triggers --add-drop-database --add-drop-table DB_NAME > panic.sql
  2. Stop MySQL: 
    sudo service mysqld stop
  3. Delete the corrupt database files: 
    cd /var/lib/mysql
rm -Rf <DB_NAME>
  4. Comment out the innodb_force_recovery line in my.cnf: 
    # innodb_force_recovery=…
  5. Restart MySQL: 
    sudo service mysqld start
  6. Connect to MySQL as root and import the database: 
    mysql> tee import_database.log
mysql> source panic.sql

From this point you may need to confirm that user accounts are able to connect and use the database.

May you never need to do this for a production system …

matigo
  • 22,138
  • 7
  • 45
  • 75