1

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

Community
  • 1
user5950
  • 113
  • 3

1 Answers1

1

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.

danblack
  • 7,719
  • 2
  • 10
  • 27
  • 1
    Not forgetting Percona's XtraBackup - best solution IMHO! The Percona XtraBackup tools provide a method of performing a hot backup of your MySQL data while the system is running. Percona XtraBackup is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL®. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows. – Vérace Nov 04 '19 at 10:20
  • Small point to keep in mind about the (slight) difference between a mysqldump backup and an XtraBackup one. – Vérace Nov 04 '19 at 10:25