💕10、MySQL数据备份和恢复

mysql日志管理

Mysql日志管理

1、错误日志

#作用：MySql启动报错的错误信息，为了排查

#默认是否开启：开启


#储存位置：
[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql   179797 Aug 20 10:31 db01.err

root@localhost:(none) > show variables like '%err%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| log_error           | ./db01.err   | 
+---------------------+--------------+

#名字：hostname.err
./db01.err 

#设置mysql错误日志的位置，但是需要提前创建这个日志文件并且授权
1、创建出错误日志文件,并授权
[root@db01 ~]# touch /opt/mysql_error.txt
[root@db01 ~]# chown mysql.mysql /opt/mysql_error.txt

2、修改mysql配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签随便添加一行写入：
log_error=/opt/mysql_error.txt  #后面写上你要存放的路径

3、重启
[root@db01 ~]# /etc/init.d/mysqld restart

2、常规日志

#作用：记录常规操作

#默认是否开启：否

#储存位置：
root@localhost:(none) > show variables like '%general_log%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /app/mysql-5.6.50/data/db01.log |
+------------------+---------------------------------+

#名字：hostname.log
db01.log

#修改方式：
# 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/app/mysql/data/db01.log

3、二进制日志

#作用：只记录对数据发生变化的语句
记录已提交的DML语句，并拆分为多个事件(event)来进行记录 记录所有DDL,DML,DCL

#默认是否开启：否

#储存位置：
root@localhost:(none) > show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| log_bin                         | ON                                     |
| log_bin_basename                | /app/mysql-5.6.50/data/mysql-bin       |
| log_bin_index                   | /app/mysql-5.6.50/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                    |
| log_bin_use_v1_row_events       | OFF                                    |
| sql_log_bin                     | ON                                     |
+---------------------------------+----------------------------------------+

#修改方法 MySQL5.6
修改mysql配置文件
[root@db01 ~]# vim /etc/my.cnf
#在空行随便添加一个
[mysqld]
.....
log_bin=mysql_bin


#修改方法 MySQL5.7  (需要和server_id配合使用)
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
.....
server_id=1
log-bin=mysql-bin

二进制的3种工作模式

1、statement 语句模式(mysql5.6的默认模式)
2、row 行级模式(mysql5.7的默认模式)
3、mixed 混合模式

• statement 语句模式(mysql5.6的默认模式)

#查看当前mysql5.6默认模式
root@localhost:(none) > show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

#查看当前mysql5.7默认模式
root@localhost:(none) > show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+


#语句模式的每一条语句都很清晰
优点：简单易读，占用磁盘空间小
缺点：不易读

• row 行级模式(mysql5.7的默认模式 企业常用的模式)

优点：严谨记录原数据和数据的变化过程，不容易丢数据
缺点：占用磁盘空间大，不易读

#5.6开启行级模式
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
.....
binlog_format=row

• mixed 混合模式(上面2种模式的混合 不常用)

二进制日志的查看方式

#二进制日志查看方式
[root@db01 ~]# mysqlbinlog /app/mysql-5.6.50/data/mysql-bin.000010 


[root@db04 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.7.44/data/mysql-bin.000001 
base64加密算命去给他解码
decode-rows:转码
incode  编码

#只看和某个binlog库相关的数据   -d+库名
[root@db04 ~]# mysqlbinlog --base64-output=decode-rows -vvv -d binlog /app/mysql-5.7.44/data/mysql-bin.000001 

mysql5.6:一般新数据库at 120位置点之后就是写入的数据
mysql5.7:一般新数据库at 154位置点之后就是写入的数据


#mysqlbinlog用的什么工作模式，怎么解析数据的？  面试问题
行级模式
mysqlbinlog --base64-output=decode-rows -vvv    就可以解析出来

二进制日志的操作

#开启方式 
#注意:在mysql5.7中开启binlog必须要加上server-id。
[root@db01 data]# vim /etc/my.cnf
[mysqld]
.....
log-bin=mysql-bin
binlog_format=row
server_id=1


#查看当前有哪些binlog  以及binlog的大小
物理方法
[root@db04 ~]# ll /app/mysql-5.7.44/data/
-rw-r----- 1 mysql mysql      688 Aug 20 14:26 mysql-bin.000001

mysql命令行查看
root@localhost:(none) > show binary logs;

#查看当前最新的binlog和位置点
root@localhost:(none) > show master status;

#查看指定binlog中的所有事件，不适合看行级模式
root@localhost:(none) > show binlog events in  'mysql-bin.000012';

事件介绍
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）

事件的特性(binlog的特性)

1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置（文件大小）
3）对于一个二进制日志中，前120个position是文件格式信息预留空间（MySQL5.6）。
   对于一个二进制日志中，前154个position是文件格式信息预留空间（MySQL5.7）
4）MySQL第一个记录的事件，都是从120开始的

利用二进制日志恢复数据 （MySQL 5.6）

1、模拟数据

1、#刷新的binlog,并且从120开始
root@localhost:(none) > flush logs;
root@localhost:(none) > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2、#创建binlog库 表 
root@localhost:(none) > create database binlog;
root@localhost:(none) > use binlog;
root@localhost:binlog > create table binlog_table(id int);

3、#插入数据
root@localhost:binlog > insert into binlog_table values(1);
mysql> select * from binlog_table;
+------+
| id   |
+------+
|    1 |
+------+

4、#再插入数据
root@localhost:binlog > insert into binlog_table values(2);
root@localhost:binlog > insert into binlog_table values(3);
mysql> select * from binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

5、#删除一条数据
root@localhost:binlog > delete from binlog_table where id=1;
root@localhost:binlog:18: >select * from binlog_table;
+------+
| id   |
+------+
| 2    |
| 3    |
+------+

6、#修改一条数据
root@localhost:binlog > update binlog_table set id=22 where id=2;
root@localhost:binlog > select * from binlog_table;
+------+
| id   |
+------+
|   22 |
|    3 |
+------+

7、#删表
root@localhost:binlog > drop table binlog_table;

8、#删库
root@localhost:binlog > drop database binlog;

2、使用bin log 恢复数据到delete之前

恢复到： 
mysql> select * from binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

你要知道你在这个状态之后执行了什么：执行了delete

1、#准备一个新环境 db02
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql-5.6.50
datadir=/app/mysql-5.6.50/data
log_bin=mysql-bin
server_id=1
lower_case_table=1
#innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
binlog_format=row

[client]

[mysql]
prompt="\\u@\\h:\\d > "
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start

2、#把旧环境的数据库进行全备
[root@db01 ~]# mysqldump -uroot -p123 -A > /tmp/full.sql

3、#新环境db02创建一个可以远程连接的用户(如果数据量比较大，可以创建用户远程恢复)
root@localhost:(none) > grant all on *.* to root@'%' identified by '123';
root@localhost:(none) > select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |


4、#db01将全备恢复到新环境
[root@db01 ~]# mysql -uroot -p123 -h172.16.1.52 < /tmp/full.sql 


5、#旧环境截取binlog
因为一开始刷新的binlog,所以起始位置点从120开始 mysql-bin.000012
起始位置点：120
结束位置点: id为1 2 3 之后，执行了delete   
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000012 | grep -iC 10 delete
# at 937
#240820 11:18:12 server id 1  end_log_pos 1011 CRC32 0x9073f227         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1724123892/*!*/;
BEGIN
/*!*/;
......
COMMIT/*!*/;

查到结束位置点就是：937 (上下10行 begin 前面的at 937)

#截取
[root@db01 ~]# mysqlbinlog --start-position=120 --stop-position=937 /app/mysql-5.6.50/data/mysql-bin.000012 > /tmp/binlog.sql

6、#恢复截取的数据到新环境
[root@db01 ~]# mysql -uroot -p123 -h172.16.1.52 < /tmp/binlog.sql 

7、#db02新环境查看数据
root@localhost:(none) > select * from binlog.binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

8、#全备完了，用户如果还在往旧数据库写数据，需要恢复

9、#应用割接
      - 开发改代码
      - 运维导出新环境的数据到旧环境

存在问题

1、生产中不会刷新binlog

2、生产中肯定会一直提供服务，实时都在写入数据

3、生产中截取数据时，可能会截取到其他库的数据
加上-d  只截取和binlog相关的库
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv -d binlog
/app/mysql/data/mysql-bin.000030

120~937位置点中包含binlog库的数据
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=937 -d binlog /app/mysql/data/mysql-bin.000030 > /tmp/binlog.sql

#所以不能单独使用binlog截取数据，可以每天凌晨一点在定时任务里面做一次全备
#binlog只能用来当成是增量数据的备份，一定要配合mysqldum逻辑备份的全备一起使用，单独使用非常累

binlog的刷新和删除

#binlog刷新
1、重启mysql数据库
2、执行flush logs;
3、mysqladmin -uroot -p123 flush-log
4、当binlog大小到达1G,会自动刷新

#binlog要定期清理和删除
1、根据时间删除
#临时生效
root@localhost:(none) > SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2、根据时间删除
root@localhost:(none) > PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3、根据文件名删除(常用)
root@localhost:(none) > PURGE BINARY LOGS TO 'mysql-bin.000020';
20  会把20之前的全部删除

4、慢查询日志

只要会开启他就行了 他是记录执行比较慢的SQL语句

# 作用：记录执行的比较慢的SQL语句

# 默认是否开启：否

# 存储位置：/app/mysql/data/
root@localhost:(none) > show variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | OFF                                  |
| slow_query_log_file       | /app/mysql-5.6.50/data/db01-slow.log |
+---------------------------+--------------------------------------+

# 名字：hostname-slow.log
db01-slow.log

# 修改方式
## 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log=1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/app/mysql-5.6.50/data/db01-slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询语句是否记录到日志
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志  （鸡肋）
min_examined_row_limit=100




## 模拟慢查询
#进入world库
mysql> use world
#查看表
mysql> show tables
#将city表中所有内容加到t1表中
mysql> create table t1 select * from city;
#查看t1的表结构
mysql> desc t1;
#将t1表所有内容插入到t1表中（多插入几次）
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
#删除t1表中id>2000的数据
mysql> delete from t1 where id>2000;
#查看慢日志
[root@db01 ~]# cat /application/mysql/data/mysql-db01


[root@db01 ~]# mysqldumpslow /app/mysql-5.6.50/data/db01-slow.log
-s：指定排序顺序
      c：Count 执行次数
      t：Time 执行时间 总时间
      r：Rows 结果行数 总行数
      l：Lock 锁表时间 总锁表时间
      
      at：平均时间
      ar：平均行数
      al：平均锁表时间

使用percona公司提供的pt-query-digest工具分析慢查询日志

https://www.percona.com/  


1、下载并安装
[root@db01 ~]# yum install -y https://downloads.percona.com/downloads/percona-toolkit/3.6.0/binary/redhat/7/x86_64/percona-toolkit-3.6.0-1.el7.x86_64.rpm

2、这条命令就可以分析慢查询日志
[root@db01 ~]# pt-query-digest /app/mysql-5.6.50/data/db01-slow.log 




可视化界面：
Anemometer基于pt-query-digest将MySQL慢查询可视化
httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
httpss://github.com/box/Anemometer 可视化代码下载

在恢复数据时，数据库在实时写入数据，怎么恢复

#往数据库一直写数据的脚本
[root@db01 ~]# vim insert.sh 
#!/bin/bash
master_ip=$(hostname -I|awk  '{print $2}')
mysql_cli="mysql -uroot -p123 -h${master_ip}"
${mysql_cli} -e 'drop database if exists prod;'
${mysql_cli} -e 'create database if not exists prod;'
${mysql_cli} -e 'create table if not exists prod.prod(id int primary key auto_increment);'
num=1
while true;do
 ${mysql_cli} -e "insert into prod.prod values($((num++)));commit;"
 sleep 1
done

1、模拟写数据

1、#运行脚本
[root@db01 ~]# sh insert.sh

2、#创建binlog库 表 
root@localhost:(none) > create database binlog;
root@localhost:(none) > use binlog;
root@localhost:binlog > create table binlog_table(id int);

3、#插入数据
root@localhost:binlog > insert into binlog_table values(1);

4、#再插入数据
root@localhost:binlog > insert into binlog_table values(2);
root@localhost:binlog > insert into binlog_table values(3);

5、#删除一条数据
root@localhost:binlog > delete from binlog_table where id=1;

6、#修改一条数据
root@localhost:binlog > update binlog_table set id=22 where id=2;

7、#删表
root@localhost:binlog > drop table binlog_table;

8、#删库
root@localhost:binlog > drop database binlog;

2、恢复

恢复到： 
mysql> select * from binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

你要知道你在这个状态之后执行了什么：执行了delete
如果旧环境还能提供服务就暂时不停止库，一会再停止

1、#准备一个新环境 db02
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql-5.6.50
datadir=/app/mysql-5.6.50/data
log_bin=mysql-bin
server_id=1
lower_case_table=1
#innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
binlog_format=row

[client]

[mysql]
prompt="\\u@\\h:\\d > "
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start


2、#旧环境截取binlog数据的位置点
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015|grep -iC 10  'create database binlog'
开始位置：11999
[root@db01 ~]#  mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015|grep -iC 10 'delete from `binlog`'
结束位置点：30114

只恢复binlog库数据的截取
[root@db01 ~]# mysqlbinlog --start-position=11999 --stop-position=30114 -d binlog /app/mysql-5.6.50/data/mysql-bin.000015 > /tmp/binlog.sql 

3、#新环境db02创建一个可以远程连接的用户(如果数据量比较大，可以创建用户远程恢复)
root@localhost:(none) > grant all on *.* to root@'%' identified by '123';

4、#db01将截取的数据恢复到新环境
[root@db01 ~]# mysql -uroot -p123 -h172.16.1.52 < /tmp/binlog.sql 


5、#新环境查看数据
root@localhost:(none) > show databases;
root@localhost:(none) > select * from binlog.binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

6、#应用割接   运维导出新环境binlog的数据到旧环境
[root@db02 ~]# mysqldump -B binlog > /tmp/binlog2.sql
[root@db02 ~]# scp /tmp/binlog2.sql 172.16.1.51:/tmp

7、#挂维护页，停止连接数据库的程序tomcat php，不能停止数据库，因为导入数据需要使用mysql客户端的命令



8、#新环境关闭binlog的记录，防止binlog被污染,再恢复数据
root@localhost:(none) > set sql_log_bin=0;
root@localhost:binlog > source /tmp/binlog2.sql;

9、#开启binlog的记录
root@localhost:(none) > set sql_log_bin=1;
root@localhost:binlog > select * from binlog_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

10、#取消维护页，开启连接数据库的服务

MySQL数据备份和恢复

备份的原因
运维工作的核心简单概括就两件事:
1）第一个是保护公司的数据.
2）第二个是让网站能7*24小时提供服务(用户体验)。

备份的类型
冷备份(停服务做备份)：(mysqldump做不了)
这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

温备份(不停服务做备份，但是备份过程中会锁表)：
这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

热备份(不停服务做备份，也不锁表)：
这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

恢复数据的时候一定要跟开发确认好

逻辑备份 物理备份可以一起用，哪种速度快，可以考虑使用

备份的工具

1、mysqldump (逻辑)
mysql原生自带很好用的逻辑备份工具

2、mysqlbinlog (逻辑)
实现binlog备份的原生态命令

3、xtrabackup (物理)
precona公司开发的性能很高的物理备份工具

备份的方式

• 逻辑备份

基于SQL语句的备份
1）binlog
2）into outfile
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp

#这个是恢复表里面的真实数据
mysql> select * from world.city into outfile '/tmp/world_city.data';


3）mysqldump
4）replication(主从复制)
5) mysqlbinlog

• 物理备份

  基于数据文件的备份
  1）Xtrabackup（percona公司写的)

