mysql之备份与恢复

 

数据备份全备

备份命令 :mysqldump把数据库的数据以sql语句导出属于逻辑备份

格式 :

mysqldump -uroot -p123456 -S 多实例的mysql.sock 数据库名 >/备份的文件名　　　　　　　　#单库备份
egrep -v '#|*|--|^$' /备份文件名    #检查备份的结果

恢复数据:

mysql -u用户名 -p密码 库名</备份文件名　　　　　　#把备份的文件导入mysql

备份单个表

mysqldump -uroot -p123456 -S /多实例.sock 库名 表名>/备份文件名  #不加-B前边是库后边是表

恢复单个表

mysql -u用户名 -p密码 库名</备份文件名　　　　　　#把备份的文件导入mysql

mysqldump 的参数

-B 库名 　　#在备份文件里增加创建数据库和进入数据库的命令 加-B就算数据库被drop掉也可以直接恢复

mysqldump -uroot -p123456 -S /多实例mysql.sock -B 库名>/备份文件　　　　　　　　　　#备份 可备份多个库 -B 库名1 库名2 库名N  
mysql -uroot -p123456 -S /多实例mysql.sock <备份文件　　　　　　 　　　　　　　　　　#恢复

 -d　　备份表结构

mysqldump -uroot -p123456 -S /多实例.sock -d 库名 表名>/备份文件名

-t　　只备份表内数据

mysqldump -uroot -p123456 -S /多实例.sock -t 库名 表名>/备份文件名

-A　　备份所有数据

mysqldump -uroot -p123456 -S /多实例mysql.sock -A -B --events>/备份文件名　　　　　　#-A需和-B和--events连用

-F　　刷新binlog文件使用后新的binlog文件记录日志

mysqldump -uroot -p123456 -S /多实例mysql.sock -A -B -F --events>/备份文件名　

|gzip　　将mysqldump全备的文件进行压缩

mysqldump -uroot -p123456 -S /多实例mysql.sock -A -B -F --events|gzip>/备份文件名　　　　　　　#用于数据库数据过大

备份时锁表   myisam引擎用:-x　 innodb引擎用: --single-transaction 　　 #这两个要放在前边　

mysqldump -uroot -p123456 -S /多实例mysql.sock -x --events  -A -B -F| gzip >/备份文件名   　　#myisam引擎 

mysqldump -uroot -p123456 -S /多实例mysql.sock --single-transaction --events -A -B -F| gzip >/备份文件名　　 #innodb引擎

--master-data=1 　　 记录日志文件名和偏移量　 --master-data=2记录偏移量并做注释　#在使用主从复制时不需要在change master里写入日志文件名和偏移量

mysqldump -uroot -p123456 -S /多实例mysql.sock --events --single-transaction -A -B -F --master-data=1 | gzip >/备份文件名

--master-data=2  

--master-data=1

 数据备份增备

增量备份是利用mysql的binlog日志记录的sql语句完成增量备份的,当执行一次完全备份后,记录当前binlog日志的位置,做增量备份时,只需要从全备记录的binlog日志位置开始把往后记录的所有sql语句导入mysql即可

增量备份就是要开启binlog日志,然后结合全备做增备

vim my.cnf 　　　　　　　　　　#修改主库的配置文件　　
[mysqld]　　　　　　　　　　 　#参数要放在my.cnf中的[mysqld]模块下，否则会出错。 
log-bin = /data/3306/mysql-bin     #binlog日志的位置 

mysqlbinlog命令 查看binlog日志内容

#按位置查找
mysqlbinlog mysql-bin.000001 --start-position=12099 --stop-position=12299 -r /root/321
查看binlog日志                 从12099位置开始看　　　到12299位置结尾的内容不被包含   把12099到12299的内容导入到文件321里 -r等于重定向>
可以只设置--start-position=12099 既从12099位置一直到结尾   也可以只设置--stop-position=12299 既从开头到12299结束
#按时间查找
mysqlbinlog mysql-bin.000001 --start-datetime="2018-08-06 6:18:35" --stop-datetime="2018-08-06 6:43:21"
 #和位置一样只是查询方法不同 不建议用,不准确

mysqlbinlog mysql-bin.000001 -d 库名 　　#截取指定库binlog日志

如果企业中不小心drop或update掉了数据内容,可以选择停库,全备恢复,把binlog日志的内容用mysqlbinlog导出来找到里面的drop或update的sql语句删掉然后恢复增量备份即可

如果是update破坏数据 会更麻烦一些,

全备和增倍的频率

每天00:00做全备

优点:恢复时间短,维护成本低　　　　　　缺点:占用空间和占用资源多.

每周一00:00做一次全备

 优点:占用空间小,占用系统资源少,无需锁表,用户体验好一些　　　　　　　　缺点:维护成本高,恢复麻烦,时间长

企业是怎样做备份的

企业场景全量和增量的频率是怎么做的呢?。
1)中小公司，全量一般是每天一次， 业务流量低谷执行全备， 备份时会锁表。。
2)单台数据库，如何增量。用rsync (配合定时任务频率大点或者inotify, 主从复制)把所有binlog 备份到远程服务器，尽量做主从复制。。
增量备份的例子: rsync -avz /data/3306/ mysql-bin.000* rsync_ backup@ 10.0.0. l8::backup --password-file=/etc/rsync.password.
3)大公司周备，每周六00点一次全量，下周日-下周六00点前都是增量。
优点:节省备份时间，减小备份压力。缺点:增量的binlog文件副本太多，还原会很麻烦。
4)一主五从，会有一个从库做备份，延迟同步。。

增量回复的核心思想

增量恢复的核心思想:
1、流程制度控制。如果不做，面临服务和数据二选一

2、延迟备份来解决。信息做监控，黑名单， 白名单机制。

3、业务需求容忍度，可量化的目标，根据需求选择停库或锁表或者容忍丢失部分数据

恢复备份案例

全备之前,启动bin-log功能可以看到bin-log日志文件mysql-bin.000001

0:00做全备,定时任务后刷新binlog,使其用新文件名记录日志
0:00-10:00 全备后的新数据记录到新binlog日志文件里 mysql-bin.000002
10:00 出现错误
10:10收到消息恢复mysql

恢复步骤

锁库
首先恢复全备
然后在binlog里找出错误原因 再恢复到错之前的binlog增量
10:00-10:10由于已经错误了 所以没有写入的数据