Is it possible to restore a mysql database from the /var/lib/mysql/database folder?

Solution 1:

Alright, here goes. So, you need to do a full purge of mysql. Don't bother even trying to switch to mariadb at this point, it simply won't work (unable to change root password despite deleting every file related to mysql).

[EDIT: I later realized this was because I was not running mariadb as root. For some reason, if you have mariadb installed you must start a prompt with sudo mysql -u root -p. So in theory mariadb should work for this process as well.]

You need to do a fresh install of mysql-server. Start by removing everything mysql-related with

sudo apt-get purge mysql-server* mariadb*

Then remove all the mysql-related folders (make sure you have a safe backup already of the entire /var/lib/mysql folders).

sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql
sudo rm -rf /var/log/mysql

Then reinstall mysql-server. If it fails with the silly dependency error, do

ln -s /etc/mysql/mysql.conf.d /etc/mysql/conf.d

(or the other way round, can't remember now) then run

sudo apt-get install mysql-server 

again and let it finish setting up. Stop the mysql server with

sudo systemctl stop mysql

Then copy JUST the database folders (and all their content) back to /var/lib/mysql. Then also copy ib* to /var/lib/mysql as well (these are the innodb dictionary and other files).

sudo rsync -r <backedupfolder>/ib* /var/lib/mysql/

Make sure to do

sudo chown -R mysql:mysql /var/lib/mysql 

Edit /etc/mysql/my.cnf and add innodb_force_recovery=5 to the [mysqld] section (you might need to add [mysqld]). Now start mysql again with systemctl start mysql. If you look at /var/log/mysql/error.log you might still see errors, however try getting into a prompt with

mysql -u root -p

If you're able to start it up, great. If not, take another look at systemctl status mysql.service and see what it says. It might be a permisssions issue. If you were finally able to get into a prompt, immediately do

mysqldump -u root -p <databasename> > database.sql 

so we can get a clean dump of that database. Now that we have a clean dump of the important databases, we need to redo the first few steps all over (at least I did, to be sure... I was able to start up WordPress at this point but I couldn't log in). So redo the first few cleaning steps, remove all the mysql directories after uninstalling mysql-server. Reinstall, but this time just run

 mysql -u root -p <databasename> < database.sql

And you should be golden! You might need to re-create the databases (CREATE DATABASE databasename;) Don't forget to re-create the user that owned that database before (if you've forgotten, look in your /var/www/html/wp-config.php or wherever else your wp-config.php is for the details).