percona软件介绍、 innobackupex备份与恢复

最新推荐文章于 2023-07-07 22:37:41 发布

骁林

最新推荐文章于 2023-07-07 22:37:41 发布

阅读量303

点赞数

分类专栏： Database 文章标签： percona软件介绍、 innobackupex备份与恢复

本文链接：https://blog.csdn.net/qq_40380640/article/details/98100535

版权

Database 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文介绍了如何使用Percona的XtraBackup工具进行MySQL数据库的完全备份、单表恢复以及增量备份与恢复操作。详细步骤包括安装XtraBackup、执行数据备份、恢复数据以及验证数据完整性。案例涵盖了从全量备份到增量备份的整个流程，对于数据库管理员来说具有很高的参考价值。

摘要由CSDN通过智能技术生成

案例1：数据完全备份与恢复
案例2：恢复单张表
案例3：增量备份与恢复

1 案例1：数据完全备份与恢复
1.1 问题

安装percona软件包
备份所有数据到/allbak目录下
搭建新的数据库服务器，使用备份文件恢复数据
验证数据恢复

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：安装XtraBackup软件包

1）安装软件

[root@host50 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
[root@host50 ~]# yum -y  install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
警告：percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID cd2efd2a: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:percona-xtrabackup-24-2.4.6-2.el7################################# [ 33%]
   2:percona-xtrabackup-test-24-2.4.6-################################# [ 67%]
   3:percona-xtrabackup-24-debuginfo-2################################# [100%]

2）确认安装的主要程序/脚本

[root@host50 ~]# rpm -qa  | grep -i percona
percona-xtrabackup-24-2.4.7-1.el7.x86_64
[root@host50 ~]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
[root@host50 ~]#
[root@host50 ~]# innobackupex --help  //查看简单帮助
[root@host50 ~]#
[root@host50 ~]# man  innobackupex //查看详细帮助
/usr/share/man/man1/xtrabackup.1.gz

步骤二：备份所有数据到/allbak目录下

1）备份所有数据

[root@host50 ~]# innobackupex --user root --password 123456 /allbak --no-timestamp //执行备份命令
170425 11:05:44 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.17
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
170425 11:05:45 >> log scanned up to (2543893)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1
170425 11:05:45 [01]        ...done
170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd
170425 11:05:46 [01]        ...done
170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd
170425 11:05:46 [01]        ...done
170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd
170425 11:05:46 [01]        ...done
170425 11:05:46 >> log scanned up to (2543893)
.. ..
170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm
170425 11:06:00 [01]        ...done
170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm
170425 11:06:00 [01]        ...done
170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt
170425 11:06:00 [01]        ...done
170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm
170425 11:06:00 [01]        ...done
170425 11:06:00 Finished backing up non-InnoDB tables and files
170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2543884'
xtrabackup: Stopping log copying thread.
.170425 11:06:00 >> log scanned up to (2543893)
170425 11:06:00 Executing UNLOCK TABLES
170425 11:06:00 All tables unlocked
170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
170425 11:06:00 [00]        ...done
170425 11:06:00 Backup created in directory '/backup/'
170425 11:06:00 [00] Writing backup-my.cnf
170425 11:06:00 [00]        ...done
170425 11:06:00 [00] Writing xtrabackup_info
170425 11:06:00 [00]        ...done
xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.
170425 11:06:01 completed OK

确认备份好的文件数据：

[root@host50 ~]# ls /allbak
backup-my.cnf ib_buffer_pool mysql sys xtrabackup_info
db1 ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile

3）把备份文件传递给目标服务器51

[root@host50 ~]#
[root@host50 ~]# scp -r /allbak root@192.168.4.51:/root/
[root@host50 ~]#

步骤三：在51主机，使用备份文件恢复数据

1）安装软件包，提供恢复命令

[root@host51 ~]#  rpm -ivh  libev-4.15-1.el6.rf.x86_64.rpm
[root@host51 ~]# yum -y  install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2）恢复数据

[root@host51 ~]# systemctl  stop mysqld
[root@host51 ~]# ls /var/lib/mysql
[root@host51 ~]# rm -rf /var/lib/mysql/* //清空数据
[root@host51 ~]#innobackupex--apply-log  --redo-only /root/allbak //恢复数据
170425 11:42:19 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.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: cd to /backup/
xtrabackup: This target seems to be already prepared.
InnoDB: Number of pools: 1
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
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: Not 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.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2544177
InnoDB: Number of pools: 1
170425 11:42:20 completed OK!
[root@host51 ~]#
[root@host51 ~]#  innobackupex --copy-back /root/allbak //拷贝数据
170425 11:42:55 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.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
incremental backup from 2543884 is enabled.
xtrabackup: cd to /backup/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2549924)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = /incr01/
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytes
Applying /incr01//ibdata1.delta to ./ibdata1...
... ...
170425 11:43:09 [01] Copying /incr01/performance_schema/global_status.frm to ./performance_schema/global_status.frm
170425 11:43:09 [01]        ...done
170425 11:43:09 [01] Copying /incr01/performance_schema/session_status.frm to ./performance_schema/session_status.frm
170425 11:43:09 [01]        ...done
170425 11:43:09 [00] Copying /incr01//xtrabackup_info to ./xtrabackup_info
170425 11:43:09 [00]        ...done
170425 11:43:10 completed OK!
[root@host50 ~]# chown  -R mysql:mysql /var/lib/mysql //修改所有者与组

