mysql 物理备份及恢复

一、物理复制的基本概念

物理备份:直接复制数据库文件，适用于大型的数据库环境，不受存储引擎的限制，但不能恢复到不同的mysql版本

完整备份：也叫完全备份，每次将所有数据（不管自第一次备份有没有修改过），进行一次完整的复制，备份后会清楚文件的存档属性，方便日后增量备份或者差异备份进行版本比较。

特点：占用空间大，备份速度慢，但是恢复时一次恢复到位，恢复速度快

增量备份：每次备份上一次备份到现在产生的数据

在第一次完整备份后，第二次开始每次都添加了存档属性，并在备份后将存档属性清除（为了在下一次备份时文档是否有变化，因为用户在每次备份以后修改清除存档属性的文件，存档属性就会自动加上，告诉系统这些文件有变化，下一次备份这些文件，这就是增加备份的工作机制）

特点：备份体积小，备份速度快，但是恢复的时候，需要按备份的时间顺序，逐个备份版本进行恢复，恢复时间长。

差异备份：只备份和完整备份不一样的

特点：占用空间的增量备份大，比完整备份小，恢复时仅需恢复第一个完整版和最后一个差异版，恢复速度介于完整备份和增量备份之间

二、安装xtrabackup

下载安装包并解压安装

三、完全备份和数据恢复

1.创建备份目录

[root@localhost ~]# mkdir /xt/full -p

2.备份

语法：innobackupx --user=用户 --password='密码'   备份目录

[root@localhost yum.repos.d]# innobackupex --user=root --password='123' /xtrabackup/full
xtrabackup: recognized server arguments: --server-id=1 --datadir=/var/lib/mysql --log_bin=/var/lib/mysql/mysql-bin.log 
xtrabackup: recognized client arguments: 
231006 11:46:47 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!".

231006 11:46:48  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).

.
.
.

231006 11:46:49 [00]        ...done
xtrabackup: Transaction log of lsn (3698047) to (3698056) was copied.
231006 11:46:49 completed OK!

3.查看备份文件

[root@localhost ~]# cd /xt/full
[root@localhost full]# ls
 2023-10-06_11-46-47
[root@localhost full]# cd 2023-10-06_11-46-47/
[root@localhost 2023-10-06_11-46-47]# ls
backup-my.cnf  hf              mysql               sys                     xtrabackup_info
db1            ib_buffer_pool  performance_schema  xtrabackup_binlog_info  xtrabackup_logfile
db3            ibdata1         school              xtrabackup_checkpoints

4.完全备份恢复数据

（1）关闭数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*

（2）恢复之前的验证

[root@localhost ~]# innobackupex --apply-log /xt/full/2023-10-06_11-46-47/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --
.
innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
xtrabackup: recognized client arguments: 
231006 12:35: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.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /xt/full/2023-10-06_11-46-47/
.
.
231006 12:35:59 completed OK!

（3）查看配置文件，确认数据库恢复目录

[root@localhost ~]# vim /etc/my.cnf

datadir=/var/lib/mysql

（4）恢复数据

[root@localhost ~]# innobackupex --copy-back /xt/full/2023-10-06_11-46-47/
xtrabackup: recognized server arguments: --server-id=1 --datadir=/var/lib/mysql --log_bin=/var/lib/mysql/mysql-bin.log 
xtrabackup: recognized client arguments: 
231006 12:38:02 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
231006 12:38:02 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
.
.
231006 12:38:02 [01]        ...done
231006 12:38:02 completed OK!

（5）修改权限

# chown mysql.mysql  /var/lib/mysql -R

（6）启动数据库

# systemctl start mysqld

四、增量备份和数据恢复

1.先完整备份（周一）

语法：innobackupex --user=用户 --password='密码' 备份路径

[root@localhost log]# innobackupex --user=root --password='123' /opt/full
xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
xtrabackup: recognized client arguments: 
231007 21:05:07 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!".

231007 21:05:07  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
231007 21:05:07  version_check Connected to MySQL server
231007 21:05:07  version_check Executing a version check against the server...
231007 21:05:07  version_check Done.
231007 21:05:07 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
.
.
xtrabackup: Transaction log of lsn (3080362) to (3080371) was copied.
231007 21:05:08 completed OK!

[root@localhost notfull]# cd /opt/full
[root@localhost full]# ls
2023-10-07_21-05-07

2.增量备份(周二)

语法： innobackupex --user=用户 --password='密码'  --incremental 备份路径 --incremental-basedir=完整备份的文件（周一）

[root@localhost log]# innobackupex --user=root --password='123' --incremental /opt/notfull --incremental-basedir=/opt/full/2023-10-07_21-05-07/
xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
xtrabackup: recognized client arguments: 
231007 21:16:51 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!".

231007 21:16:51  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
231007 21:16:51  version_check Connected to MySQL server
231007 21:16:51  version_check Executing a version check against the server...
231007 21:16:51  version_check Done.
231007 21:16:51 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080362 is enabled.
xtrabackup: uses posix_fadvise().
.
.
xtrabackup: Transaction log of lsn (3080717) to (3080726) was copied.
231007 21:16:52 completed OK!

[root@localhost log]# cd /opt/notfull
[root@localhost notfull]# ls
2023-10-07_21-16-51

3.增量备份（周三）

