数据备份全备
备份命令 :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由于已经错误了 所以没有写入的数据