mysql的备份和恢复

备份：完全备份 增量备份

完全备份：将整个数据库完整的进行备份

增量备份：在完全备份的基础之上，对后续新增的内容进行备份

备份的需求

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

1. 增量备份，位置节点和时间点，注意一下断点

基于位置节点

从某一个点开始，恢复到最后

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