Xtrabackup备份mysql

数据库

Xtrabackup备份mysql

一、安装Xtrabackup8.0

官网地址：https://www.percona.com/
下载地址：1689668481236
1689668549561

1
yum install  -y /usr/local/src/percona-xtrabackup-80-8.0.33-27.1.el7.x86_64.rpm

二、全量备份

1
xtrabackup -uroot -H 10.1.0.18 -P 3306  -p123456@Root --backup --target-dir=/home/backup/base --extra-lsndir=/home/mysql/binlog

三、预还原准备

1
xtrabackup --prepare --target-dir=/home/backup/base

四、还原

1
2
3
4
xtrabackup  --no-defaults   --copy-back --target-dir=/home/backup/base --datadir=/home/mysql3307

 chown  mysql:mysql -R mysql3307
注意：自定义还原目录还原到期望的数据库就忽略配置文件

五、还原到docker

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
#指定运行docker的用户和组，本机mysql用户和组为306，把用户和组都挂载
docker run -d --name mysql3307 \
 --user 306:306 \
 -p 3307:3306  \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai   \
-v /home:/home \
-v /etc/my3307.cnf:/etc/my.cnf \
mysql:8.0.33

/* 配置文件*/
#/etc/my3307.cnf
[mysqld]
bind-address = 0.0.0.0
default_time_zone = '+8:00'
#system-time_zone ='+8:00'
log_timestamps=system
datadir=/home/mysql3307
socket=/home/mysql3307/mysql.sock
pid-file=/home/mysql3307/mariadb.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd


log-error=/home/mysql3307/log
general_log=on
general_log_file=/home/mysql3307/general_log
log_output=TABLE
slow_query_log= on
slow_query_log_file=/home/mysql3307/slow_query_log
long_query_time=3
#log_bin=/home/binlog3307/binlog
#pid-file=/home/mysql/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[client]
socket=/home/mysql3307/mysql.sock

六、增量备份

1
2
3
4
5
6
7
8
xtrabackup -uroot \
 -H 10.1.0.18 \
 -P 3306  \
 -p123456@Root \
 --backup \
 --target-dir=/home/backup/inc1 \
 --incremental-basedir=/home/backup/base \
 --extra-lsndir=/home/mysql/binlog

七、增量还原

1
2
3
4
5
6
7
8
9
10
11
12
#预还原全量备份
xtrabackup --prepare \
--apply-log-only \
--target-dir=/home/backup/base

#预还原增量备份 --apply-log-only \ 多次还原不同时间点的增量，过程中的增量不能回滚事务，添加这个参数，最后一个增量回滚事务，不添加这个参数

xtrabackup --prepare \
--target-dir=/home/backup/base \
--incremental-dir=/home/backup/inc1

xtrabackup  --no-defaults   --copy-back --target-dir=/home/backup/base --datadir=/home/mysql3307