一、mysql 全量备份以及增量备份
1、全量备份命令:
- /application/mysql/bin/mysqldump -uroot -p123456 --lock-all-tables -A -B -F --master-data=2 --single-transaction --events|gzip > /opt/MysqlBackup/allbackup/allbackup.sql.gz*
如上一段代码所示,其功能是将所有数据库全量备份。
其中 MySQL 用户名为:root ,密码为:123456。
备份的文件路径为:/opt/Mysql_Backup/all_backup,当然这个路径是按照个人意愿修改的。
备份的文件压缩包名为 all_backup.sql.gz
参数 --lock-all-tables:锁定所有数据库;
参数 -A:备份所有库;
参数 -B:指定多个库,增加建库语句和 use 语句;
参数 -F:刷新 binlog 日志;
参数 --master-data=0|1|2:
0: 不记录
1:记录为CHANGE MASTER语句
2:记录为注释的CHANGE MASTER语句;
参数 --single-transaction:适合 innodb 事务数据库备份;
参数 --events:导出事件;
参数 gzip:备份压缩文件。
2、全量备份脚本:
- #!/bin/bash . /etc/init.d/functions user=root password="123456" BackupTools=/application/mysql/bin/mysqldump BackupDir=/opt/MysqlBackup AllBackup=$BackupDir/allbackup mkdir -p $AllBackup echo '=========='$(date +"%Y-%m-%d %H:%M:%S")'==========' “备份开始” >>$AllBackup/allbackup.log $BackupTools -u$user -p$password -A -B -F --master-data=2 --single-transaction --events|gzip >$AllBackup/allbackup$(date +%Y%m%d).sql.gz if [ $? -eq 0 ] then echo '=========='$(date +"%Y-%m-%d %H:%M:%S")'==========' "备份完成" >>$AllBackup/allbackup.log action "Mysql full backup is ok" /bin/true else action "Mysql full backup is not ok" /bin/false fi
3、恢复全量备份命令:
cd /opt/MysqlBackup/allbackup gzip -d allbackup.sql.gz mysql -uroot -p123456 < allbackup.sql 或者:
*mysql> source /opt/MysqlBackup/allbackup/allbackup.sql *
4、增量备份
首先在进行增量备份之前需要查看一下配置文件,查看 logbin 是否开启,因为要做增量备份首先要开启 logbin 。
首先,进入到 myslq 命令行,输入如下命令:
mysql> show variables like '%logbin%'; +---------------------------------+-------+ | Variablename | Value | +---------------------------------+-------+ | logbin | ON | | logbintrustfunctioncreators | OFF | | sqllogbin | ON | +---------------------------------+-------+
如上已经开启了binlog,如果没有开启,执行如下命令:
vim /etc/my.cnf 开启: [mysqld] log-bin=mysql-bin
查看当前使用的 mysql_bin.000 日志文件:
*mysql> show master status;`` +------------------+----------+--------------+------------------+ | File | Position | BinlogDoDB | BinlogIgnoreDB | +------------------+----------+--------------+------------------+ | mysql-bin.000019 | 533039 | | | +------------------+----------+--------------+------------------+ * 当前正在记录日志的文件名为 mysql-bin.000019。
增量备份脚本
*#!/bin/bash export LANG=enUS.UTF-8 BackupDir=/opt/MysqlBackup/binlogbackup/ BinDir=/application/mysql/data/ LogFile=/opt/MysqlBackup/binlog.log BinFile=/application/mysql/data/mysql-bin.index mkdir -p $BackupDir /application/mysql/bin/mysqladmin -uroot -p123456 flush-logs Counter=
wc -l $BinFile|awk '{print $1}'
NextNum=0 for file incat $BinFile
do base=basename $file
NextNum=expr $NextNum + 1
if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BackupDir/$base if [ -e $dest ] then echo $base exist! >> $LogFile else cp $BinDir/$base $BackupDir echo $base copying >> $LogFile fi fi done echodate +"%Y年%m月%d日 %H:%M:%S"
Backup succ!>> $LogFile
二、增量数据恢复案例
1、场景概述
a、MySQL数据库每日零点自动全备
b、某天上午10点,小明莫名其妙地drop了一个数据库
c、我们需要通过全备的数据文件,以及增量的binlog文件进行数据恢复
2、主要思想
a、利用全备的sql文件中记录的CHANGE MASTER语句,binlog文件及其位置点信息,找出binlog文件增量的部分
b、用mysqlbinlog命令将上述的binlog文件导出为sql文件,并剔除其中的drop语句
c、通过全备文件和增量binlog文件的导出sql文件,就可以恢复到完整的数据
3、过程示意图
4、操作过程
1)、模拟数据
*CREATETABLEstudent(
id
int(11)NOT NULLAUTOINCREMENT,
name
char(20)NOT NULL,
age
tinyint(2)NOT NULLDEFAULT'0',PRIMARY KEY(
id
),KEY
indexname
(name
))ENGINE=InnoDBAUTOINCREMENT=8DEFAULTCHARSET=utf8
mysql>insertstudentvalues(1,'zhangsan',20);
mysql>insertstudentvalues(2,'lisi',21);
mysql>insertstudentvalues(3,'wangwu',22);
2)、全备命令
/application/mysql/bin/mysqldump -uroot -p123456 --lock-all-tables -A -B -F --master-data=2 --single-transaction --events|gzip > /opt/MysqlBackup/allbackup/allbackup.sql.gz
3)、继续插入数据
*mysql>insertstudentvalues(6,'xiaoming',20);
mysql>insertstudentvalues(6,'xiaohong',20);
此时误操作,删除了test数据库
mysql>dropdatabasetest;
-
此时,全备之后到误操作时刻之间,用户写入的数据在binlog中,需要恢复出来
4)、查看全备之后新增的binlog文件
*cd /opt/MysqlBackup/allbackup/ ls allbackup20180831.sql.gz gzip -d allbackup20180831.sql.gz grep CHANGE allbackup20180831.sql -- CHANGE MASTER TO MASTERLOGFILE='mysql-bin.000003', MASTERLOGPOS=107;
这是全备时刻的binlog文件位置,即mysql-bin.000003的107行,因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了
5)、移动binlog文件,并读取sql,剔除其中的drop语句
*cp mysql-bin.000003 /tmp/ mysqlbinlog -d test mysql-bin.000003 > bin.log 用vim编辑文件,剔除drop语句
-
在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱
6)、恢复数据
mysql -uroot -p < all_backup_20180831.sql <全备恢复>
mysql -uroot -p -e "select * from test.student;"
+----+----------+-----+
|id|name |age|
+----+----------+-----+
| 1|zhangsan| 20|
| 2|lisi | 21|
| 3|wangwu | 22|
+----+----------+-----+
//此时恢复了全备时刻的数据
//然后使用003bin.sql文件恢复全备时刻到删除数据库之间,新增的数据
mysql -uroot -p test< /tmp/bin.sql <增量 binlog 语句恢复>
</span># mysql -uroot -p -e "select * from test.student;"
+----+----------+-----+
|id|name |age|
+----+----------+-----+
| 1|zhangsan| 20|
| 2|lisi | 20|
| 3|wangwu | 20|
| 4|xiaoming| 20|
| 5|xiaohong| 20|
+----+----------+-----+
完成
5、小结
a、适合人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复
b、恢复条件要全备和增量的所有数据
c、恢复时建议对外停止更新,即禁止更新数据库
d、先恢复全量,然后把全备时刻点以后的增量日志,按顺序恢复成SQL文件,然后把文件中有问题的SQL语句删除(也可通过时间和位置点),再恢复到数据库
三、定时清理 binlog 日志
最近磁盘增长的非常快,发现binlog日志占用很大的磁盘资源。我们采用手动清理,后面设置一下自动清理。
查看指定删除日志
mysql >show binary logs; 查看多少binlog日志,占用多少空间。
mysql> PURGE MASTER LOGS TO 'mysql-bin.002467'; 删除mysql-bin.002467以前所有binlog,这样删除可以保证*.index信息与binlog文件同步。
手动清理
mysql>PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY); 手动删除5天前的binlog日志
自动设置清理
mysql> set global expire_logs_days = 5; 把binlog的过期时间设置为5天; mysql> flush logs; 刷一下log使上面的设置生效,否则不生效。
为保证在MYSQL重启后仍然有效,在my.cnf中也加入此参数设置
expire_logs_days = 5