mysql数据备份
数据备份分类
-
数据库的备份类型
- 完全备份：对整个数据库的数据进行备份
- 部分备份：对部分数据进行备份（可以是一张表也可以是多张表）
- 增量备份：是以上一次备份为基础来备份变更数据的，节约空间
- 差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间
-
数据库备份的方式
- 逻辑备份：直接生成sql语句保存起来，在恢复数据的时候执行备份的sql语句来实现数据的恢复
- 物理备份：直接拷贝相关的物理数据
- 区别：逻辑备份效率低，恢复数据效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高
-
数据库备份的场景
- 热备份：备份时，数据库的读写不会受到影响
- 温备份：备份时，数据库的读操作可以进行，但是写操作不能执行
- 冷备份：备份时，不能进行任何操作
逻辑备份与恢复mysqldump
-
mysqldump使用语法
mysqldump -u用户 -p -h主机 数据库 表名 > 路径
-
实例
# 备份单库 mysqldump -uroot -p -h127.0.0.1 --databases test | gzip > /mysql_data_back/test.sql.gz# 备份单库单表 mysqldump -uroot -p -h127.0.0.1 test test | gzip > /mysql_data_back/test.sql.gz# 备份多库 mysqldump -uroot -p -h127.0.0.1 --databases test sys | gzip > /mysql_data_back/test.sql.gz -
数据恢复
mysql -uroot -p -h127.0.0.1 < /mysql_data_back/test.sql
物理备份
-
查询数据库源文件路径
- 方式一：show variables like ‘datadir’;
- 方式二：配置文件中查看vim /etc/my.cnf
-
MyISAM表源文件
- db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
- .frm：记录表结构信息
- .MYD：记录表数据
- .MYI：记录索引
-
InnoDB表源文件：InnoDB有着共享表空间跟独立表空间的概念
- db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
- .frm：记录表结构信息
- .ibd：独立表空间，记录这个表的数据和索引
- ibdata1：共享表空间，记录表的数据和索引（和数据库同级别存放）
物理备份：直接将源文件拷贝，工作中不常用，注意不要拷贝缺失
二进制备份mysqlbinlog （结合mysqldump使用）
-
简介
- 二进制日志就是记录着mysql数据库中的一些写入性操作，比如增删改，不包括查询
- 实现增量备份
- 开启二进制日志会有1%性能消耗
-
查看二进制日志是否开启
show variables like 'log_bin%'; -
开启二进制日志：vim /etc/my.cnf
[mysqld] log-bin=/data/mydata/log_bin/mysql_bin server-id=1 -
binlog相关操作
-- 查看所有的binlog日志列表 show master logs;-- 刷新二进制日志（重新生成新的日志） flush logs;-- 清空二进制日志文件 reset master; -
使用mysqldump备份数据时，加上-F可以重新生成一个新的二进制日志
mysqldump -uroot -p -h127.0.0.1 test test -F > /mysql_data_back/test.sql
二进制恢复mysqlbinlog
-
查看二进制日志：mysqlbinlog mysql_bin.000002
-
二进制日志文件导出成普通文件
mysqlbinlog --base64-output=DECODE-ROWS -v mysql_bin.000002 > mysqlbin.sql -
找出要恢复的位置
# 找出关键字的行数 mysqlbinlog --no-defaults mysql_bin.000002 | cat -n | grep -iw 'drop'# 打印相关内容 mysqlbinlog --no-defaults mysql_bin.000002 | cat -n | sed -n '100,114p' -
恢复数据
# 先恢复之前全量备份的数据 mysql -uroot -p -h127.0.0.1 test < /mysql_data_back/test.sql # 恢复增量备份的数据：注意position是文件中的# at 1275 mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="1275" /data/mydata/log_bin/mysql_bin.000002 | mysql -uroot -p