步骤四：验证数据恢复

1）启动服务

[root@host51 ~]# systemctl  start mysqld
[root@host51 ~]# mysql -uroot -p123456
mysql> show databases;
mysql> select * from db3.user2;
mysql>select count(*) from db3.user;
mysql>

2）查看数据

[root@host51 ~]# mysql -uroot -p123456
mysql> show databases;
mysql> select * from db3.user2;
mysql> select count(*) from db3.user;

2 案例2：恢复单张表
2.1 问题

执行删除数据命令
使用备份目录/allbak 恢复表数据
验证数据恢复

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：安装XtraBackup软件包

1）执行删除数据命令

[root@host50 ~]# mysql –uroot  -p123456
mysql> delete from db3.user2; //误删除数据操作   
mysql>

删除表空间

mysql> alter table db3.user2 discard tablespace;
导出表信息

[root@host50 ~ ]# innobackupex --apply-log --export /allbak
拷贝表信息文件到数据库目录下

[root@host50 ~]# cp /allbak/db3/user2.{cfg,exp,ibd} /var/lib/mysql/db3/
修改表信息文件的所有者及组用户为mysql

[root@host50 ~]# chown mysql:mysql /var/lib/mysql/db3/user2.*
导入表空间

mysql> alter table db3.user2 import tablespace;
删除数据库目录下的表信息文件

[root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.cfg
[root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.exp
查看表记录

mysql> select * from db3.user2;

3 案例3：增量备份与恢复
3.1 问题

具体要求如下：
备份所有数据
备份新产生的数据
删除数据
使用备份文件恢复数据

3.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：备份所有数据,在50主机执行

1）完全备份（备份所有数据到/fullbak目录）

[root@host50 ~]# innobackupex --user root --password 123456  /fullbak --no-timestamp

步骤二：增量备份（每次执行备份，值备份新数据,在50主机执行）

插入新记录，并做增量备份

mysql> insert into db3.user2 values(5,“jack”);// 插入新记录,多写几条
[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp //第1次增量备份，数据存储目录/new1dir
插入新记录，并做增量备份

mysql> insert into db3.user2 values(6,“jack”);// 插入新记录,多写几条

[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/newdir1 --no-timestamp //第2次增量备份，数据存储目录/new2dir

把备份文件拷贝给目标主机51

[root@host50 ~]# scp -r /fullbak root@192.168.4.51:/root/
[root@host50 ~]# scp -r /new1dir/ root@192.168.4.51:/root/
[root@host50 ~]# scp -r /new2dir/ root@192.168.4.51:/root/

步骤三：在主机51 恢复数据

停止服务，并清空数据

[root@host51 ~]# systemctl stop mysqld
[root@host51 ~]# rm -rf /var/lib/mysql/*
合并日志

[root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak //准备恢复数据
[root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new1dir //合并日志
[root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new2dir //合并日志
[root@host51 ~ ]# rm -rf /root/new2dir //恢复后，可以删除了
[root@host51 ~ ]# rm -rf /root/new1dir //恢复后，可以删除了
恢复数据

[root@host51 ~ ]# innobackupex --copy-back /root/fullbak //拷贝文件到数据库目录下
[root@host51 ~ ]# chown -R mysql:mysql /var/lib/mysql //修改所有者与组用户
[root@host51 ~ ]# systemctl start mysqld //启动服务
[root@host51 ~ ]# mysql -uroot -p123456 //登录
mysql> select count(*) from db3.user; //查看数据

骁林

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
percona软件介绍、 innobackupex备份与恢复

案例1：数据完全备份与恢复案例2：恢复单张表案例3：增量备份与恢复1 案例1：数据完全备份与恢复1.1 问题安装percona软件包备份所有数据到/allbak目录下搭建新的数据库服务器，使用备份文件恢复数据验证数据恢复1.2 步骤实现此案例需要按照如下步骤进行。步骤一：安装XtraBackup软件包1）安装软件[root@host50 ~]# rpm -ivh li...
复制链接

扫一扫