介绍: 本章内容为阿里云RDS Mysql和MongoDB 将物理备份文件.xb恢复到自建数据库的过程、环境搭建、错误解析
前提准备:
1: Centos7环境基础环境准备:?IP映射、关闭防火墙、Selinux关闭、swappiness修改、JDK配置等
目录：
一:安装percona-xtrabackup2.4和8.0版本工具
备份文件.xb的生成过程是先经过 qpress 压缩，后经过 xbstream 打包,所以恢复数据的过程是需要先解包在解压
Xtrabackup是一个对InnoDB做数据物理备份的工具,需要用此工具将准备好的.xb文件恢复到自建数据库,(不管是要恢复Mysql还是Mongodb都需要安装下载)
如果未按照Xtrabackup将会报错?-bash: xbstream: command not found
1.1 从Percona?存储库安装Percona XtraBackup Yum
存储库地址：Installing Percona XtraBackup 2.4
1.1.1:通过以root用户或sudo运行以下命令来安装Percona yum存储库?：
[root@iZm5e7euuomowjjtvoos85Z /]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
?
1.1.2:启用存储库：
[root@iZm5e7euuomowjjtvoos85Z /]# percona-release enable-only tools release
* Disabling all Percona Repositories
* Enabling the Percona Tools repository
<*> All done!
[root@iZm5e7euuomowjjtvoos85Z /]#?
?
1.1.3:如果Percona XtraBackup打算与MySQL服务器结合使用，只需要启用tools?存储库(可选)：
[root@iZm5e7euuomowjjtvoos85Z /]# percona-release enable-only tools
* Disabling all Percona Repositories
* Enabling the Percona Tools repository
<*> All done!
1.1.4安装Percona XtraBackup,根据mysql版本选其一即可
- 对于MySQL 5.7、5.6或5.5实例：安装Percona XtraBackup 2.4。
- 对于MySQL 8.0实例，安装Percona XtraBackup 8.0。
[root@iZm5e7euuomowjjtvoos85Z /]# yum install -y percona-xtrabackup-24? #安装2.4版本
[root@iZm5e7euuomowjjtvoos85Z /]# yum install -y percona-xtrabackup-80??#安装8.0版本
二: 安装解压工具qpress
[root@iZm5e7euuomowjjtvoos85Z /]# wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
[root@iZm5e7euuomowjjtvoos85Z /]# tar xvf qpress-11-linux-x64.tar
[root@iZm5e7euuomowjjtvoos85Z /]# chmod 775 qpress
[root@iZm5e7euuomowjjtvoos85Z /]# cp qpress /usr/bin
?
准备工作完成,开始恢复物理备份.xb文件
?三:恢复Mysql
如图所示 practice_qp_20*.xb为 Mysql物理备份文件,需要将它恢复到自建Mysql数据库中
?3.1.1: 搭建Mysql5.7数据库
3.1.2：解包
安装完成Mysql之后,就需要将.xb文件恢复到数据库里面,恢复一共有两个比较重要的步骤, 先解压在解包到mysql的data目录里面
自建mysql查看
- ?找到.xb文件 使用XtraBackup对.xb文件进行解包
- ? 使用XtraBackup命令进行解包
[root@iZm5ei78110h0cdb61oku5Z software]# mkdir ?/mnt/lzx/mysqlXB? ? #创建一个解包的临时目录
[root@iZm5ei78110h0cdb61oku5Z software]#?cat practice_qp_2019.xb | xbstream -x -v -C /mnt/lzx/mysqlXB? # 大写-C代表将数据解包到指定目录, 如果.xb过大那么时间较长
?
3.1.3：解压, Mysql5.6/5.7和Mysql5.8解压方式也不同,根据Mysql版本选择其中一种即可
[root@iZm5ei78110h0cdb61oku5Z software]#?innobackupex --decompress --remove-original /home/mysql/data??#Mysql5.6/5.7解压方式,因为这次自建数据库是5.7.11所以选择这一种方式
[root@iZm5ei78110h0cdb61oku5Z software]#?xtrabackup --decompress --remove-original --target-dir=/home/mysql/data? ?#Mysql8.0解压方式
?
?
3.1.4:恢复数据,修改权限
- 将需要恢复的数据库复制到自建mysql的data数据目录下
[root@iZm5ei78110h0cdb61oku5Z mysqlXB]# cp -r bigdata mogujob mogupay mogustudy user_center /usr/local/mysql/data/??
#注意:复制之前需要先停掉自建Mysql,使用实际操作mysql的用户来停止Mysql,这里是mysqladmin
使用cp -r是递归, /usr/local/mysql/data/是自建mysql数据库指定的数据目录,需要根据自己my.conf中datadir指定的路径来判断
?mysql 、sys和performance_schema不用复制过去因为这是系统自带的。
- 修改权限 用户组?
cd到mysql的数据存储目录可以看到,复制过来的数据库都是root权限，但是其他的都是mysqladmin:dba权限,所以要统一,如果没有mysql用户并且都是用root搭建的数据库,那不需要这一步
[root@iZm5ei78110h0cdb61oku5Z data]# chown -R mysqladmin:dba mogujob mogupay mogustudy user_center bigdata? ??#将数据库文件夹用户权限给mysqladmin并且赋给dba用户组
[root@iZm5ei78110h0cdb61oku5Z data]# chmod 755 mogujob mogupay mogustudy user_center bigdata? #将数据库文件夹赋755权限
3.1.5:启动Mysql?
需要先切换到实际操作mysql用户启动,这里是mysqladmin
[root@iZm5ei78110h0cdb61oku5Z data]# su - mysqladmin? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#切换到实际操作Mysql用户
Last login: Tue Apr 12 18:04:46 CST 2022 on pts/1
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>service mysql restart? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #重启Mysql
Shutting down MySQL..[ ?OK ?]
Starting MySQL..[ ?OK ?]iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>mysql -uroot -pmysqldba? ? ? ? ? ? ? ? ? #进入Mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. ?Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>?show databases;? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看数据库,可以看到已经将数据库文件恢复到自建数据库了
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema? ?|
| bigdata? ? ? ? ? ? ? ? ? ? ? ? |
| mogujob? ? ? ? ? ? ? ? ? ? ? |
| mogupay? ? ? ? ? ? ? ? ? ? ?|
| mogustudy? ? ? ? ? ? ? ? ? |
| mysql? ? ? ? ? ? ? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ??
| user_center? ? ? ? ? ? ? ? |
+--------------------+
9 rows in set (0.02 sec)mysql> use bigdata;
Database changedmysql> show tables;
+---------------------+
| Tables_in_bigdata ? |
+---------------------+
| user_area_authority |
+---------------------+
1 row in set (0.00 sec)mysql>?
mysql> select count(1) from user_area_authority;? ? ? ? ? ? ? ? ? ? ? ? ? #查询一个表,但是报表不存在,可是数据库明明有
ERROR 1146 (42S02): Table 'bigdata.user_area_authority' doesn't exist
mysql>?
- 报错: 数据库明明有有表,?show databases;也查询到了,但是查询表数据却报表不存在,ERROR 1146 (42S02): Table 'bigdata.user_area_authority' doesn't exist
- 原因: 需要将使用innobackupex命令解压后的的ibdata1复制过来替换掉mysql数据目录的ibdata1
解决步骤1：退出Mysql终端，并且停掉正在运行的Mysql
mysql> exit;
Bye
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>service mysql stop
Shutting down MySQL..[ ?OK ?]
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>?解决步骤2：先进入到data目录将自建mysql的ibdata1改个名字作为备份
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>cd data/
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>ll
total 2097276
-rwxr-xr-x 1 mysqladmin dba ? ? ? ? 56 Apr 12 17:05 auto.cnf
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 bigdata
-rwxr-xr-x 1 mysqladmin dba ? ? ?33805 Apr 12 23:03 hostname.err
-rw-r----- 1 mysqladmin dba ? ? ? ?300 Apr 12 23:03 ib_buffer_pool
-rwxr-xr-x 1 mysqladmin dba 1073741824 Apr 12 23:03 ibdata1
-rwxr-xr-x 1 mysqladmin dba 1073741824 Apr 12 17:04 ibdata2
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 mogujob
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 mogupay
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 mogustudy
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 17:05 mysql
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 17:05 performance_schema
drwxr-xr-x 2 mysqladmin dba ? ? ?12288 Apr 12 17:05 sys
drwxr-xr-x 2 mysqladmin dba ? ? ?36864 Apr 12 22:15 user_center
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>mv ibdata1 ibdata1.old解决步骤3：将解压后的ibdata1文件拷贝到mysql的data目录下,因为用mysqladmin用户复制过来的,所以权限和用户组都是一样的,如果用其他用户需要更改对应权限使文件夹的数据目录权限一致
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>cp /mnt/lzx/mysqlXB/ibdata1 /usr/local/mysql/data/
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>ll
total 2302080
-rwxr-xr-x 1 mysqladmin dba ? ? ? ? 56 Apr 12 17:05 auto.cnf
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 bigdata
-rwxr-xr-x 1 mysqladmin dba ? ? ?33805 Apr 12 23:03 hostname.err
-rw-r----- 1 mysqladmin dba ? ? ? ?300 Apr 12 23:03 ib_buffer_pool
-rw-r--r-- 1 mysqladmin dba ?209715200 Apr 12 23:11 ibdata1
-rwxr-xr-x 1 mysqladmin dba 1073741824 Apr 12 23:03 ibdata1.old
-rwxr-xr-x 1 mysqladmin dba 1073741824 Apr 12 17:04 ibdata2
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 mogujob
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 mogupay
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 22:13 mogustudy
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 17:05 mysql
drwxr-xr-x 2 mysqladmin dba ? ? ? 4096 Apr 12 17:05 performance_schema
drwxr-xr-x 2 mysqladmin dba ? ? ?12288 Apr 12 17:05 sys
drwxr-xr-x 2 mysqladmin dba ? ? ?36864 Apr 12 22:15 user_center
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>chmod 755 ibdata1? ? ? ? ? ? ?#赋值755操作权限
- 启动Mysql
?iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>service mysql start
Starting MySQL..The server quit without updating PID file (/usr/local/mysql/data/hostname.pid).[FAILED]
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql/data:>将ibdata1复制过来之后报错Starting MySQL..The server quit without updating PID file (/usr/local/mysql/data/hostname.pid).[FAILED]? ? ? ? ? ? ? ? ? ? ? ??查看日志
2022-04-12T15:15:09.844484Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 2022-04-12T15:15:10.062486Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead. 2022-04-12T15:15:10.062621Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-04-12T15:15:10.062658Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2022-04-12T15:15:10.062689Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.11-log) starting as process 24918 ... 2022-04-12T15:15:10.094597Z 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html 2022-04-12T15:15:10.094703Z 0 [Note] InnoDB: PUNCH HOLE support not available 2022-04-12T15:15:10.094719Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2022-04-12T15:15:10.094727Z 0 [Note] InnoDB: Uses event mutexes 2022-04-12T15:15:10.094732Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier 2022-04-12T15:15:10.094737Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2022-04-12T15:15:10.094744Z 0 [Note] InnoDB: Using Linux native AIO 2022-04-12T15:15:10.095273Z 0 [Note] InnoDB: Number of pools: 1 2022-04-12T15:15:10.095412Z 0 [Note] InnoDB: Using CPU crc32 instructions 2022-04-12T15:15:10.107450Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M 2022-04-12T15:15:10.248596Z 0 [Note] InnoDB: Completed initialization of buffer pool 2022-04-12T15:15:10.280318Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2022-04-12T15:15:10.290336Z 0 [ERROR] InnoDB: The innodb_system data file '/usr/local/mysql/data/ibdata1' is of a different size 12800 pages (rounded down to MB) than the 65536 pages specified in the .cnf file! 2022-04-12T15:15:10.290369Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2022-04-12T15:15:10.890918Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 2022-04-12T15:15:10.890978Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2022-04-12T15:15:10.890988Z 0 [ERROR] Failed to initialize plugins. 2022-04-12T15:15:10.890994Z 0 [ERROR] Aborting 2022-04-12T15:15:10.891006Z 0 [Note] Binlog end 2022-04-12T15:15:10.891083Z 0 [Note] Shutting down plugin 'keyring_file' 2022-04-12T15:15:10.897105Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 2022-04-12T15:15:10.993384Z mysqld_safe mysqld from pid file /usr/local/mysql/data/hostname.pid ended其中有一行报错:?[ERROR] InnoDB: The innodb_system data file '/usr/local/mysql/data/ibdata1' is of a different size 12800 pages (rounded down to MB) than the 65536 pages specified in the .cnf file!
原因：innodb_data_file_path配置项使用来指定innodb共享表空间文件的,报错的意思可以理解为因为我们实际数据变多,那么表空间也要换算成一致的值,innodb_data_file_path算法参考?, 参考这篇文章之后,需要将报错中提到的大小12800*16=204800KB=200MB,把配置文件中innodb_data_file_path项改为200MB
[root@iZm5ei78110h0cdb61oku5Z ~]# vim /etc/my.cnf
找到此配置项,修改ibdata1的值,因为报错信息里没有提到ibdata2,所以不变
改为:
启动Mysql再次报错
2022-04-13T01:27:55.994529Z 0 [Warning] InnoDB: Cannot calculate statistics for table `mysql`.`time_zone_leap_second` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2022-04-13T01:27:55.994549Z 0 [Warning] Can't open and lock time zone table: Tablespace is missing for table `mysql`.`time_zone_leap_second`. trying to live without them 2022-04-13T01:27:56.012231Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`servers` in the cache. Attempting to load the tablespace with space id 2512 2022-04-13T01:27:56.012366Z 0 [ERROR] InnoDB: In file './mysql/servers.ibd', tablespace id and flags are 3 and 33, but in the InnoDB data dictionary they are 2512 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:27:56.012380Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2022-04-13T01:27:56.012385Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2022-04-13T01:27:56.012390Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/servers`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:27:56.012432Z 0 [Warning] InnoDB: Cannot calculate statistics for table `mysql`.`servers` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2022-04-13T01:27:56.012450Z 0 [ERROR] Can't open and lock privilege tables: Tablespace is missing for table `mysql`.`servers`. 2022-04-13T01:27:56.015396Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_master_info` in the cache. Attempting to load the tablespace with space id 2513 2022-04-13T01:27:56.015506Z 0 [ERROR] InnoDB: In file './mysql/slave_master_info.ibd', tablespace id and flags are 16 and 33, but in the InnoDB data dictionary they are 2513 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:27:56.015518Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2022-04-13T01:27:56.015523Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2022-04-13T01:27:56.015527Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/slave_master_info`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:27:56.015582Z 0 [Warning] InnoDB: Cannot calculate statistics for table `mysql`.`slave_master_info` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2022-04-13T01:27:56.016973Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_relay_log_info` in the cache. Attempting to load the tablespace with space id 2514 2022-04-13T01:27:56.017061Z 0 [ERROR] InnoDB: In file './mysql/slave_relay_log_info.ibd', tablespace id and flags are 15 and 33, but in the InnoDB data dictionary they are 2514 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:27:56.017073Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2022-04-13T01:27:56.017083Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2022-04-13T01:27:56.017088Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/slave_relay_log_info`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:27:56.017123Z 0 [Warning] InnoDB: Cannot calculate statistics for table `mysql`.`slave_relay_log_info` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2022-04-13T01:27:56.017223Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 2022-04-13T01:27:56.017246Z 0 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE. 2022-04-13T01:27:56.017264Z 0 [ERROR] Error creating master info: Error checking repositories. 2022-04-13T01:27:56.017267Z 0 [ERROR] Failed to create or recover replication info repository. 2022-04-13T01:27:56.017274Z 0 [ERROR] Failed to create or recover replication info repositories. 2022-04-13T01:27:56.017279Z 0 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. 2022-04-13T01:27:56.124417Z 0 [Note] Event Scheduler: Loaded 0 events 2022-04-13T01:27:56.124619Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.7.11-log' socket: '/usr/local/mysql/data/mysql.sock' port: 3306 MySQL Community Server (GPL) 2022-04-13T01:45:43.743140Z 2 [ERROR] InnoDB: Page [page id: space=0, page number=362] log sequence number 51306797559 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743195Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743263Z 2 [ERROR] InnoDB: Page [page id: space=0, page number=517] log sequence number 51306797587 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743300Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743355Z 2 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`innodb_table_stats` in the cache. Attempting to load the tablespace with space id 2505 2022-04-13T01:45:43.743461Z 2 [ERROR] InnoDB: In file './mysql/innodb_table_stats.ibd', tablespace id and flags are 13 and 33, but in the InnoDB data dictionary they are 2505 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:45:43.743475Z 2 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2022-04-13T01:45:43.743480Z 2 [ERROR] InnoDB: The error means the system cannot find the path specified. 2022-04-13T01:45:43.743497Z 2 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/innodb_table_stats`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2022-04-13T01:45:43.743540Z 2 [ERROR] InnoDB: Tablespace for table `mysql`.`innodb_table_stats` is missing. 2022-04-13T01:45:43.743550Z 2 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mogujob`.`mogu_major` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead. 2022-04-13T01:45:43.743672Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=3] log sequence number 11714272188 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743698Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743732Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=2] log sequence number 11714272188 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743738Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743777Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=5] log sequence number 11714243814 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743784Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743813Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=1] log sequence number 11712993207 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743819Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743865Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=8] log sequence number 11714277049 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743871Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.743946Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=6] log sequence number 11714272188 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.743960Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.744049Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=4] log sequence number 11714215891 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.744056Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2022-04-13T01:45:43.750269Z 2 [ERROR] InnoDB: Page [page id: space=1416, page number=7] log sequence number 11714272188 is in the future! Current system log sequence number 2494393. 2022-04-13T01:45:43.750303Z 2 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.解决:?将my.cnf中innodb_force_recovery = 1或2——6几个数字这行配置删除或者配置为innodb_force_recovery = 0，重启MySQL服务
之后启动成功,并且数据库存在数据。
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>service mysql start? ?#启动Mysql
Starting MySQL..[ ?OK ?]
iZm5ei78110h0cdb61oku5Z:mysqladmin:/usr/local/mysql:>mysql -uroot -pmysqldba
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. ?Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema? ?|
| bigdata? ? ? ? ? ? ? ? ? ? ? ? |
| mogujob? ? ? ? ? ? ? ? ? ? ? |
| mogupay? ? ? ? ? ? ? ? ? ? ?|? ? ? ??
| mogustudy? ? ? ? ? ? ? ? ? |
| mysql? ? ? ? ? ? ? ? ? ? ? ? ? |
| performance_schema |
| sys? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| user_center? ? ? ? ? ? ? ? |
+--------------------+
9 rows in set (0.00 sec)mysql> use mogujob;
Database changed
mysql> select count(1) from mogu_major;? ? ?#查看表已经恢复了数据
+----------+
| count(1) |
+----------+
| ? ? ?847 |
+----------+
1 row in set (0.00 sec)mysql>?
四:恢复Mongodb
如图所示 practice_qp_20*.xb为 Mysql物理备份文件,需要将它恢复到自建Mysql数据库中
?4.1.1：搭建MongoDB4.2.7版本(版本要和RDS数据库一致)
- ?解压
[root@iZm5ei78110h0cdb61oku5Z data]# tar zxvf mongodb-linux-x86_64-rhel70-4.2.7.tgz -C /usr/local/
mongodb-linux-x86_64-rhel70-4.2.7/THIRD-PARTY-NOTICES.gotools
mongodb-linux-x86_64-rhel70-4.2.7/README
mongodb-linux-x86_64-rhel70-4.2.7/THIRD-PARTY-NOTICES
mongodb-linux-x86_64-rhel70-4.2.7/MPL-2
mongodb-linux-x86_64-rhel70-4.2.7/LICENSE-Community.txt
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongodump
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongorestore
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongoexport
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongoimport
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongostat
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongotop
mongodb-linux-x86_64-rhel70-4.2.7/bin/bsondump
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongofiles
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongoreplay
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongod
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongos
mongodb-linux-x86_64-rhel70-4.2.7/bin/mongo
mongodb-linux-x86_64-rhel70-4.2.7/bin/install_compass
[root@iZm5ei78110h0cdb61oku5Z data]# cd /usr/local/
[root@iZm5ei78110h0cdb61oku5Z local]# ll
total 56
drwxr-xr-x ?10 root ? ? ? root 4096 Apr ?7 20:19 aegis
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 bin
drwxr-xr-x ? 5 root ? ? ? root 4096 Jan 26 ?2021 cloudmonitor
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 etc
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 games
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 include
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 lib
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 lib64
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 libexec
drwxr-xr-x ? 3 root ? ? ? root 4096 Apr 13 16:57 mongodb-linux-x86_64-rhel70-4.2.7
drwxr-xr-x ?13 mysqladmin dba ?4096 Apr 13 10:55 mysql
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 sbin
drwxr-xr-x. ?7 root ? ? ? root 4096 Nov 30 21:33 share
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 src
[root@iZm5ei78110h0cdb61oku5Z local]# mv mongodb-linux-x86_64-rhel70-4.2.7 mongodb-4.2.7
[root@iZm5ei78110h0cdb61oku5Z local]# ll
total 56
drwxr-xr-x ?10 root ? ? ? root 4096 Apr ?7 20:19 aegis
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 bin
drwxr-xr-x ? 5 root ? ? ? root 4096 Jan 26 ?2021 cloudmonitor
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 etc
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 games
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 include
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 lib
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 lib64
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 libexec
drwxr-xr-x ? 3 root ? ? ? root 4096 Apr 13 16:57 mongodb-4.2.7
drwxr-xr-x ?13 mysqladmin dba ?4096 Apr 13 10:55 mysql
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 sbin
drwxr-xr-x. ?7 root ? ? ? root 4096 Nov 30 21:33 share
drwxr-xr-x. ?2 root ? ? ? root 4096 Apr 11 ?2018 src
三:恢复Mongodb
3.1搭建mongodb
启动 删除db
3.2 解包 解压到数据目录
3.2启动
修改配置文件directoryPerDB删除
把db路径下的
WiredTiger.lock
mongodb.lock
storage.bson
注释验证配置块
客户端访问不了 增加0.0.0.0
删除
WiredTiger.lock
mongodb.lock
storage.bson
