阿里云Mysql数据库物理全备文件恢复到自建数据库Mysql报错:InnoDB: Log file ./...xtrabacku

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 阿里云Mysql数据库物理全备文件恢复到自建数据库Mysql报错:InnoDB: Log file ./...xtrabacku

报错信息:

......
InnoDB: Log file ./ib_logfile1 is of different size 50331648 bytes than other log files 12058624 bytes!
InnoDB: Plugin initialization aborted with error Generic error
xtrabackup: innodb_init(): Error occured.

环境介绍:


CentOS 7.5


Mysql 5.7.30


percona-xtrabackup-24


过程回顾:

[root@test2 ~]# chown -R mysql:mysql /var/lib/mysql
[root@test2 ~]# ll /var/lib/mysql
总用量 293172
-rw-r----- 1 mysql mysql        56 7月  10 16:41 auto.cnf
-rw-rw---- 1 mysql mysql       543 7月  10 16:50 backup-my.cnf
-rw------- 1 mysql mysql      1680 7月  10 16:41 ca-key.pem
-rw-r--r-- 1 mysql mysql      1112 7月  10 16:41 ca.pem
-rw-r--r-- 1 mysql mysql      1112 7月  10 16:41 client-cert.pem
-rw------- 1 mysql mysql      1680 7月  10 16:41 client-key.pem
drwxr-xr-x 2 mysql mysql      4096 7月  10 16:48 crmdb
drwxr-xr-x 2 mysql mysql      4096 7月  10 16:48 crmdb_test
-rw-r----- 1 mysql mysql       425 7月  10 16:41 ib_buffer_pool
-rw-rw---- 1 mysql mysql 209715200 7月   8 12:31 ibdata1
-rw-r----- 1 mysql mysql  50331648 7月  10 16:41 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 7月  10 16:41 ibtmp1
drwxr-x--- 2 mysql mysql      4096 7月  10 16:48 mysql
srwxrwxrwx 1 mysql mysql         0 7月  10 16:41 mysql.sock
-rw------- 1 mysql mysql         5 7月  10 16:41 mysql.sock.lock
drwxr-x--- 2 mysql mysql      8192 7月  10 16:48 performance_schema
-rw------- 1 mysql mysql      1680 7月  10 16:41 private_key.pem
-rw-r--r-- 1 mysql mysql       452 7月  10 16:41 public_key.pem
-rw-r--r-- 1 mysql mysql      1112 7月  10 16:41 server-cert.pem
-rw------- 1 mysql mysql      1680 7月  10 16:41 server-key.pem
drwxr-x--- 2 mysql mysql      8192 7月  10 16:48 sys
-rw-rw---- 1 mysql mysql       119 7月   8 13:31 xtrabackup_binlog_info
-rw-rw---- 1 mysql mysql       147 7月   8 13:31 xtrabackup_checkpoints
-rw-rw---- 1 mysql mysql       845 7月   8 13:31 xtrabackup_info
-rw-rw---- 1 mysql mysql  10747904 7月  10 17:16 xtrabackup_logfile
-rw-rw---- 1 mysql mysql        74 7月   8 13:31 xtrabackup_slave_filename_info
-rw-rw---- 1 mysql mysql       162 7月   8 13:31 xtrabackup_slave_info
[root@test2 ~]# innobackupex --defaults-file=/var/lib/mysql/backup-my.cnf --apply-log /var/lib/mysql
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_data_file_path=ibdata1:200M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1048576000 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=2931342899
xtrabackup: recognized client arguments:
200710 17:20:40 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.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: cd to /var/lib/mysql/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=12058624, start_lsn=(10489267059)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 12058624
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 12058624
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
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: Log file ./ib_logfile1 is of different size 50331648 bytes than other log files 12058624 bytes!
InnoDB: Plugin initialization aborted with error Generic error
xtrabackup: innodb_init(): Error occured.

解决方法:


移除所有ib_logfile*的文件,最好是移动到其他文件夹,不要直接删除。

