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

Question

Due to a nightmare that somehow became real, my mysql installation ended up being reinstalled. I couldn't start mysql before the reinstall, so I couldn't use mysqldump to do a proper backup. I did however copy /var/lib/mysql to a safe place. Trying to rsync the database folders back into their old locations doesn't work -- well it sort of works, but then wordpress has a meltdown, even with the permissions corrected. If I create that database manually and then rsync it, mysql won't start.

Is it possible to restore this folder somehow?

EDIT: I now have it so that I can see the folder while in the mysql prompt. I can USE that database, but trying SELECT * FROM wp_posts; gives me

 mysql> SELECT * FROM wp_posts;
 ERROR 1146 (42S02): Table 'alfheimwp.wp_posts' doesn't exist

Despite the fact that

mysql> SHOW TABLES;
+-------------------------------------------------+
| Tables_in_alfheimwp                             |
+-------------------------------------------------+
| wp_bp_activity                                  |
| wp_bp_activity_meta                             |
| wp_bp_friends                                   |
| wp_bp_groups                                    |
| wp_bp_groups_groupmeta                          |
| wp_bp_groups_members                            |
| wp_bp_messages_messages                         |
| wp_bp_messages_meta                             |
| wp_bp_messages_notices                          |
| wp_bp_messages_recipients                       |
| wp_bp_notifications                             |
| wp_bp_notifications_meta                        |
| wp_bp_user_blogs                                |
| wp_bp_user_blogs_blogmeta                       |
| wp_bp_xprofile_data                             |
| wp_bp_xprofile_fields                           |
| wp_bp_xprofile_groups                           |
| wp_bp_xprofile_meta                             |
| wp_commentmeta                                  |
| wp_comments                                     |
| wp_links                                        |
| wp_options                                      |
| wp_postmeta                                     |
| wp_posts                                        |
| wp_sg_action                                    |
| wp_sg_config                                    |
| wp_sg_schedule                                  |
| wp_signups                                      |
| wp_term_relationships                           |
| wp_term_taxonomy                                |
| wp_termmeta                                     |
| wp_terms                                        |
| wp_ucare_logs                                   |
| wp_usermeta                                     |
| wp_users                                        |
| wp_woocommerce_api_keys                         |
| wp_woocommerce_attribute_taxonomies             |
| wp_woocommerce_downloadable_product_permissions |
| wp_woocommerce_log                              |
| wp_woocommerce_order_itemmeta                   |
| wp_woocommerce_order_items                      |
| wp_woocommerce_payment_tokenmeta                |
| wp_woocommerce_payment_tokens                   |
| wp_woocommerce_sessions                         |
| wp_woocommerce_shipping_zone_locations          |
| wp_woocommerce_shipping_zone_methods            |
| wp_woocommerce_shipping_zones                   |
| wp_woocommerce_tax_rate_locations               |
| wp_woocommerce_tax_rates                        |
| wp_wpsp_agent_settings                          |
| wp_wpsp_attachments                             |
| wp_wpsp_canned_reply                            |
| wp_wpsp_catagories                              |
| wp_wpsp_custom_fields                           |
| wp_wpsp_custom_priority                         |
| wp_wpsp_custom_status                           |
| wp_wpsp_faq                                     |
| wp_wpsp_faq_catagories                          |
| wp_wpsp_panel_custom_menu                       |
| wp_wpsp_ticket                                  |
| wp_wpsp_ticket_thread                           |
+-------------------------------------------------+
61 rows in set (0.00 sec)

So there's obviously something about this database that mysql is missing, however this is the exact same version of mysql that I was using before.

EDIT 2: Finally starting to get somewhere, but I'm deep down the rabbit hole and I need an innodb helper here... now mysql fails to start with this:

2017-10-13T01:55:16.625761Z 0 [ERROR] [FATAL] InnoDB: Tablespace id is 1121 in the data dictionary but in file ./mysql/help_relation.ibd it is 6!

The reason I couldn't read from the tables was because I didn't restore the innodb files into the mail /var/lib/mysql folder. Now that I have, though, these tablespace ids aren't lining up. I have no idea how to edit them or if that's even the way to solve it. I wish there was an automated way to correct these!

Answer 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).