MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8457字,纯文字阅读大概需要13分钟。
内容图文
![MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7](/upload/InfoBanner/zyjiaocheng/471/89269ae4199a4221849046d0d6906a87.jpg)
way2
/usr/bin/innobackupex --defaults-file=/etc/mysql/3306.cnf /data0/sql --databases=‘test.t1 mysql performance_schema‘ --user=root --socket=/tmp/mysql_3306.sock
但在应用日志时都会出现同样的问题,报备份单个表所在的库的其他表的表空间不存在
[root@mysql-bcc01 sql]# innobackupex --apply-log --export 2016-10-08_17-53-09 161008 17:53:50 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2) xtrabackup: auto-enabling --innodb-file-per-table due to the --export option xtrabackup: cd to /data0/sql/2016-10-08_17-53-09 xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(802528374) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support not available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: The log sequence number 2342986 in the system tablespace does not match the log sequence number 802528374 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Doing recovery: scanned up to log sequence number 802528374 (0%) InnoDB: xtrabackup: Last MySQL binlog file position 2987, file name mysql-bin.000003 InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file ‘./ibtmp1‘ size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File ‘./ibtmp1‘ size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 802528374 xtrabackup: export option is specified. xtrabackup: export metadata of table ‘test/t1‘ to file `./test/t1.exp` (1 indexes) xtrabackup: name=PRIMARY, id.low=32, page=3 xtrabackup: export metadata of table ‘mysql/slave_relay_log_info‘ to file `./mysql/slave_relay_log_info.exp` (1 indexes) xtrabackup: name=PRIMARY, id.low=19, page=3 xtrabackup: export metadata of table ‘mysql/innodb_index_stats‘ to file `./mysql/innodb_index_stats.exp` (1 indexes) xtrabackup: name=PRIMARY, id.low=18, page=3 xtrabackup: export metadata of table ‘mysql/slave_worker_info‘ to file `./mysql/slave_worker_info.exp` (1 indexes) xtrabackup: name=PRIMARY, id.low=21, page=3 xtrabackup: export metadata of table ‘mysql/innodb_table_stats‘ to file `./mysql/innodb_table_stats.exp` (1 indexes) xtrabackup: name=PRIMARY, id.low=17, page=3 xtrabackup: export metadata of table ‘mysql/slave_master_info‘ to file `./mysql/slave_master_info.exp` (1 indexes) xtrabackup: name=PRIMARY, id.low=20, page=3 InnoDB: xtrabackup: Last MySQL binlog file position 2987, file name mysql-bin.000003 xtrabackup: starting shutdown with innodb_fast_shutdown = 0 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Failed to find tablespace for table `test`.`t2` in the cache. Attempting to load the tablespace with space id 113 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: Cannot open datafile for read-only: ‘./test/t2.ibd‘ OS error: 71 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: Could not find a valid tablespace file for `test/t2`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. InnoDB: Shutdown completed; log sequence number 802528393 InnoDB: Number of pools: 1 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 104857600 InnoDB: PUNCH HOLE support not available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Setting log file ./ib_logfile101 size to 100 MB InnoDB: Progress in MB: 100 InnoDB: Setting log file ./ib_logfile1 size to 100 MB InnoDB: Progress in MB: 100 InnoDB: Setting log file ./ib_logfile2 size to 100 MB InnoDB: Progress in MB: 100 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=802528393 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 802528780 InnoDB: Doing recovery: scanned up to log sequence number 802528789 (0%) InnoDB: Doing recovery: scanned up to log sequence number 802528789 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 2987, file name mysql-bin.000003 InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file ‘./ibtmp1‘ size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File ‘./ibtmp1‘ size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 802528789 InnoDB: Failed to find tablespace for table `test`.`t2` in the cache. Attempting to load the tablespace with space id 113 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: Cannot open datafile for read-only: ‘./test/t2.ibd‘ OS error: 71 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: Could not find a valid tablespace file for `test/t2`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. xtrabackup: starting shutdown with innodb_fast_shutdown = 0 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 802528808 161008 17:53:57 completed OK!
查找结果是这是一个bug:https://bugs.launchpad.net/percona-xtrabackup/+bug/1532878
直接通过cp备份集到5.7环境下恢复并升级后
[root@mysql-bcc02 mysql]# mysql_upgrade --user=root --socket=/tmp/mysql_6666.sock
重启成功,但错误日志中报和上面相同的错误,既没有复制的表的表空间不存在
这又是一个bug:https://bugs.mysql.com/bug.php?id=82060
恢复部分备份
参考文献:https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_individual_tables_ibk.html
MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7
标签:
本文系统来源:http://www.cnblogs.com/Bccd/p/5939729.html
内容总结
以上是互联网集市为您收集整理的MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7全部内容,希望文章能够帮你解决MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。