[root@test2 ~]# mv /var/lib/mysql/ib_logfile1 /root/crmdb/        //将id_logfile1移动至其他文件夹
[root@test2 ~]# innobackupex --defaults-file=/var/lib/mysql/backup-my.cnf --apply-log /var/lib/mysql
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_data_file_path=ibdata1:200M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1048576000 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=2931342899
xtrabackup: recognized client arguments:
200710 17:38:03 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.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: cd to /var/lib/mysql/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=13565952, start_lsn=(10489267059)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 13565952
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 13565952
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
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: Log scan progressed past the checkpoint lsn 10489267059
InnoDB: Doing recovery: scanned up to log sequence number 10494509568 (43%)
InnoDB: Doing recovery: scanned up to log sequence number 10498831278 (79%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 593541, file name mysql-bin.001448
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: Waiting for purge to start
InnoDB: 5.7.26 started; log sequence number 10498831278
InnoDB: page_cleaner: 1000ms intended loop took 46390ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: xtrabackup: Last MySQL binlog file position 593541, file name mysql-bin.001448
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 10498832727
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1048576000
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
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 1000 MB
InnoDB: Progress in MB:
 100 200 300 400 500 600 700 800 900 1000
InnoDB: Setting log file ./ib_logfile1 size to 1000 MB
InnoDB: Progress in MB:
 100 200 300 400 500 600 700 800 900 1000
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=10498832727
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 10498832908
InnoDB: Doing recovery: scanned up to log sequence number 10498832917 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 593541, file name mysql-bin.001448
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: page_cleaner: 1000ms intended loop took 35376ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.26 started; log sequence number 10498832917
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 10498832936
200710 17:39:28 completed OK!

可以看到这时再执行 innobackupex 恢复数据,就执行成功了。


注意:


 数据恢复成功后,由于我是恢复的RDS全备文件,所以我们自建的Mysql数据库密码会被覆盖成RDS的数据库密码,如果忘记密码,可以临时在Mysql配置文件中加入skip-grant-tables配置,无密码进入数据库,验证数据恢复情况。

[root@test2 ~]# vim /etc/my.cnf
[mysqld]
...
skip-grant-tables
[root@test2 ~]# systemctl start mysqld
[root@test2 ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> use crmdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------------+
| Tables_in_crmdb        |
+------------------------+
| 产品表_rsow_product     |
| calllist               |
| cus_manage_set         |
| dictionary             |
| importtel              |
| imptable               |
| imptable2              |
| log                    |
| rsow_product           |
| rxpbls                 |
| sys_app_user           |
| sys_dept               |
| sys_dictionaries       |
| sys_gl_qx              |
| sys_menu               |
| sys_role               |
| sys_user               |
| sys_user_qx            |
| tb_pictures            |
| test                   |
| tmp_ybc                |
| user                   |
mysql> select * from  user;
+--------+----------+----------+------+------+------------+
| userID | userName | password | name | sex  | department |
+--------+----------+----------+------+------+------------+
|      1 | admin    | admin    | NULL | NULL | NULL       |
|      2 | 111111   | 111111   | NULL | NULL | NULL       |
|      3 | 222222   | 222222   | NULL | NULL | NULL       |
|      4 | 333333   | 333333   | NULL | NULL | NULL       |
|      5 | 444444   | 444444   | NULL | NULL | NULL       |
|      6 | 555555   | 555555   | NULL | NULL | NULL       |
+--------+----------+----------+------+------+------------+
6 rows in set (0.00 sec)

 数据恢复成功!!!


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
人工智能 运维 关系型数据库
|
1月前
|
数据可视化 关系型数据库 MySQL
ELK实现nginx、mysql、http的日志可视化实验
通过本文的步骤,你可以成功配置ELK(Elasticsearch, Logstash, Kibana)来实现nginx、mysql和http日志的可视化。通过Kibana,你可以直观地查看和分析日志数据,从而更好地监控和管理系统。希望这些步骤能帮助你在实际项目中有效地利用ELK来处理日志数据。
266 90
|
20天前
|
SQL 运维 关系型数据库
MySQL Binlog 日志查看方法及查看内容解析
本文介绍了 MySQL 的 Binlog(二进制日志)功能及其使用方法。Binlog 记录了数据库的所有数据变更操作,如 INSERT、UPDATE 和 DELETE,对数据恢复、主从复制和审计至关重要。文章详细说明了如何开启 Binlog 功能、查看当前日志文件及内容,并解析了常见的事件类型,包括 Format_desc、Query、Table_map、Write_rows、Update_rows 和 Delete_rows 等,帮助用户掌握数据库变化历史,提升维护和排障能力。
|
20天前
|
Cloud Native 关系型数据库 MySQL
华鼎冷链科技 × 阿里云瑶池数据库,打造全链路协同的智慧冷链新标杆
从 PolarDB 的高性能数据库服务到 AnalyticDB 的强大数据分析,阿里云提供的丰富产品矩阵为华鼎冷链科技构建了全面的解决方案,推动华鼎冷链科技从成本中心向效率中心转型。
|
1月前
|
存储 SQL 关系型数据库
mysql的undo log、redo log、bin log、buffer pool
MySQL的undo log、redo log、bin log和buffer pool是确保数据库高效、安全和可靠运行的关键组件。理解这些组件的工作原理和作用,对于优化数据库性能和保障数据安全具有重要意义。通过适当的配置和优化,可以显著提升MySQL的运行效率和数据可靠性。
52 16
|
18天前
|
人工智能 关系型数据库 分布式数据库
让数据与AI贴得更近,阿里云瑶池数据库系列产品焕新升级
4月9日阿里云AI势能大会上,阿里云瑶池数据库发布重磅新品及一系列产品能力升级。「推理加速服务」Tair KVCache全新上线,实现KVCache动态分层存储,显著提高内存资源利用率,为大模型推理降本提速。
|
1月前
|
存储 SQL 关系型数据库
mysql的undo log、redo log、bin log、buffer pool
MySQL的undo log、redo log、bin log和buffer pool是确保数据库高效、安全和可靠运行的关键组件。理解这些组件的工作原理和作用,对于优化数据库性能和保障数据安全具有重要意义。通过适当的配置和优化,可以显著提升MySQL的运行效率和数据可靠性。
43 4
|
1月前
|
人工智能 Cloud Native 关系型数据库
亚太唯一,阿里云连续5年位居Gartner®云数据库管理系统报告「领导者」
亚太唯一,阿里云连续5年位居Gartner®云数据库管理系统报告「领导者」
|
1月前
|
存储 SQL 安全
【赵渝强老师】达梦数据库的物理存储结构
本文介绍了达梦数据库的存储结构及各类物理文件的作用。达梦数据库通过逻辑和物理存储结构管理数据,包含配置文件(如dm.ini、sqllog.ini)、控制文件(dm.ctl)、数据文件(*.dbf)、重做日志文件(*.log)、归档日志文件、备份文件(*.bak)等。配置文件用于功能设置,控制文件记录数据库初始信息,数据文件存储实际数据,重做日志用于故障恢复,归档日志增强数据安全性,备份文件保障数据完整性,跟踪与事件日志辅助问题分析。这些文件共同确保数据库高效、稳定运行。
|
2月前
|
人工智能 Cloud Native 关系型数据库
亚太唯一!阿里云连续5年入选Gartner®云数据库管理系统「领导者」象限
亚太唯一!阿里云连续5年入选Gartner®云数据库管理系统「领导者」象限

热门文章

最新文章

下一篇
oss创建bucket