MySQL备份与还原

 数据备份的重要性

数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因有如下几种:

 程序错误。
人为操作错误。
运算错误。
磁盘故障。
灾难(如火灾、地震)和盗窃。

数据库备份类型

1.从物理与逻辑的角度分类
数据库备份可以分为物理备份和逻辑备份。

冷备份:在数据库关闭状态下进行备份操作。

热备份:在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文。

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

从数据库的备份策略角度分类

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份。完全备份:每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

常见的备份方法

1.物理冷备份
物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹(本章中的数据库文件夹位于/usr/locallmysqldata)来实现备份。

2.专用各份工具mysqldump或mysqlhotcopy

储一个到多个MySQL 数据库，对其进行备份或传输到远程 SQL 服务器。mysqldump 更头通用，因为它可以备份各种表。mysqlhotcopy 仅适用于某些存储引擎。

3.通过启用二进制日志进行增量备份
MYSQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改)，需要刷新二进制日志。

 物理冷备份与恢复

1.备份数据库

创建一个/backup 目录作为备份数据存储路径，使用tar 创建备份文件。整个数据库文件夹备份属于完全备份。

systemctl stop mysqld
mkdir /backup
tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql
systemctl start mysqld

2.恢复数据库

mkdir bak
mv /usr/local/mysql/data/ bak/

执行下面操作可以从备份文件恢复数据。

mkdir restore
tar zxf /backupmysql_all-2020-01-02.tar.gz -c restore/
mv restore/usr/local/mysql/data/ /usr/local/mysql/
systemctl start mysql

mysqldump 备份与恢复

1.备份数据库
使用 mysqldump命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合 Shel 的">"重定向输出操作

格式 1:备份指定库中的部分表。

mysqldump[选项]库名[表名 1][表名 2]..>1备份路径1备份文件名

格式 2:备份一个或多个完整的库(包括其中所有的表)。

mysqldump[选项] --databases 库名1[库名2]..>/备份路径/备份文件名

格式3：备份Mysql服务器中的所有数据库。

mysqldump[选项] --all-databases >/备份路径/备份文件名

分别使用格式 1、格式 2，将 mysql 库中的 user 表导出为mysqluser.sql，将整个 auth导出为 auth.sql 文件，所有操作均以root 用户身份验证。

mysqldump -u root -p mysql user> mysql-user.sql

mysqldump -u root -p .databases auth > auth.sql

若需要备份整个 MySQL服务器中的所有库，应使用格式 3。当导出的数据量较大的时候，可以添加“--opt"选项以优化执行速度。

 mysqldump -u root -p --opt -all-databases > all-data.sql

2.查看备份文件

通过 mysqldump 工具导出的 SQL 脚本是文本文件，其中“/*..*/"部分或以"-"开头的行示注释信息。

grep -v "^.." auth.sql| grep -v "^!"| grep -v "^$"

3.恢复数据库

使用 mysqldump 命令导出的 SQL备份脚本，在需要恢复时可以通过 mysql 命令对其进行导入操作

mysql[选项][库名][表名]</备份路径/备份文件名

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

 mysql -u root -p test < mysql-user.sql
mysql -u root -p -e'SHOW TABLES FROM test;'

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名。

mysql -u root -p -e 'DROP DATABASE auth;
mysql -u root -p -e "SHOW DATABASES;
mysql -u root -p<-/auth.sql;
mysql -u root -p -e'SHOW DATABASES;'

 MySQL 增量备份概述

1.增量备份的特点

与完全备份不同，增量备份没有重复数据，备份量不大，时间短:但其复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。

2.MySQL 二进制日志对备份的意义

二进制日志保存了所有更新数据库的操作。二进制日志在启动 MYSQL 服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到 fush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。

vim fetcimy.cnf
[mysgld]
log-bin:/usr/local'mysqidata/mysql-bin
binlog_fomat= MIXED
systemctl restart mysqld

 MySQL 增量恢复

在维护数据库时，因为各种各样的原因可能会导致数据丢失，如:人为的SOL语句破坏数据库、在进行下一次全备份之前发生系统故障导致数据库数据丢失、在数据库主从架构中主库的数据发生故障等。当出现以上场景时可以使用增量恢复来复数据。

mysqlbinlog [--no-defaults]增量备份文件|mysgl-u 用户名 -p 密码

基于位置的恢复:数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。

格式 1:恢复数据到指定位置。
mysqlbinlog-stop-position='操作 id'二进制日志|mysql-u用户名-p 密码
格式 2:从指定的位置开始恢复数据。
南
mysqlbinlog-start-position='操作 id’二进制日志 |mysql-u用户名-p 密码

基于时间点的恢复:跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。
格式 1:从日志开头截止到某个时间点的恢复

mysqlbinlog[--no-defaults]--stop-datetimme='年-月-日 小时:分钟:秒’二进制日志|mysql·u 用户名 -p 密码

格式 2:从某个时间点到日志结尾的恢复，
mysqlbinlog [-no-defaults]-start.datetime=年.月.日 小时:分钟:秒’二进制日志|mysql-u 用户名 -p 密码
格式3;从某个时间点到某个时间点的恢复。

mysqlbinlog [--no-defaults]-start-datetime="年.月.日 小时:分钟:秒
--stop-datetime="年-月-日小时:分钟:秒’二进制日志|mysql-u用户名 -p 密码

基于位置的恢复

模拟误操作

mysql -uroot -p -e 'drop table auth.users;'
mysql -u root -p -e 'select * from auth.users;'   ##没有了数据

恢复完全备份

 mysql -u root -p auth < /opt/auth.sql 

查看日志内容

mysqlbinlog --no-defaults mysql-bin.000002

从日志中分析哪些数据还没有恢复，找出想要恢复到哪个数据的操作ID

mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p

恢复到指定的地方

mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p      ##恢复到521之前的一个数据，不包含521
mysql -u root -p -e 'select * from auth.users;' 

从指定的位置向后开始恢复

mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p

在进行备份和还原操作时，需要注意以下几点：

确保有足够的存储空间来保存备份文件。
选择合适的备份策略，如全量备份、增量备份等。
测试还原过程，以确保在需要时能够成功恢复数据。