[SOLVED] Attempting to recover Mysql Database

[xboxboy]

Basically, I have a system that the HDD is very sick: (I've not dumped the database: I want to use that disc as little as possible given it's just logging smart errors constantly)

I was advised, that I 'should' be able to take /var/lib/mysql and drop it into a clean os/system and it 'should' work.
But, it's not that simple it appears: So far:

I've confirmed that mysql is working/starting correctly. I then stopped server with 'systemctl stop mysql', confirmed it was stopped with 'systemctl status mysql'.
I then renamed /var/lib/mysql to /var/lib/mysqlOrig.

I then copied my sick HDD's /var/lib/mysql to the working system's /var/lib/mysql
Then issue:

Code: Select all

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


Then:

Code: Select all

systemctl status mariadb.service
× mariadb.service - MariaDB 10.5 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Sat 2022-04-30 23:19:17 ACST; 36s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 2831 ExecStartPre=/usr/libexec/mariadb-check-socket (code=exited, status=0/SUCCESS)
    Process: 2857 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
    Process: 2892 ExecStart=/usr/libexec/mariadbd --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER (code=exited, status=1/FAILURE)
   Main PID: 2892 (code=exited, status=1/FAILURE)
     Status: "MariaDB server is down"
        CPU: 1.608s

Apr 30 23:19:15 fedora systemd[1]: Starting MariaDB 10.5 database server...
Apr 30 23:19:15 fedora mariadb-check-socket[2831]: Socket file /var/lib/mysql/mysql.sock exists.
Apr 30 23:19:15 fedora mariadb-check-socket[2831]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically.
Apr 30 23:19:16 fedora mariadb-prepare-db-dir[2857]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Apr 30 23:19:16 fedora mariadb-prepare-db-dir[2857]: If this is not the case, make sure the /var/lib/mysql is empty before running mariadb-prepare-db-dir.
Apr 30 23:19:16 fedora mariadbd[2892]: 2022-04-30 23:19:16 0 [Note] /usr/libexec/mariadbd (mysqld 10.5.13-MariaDB) starting as process 2892 ...
Apr 30 23:19:17 fedora systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Apr 30 23:19:17 fedora systemd[1]: mariadb.service: Failed with result 'exit-code'.
Apr 30 23:19:17 fedora systemd[1]: Failed to start MariaDB 10.5 database server.
Apr 30 23:19:17 fedora systemd[1]: mariadb.service: Consumed 1.608s CPU time.


I'm not actually sure where to go from here: I can't determine what the actual fault is, much less work out what to do about it.
Any help most appreciated.

[Germ]

I found this. Don't know if its any help...

https://dev.mysql.com/doc/refman/8.0/en ... overy.html

[JoesCat]

According to information I recall hearing, somewhere on this website https://www.grc.com/sr/spinrite.htm when you begin seeing smartmon messages, the problem isn't linear, but will ramp-up. So, you really should consider getting any and all data off the drive ASAP.
If you're not sure you captured all info, you should take a snapshot/picture of the drive using clonezilla, which you can then drop onto another drive. For clonezilla to work properly, you need to have the drive in a static, mode, so you aren't logged in, but seeing it using another login, for example, use a clonezilla disk, or a mageia live distro, and fetch from there, not your normal login.

Some programs have an export/import, and you are better-off using those to fetch the info.
The reason for this is because some data can be held in a hybrid format, where some data are files located in one place, and mysql pointers to those files, so you would need to backup the mysql data, plus the files associated with the programs.

Make a list of all the places you think you have data, and then backup each one separately using the export for those programs.... example.... kmail, kaddessbook,......etc.
on the new drive, import those data, and check.

When satisfied, park the bad drive for a little while in case you might have missed anything....long term, consider destructive recycling the harddrive as it is a risk to you to work with a faulty drive, and a risk for you to throw-out a harddrive that could potentially still be read.

[xboxboy]

Thanks guys: I dug a bit deeper, and got into the mysql logs. Bad news, the data of my directory must have been corrupted when the HDD died: Complaints of number of pages not being consistent.
So I've restored and older dump file, and it's at least working: I still need to configure the system correctly, but I'm pretty close to having it all functional again.

[Germ]

Good deal.

[xboxboy]

I've got the nextcloud app running, but I've got a few warnings, and I can only log in as admin: but what this tells me, is I *should* be able to get it back to functional....

This time once it's up, it's getting raided drives and backups!!!! I just got too lazy.

[jiml8]

Sorry I didn't see this sooner. I might have been able to talk you through a procedure that would recover the system. You would need another drive and would use "dd conv=noerror,sync" to image the bad drive, which you would pack in ice while you were transferring.

You would be surprised how often that works.

But, having backups is a much better and much less risky way. My nextcloud data sits on a 10 drive RAID-6 NAS, and I still back it up

[xboxboy]

Hi Jim, I still haven't quite got it all sorted yet: For some reason, nextcloud is declaring my data directory as unwritable. It can access it, as I can see my files etc when logged in as a nextcloud user.

I have no doubt some level of recovery is possible: I once used ddrescue on a drive I'd accidentally wiped... it got everything back.