备份策略

• 全备：备份所有数据
• 增备：每次基于上一次备份后新增的数据
• 差异备份：(每次都基于全备)

mysqldump逻辑备份工具

#客户端命令选项
-u：指定用户
-p：指定密码
-h：指定主机
-P：指定端口
-S：指定socket文件

备份命令的选项

1、全备

-A  /  --all-databases :备份mysql的所有库和所有表

[root@db02 ~]# mysqldump -uroot -p123 -A > /tmp/full.sql  

2、库级备份

-B    /   --databases  ：指定库做备份

#mysqldupm -uroot -p123 -B 库名  库名 库名  库名...

#指定单个库做备份    (备份出来的文件有建库语句)
[root@db02 ~]# mysqldump -uroot -p123 -B world > /tmp/world.sql

#恢复方式
[root@db02 ~]# mysqldump -uroot -p123 < /tmp/world.sql

#指定多个库做备份
[root@db02 ~]# mysqldump -uroot -p123 -B world student > /tmp/world_stu.sql

3、表级备份

#不加选项，直接写库名字
[root@db02 ~]# mysqldump -uroot -p123 world > /tmp/world1.sql

#恢复方式(恢复时需要指定一个库)
[root@db02 ~]# mysqldump -uroot -p123 test < /tmp/world1.sql


