MySQL备份与恢复

一、备份

1.1 数据备份的重要性

1.2 备份的分类

1.2.1 物理备份

1.2.2 逻辑备份

1.2.3 备份策略

1.3 常见的备份方法

1.3.1 物理冷备

1.3.2 专用备份工具mydump或mysqlhotcopy

1.3.3 启用二进制日志进行增量备份

1.3.4 第三方工具备份

1.4 MySQL 完全备份

1.4.1 优缺点

1.4.2 分类

1.4.3 相关命令

1.5 日志

1.6 MySQL 增量备份

1.6.1 修改mysql配置文件/etc/my.cnf并重启服务

1.6.2  进行完全备份

1.6.3 进行增量备份操作，生成新的二进制日志文件

1.6.4 插入新数据，以模拟数据的增加或变更

二、恢复

2.1 完全备份恢复

2.1.1 解压恢复

2.1.2 mysqldump 备份恢复

2.1.3 使用source命令恢复数据

2.2 增量备份恢复

2.2.1 一般恢复

2.2.2 基于位置恢复

2.2.3 基于时间点恢复

2.2.4 位置恢复案例

2.2.5 时间恢复案例

三、小结

---

一、备份

1.1 数据备份的重要性

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

造成数据丢失的原因：

      1） 程序错误

      2）人为操作错误

      3)   运算错误

      4)   磁盘故障

      5)    灾难(如火灾、地震)和盗窃

1.2 备份的分类

1.2.1 物理备份

对数据库操作系统的物理文件(如数据文件日志文件等)的备份。

方法:

        1）冷备份(脱机备份):是在关闭数据库的时候进行的。

        2）热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件。

        3）温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。

1.2.2 逻辑备份

对数据库逻辑组件(如:表等数据库对象)的备份。

1.2.3 备份策略

（1）完全备份:每次对数据库进行完整的备份。

（2）差异备份:备份自从上次完全备份之后被修改过的文件。

（3）增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份。

1.3 常见的备份方法

1.3.1 物理冷备

备份时数据库处于关闭状态，直接打包数据库文件。

备份速度快，恢复时也是最简单的。

1.3.2 专用备份工具mydump或mysqlhotcopy

mysqldump常用的逻辑备份工具。

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表。

1.3.3 启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志。

1.3.4 第三方工具备份

免费的MySQL热备份软件Percona XtraBackup。

1.4 MySQL 完全备份

1.4.1 优缺点

优点
备份与恢复操作简单方便。

缺点
数据存在大量的重复，占用大量的备份空间，备份与恢复时间长。

1.4.2 分类

物理冷备份与恢复

（1）关闭MySQL数据库

（2）使用tar命令直接打包数据库文件夹

（3）直接替换现有MySQL目录即可

mysqldump备份与恢复

（1）MySQL自带的备份工具，可方便实现对MySQL的备份

（2）可以将指定的库、表导出为SQL脚本

（3）使用命令mysql导入备份的数据

1.4.3 相关命令

1.物理冷备份与恢复

压缩备份

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz  /usr/local/mysql/data/

mv /usr/local/mysql/data/    /opt/

2. mysqldump 备份（温备份）

mysqldump -uroot -p123456 --databases ky35 > /opt/ky35_01.sql 

注意：当备份时不加 --databases，表示针对ky35库下的所有表

mysqldump -uroot -p123456 ky35 > /opt/ky35_02.sql

1.5 日志

MySQL 的日志默认保存位置为/usr/local/mysql/data

编辑配置文件 ：vim /etc/my.cnf

[mysqld]

（1）错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

log-error=/usr/local/mysgl/data/mysql_error.log  #指定日志的保存位置和文件名

（2）通用查询日志，用来记录MySOL的所有连接和语句，默认是关闭的

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

（3）二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

log-bin mysql-bin 或 log_bin=mysql-bin

（4）中继日志

一般情况下在Mysql主从同步(复制)、读写分离集群的从节点开启。主节点一般不需要这个日志。

（5）#慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些査询语句执行时间长，以便提醒优化，默认是关闭的

slow_query_log=ON

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

long_query_time=5#设置超过5秒执行的语句被记录，缺省时为10秒

1.6 MySQL 增量备份

1.6.1 修改mysql配置文件/etc/my.cnf并重启服务

1.6.2  进行完全备份

增量备份是基于完全备份的

1.6.3 进行增量备份操作，生成新的二进制日志文件

1.6.4 插入新数据，以模拟数据的增加或变更

二、恢复

2.1 完全备份恢复

2.1.1 解压恢复

tar Jxvf /opt/mysql_all_2024-03-25.tar.xz  -C  /usr/local/mysql/data/

cd /usr/local/mysql/data

mv /usr/local/mysql/data/*./

2.1.2 mysqldump 备份恢复

#库

mysql -uroot -p123456

drop database ky35;

exit

mysql -uroot -p123456 < /opt/ky35_01.sql

#所有表

mysql -uroot -p123456

drop database ky35;

create database ky35;

exit

mysql -uroot -p123456 ky35 < /opt/ky35_02.sql

2.1.3 使用source命令恢复数据

2.2 增量备份恢复

2.2.1 一般恢复

将所有备份的二进制日志内容全部恢复

2.2.2 基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

发生错误节点之前的一个节点，上一次正确操作的位置点停止

2.2.3 基于时间点恢复

跳过某个发生错误的时间点实现数据恢复

在错误时间点停止，在下一个正确时间点开始

2.2.4 位置恢复案例

2.2.5 时间恢复案例

三、小结

物理冷备份：关闭mysald 服务，tar命令打包data目录，恢复就是直接解压tar包。

逻辑备份：

mysqldump -u -p --databases 库1库2 > xxx.sql

mysqldump -u -p --all-databases > xxx.sql

mysqldump -u -p 库1表1表2  >  xxx.sql

完全恢复:

mysql -u -p < xxx.sql 恢复库

mysq  -u -p  库名 < xxx.sql 恢复表

增量备份:

1.开启二进制日志，设置二进制日志格式MIXED

2.进行一次完全备份，可每周备份一次，通过crontab-e

3.使用mysqladmin -uroot -p flush-logs刷新分割出二进制日志文件，由于刷新之前的数据都会记录在老的二进制日志里。

4、可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件名，查看日志内容

5、可以通过mysqlbinlog --no-defaults 二进制日志文件名 mysql -uroot -p ，恢复丢失的数据库

位置恢复：

start        开始(单独使用假设你要回复之后的数据可以使用start)

stop        结束(单独使用假设你要回复之前的数据可以使用stop)

时间恢复 ：同上。