语法：innobackupex --user=用户 --password='密码' --incremental 备份的路径 --incremental-basedir=上次备份的文件（周二）

[root@localhost full]# innobackupex --user=root --password='123' --incremental /opt/notfull --incremental-basedir=/opt/notfull/2023-10-07_21-16-51/
xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
xtrabackup: recognized client arguments: 
231007 21:22:21 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!".

231007 21:22:21  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
231007 21:22:21  version_check Connected to MySQL server
231007 21:22:21  version_check Executing a version check against the server...
231007 21:22:21  version_check Done.
231007 21:22:21 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080717 is enabled.
xtrabackup: uses posix_fadvise()
.
.
xtrabackup: Transaction log of lsn (3081072) to (3081081) was copied.
231007 21:22:22 completed OK!

[root@localhost full]# cd /opt/notfull
[root@localhost notfull]# ls
2023-10-07_21-16-51  2023-10-07_21-22-21

4.增量备份恢复流程

1.关闭数据库

[root@localhost ~]# systemctl stop mysqld

2.清理环境（生产环境不可用）

[root@localhost ~]# rm -rf /var/lib/mysql/*

3.依次重演回滚

（1）重演周一

innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
xtrabackup: recognized client arguments: 
231007 21:26:59 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.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3080362)
xtrabackup: using the following InnoDB configuration for recovery:
.
.
InnoDB: Number of pools: 1
231007 21:27:00 completed OK!

(2)重演周二

[root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/ --incremental-dir=/opt/notfull/2023-10-07_21-16-51/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
xtrabackup: recognized client arguments: 
231007 21:27:49 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.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080362 is enabled.
xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
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=(3080717)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
.
.
231007 21:27:51 [00]        ...done
231007 21:27:51 completed OK!

(3)回滚周三

[root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/ --incremental-dir=/opt/notfull/2023-10-07_21-22-21/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
xtrabackup: recognized client arguments: 
231007 21:28: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.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080717 is enabled.
xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
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=(3081072)
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 = /opt/notfull/2023-10-07_21-22-21/
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
.
.
231007 21:28:20 [00]        ...done
231007 21:28:20 completed OK!

(4)重演，恢复数据

[root@localhost ~]# innobackupex --copy-back /opt/full/2023-10-07_21-05-07/
xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
xtrabackup: recognized client arguments: 
231007 21:28:57 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
231007 21:28:57 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
231007 21:28:57 [01]        ...done

.
.
231007 21:28:57 [01]        ...done
231007 21:28:57 completed OK!

4.修改权限

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/*

5.重启mysqld

[root@localhost ~]# systemctl start mysqld

五、差异备份和数据恢复

  环境准备

mysql> create table db3(id int,name varchar(30),time varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into db3 values(1,'xiaoliu','星期一');
Query OK, 1 row affected (0.00 sec)

[root@localhost ~]# rm -rf /opt/full

1.完整备份（周一）

语法：innobackupex --user=用户 --password=密码  完整备份的路径

[root@localhost ~]# innobackupex --user=root --password=123 /opt/full
xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
xtrabackup: recognized client arguments: 
231007 23:06:28 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!".

231007 23:06:28  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
231007 23:06:28  version_check Connected to MySQL server
231007 23:06:28  version_check Executing a version check against the server...
231007 23:06:28  version_check Done.
231007 23:06:28 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
.
.
xtrabackup: Transaction log of lsn (3085687) to (3085696) was copied.
231007 23:06:30 completed OK!

2.差异备份（周二）

mysql> insert into db.db3 values (2,'xiaozhao','星期二');

语法：innobackupex --user=root --password='密码' --incremental  备份路径 --incremental-basedir=完整备份的路径（第一天）

[root@localhost ~]# innobackupex --user=root --password=123 --incremental /opt/diff --incremental-basedir=/opt/full/2023-10-07_23-06-28/
.
.
xtrabackup: Transaction log of lsn (3086067) to (3086076) was copied.
231007 23:12:31 completed OK!

3.差异备份（周三）

mysql> insert into db.db3 values(3,'xiaozhang','星期三');
Query OK, 1 row affected (0.00 sec)

语法：innobackupex --user=用户 --password='密码' --incremental 备份路径 --incremental-basedir=完整备份的路径（周一）

[root@localhost ~]# innobackupex --user=root --password='123' --incremental /opt/diff --incremental-basedir=/opt/full/2023-10-07_23-06-28/
.
.
xtrabackup: Transaction log of lsn (3086448) to (3086457) was copied.
231007 23:17:24 completed OK!

 4.数据恢复

mysql> drop table db3;
Query OK, 0 rows affected (0.01 sec)

（1）停止数据库

[root@localhost ~]# systemctl stop mysqld

（2）清理环境

[root@localhost ~]# rm -rf /var/lib/mysql/*

（3）重演

重演周一

[root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_23-06-28/

重演周三

[root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_23-06-28/ --incremental-dir /opt/diff/2023-10-07_23-12-29/
.
.
231007 23:40:37 [00]        ...done
231007 23:40:37 completed OK!

回滚

[root@localhost ~]# innobackupex --copy-back /opt/full/2023-10-07_23-06-28/
.

.
231007 23:41:07 [01]        ...done
231007 23:41:07 completed OK!

(4)修改权限

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/*

(5)重启服务

[root@localhost ~]# systemctl start mysqld