Is it possible to restore a mysql database from the /var/lib/mysql/database folder?
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).
Related videos on Youtube
Aurelius
Updated on September 18, 2022Comments
-
Aurelius almost 2 years
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!
-
s1mmel about 6 yearstake a look at files per tables and the innodb engine, you might like it ;-)
-
guest almost 6 yearsThanks alot! However I had to set
innodb_force_recovery=6
in /etc/mysql/my.cnf to get into MySQL prompt. -
Eric F. over 5 yearsThanks a million!! I had to delete the log files a couple times to get the server to start, but after that it worked!