MySql8.0数据备份与恢复

以 SQL 格式文件转储并恢复

# 备份指定数据库实例
sudo mysqldump  -u 用户账号 -p用户密码 数据库实例名称 > 数据库实例名称.sql
# 备份指定表（1千万条数据大概占用1G磁盘空间）
sudo mysqldump  -u 用户账号 -p用户密码 数据库实例名称 表名 > 表名.sql

# 恢复数据
sudo mysql  -u 用户账号 -h 登陆主机ip -p用户密码 数据库实例名称 < /var/lib/mysql-files/已备份文件名称.sql

以分隔文本格式文件转储并恢复

show global variables like '%secure%';

# 默认指定导出目录：/var/lib/mysql-files/
sudo mysqldump -u 用户账号 -p密码 --tab=/var/lib/mysql-files/ 数据库实例
sudo mysqldump -u 用户账号 -p密码 --tab=/var/lib/mysql-files/ 数据库实例 表名

sudo mysqlimport -u 用户账号 -p密码 数据库实例名称 /var/lib/mysql-files/已备份文件名称.txt

使用二进制日志的时间点恢复

mysqlbinlog具有根据事件时间或日志中事件位置选择相应二进制日志并将二进制格式转换为文本

• 确定数据被删除或修改的开始时间

# 登陆mysql
mysql -u用户账号 -p用户密码
# 查看日志文件列表，默认最后一个是最新的
SHOW BINARY LOGS; 
# 刷新binlog日志，生产新的日志文件便于测试
# flush logs; 
# 查看正在使用的日志文件
# SHOW MASTER STATUS; 

# 查看日志中的操作记录，如果日志太多建议下载到本地查看
show binlog events in 'binlog.000020'; 

# 下载日志（linux命令行下输入）到本地
sudo mysqlbinlog /var/lib/mysql/binlog.000020 > /home/lixing/binlog.000020
# 下载日志（linux命令行下输入）到本地【显示编码为 base-64 字符串】
mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/binlog.000022 > /home/lixing/binlog.000022
# 下载指定位置的日志到本地
sudo mysqlbinlog /var/lib/mysql/binlog.000020 --start-position=236 --stop-position=485 > /home/lixing/binlog.000020

根据日志可以看出：2022-04-09 20:39:34删除了一条数据，所以我们需要先恢复到最近一次的备份数据（2022-04-09凌晨的备份数据），然后跳过binlog.000020日志的236-485行并应用该日志

• 还原最近一次备份

1、以 SQL 格式文件转储形式的恢复
2、以分隔文本格式文件转储形式的恢复
注意：如果是误删除、误更新的操作，需要先将目标表进行备份然后清空数据再进行恢复
CREATE TABLE table1 SELECT * FROM table2; -- 创建表1，同时拷贝表2的结构和数据

• 应用二进制日志中的事件
  应用二进制日志中的事件,重新执行它们所表示的数据修改,这样就可以在给定的时间跨度内恢复数据更改

# 对二进制日志中指定位置进行应用（跳过误删除、误更新的位置）
mysqlbinlog --stop-position=236 /var/lib/mysql/binlog.000020 | mysql -u用户账号 -p账号密码 
mysqlbinlog --start-position=485 /var/lib/mysql/binlog.000020 | mysql -u用户账号 -p账号密码