Mysql数据备份与恢复( 三 ) _Mysql

根据备份后的文件内容可以划分为：

逻辑备份，数据库执行的 sql 内容
文件备份，备份数据库的物理文件

一般我们会定时对数据执行备份脚本，然后将备份的内容压缩发送到存储文件的服务器，比如 OSS 。
备份与恢复使用到程序

mysqldump，对数据库进行不停机执行逻辑备份及恢复
mysqlbinlog，操作 binlog 日志，使数据恢复到某个时间点的数据
xtrabackup，percona 开源工具，对数据库不停机进行文件备份

mysqldump 使用备份某些数据库mysqldump --master-data --single-transaction --databases ceshi2 ceshi -h10.211.55.8 -uroot -pMysql@12345678> backup.sql备份所有数据库mysqldump --master-data --single-transaction --all-databases -h10.211.55.8 -uroot -pMysql@12345678> backup.sql参数说明

--single-transaction 用于全是 InnoDB 表的备份。备份开始执行前 START TRANSACTION 会开启事务，由于 MVCC 的特性这种备份不会影响数据库读写，而且还保证了备份期间数据的一致性
--master-data 为 1 时记录 CHANGE MASTER 语句，可以在从库中使用备份的文件，比如新增加一个从库，就可以在从库上执行这个备份的数据。为 2 时会注释 CHANGE MASTER 。
--lock-tables 锁住单个数据库中所有表，只允许读取数据。为了保证备份时数据的一致性。因为只能锁住单个数据库，如果有多个数据库就不能保证数据的一致性了。当数据库采用的存储引擎既有 InnoDB 和 MyISAM 时需要使用这个属性
--lock-all-tables 锁住备份所有数据库的表，能保证多个数据库数据的一致性。
--databases 可以指定备份哪些数据库实例
--all-databases 备份连接中所有的数据库实例。
--evnets 备份事件调度器
--routines 备份存储过程和存储函数
--triggers 备份触发器
--flush-logs 导出之前刷新日志，因为有的数据在内存中，可能还没有写入到二进制日志中

mysqlbinlog 使用mysqlbinlog 可以解析 binlog 生成 sql语句。

# 在本地生成 sqlmysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sqlmysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql# 根据日志的位置mysqlbinlog binlog.000019 --disable-log-bin --start-position 775 > 775.sqlmysqlbinlog binlog.000019 --disable-log-bin --start-position 477 --stop-position 556 > 477-556.sql# 根据时间mysqlbinlog binlog.000019 --start-date='2017-12-19 10:10:00' --stop-date='2017-12-19 18:52:00' > aa.sql# 链接远程使用mysqlbinlog --disable-log-bin --read-from-remote-server--host=10.211.55.8 --user=root --password=Mysql@12345678 binlog.000019 binlog.000020> remote_test.sql

--start-position 指定从哪个位置开始
--stop-position 指定从哪个位置开始
--start-datetime 指定开始时间
--stop-datetime 指定结束时间
--disable-log-bin 生成的 sql 语句中，添加 SET SQL_LOG_BIN=0 ,执行转换的 sql 时，不会生成二进制日志
--read-from-remote-server 从远程服务器读取

数据恢复一般我们会使用 mysqldump 进行一个全量备份，在这个全量备份的基础上，从 binlog 提取后续 sql 进行数据恢复。
模拟一个场景1、比如我们在某个 2020-11-28 16:30:00 进行了全量备份。
2、2020-11-28 16:35:00 删除了 account 表中全部数据
3、删除之后不知道，又插入了两条数据

INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '删除全库之后插入', 11);INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111112, 'asdfasd', 12);

恢复数据的时候，为避免恢复操作写入到二进制日志中去，需要暂时关闭二进制日志，恢复会话期间不写入二进制日志
SET SQL_LOG_BIN=0;SHOW VARIABLES LIKE '%sql_log_bin%';现在开始对数据库进行数据恢复