Linux基础——MySQL（三）备份与恢复

数据丢失的原因	程序出错 人为错误 计算机失败 磁盘失败 灾难和盗窃
备份的分类
企业备份策略	小型公司：服务器小于50 中型公司：服务器50-200 中大型公司：200-500 中小型公司企业备份策略： 企业一般对于数据安全来说，更注重性价比； 少量的数据丢失是可以承受的； 数据丢失不过是回档。 因此，中小型公司备份，由于业务量小，数据量就小。备份时间短。每天进行一次全量备份，然后将备份推送到服务器进行校验即可。 对于大公司来说，数据安全很重要，与成本来讲，更注重于安全。因此大企业的数据量很大，每备份一次时间很长。所以每周进行一次全量备份，每天进行增量备份。
完全备份&恢复
备份 命令	mysqldump 格式：mysqldump -u账号 -p密码 库名 > 备份到的目录/名称.sql 此备份方式是备份了指定库中的所有表，但并不备份库本身。 连同库本身一起备份：--databases 格式：mysqldump -u账号 -p密码 --databases 库名 > 备份到的目录/名称.sql 加快数据库的查询速度：--opt   全部数据库：--all-databases 格式：mysqldump -u账号 -p密码 --opt  --all-databases > 备份到的目录/名称.sql
恢复 命令	1.交互式：source 格式：source 路径/名称.sql 2.非交互：mysql -u 账号 -p密码 恢复到的库 <  /备份文件路径/名称.sql
举例 (1)	mysql> create database my; //创建一个client库 mysql> use my; //进入该库 create table user_info(身份证 int(20),姓名 char(20),性别 char(2),用户ID号 int(110),资费 int(10)) DEFAULT CHARSET=utf8; insert into user_info values('000000001','孙空武','男','011','100'); insert into user_info values('000000002','蓝凌','女','012','98'); insert into user_info values('000000003','姜纹','女','013','12'); insert into user_info values('000000004','关园','男','014','38'); insert into user_info values('000000004','罗中昆','男','015','39'); //创建表（直接将这段内容复制粘贴过去即可） mysql> select * from user_info; //查看表中数据 mysql>exit //退出数据库 [root@centos ~]# mkdir /bakcup //创建/backup目录用来存放备份数据 [root@centos ~]# mysqldump -uroot -pabc123, my > /backup/mysql_my.sql //备份数据库my到/backup中 [root@centos ~]# ls /backup/ //查看备份存放目录 [root@centos ~]# mysql -uroot -pabc123, //进入数据库 mysql> use my; //切换到my库 mysql> drop table user_info; //删除表 mysql> source /backup/mysql_my.sql //在交互式恢复数据 Ps：备份的是数据库中的表，如果删除了库，是恢复不了数据的；同时不指定恢复到哪个库，也是不能恢复表的。 恢复成功 mysql> drop table user_info; //删除表 mysql> exit //退出数据库 [root@centos ~]# mysql -uroot -pabc123, my < /backup/mysql_my.sql //非交互式恢复数据 [root@centos ~]# mysql -uroot -pabc123, //登录数据库 mysql> select * from my.user_info; //查看数据是否恢复 完整备份恢复成功！
举例（2）	备份所有数据库 [root@centos ~]# mysqldump -uroot -pabc123, --opt --all-databases > /backup/mysql_all.sql
增量备份&恢复
概念
准备	[root@centos ~]# vim /etc/my.cnf //进入MySQL主配置文件，将二进制配置文件加上#，将其注释 #log-bin=mysql-bin [root@centos ~]# cd /usr/local/mysql/data/ //进入data目录 [root@centos data]# rm -f mysql-bin.* //删除掉二进制备份文件 [root@centos data]# /etc/init.d/mysqld restart //重启服务 [root@centos ~]# vim /etc/my.cnf //进入主配置文件，取消二进制文件的注释 [root@centos data]# /etc/init.d/mysqld restart //重启服务
含义	log-bin = mysql-bin，前面是参数，后面是路径名字；index是索引，当这个文件满了会再创建一个文件，或者使用flush logs刷新日志。
刷新日志	flush logs手动刷新，生成新的日志文件 例：[root@centos data]# mysql -uroot -pabc123, -e 'flush logs'
查看二进制日志	格式：mysqlbinlog 日志 日志一般在/uer/local/mysql/data中 例如： end_log_pos 107：目前日志占用107字符 Start：日志开始 End of log file：结束日志
免交互 参数	-e 格式：mysql -u账户 -p密码 -e ‘要执行的命令;’ 举例：
（1）、二进制日志内容全部恢复
1.	[root@centos data]# mysql -uroot -pabc123, //登录数据库 （ps：data路径：/usr/local/mysql/data） mysql> create database benet; //创建benet数据库
2.	[root@centos data]# mysqlbinlog mysql-bin.000001 //查看二进制日志中的内容 [root@centos data]# vim /etc/my.cnf //进入mysql主配置文件，将二进制日志注释 #log-bin=mysql-bin [root@centos data]# /etc/init.d/mysqld restart //重启数据库
3.	[root@centos data]# mysql -uroot -pabc123, -e 'drop database benet;' //免交互删除benet库 [root@centos data]# mysql -uroot -pabc123, -e 'show databases;' //查看MySQL中的库
4.	[root@centos data]# mysqlbinlog mysql-bin.000001 | mysql -uroot -pabc123, //将二进制日志中的内容全部恢复 [root@centos data]# mysql -uroot -pabc123, -e 'show databases;' //免交互查询数据库 benet库恢复成功！
（2）、基于时间点的恢复
命令	从日志开头截至到某个时间的恢复： mysqlbinlog --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码 从某个时间点到日志结尾的恢复： mysqlbinlog --start-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码 从某个时间点到某个时间点的恢复： mysqlbinlog --start-datetime=‘年-月-日 小时:分钟:秒’ --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码
环境	开启二进制日志，重启mysqld服务，在MySQL数据库创建两个库：benet和benet2；创建完后进入mysql主配置文件，关闭二进制日志，重启mysqld服务，并查看二进制日志中记录的内容：
1.	[root@centos data]# mysql -uroot -pabc123, //登录数据库 mysql> drop database benet; mysql> drop database benet2; //删除benet和benet2两个库，模拟数据丢失
2.	[root@centos data]# mysqlbinlog --start-datetime='2020-12-25 8:48:31' mysql-bin.000001 | mysql -uroot -pabc123, //根据时间恢复指定时间之后的所有数据 [root@centos data]# mysql -uroot -pabc123, -e 'show databases;' //非交互式查看数据库中所有库 数据恢复成功！
3.	[root@centos data]# mysqlbinlog --stop-datetime='2020-12-25 8:49:32' mysql-bin.000001 | mysql -uroot -pabc123, //恢复指定时间之前的所有数据 [root@centos data]# mysql -uroot -pabc123, -e 'show databases;' //非交互式显示数据库中所有库 数据恢复成功！
4.	[root@centos data]# mysqlbinlog --start-datetime='2020-12-25 8:48:31' --stop-datetime='2020-12-25 8:48:48' mysql-bin.000001 | mysql -uroot -pabc123, //恢复指定时间段内的数据 [root@centos data]# mysql -uroot -pabc123, -e 'show databases;' 数据恢复成功！
（3）、基于位置的恢复
命令	恢复指定位置以后所有的数据： mysqlbinlog  --start-position=‘位置号’  二进制日志 |  mysql -u账号 -p密码 恢复指定位置之前所有的数据： mysqlbinlog  --stop-position=‘位置号’ 二进制日志 | mysql -u 账号 -p 密码
环境	开启二进制日志，重启mysqld服务，在MySQL数据库创建两个库：benet和benet2；创建完后进入mysql主配置文件，关闭二进制日志，重启mysqld服务，并查看二进制日志中记录的内容：
1.	[root@centos data]# mysql -uroot -pabc123, //登录数据库 mysql> drop database benet; mysql> drop database benet2; //删除benet和benet2两个库，模拟数据丢失
2.	恢复所有数据 [root@centos ~]# mysqlbinlog --start-position='107' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -pabc123, //使用二进制日志恢复107开始的所有数据 [root@centos ~]# mysql -uroot -pabc123, -e 'show databases;' //免交互登录查看数据库 数据恢复成功！ 恢复成功 （ps：使用位置恢复时，如果不写结尾，默认恢复自指定位置到结尾的所有数据）
3.	恢复指定数据：benet，但不恢复benet2 [root@centos ~]# mysqlbinlog --stop-position='192' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -pabc123, //通过二进制日志，恢复192之前的所有数据 [root@centos ~]# mysql -uroot -pabc123, -e 'show databases;' //免交互查看库 数据恢复成功！
企业数据备份整理思路
业务 低谷期	一般在周一凌晨的五点到十一点之间
公司备份策略	对数据安全要求较高：每周一全备，每天一增备。 每周一凌晨四点全备份
脚本 执行流程	每周一凌晨四点触发脚本 通过mysqldump对数据进行全量备份 全量备份的包要经过gzip进行压缩 例：mysqldump -uroot -pabc123, --opt –all-databases | gzip -9 > /tmp/xxx.sql.gz 使用rsync将备份包和MD5校验码一起推送到备份服务器。 将所有以mysql-bin.0*开头的文件移动到某个目录，打包移动到备份服务器（全备，历史增备，现在增备） flush logs刷新日志 使用rsync+innotify实时同步mysql-bin.xxxxxx（不在脚本中）