备份:完全备份 增量备份
完全备份:将整个数据库完整的进行备份
增量备份:在完全备份的基础之上,对后续新增的内容进行备份
备份的需求
1、在生产环境中,数据的安全至关重要,任何数据的都可能产生非常严重的后果
2、数据为什么会丢失,程序操作,运算错我,磁盘故障,不可预期的时间(地震之类),人为操作
冷备份:关机备份,停止mysql服务,然后进行备份
热备份:开机备份,无须关闭mysql服务,进行备份
物理备份:对数据库系统的物理文件(数据文件,日志文件),进行备份
逻辑备份,只是对数据库的逻辑组件进行备份,(表结构),以sql语句的形式,把库,表结构,表数据进行备份保存
(直接在数据库系统中删除全部文件)
物理备份:完全备份,对整个数据库进行完整的打包备份
优点:操作简单
缺点:数据库文件占用量是很大,占用空间太大,备份和恢复的时间很长,且需要暂定数据库服务
打包备份最好是把服务关掉,避免有新的数据进入,被覆盖,也可能会导致恢复失败
冷备份实验
终端
关闭mysql服务
systemctl stop mysqld.service
安装xz服务
yum -y install xz
进入mysql目录下
cd /usr/local/mysql/
打包
tar Jcvf /opt/mysql_all_$(date+%F).tar.xz data
解压刚刚压缩的文件
cd /opt/
tar Jxvf mysql_all_.tar.xz
删除源文件
恢复数据
把解压过的文件复制到mysql的目录下
表数据没有发生变化,保持原有的数据
远程复制
把终端2 的mysql的data文件删除
通过远程复制,复制终端1的mysql的data文件到终端2
在复制mysql目录下
注意点:在复制到mysql目录下查看data文件的文件所属ll
热备份当中的逻辑备份
mysqldump 这是mysql自带的工具
mysqldump
备份单个库
mysqldump -u root -p123456 --databases 库名 > /opt/文件名.sql
备份多个库
mysqldump -u root -p123456 --databases 库名1 库名2 > /opt/文件名.sql
备份所有库
mysqldump -u root -p123456 --all-databases > /opt/文件名.sql
指定连接mysql之后执行完命令,自动退出
mysql -u root -p123456 -e 'show databases;'
实例
备份单个库
删除这个库
还原
命令行备份和恢复
创建库
create database xiaobu;
创建表
create table info (
id int(4) not null primary key,
name char(5) default null,
hobby varchar(10) default null
);
对多个库备份
备份
mysqldump -u root -p --databases 库名1 库名2 > /opt文件名.sql
删除
mysql -u root -p -e 'drop database 库名1;'
mysql -u root -p -e 'drop database 库名2;'
恢复多个
mysql -u root -p < /opt/库名.sql
备份所有
mysqldump -uroot -p --all-databases > /opt/ 文件名.sql
删除
mysql -u root -p -e 'drop database 库名1;'
mysql -u root -p -e 'drop database 库名2;'
恢复多个
mysql -u root -p < /opt/库名.sql
如何恢复指定库,指定表
单个表备份
mysqldump -u root -p 库名 表名 > /opt/文件名.sql
不进入数据库删除
mysql -u root -p -e 'drop table 库名.表名'
恢复
mysql -u root -p 库名 < /opt/文件名.sql
多个表
mysqldump -u root -p 库名 表名1 表名2 > /opt/文件名.sql
删除
mysql -u root -p -e 'drop table 库名.表名1'
mysql -u root -p -e 'drop table 库名.表名2'
恢复
mysql -u root -p 库名 < /opt/文件名.sql
物理冷备份和物理热备份
特点:简单
数据量,占用的备份空间比较大
mysqldump 这是mysql自带的备份文件的命令
特点:方便,简单,但是只能给予逻辑上的表结构和表数据恢复,物理删除之后再用逻辑恢复会报错
它也可以作为数据迁移,占用大空间,比较物理备份相对来说占的空间要小的多
增量备份
mysqldump 支持增量备份
没有重复数据,备份量小,时间端
mysqldump增量备份恢复表数据期间,表会锁定(优点)
缺点:备份时锁表,必然会影响业务,超过10G,耗时会比较长,导致服务不可用
增量备份的过程
1、mysql提供的二进制日志文件的实现增量备份
二进制文件怎么来?
修改配置文件(vim /etc/my.conf)
log-bin=mysql-bin binlog_format=MIXED
mysql二进制记录格式有三种
1、STATENET:基于sql语句
纪录修改的sql语句,高并发情况下,纪录sql语句时候的顺序可能会出错,恢复数据时可能会导致丢失和误差,效率比较高
2、ROW:基于行
精确纪录每一行的数据,准确率高,但是恢复的时效率低
3、MIXED :既可以根据SQL语句,也可以根据行
在正常情况下使用STATEENT,一旦发生高并发,会智能自动切换到row行
重启服务
查看备份的二进制文件(必须在data目录下查看)
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
mysqladmin -u root -p flush-logs 刷新日志文件(打印断点)
就会生成一个日志文件00002
删除表里的内容(删除之前要断点)
如何恢复
mysqlbinsql --no-defaults mysql-bin.000003 | mysql -u root -p
- 增量备份,位置节点和时间点,注意一下断点
基于位置节点
从某一个点开始,恢复到最后
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
从开头,一直恢复到某个位置
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
从指定点---指定结束点
mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p
从某一个点开始,恢复到最后
从开头,一直恢复到某个位置
从指定点---指定结束点
基于时间点
1、从某个时间点开始 恢复到最后
mysqlbinlog --no-defaults --start-datetime='时间点' 文件 | mysql -u root -p
2、从开头,到指定的结尾时间点
mysqlbinlog --no-defaults --stop-datetime='时间点' 文件 | mysql -u root -p
3、指定时间范围
mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 文件 | mysql -u root -p
从某个时间点开始 恢复到最后
从开头,到指定的结尾时间点
指定时间范围
总结
在生产中,通过binlog进行增量恢复是非常好用的方法
我们只要需要对binlog文件进行备份,随时可以进行备份和恢复
附加题
写个脚本,每个月的20号,对数据库打一个断点
断点之后进行自动进行增量备份
如何纪录日志文件的错误日志
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5