# Is it possible to backup and restore InnoDB tables on Oracle's MySQL 5.7.19 Server? **URL:** \ **Category:** Percona XtraBackup\ **Created:** [July 28, 2017, 11:46am UTC](https://forums.percona.com/t/is-it-possible-to-backup-and-restore-innodb-tables-on-oracles-mysql-5-7-19-server/5774 "2017-07-28T11:46:07Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![zentavr](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/zentavr/32/869_2.png) [@zentavr](https://forums.percona.com/u/zentavr)\ **Post date:** [July 28, 2017, 11:46am UTC](https://forums.percona.com/t/is-it-possible-to-backup-and-restore-innodb-tables-on-oracles-mysql-5-7-19-server/5774/1 "2017-07-28T11:46:07Z")
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. ```auto 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. ```auto 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: ```auto 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: ```auto 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: ```auto lzop -dcfU /home/forum.xbs.lzo | xbstream -x --directory=/home/forum.restore ``` Apply Log: ```auto innobackupex --apply-log --redo-only /home/forum.restore --use-memory=1G 2>/home/restore.log ``` Prepare the backup for usage: ```auto innobackupex --apply-log /home/forum.restore --use-memory=1G 2>>/home/restore.log ``` After that my directory has: ```auto 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: [LIST=1] [_]stop MySQL server [_]chown -R mysql:mysql /home/forum.restore/forum [_]mv /home/forum.restore/forum /var/lib/mysql/forum [_]start MySQL [/LIST] 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. ---
**Author:** ![zentavr](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/zentavr/32/869_2.png) [@zentavr](https://forums.percona.com/u/zentavr)\ **Post date:** [July 31, 2017, 4:03pm UTC](https://forums.percona.com/t/is-it-possible-to-backup-and-restore-innodb-tables-on-oracles-mysql-5-7-19-server/5774/2 "2017-07-31T16:03:30Z")
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. ---
**Author:** ![zentavr](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/zentavr/32/869_2.png) [@zentavr](https://forums.percona.com/u/zentavr)\ **Post date:** [July 31, 2017, 6:37pm UTC](https://forums.percona.com/t/is-it-possible-to-backup-and-restore-innodb-tables-on-oracles-mysql-5-7-19-server/5774/3 "2017-07-31T18:37:18Z")
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) 1. I did the restore without specifying the `--export`, so had the problems with those `.frm`’s. 2. If you have stored procedures/etc - maybe you need to backup them as well. I have no chance to check that.