Innodb – How to restore a database with innodb tables from files

innodbmariadbrestore

I am trying to restore a MariaDB database containing innodb tables from files. Blue-eyed as I am, I considered this an easy task: I just stopped the server, copied the files and restarted it as follows:

# Stop MariaDB server
systemctl stop mariadb.service
# Copied database 
sudo rsync --numeric-ids -aHAEXv --rsh="ssh" user@mybackupserver:/xyz/mysql/owncloud /var/lib/
# Start MariaDB server
systemctl start mariadb.service

Unfortunately this does not work as expected. Although the database and all it's tables are there, I can't access any table. Trying to do so I get:

1932 – Table 'owncloud.oc_activity_mq' doesn't exist in engine.

I than tried to create an empty database of the same name before I repeated all steps above, but I got the same error.

Oddly in PHPMyAdmin all tables have the Collation in use.

I checked permissions and ownership of the directory a dozen times, everything within my MariaDB data directory is owned by the user mysql and every file has the permission 0660 every directory 0700

My question is, is it possible to restore a single database with innodb tables directly from an old data directory to a new one? And if it is, how?

I know this question came up before, for example here: How to restore MySQL database from Files

But I am asking here specifically for a database with innodb engine, as off what I read, I got the impression the problems I ran in, might have to do with the engine

Best Answer

With innodb files you need to restore the entire database server to a new instance.Use mysqldump oc_activity_mq to extract a SQL version of the table out of that backup. And then import this into your active instance.

This assumes you've made consistent backup. If mysql was running and being updated at the same time it was copied it might have some form of corruption.

Revist your backup strategy using innodbbackup, lvm, and/or mysqldump/pump.