Not the answer you need?
Register and ask your own question!

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

zentavrzentavr EntrantCurrent User Role Novice
Hello world!

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.

Comments

  • zentavrzentavr Entrant Current User Role Novice
    UPD: The backup with the same utillities, but without --include=.... parameters works fine and restore works as well. But I cannot skip other databases which exist on the same server.
  • zentavrzentavr Entrant Current User Role Novice
    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][1])
    - 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])
    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.


    [1]: https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/restoring_individual_tables_ibk.html
    [2]: https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.