专栏首页mysql-dbaxtrabackup8.0.25 备份恢复MySQL server 8.0.25
原创

xtrabackup8.0.25 备份恢复MySQL server 8.0.25

1.安装xtrabackup工具版本为8.0.25-17

https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-80
[root@ck1 data]# xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
[root@ck1 data]# 

2.xtrabackup 备份mysql实例

[root@ck1 mysqlbackup]# /usr/bin/xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=123456 --socket=/tmp/mysql3306.sock --backup --target-dir /data/mysqlbackup 
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --server-id=2013306 --open_files_limit=65535 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=1G --innodb_log_files_in_group=3 --innodb_undo_directory=undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=2000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --innodb_buffer_pool_size=800M 
xtrabackup: recognized client arguments: --user=root --password=* --socket=/tmp/mysql3306.sock --backup=1 --target-dir=/data/mysqlbackup 
/usr/bin/xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
210621 17:21:58  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql3306.sock' as 'root'  (using password: YES).
210621 17:21:58  version_check Connected to MySQL server
210621 17:21:58  version_check Executing a version check against the server...
210621 17:21:58 version_check Done.
210621 17:21:58 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql3306.sock
Using server version 8.0.25
210621 17:21:58 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3306/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 1073741824
xtrabackup: using O_DIRECT
............
............
210621 17:21:58 [01] Copying performance_schema/keyring_componen_191.sdi to /data/mysqlbackup/performance_schema/keyring_componen_191.sdi
210621 17:21:58 [01]        ...done
210621 17:21:58 [00] Writing /data/mysqlbackup/ww/db.opt
210621 17:21:58 [00]        ...done
210621 17:21:58 Finished backing up non-InnoDB tables and files
210621 17:21:58 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
210621 17:21:58 Selecting LSN and binary log position from p_s.log_status
210621 17:21:58 [00] Copying /data/mysql/mysql3306/logs/mysql-bin.000020 to /data/mysqlbackup/mysql-bin.000020 up to position 196
210621 17:21:58 [00]        ...done
210621 17:21:58 [00] Writing /data/mysqlbackup/mysql-bin.index
210621 17:21:58 [00]        ...done
210621 17:21:58 [00] Writing /data/mysqlbackup/xtrabackup_binlog_info
210621 17:21:58 [00]        ...done
210621 17:21:58 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '14370468920'
xtrabackup: Stopping log copying thread at LSN 14370473716.
Starting to parse redo log at lsn = 14370468892
210621 17:21:58 >> log scanned up to (14370473726)

210621 17:21:59 Executing UNLOCK INSTANCE
210621 17:21:59 All tables unlocked
210621 17:21:59 [00] Copying ib_buffer_pool to /data/mysqlbackup/ib_buffer_pool
210621 17:21:59 [00]        ...done
210621 17:21:59 Backup created in directory '/data/mysqlbackup/'
MySQL binlog position: filename 'mysql-bin.000020', position '196', GTID of the last change '33ab1a2d-cf38-11eb-9661-56c8a95977d1:1-432605'
210621 17:21:59 [00] Writing /data/mysqlbackup/backup-my.cnf
210621 17:21:59 [00]        ...done
210621 17:21:59 [00] Writing /data/mysqlbackup/xtrabackup_info
210621 17:21:59 [00]        ...done
xtrabackup: Transaction log of lsn (14370468920) to (14370473736) was copied.
210621 17:22:00 completed OK!

3.创建数据库目录,用于恢复使用

mkdir /data/mysql/mysql3310/{data,logs,tmp} -p

4.拷贝备份到数据库目录

