MySQL备份与恢复

数据库的备份和恢复

备份 完全备份 增量备份

完全备份 将整个数据库完整的进行备份

增量备份 在完全备份的基础上 对后续新增的内容进行备份

备份的需求

在生产环境中 数据的安全至关重要 任何数据的丢失都可能产生非常严重的后果

冷备份 关机备份 停止mysql服务 进行备份

热备份 开机备份 无需关闭MySQL服务 进行备份

物理备份 对数据库系统的物理文件（数据文件 日志文件）进行备份

逻辑备份 只是对数据库的逻辑组件进行备份 （表结构）以sql语句形式 把库 表结构 表数据进行备份保存（直接在数据库系统中删除全部文件 逻辑备份无法恢复）

物理备份：完全备份 对整个数据库进行完整的打包备份 把服务关闭 避免有新数据被覆盖

优点：操作简单

缺点：数据库文件占用量很大 备份和恢复的时间都很长 需要停止MySQL服务

热备份的逻辑备份 MySQL自带工具 mysqldunp

备份单个库：

Mysqldump -u root -p1234567 --databases 库名 > /opt/库名.sql

备份多个库：

Mysqldump -u root -p1234567 --databases 库名 库名 > /opt/库名.sql

备份所有库

Mysqldump -u root -p1234567 --alldatabases> /opt/库名.sql

Mysql -u root -p123456 -e ‘show databases;’

备份表

物理冷备份和物理热备份

特点 简单

数据量 占用的备份空间较大

MySQL dump 自带的备份文件命令

特点 方便 简单 只能基于逻辑上的表结构和表数据恢复 物理删除只会再用逻辑恢复会报错 也可以做为数据迁移 占用空间大

增量备份：

MySQL dump支持增量备份 没有重复数据 备份量小 时间短

MySQL dump增量备份恢复期间 表会锁定

缺点：备份时锁表 影响业务 超过10G耗时长 导致服务不可用

过程：

MySQL提供的二进制日志间接的实现增量备份

MySQL二进制日志记录格式有三种

1. STATEMNET：基于sql语句 记录修改的sql语句 在高并发情况下 记录sql语句的顺序可能会出错 恢复数据时可能会导致丢失和误差 效率比较高 不适合高并发
2. ROW：基于行 精准记录每一行数据 准确率高 但恢复效率低
3. MIXED：既可以根据sql语句 也可以根据行 在正常情况下 使用STATEMENT 一旦发生高并发 会智能自动切换ROW模式

改配置文件 /etc/my.cnf

log-bin=mysql-bin

binlog_format=MIXED

cd /usr/local/mysql/data ;

mysqlbinlog –no-defaults –base64-outpput=decode-rows -v mysql-bin.000001

查看备份的二进制文件内容

MySQLadmin -u root -p flush-logs 刷新 生成断点文件

如何恢复？

Mysqlbinlog –no-defaults mysql-bin.000001 | mysql -u root -p 根据断点文件恢复

1234567

位置恢复和时间恢复：位置点打开000001文件是at后面的数字

1. 从某一个点开始恢复到最后

Mysqlbinlog –no-defaults --start-position=’位置点’ 文件名 | mysql -u root -p

1. 从开头一直恢复到某个位置

Mysqlbinlog –no-defaults --stop-position=’位置点’ 文件名 | mysql -u root -p

3、从指定点到指定结束点

Mysqlbinlog –no-defaults --start-position=’位置点’ --stop-position=’位置点’文件名 | mysql -u root -p

时间点恢复：时间点要用标准格式2023-11-06 XX:XX:XX 时间严格按照文件中

1. 从某个时间点开始

MysqlBinlog –no-defaults --start-datetime=’时间点’ 文件名 | mysql -u root -p

1. 从开头到指定的结尾时间点

MysqlBinlog –no-defaults --stop-datetime=’时间点’ 文件名 | mysql -u root -p

1. 指定时间范围

MysqlBinlog –no-defaults --start-datetime=’时间点’ –stop-datetime=’时间点’ 文件名 | mysql -u root -p

在生产中 通过binlog进行增量恢复 只需要对binlog文件进行备份 随时可以进行备份和恢复

MySQL的默认日志如何打开？

Log-error = /usr/local/mysql/data/mysql_error.log 错误日志单独存放

General_log=ON

General_log_file=/usr/local/mysql/data/mysql_general/log 一般查询日志记录

Slow_query_log=ON  慢查询记录（查询超过五秒的）

Slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

Long_query_time=5

show variables like 'general%';

作业：

如何把本地的数据库迁移上云

Sql文件 如果复制到另外一个库 不是重命名的库 是否会被覆盖 相同的表名会不会覆盖 相同的库名会不会覆盖

写shell脚本 每个月20号对数据库打一个端点 断点之后自动进行增量备份