MySQL-备份（三）

备份作用：保证数据的安全和完整。

一 备份类别

类别	物理备份 xtrabackup	逻辑备份mysqldump
对象	数据库物理文件	数据库对象（如用户、表、存储过程等）
可移植性	差，不能恢复到不同版本mysql	对象级备份，可移植性强
占用空间	占用空间大	占用空间小
恢复效率	效率高（上百G建议使用）	效率低
适用场景	大型业务系统的容灾恢复、系统级全量备份	主备数据库之间的增量数据备份、 不同业务系统之间数据同步、 业务不中断升级过程中数据迁移

二 逻辑备份具体操作

mysqldump连接参数

• -u mysql用户名
• -p mysql用户密码
• -S mysql本地socket文件
• -h 指定主机地址
• -P 指定mysql端口

使用mysqldump命令进行数据备份_mysqldump备份数据库-CSDN博客

mysqldump备份参数

全量备份：导出所有库、表、数据，转存储为SQL文件  --all--databases, -A 转储所有数据库中的所有表

指定备份库下的多个表

指定哪些数据库，进行备份

备份单个数据表

备份多个表

只备份表结构  --no-data参数

只备份数据

三 锁表

基于会话锁表，防止他们冲突写入数据库

单独锁某个表
mysql> lock table  a.city write;
mysql> unlock tables;

全局读锁，针对普通用户的写入权限
set global read_only=1;
set global read_only=0;

四 二进制binlog日志

作用：将 增删改 SQL语句记录下来（insert、update、delete、create、drop、alter、等关键字）

1. 记录mysql数据的增量数据，用于增量数据库恢复
2. 主从数据复制

开启：配置文件中设置

#查看binlog日志
show binary logs;
#模拟binglog日志更新，随便插入数据，查看position变化
show master status;

binlog二进制转为可读性日志（使用mysqlbinlog --base64-output=decode-rows -vv 二进制日志 > x.txt）

./mysqlbinlog --base64-output=decode-rows -vv   /data/mysql/data/atguigu-bin.000263> /tmp/decode-mysql-log.txt

五 拓展

mysql默认使用引擎是innodb，支持事务
开启二进制日志后，每次进行 增删改 操作，都会被计入，其position会变化

参考文档 ：
MySQL基于xtrabackup、binlog实现全量备份、增量备份_extrabackup binlog-CSDN博客

使用mysqldump命令进行数据备份_mysqldump备份数据库-CSDN博客

六 多binlog日志恢复数据

模拟题：

1.先创建一个库a，flush logs（生成新的binlog日志）
2.创建一个表b，flush logs
3.插入数据到a.b表，flush logs
4.删除表b

如何恢复表b？
1.先停止binlog记录 ：mysql> set sql_log_bin=0;
2.查找对应binlog日志，以及postitons:
mysqlbinlog  --start-position=XX --stop-position=XX mysql-log-bin.000007 日志2 日志3 > /tmp/1.sql 在source恢复
3.查看数据恢复，重新开启binlog记录

七、基于GTID的binlog应用

六中需要我们，眼睛去观察每个事务操作的postion值，比较麻烦，从mysql5.7开始提供了更易于数据恢复的GTID模式。

1. 通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。
2. 这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
3. 在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量pos值进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。

 GTID (Global Transaction IDentifier) 是全局事务标识。它具有全局唯一性，一个事务对应一个GTID。

查看事务：show binlog events in "日志名";

开启（必备参数）：

[mysqld]
gtid-mode=ON
enforce-gtid-consistency=true
log-slave-updates=ON
server_id=1

每个事务有一个id，不需要特别麻烦的去看每个事务的postion范围

mysqlbinlog  --skip-gtids  --include-gtids='65c03c38-b158-11ee-915b-000c291d9a97:1-293-294' mysql-log-bin.000263  > /opt/2.sql