# mysqldump -uroot -p123 库名 表名 表名 表名 表名...
[root@db02 ~]# mysqldump -uroot -p123 linux8 city > /tmp/linux8_city.sql
[root@db02 ~]# mysqldump -uroot -p123 linux8 prod > /tmp/linux8_prod.sql

4、打点备份

#打点备份(备份位置点和名字) 
好处：这次全备就记录到这个位置点了，新增的数据就是这个点之后的，想截取binlog，开始位置点就看这个点，结束位置点去binlog里面截取

--master-data=[0|1|2] ：(温备选项)  

his option will turn --lock-all-tables on(使用这个选项时，会打开 --lock--all-tables选项，会锁表)
unless --single-transaction is specified too(除非加入 --single-transaction这个选项，就不会被锁表(快照)) 

0：关闭这个选项，跟不写这个选项一样，没有change master的记录
1：If equal to 1, will print it as a CHANGE MASTER command; 
    #打印chenge master语句
2：if equal to 2, that command will be prefixed with a comment symbol.
    #chenge master将会被注释掉(主从复制可以用 2比较常用)  文件的22行记录

5、快照备份

#快照备份
--single--transaction：(热备选项) 不锁表，不影响用户持续写入数据

加入这个选项，在备份的时候就相当于拍了快照，就备份到目前的指定位置，后面用户写入的数据我不备份，只有快照我不知道备份到哪里，需要配合打点备份使用，用户写入的数据就是这个点之后的数据


