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!
Best Answer
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
Then remove all the mysql-related folders (make sure you have a safe backup already of the entire /var/lib/mysql folders).
Then reinstall mysql-server. If it fails with the silly dependency error, do
(or the other way round, can't remember now) then run
again and let it finish setting up. Stop the mysql server with
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).
Make sure to do
Edit /etc/mysql/my.cnf and add
innodb_force_recovery=5to the[mysqld]section (you might need to add [mysqld]). Now start mysql again withsystemctl start mysql. If you look at/var/log/mysql/error.logyou might still see errors, however try getting into a prompt withIf 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
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
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).