目录
一、数据备份
1.1物理备份
对数据库操作的物理文件进行备份，这种备份适用于出现问题时需要快速恢复的大型数据库。
1.1.1冷备份
关闭数据库后进行备份（tar）。
tar,需要关闭Msgl服务，或者确保服务在进行tar备份时,没有客户端操作。
1.1.2热备份
运行中备份，依赖于数据库的日志文件。
xtrabackup mysqllhotcapy 等热备工具 (第三方)，在mysgl正常运行时，进行备份。
1.1.3温备份
数据库锁定表格（不可写入，但可读）的状态备份操作。
mysqldump，这个是mysg自带的备份工具，特性 表备份，每备份一张表时，会先drop删除，然后重新create创建表结构，然后再锁表(仅锁定写的操作，但可读)，进行insert语句的备份，备份完成之后，进行解锁 (unlock)，然后会备份下一个。
二、逻辑备份
2.1完全备份
对数据库完全备份，全部备份，但是占用时间长，占用大量的空间
2.2差异备份
每一次差异备份，都会备份上一次完全备份后的数据，可能出现备份重复，占用大量空间
2.3增量备份
主要是使用bin-log 二进制日志来完成备份，同时每个增量备份的文件，可以通过mysgladmin -u-pflush-logs来刷新生成新的二进制增备的日志文件，同时可以结合crontab完成自动刷新。
再进行基于二进制文件的增备恢复时，有必要的话，需要先恢复完备的数据，再去 一次恢复增备的数据，知道恢复至我们需要恢复的数据位置。
三、数据备份恢复实验
3.1做一个数据
create database text1;
use text1;
create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id));
insert into info1 values(1,'user1',20,'running');
insert into info1 values(2,'user2',30,'singing');
insert into info1 values(3,'user3',28,'car');
select * from info1; 
3.2物理冷备份与恢复
systemctl stop mysqld
yum -y install xz
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
mv /usr/local/mysql/data/ /opt/
#解压恢复
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./3.3mysqldump 备份与恢复（温备份）
create table info2 (id int,name char(10),age int,sex char(4));
insert into info2 values(1,'user',11,'性别');
insert into info2 values(2,'user',11,'性别');
(1)、完全备份一个或多个完整的库 (包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
#备份一个kgc库
mysqldump -u root -p --databases text > /opt/text.sql
#备份mysql与 kgc两个库
mysqldump -u root -p --databases mysql text > /opt/mysql-text.sql
(2)、完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
mysqldump -u root -p --all-databases > /opt/all.sql
(3)、完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
例:
mysqldump -u root -p [-d] kgc info1 info2 > /opt/kgc_info1.sql
#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d"选项，说明表数据也进行备份
#做为一个表结构模板
(4)查看备份文件
grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"
3.4Mysql数据恢复
数据备份
mysqldump -uroot -p --databases text > /opt/text.sql
2.模拟数据库出现问题
[root@server1 backup]# mysql -uroot -pabc123 登录数据库
mysql> show databases; 查看数据库信息
mysql> drop database school; 删除数据库school
mysql> show databases;
3.数据恢复
mysql -uroot -pabc123 text < /opt/info1.sql
4.查看情况
mysql -uroot -pabc123 -e 'show tables from text;'
3.5模拟数据库text丢失
3.5.1备份数据库text
mysqldump -uroot -pabc123 --databases text > /opt/text_all.sql
3.5.2模拟数据库text丢失
drop database text; 
3.5.3数据库text恢复
mysql -uroot -p text < /opt/text_all.sql
mysql -uroot -p -e 'show tables from text;'
四、MySQL 增量备份与恢复
4.1MySQL数据库增量恢复
1.一般恢复
将所有备份的二进制日志内容全部恢复
2.基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作 可以基于精准的位置跳过错误的操作 发生错误节点之前的一个节点，上一次正确操作的位置点停止
3.基于时间点恢复
跳过某个发生错误的时间点实现数据恢复 在错误时间点停止，在下一个正确时间点开始
4.2MySQL 增量备份
4.2.1.开启二进制日志功能
二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
server-id = 1 4.2.2查看生成的二进制文件
二进制日志中需要关注的部分
1、at ：开始的位置点
2、end_log_pos：结束的位置
3、时间戳： 210712 11:50:30
4、SQL语句
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
#①
mysqlbinlog --no-defaults /opt/mysql-bin.000002
#②
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
# 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
4.3MySQL增量恢复
4.3.1一般恢复
#数据 备份
[root@localhost opt]# mysqldump -uroot -pabc123 text info1 > /opt/text_info1__2023-07-18.sql
#删除数据
mysql> drop table info1;
Query OK, 0 rows affected (0.00 sec)
#数据恢复
[root@localhost opt]# mysql -uroot -pabc123 text < text_info1_2023-07-18.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
#查看数据是否恢复
mysql> mysql> show tables;
+----------------+
| Tables_in_text |
+----------------+
| info1 |
| info2 |
| info3 |
| info_bak |
| info_bak1 |
+----------------+
5 rows in set (0.00 sec)
mysql> select * from info1;
+----+-------+-----+---------+
| id | name | age | hobby |
+----+-------+-----+---------+
| 1 | user1 | 20 | running |
| 2 | user2 | 30 | singing |
+----+-------+-----+---------+
2 rows in set (0.00 sec)
mysql> drop database ky13;
Query OK, 1 row affected (0.00 sec)
mysql> exit
② 基于mysql-bin.000002恢复
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
4.3.2断点恢复
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例:
at 302
#201122 16:41:16
插入了"user3"的用户数据
at 623
#201122 16:41:24
插入了"user4"的用户数据
(1)、基于位置恢复
① 插入三条数据
mysql> use lakers;
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into test1 values(3,'true');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(4,'f');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(5,'t');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
| 3 | true |
| 4 | f |
| 5 | t |
+------+------+
5 rows in set (0.00 sec)
#需求：以上id =4的数据操作失误，需要跳过
② 确认位置点，刷新二进制日志并删除test1表
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000003
960 停止
1066 开始
#刷新日志
mysqladmin -uroot -p123123 flush-logs
mysql> use lakers;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_lakers |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table lakers.test1;
Query OK, 0 rows affected (0.00 sec)
③ 基于位置点恢复
#仅恢复到操作 ID 为“623"之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p #恢复从位置为400开始到位置为623为止
(2)、基于时间点恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
#仅恢复到16:41:24 之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start
五、总结
完全备份
物理冷备份 ―关闭Mysqld 服务，tar命令打包 data目录，恢复就是解压tar包
逻辑备份
mysqldump -u-p--datebases 库1 库2 > xxx.sql
mysqldump -u -p --all-datebases > Xxx.sql
mysqldump -u -p 库1 表1 表2 >XXx.sql
完全恢复
mysql -u -p <xxx.sql 恢复库
mysql-u -p 库名 < XXx.sql 恢复表
增量备份
1、开启二进制日志，设置二进制日志格式MIXED
2、进行一次完全备份，可每周备份一次，通过crontab -e
3、使用mysqladm -uroot -p flush-logs刷新分割出二进制日志，由于刷新之前的数据操作都记录在老的二进制日志里，所以需要把老的二进制日志复制到一个安全的目录中保管
4、可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v二进制日志文件名查看日志内容
5、可以通过mysqlbinlog --no-defaults二进制日志文件名l mysql -uroot -p恢复丢失的数据 位置恢复 时间恢复
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