#全备且热备的命令
[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /tmp/full.sgl

[root@db01 ~]# vim /tmp/full.sgl
22行：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=105700;
记录这次全备，备份到mysql-bin.000015，位置点是：10500，这个点之后的数据就是新增数据

6、备份函数和存储过程

-R： 函数和储存过程是开发写程序的时候写的(如果有就备份，如果没有就不备份，但是加上这个选项也没有任何影响)

mysqldump -uroot -p123 -A -R --master-data=2 --single-transaction > /tmp/full.sgl

7、备份触发器

--triggers:  备份触发器 (如果开发写了就备份，如果没有就不备份,加上这个选项也没有任何影响)

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sgl

#压缩
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction |gzip > /tmp/full.sgl

#完整的备份语句
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

8、刷新binlog备份

-F：做备份的同时，每备份一个库，刷新一个新的binlog出来(不常用)
出来的是没有用的binlog,小文件就会多，会导致inode被沾满

[root@db02 ~]# mysqldump -uroot -p123 -B table1 table2 table3 -F > /tmp/test_binlog.sql
会刷新3个binlog出来


#认识，但不用的选项
-d:仅备份表结构
-t:仅备份数据

完整的热备语句

任何场景的备份命令：
压缩，并且记录备份时间戳,写脚本也适用

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz


-rw-r--r--  1 root root 252419 Aug 22 00:55 full_2024-08-22.sql.gz
#解压，恢复数据不需要解压的
[root@db01 ~]# gzip -d /tmp/full_2024-08-22.sql.gz 

#恢复数据
[root@db01 ~]# zcat /tmp/full_2024-08-22.sql.gz |mysql -uroot -p123

注意：
1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
3）mysqldump是以覆盖的形式恢复数据的

企业故障恢复案例1

背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本

故障时间点：
上午10点开发人员误删除一个核心业务表，如何恢复？

#23~10点的增量数据要截取
#删表语句后面新增的数据也要截取

思路：
1）停业务避免数据的二次伤害
2）找一个临时的库，恢复前一天的全备
3）截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4）测试可用性和完整性
5）开启业务前的两种方式
    a.直接使用临时库顶替原生产库，前端应用割接到新库
    b.将误删除的表单独导出，然后导入到原生产环境
6）开启业务

1、故障模拟演练

1、#先执行写入数据的脚本
[root@db01 ~]# sh insert.sh 

2、#创建库创表
root@localhost:(none) > create database hexin;
root@localhost:(none) > use hexin;
root@localhost:hexin > create table hexin(id int);

3、#运行那么久了，往里面写点数据
root@localhost:hexin > insert into hexin values(1),(2),(3),(4);
root@localhost:hexin > select * from hexin;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

4、#假设现在到晚上23点了，任务计划自动做全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

5、#模拟新增数据
root@localhost:hexin > update hexin set id=10 where id=1;
root@localhost:hexin > select * from hexin;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
|    4 |
+------+

root@localhost:hexin > delete from hexin where id=3;
root@localhost:hexin > select * from hexin;
+------+
| id   |
+------+
|   10 |
|    2 |
|    4 |
+------+

6、#上午10点开发人员误删除一个核心业务表
root@localhost:hexin > drop table hexin;

root@localhost:hexin > select * from hexin;
ERROR 1146 (42S02): Table 'hexin.hexin' doesn't exist

如果网站不能提供服务了就停止数据库，如果网站还能使用，就可以不停止数据库

2、恢复(不停库)

+------+   #恢复到这里
| id   |
+------+
|   10 |
|    2 |
|    4 |
+------+


1、#准备新环境  db02
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql-5.6.50
datadir=/app/mysql-5.6.50/data
log_bin=mysql-bin
server_id=1
lower_case_table=1
#innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
binlog_format=row

[client]

[mysql]
prompt="\\u@\\h:\\d > "
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start


2、#新环境创建一个远程连接的用户
root@localhost:(none) > grant all on *.* to root@'172.16.1.%' identified by '123';


3、#将头一天的全备恢复到新环境
[root@db01 ~]# zcat /tmp/full_2024-08-22.sql.gz |mysql -uroot -p123 -h172.16.1.52
新环境查看
root@localhost:(none) > select * from hexin.hexin;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

3、#截取binlog新增数据
[root@db01 ~]# zcat /tmp/full_2024-08-22.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=527709;
在全备里面   'mysql-bin.000015   
开始位置点：527709

结束位置点：上午10点开发人员误删除一个核心业务表
drop table hexin这个语句之前就是结束位置点
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015 |grep -iC 10 'drop table `hexin`'
结束位置点：584459

[root@db01 ~]# mysqlbinlog --start-position=527709 --stop-position=584459 /app/mysql-5.6.50/data/mysql-bin.000015 > /tmp/inc1.sql


4、#将第一段的数据恢复到新环境
[root@db01 ~]# mysql -uroot -p123 -h172.16.1.52 < /tmp/inc1.sql 
新环境查看
root@localhost:(none) > select * from hexin.hexin;
+------+
| id   |
+------+
|   10 |
|    2 |
|    4 |
+------+

5、#挂维护页，停止连接数据库的程序
这个时候可以停止脚本执行
查看总的数据
root@localhost:hexin > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     4366 |
+----------+


6、#截取删表之后的新增数据
开始位置点：上午10点开发人员误删除一个核心业务表
drop table hexin这个语句位置点，的后面一个位置点
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015 |grep -iC 10 'drop table `hexin`'
SET @@session.pseudo_thread_id=2475/*!*/;
DROP TABLE `hexin` /* generated by server */
/*!*/;
# at 584579
#240822  1:54:12 server id 1
开束位置点：584579


结束位置点：(需要停止连接数据库的服务才好找，不要往数据库里面写数据)
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015|tail
# at 940180                     结束位置点
240822  2:26:40 server id 1  'end_log_pos 940211' CRC32 0xd479131d       Xid = 26217

或者直接看文件大小就可以，结束位置点和binlog文件停止写入的大小一样
[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql   940211 Aug 22 02:26 mysql-bin.000015

结束位置点：940211    (end_log_pos 940211)

[root@db01 ~]# mysqlbinlog --start-position=584579 --stop-position=940211 /app/mysql-5.6.50/data/mysql-bin.000015 > /tmp/inc2.sql

7、#将第二段的新增数据恢复到新环境
[root@db01 ~]#  mysql -uroot -p123 -h172.16.1.52 < /tmp/inc2.sql

新环境查看总的数据，和旧环境的数据一样
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     4366 |
+----------+
1 row in set (0.00 sec)

root@localhost:(none) > select * from hexin.hexin;
+------+
| id   |
+------+
|   10 |
|    2 |
|    4 |
+------+

8、#应用割接
        - 运维：新环境全备，导入旧环境
        - 开发：修改连接数据库的代码
        
9、#取消护页，开启连接数据库的程序

企业故障恢复案例2

背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本

故障时间点：
上午10点开发人员误删除一个核心业务表之前，'还误修改了一条数据,误将1改成10了'，如何恢复？


最终要恢复的数据是：
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+

#截取思路
1、截取全备~update之前
2、update之后~(drop table hexin)上午10点开发人员误删除一个核心业务表之前
3、(drop table hexin)之后~停库

1、故障模拟演练

1、#先执行写入数据的脚本
[root@db01 ~]# sh insert.sh 

2、#创建库创表
root@localhost:(none) > create database hexin;
root@localhost:(none) > use hexin;
root@localhost:hexin > create table hexin(id int);

3、#运行那么久了，往里面写点数据
root@localhost:hexin > insert into hexin values(1),(2),(3),(4);
root@localhost:hexin > select * from hexin;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

4、#假设现在到晚上23点了，任务计划自动做全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

5、#模拟新增数据
root@localhost:hexin > update hexin set id=10 where id=1;
root@localhost:hexin > select * from hexin;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
|    4 |
+------+

root@localhost:hexin > delete from hexin where id=3;
root@localhost:hexin > select * from hexin;
+------+
| id   |
+------+
|   10 |
|    2 |
|    4 |
+------+

6、#上午10点开发人员误删除一个核心业务表
root@localhost:hexin > drop table hexin;

root@localhost:hexin > select * from hexin;
ERROR 1146 (42S02): Table 'hexin.hexin' doesn't exist

如果网站不能提供服务了就停止数据库，如果网站还能使用，就可以不停止数据库

2、恢复(不停库)

+------+   #恢复到这里
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+


1、#准备新环境  db02
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
配置文件和旧环境大部分保持一致
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
skip-name-resolve
basedir=/app/mysql-5.6.50
datadir=/app/mysql-5.6.50/data
log_bin=mysql-bin
server_id=1
lower_case_table=1
#innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
binlog_format=row

[client]

[mysql]
prompt="\\u@\\h:\\d > "
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start


2、#新环境创建一个远程连接的用户
root@localhost:(none) > grant all on *.* to root@'172.16.1.%' identified by '123';   #当全备恢复过来，这个用户就不在了，我们后面用远程用户导数据，是没有刷新表或者重启mysql，如果一旦做了重启或者刷新，这个用户就不能用了


3、#将头一天的全备恢复到新环境
[root@db01 ~]# zcat /tmp/full_2024-08-22.sql.gz |mysql -uroot -p123 -h172.16.1.52
新环境查看
root@localhost:(none) > select * from hexin.hexin;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

#截取思路
1、截取全备~update之前
2、update之后~(drop table hexin)上午10点开发人员误删除一个核心业务表之前
3、(drop table hexin)之后~停库

3、#截取全备~update之前
[root@db01 ~]# zcat /tmp/full_2024-08-22.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=969876;
开始位置点：969876

[root@db01 ~]#  mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015 |grep -iC 10 'update `hexin`'
结束位置点：971995

mysqlbinlog --start-position=969876 --stop-position=971995 /app/mysql-5.6.50/data/mysql-bin.000015 > /tmp/inc1.sql



4、#截取update之后~上午10点开发人员误删除一个核心业务表
[root@db01 ~]#  mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015 |grep -iC 10 'update `hexin`'
开始位置点：972090

[root@db01 ~]#  mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015 |grep -iC 10 'drop table `hexin`'
结束位置点：979382

mysqlbinlog --start-position=972090 --stop-position=979382 /app/mysql-5.6.50/data/mysql-bin.000015 > /tmp/inc2.sql

5、#挂维护页，停止连接数据库的程序
这个时候可以停止脚本执行
查看总的数据
root@localhost:hexin > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     2115 |
+----------+

6、#截取(drop table hexin)之后~停库(停止连接数据库的程序)
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql-5.6.50/data/mysql-bin.000015 |grep -iC 10 'drop table `hexin`'
开始位置点：979502

[root@db01 ~]# ll /app/mysql-5.6.50/data/
-rw-rw---- 1 mysql mysql  1334948 Aug 22 04:25 mysql-bin.000015
结束位置点：1334948

mysqlbinlog --start-position=979502 --stop-position=1334948 /app/mysql-5.6.50/data/mysql-bin.000015 > /tmp/inc3.sql


7、#将数据恢复到新环境
mysql -uroot -p123 -h172.16.1.52 < /tmp/inc1.sql
mysql -uroot -p123 -h172.16.1.52 < /tmp/inc2.sql
mysql -uroot -p123 -h172.16.1.52 < /tmp/inc3.sql

新环境查看总的数据，和旧环境的数据一样
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     2115 |
+----------+
1 row in set (0.00 sec)

root@localhost:(none) > select * from hexin.hexin;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+

8、#应用割接
        - 运维：新环境全备，导入旧环境
        - 开发：修改连接数据库的代码
        
9、#取消护页，开启连接数据库的程序


如果在就服务器里面恢复，先截取使用数据，把所有需要的数据截取准备好，停服务，按顺序一条一条往里面恢复

mysql物理备份

安装xtrabackup

1、下载2.4版本的xtrabckup   db02
[root@db02 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm

2、安装
[root@db02 ~]# yum localinstall -y percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm

3、可以tabl出这两个命令就安装成功
#(热备的命令,专门innodb储存引擎做备份的)
[root@db02 ~]# innobackupex 

#(老版本的命令,他也可以做innodb的存储引擎,要锁表,相当于温备了,可以备任何储存引擎,属于温备)
[root@db02 ~]# xtrabackup  


4、配置文件加上socket路径，mysql用户需要有进入这个目录的权限
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
....
#二进制安装的mysql默认在tmp下
socket=/tmp/mysql.sock

[client]
#客户端需要指定socket文件，不然备份的时候会报错
socket=/tmp/mysql.sock

5、准备新环境  db02
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -fr /app/mysql/data
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql-5.6.50 --datadir=/app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start
6、#先给root用户配置一个密码
[root@db02 ~]# mysqladmin -u root -p password 123
Enter password: #直接回车
Warning: Using a password on the command line interface can be insecure.
创建一个用户
root@localhost:(none) > grant all on *.* to root@'172.16.1.%' identified by '123'; 

Xtrabackup介绍
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份/恢复时读取配置文件/etc/my.cnf

全备

热备：备份的时候，可以写数据
温备：备份的时候，只能读，不能写

1、#修改配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
....
#二进制安装的mysql默认在tmp下
socket=/tmp/mysql.sock

[client]
#客户端需要指定socket文件，不然备份的时候会报错
socket=/tmp/mysql.sock


2、#(热备)全备的命令
root无密码 (backup不需要提前创建，会自动创建的)
[root@db02 ~]# innobackupex --user=root /backup
root有密码
[root@db02 ~]# innobackupex --user=root  --password='123' /backup
请注意备份结果是否完成
xtrabackup: Transaction log of lsn (1625997) to (1625997) was copied.
240822 15:16:57 completed OK!

#(锁表温备)全备的命令   如果公司不是inodb的储存引擎。就用温备
[root@db02 ~]# xtrabackup --user-root --password=123 --backup --target-dir=/tmp/full_$(date +%F)
请注意备份结果是否完成
xtrabackup: Transaction log of lsn (1625997) to (1625997) was copied.
240822 15:15:52 completed OK!


3、#查看备份文件
[root@db02 ~]# ll /backup/2024-08-22_15-16-55/
total 12316
-rw-r----- 1 root root      481 Aug 22 15:16 backup-my.cnf   #mysql的配置文件
-rw-r----- 1 root root 12582912 Aug 22 15:16 ibdata1         #共享表空间,undo在里面
drwxr-x--- 2 root root     4096 Aug 22 15:16 mysql
drwxr-x--- 2 root root     4096 Aug 22 15:16 performance_schema
drwxr-x--- 2 root root       20 Aug 22 15:16 test
-rw-r----- 1 root root       21 Aug 22 15:16 xtrabackup_binlog_info  #记录打点备份 备份到的位置点
-rw-r----- 1 root root      135 Aug 22 15:16 xtrabackup_checkpoints #form_lsn  to_lsn  查看增量备份是否衔接
-rw-r----- 1 root root      465 Aug 22 15:16 xtrabackup_info    #这次备份的信息
-rw-r----- 1 root root     2560 Aug 22 15:16 xtrabackup_logfile   #redo

[root@db02 ~]# cat /backup/2024-08-22_15-16-55/xtrabackup_checkpoints
backup_type = full-backuped   #备份类型=全备
from_lsn = 0                  #日志版本号起点：lsn:日志版本号  从0号日志版本号开始备份
to_lsn = 1625997              #日志版本号备份到的点位   备份结束位置：1625997
last_lsn = 1625997            #最新的日志版本号   如果有用户往里面写入数据，这个数据就会比to_lsn大
compact = 0
recover_binlog_info = 0       #是否覆盖二进制的log信息  0：关闭
flushed_lsn = 1625997         #备份完成后获取到的最新lsn
增备的from_lsn 一定是上一次增备的to_lsn,要检查是否衔接上，没有连接上，做的增备数据就不对

#这个文件里面的就好比打点备份  想截binlog 开始位置点就在这里面看
[root@db02 ~]# cat /backup/2024-08-22_16-05-44/xtrabackup_binlog_info 
mysql-bin.000004        24251

全备恢复

恢复全备的条件：
1、被恢复的目录必须是空的(data目录是空的)
2、被恢复的数据实例是关闭的(需要停库)

#全备恢复之前：需要手动模拟CSR的恢复过程，把redo中的数据 重做，undo中的数据  回滚
因为表空间里面的数据是不全的，有一部分在redo,如果只做redo,redo里面存在一下没有提交的数据，undo也要做一遍

1、#先删除一个prod库,再恢复数据
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     1077 |
+----------+

root@localhost:(none) > drop database prod;

2、#因为是物理备份，做redo undo之前需要停止数据库
[root@db02 ~]# /etc/init.d/mysqld stop

3、#模拟CSR恢复过程：把redo中的数据 重做，undo中的数据  回滚，这个命令不需要连接服务端，所以恢复数据的时候可以停库
这个重做要小心谨慎，这个是一次性的，做完了这次备份就不能用了，后面有增量数据，就不能从这个的基础上做增量备份了
innobackupex --apply-log +全备的路径
[root@db02 ~]# innobackupex --apply-log /backup/2024-08-22_16-05-44/

4、#模拟完成，查看目录，多了好几个东西
[root@db02 ~]# ll /backup/2024-08-22_16-05-44/

5、#恢复  清空data目录
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
恢复数据
innobackupex --copy-bak +全备的路径
[root@db02 ~]# innobackupex --copy-back /backup/2024-08-22_16-54-47/
授权
[root@db02 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data
启动
[root@db02 ~]# /etc/init.d/mysqld start
查看数据
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|       53 |
+----------+

----------------------不用这种方法：这个方法是底层原理---------------------
5、#恢复  清空data目录
[root@db02 ~]# rm -rf /app/mysql-5.6.50/data
恢复数据
[root@db02 ~]# mv /backup/2024-08-22_16-05-44/ /app/mysql-5.6.50/data
授权
[root@db02 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data
启动
[root@db02 ~]# /etc/init.d/mysqld start
查看数据
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|       53 |
+----------+
----------------------------------------------------------

1、执行脚本
[root@db02 ~]# sh insert.sh 

2、等待一会 做全备
[root@db02 ~]# innobackupex --user=root --password='123' /backup

3、查看数据里面是否有数据，并删除数据
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|      211 |
+----------+
root@localhost:(none) > drop database prod;

4、等待一会，停止脚本，进行数据的恢复
[root@db02 ~]# sh insert.sh ^C
[root@db02 ~]# /etc/init.d/mysqld stop
模拟CSR
[root@db02 ~]# innobackupex --apply-log /backup/2024-08-22_17-09-10/
模拟完成，查看目录，多了好几个东西
[root@db02 ~]# ll /backup/2024-08-22_17-09-10/
[root@db02 ~]# innobackupex --copy-back /backup/2024-08-22_17-09-10/
[root@db02 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start
查看数据
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|      113 |
+----------+

增量备份和恢复

备份方式
1）基于上一次备份进行增量
2）增量备份无法单独恢复，必须基于全备进行恢复
3）所有增量必须要按顺序合并到全备当中

1、备份

1、执行脚本
[root@db02 ~]# sh insert.sh 

2、全备  等待一会 做全备不带时间戳，并查看位置点
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 3252565
last_lsn = 3260144

3、第一次增量备份
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22 /backup/inc1_$(date +%F-%H)
[root@db02 ~]# cat /backup/inc1_2024-08-22-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3252565   #要和全备的to_lsn一样
to_lsn = 3473250
last_lsn = 3477650

4、第二次增备
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1_2024-08-22-17 /backup/inc2_$(date +%F-%H)
[root@db02 ~]# cat /backup/inc2_2024-08-22-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3473250   #要和inc1的to_lsn一样
to_lsn = 3540803
last_lsn = 3554601


5、第三次增备
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc2_2024-08-22-17 /backup/inc3_$(date +%F-%H)
[root@db02 ~]# cat /backup/inc3_2024-08-22-18/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3540803   #要和inc2的to_lsn一样
to_lsn = 3576288
last_lsn = 3590568

6、第四次增备
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc3_2024-08-22-18 /backup/inc4_$(date +%F-%H)
[root@db02 ~]# cat /backup/inc4_2024-08-22-18/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3576288
to_lsn = 3625448
last_lsn = 3626012

第四次增量备份之后的数据只能去binlog里面去查找

[root@db02 ~]# ll /backup
drwxr-x--- 8 root root  268 Aug 22 17:42 full_2024-08-22
drwxr-x--- 7 root root  267 Aug 22 17:53 inc1_2024-08-22-17
drwxr-x--- 7 root root  267 Aug 22 17:58 inc2_2024-08-22-17
drwxr-x--- 7 root root  267 Aug 22 18:00 inc3_2024-08-22-18
drwxr-x--- 7 root root  267 Aug 22 18:02 inc4_2024-08-22-18

当前系统时间-1h
date +%F-%H -d '-1hour'
date +%F-%H -d '-1day'
date +%F-%H -d '-1minute'

2、恢复

1）full+inc1+inc2+inc3+inc4
2）需要将inc1和inc2按顺序合并到full中
3）分步骤进行–apply-log

1、#先查看里面有多少数据，再删除prod库
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     2181 |
+----------+
root@localhost:(none) > drop database prod;
脚本开始报错无法写入数据，停止脚本即可
[root@db02 ~]# sh insert.sh ^C

2、#停止数据库
[root@db02 ~]# /etc/init.d/mysqld stop

3、#挪走data目录
[root@db02 ~]# mv /app/mysql-5.6.50/data /tmp/

4、#第一步：在全备中apply-log时，只应用redo，不应用undo    把增备按照顺序合并到全备
全备模拟CSR只做redo，不做undo,因为一条数据写进来，没有提交全备做完了，但在下一个增备里面提交了，如果这个时候把undo做了，数据就回滚了
innobackupex --apply-log --redo-noly /backup/全备
[root@db02 ~]# innobackupex --apply-log --redo-only /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_checkpoints
backup_type = log-applied  #状态变了
from_lsn = 0
to_lsn = 3252565
last_lsn = 3260144
#查看全备的位置点有无变化并记录下来
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001       

5、#第二步：合并inc1合并到full中，并且apply-log，只应用redo，不应用undo
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1_2024-08-22-17 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc1_2024-08-22-17/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3252565
to_lsn = 3473250
last_lsn = 3477650
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001  

6、#第三步：合并inc2合并到full中，只做redo，不做undo
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2_2024-08-22-17 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc2_2024-08-22-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 3473250
to_lsn = 3540803
last_lsn = 3554601
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001  

7、#第四步：合并inc3合并到full中，只做redo，不做undo
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc3_2024-08-22-18 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc3_2024-08-22-18/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3540803
to_lsn = 3576288
last_lsn = 3590568
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001  

8、#第五步：合并inc4（最后一次）合并到full中，redo和undo都做
[root@db02 ~]# innobackupex --apply-log --incremental-dir=/backup/inc4_2024-08-22-18 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc4_2024-08-22-18/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3576288
to_lsn = 3625448
last_lsn = 3626012
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001  

9、#将全备--apply-log，redo和undo都应用
[root@db02 ~]# innobackupex --apply-log /backup/full_2024-08-22/

10、#恢复数据
[root@db02 ~]# innobackupex --copy-back /backup/full_2024-08-22/

11、#授权  启动
[root@db02 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start

12、#连接进去查看一下数据是否对得上
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     1517 |
+----------+  #可以看到数据少了，还有一部分数据，在binlog里面

13、#截取binlog
合并完了，这个binlog的位置点也会合并
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001        282633
开始位置点：282633

由于之前的data目录被挪到tmp了
[root@db02 ~]# mysqlbinlog --base64-output=decode-rows /tmp/data/mysql-bin.000001 |grep  -iC 10 'drop database'
结束位置点：407253

[root@db02 ~]# mysqlbinlog --start-position=282633 --stop-position=407253 /tmp/data/mysql-bin.000001 > /tmp/inc5.sql

14、#进入数据库
关闭binlog,防止binlog被污染，让binlog从120开始
root@localhost:(none) > set sql_log_bin=0;

导入数据
root@localhost:(none) > source /tmp/inc5.sql

开启binlog
root@localhost:(none) > set sql_log_bin=1;

查看数据，已经恢复
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     2187 |
+----------+


增量备份挺麻烦的 所以企业里面都是做差异备份
但是差异备份比较占磁盘空间

企业级增量恢复实战

背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M

备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。

故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？？？

#全备+增备+binlog

1、模拟备份删数据

1、#执行写数据的脚本
[root@db02 ~]# sh insert.sh 

2、#等待一会，全备 (周六)
[root@db02 ~]# rm -rf /backup/*
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 4215962


3、#等待一会，增备inc1 (周日)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc1_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc1_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4215962
to_lsn = 4333233


4、#等待一会，增备inc2 (周一)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1_2024-08-22-20/ /backup/inc2_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc2_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4333233
to_lsn = 4395658

5、#等待一会，增备inc3 (周二)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc2_2024-08-22-20/ /backup/inc3_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc3_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4395658
to_lsn = 4411035

6、#等待一会，增备inc4 (周三)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc3_2024-08-22-20/ /backup/inc4_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc4_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4411035
to_lsn = 4455757

7、#周三下午2点出现数据库意外删除表操作，先查看数据，再删表
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     1199 |
+----------+
root@localhost:(none) > drop table prod.prod;
停止脚本运行
[root@db02 ~]# sh insert.sh ^C

2、恢复

1、#停止数据库
[root@db02 ~]# /etc/init.d/mysqld stop

3、#挪走data目录
[root@db02 ~]# mv /app/mysql-5.6.50/data /tmp/

2、#第一步：在全备中apply-log时，只应用redo，不应用undo
[root@db02 ~]# innobackupex --apply-log --redo-only /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 4215962
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001        55527

3、#第二步：合并inc1合并到full中，并且apply-log，只应用redo，不应用undo  (周日和周六合并)
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1_2024-08-22-20 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc1_2024-08-22-20/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 4215962
to_lsn = 4333233
[root@db02 ~]# cat /backup/inc1_2024-08-22-20/xtrabackup_binlog_info 
mysql-bin.000001        112629

4、#第三步：合并inc2合并到full中，只做redo，不做undo    (周一和周六合并)
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2_2024-08-22-20 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc2_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4333233
to_lsn = 4395658
[root@db02 ~]# cat /backup/inc2_2024-08-22-20/xtrabackup_binlog_info 
mysql-bin.000001        144249

5、#第四步：合并inc3合并到full中，只做redo，不做undo    (周二和周六合并)
[root@db02 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc3_2024-08-22-20 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc3_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4395658
to_lsn = 4411035
[root@db02 ~]# cat /backup/inc3_2024-08-22-20/xtrabackup_binlog_info 
mysql-bin.000001        163965

6、#第五步：合并inc4（最后一次）合并到full中，redo和undo都做   (周三和周六合并)
[root@db02 ~]# innobackupex --apply-log --incremental-dir=/backup/inc4_2024-08-22-20 /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/inc4_2024-08-22-20/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4411035
to_lsn = 4455757
[root@db02 ~]# cat /backup/inc4_2024-08-22-20/xtrabackup_binlog_info 
mysql-bin.000001        186099

7、#将全备--apply-log，redo和undo都应用
[root@db02 ~]# innobackupex --apply-log /backup/full_2024-08-22/

8、#恢复数据
[root@db02 ~]# innobackupex --copy-back /backup/full_2024-08-22/

9、#授权  启动
[root@db02 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start

10、#连接进去查看一下数据是否对得上
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|      998 |
+----------+  #可以看到数据少了，还有一部分数据，在binlog里面

11、#截取binlog
合并完了，这个binlog的位置点也会合并
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001        186099
开始位置点：186099
由于之前的data目录被挪到tmp了
[root@db02 ~]# mysqlbinlog --base64-output=decode-rows /tmp/data/mysql-bin.000001 |grep  -iC 10 'drop table'
结束位置点：223640

[root@db02 ~]# mysqlbinlog --start-position=186099 --stop-position=223640 /tmp/data/mysql-bin.000001 > /tmp/inc5.sql

12、#进入数据库
关闭binlog,防止binlog被污染，让binlog从120开始
root@localhost:(none) > set sql_log_bin=0;

导入数据
root@localhost:(none) > source /tmp/inc5.sql

开启binlog
root@localhost:(none) > set sql_log_bin=1;

查看数据，已经恢复
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|     1199 |
+----------+

xtrabackupex进行差异备份

1、模拟备份删数据

1、#执行写数据的脚本
[root@db02 ~]# sh insert.sh 

2、#等待一会，全备 (周六)
[root@db02 ~]# rm -rf /backup/*
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 4915321


3、#等待一会，差异备inc1 (周日)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc1_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc1_2024-08-22-21/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 4915321
to_lsn = 4987544


4、#等待一会，差异备inc2 (周一)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc2_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc2_2024-08-22-21/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 
to_lsn = 

5、#等待一会，差异备inc3 (周二)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc3_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc3_2024-08-22-21/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 
to_lsn = 

6、#等待一会，差异备inc4 (周三)
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc4_$(date +%F-%H)

[root@db02 ~]# cat /backup/inc4_2024-08-22-21/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 
to_lsn = 

7、#周三下午2点出现数据库意外删除表操作，先查看数据，再删表
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|      497 |
+----------+
root@localhost:(none) > drop table prod.prod;
停止脚本运行
[root@db02 ~]# sh insert.sh ^C

2、恢复

1、#停止数据库
[root@db02 ~]# /etc/init.d/mysqld stop

3、#挪走data目录
[root@db02 ~]# mv /app/mysql-5.6.50/data /tmp/

2、#第一步：在全备中apply-log时，只应用redo，不应用undo
[root@db02 ~]# innobackupex --apply-log --redo-only /backup/full_2024-08-22/

[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 4915321
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001        9399

3、#第二步：合并inc4（最后一次的差异备份）到full中，redo和undo都做   (周三和周六合并)
[root@db02 ~]# innobackupex --apply-log --incremental-dir=/backup/inc4_2024-08-22-21 /backup/full_2024-08-22/


[root@db02 ~]# cat /backup/inc4_2024-08-22-21/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4915321
to_lsn = 5067923
[root@db02 ~]# cat /backup/inc4_2024-08-22-21/xtrabackup_binlog_info 
mysql-bin.000001         85473

4、#将全备--apply-log，redo和undo都应用
[root@db02 ~]# innobackupex --apply-log /backup/full_2024-08-22/

5、#恢复数据
[root@db02 ~]# innobackupex --copy-back /backup/full_2024-08-22/

6、#授权  启动
[root@db02 ~]# chown -R mysql.mysql /app/mysql-5.6.50/data
[root@db02 ~]# /etc/init.d/mysqld start

7、#连接进去查看一下数据是否对得上
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|      457 |
+----------+  #可以看到数据少了，还有一部分数据，在binlog里面

8、#截取binlog
合并完了，这个binlog的位置点也会合并
[root@db02 ~]# cat /backup/full_2024-08-22/xtrabackup_binlog_info 
mysql-bin.000001        85473
开始位置点：85473
由于之前的data目录被挪到tmp了
[root@db02 ~]# mysqlbinlog --base64-output=decode-rows /tmp/data/mysql-bin.000001 |grep  -iC 10 'drop table'
结束位置点：93068

[root@db02 ~]# mysqlbinlog --start-position=85473 --stop-position=93068 /tmp/data/mysql-bin.000001 > /tmp/inc5.sql

9、#进入数据库
关闭binlog,防止binlog被污染，让binlog从120开始
root@localhost:(none) > set sql_log_bin=0;

导入数据
root@localhost:(none) > source /tmp/inc5.sql

开启binlog
root@localhost:(none) > set sql_log_bin=1;

查看数据，已经恢复
root@localhost:(none) > select count(*) from prod.prod;
+----------+
| count(*) |
+----------+
|      497 |
+----------+  #差异备份恢复完成