Is it possible to backup and restore InnoDB tables on Oracle's MySQL 5.7.19 Server with Percona Xtrabackup?

Question

I have the next configuration:

OS: Ubuntu 16.04.2 x86_64 MySQL Server: Ver 5.7.19-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) installed from Ubuntu's repos

Percona Tools: innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7) installed from the Percona's repos.

The problem is the next: When I do the backup with innobackupex and then restore - my InnoDB tables got corrupted.

2017-07-28T15:49:50.078080Z 4 [Warning] InnoDB: Cannot open table forum/domain from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

The backup/restore happens on the same server. MySQL server has the defaults settings, innodb_file_per_table option is not set directly, but Oracle's MySQL has that enabled by the default.

mysql> show variables like '%innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

In order to do the backup, I do:

innobackupex --defaults-extra-file=/root/.my.cnf \
--include=forum.* --parallel=9 \
--extra-lsndir=/home/forum \
--stream=xbstream \
--no-timestamp /home/forum 2>/home/backup.log | lzop -c > /home/forum.xbs.lzo

the xtrabackup_info file from the result:

uuid = 850f3d9e-73ab-11e7-8b45-448a5b2c32e7
name =
tool_name = innobackupex
tool_command = --defaults-extra-file=/root/.my.cnf --include=forum.* --parallel=9 --extra-lsndir=/home/forum --stream=xbstream --no-timestamp /home/forum
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 5.7.19-0ubuntu0.16.04.1
start_time = 2017-07-28 17:43:27
end_time = 2017-07-28 17:43:36
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 14341896999
partial = Y
incremental = N
format = xbstream
compact = N
compressed = N
encrypted = N

As the restore, I do the next: unarchive the file:

lzop -dcfU /home/forum.xbs.lzo | xbstream -x --directory=/home/forum.restore

Apply Log:

innobackupex --apply-log --redo-only /home/forum.restore --use-memory=1G 2>/home/restore.log

Prepare the backup for usage:

innobackupex --apply-log /home/forum.restore --use-memory=1G 2>>/home/restore.log

After that my directory has:

root@restore-test /home/forum.restore # tree
.
├── backup-my.cnf
├── forum
│   ├── db.opt
│   ├── domain.frm
│   └── domain.ibd
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── xtrabackup_checkpoints
├── xtrabackup_info
└── xtrabackup_logfile

The final is:

1. stop MySQL server
2. chown -R mysql:mysql /home/forum.restore/forum
3. mv /home/forum.restore/forum /var/lib/mysql/forum
4. start MySQL

The database is there, but when i try to do any operation with the table (SHOW/DESCRIBE/etc) - I'm having the error listed above.

Answer 1

After doing some research and re-reading the manuals I found the next:

1. I had done so called partial backups, that mean that the restore process is a little bit different - you need to specify --export option when doing the restore process (see Restoring Individual Tables)
   • If you are on Percona Server you can try to use innodb_expand_import option and avoid of some routine operations.
   • If you are on MySQL server - you must also to do the schema dumps. Then do the tricks with ALTER TABLE xxx DISCARD TABLESPACE and ALTER TABLE xxx IMPORT TABLESPACE for an every single table. (See here)
2. I did the restore without specifying the --export, so had the problems with those .frm's.
3. If you have stored procedures/etc - maybe you need to backup them as well. I have no chance to check that.

Answer 2

If you want to backup database forum and all of its tables you should use this 'simplified' command:

innobackupex --databases="forum mysql" --user=root --password='secretPassword' /path/to/backup

We recommend to start using xtrabackup instead of innobackupex:

xtrabackup --databases="forum mysql" --backup --user=root --password='secretPassword' --target-dir=/path/to/backup

To prepare the backup:

innobackupex --apply-log /path/to/backup

xtrabackup --prepare --target-dir=/path/to/backup

You need to include the "mysql" database every time you backup.