So looks like I’m having some issue, the MySQL process is creating a huge log file that is making the system crash.
Failed to start database : Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
-rw-r----- 1 mysql mysql 119936856 Feb 3 10:53 error.log
-rw-r----- 1 mysql mysql 123839466 Feb 3 10:53 error.log
And growing.
This started yesterday without any reason as far as I know.
You’d need to post the last lines from the error log or the output of journalctl -xe because that’ll tell you what the issue is, or at least give a general direction.
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Fri 2023-02-03 14:27:38 -03; 2s ago
Process: 101453 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Process: 101468 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
Main PID: 101468 (code=exited, status=2)
Status: “Server startup in progress”
Feb 03 14:27:30 c946. systemd[1]: Starting MySQL Community Server…
Feb 03 14:27:38 c946 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Feb 03 14:27:38 c946 systemd[1]: mysql.service: Failed with result ‘exit-code’.
Feb 03 14:27:38 c946 systemd[1]: Stopped MySQL Community Server.
InnoDB: End of page dump
InnoDB: Page may be a transaction system page
2023-02-03T15:08:52.208740Z 1 [ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=0, page number=5] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/8.0/en/ for more details. Aborting…
2023-02-03T15:08:52.208763Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: buf0buf.cc:4054:ib::fatal triggered thread 139933160220416
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB:
InnoDB: about forcing recovery.
2023-02-03T15:08:52Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=13fe4cc6857ad6fa2a5024c674977ce812a055cc
Thread pointer: 0x55ecc3750a80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f44ba4e9be0 thread_stack 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x55ecbd939fc1]
/usr/sbin/mysqld(print_fatal_signal(int)+0x39b) [0x55ecbc7be78b]
/usr/sbin/mysqld(my_server_abort()+0x76) [0x55ecbc7be8d6]
/usr/sbin/mysqld(my_abort()+0xe) [0x55ecbd933fae]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x349) [0x55ecbdbcad39]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xd5) [0x55ecbdbcd545]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::read_page()+0x1aa) [0x55ecbdc33bfa]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0x657) [0x55ecbdc3fbd7]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::single_page()+0x59) [0x55ecbdc3fcc9]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, ut::Location, mtr_t*, bool)+0x233) [0x55ecbdc41553]
/usr/sbin/mysqld(Double_write::get(mtr_t*)+0x56) [0x55ecbdc44be6]
/usr/sbin/mysqld(Double_write::init_v1(unsigned int&, unsigned int&)+0x11b) [0x55ecbdc49c5b]
/usr/sbin/mysqld(dblwr::v1::init()+0x1b) [0x55ecbdc49e6b]
/usr/sbin/mysqld(srv_start(bool)+0x1483) [0x55ecbdb6ca33]
/usr/sbin/mysqld(+0x2323656) [0x55ecbd979656]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x9e) [0x55ecbd6b725e]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x1a9) [0x55ecbd90c119]
/usr/sbin/mysqld(+0x122d776) [0x55ecbc883776]
/usr/sbin/mysqld(+0x2864f9d) [0x55ecbdebaf9d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8609) [0x7f44c9655609]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7f44c9226133]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 1
Status: NOT_KILLED
The manual page at contains
information that should help you find out what is causing the crash.
InnoDB: End of page dump
InnoDB: Page may be a transaction system page
2023-02-03T15:08:52.140109Z 1 [ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=0, page number=5]. You may have to recover from a backup.
len 16384; hex 446f450b0000000500000000000000000000001b4d8de1fa00070000000000000000000000000000000007d0e301000000000000000200f20000000000000006
Ah yes, sadly you are probably correct. A restore is the best option here, and there are many resources online for deleting the offending database, bringing mysql online and then restoring the offending database.
BEFORE you do that though, stop mysql and take a complete copy of everything in the database directory. You can find where this is by looking in /etc/my.cnf or /etc/my.cnf.d/server.cnf. It’ll be under datadir=. This will give you a copy before you go down the restore route and is just a safety thing, in case you discovery mid restore that your backup is corrupt, or that for whatever reason the backup failed back in 2020 and no one noticed, etc. It does happen.
I have a backup of all the Virtualservers and I’m working on that right now. My trouble is that I have one virtual server that has no backups, so the only data I have is the one on the service (now on a different folder to re-install the Mysql).
Do you think there is anyway I can troubleshoot or fix the issue?
There are ways to do it, but its not without challenges.
InnoDB (recent versions) store each db in a separate directory, and within that each table in a different pair of files (a .frm and a .ibd).
Now you CANNOT just copy/paste these around, of only it were that simple, but it is possible to re-attach tables to a new database (that mounts).
Search for restoring .ibd and .frm for numerous processes to do this, I’ve done it and I can say it works, but there’s no guarantees.
Whichever way you do it, once you have, then dump the whole thing with mysqldump then restore that dump to the live system. DO NOT rely on your fudged together db to be stable or work reliably, this is just a data recovery exercise.
Databases don’t generally get corrupted for no reason. You’ve got some kind of failing hardware, likely disk or RAM. It’s also possible to cause corruption just by having the database not shut down cleanly, but that’s much more rare. Though I’ve seen folks make it happen by setting it up to restart on fail on a system without enough memory, which causes it to start and stop a whole lot over time every time the OOM killer kills it, virtually guaranteeing corruption eventually (you roll the dice every time you yank the plug on a running database). You’ll want to figure out why you have corruption in one or more of your databases, and fix it, before relying on this system again.
There are tools for recovering a database, which usually loses some data, but that’s better than losing the whole database. Look into mysqlcheck (e.g. --auto-repair flag). Get as much as you can backed up before doing that, though, as it can make things worse, depending on the cause (a failing disk may keep getting worse losing more and more data whenever you write to it, for instance…reads are safer than writes).
Don’t ask me how, but I finally restored the DB after working the fucking whole day on it.
Looks like a moodle DB was corrupted (I have no idea why, maybe during the Backup creation), and crashed the whole service.
I fixed it with:
innodb_force_recovery = 3
The first two (1 and 2) didn’t work, but 3 just started the service with all the DB working except for that what. The good news is that I don’t care, because I have a previous back up without issues.
So, the steps I performed:
create a copy of the /var/lib/mysql/ folder to /var/lib/mysql_bkp/
