2023.6.16 10.数据库备份恢复

10.数据库备份恢复
1.MySQL逻辑备份与恢复
1.1数据库完整备份与恢复
1.2数据库增量备份与恢复
2.MySQL物理备份与恢复
2.1数据库完整备份与恢复
2.2数据库增量备份与恢复
2.3数据库差异备份与恢复
3.简单命令进⾏物理备份
4.⽣产备份思路与实战
 
通常数据库备份 database 数据⽂件 binlog ⽇志⽂件 my.cnf 配置⽂件,所有的数据库的备份数据都应放在⾮数据
库本地, ⽽且建议备份多份。
仅备份是没有任何意义, 需要在测试环境中做⽇常恢复演练, 测试备份的可⽤性, 恢复较⽐备份更加的重要。
备份: 能够有效防⽌设备故障以及⼈为误操作带来的数据丢失, 例如:将数据⽂件保存在远端。
冗余: 数据有多分冗余, 但不等于备份, 只能防⽌机械故障丢失的数据, 例如: 主备模式、数据库集群。
数据库备份必须考虑因素
1.数据的⼀致性
2.服务的可⽤性
数据库备份⽅式
1.逻辑备份: 备份 DDL DML DCL 语句, 适⽤于中⼩型数据库, 效率相对低下。 mysqldump、mydumper
2.物理备份: 直接复制数据库⽂件, 适⽤于⼤型数据库环境, 效率相对较⾼。 xtrabackup、inbackup、cp、
tar、lvm snapshot
在选择备份⽅式时，需要考虑数据库的规模、备份和恢复的效率、备份数据的⼀致性等因素。同时，为了保证备份
数据的安全性，备份数据应该保存在⾮本地的地⽅，并且建议备份多份。
数据库备份模式
完全备份
增量备份
差异备份
冷备份与热备份
冷备份
关闭数据库服务，完整拷⻉数据⽂件
热备份
在不影响数据库读写服务的情况下备份数据库
备份周期
考虑因素：
数据库⼤⼩(决定备份时间)
恢复速度要求(快速or慢速)
备份⽅式(全量or增量)
企业备份机制
如果你的数据库⼩于20G 每天公司0点进⾏全量备份 逻辑备份 物理备份可以的
如果数据库⼤于100G，周⼀晚上0点进⾏物理全备份，周⼆ 增量备份,周三 以此类推，
1.MySQL逻辑备份与恢复
mysql⾃带逻辑备份⼯具 mysqldump , 可以保证数据备份⼀致性, 以及服务可⽤性
1.1数据库完整备份与恢复
mysqldump命令使⽤⽅式
默认情况下， mysqldump 命令会锁定整个数据库，备份导出数据时会阻塞所有对数据库的写⼊操作，这对于⽣产
环境的数据库来说是不可接受的。使⽤ single-transaction 参数后， mysqldump 命令会在⼀个事务中导出数
据，这样可以保证导出的数据是⼀致的，⽽且不会阻塞数据库的写⼊操作。
single-transaction 参数的使⽤条件如下：
1. 数据库引擎必须⽀持事务，例如 InnoDB 引擎。
2. 导出的表必须使⽤事务，例如表类型为 InnoDB。
注意，使⽤ single-transaction 参数仅适⽤于导出数据，不适⽤于导⼊数据。如果需要导⼊数据，可以将导出
的 SQL ⽂件直接导⼊到 MySQL 数据库中。
注意: 不管物理备份还是逻辑备份, 必须开启 binlog ⽇志
>mysqldump -h 服务器 -u ⽤户名 -p 密码 数据库名 > 备份⽂件.sql
//常⽤参数
-A, --all-databases #备份所有库
-B, --databases #备份多个库多个数据库
--single-transaction#InnoDB ⼀致性 服务可⽤性
--master-data=1|2 #记录 binlog⽇志位置与⽂件名,追加⾄备份⽂件中
--triggers #备份触发器
-F, --flush-logs #备份之前刷新⽇志
-E, --events #备份事件调度器代码
-R, --routines #备份存储过程和存储函数
默认情况下， mysqldump 命令会锁定整个数据库，备份导出数据时会阻塞所有对数据库的写⼊操作，这对于⽣产
环境的数据库来说是不可接受的。使⽤ single-transaction 参数后， mysqldump 命令会在⼀个事务中导出数
据，这样可以保证导出的数据是⼀致的，⽽且不会阻塞数据库的写⼊操作。
single-transaction 参数的使⽤条件如下：
1. 数据库引擎必须⽀持事务，例如 InnoDB 引擎。
2. 导出的表必须使⽤事务，例如表类型为 InnoDB。
注意，使⽤ single-transaction 参数仅适⽤于导出数据，不适⽤于导⼊数据。如果需要导⼊数据，可以将导出
的 SQL ⽂件直接导⼊到 MySQL 数据库中。
注意: 不管物理备份还是逻辑备份, 必须开启 binlog ⽇志
 