cp -rf /data/mysqlbackup/* /data/mysql/mysql3310/data

5.执行恢复prepare

[root@ck1 data]# xtrabackup --prepare  --target-dir=/data/mysql/mysql3310/data
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=1073741824 --innodb_page_size=16384 --innodb_undo_directory=undolog --innodb_undo_tablespaces=95 --server-id=2013306 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/data/mysql/mysql3310/data 
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
xtrabackup: cd to /data/mysql/mysql3310/data/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(14370468920)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: inititialize_service_handles suceeded
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M: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)
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 747 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 18097206 in the system tablespace does not match the log sequence number 14370468920 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 14370468892, whereas checkpoint_lsn = 14370468920 and start_lsn = 14370468864
Doing recovery: scanned up to log sequence number 14370473716
Log background threads are being started...
Applying a batch of 11 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 6455761
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.25 started; log sequence number 14370473716
Allocated tablespace ID 1855 for conf/t_worktime, old maximum was 0
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 14370473716
Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 1073741824
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 747 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Creating log file ./ib_logfile101
Creating log file ./ib_logfile1
Creating log file ./ib_logfile2
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=14370473996
Starting to parse redo log at lsn = 14370473996, whereas checkpoint_lsn = 14370473996 and start_lsn = 14370473984
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 6455761
Removed temporary tablespace data file: "ibtmp1"
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.25 started; log sequence number 14370473996
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Trying to access missing tablespace 4294967294
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 14370473996
210621 17:37:56 completed OK!

6.启动数据库

[root@ck1 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3310/my3310.cnf --user=mysql &

如下报错:
2021-06-21T17:38:56.755255+08:00 1 [Note] [MY-012535] [InnoDB] Apply batch completed!
2021-06-21T17:38:56.755974+08:00 1 [Note] [MY-012905] [InnoDB] Cannot create undolog/undo_001 because ./undo_001 already uses Space ID=4294967279! Did you change innodb_undo_directory?
2021-06-21T17:38:56.756126+08:00 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Invalid Filename.
2021-06-21T17:38:57.137020+08:00 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2021-06-21T17:38:57.137422+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-06-21T17:38:57.137745+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2021-06-21T17:38:57.137830+08:00 0 [Note] [MY-010120] [Server] Binlog end

删除undo,再次启动
[root@ck1 data]# rm -rf undo_00*
[root@ck1 data]# ps -ef |grep mysql
mysql     6064  5534 19 17:39 pts/3    00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3310/my3310.cnf --user=mysql

7.连接数据库验证

[root@ck1 data]# /usr/local/mysql/bin/mysql -S /tmp/mysql3310.sock -uroot -p1xxxx
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 8
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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           |
+--------------------+
| conf               |
| db_test            | 
| gcuiyu_cc          |
| cuiyu_vv       |
| cuiyu_sms          |
| vuiyu_xccehuf        |
| information_schema |
| mysql              |
| ofpensip           |
| performance_schema |
| sys                |
| ww                 |
| xxl_job            |
+--------------------+
23 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 故障分析 | xtrabackup 吃掉了MySQL的 binlog 文件名?

    前段时间在 centos 8 环境上做 MySQL 的备份恢复测试的时候,遇到一个问题,下面跟大家分享下。

    爱可生开源社区
  • mysqldump测试--mysql基础系列

    本系列文章主要是对mysql的一些基础功能做些测试记录,方便大家参考,也方便自己后续翻看。

    CorollaD
  • mysql升级到最新版(mysql8.0.25)

    MYSQL 5.6 --> MySQL 5.7 --> MySQL8.0.x

    wangwei-dba
  • 在Linux下源码编译安装GreatSQL/MySQL

    本次介绍如何利用Docker来将GreatSQL源码编译成二进制文件,以及制作二进制包、RPM包等。

    老叶茶馆
  • 技术分享 | 利用GreatSQL部署MGR集群,并完成添加新节点 、滚动升级、切主(超详细)

    本文详细介绍如何在单机环境下,利用GreatSQL构建一个3节点的MGR集群,并用mysqld_multi进行管理。

    老叶茶馆
  • 在RedHat Linux平台上安装MySQL 8

    到MySQL的官方网站https://www.mysql.com/downloads/上下载完整的MySQL 8社区版RPM安装包,注意选择对应的平台和操作系统...

    用户4988085
  • Mysql 备份恢复与xtrabackup备份

      备份是数据安全的最后一道防线,对于任何数据丢失的场景,备份虽然不一定能恢复百分之百的数据(取决于备份周期),但至少能将损失降到最低。衡量备份恢复有两个重要的...

    惨绿少年
  • mysql备份与恢复

    程序员同行者
  • mysql 备份与恢复

    1、备份 mysqldump -u x -p x dbname1 > dbname1.date.sql

    用户5760343
  • mysql备份与恢复

    对于DBA来说,备份和恢复是一项最基本的操作,在服务器宕机、磁盘损坏、RAID卡损坏等意外情况下,要保证数据不丢失或者丢失量在可接受范围内,每个DBA应该时刻...

    AsiaYe
  • mysql备份与恢复

      MySQL数据库自带的一个很好用的备份命令。是逻辑备份,导出 的是SQL语句。也就是把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的...

    浅风沐雪
  • MySQL 备份恢复(一)

    数据是很重要的,没有备份,删库就只能跑路了,当然这只是玩笑话了。但当数据损坏或者误操作删除数据时,备份就显得尤为重要了,备份可以恢复误删除的数据,备份可以作为我...

    JiekeXu之路
  • MySQL 备份恢复(三)

    mydumper 最突出的特性就是可采用多线程并行备份,极大提高了数据导出的速度。其特征之一是在处理过程中需要对列表加以锁定,所以备份时尽量不要选择工作时间,避...

    JiekeXu之路
  • MySQL 备份恢复(四)

    热备中主要有逻辑备份和裸文件备份,裸文件备份要比逻辑备份在速度上快一些,mysqldump 备份方式是采用的逻辑备份,其最大的缺陷是备份和恢复速度较慢,如果数据...

    JiekeXu之路
  • MySQL 备份恢复(二)

    前面一篇已经介绍了MySQL 备份相关的原理与方法,要是还没有来得及看的可以戳此查看『MySQL 备份恢复(一)』,那么今天就接着上一篇的内容继续谈谈备份恢复相...

    JiekeXu之路
  • MySQL备份与恢复

    数据库在信息系统中担任着非常重要的角色,尤其一些对数据可靠性要求非常高的行业,如果发生宕机或数据丢失,其损失是非常严重的。 在公司中备份的策略并不是千篇一律的,...

    小手冰凉
  • MySQL备份与恢复(二)

    前天的文章中简单写了备份与恢复的方法,今天我们主要来看看不同的备份方法备份出来的文件结果,以及它们的恢复方法。

    AsiaYe
  • mysql的备份及恢复

    我们试着想一想, 在生产环境中什么最重要?如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢?这可能是最恐怖的事情了吧...

    用户4877748
  • 《叶问》35期,binlog解析出来的日志为何无法恢复

    问题来自一位群友,简单说就是用 mysqlbinlog 工具读取 binlog 欲进行恢复,却发现数据并没被恢复。

    老叶茶馆

扫码关注云+社区

领取腾讯云代金券