数据库备份和还原

一、备份

备份类型

1.完全备份

全备份是指对整个数据集进行完整备份。每次备份都会复制所有选定的数据，无论这些数据是否发生了变化。

2.增量备份

增量备份是指仅备份自上次备份（无论是全备份还是增量备份）以来发生变化的数据。它记录了自上次备份以来新创建或修改过的文件。

示例：
周一：全备份（Full Backup）
周二：备份自周一以来的变化（差异备份）
周三：备份自周一以来的变化（差异备份）
周四：备份自周一以来的变化（差异备份）
恢复到周四时需要：周一的全备份 + 周四的差异备份。

3.差异备份

差异备份是指备份自上次全备份以来发生变化的数据。每次差异备份都会包含自上次全备份以来的所有更改数据。

示例：
周一：全备份（Full Backup）
周二：备份自周一以来的变化（差异备份）
周三：备份自周一以来的变化（差异备份）
周四：备份自周一以来的变化（差异备份）
恢复到周四时需要：周一的全备份 + 周四的差异备份。

一、mysqldump备份与恢复

1.mysqldump常见通用选项

选项	含义
-A, --all-databases	#备份所有数据库，含create database
-B, --databases db_name…	#指定备份的数据库，包括create database语句
-E, --events：	#备份相关的所有event scheduler
-R, --routines：	#备份所有存储过程和自定义函数
–triggers：	#备份表相关触发器，默认启用,用–skip-triggers，不备份触发器
–default-character-set=utf8	#指定字符集
–master-data[=#]：	#此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复 制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭–lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启-- single-transaction）
-F, --flush-logs	#备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， 配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–single transaction或-x，–master-data 一起使用实现，此时只刷新一次二进制日志
–compact	#去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data	#只备份表结构,不备份数据,即只备份create table
-t, --no-create-info	#只备份数据,不备份表结构,即不备份create table
-n,–no-create-db	#不备份create database，可被-A或-B覆盖
–flush-privileges	#备份mysql或相关时需要使用
-f, --force	#忽略SQL错误，继续执行
–hex-blob	#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick	#不缓存查询，直接输出，加快备份速度

实际例子：

mysqldump -u root -p --single-transaction --quick -B mydatabase > /path/to/backup/mydatabase_backup.sql-u root：使用 root 用户进行备份。
-p：提示输入 root 用户的密码。注意，这里不建议在命令行中直接输入密码，以保证安全。
--single-transaction：在导出 InnoDB 表时使用单个事务，以确保数据一致性。这对于运行中的数据库非常有用，因为它不会锁定表。
--quick：直接从服务器读取结果并输出，减少客户端内存占用，适合大数据量备份。
-B mydatabase：备份 mydatabase 数据库。-B 选项可以确保数据库名被正确转义，适用于数据库名包含特殊字符的情况。
/path/to/backup/mydatabase_backup.sql：将备份输出到指定文件。替换 /path/to/backup/mydatabase_backup.sql: 为你想要保存备份文件的位置和名称。                     

三、mysqlbinlog

1.常用选项

--no-defaults：不读取任何配置文件（如 my.cnf 或 my.ini），只使用命令行中的选项。
--start-position=pos：从指定的日志文件位置开始读取。
--stop-position=pos：读取到指定的日志文件位置为止。
--start-datetime=datetime：从指定的日期和时间开始读取（格式为 YYYY-MM-DD HH:MM:SS）。
--stop-datetime=datetime：读取到指定的日期和时间为止。
--database=db_name：仅显示指定数据库的更改。
--result-file=name：将输出写入指定文件。
--host=name：连接到 MySQL 主机。
--user=name：连接到 MySQL 使用的用户名。
--password[=name]：连接到 MySQL 使用的密码。
--port=#：连接到 MySQL 使用的端口号。
--verbose：输出详细的内容。
--base64-output=DECODE-ROWS：显示事件内容时解码 BASE64 编码的 BINLOG 事件

2.实际例子：

1.基本用法：

mysqlbinlog /opt/mysql-bin.000002

此命令将读取 /opt/mysql-bin.000002 二进制日志文件，并将内容输出到终端。

2.将二进制日志应用到数据库

mysqlbinlog /opt/mysql-bin.000002 | mysql -u root -p

此命令将 /opt/mysql-bin.000002 文件中的内容应用到 MySQL 数据库。

实验

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

#开启二进制日志
vim /etc/my.cnf
log_bin=/data/mysql-bin
server-id = 1mkdir /data
chown mysql.mysql /data/ -R
systemctl restart   mysqld#######2:30  执行全备
mysqldump -uroot -p123123 -A -F --single-transaction --master-data=2 > /opt/all.sql#完全备份后数据更新
insert students (name,age,gender) values('rose',20,'f');
insert students (name,age,gender) values('jack',20,'f');##############10:00  误删除了一个students的表
drop table students;###########后续其余的表继续更新insert teachers (name,age,gender)values('test',30,'M');insert teachers (name,age,gender)values('test1',30,'M');#####10点10分发现进行还原
#停止数据库访问grep 'mysql-bin.00'  /opt/all.sql
#从完全备份中，找到二进制位置
#-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;#备份  完全备份后的二进制日志
mysqlbinlog --start-position=158 /data/mysql-bin.000002 > /opt/inc.sql#找到  删除的语句
grep -i "^drop table"  /opt/inc.sql 
DROP TABLE `students` /* generated by server */#删除 删表的那一行
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql #先登录数据库还原
set sql_log_bin=0;     #先关闭二进制日志    临时关闭  
set sql_log_bin=1; source     /opt/inc.sql