Recover MySQL from copied /var/lib/mysql

Question

I have spent most of the day trying to recover from a crash followed by a couple of bad decisions.

While copying a table from DB1 to DB2, using phpmyadmin, MySQL crashed and from that point, I just was not able to access MySQL in any way.

Heart started to beat faster than usual and after much searching online, I followed a suggestion that some said had worked for them - Well, it did not for me.

Faced with not being able to access MySQL nor run any command (they all failed with denied ... using password YES or NO) - I tried my passwords that I know are correct, I used them in my PHP code but nothing worked.

BAD MOVE: I proceeded to purge phpmyadmin and mysql.

Before doing so, I copied /var/lib/mysql to ~/mysql so I have a copy of the entire directory as it was as of the crash and prior to purge/resinstall.

I tried this: Recover MySQL /var/lib/mysql after update

but I am getting this error as I try to restart mysql

Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. failed!

systemctl status mysql.service shows

● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Sat 2021-08-21 06:20:09 UTC; 6min ago Process: 23904 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Process: 23912 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE) Main PID: 23912 (code=exited, status=1/FAILURE) Status: "Server startup in progress"

Aug 21 06:20:09 mail.shipsuites.com systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: Stopped MySQL Community Server. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: mysql.service: Start request repeated too quickly. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: mysql.service: Failed with result 'exit-code'. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: Failed to start MySQL Community Server.

Any suggestion will be greatly appreciated - I am at a loss here and nothing I find seems to work for me.

OS: Ubuntu 20 MySQL 8.0 (fresh install)

Answer 1

OK - I managed to get my DBs back and all is working well but not without much digging.

In hope that the next "oopsy" victim does not spent countless hours, here is what I went through and how I pulled myself out:

1. As mentioned on my OP, MySQL just crashed, was not able to access the DB from CLI nor phpmyadmin - no matter which user/password I tried (I do setup multiple thinking I may need them but I guess not)

2. Trying out some suggestions I found I ended up in what I thought was a worst situation - Feeling that I had no means to cleanly access MySQL and not being able to run mysql, mysqladmin, mysqldump nor any mysql related command due to it not recognizing any of my user/password combos, I opted to purge mysql and phpmyadmin

3. Before the purge, I copied /var/lib/mysql to ~/mysql - Better save the raw files as I hoped to find means to retrieve my DBs and the data (I think this was the only sound decision I made up until this point).

4. These steps from link I posted origionally helped to a point but very crucial none the less

   #make sure mysql isn't running sudo service mysqld stop

   #double check that there is no MySQL PID running sudo ps aux | grep mysql

   #move the new MySQL 8.0 data files out of the way sudo mv /var/lib/mysql /var/lib/mysql.bak

   #copy the original data files back to /var/lib/mysql #note that the trailing / is required for both paths sudo rsync -av /path/to/original/mysql/db/files/ /var/lib/mysql/

   #change user and group ownership to mysql sudo chown -R mysql:mysql /var/lib/mysql

5. The above rsync command a true life saver, got the files back where needed. At this point, I had already installed a new version of MySQL and still was not able to access the data due the very same problem - access denied.

6. Here are some of the links I read through and from each, got enough bits of info to get what I needed

https://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/

https://www.techrepublic.com/article/how-to-set-change-and-recover-a-mysql-root-password/

https://www.tecmint.com/reset-root-password-in-mysql-8/ https://stackoverflow.com/questions/54292491/how-to-fix-error-1726-hy000-storage-engine-myisam-does-not-support-system-t

7. One major problem I ran into was having tables/DBs in both MyISAM and InnOB so, the last link brought to light the needed options I had to use to get pass that, such as --upgrade=FORCE and --initialize. The ALTER TABLE commands were the true turning point and things started to flow from there

Most articles I found make no mention of the need to use FLUSH PRIVILEGES; in order to successfully run any command when you start mysql with --skip-grant-tables set ON.

I came back here to post this and let you all know what/how I solved my problem - I wrote a shell script to dump my DBs and one to copy the dumped data to my home office server; I now have two daily copies of my DBs - Lesson learned!