一、备份
备份类型
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 = 1
mkdir /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