mysql 数据备份与数据恢复

数据备份

mysqldump

源库生成insert…values()语句，到从库上执行插入

// 源库
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
// 目标库
mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

导出 CSV 文件

// 源库
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
// 目标库
load data infile '/server_tmp/t.csv' into table db2.t;

select…into outfile不会生成表结构文件，表结构单独用mysqldump导出

mysqldump -h$host -P$port -u$user ---single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

物理备份

create table t2 like t1; // 创建相同表结构的空表
alter table t2 discard tablespace; // 删除t2的.ibd文件（.ibd文件是用来存数据的）
flush table t1 for export; // 创建t.cfg文件
cp t1.cfg t2.cfg;cp t1.idb t2.ibd; // 拷贝文件
unlock tables; // 删除t1.cfg文件
alter table t2 import tablespace; // 将t2.ibd文件作为t2的新的表空间，那t2就有和t1一样的数据了

三种备份方式对比

• mysqldump最简单，支持简单的where筛选，不支持join等复杂语句
• select…into outfile最灵活，支持所有sql写法，但每次只能导出一张表数据，表结构需要单独备份
• 物理拷贝最快，但只能全表拷贝，只能再服务器上拷贝，要求源库、目标库使用相同的而存储引擎

数据如何恢复

• 数据库定期整库备份
• binlog=row，binlog_row_image=FULL 用于恢复数据
• 预防误删库：创建延时备库

预防误删操作

• 账号分离，权限粒度最小

• 规范操作

  删除表前先给表改名，比如该成固定后缀_to_be_deleted，观察一段时间没问题再删

  删库、表走OA

误删数据，如何恢复

恢复操作，不要直接在主库上改；先找个备库改好，确认没问题再切成主库。

delete误删行

Flashback（工具）闪回恢复，原理：应用binlog (delete语句改成insert语句)

事前预防：禁止不带where条件的删除语句 sql_safe_updates=on

drop/truncate table误删表

步骤：

• 取最近一次全量备份，比如一天一备，恢复出一个当天0点的临时库

• 在从binlog中取出0点后的日志，应用误删数据前的日志和误删数据后（找对应的GTID）的日志

  注意：mysqldump是单线程的、而且回放binlog时不仅只回放这个表而是全部，所以会比较慢

• 在切成主库

优化

1. mysqlbinlog -database 指定回放数据库，避免应用其他库的日志

2. 利用从库并行复制能力

   将这个全量备份后的临时库设置成线上备库的从库。在start slave之前，先执行change replication filter replicate_do_table=(tbl_name)，让临时库只同步误操作的表；这样也会用上从库的并行复制能力

   如果时间过了太久，线上备库没有完整的binlog，我们可以把缺的binlog从binlog备份中找到塞到线上备库里，然后建立主备关系

   塞进去方法：

   • 从备份系统找到缺失的文件，放到线上备库的日志目录下
   • 打开日志目录的master.index文件，在开头加上缺失文件的路径；重启备库 => 让备库重新识别两个日志文件

binlog必须row格式的场景

binlog主要用于主备同步，binlog=row 日志具体，可以进行数据恢复

间隙锁

在可重复读级别下有间隙锁，保证并发事务一致性。正读提交级别下，为保证并发数据插入顺序和日志回放数据一致，需要设置binlog=row，显示具体操作的哪行。

详见：mysql中的锁-CSDN博客的间隙锁部分

delete…limit

delete … limit 1 可能使用的索引不同，可能主备数据不一致，导致删除的行不同。通过binlog=row 明确删除哪行。这个binlog=mixed也可以做到。

binlog=mixed: 对会造成主备不一致的语句用row格式，其他用statement格式

恢复数据

flashback工具恢复误删行依赖于binlog=row，binlog_row_image=FULL

主备复制使用按行复制

主备复制使用按行复制模式需要binlog=row才知道行信息

详见：mysql 可用性的保障机制：主讲主从复制机制-CSDN博客的从库复制策略：按行分发worker

临时表

这点是差异，不是必须选binlog=row的原因

当事务使用临时表时，系统会在会话推出时在binlog中添加删除临时表的语句，不然从库binlog回放就没删。而binlog=row用的是对数据的具体操作，不会创建临时表，就不用管了。

自增ID锁 innodb_autoinc_lock_mode=2

为提高并发插入能力，可行的配置：innodb_autoinc_lock_mode=2(自增锁用完立即释放) + binlog=row(保证数据一致)。

详见：mysql中的自增ID-CSDN博客的innodb_autoinc_lock_mode