mysql之备份和恢复

1、物理备份——冷备份

（1）备份到本机

创建库和表

停止数据库服务

存放mysql的数据文件

备份。打包整个数据库data文件到/opt目录下

date +$F：添加时间

查看是否生成备份文件。结论：已生成

解包。在/opt目录下形成一个usr目录

模拟删库（禁止操作，坐牢）

恢复

（2）备份到另一台服务器

mysql1

关闭数据库

mysql2

mysql1的数据库备份到mysql2的/opt目录下

模拟删除mysql1的数据库（禁止操作，坐牢）

恢复。从mysql2恢复数据库到mysql1

属组改变，无法打开数据库

改变属组为mysql（注意要chown -R才能启动mysql服务）

2、逻辑备份（备份mysqldump、恢复mysql）——热备份

（1）备份库

①备份单个库

备份yyy库到/opt目录下，以yyy.sql命名（一定要以.sql结尾）

模拟删库（禁止操作，坐牢）

-e：指定连接mysql之后，执行完命令自动退出

恢复

②删除多个库

备份多个库

模拟删除多个库（禁止操作，坐牢）

恢复多个库

③删除所有库

备份所有库

模拟删除所有库（禁止操作，坐牢）

恢复所有库

（2）恢复表

①恢复单个表

备份表

模拟删除单个库（禁止操作，坐牢）

恢复单个表

②删除多个表

备份多个表

模拟删除多个表（禁止操作，坐牢）

恢复多个表

③删除所有表

备份所有表

模拟删除所有表（禁止操作，坐牢）

恢复所有表

3、增量备份——热备份

（1）基于mysql的二进制日志间接实现增量备份

二进制文件是怎么来的？修改配置文件

mysql二进制日志记录格式：

①STATEMENT：基于sql语句

记录修改的sql语句。高并发情况下，记录sql语句的顺序可能会出错，恢复数据时可能会导致丢失和误差，但恢复效率高

②ROW：基于行

精准记录每一行的数据，准确率高，但恢复效率低

③MIXED：既基于sql语句，也基于行

再正常情况下，使用的是STATEMENT模式，一旦发生高并发会自动切换到ROW模式，效率低，但不会出错

修改配置文件，将二进制文件修改成混合模式

切换到cd /usr/local/mysql/data/目录下

mysql-bin.000001：保存对数据表内容的修改记录

刷新会生成新的二进制文件，下次新增数据会在此新二进制文件中，依次往后推进，每刷新一次会生成一个新的二进制文件（断点）

插入新数据，查看此文件里是否有记录

刷新，保存断点mysqladmin -u root -p flush-logs

删除记录（禁止操作，坐牢）

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003查看对数据库的操作记录

恢复数据mysqlbinlog --no-defaults mysql-bin.000001|mysql -u root -p

（2）基于位置节点恢复数据

①从某个点开始恢复到最后

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

查看位置点（以commit后面的数字为准）：

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004

（3）基于时间点进行恢复（常用，注意时间格式）

①从某个时间点开始

mysqlbinlog --no-defaults --start-datetime=’时间点’ 文件 | mysql -u -root -p

②从开头到某个时间点

mysqlbinlog --no-defaults --stop-datetime=’时间点’ 文件 | mysql -u -root -p

③指定时间范围

mysqlbinlog --no-defaults --start-datetime=’时间点’ --stop-datetime=’时间点’ 文件 | mysql -u -root -p