6

I had a perfectly fine wordpress setup, but today the server suddenly stopped loading my site. I logged in and restarted my centos 7 VPS. After the reboot it did not started the MariaDB. Here is what I found in my log:

141026 18:13:50 [Note] /usr/libexec/mysqld: Shutdown complete

141026 18:13:50 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
141026 18:14:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141026 18:14:58 InnoDB: The InnoDB memory heap is disabled
141026 18:14:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141026 18:14:58 InnoDB: Compressed tables use zlib 1.2.7
141026 18:14:58 InnoDB: Using Linux native AIO
141026 18:14:58 InnoDB: Initializing buffer pool, size = 128.0M
141026 18:14:58 InnoDB: Completed initialization of buffer pool
InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1
141026 18:14:58 InnoDB: Could not open or create data files.
141026 18:14:58 InnoDB: If you tried to add new data files, and it failed here,
141026 18:14:58 InnoDB: you should now edit innodb_data_file_path in my.cnf back
141026 18:14:58 InnoDB: to what it was, and remove the new ibdata files InnoDB created
141026 18:14:58 InnoDB: in this failed attempt. InnoDB only wrote those files full of
141026 18:14:58 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
141026 18:14:58 InnoDB: remove old data files which contain your precious data!
141026 18:14:58 [ERROR] Plugin 'InnoDB' init function returned error.
141026 18:14:58 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
141026 18:14:58 [Note] Plugin 'FEEDBACK' is disabled.
141026 18:14:58 [ERROR] Unknown/unsupported storage engine: InnoDB
141026 18:14:58 [ERROR] Aborting

Can someone help me investigate what could be the problem? Is there a possibility now to backup my database without being able to start MariaDB?

Peter
  • 235
  • 2
  • 3
  • 11

2 Answers2

11

Edit: copied my-innodb-heavy-4G.cnf to /etc/my.cnf.d and the server started fine. Probably caused by changing the binlog size.

It sounds like you changed the size of your InnoDB buffer, or it became corrupted. Possible that these changes were made some time ago and just took effect when you rebooted.

Definitely make a backup of your data in /var/lib/mysql first - preferably the entire directory.

cp -R /var/lib/mysql /var/lib/mysql-backup

With your data backed up, the best first step is to revert any changes that you made to the /etc/my.cnf file and restart.

If that doesn't do it, or you don't know what the size of your buffer used to be, the next possible option is to delete the InnoDB binlog files and let MariaDB/MySQL recreate them. The files are named /var/lib/mysql/ib_logfile{N} where {N} is a number.

rm -rf /var/lib/mysql/ib_logfile*

If that doesn't solve the problem try adding the following to your /etc/my.cnf (or whichever config file you use, backup first of course). It should go in the [innodb] section.

innodb_buffer_pool_size        = 256M
innodb_log_file_size           = 256M
innodb_thread_concurrency      = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method            = normal

The trick is knowing what MariaDB is expecting. If you don't have success with the above try just the last line and set [innodb_flush_method].

doublesharp
  • 26,888
  • 6
  • 52
  • 73
  • I never changed the buffer. in my.cnf there is nothing related to buffer size. I made a backup now as you suggested thank you. I tried moving ib_logfiles as you suggested but it did not help. – Peter Oct 26 '14 at 21:34
  • See my edits for some more things to try - I'm guessing this is a VPS and may have been moved to a new hypervisor when you rebooted? – doublesharp Oct 26 '14 at 21:53
  • There is no [innodb] section in my.cnf therefore I added one and copied what you wrote and it did not help. I tried adding just innodb_flush_method = normal that did not help either. Yes this is a VPS. I'm not aware that it would have been moved to a new hypervisor. Do you have more suggestion? it would be very important to recover that database. – Peter Oct 26 '14 at 22:06
  • Sorry, just double checked my server and those settings are under `[mysqld]` - you are right there is no `[innodb]` section. My config also uses `/etc/my.cnf` to load all the cnf files in `/etc/my.cnf.d/`, but I'm on CentOS 5. I have no idea what might have happened when you rebooted, but since it's a VPS in theory it could be on a different host with different settings... unlikely but possible. – doublesharp Oct 26 '14 at 22:16
  • I think the most likely thing is that a setting changed and you didn't see it until you rebooted - have you done any yum updates, any changes related to the db cnf files, etc. If it did just become corrupted you can still recover data from the `ibdata` files but it's not a sure thing. – doublesharp Oct 26 '14 at 22:20
  • Saw another suggestion to try adding this to your config `innodb_use_sys_malloc = 0` – doublesharp Oct 26 '14 at 22:22
  • And unfortunately Google seems to mostly reference my own site for your errors: http://justinsilver.com/technology/upgrade-mysql-mariadb-centos/ – doublesharp Oct 26 '14 at 22:23
  • today my blog simply stopped opening. I sshd to the server, i think run yum update, and then restarted the server. – Peter Oct 26 '14 at 22:24
  • I would be that yum changed some of your mariadb settings. You can attempt to have the database do a recovery by adding `innodb_force_recovery = 1` and increase to 6 restarting each time until it works. – doublesharp Oct 26 '14 at 22:31
  • I tried innodb_force_recovery = 1 to 6 but that did not help too. – Peter Oct 26 '14 at 22:34
  • This appeared in the logfile if it helps: Server version: 5.5.37-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=0 max_threads=153 thread_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466717 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x0 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... – Peter Oct 26 '14 at 22:40
  • stack_bottom = 0x0 thread_stack 0x48000 /usr/libexec/mysqld(my_print_stacktrace+0x3d)[0x7f00ac82bebd] /usr/libexec/mysqld(handle_fatal_signal+0x515)[0x7f00ac4460d5] /lib64/libpthread.so.0(+0xf130)[0x7f00abb76130] /lib64/libc.so.6(gsignal+0x39)[0x7f00aa32e5c9] /lib64/libc.so.6(abort+0x148)[0x7f00aa32fcd8] /usr/libexec/mysqld(+0x6dbf29)[0x7f00ac681f29] /usr/libexec/mysqld(+0x6dc57a)[0x7f00ac68257a] – Peter Oct 26 '14 at 22:41
  • Sorry, I don't have any more suggestions without sitting down in front of it and hacking away... – doublesharp Oct 27 '14 at 00:06
  • @Peter - mind marking this as the answer (this actually was your root problem, though you also had a lot of others) – doublesharp Oct 29 '14 at 18:29
  • copying 'my-innodb-heavy-4G.ini' to 'my.ini' did it for me on my Windows 10. – Ankur Jan 07 '16 at 09:24
  • Note that `innodb_flush_method = normal` only works under Windows. – Gwyneth Llewelyn Nov 07 '16 at 13:23
  • 1
    @GwynethLlewelyn good catch, and relevant since I use CentOS :) - here are all the valid options for anyone looking: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method – doublesharp Nov 07 '16 at 18:16
7

If you encountered this problem when you installed MySQL initially, just remove ibdata and ib_logfile* files in your MySQL data directory, and then start MySQL again.

Good Luck!

robinwen
  • 852
  • 10
  • 10