Partial restore of MySQL backup

[GoWilkes]

I had a hard drive crash last night that apparently caused the loss of 60+ of my MySQL (well, MariaDB) tables. I don't understand the why or how, but... it did.

I have a backup from several months ago, but it only has 3 files:

example.create
example.sql
openfileslimit

If I can get this to work then I'll have only lost 6 months of data instead of 20 years worth... better than nothing!

How do I restore only the lost tables without overwriting the ones that are still there?

 

[andrew.n]

I believe the safest way to proceed is to restore the SQL file into a new database and then just only backup the tables you need and then restoring those tables.

 

[GoWilkes]

I'm trying to restore now, but I'm having problems.

cPanel wants me to restore from a local .sql instead of the file that's on the server; do I really need to download a 20G file just to reupload it?

WHM > Backup > Backup Restoration shows that there's a backup, but when I click it I get an error: 'Could not add "example" to the restoration queue (Archive does not exist for "example" at "2020-12-19".).' Even though it clearly does exist, since it gave me the option to select it.

Any other suggestions?

 

[andrew.n]

You need to do this via SSH to import the SQL file onto the new database. I suggest you to create a new MySQL user with the new database and then you can use this command to import the db:

mysql -u username -p database_name < /path/to/database.sql

 

[cPRex]

Let us know if that method doesn't work!

 

[GoWilkes]

It did, but I had to run REPAIR all 122 tables... not sure if that's because of a software update after the backup or what, but it's worth noting for future readers.

 

[andrew.n]

uhh how do you mean you had to? If they were corrupt when the backup was made..repairing the is the best you can do.

 

[GoWilkes]

They weren't corrupt when I made the backup, the backup was just when I moved from one server to a new one so this was a backup from the old server. The old one used MySQL 5.x, and the new one updated to MariaDB 10.3.

When I restored, I saw that PHP could select but not insert. I didn't do a lot of research on why, my first reaction was to run REPAIR on one of the main tables and saw that it fixed the problem. After I ran across the same problem in another table, I ran REPAIR on all of them. And there haven't been any new errors after that.

I'm just guessing that it's because the backup was from an older version of MySQL?

 

[andrew.n]

Interesting, in theory it shouldn't be but I'm glad all turned out fine, great job

 

[GoWilkes]

Bad news, I found out today that ALL of my hosting clients that use Wordpress were also affected. Wordpress mostly uses InnoDB, and for whatever reason it looks like all of them are set to read-only.

I don't think there's any data loss for them, though, so that's good

But with literally hundreds of them, it's gonna be some work to delete and restore them all. Worse, since it's with Wordpress, I suspect that I'll need to recreate the database and user with the same password as the original? I have no clue what those are, so that should be fun...

 

[andrew.n]

and on the original server those are not read-only? If that's the case it is something you can Submit a ticket about or alternatively reach out of one of the cPanel certified support providers to look into this for you.

 

[ffeingol]

Can you explain exactly how you are backing up and restoring?

A MySQL dump/backup is simply a series of 'create table' and then 'inserts'. For the most part, as long as the new version of the DB supports the syntax of the old it will restore fine. If not, you'll simply get an error that it does not understand the syntax.

Innodb storage engine stores things differently than myisam. WordPress (at least newer WordPress) will normally use the innodb storage engine for tables. Are you seeing any error messages in the MySQL/MariaDB error log?

 

[GoWilkes]

Can you explain exactly how you are backing up and restoring?

For the hosted clients, I haven't started yet. I only do things like that after midnight so that it doesn't disrupt their work day, and I discovered this problem just before going to bed last night, so I'm planning to work on it tonight.

Generally speaking, though, I would use PMA and Export to SQL. Then my understanding is to drop the database (if it will let me; on my site, it threw an error when I tried to drop a table so I had to delete it manually), delete the user, recreate it in cPanel (in this case it would have to be with the same name, so that Wordpress can find it), create a new user in cPanel and add it to the database, then log back in to PMA and Import the SQL that I exported before.

Innodb storage engine stores things differently than myisam. WordPress (at least newer WordPress) will normally use the innodb storage engine for tables. Are you seeing any error messages in the MySQL/MariaDB error log?

Not since the original crash, no. As far as I can tell, the error originated in an InnoDB table in my own site (which has now been rebuilt as MyISAM), and after I deleted it there haven't been any more errors. But it somehow trickled down to force all of the InnoDB tables on the server to be read-only, not just the one that was throwing errors in the log.

I've also read a lot of sites talking about having the same problem with InnoDB :-/ Like this one from 2018:

InnoDB is in read only mode.

johnny.heliohost.org I'm trying to connect mysql from node. It shows InnoDB is in read only modewhen I write/update/drop tables.Please help. It was working properly before. Suddenly it started showing this error.

www.helionet.org

I think I'm going to change ALL tables to MyISAM in an attempt to avoid this problem in the future.

 

[GoWilkes]

Well... that didn't work :-(

