Adi R's picture

On my Turnkey-Lamp-14.1 with Wordpress the MySQL database became corrupt.

As a result MySQL service is failing to start - this of course is why we keep backups, so I proceeded to use 

tklbam-restore 1

However, both incremental restore and full restore give me the following ridiculous message:

SKIPPING MYSQL DATABASE RESTORE: MySQL service not running

Well, OF COURSE it is not running - because DB files are corrupt! Hence the need to restore! How can I restore all my database files, please?

Thanks!

Forum: 
Jeremy Davis's picture

Apologies on the slow response. There is lots going on behind the scenes at all levels here at the moment (personal dramas, TurnKey deep in new major version development, not to mention friends, colleagues, contributors and customers in Ukraine).

Firstly, it's well worth noting that v14.x has not been supported for some time and would likely have a number of unpatched security issues. I would highly recommend migrating your data to a newer, supported OS. Also if you aren't clear on the cause of the data corruption, then I would encourage you to investigate possible early signs of hardware issues.

Anyway, as you've discovered, unfortunately to backup and restore a database (be it MySQL/MariaDB, PostgreSQL or LDAP) the relevant database service must be running. So to be able to restore your backup, you'll need to work out why MySQL isn't running.


One option could be to restore your data to a new server. Note that v14.2 ISOs are still on the mirror although I'm not sure for how long... It is likely that a restore of your data to a v14.2 VM would "just work".

As per my note above, now might even be a good time to look at migrating your data to a newer version. Be aware though, that using TKLBAM may not "just work" when changing major versions. I would recommend just doing a minimalist restore if you are considering that path. I'm more than happy to help, but won't go into details unless you note your interest.


But turning to your immediate issues specifically; I'm pretty rusty on old v14.x these days. IIRC back then we were still using SysvInit init system (we use systemd now) so the way to find out relevant info is to check the logs. There should be relevant entries in the syslog (/var/log/syslog). There may also be a MySQL specific error log (in /var/log/mysql/ ?).

I'm not sure how comfortable/knowledgeable you are on the Linux CLI, but here's some pointers. To view syslog entries specifically related to MySQL, you can use grep. To just show the most recent results of that search you can use tail. So I suggest a command like this:

grep -i mysql /var/log/syslog | tail -30

That will show the 30 most recent lines from the syslog that include 'mysql' (case insensititve). For more detail, please see explain-shell.

I do also vaguely recall that back then occasionally there were issues of log file corruption (e.g. mysql interrupted during log write operations) and that would stop MySQL from starting. I've just had a bit of a search and found note of it on the TurnKey MySQL doc page. It is specifically related to the InnoDB engine, so may not be relevant (if you are not using InnoDB).


I hope that is of some help. If you continue to have issues and/or want more support to disanose the issue(s), please post back with more info (e.g. info from logs) and/or clarification of what you'd like more info/details on. I'll try to respond ASAP.

Adi R's picture

Thanks a lot of detailed response, I really appreciate it.

I am pretty comfortable with CLI and reviewed MySQL logs earlier, is how I know for sure that some DB files became corrupt. I was hoping for some "force" switch, but if that is not available - i'll find a way to unmount corrupt DB to ensure mysql starts, and then do restore again.

Not sure upgrade worth it, that server only hosts seldom access WP blog - I was looking at just migrating that to static hosting solution.

Thanks again, will update once resolved. Stay safe!

Jeremy Davis's picture

Hi there. I'm not sure where you might be up to with this, but I just had an idea.

IIRC the relevant database(s) files should be in /var/www/mysql (or something like that), so perhaps you could just rename that dir, then try starting MySQL. Obviously your data/databases will be gone, but with MySQL running, you should be able to restore your backup. I.e. something like:

mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
service mysql start

I haven't tested any of this and you may need to make some adjustments (e.g. the mysql username may not be 'mysql' (although fairly sure it is) and the data path might also be wrong.

Adi R's picture

Great idea, thanks - I will definitely try it this week. Just been very busy with other issues.

Will keep you posted!

Jeremy Davis's picture

Please note that the path noted in my previous post was wrong! Oops!

I've since updated it with the correct path. To reiterate, the correct path should be: /var/lib/mysql

Adi R's picture

It took many more hours than I hoped, but here's a high level recap how I got there:

  • Followed your suggestion first, removed my corrupt mysql directory and recreated, however mysql service still didn't start due to missing root permissions
  • I found a suggestion to create initial DB, after few commands initial DB got created and I was able to finally start mysql server without errors
  • I proceed immediatelly to restore with tkblam from full backup, and this time it showed no messages about "mysql not running". HOWEVER I found that it still didn't restore Any DB files. This got me really stuck for long time, I tried few older and older backups, but same result - no mysql files restored and no errors or warning messages.
  • Trying to figure it out I executed "service mysql status" - and this surprisingly gave me an error. It was permissions issue - apparently Debian uses some built-in account with pre-encoded password
  • Thinking that perhaps this was why tkblam was also failing to obtain proper status of mysql, I followed complex instructions to reset mysql root password, but then also needed to create special debian user for mysql access, with password from relevant config
  • This finally fixed "service mysql status" - it was reporting proper status of "mysql running"
  • I then proceeded to restore with tkblam again. This time it FAILED on other MySql permissions, however, it proceeded farther - I noticed that just before failure it restored "default" mysql db that probably holds permissions.
  • I rebooted again, hoping that this will force mysql to reload permissions from newly restored default DB. I then tried tkblam restore again - this time all permissions for restoring other DBs were correct, as it proceeded to restore ALL my databases! 
  • After another reboot - All looks back to normal! All wordpress sites running fine and connecting to their respective DBs.

So there are we, thanks for assistance and hope this helps someone. Maybe there is an easier way, perhaps by deleting only the specific corrupt DB files and not entire directory, but I haven't confirmed it.

Jeremy Davis's picture

Thanks for posting back with the writeup. Sorry to hear that it was so painful.

In retrospect, I should have considered the MySQL user accounts. Apologies on my oversight. If it had been a newer OS version then purging and reinstalling MySQL probably would have been a better way to go (although seeing as your server is so old, that probably wouldn't have been an easy option as the Debian repos are no longer available).

Add new comment