Day09-数据库服务备份恢复
Day-09-数据库服务数据备份恢复
- 1、数据库逻辑备份实践（mysqldump）
- 2、数据库逻辑备份参数（进阶参数）
- 2.1 --single-transaction
- 2.2 --master-data
- 2.3 -R -E --triggers
- 2.4 --max_allowed_packet
- 3、数据库逻辑备份案例
- 4、数据库逻辑备份痛点
- 5、数据库物理备份操作（克隆）
- 6、数据库服务克隆操作
1、数据库逻辑备份实践（mysqldump）
2、数据库逻辑备份参数（进阶参数）
3、数据库逻辑备份案例
4、数据库逻辑备份痛点
5、数据库物理备份操作 xbk
6、数据库服务克隆操作 8.0（云产品） RDS
7、数据库主从同步原理 数据库（单台）********
8、数据库主从同步构建
9、数据库主从同步查看
课程作业:
情况一：日志文件被清理过，可能建库语句所在日志已经丢失;**(在后面课程章节处理)
情况二：所需日志跨越多个文件，如何进行日志信息的截取；（gtid）
第一步操作：生成新的binlog日志
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| db01-binlog.000001 | 1236343 | No |
| db01-binlog.000002 | 179 | No |
| db01-binlog.000003 | 1212 | No |
| db01-binlog.000004 | 245 | No |
| db01-binlog.000005 | 196 | No |
+--------------------+-----------+-----------+
5 rows in set (0.00 sec)
第二步操作：进行事务操作
create database gtdb;
use gtdb;
create table t1(id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
# 进行binlog事件信息查看
mysql> show binlog events in 'db01-binlog.000005';
-- 可以获取以上的数据操作事件信息，
第三部操作：数据库误删除
drop database gtdb;
-- 模拟破坏性操作，删除数据库
第四步操作：获取需要保留的事务编号
mysql> show binlog events in 'db01-binlog.000005';
-- 保留事务编号（5-9）
第五步操作：利用binlog事务信息进行数据恢复
[root@db01 ~]# mysqlbinlog --include-gtids='774d2b7b-4f0a-11ef-97ee-000c29978564:5-9' /data/3306/logs/bin_log/db01-binlog.000005 >/tmp/gtid.sql
-- 以上备份数据信息存在幂等性问题
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='774d2b7b-4f0a-11ef-97ee-000c29978564:5-9' /data/3306/logs/bin_log/db01-binlog.000005 >/tmp/gtid02.sql
-- 临时跳过幂等性机制
mysql> set sql_log_bin=0;
-- 临时关闭binlog记录功能
mysql> source /tmp/gtid02.sql
# 操作扩展：可以实现排除指定gtid信息不做日志记录截取
[root@oldboyxiaoq ~]# mysqlbinlog --exclude-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:4' --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004
# 操作扩展：跨多日志文件信息截取
[root@oldboyxiaoq ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:1-10' /data/3306/data/binlog.000001 /data/3306/data/binlog.000002 /data/3306/data/binlog.000003 >/tmp/gtid.sql
- GTID概念介绍：
GTID（global transation id）称为全局事务（事件）ID，标识binlog日志记录的唯一性；
GTID信息的表示方式：
| 表现形式 | 关键列 | 解释说明 |
|---|---|---|
| server_uuid:N | server_uuid | 表示数据库初始化启动之后，自动生成的随机数信息（唯一的） |
| N | 表示第几个相关的事务或事件信息，会不断进行自增 |
- GTID功能作用：
利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；
利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；
简单描述：标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；
- GTID功能配置：
GTID功能相关参数介绍：
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF |
+-------------+
1 row in set (0.00 sec)
-- 设置是否开启显示gtid信息功能（在5.7之后是有个匿名的gtid，是数据库系统自己维护的）
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF |
+----------------------------+
1 row in set (0.00 sec)
-- 设置是否开启GTID强制一致性功能
-- 对某些 SQL 会有限制，例如 CREATE TABLE … SELECT 必须得分成两条语句执行。
-- OFF： 表示事务允许违反 GTID 一致性。
-- ON： 表示事务不允许违反 GTID 一致性，有相关 SQL 会直接返回异常。
-- WARN：表示事务允许违反 GTID 一致性，但会将警告信息记录到 ERROR LOG。
mysql> select @@log_slave_updates;
+---------------------+
| @@log_slave_updates |
+---------------------+
| 1 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
-- 确认是否开启从库也具有记录binlog日志功能
-- 和配置主从有关（在8.0.26开始 推荐配置log_replica_updates替代log_slave_updates参数）
-- 此参数表示从服务器从主服务器接收的更新信息，是否也会记录在从服务器本地的二进制文件中
GTID功能相关参数激活：
[root@xiaoq ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=on
-- 配置文件信息修改完毕后，重启数据库服务使配置生效
- GTID信息查看：
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
1 row in set (0.00 sec)
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| db01-binlog.000003 | 156 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create database test01;
Query OK, 1 row affected (0.06 sec)
mysql> use test01;
Database changed
mysql> create table t1 (name varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values('xiaoQ');
Query OK, 1 row affected (0.01 sec)
mysql> show master status;
+--------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+------------------------------------------+
| db01-binlog.000003 | 833 | | | 774d2b7b-4f0a-11ef-97ee-000c29978564:1-3 |
+--------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('xiaoA');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('xiaoB');
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+--------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+------------------------------------------+
| db01-binlog.000003 | 833 | | | 774d2b7b-4f0a-11ef-97ee-000c29978564:1-3 |
+--------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+--------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+------------------------------------------+
| db01-binlog.000003 | 1212 | | | 774d2b7b-4f0a-11ef-97ee-000c29978564:1-4 |
+--------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'db01-binlog.000003';
+--------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| db01-binlog.000003 | 4 | Format_desc | 3306 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| db01-binlog.000003 | 125 | Previous_gtids | 3306 | 156 | |
| db01-binlog.000003 | 156 | Gtid | 3306 | 233 | SET @@SESSION.GTID_NEXT= '774d2b7b-4f0a-11ef-97ee-000c29978564:1' |
| db01-binlog.000003 | 233 | Query | 3306 | 347 | create database test01 /* xid=5 */ |
| db01-binlog.000003 | 347 | Gtid | 3306 | 424 | SET @@SESSION.GTID_NEXT= '774d2b7b-4f0a-11ef-97ee-000c29978564:2' |
| db01-binlog.000003 | 424 | Query | 3306 | 550 | use `test01`; create table t1 (name varchar(10)) /* xid=12 */ |
| db01-binlog.000003 | 550 | Gtid | 3306 | 629 | SET @@SESSION.GTID_NEXT= '774d2b7b-4f0a-11ef-97ee-000c29978564:3' |
| db01-binlog.000003 | 629 | Query | 3306 | 706 | BEGIN |
| db01-binlog.000003 | 706 | Table_map | 3306 | 760 | table_id: 89 (test01.t1) |
| db01-binlog.000003 | 760 | Write_rows | 3306 | 802 | table_id: 89 flags: STMT_END_F |
| db01-binlog.000003 | 802 | Xid | 3306 | 833 | COMMIT /* xid=13 */ |
| db01-binlog.000003 | 833 | Gtid | 3306 | 912 | SET @@SESSION.GTID_NEXT= '774d2b7b-4f0a-11ef-97ee-000c29978564:4' |
| db01-binlog.000003 | 912 | Query | 3306 | 989 | BEGIN |
| db01-binlog.000003 | 989 | Table_map | 3306 | 1043 | table_id: 89 (test01.t1) |
| db01-binlog.000003 | 1043 | Write_rows | 3306 | 1085 | table_id: 89 flags: STMT_END_F |
| db01-binlog.000003 | 1085 | Table_map | 3306 | 1139 | table_id: 89 (test01.t1) |
| db01-binlog.000003 | 1139 | Write_rows | 3306 | 1181 | table_id: 89 flags: STMT_END_F |
| db01-binlog.000003 | 1181 | Xid | 3306 | 1212 | COMMIT /* xid=16 */ |
+--------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
18 rows in set (0.00 sec
情况三：如何从日志文件中恢复单库、单表、或者部分行数据信息；
A计划：单库日志信息截取，企业实战过程：
第一步操作：创建数据环境
create database test1;
use test1;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
commit;
select * from t1;
-- 创建test1数据库 库中创建t1数据表
create database test2;
use test2;
create table t2 (id int);
insert into t2 values(1);
insert into t2 values(2);
commit;
-- 创建了一个test2数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
use test1;
insert into t1 values(3);
insert into t1 values(4);
use test2;
insert into t2 values(3);
insert into t2 values(4);
commit;
select * from test1.t1;
select * from test2.t2;
-- 通过操作不同的数据库，以及不同的数据表，实现binlog日志事件信息的交叉
第二步操作：数据库模拟异常情况破坏操作：
mysql> drop database test1;
-- 模拟破坏性操作，删除数据库
第三步操作：截取日志信息（根据指定的数据库进行截取）
mysql> show master status;
mysql> show binlog events in 'db01-binlog.000006';
mysqlbinlog --skip-gtids --start-position=273 --stop-position=3123 -d test1 /data/3306/logs/bin_log/db01-binlog.000006 >/tmp/bin.sql
B计划：可以借助第三方工具实现单表或部分数据恢复；
第一个步骤：安装软件工具
[root@db01 ~]# cd /opt/
[root@db01 opt]# unzip binlog2sql-master.zip
[root@db01 opt]# cd binlog2sql-master/
[root@db01 binlog2sql-master]# ll
总用量 44
-rwxr-xr-x 1 root root 9155 2019-10-29 14:25 binlog2sql.py
-rwxr-xr-x 1 root root 11636 2019-10-29 14:25 binlog2sql_util.py
-rw-r--r-- 1 root root 10786 2019-10-29 14:25 binlog2sql_util.pyc
-rw-r--r-- 1 root root 92 2019-10-29 14:25 __init__.py
-rw-r--r-- 1 root root 62 2019-10-29 14:25 README.md
# 部署第三方工具运行环境
[root@xiaoq ~]# yum install -y python3
[root@xiaoq ~]# pip3 install -r requirements.txt
[root@xiaoq ~]# pip3 show pymysql
[root@xiaoq ~]# pip3 install --upgrade pymysql （此步骤可以忽略）
-- 以上pip3下载软件缓慢，可以优化pip3下载源
-- 下载源优化方法：https://developer.aliyun.com/mirror/pypi?spm=a2c6h.13651102.0.0.3e221b11H9Q7La
[root@db01 binlog2sql-master]# pip3 show pymysql
Name: PyMySQL
Version: 0.9.3
Summary: Pure Python MySQL Driver
Home-page: https://github.com/PyMySQL/PyMySQL/
Author: yutaka.matsubara
Author-email: yutaka.matsubara@gmail.com
License: "MIT"
Location: /usr/local/lib/python3.6/site-packages
Requires:
第二个步骤：创建测试数据信息
use test1;
create table t12 (id int);
insert into t12 values (1),(2);
commit;
第三个步骤：利用工具对binlog日志进行分析；
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='db01-binlog.000006'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 654 end 819 time 2024-08-29 16:36:19 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 929 end 1094 time 2024-08-29 16:36:21 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2133 end 2298 time 2024-08-29 16:38:42 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 2408 end 2573 time 2024-08-29 16:38:42 gtid
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t11 --start-file='db01-binlog.000006'
INSERT INTO `test1`.`t11`(`id`) VALUES (1); #start 3417 end 3588 time 2024-08-29 18:49:47 gtid
INSERT INTO `test1`.`t11`(`id`) VALUES (2); #start 3417 end 3588 time 2024-08-29 18:49:47 gtid
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t12 --start-file='db01-binlog.000006'
INSERT INTO `test1`.`t12`(`id`) VALUES (1); #start 3890 end 4061 time 2024-08-29 18:50:05 gtid
INSERT INTO `test1`.`t12`(`id`) VALUES (2); #start 3890 end 4061 time 2024-08-29 18:50:05 gtid
[root@db01 binlog2sql-master]#
第四个步骤：进行破坏操作
# 在指定数据库的相应数据表中做修改操作
use test1;
update t1 set id=10 where id=1;
commit;
-- 模拟误修改情况
# 在指定数据库的相应数据表中做删除操作
use test1;
delete from t1 where id=3;
commit;
-- 模拟误删除情况
第五个步骤：修复破坏的数据信息
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='db01-binlog.000006'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 654 end 819 time 2024-08-29 16:36:19 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 929 end 1094 time 2024-08-29 16:36:21 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2133 end 2298 time 2024-08-29 16:38:42 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 2408 end 2573 time 2024-08-29 16:38:42 gtid
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4355 end 4535 time 2024-08-29 18:57:00 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 4645 end 4810 time 2024-08-29 18:57:06 gtid
# 只想查看删除操作信息
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='db01-binlog.000006'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 4645 end 4810 time 2024-08-29 18:57:06 gtid
# 只想查看修改操作信息
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='db01-binlog.000006'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4355 end 4535 time 2024-08-29 18:57:00 gtid
# 只想查看插入操作信息
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='db01-binlog.000006'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 654 end 819 time 2024-08-29 16:36:19 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 929 end 1094 time 2024-08-29 16:36:21 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2133 end 2298 time 2024-08-29 16:38:42 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 2408 end 2573 time 2024-08-29 16:38:42 gtid
数据库日志信息工具回滚操作：（生成指定事件回滚语句-闪回操作）
假设在某个企业的应用场景中，有3000万行数据，占用200G的存储空间，其中误删除了10行数据信息，请问如何进行恢复数据？
# 误删除操作语句反转操作
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- 获取删除操作语句信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003' -B
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- 在获取删除操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
# 误修改操作语句反转操作
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
-- 获取修改操作语句信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003' -B
UPDATE `test1`.`t1` SET `id`=1 WHERE `id`=10 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
-- 在获取修改操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
# 误插入操作语句反转操作
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
-- 获取插入操作语句信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003' -B
DELETE FROM `test1`.`t1` WHERE `id`=4 LIMIT 1; #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
DELETE FROM `test1`.`t1` WHERE `id`=2 LIMIT 1; #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
DELETE FROM `test1`.`t1` WHERE `id`=1 LIMIT 1; #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
-- 在获取插入操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
1、数据库逻辑备份实践（mysqldump）
- 单表或多表数据备份：
# 单表数据备份
[root@db01 binlog2sql-master]# mysqldump -h 10.0.0.51 -uroot -p123456 -S /tmp/mysql3306.sock test1 t1 >/database_backup/t1_bak.sql # 备份
[root@db01 binlog2sql-master]# mysql -uroot -p123456 -h 10.0.0.51 test1 </database_backup/t1_bak.sql # 恢复
# 多表数据备份
[root@db01 binlog2sql-master]# mysqldump -h 10.0.0.51 -uroot -p123456 -S /tmp/mysql3306.sock test1 t1 t2 t3 >/database_backup/t1_t2_t3_bak.sql # 备份
[root@db01 binlog2sql-master]# mysql -uroot -p123456 -h 10.0.0.51 test1 </database_backup/t1_t2_t3_bak.sql # 恢复
2、数据库逻辑备份参数（进阶参数）
2.1 --single-transaction
这个参数的用法作用可以利用一个形象的例子去理解：比如在某个时刻班主任希望统计班级同学的数量情况，那么该如何统计准确呢？
方法一：
形象说明：锁门封闭统计，禁止人员在教室内外随意走动，取班级人数变化的静止状态的学生数量；
真实应用：锁表封闭备份，禁止数据库程序进行数据更新操作，实现静止锁表状态进行数据备份；（一般选择半夜操作）
方法二：
形象说明：瞬时拍照统计，允许人员在教室内外随意走动，但是会根据拍照时刻人员数量进行统计；
真实应用：瞬时节点备份，允许数据库程序进行数据更新操作，只把备份操作瞬间已有数据备份；
因此，利用–single-transaction参数进行数据备份，就等价于在备份的时候给数据库的数据拍了照，备份时候数据库可以继续更新；
命令参数官方信息详细解读：
- 对于InnoDB存储引擎的表，将会利用MVCC中的一致性快照进行备份；
- 在备份数据期间不要出现DDL操作语句信息，如果出现DDL操作语句，将会导致备份数据不一致；
--single-transaction
Creates a consistent snapshot by dumping all tables in a single transaction.
通过在单个事务中备份所有表时，会创建一致性快照
Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does);
仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB）
对于InnoDB，会利用MVCC中一致性快照进行备份；
the dump is NOT guaranteed to be consistent for other storage engines.
这种方式的备份不能保证与其他存储引擎一致
While a --single-transaction dump is in process, to ensure a valid dump file
当--single-transaction参数应用在备份进程中时，确保备份文件的有效性
(correct table contents and binary log position),
含有正确的表内容和binlog日志位置点
no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them.
在进行备份数据期间，不要出现相关DDL的操作信息，导致备份数据不一致；
Option automatically turns off --lock-tables.
2.2 --master-data
备份数据后，会在备份后的文件中记录位置点信息
数据备份痛点：在进行数据库全备+binlog恢复数据时，如何进行binlog的临界点（起点）截取操作？
在备份数据的时候会记录binlog日志位置点到备份文件中，这个位置点是上一次全备之后新增数据的临界点；
在未来数据库服务出现异常时，会先恢复全备的数据信息，然后恢复binlog日志临界点之后的数据信息；
在指定日志位置点进行备份的时候，生成的操作日志语句如下：
命令参数官方信息详细解读：
- 利用此参数功能，可以实现自动记录位置点信息；
- 利用此参数功能，可以实现自动添加全局读锁（GRL）功能（在配合–single-transaction参数使用时，可以减少锁时间）；
--master-data[=#]
This option is deprecated and will be removed in a future version. Use source-data instead.
此选项已弃用，将在以后数据库服务的版本中删除，请使用source-data代替此参数使用；
--source-data[=#]
This causes the binary log position and filename to be appended to the output.
这个参数会导致binlog日志位置点信息和文件名信息会附加到输出中，即附件到备份文件中。
If equal to 1, will print it as a CHANGE MASTER command;
如果数值等于1，将输出显示change master的命令信息；
if equal to 2, that command will be prefixed with a comment symbol.
如果数值等于2，该命令将以注释符号作为前缀
This option will turn --lock-all-tables on, unless --single-transaction is specified too
这个参数在使用时，将会自动开启--lock-all-tables参数功能，除非也指定了--single-transaction参数信息；
(in which case a global read lock is only taken a short time at the beginning of the dump;
在这种情况下，全局读锁只在备份开始时占用很短的时间
don't forget to read about --single-transaction below).
不要忘记阅读一下--single-transaction参数功能说明
In all cases, any action on logs will happen at the exact moment of the dump.
在所有情况下，日志上的任何操作都将在备份的确切时刻发生
Option automatically turns --lock-tables off.
参数将自动关闭 --lock-tables参数功能
备份数据进阶方式实践：
# 进阶方式数据备份（不压缩备份）
[root@db01 binlog2sql-master]# mysqldump -uroot -p123456 -h 10.0.0.51 -S /tmp/mysql3306.sock --master-data=2 --single-transaction -A >/database_backup/xiaoQ-01.sql
-- -B 表示在备份时添加use语句信息
# 进阶方式数据备份（压缩备份）
[root@xiaoQ ~]# mysqldump -uroot -poldboy123 --master-data=2 --single-transaction -A -B|gzip >/tmp/bak.sql.gz
[root@xiaoQ ~]# gzip -d /tmp/bak.sql.gz
-- 压缩数据解压命令
2.3 -R -E --triggers
- R： 表示进行数据库存储过程备份
存储过程=用SQL语句写成的脚本代码
- E： 表示进行数据库事件信息备份
--triggers：备份数据库触发器信息
mysqldump -uroot -poldboy123 -B mdb --master-data=2 --single-transaction -R -E --triggers >/databases_backup/oldboy_`date +%F`.sql
2.4 --max_allowed_packet
此参数表示允许进行传输的数据包大小，在某些时候如果备份的数据为大表数据，需要调整此参数信息；
如果没有正确的设置此参数信息，可能会导致备份大表数据时，会出现数据备份失败的情况；
# 数据库数据信息备份过程（全备）
[root@xiaoQ-01 ~]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/database_backup/full_`date +%F`
[root@xiaoQ-01 ~]# ll /database_backup/
-rw-r--r-- 1 root root 51254551 11月 26 00:47 full_2022-11-26
# 数据库数据备份信息查看
[root@xiaoQ-01 ~]# vim /database_backup/full_2022-11-26.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '9d14be39-6423-11ed-bb21-000c2996c4f5:1-6';
-- 表示在进行数据恢复操作时，会将gtid1-6的事件信息删除掉，因为在之前备份数据中已经有了1-6的事件数据信息；
-- 因此，从GTID的编号来看，可以从编号7事件开始进行数据增量恢复；
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=1312;
-- 输出信息表示增量数据的临界点在binlog.000013日志文件的1312位置，同时是备份结束时的位置点；
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 67108864 |
+----------------------+
1 row in set (0.00 sec)
3、数据库逻辑备份案例
第一步：模拟时间-某周周一~周二，正常网站用户访问网站进行数据库信息录入
mysql> flush logs;
-- 将binlog日志文件进行刷新，创建一个新的日志文件
mysql> create database mdb;
mysql> use mdb;
-- 模拟创建用户存储数据的数据库信息
mysql> create table t1 (id int);
mysql> create table t2 (id int);
-- 模拟创建用户存储数据的数据表信息
begin;
mysql> insert into t1 values(1),(2),(3);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
-- 模拟用户向数据表中添加新的数据
mysql> select * from t1;
mysql> select * from t2;
-- 检查用户创建的数据信息是否生成
第二步：模拟时间-某周周二晚零点，企业数据库管理员进行一次数据库服务数据全备操作
[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql3306.sock -h 10.0.0.51 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/database_backup/full_`date +%F`.sql
第三步：模拟时间-某周周二晚零点之后，模拟用户继续访问网站业务产生了增量的数据信息
mysql> use mdb;
begin;
mysql> create table t3 (id int);
mysql> insert into t3 values(1),(2),(3);
mysql> insert into t2 values(4),(5),(6);
mysql> commit;
第四步：模拟时间-某周周三下午14点，模拟系统相关技术人员误删除了数据库，并且已紧急跑路
drop database mdb;
第五步：修复操作-查看找寻数据库服务全备数据，并进行全备数据恢复
mysql> set sql_log_bin=0;
mysql> source /database_backup/full_2024-08-30.sql
第六步：获取增量起始点，截取binlog日志
[root@db01 ~]# vim /database_backup/full_2024-08-30.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='db01-binlog.000001', MASTER_LOG_POS=1090;
[root@db01 ~]# mysqlbinlog --help|grep start
--start-datetime=name
-j, --start-position=#
start-datetime (No default value)
start-position 4
[root@db01 ~]# mysqlbinlog --help|grep stop
--stop-datetime=name
--stop-never Wait for more data from the server instead of stopping at
but instead of stopping at the end of the last log it
--stop-never-slave-server-id=#
--stop-never. This option is deprecated and will be
stop-never-slave-server-id.
--stop-position=# Stop reading the binlog at position N. Applies to the
-t, --to-last-log Requires -R. Will not stop at the end of the requested
stop-datetime (No default value)
stop-never FALSE
stop-never-slave-server-id -1
stop-position 18446744073709551615
[root@db01 bin_log]# mysqlbinlog --start-position=1090 --stop-position=1839 /data/3306/logs/bin_log/db01-binlog.000001 >/tmp/add.sql
第七步：恢复增量数据
mysql> source /tmp/add.sql;
第八步：将之后的数据进行追加
mysqlbinlog --start-position=2017 /data/3306/logs/bin_log/db01-binlog.000001 >/tmp/add02.sql
source /tmp/add02.sql;
-- 以上操作可以在从库或备库进行，切换业务到从库或备库
说明：数据库数据修复复原完毕后，别忘让开发人员或测试人员进行业务功能测试，最终让运维人员恢复业务上线。
4、数据库逻辑备份痛点
大的数据库有小的数据损坏，尽量用增量数据修复故障数据，避免采用全备恢复数据；
假设某个企业进行数据库服务的数据备份，将会采用数据库全备方案，每次全备会生成大约50G的数据信息；
并且每次数据库服务进行全备耗时大约1530分钟，因此如果有需要进行数据恢复时，耗费时间大约35小时左右（备份时间的3-5倍）；
但是，在实际生产环境中，只是误删除（误修改）了一个10M大小的数据表，如何进行部分数据信息的快速恢复；
说明：此时需要实现部分单表数据信息恢复时，在实际企业生产环境中，并没有做指定的单表数据信息备份操作；
痛点解决思路：
只能通过已有的全备数据信息，配合已有binlog日志信息，进行指定表数据信息的恢复操作；
- 基于全备数据信息，可以将指定数据表的建表语句和插入语句提取出来，进行单表数据信息恢复（恢复全备前的数据）；
- 基于增量日志信息，可以将指定数据表的所有相关事件信息进行截取，进行单表数据信息增量恢复；
处理方法参考：
- 基于全备数据信息，获取指定数据表的建表语句和插入语句信息：
[root@xiaoQ ~]# sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `xiaoQ`/!d;q' /database_backup/full.sql > /database_backup/createtable.sql
-- 获取指定表的建表语句信息；
[root@xiaoQ ~]# grep -i 'INSERT INTO `xiaoQ`' /database_backup/full.sql > /database_backup/data_insert.sql
-- 获取指定表的插入语句信息；
[root@xiaoQ ~]# grep -i 'UPDATE `xiaoQ`' /database_backup/full.sql > /database_backup/data_delete.sql
-- 获取指定表的修改语句信息；
[root@xiaoQ ~]# grep -i 'DELETE FROM `xiaoQ`' /database_backup/full.sql > /database_backup/data_delete.sql
-- 获取指定表的删除语句信息；
- 基于增量日志信息，获取指定数据表的增量变化的日志数据信息：
[root@xiaoQ ~]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d 数据库 -t 数据表 --start-file='binlog.00000N'
-- binlog2sql 截取指定单表的binlog数据，进行恢复/分析操作
5、数据库物理备份操作（克隆）
在数据库服务运行使用过程中，除了上面介绍的逻辑备份数据方法，还可以采用物理方式备份数据信息；
物理备份数据方式又可以细分为冷备份和热备份两种，和逻辑备份相比，它的最大优点是备份和恢复的速度更快；
说明：因为物理备份的原理都是基于文件的cp。
02 物理热备份
在MySQL中，对于不同的存储引擎热备份方法也有所不同，下面主要介绍MyISAM和InnoDB两种最常用的存储引擎的热备份方法；
- 数据库存储引擎应用：MyISAM
MyISAM存储引擎的热备份有很多方法，本质其实就是将要备份的表加读锁，然后再cp数据文件到备份目录：
方法一：使用mysqlhotcopy工具
mysqlhotcopy是MySQL自带的一个热备份工具，使用方法很简单：
[root@xiaoQ ~]# mysqlhotcopy db_name [/path/to/new_directory]
-- mysqlhotcopy有很多选项，具体可以使用--help查看帮助信息；
参考官方链接说明：https://dev.mysql.com/doc/refman/5.6/en/mysqlhotcopy.html
- 数据库存储引擎应用：InnoDB
ibbackup是Innobase公司的一个热备份工具，专门对InnoDB存储引擎进行物理热备份，此工具是收费的，但可以免费使用1个月；
Innobase公司已经于2005年被Oracle公司所收购。
使用ibbackup备份工具的备份步骤简要说明：
01 编辑用于启动的配置文件my.cnf和用于备份的配置backup-my.cnf
# 配置文件配置参考：my.cnf
[mysqld]
datadir=/data/3306/data
innodb_data_home_dir=/data/3306/data
innodb_data_file_path=ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
innodb_log_group_home_dir=/data/3306/data
set-variable=innodb_log_files_in_group=2
set-variable=innodb_log_file_size=20M
# 配置文件配置参考：backup-my.cnf
[mysqld]
datadir=/data/3306/backup
innodb_data_home_dir=/data/3306/backup
innodb_data_file_path=ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
innodb_log_group_home_dir=/data/3306/backup
set-variable=innodb_log_files_in_group=2
set-variable=innodb_log_file_size=20M
02 实现数据文件信息热备过程：
[root@xiaoQ ~]# ibbackup /data/3306/my.cnf /data/3306/backup-my.cnf
... 省略部分信息...
-- ibbackup工具不会覆盖任何重名的文件，因此在新的备份开始之前，需要确保备份目录中没有重名文件，否则备份可能会失败
[root@xiaoQ ~]# ll /data/3306/backup
-- 备份成功后，备份目录下包含有数据文件和日志文件等相关数据信息；
Xtrabackup（PXB）是Percona公司CTO Vadim参与开发的一款基于InnoDB的在线热备工具，属于物理备份数据工具；
具有开源、免费、支持在线热备、备份恢复速度快、占用磁盘空间小等特点，并且支持不同情况下的多种备份形式。
官方软件下载链接：https://www.percona.com/downloads/
对于数据库8.0.20版本，需要使用PXB 8.0.12+以上版本，对于数据库8.0.11~8.0.19，可以使用PXB 8.0正式版本；
PXB 8.0只能备份MySQL 8.0版本数据，不能备份低版本数据信息；如果想备份数据库服务低版本程序数据，需要下载使用PXB 2.4版本；
xtrabackup包含两个主要的工具：xtrabackup和innobackupex
- xtrabackup 只能备份InnoDB和XtraDB两种类型的数据表，而不能备份MyISAM类型数据表；
- innobackupex 是一个封装了xtrabackup的perl脚本，支持同时备份InnoDB和MyISAM，但对MyISAM备份时需要加全局读锁；
由于PXB属于第三方软件工具程序，因此需要进行单独下载安装：
安装软件程序
# 进行软件程序上传
[root@db01 bin_log]# cd /usr/local/
[root@db01 local]# rz -E
rz waiting to receive.
[root@db01 local]# ll percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 13097340 2024-08-30 11:12 percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm
# 进行软件程序安装
[root@db01 local]# yum install -y percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm
-- 利用yum方式安装本地的rpm包程序，可以有效解决软件依赖的问题；
Xtrabackup（PXB）属于物理备份工具（针对数据文件进行备份），具体备份逻辑如下：（支持增量备份数据）
- 在数据库服务运行期间，通过拷贝数据文件（实质拷贝的是数据页），进而实现数据备份目的；
- 在进行数据文件拷贝的同时，会将备份期间的变化redo日志信息同时进行备份（拷贝）；
Xtrabackup（PXB）属于物理备份工具（针对数据文件进行备份），具体恢复逻辑如下：
- 在进行数据恢复时，模拟了InnoDB Crash recovery（CR）的运行过程，需要将备份进行处理，才能进行数据恢复；
- 在进行数据恢复时，对于备份进行处理操作，特指的就是前滚操作（redo）和回滚操作（undo），从而解决数据恢复一致性问题；
Xtrabackup数据备份方式：实现全量备份
第一个步骤：进行数据全备
mkdir /data/backup/full -p
xtrabackup --defaults-file=/etc/my80.cnf --host=10.0.0.51 --user=root --password=123456 --port=3306 --backup --target-dir=/data/backup/full
Xtrabackup数据恢复方式：全量备份恢复
[root@db01 bin_log]# systemctl stop mysqld
[root@db01 bin_log]# ll /data/3306/
总用量 8
drwxr-xr-x 18 mysql mysql 4096 2024-08-30 17:20 data
drwxr-xr-x 5 mysql mysql 57 2024-08-23 13:54 logs
[root@db01 bin_log]# rm -rf /data/3306/*
[root@db01 bin_log]# ll /data/3306/
总用量 0
进行数据库数据恢复的操作过程：
[root@xiaoQ ~]# xtrabackup --prepare --target-dir=/data/backup/full
...忽略部分信息...
Shutdown completed; log sequence number 19214860
221127 16:31:58 completed OK!
-- 表示模拟CR过程，将redo日志进行前滚，undo日志进行回滚，让恢复数据信息保持一致性状态
[root@xiaoQ ~]# xtrabackup --copy-back --target-dir=/data/backup/full
-- 将进行物理备份后的数据，再次进行还原恢复到备份前的目录中（拷贝回数据）
[root@xiaoQ ~]# chown -R mysql.mysql /data/*
[root@xiaoQ ~]# /etc/init.d/mysqld start
-- 重新设置数据目录权限，并重新启动恢复数据库业务
Xtrabackup数据备份方式：实现增量备份
mkdir /data/backup/full -p
mkdir /data/backup/inc -p
# 进行全备操作：
[root@xiaoQ-01 ~]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.30.101 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/full
-- 进行全量备份操作，并且开启并发线程备份功能（--parallel=4），从而提高备份效率（建议4~8个）
# 增量备份：
mysql> create database pxb;
mysql> use pxb
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
-- 模拟登陆数据库，进行相关操作，实现增量备份的效果
# 第一次增量
xtrabackup --defaults-file=/etc/my80.cnf --host=10.0.0.51 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full
-- 进行增量备份操作
#第二次增量
# 可以进行继续备份操作（了解）
[root@xiaoQ-01 ~]# mkdir /data/backup/inc02 -p
-- 提前准备好增量备份的增量备份目录；
mysql> create database pxb02;
mysql> use pxb02
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
-- 模拟登陆数据库，进行相关操作，实现增量备份的效果
[root@xiaoQ-01 ~]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.30.101 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/inc02 --incremental-basedir=/data/backup/inc
-- 进行增量备份操作的下一次增量备份
Xtrabackup数据备份方式：实现增量恢复
https://blog.51cto.com/u_15080021/2642159
# 准备相关备份日志信息
[root@xiaoQ ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
-- 准备全量备份的日志；
[root@xiaoQ ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full --incremental-dir=/data/backup/inc
-- 准备增量备份的日志，并且将增量备份合并到全量备份中；
-- apply-log-only跳过消除（undo）阶段，只执行重做（redo）阶段，从而实现增量备份恢复
[root@xiaoQ ~]# xtrabackup --prepare --target-dir=/data/backup/full
-- 在全量和增量数据合并后，在整体做日志信息的准备；
# 进行数据备份拷回操作
[root@xiaoQ ~]# xtrabackup --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full
或者
[root@xiaoQ ~]# xtrabackup --copy-back --target-dir=/data/backup/full
-- 将进行物理备份后的数据，再次进行还原恢复到备份前的目录中（拷贝回数据）
# 重新启动恢复业务功能
[root@xiaoQ ~]# chown -R mysql.mysql /data/*
[root@xiaoQ ~]# /etc/init.d/mysqld start
-- 重新设置数据目录权限，并重新启动恢复数据库业务
6、数据库服务克隆操作
作用：
1）在实现大的数据迁移时，克隆效率最高
2）实现云主机数据资源，迁移到物理主机
克隆方式：
本地克隆：数据备份
远程克隆：数据迁移