1.使⽤ mysqldump 完整备份
//本地备份
[root@sql ~]# mkdir /backup/mysql -p
[root@sql ~]# mysqldump -uroot -p'Wing@123' -h 10.1.106.70 \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql
 
2.查看 binlog ⽇志信息
[root@sql ~]# sed -n "22p" /backup/mysql/`date +%F%H`-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000003', MASTER_LOG_POS=154;
 
数据库完整恢复流程
1.停⽌数据库
2.删除破损数据库
3.重新初始化数据库
4.重置密码,否则⽆法恢复
5.恢复数据[新密码]
6.刷新授权[备份时密码]
 
yum安装数据库操作
#1.模拟数据奔溃
[root@sql ~]# systemctl stop mysqld
[root@sql ~]# rm -rf /var/lib/mysql
#2.2.重新初始化数据库,启动后修改密码
[root@sql ~]# systemctl start mysqld
[root@sql ~]# grep "password" /var/log/mysqld.log
khy_=i512g=F
[root@sql ~]# mysqladmin -uroot -p'khy_=i512g=F' password "Wing@123"
 
 
⼆进制安装操作
#1.模拟数据奔溃
[root@sql ~]#/etc/init.d/mysqld stop
[root@sql ~]# rm -rf /soft/mysql-5.7.30-linux-glibc2.12-x86_64/
[root@sql ~]# rm -rf /soft/mysql
#2.基础环境准备
[root@sql ~]# systemctl stop firewalld
[root@sql ~]# systemctl disable firewalld
[root@sql ~]# setenforce 0
 