Using PMA, I exported the database and downloaded it as an SQL file.

Then in cPanel I tried to delete the database but it came back with an error. I don't remember the exact message, but it said that it couldn't be deleted. But then I clicked "Go back" and it didn't show up.

When I went to /var/lib/mysql, though, it DID show up. So I renamed it.

Then back in cPanel, I created the database again with the same name (so that Wordpress can find it), then added the user. No errors.

Then I log in to PMA, click on Import... I get an error that it can't create the tables because they're read-only :-O

 #1005 - Can't create table `example_new`.`wp_bp_activity` (errno: 165 "Table is read only")

Next attempt, I created another database with a new name, then in PMA tried to import the SQL file... nope, same error.

So... now what?

 

[GoWilkes]

Update:

I opened the SQL file in Notepad, and replaced:

ENGINE=InnoDB

with

ENGINE=MyISAM

PMA wouldn't let me drop the original table, so I created example_new and imported the SQL to that, So far so good!

But since I can't drop the original table, I can't rename example_new to example_info.

So I FTPed in and went to /var/lib/mysql/, and renamed example_info to example_info_backup. Still can't rename example_new in PMA, so I tried just renaming the directory via FTP.

You would think that would work, but no, PMA still shows those tables as read-only InnoDB!

I suspect that it's showing data from cache? I tried repairing the tables, but of course that doesn't do anything for InnoDB so it didn't help. And restarting MySQL didn't help, either.

Worse, when I try to just open example_info in PMA, the server load skyrocketed to 50+! Then I found that a lot of my MyISAM tables on my main site (unrelated to example) have crashed and need to be repaired.

 

[ffeingol]

This is only a SWAG, but I doubt that MySQL is going to recognize that you renamed the database unless you restart MySQL. Having said that, I'm not sure that your going to want to restart MySQL. If your innodb storage is corrupt, there is a decent chance that MySQL won't restart because it's not going to be able to apply any rollback/rollforward.

Getting rid of all the innodb tables is only going to be a short term solution. Any new tables that get created (from plugins, for example) are prob. going to default to be innodb.

 

[GoWilkes]

I was nervous about restarting MySQL, too, but when the server load skyrocketed at 4pm, it restarted MySQL on its own :-O I haven't seen any data loss from it, so I guess it's OK?

Restarting didn't force it to recognize the new database, either :-/

I'm honestly kind of lost on what I should be doing. This is the explanation from the server provider as to the original problem:

The host was reporting a disk image corruption and it's a bit vague as to what that means. Fortunately, sys boss has seen this exact thing before and was able to recognize it as a virtual disk image issue. Most likely something was simply written poorly by an operating daemon within the container and caused a corruption. Not a big deal, since that sort of thing happens all the time on servers. However, in this case it prevented your VPS container from starting, as the corruption has to be addressed with a manual check, which involves mounting the container disk image outside the container.

At that point they thought it was fixed, but I found that MySQL / MariaDB was still not responding. They replied with:

Yes, you have some corrupted database tables causing innodb problems that prevent MySQL (mariadb) from starting.

and then stated that they would fix it, which they did by adding "innodb_force_recovery=4" to my.cnf and restarting MySQL. And THIS is what (I think) caused the loss of my 60+ tables.

So as far as I can tell, the original problem was on their end with the "disk image corruption", and they fixed it, but that led to corrupted InnoDB tables. So now I'm dealing with the nuclear fallout from a rare problem that would probably never happen again. Right?

 

[ffeingol]

Per the MySQL documentation: MySQL :: MySQL 8.0 Reference Manual :: 15.21.2 Forcing InnoDB Recovery

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes. Sets InnoDB to read-only.

Based on what you have described, there was some sort of file system corruption on your VPS. Your provider attempted to correct it (prob. a fsck) but it was not fully recovered. In order to get MySQl to start they had to innodb_force_recovery, which bypasses parts of the automatic recovery. It get things back 'up', but your MySQL is not in a good state.

 

[GoWilkes]

That's close, @ffeingol , they originally fixed (?) it with e2fsck. That got the drive back up, but not MySQL.

I'm a little irritated, though, because after that they more or less said that I'm on my own fixing the MySQL problem. Which doesn't seem fair to me if the corruption happened because of a hardware problem on their end? I don't do a lot of commercial web design anymore, but back when I did, I couldn't imagine a client having a problem with one of my scripts and me just blowing them off like that!

But anyway.

The problem I'm having is that everything points me to finding a general idea of the problem, and that's great, but nothing has helped me narrow it down to the specific table(s), or given me any clues on how to fix them when I find them. But this is straying pretty far from the original topic, so for the sake of future readers I created another thread on this issue:

MySQL / MariaDB error with InnoDB on startup

I've been dealing with a problem with InnoDB for several days now. So far it looks like the short term fix is to eliminate InnoDB entirely, but of course I think I'll just have problems again when/if someone creates a new table. It's notable that I have "innodb_force_recovery=4" set in my.cnf...

forums.cpanel.net