暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Xtrabackup8.0压缩备份和恢复MySQL8.0

Xtrabackup8.0压缩备份和恢复MySQL8.0

原创 董小姐 2024-08-06
1166

本文档实验环境如下：

操作系统：Centos7.9 x86_64 查询命令：cat /etc/*-release lscpu

数据库：MySQL8.0.35 查询命令：mysql -V

Xtrabackup版本：Xtrabackup8.0

Xtrabackup介绍

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，Xtrabackup有2个工具，分别是xtrabakup、innobakupe。

Xtrabackup是Percona公司的一款用于MySQL在线物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具，是一个只能对innoDB和xtraDB存储引擎做数据备份的工具，不能备份MyISAM数据表，支持在线备份（备份时不影响数据读写），全量备份和增量备份，单库单表备份，是商业备份工具InnoDB Hotbackup的一个很好的替代品。

它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。

Innobackupex完整备份后生成几个重要文件。

Xtrabackup特点

  1. 能够对InnoDB实现热备，无需暂停数据库
  2. 能够对MySQL进行增量备份
  3. 对MySQL备份能够实现流式压缩并传输给其他服务器，–stream参数实现
  4. MySQL服务运行时能够在MySQL 服务器之间进行表的迁移
  5. 能够很容易创建一个MySQL从服务器
  6. 备份MySQL时不会增加服务器负担
  7. 自动备份校验
  8. 还原速度快
  9. XtraBackup能够带InnoDB引擎创建的表实现热备，对MyISAM引擎实现温备

Xtrabackup备份原理

在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

使用xtrabackup进行还原，需要xtrabackup进行”备份”和”准备”：先将文件全部复制过来，再根据事务日志对部分操作进行回滚，如下描述：

xtrabackup的备份过程。Xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。所以，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。Xtrabackup持续地做这个操作，这些数据改动会写入xtrabackup_logfile文件。xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。

接下来是准备（prepare）过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像mysql刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了。

详细过程如下

  1. 调用xtrabackup对innodb表空间文件（这一瞬间的映像Time1）备份，而在这个innodb表备份期间数据库是不加锁的，外部可以继续往库里增减数据（这才能叫热备份）。而在Time1和Time2这两个时间点之间的改动由一个线程不断地扫innodb log获得(ChangeSet1)，一旦发现redo中有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中
  2. 锁所有库。
  3. 以直接拷贝的方式备份frm,MYD,MYI,MRG,TRG,TRN,opt格式的文件。
  4. 步骤3中的数据备份完毕时(Time2)，停止扫innodb log的线程，把ChangeSet1的数据拷贝到备份中。
  5. 解锁所有库。
  6. 终止挂起，备份完毕。

Xtrabackup 不备份 binlog 怎么保证一致性

  • 备份时全局锁阶段做的操作
2020-08-17T09:58:36.167905+08:00         2116 Query     FLUSH TABLES WITH READ LOCK
2020-08-17T09:58:36.490928+08:00         2116 Query     SHOW VARIABLES
2020-08-17T09:58:36.498670+08:00         2116 Query     SHOW SLAVE STATUS
2020-08-17T09:58:36.499435+08:00         2116 Query     SHOW MASTER STATUS
2020-08-17T09:58:36.499747+08:00         2116 Query     SHOW VARIABLES
2020-08-17T09:58:36.503341+08:00         2116 Query     FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
2020-08-17T09:58:36.704273+08:00         2116 Query     UNLOCK TABLES
  • 这里最主要是要保证两点：
  • 非事务数据之间一致性；
  • 数据和 binlog 位点的一致性。
  • 其中 FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS，是防止 innodb_flush_log_at_trx_commit 不等于 1 ，redo log没有刷到磁盘。而 SHOW MASTER STATUS 则是为了获取 binlog 位点。

crash recovery 过程要保证“数据和binlog的一致”，因为 crash 后不能出现事务重做提交而 binlog 没记录的情况，这样会导致从库丢失数据。那备份需要考虑的是什么？有两点：

  • 备份恢复后，再基于 binlog 做精确恢复时，--start-position 的位置是正确的，不会重放、漏掉事物；
  • 备份恢复后，作为从库向主库复制数据时，复制起始位置是正确的，不会重放、漏掉事物。

首先我们得知道事务二阶段提交过程中的3个队列 flush、sync、commit 都有排他锁，一个大事务 commit 可能需要几秒钟，那么此时执行 FTWRL 是会被阻塞的，commit 结束后才能取得全局锁，取得全局锁后，执行 commit 会被阻塞：

这保证了 xtrabackup 备份的 redo log 中只有两种事务：已经完成提交的，和还没开始提交的（未执行 commit 的事务可能被后台线程刷盘），不会出现 prepare 状态的事务。另外还有一个知识点：GTID 的生成和写 binlog 缓存是在二阶段提交的 binlog flush 阶段做的。结合起来则说明：FTWRL 后 执行 show master status 获取 binlog 位点，只有完成提交的事务才会在其中，所以这保证了 binlog 位点和 binlog 的一致。

所以在 xtrabackup 的恢复过程，不需要处理 prepare 状态的事务，也就不需要再验证该事务是否在 binlog 中了。

安装前准备

查看操作系统基本信息

--查看操作系统
cat /etc/*-release

--查看glibc版本
ldd --version

配置本地Yum源

下载OS镜像文件

所有版本：centos-vault安装包下载_开源镜像站-阿里云 (aliyun.com)

Centos7.9：https://mirrors.aliyun.com/centos-vault/7.9.2009/isos/x86_64/CentOS-7-x86_64-DVD-2009.iso

挂载OS镜像文件

方法1：上传OS镜像文件至“/opt”路径，并挂载OS镜像文件至“/mnt”目录下。

mount /opt/CentOS-7-x86_64-DVD-2009.iso /mnt

说明

iso文件名请根据实际情况修改，该操作单次生效，重启后失效，可执行下列操作开机自动挂载OS镜像文件。

  1. 打开fstab文件。
vim /etc/fstab
  1. 按“i”进入编辑模式，在文件末尾添加如下信息。
/opt/mount /opt/CentOS-7-x86_64-DVD-2009.iso  /mnt iso9660 loop 0 0
  1. 按“Esc”键，输入:wq!，按“Enter”保存并退出编辑。

方法2：使用浏览器登录BMC，通过KVM加载OS镜像文件

  1. 查看OS镜像对应的设备符号。ls /dev/sr*
  2. 将OS镜像文件挂载至“/mnt”目录下。
mount /dev/sr0 /mnt
df -h | grep /mnt
ls /mnt/

说明

/dev/sr0为OS镜像对应的设备符号，需要和查看的设备符号保持一致。

备份Yum源

cd /etc/yum.repos.d
mkdir bak
mv *.repo bak

配置本地Yum源

cd /etc/yum.repos.d
cat >> local.repo << "EOF"
[local]
name=local.repo
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF

参数说明：baseurl中file路径为镜像挂载路径，挂载目录相对应

使Yum源生效

yum clean all
yum makecache
yum list

源库构建测试数据

mysql -uroot -pmysql
create database dxj;
use dxj;
create table dxj(id int);
insert into dxj values(10);
insert into dxj values(50);
select * from dxj;

输出如下：

root@localhost :dxj 16:56:10>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
+------+
2 rows in set (0.00 sec)

源库数据统计

--查看所有数据库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

--查看指定库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

--统计所有对象数量
--统计所有对象数量
#统计表数量
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   WHERE table_schema='dxj' GROUP BY table_schema; 
#存储过程
SELECT db,COUNT(name) FROM mysql.proc WHERE db='dxj' AND type= 'PROCEDURE'  GROUP BY db;
#函数
SELECT db,COUNT(`name`) FROM mysql.proc WHERE dbdb='dxj' AND `type` = 'FUNCTION'  GROUP BY db
#视图
select table_schema,count(*)  from information_schema.views  where table_schema='dxj' group by table_schema; 
#触发器
select trigger_schema,count(*)  from information_schema.triggers  where trigger_schema='dxj' group by trigger_schema;

--统计每个表记录数
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'pims_ptemp' order by table_rows desc;
说明：where table_schema = '数据库名';

下载并安装

XtraBackup目前有2.4和8.0版本，其中XtraBackup2.4是基于MySQL5.x，XtraBackup8.0是基于MySQL8.x，若建议下载最新版本。

安装方式有rpm方式、二进制tar包方式、源码方式，可视情况选择，由于rpm方式安装需要下载依赖包，有些依赖包不好下载，建议用二进制方式安装。

rpm包下载和安装

下载并安装依赖包

Xtrabackup需要perl、libev、zstd依赖包，下载Xtrabackup并上传至/opt目录下，若不安装会报如下错误：

[root@localhost opt]# rpm -ivh percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm
warning: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
	libev.so.4()(64bit) is needed by percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
	perl(DBD::mysql) is needed by percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
	perl(Digest::MD5) is needed by percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
	rsync is needed by percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
	zstd is needed by percona-xtrabackup-80-8.0.35-31.1.el7.x86_64

操作系统版本Centos7.9 故下载操作系统对应的rpm包

所有版本依赖包入口：centos-vault安装包下载_开源镜像站-阿里云 (aliyun.com)

yum install -y   perl*
rpm -ivh libev-4.15-7.el7.x86_64.rpm
rpm -ivh zstd-1.5.5-1.el7.x86_64.rpm

扩展：libev、zstd 本地yum中没有，可在以下链接中下载

https://rpm.pbone.net/info_idpl_72380676_distro_centos7_com_libev-4.15-7.el7.x86_64.rpm.html

https://rpm.pbone.net/info_idpl_82568853_distro_redhatel7_com_zstd-1.5.5-1.el7.x86_64.rpm.html

【免费】Xtrabackup依赖包-zstd资源-CSDN文库

下载Xtrabackup

根据以上查出的glibc版本，下载对应的Xtrabackup并上传至/opt目录下

## 下载地址
https://www.percona.com/downloads/XtraBackup/LATEST/

## 下载xtrabackup
https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-31/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm

安装Xtrabackup

--安装Xtrabackup
cd /opt
rpm -ivh percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm

--查看版本
xtrabackup --version
或
xtrabackup -v

输出如下：

[root@localhost opt]# xtrabackup --version
2024-08-02T15:21:36.127062+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysqldb/data8 --server-id=103 --log_bin=/mysqldb/binlog8/mybinlog --open_files_limit=65535 --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_file_per_table=1 --innodb_buffer_pool_size=1G
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)


Xtrabackup完全备份恢复

xtrabackup全备份

创建备份目录

--创建备份目录
mkdir -p /backup
chown -R mysql:mysql /backup

全备份

全备份分2种场景，前台备份和后台备份。VPN、堡垒机、服务器固定时间断开时采用后台备份场景。

场景1：前台备份

--前台备份
xtrabackup -uroot -pmysql --backup --target-dir=/backup/base --parallel=6 > /backup/full_`date +%Y%m%d`.log  2>&1

场景2：后台备份

--后台备份
nohup xtrabackup -uroot -pmysql --backup --target-dir=/backup/base --parallel=6 > /backup/full_`date +%Y%m%d`.log  2>&1  &

查看备份目录内容

--查看备份目录内容
ls -l /backup/base/

输出如下

[root@localhost ~]# ls -l /backup/base/
total 70708
-rw-r-----. 1 root root      450 Aug  6 08:53 backup-my.cnf
drwxr-x---. 2 root root       21 Aug  6 08:53 dxj
-rw-r-----. 1 root root     4883 Aug  6 08:53 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Aug  6 08:53 ibdata1
-rw-r-----. 1 root root      157 Aug  6 08:53 mybinlog.000016
-rw-r-----. 1 root root       33 Aug  6 08:53 mybinlog.index
drwxr-x---. 2 root root      159 Aug  6 08:53 mysql
-rw-r-----. 1 root root 26214400 Aug  6 08:53 mysql.ibd
drwxr-x---. 2 root root     8192 Aug  6 08:53 performance_schema
drwxr-x---. 2 root root       28 Aug  6 08:53 sys
-rw-r-----. 1 root root 16777216 Aug  6 08:53 undo_001
-rw-r-----. 1 root root 16777216 Aug  6 08:53 undo_002
-rw-r-----. 1 root root       20 Aug  6 08:53 xtrabackup_binlog_info
-rw-r-----. 1 root root      134 Aug  6 08:53 xtrabackup_checkpoints
-rw-r-----. 1 root root      480 Aug  6 08:53 xtrabackup_info
-rw-r-----. 1 root root     2560 Aug  6 08:53 xtrabackup_logfile
-rw-r-----. 1 root root       39 Aug  6 08:53 xtrabackup_tablespaces

停止数据库

systemctl stop mysqld
ps -ef | grep mysqld

xtrabackup全恢复

源服务器

将备份传输到目标服务器

将备份文件传输到目标服务器192.168.40.161

scp -r /backup/base root@192.168.40.161:/opt/

将配置文件传输到目标服务器192.168.40.161

scp /mysqldb/my8.cnf root@192.168.40.161:/opt/my.cnf

目标服务器

安装同版本数据库软件

安装过程省略，可参考openEuler 22.03 LTS源码安装部署MySQL8.0.39 - 墨天轮 (modb.pro)

--查MySQL版本
[root@mysqldb01 xtrabackup]# mysql -V
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

规划数据目录

mkdir -p  /opt/mysqldb/data 
mkdir -p  /opt/mysqldb/binlog
mkdir -p  /opt/mysqldb/log

chown -R mysql:mysql /opt/mysqldb/data 
chown -R mysql:mysql /opt/mysqldb/binlog
chown -R mysql:mysql /opt/mysqldb/log

chmod -R 755 /opt/mysqldb/data 
chmod -R 755 /opt/mysqldb/binlog
chmod -R 755 /opt/mysqldb/log

更改配置文件

更改配置文件中的目录

--复制配置文件
cp /opt/my.cnf /opt/mysqldb/my.cnf

--更改配置文件
vi /opt/mysqldb/my.cnf
将目录更改为目标服务器规划的目录

--更改权限
chown mysql:mysql /opt/mysqldb/my.cnf
chmod 755  /opt/mysqldb/my.cnf

--创建软链接
ln -s /opt/mysqldb/my.cnf /etc/my.cnf

一致性检查

xtrabackup --prepare --target-dir=/opt/base

说明：
2024-08-06T08:59:34.158261+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
返回如上结果说明一致性检查成功。

数据库恢复

  1. 确保数据库目录是空的
  2. 确认数据库服务停止状态
--确保数据库目录是空的
ls -l /opt/mysqldb/data/   返回空

--确认数据库服务停止状态
ps -ef | grep mysql     返回空 

--恢复
xtrabackup --defaults-file=/opt/mysqldb/my.cnf --copy-back   --target-dir=/opt/base 

返回2024-08-05T17:51:45.805344+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
说明恢复成功

参数说明：
--copy-back         Copy all the files in a previously made backup from the
                    backup directory to their original locations.
                    将以前创建的备份中的所有文件从备份目录复制到其原始位置。

--target-dir  备份文件位置    

--defaults-file  会将文件恢复到配置文件中配置的数据目录  日志目录中

修改文件及文件夹属主

chown -R mysql.mysql /opt/mysqldb

配置开机自启服务并启动数据库

cat > /usr/lib/systemd/system/mysqld.service << "EOF"
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysqldb/mysql8035/bin/mysqld --defaults-file=/opt/mysqldb/my.cnf
LimitNOFILE=65536  
LimitNPROC=65536
EOF

chmod +x /usr/lib/systemd/system/mysqld.service

systemctl daemon-reload 
systemctl enable mysqld 
systemctl start mysqld 
systemctl status mysqld

检验数据

mysql -uroot -pmysql

--查看所有数据库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

--查看指定库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

--统计所有对象数量
--统计所有对象数量
#统计表数量
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   WHERE table_schema='dxj' GROUP BY table_schema; 
#存储过程
SELECT db,COUNT(name) FROM mysql.proc WHERE db='dxj' AND type= 'PROCEDURE'  GROUP BY db;
#函数
SELECT db,COUNT(`name`) FROM mysql.proc WHERE dbdb='dxj' AND `type` = 'FUNCTION'  GROUP BY db
#视图
select table_schema,count(*)  from information_schema.views  where table_schema='dxj' group by table_schema; 
#触发器
select trigger_schema,count(*)  from information_schema.triggers  where trigger_schema='dxj' group by trigger_schema;

--统计每个表记录数
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'pims_ptemp' order by table_rows desc;
说明：where table_schema = '数据库名';

--查询数据
use dxj;
select * from dxj;

输出如下：

root@localhost :dxj 17:52:35>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
+------+
2 rows in set (0.00 sec)

Xtrabackup增量备份恢复

模拟数据

use dxj;
insert into dxj values(80);
select * from dxj;

输出如下：

root@localhost :dxj 16:56:10>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
|   80 |
+------+
3 rows in set (0.00 sec)

Xtrabackup增量备份

Xtrabackup全备

创建备份目录

如果已创建则忽略

--创建备份目录
mkdir -p /backup
chown -R mysql:mysql /backup

模拟数据

mysql -uroot -pmysql
use dxj;
insert into dxj values(80);
select * from dxj;

输出如下：

root@localhost :dxj 16:56:10>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
|   80 |
+------+
3 rows in set (0.00 sec)

全备份

全备份分2种场景，前台备份和后台备份。VPN、堡垒机、服务器固定时间断开时采用后台备份场景。

场景1：前台备份
--前台备份
xtrabackup -uroot -pmysql --backup --target-dir=/backup/base --parallel=6 > /backup/full_`date +%Y%m%d`.log  2>&1
场景2：后台备份
--后台备份
nohup xtrabackup -uroot -pmysql --backup --target-dir=/backup/base --parallel=6 > /backup/full_`date +%Y%m%d`.log  2>&1  &

查看备份目录内容

--查看备份目录内容
[root@localhost backup]# ls -l /backup/base/

输出如下

[root@localhost backup]# ls -l /backup/base/
total 70708
-rw-r-----. 1 root root      450 Aug  6 09:07 backup-my.cnf
drwxr-x---. 2 root root       21 Aug  6 09:07 dxj
-rw-r-----. 1 root root     4501 Aug  6 09:07 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Aug  6 09:07 ibdata1
-rw-r-----. 1 root root      157 Aug  6 09:07 mybinlog.000018
-rw-r-----. 1 root root       33 Aug  6 09:07 mybinlog.index
drwxr-x---. 2 root root      159 Aug  6 09:07 mysql
-rw-r-----. 1 root root 26214400 Aug  6 09:07 mysql.ibd
drwxr-x---. 2 root root     8192 Aug  6 09:07 performance_schema
drwxr-x---. 2 root root       28 Aug  6 09:07 sys
-rw-r-----. 1 root root 16777216 Aug  6 09:07 undo_001
-rw-r-----. 1 root root 16777216 Aug  6 09:07 undo_002
-rw-r-----. 1 root root       20 Aug  6 09:07 xtrabackup_binlog_info
-rw-r-----. 1 root root      134 Aug  6 09:07 xtrabackup_checkpoints
-rw-r-----. 1 root root      480 Aug  6 09:07 xtrabackup_info
-rw-r-----. 1 root root     2560 Aug  6 09:07 xtrabackup_logfile
-rw-r-----. 1 root root       39 Aug  6 09:07 xtrabackup_tablespaces

Xtrabackup增备

模拟数据

mysql -uroot -pmysql
use dxj;
insert into dxj values(100);
select * from dxj;

输出如下：

root@localhost :dxj 16:56:10>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
|   80 |
|   100 |
+------+
4 rows in set (0.00 sec)

增量备份

增量备份分2种场景，前台备份和后台备份。VPN、堡垒机、服务器固定时间断开时采用后台备份场景。

场景1：前台备份
--前台备份
xtrabackup -uroot -pmysql --backup --target-dir=/backup/incre1  --incremental-basedir=/backup/base --parallel=6   > /backup/incre_`date +%Y%m%d`.log  2>&1

第一个参数就是你增量备份的路径， 第二个是你上一次备份的路径  在上一份备份基础上做增量
场景2：后台备份
--后台备份
nohup xtrabackup -uroot -pmysql --backup --target-dir=/backup/incre1  --incremental-basedir=/backup/base --parallel=6   > /backup/incre_`date +%Y%m%d`.log  2>&1 &

查看备份目录内容

--查看备份目录内容
ls -l /backup/incre1/

输出如下

[root@localhost ~]# ls -l /backup/incre1/
total 324
-rw-r-----. 1 root root    450 Aug  6 09:13 backup-my.cnf
drwxr-x---. 2 root root     47 Aug  6 09:13 dxj
-rw-r-----. 1 root root   4501 Aug  6 09:13 ib_buffer_pool
-rw-r-----. 1 root root  32768 Aug  6 09:13 ibdata1.delta
-rw-r-----. 1 root root     64 Aug  6 09:13 ibdata1.meta
-rw-r-----. 1 root root    157 Aug  6 09:13 mybinlog.000019
-rw-r-----. 1 root root     33 Aug  6 09:13 mybinlog.index
drwxr-x---. 2 root root    186 Aug  6 09:13 mysql
-rw-r-----. 1 root root  49152 Aug  6 09:13 mysql.ibd.delta
-rw-r-----. 1 root root     73 Aug  6 09:13 mysql.ibd.meta
drwxr-x---. 2 root root   8192 Aug  6 09:13 performance_schema
drwxr-x---. 2 root root     61 Aug  6 09:13 sys
-rw-r-----. 1 root root  49152 Aug  6 09:13 undo_001.delta
-rw-r-----. 1 root root     69 Aug  6 09:13 undo_001.meta
-rw-r-----. 1 root root 131072 Aug  6 09:13 undo_002.delta
-rw-r-----. 1 root root     69 Aug  6 09:13 undo_002.meta
-rw-r-----. 1 root root     20 Aug  6 09:13 xtrabackup_binlog_info
-rw-r-----. 1 root root    139 Aug  6 09:13 xtrabackup_checkpoints
-rw-r-----. 1 root root    524 Aug  6 09:13 xtrabackup_info
-rw-r-----. 1 root root   2560 Aug  6 09:13 xtrabackup_logfile
-rw-r-----. 1 root root     39 Aug  6 09:13 xtrabackup_tablespaces

再次增量备份

模拟数据
mysql -uroot -pmysql
use dxj;
insert into dxj values(120);
select * from dxj;

输出如下：

root@localhost :dxj 16:56:10>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
|   80 |
|   100 |
|   120 |
+------+
5 rows in set (0.00 sec)
增量备份

增量备份分2种场景，前台备份和后台备份。VPN、堡垒机、服务器固定时间断开时采用后台备份场景。

场景1：前台备份
--前台备份
xtrabackup -uroot -pmysql --backup --target-dir=/backup/incre2  --incremental-basedir=/backup/incre1 --parallel=6   > /backup/incre_`date +%Y%m%d`.log  2>&1

第一个参数就是你增量备份的路径， 第二个是你上一次备份的路径  在上一份备份基础上做增量
场景2：后台备份
--后台备份
nohup xtrabackup -uroot -pmysql --backup --target-dir=/backup/incre2  --incremental-basedir=/backup/incre1 --parallel=6   > /backup/incre_`date +%Y%m%d`.log  2>&1 &
查看备份目录内容
--查看备份目录内容
ls -l /backup/incre2/

输出如下

[root@localhost backup]# ls -l /backup/incre2/
total 196
-rw-r-----. 1 root root   450 Aug  6 09:18 backup-my.cnf
drwxr-x---. 2 root root    47 Aug  6 09:18 dxj
-rw-r-----. 1 root root  4501 Aug  6 09:18 ib_buffer_pool
-rw-r-----. 1 root root 32768 Aug  6 09:18 ibdata1.delta
-rw-r-----. 1 root root    64 Aug  6 09:18 ibdata1.meta
-rw-r-----. 1 root root   157 Aug  6 09:18 mybinlog.000020
-rw-r-----. 1 root root    33 Aug  6 09:18 mybinlog.index
drwxr-x---. 2 root root   186 Aug  6 09:18 mysql
-rw-r-----. 1 root root 16384 Aug  6 09:18 mysql.ibd.delta
-rw-r-----. 1 root root    73 Aug  6 09:18 mysql.ibd.meta
drwxr-x---. 2 root root  8192 Aug  6 09:18 performance_schema
drwxr-x---. 2 root root    61 Aug  6 09:18 sys
-rw-r-----. 1 root root 16384 Aug  6 09:18 undo_001.delta
-rw-r-----. 1 root root    69 Aug  6 09:18 undo_001.meta
-rw-r-----. 1 root root 65536 Aug  6 09:18 undo_002.delta
-rw-r-----. 1 root root    69 Aug  6 09:18 undo_002.meta
-rw-r-----. 1 root root    20 Aug  6 09:18 xtrabackup_binlog_info
-rw-r-----. 1 root root   139 Aug  6 09:18 xtrabackup_checkpoints
-rw-r-----. 1 root root   526 Aug  6 09:18 xtrabackup_info
-rw-r-----. 1 root root  2560 Aug  6 09:18 xtrabackup_logfile
-rw-r-----. 1 root root    39 Aug  6 09:18 xtrabackup_tablespaces

停止数据库

systemctl stop mysqld
ps -ef | grep mysqld

Xtrabackup增量恢复

源服务器

scp -r /backup/base root@192.168.40.161:/opt/

scp -r /backup/incre1 root@192.168.40.161:/opt/

scp -r /backup/incre2 root@192.168.40.161:/opt/

将配置文件传输到目标服务器192.168.40.161

scp /mysqldb/my8.cnf root@192.168.40.161:/opt/my.cnf

目标服务器

安装同版本数据库软件

安装过程省略，可参考openEuler 22.03 LTS源码安装部署MySQL8.0.39 - 墨天轮 (modb.pro)

--查MySQL版本
[root@mysqldb01 xtrabackup]# mysql -V
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

规划数据目录

mkdir -p  /opt/mysqldb/data 
mkdir -p  /opt/mysqldb/binlog
mkdir -p  /opt/mysqldb/log

chown -R mysql:mysql /opt/mysqldb/data 
chown -R mysql:mysql /opt/mysqldb/binlog
chown -R mysql:mysql /opt/mysqldb/log

chmod -R 755 /opt/mysqldb/data 
chmod -R 755 /opt/mysqldb/binlog
chmod -R 755 /opt/mysqldb/log

更改配置文件

更改配置文件中的目录

--复制配置文件
cp /opt/my.cnf /opt/mysqldb/my.cnf

--更改配置文件
vi /opt/mysqldb/my.cnf
将目录更改为目标服务器规划的目录

--更改权限
chown mysql:mysql /opt/mysqldb/my.cnf
chmod 755  /opt/mysqldb/my.cnf

--创建软链接
ln -s /opt/mysqldb/my.cnf /etc/my.cnf

一致性检查

xtrabackup --prepare --apply-log-only --target-dir=/opt/base
xtrabackup --prepare --apply-log-only --target-dir=/opt/base --incremental-dir=/opt/incre1
xtrabackup --prepare --target-dir=/opt/base --incremental-dir=/opt/incre2

说明：
2024-08-06T09:27:30.717889+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
返回如上结果说明一致性检查成功。

数据库恢复

  1. 确保数据库目录是空的
  2. 确认数据库服务停止状态
--确保数据库目录是空的
ls -l /opt/mysqldb/data   返回空

--确认数据库服务停止状态
ps -ef | grep mysql     返回空 

--恢复
xtrabackup --defaults-file=/opt/mysqldb/my.cnf --copy-back   --target-dir=/opt/base 
  

参数说明：
--copy-back         Copy all the files in a previously made backup from the
                    backup directory to their original locations.
                    将以前创建的备份中的所有文件从备份目录复制到其原始位置。

--target-dir  备份文件位置

修改文件及文件夹属主

chown -R mysql.mysql /opt/mysqldb

配置开机自启服务并启动数据库

cat > /usr/lib/systemd/system/mysqld.service << "EOF"
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysqldb/mysql8035/bin/mysqld --defaults-file=/opt/mysqldb/my.cnf
LimitNOFILE=65536  
LimitNPROC=65536
EOF

chmod +x /usr/lib/systemd/system/mysqld.service

systemctl daemon-reload 
systemctl enable mysqld 
systemctl start mysqld 
systemctl status mysqld

检验数据

--登录数据库
mysql -uroot -pmysql

--查看所有数据库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

--查看指定库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

--统计所有对象数量
--统计所有对象数量
#统计表数量
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   WHERE table_schema='dxj' GROUP BY table_schema; 
#存储过程
SELECT db,COUNT(name) FROM mysql.proc WHERE db='dxj' AND type= 'PROCEDURE'  GROUP BY db;
#函数
SELECT db,COUNT(`name`) FROM mysql.proc WHERE dbdb='dxj' AND `type` = 'FUNCTION'  GROUP BY db
#视图
select table_schema,count(*)  from information_schema.views  where table_schema='dxj' group by table_schema; 
#触发器
select trigger_schema,count(*)  from information_schema.triggers  where trigger_schema='dxj' group by trigger_schema;

--统计每个表记录数
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'pims_ptemp' order by table_rows desc;
说明：where table_schema = '数据库名';

--查询数据
use dxj;
select * from dxj;

输出如下：

root@localhost :dxj 09:31:27>select * from dxj;
+------+
| id   |
+------+
|   10 |
|   50 |
|   80 |
|  100 |
|  120 |
+------+
5 rows in set (0.00 sec)


参考链接：

https://www.modb.pro/db/45783?utm_source=index_ai

https://www.modb.pro/db/411046?utm_source=index_ai

利用xtrabackup8.0 完全备份和还原MySQL8.0_xtrabackup备份到异地-CSDN博客

墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论