//建⽴⽤户与相应⽬录
[root@sql ~]# groupadd mysql
[root@sql ~]# useradd -r -g mysql -s /sbin/nologin mysql
[root@sql ~]# mkdir /soft/src -p && cd /soft/src
#3.下载`MySQL`并安装
[root@sql src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux
glibc2.12-x86_64.tar.gz
[root@sql src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /soft
[root@sql src]# ln -s /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ /soft/mysql
#4.进⾏`MySQL`初始化
//创建初始化⽬录以及数据库数据⽬录
[root@sql ~]# mkdir /soft/mysql/{mysql-file,data}
[root@sql ~]# chown mysql.mysql /soft/mysql/
//初始化数据库
[root@sql ~]# /soft/mysql/bin/mysqld --initialize \
--user=mysql --basedir=/soft/mysql \
--datadir=/soft/mysql/data
-------
//初始化数据库会告诉默认登陆账户与密码
[root@sql ~]# egrep 'temporary password is generated' /log/mysql/err.log
2023-05-21T08:17:39.165590Z 1 [Note] A temporary password is generated for
root@localhost: 6N;u,POFLd8I
//使⽤ssl连接, 初始化后重新授权⽬录权限[如不需要可忽略]
[root@sql ~]# /soft/mysql/bin/mysql_ssl_rsa_setup \
--datadir=/soft/mysql/data/
[root@sql ~]# chown -R mysql.mysql /soft/mysql/
#使⽤(systemV)⽅式管理, [强烈推荐]
[root@sql ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@sql ~]# chkconfig --add mysqld
[root@sql ~]# chkconfig mysqld on
//启动数据库
[root@sql ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
//默认情况没有mysql命令, 如果有可能使⽤过yum安装, 这样容易连错数据库(PATH路径存在命令执⾏优先级问题)
[root@sql ~]# mysql
-bash: mysql: command not found
//可以选择添加路径⾄PATH中, 或者直接使⽤绝对路径执⾏
[root@sql ~]# echo "export PATH=$PATH:/soft/mysql/bin" >> /etc/profile
[root@sql ~]# source /etc/profile
//登陆数据库
[root@sql ~]# mysql -uroot -p'6N;u,POFLd8I' -h 127.0.0.1
//默认系统配置数据库密码必须修改, 否则⽆法使⽤数据库
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before
executing this statement.
//修改系统默认密码
mysql> alter user root@'localhost' identified by 'Wing@123';
Query OK, 0 rows affected (0.01 sec)
mysql> exit;
//退出后使⽤新密码重新登录数据库
[root@sql soft]# mysql -uroot -p"Wing@123" -e ' show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
 
 
3.恢复导⼊备份数据
[root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2816-mysql-all.sql
//使⽤恢复后的密码进⾏登录mysql
[root@sql ~]# mysql -uroot -p"Wing@123" -e ' show databases;'
mysql>
 
4.建议在恢复备份时暂停 binlog ⽇志记录
//1.关闭binlog记录, 不然容易恢复增量失败
[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql
all.sql
//2.在mysql控制台临时修改不记录binlog⽇志
mysql> set sql_log_bin=0;
mysql> source /backup/mysql/2023-05-2816-mysql-all.sql
//3. binlog打开
mysql> set sql_log_bin=1;
 
1.2数据库增量备份与恢复
实战案例1: 数据库完整备份+数据库增量备份
新建数据表, 进⾏了全量备份, 随着时间推移, 数据库突然奔溃
1.环境准备
//备份之前
mysql> set sql_log_bin=0;
mysql> drop database wing;
mysql> set sql_log_bin=1;
mysql> create database wing;
mysql> create table wing.t1 (id int, name varchar(20));
mysql> insert into wing.t1 values (1,"wing1");
mysql> insert into wing.t1 values (2,"wing2");
mysql> select * from wing.t1;
+------+------+
| id | name |
+------+------+
| 1 | wing1 |
| 2 | wing2 |
+------+------+
2 rows in set (0.00 sec)
//基于当前状态备份
[root@sql ~]# mysqldump -uroot -p'Wing@123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql
//模拟数据插⼊操作
mysql> insert into wing.t1 values (3,"wing3");
mysql> insert into wing.t1 values (5,"tt");
mysql> select * from wing.t1;
+------+------+
| id | name |
+------+------+
| 1 | wing1 |
| 2 | wing2 |
| 3 | wing3 |
| 5 | tt |
+------+------+
4 rows in set (0.00 sec)
//模拟数据库故障
[root@sql ~]# /etc/init.d/mysqld stop
[root@sql ~]# rm -rf /sort/mysql
 
//重新启动会⾃动初始化操作
[root@sql ~]# systemctl start mysqld
//注意修改默认密码
[root@sql ~]# mysqladmin -uroot -p'RA+:>Xu&.6K_' password "Wing@123"
 
2.恢复全备数据
//关闭binlog记录, 不然容易恢复增量失败
[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2716-mysql
all.sql
[root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2716-mysql-all.sql
[root@sql mysql]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;"
+------+------+
| id | name |
+------+------+
| 1 | wing1 |
| 2 | wing2 |
+------+------+
 
3.恢复增量数据
//查看备份后binlog起始位置点
[root@sql ~]# sed -n '22p' /backup/mysql/2023-05-2816-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000014', MASTER_LOG_POS=154;
//查找结束位置点, 进⾏恢复
[root@sql bin]# mysqlbinlog --start-position=154 sql_106_70.000014|mysql -uroot -
p'Wing@123'
//验证增量恢复数据
[root@sql bin]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;"
+------+-------+
| id | name |
+------+-------+
| 1 | wing1 |
| 2 | wing2 |
| 3 | wing3 |
| 5 | tt |
+------+-------+
 
1.3实战案例2: 数据库完整备份+数据库增量备份
例⼦：现在有⼀个运维⼈员误删除了数据库，或者是某⼀张表
1.删除所有数据库
2.删除所有binlog⽇志
3.重启mysqld服务
1.模拟环境准备
2.使⽤ mysqldump 进⾏全备
3.再次插⼊⼀些数据
mysql> create database wingdb;
mysql> use wingdb;
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,"wing");
mysql> insert into t1 values (2,"wing1");
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | wing |
| 2 | wing1 |
+------+-------+
2 rows in set (0.00 sec)
 
2.使⽤ mysqldump 进⾏全备
[root@sql ~]# mysqldump -uroot -p'Wing@123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql
 
3.再次插⼊⼀些数据
mysql> insert into t1 values
(3,'trl'),
(4,'zx'),
(5,'wq'),
(6,'tj'),
(7,'gwt');
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | wing |
| 2 | wing1 |
| 3 | trl |
| 4 | zx |
| 5 | wq |
| 6 | tj |
| 7 | gwt |
+------+-------+
7 rows in set (0.00 sec)
 
3.模拟故障
mysql> delete from t1 where id ='2';
mysql> drop database wingdb;
 
4.恢复全量备份的数据
[root@sql ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql-all.sql
[root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2816-mysql-all.sql
[root@sql ~]# mysql -uroot -p'Wing@123' -e "select * from wingdb.t1;"
+------+-------+
| id | name |
+------+-------+
| 1 | wing |
| 2 | wing1 |
+------+-------+
 
5.通过 mysqlbinlog 命令导出关于数据库 wingdb 的 sql 语句
//查看备份后binlog起始位置点
[root@sql ~]# sed -n '22p' /backup/mysql/2023-05-2816-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000015', MASTER_LOG_POS=154;
//针对全量备份后仅产⽣1个binlog⽂件的⽅式
[root@sql bin]# mysqlbinlog --start-position=154 --base64-output="decode-rows" -v
sql_106_70.000015 > db.sql
全量备份到出问题只产⽣了⼀个 sql_106_70.000017 ⽂件，
如果产⽣了多个binlog⽇志, 可是使⽤下⾯⽅法恢复
mysqlbinlog --base64-output="decode-rows" -v sql_106_70.000017 >> 1.sql
 
6.查看 sql ⽂件，找到误操作语句对应的位置,然后记录下来, 随后恢复时跳过即可
[root@sql bin]# vim db.sql
# at 640
#230527 16:44:22 server id 70 end_log_pos 686 CRC32 0x5230fb50 Delete_rows:
table id 176 flags: STMT_END_F
### DELETE FROM `wingdb`.`t1`
### WHERE
 
### @1=2
### @2='wing1'
# at 717
#230527 16:44:25 server id 70 end_log_pos 782 CRC32 0x6fad23ad Anonymous_GTID
last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 782
#230527 16:44:25 server id 70 end_log_pos 880 CRC32 0x4c5bd31f Query
thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1685177065/*!*/;
drop database wingdb
 
7.使⽤ mysqlbinlog 进⾏恢复跳过 drop 与 delete 语句
[root@sql bin]# mysqlbinlog --start-position=154 --stop-position=640 sql_106_70.000015
|mysql -uroot -p'Wing@123'
[root@sql bin]# mysql -uroot -p'Wing@123' -e "select * from wingdb.t1;"
+------+-------+
| id | name |
+------+-------+
| 1 | wing |
| 2 | wing1 |
| 3 | trl |
| 4 | zx |
| 5 | wq |
| 6 | tj |
| 7 | gwt |
+------+-------+
 
2.MySQL物理备份与恢复
2.1 xtrabackup 物理备份
特点：
开源，在线备份InnoDB表
⽀持限速备份，避免对业务造成影响
⽀持流备
⽀持增量备份
⽀持备份⽂件压缩与加密
⽀持并⾏备份与恢复，速度快
 
 
2.2 mysqldump 和 xtrabackup
mysqldump不同，xtrabackup是⼀种物理备份⼯具，但是，它是需要通过协议连接到mysql服务端（这⼀点与
mysqldump相同，它们都是客户端⼯具，都需要连接到服务端），然后读取并复制innodb底层的”数据块”，完成
所谓的”物理备份”。
 
2.3 xtrabackup备份原理
基于InnoDB的crash-recovery功能
备份期间允许⽤户读写，写请求产⽣redo⽇志
从磁盘上拷⻉数据⽂件
从InnoDB redo log file实时拷⻉⾛备份期间产⽣的所有redo⽇志
恢复的时候 数据⽂件 + redo⽇志 = ⼀致性数据
 

 

innodb存储引擎的逻辑结构如上图所示，逻辑单元从⼤到⼩分别为:表空间(tablesapce)、段(segment)、区
(extent)、⻚(page)、⾏(row)，如上图所示，每个⼤的逻辑单元中都包含了N个⼩的逻辑单元。
 
2.4 xtrabackup怎样实现增量备份
每个Page都有⾃⼰的LSN号码，LSN是⼀个全局递增的号码，每次对page中的记录进⾏修改时，都会有对应的LSN
号码，因为LSN是全局递增的，所以，LSN只会越来越⼤，之后的修改产⽣的LSN肯定⽐之前的操作产⽣的LSN⼤，
每个page中的数据被更改时，都会在这个page中记录下本次的LSN号码，所以，如果这个page中记录的LSN越
⼤，就证明这个Page中的数据越新（最近被修改）。
 
⽽xtrabackup就是通过LSN，实现对innodb表的增量备份的，每次备份开始时，xtrabackup会记录下当前备份到
的LSN号码，当下次进⾏增量备份时，xtrabackup就只会拷⻉出LSN⼤于上次记录的page，那些LSN号码⼩于上次
记录的page则会被跳过，如果page中的LSN⼩于上次备份时记录的LSN号码，则证明这个page⾃从上次备份以来
并没有被修改过，同理，如果page中的LSN⼤于上次备份时记录的LSN号，则证明这个page在上次备份以后，已经
被修改了，所以，备份出这些page，即可实现增量备份的⽬的。
假设，我们第⼀次备份的数据如下，所有数据由如下6个page组成，下图中的⻩⾊⽅块代表page，⻩⾊⽅块右上⻆
的号码代表当前page的LSN，从下图可以看出，⽬前最⼤的LSN号码为5。
 

 

安装 xtrabackup 官⽅下载地址
2.5 数据库完整备份与恢复
1.完整备份流程
mkdir -p /soft/xtrabackup && cd /soft/xtrabackup
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-
2.4.6/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
 
2.5 数据库完整备份与恢复
1.完整备份流程
//1.创建备份⽬录
[root@sql ~]# mkdir /xtrabackup/ && cd /xtrabackup/
//2.执⾏全备操作
[root@sql ~]# innobackupex --defaults-file=/etc/my.cnf -S /tmp/mysql.sock --user=root
--password="Wing@123" /xtrabackup/
//3.检查备份结果
[root@sql ~]# ls /xtrabackup/
2023-05-27_17-39-33
[root@lb-node1 xtrabackup]# cat /xtrabackup/2023-05-27_17-39-33/xtrabackup_binlog_info
sql_106_70.000017 2394
 
2.完全备份恢复流程
#1.停⽌数据库
[root@sql ~]#/etc/init.d/mysqld stop
#2.清理环境
[root@sql ~]# rm -rf /soft/mysql/data/*
#3.重演回滚
[root@sql ~]# innobackupex --apply-log /xtrabackup/2023-05-28_16-33-25
#4.恢复数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-33-25
#5.修改权限
[root@sql ~]# chown -R mysql.mysql /soft/mysql/data/
#6.启动数据库
[root@sql ~]# /etc/init.d/mysqld start
[root@sql ~]# mysql -uroot -pWing@123
mysql>
 
2.5数据库增量备份与恢复
增量备份每次需要基于前⼀次的备份
1.准备数据
#周⽇的数据
mysql> drop database wingdb;
mysql> create database wingdb;
mysql> use wingdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (7,'day7');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 7 | day7 |
+------+------+
1 row in set (0.00 sec)
 
2.使⽤物理全备
//清理之前的全量备份测试
[root@sql ~]# rm -rf /xtrabackup/*
[root@sql ~]# innobackupex --username=root --password=Wing@123 -S /tmp/mysql.sock
/xtrabackup/
 
3.后续每天进⾏增量备份
//模拟周⼀备份,先关闭时间同步
[root@lb-node1 xtrabackup]# crontab -l
#*/1 * * * * /usr/sbin/ntpdate ntp1.aliyun.com
//假如周⼀,登陆mysql插⼊数据
[root@sql ~]# date -s '2023-05-29'
[root@sql ~]# mysql -uroot -pWing@123
mysql> use wingdb;
mysql> insert into wingdb.t1 values (1,'day1');
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 7 | day7 |
| 1 | day1 |
+------+------+
//增量备份
[root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-38-33/
[root@sql ~]# cat /xtrabackup/2023-05-29_00-00-45/xtrabackup_binlog_info
sql_106_70.000018 4322
//周⼆,登陆mysql插⼊数据
[root@sql ~]# date -s '2023-05-30'
mysql> insert into wingdb.t1 values (2,'day2');
mysql> select * from wingdb.t1;
+------+------+
| id | name |
+------+------+
| 7 | day7 |
| 1 | day1 |
| 2 | day2 |
+------+------+
 
//基于周⼀基础之上进⾏增备
[root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-29_00-00-49/
[root@sql ~]# cat /xtrabackup/2023-05-30_00-00-45/xtrabackup_binlog_info
sql_106_70.000018 4587
 
4.模拟故障
//1.停⽌数据库
[root@sql ~]# /etc/init.d/mysqld stop
//2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*
 
5.依次重演
//恢复周⽇全备数据
[root@sql ~]# innobackupex --apply-log \
--redo-only /xtrabackup/2023-05-28_16-38-33/
//恢复周⼀的增量数据
[root@sql ~]# innobackupex --apply-log \
--redo-only /xtrabackup/2023-05-28_16-38-33/ \
--incremental-dir=/xtrabackup/2023-05-29_00-00-49
//恢复周⼆的增量数据
[root@sql ~]# innobackupex --apply-log \
--redo-only /xtrabackup/2023-05-28_16-38-33/ \
--incremental-dir=/xtrabackup/2023-05-30_00-00-27
 
5.执⾏回滚
//恢复对应数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-38-33/
//授权
[root@sql ~]# chown -R mysql.mysql /soft/mysql/
//启动数据库
[root@sql ~]# /etc/init.d/mysqld start
//查询数据
[root@sql ~]# mysql -uroot -pWing@123 -e "select * from wingdb.t1;"
+------+------+
| id | name |
+------+------+
| 7 | day7 |
 
1 | day1 |
| 2 | day2 |
+------+------+
 
2.6数据库差异备份与恢复
1.完整备份
//修改时间为周⽇
[root@sql ~]#/usr/sbin/ntpdate ntp1.aliyun.com
//创建对应数据
mysql> drop database wingdb;
mysql> create database wingdb;
mysql> use wingdb;
mysql> create table t2(id int);
mysql> insert into t2 values(7);
mysql> select * from t2;
+------+
| id |
+------+
| 7 |
+------+
//清理之前的备份 ⽣产谨慎操作
[root@sql ~]# rm -rf /xtrabackup/*
//使⽤物理全备
[root@sql ~]# /usr/sbin/ntpdate ntp1.aliyun.com
[root@sql ~]# mkdir /xtrabackup
[root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock
/xtrabackup/
[root@sql ~]# cat /xtrabackup/2023-05-27_18-10-25/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8559825
last_lsn = 8559834
compact = 0
recover_binlog_info = 0
 
2、差异备份:周⼀ -> 周三
//修改时间为周⼀, 新增数据
[root@sql ~]# date -s '2023-05-29'
mysql> insert into wingdb.t2 values(1);
//差异备份(以完整备份为准2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
 
 
//修改时间为周⼆, 新增数据
[root@sql ~]# date -s '2023-05-30'
mysql> insert into wingdb.t2 values(2);
//差异备份(以完整备份为准2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
 
//修改时间为周三, 新增数据
[root@sql ~]# date -s '2023-05-31'
mysql> insert into wingdb.t2 values(3);
//差异备份, (以完整备份为准2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
 
4.模拟备份后⼀次错误操作
mysql> create database WingSred;
mysql> drop database WingSred;
 
3.差异备份恢复流程
//1.停⽌数据库
[root@sql ~]# /etc/init.d/mysqld stop
//2.清理环境
[root@sql ~]# rm -rf /soft/mysql/data/*
//3.重演回滚, 回滚全备
[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/
//4.重演回滚, 将差异备份应⽤⾄完整备份上
[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/ \
--incremental-dir=/xtrabackup/2023-05-31_00-00-30/
//5.应⽤数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-52-14/
//6.修改权限
[root@sql ~]# chown -R mysql.mysql /soft/mysql/
//7.启动数据库
[root@sql ~]# /etc/init.d/mysqld start
[root@sql ~]# mysql -uroot -pWing@123 -e "select * from wingdb.t2;"
+------+
| id |
+------+
| 7 |
| 1 |
| 2 |
| 3 |
+------+
//6.binlog恢复删除掉的WingSred;库
[root@sql ~]# cat /xtrabackup/2023-05-31_00-00-25/xtrabackup_binlog_info
sql_106_70.000018 1676
//导出对应的sql, 找到drop语句位置
[root@sql ~]# mysqlbinlog --start-position=1676 \
--base64-output="decode-rows" \
-v sql_106_70.000019 sql_106_70.000018 >db.sql
//针对起始点与结束点位置恢复
[root@sql bin]# mysqlbinlog --start-position=1676 \
--stop-position=1847 sql_106_70.000018 |mysql -uroot -p'Wing@123'
 

 

3.简单命令进⾏物理备份
使⽤ tar 命令打包来进⾏物理备份数据库系统
备份期间，服务不可⽤
1.备份操作过程
//1.停⽌数据库
[root@sql ~]# /etc/init.d/mysqld stop
//2.
[root@sql ~]# mkdir /backup && cd /backup
//3.tar备份数据
[root@sql ~]# cp -rd /soft/mysql/ ./
[root@sql ~]# tar -czf `date +%F`-mysql-all.tar mysql
注：备份⽂件应该复制其它服务器或存储上
 
2.恢复操作过程
//1.停⽌数据库
[root@sql ~]#/etc/init.d/mysqld stop
//2.清理环境
[root@sql ~]# rm -rf /soft/mysql
//3.导⼊备份数据
[root@sql ~]# tar -xf /backup/2023-05-31-mysql-all.tar -C /soft/
//4.权限
[root@sql ~]# chown mysql.mysql -R /soft/mysql
//4.启动数据库
[root@sql ~]#/etc/init.d/mysqld start
//5.binlog 恢复
 
4.⽣产备份思路与实战
 
逻辑备份
保证⼀致性，服务可⽤性
适合少量的数据1G-10G
只能全备->指定某⼀个数据库备份
效率不是很⾼, 将所有的数据转成sql语句
ddl dml dcl
物理备份xtrabackup
保证⼀致性，服务可⽤性
适合⼤量的数据20G 100G 500G
⽀持增量、差异、全备
备份怎么做
物理备份 周天全备 周⼀到周六增量
/xtrabackup/1⽉/1周/xxx
/xtrabackup/1⽉/2周/xx
/xtrabackup/1⽉/3周
/xtrabackup/1⽉/4周
/xtrabackup/2⽉/1周
 
注意：
binlog⽇志⾮常的重要
备份时，不要记录binlog⽇志
备份不单单只是数据⽂件，还有binlog、my.cnf
 
 
 
 
posted @ 2023-06-16 23:20  必兮相语--  阅读(107)  评论(0)    收藏  举报