mysqldump备份数据库
完全备份+增加备份,速度相对较慢,适合中小型数据库、MyISAM是温备份,InnoDB是热备份
mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级 时相对比较合适,这也是最常用的备份方法。
备份策略可以这么定:第一次完全备份,每天一次增量备份,每周再做一次完全备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线 上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份。
- 示例一:备份恢复单个数据库
备份:mydqldump -u 用户 –p’密码’ –default-character-set=Iatin1 数据库名 [|gzip] > 备份文件名
示例:mysqldump -uroot -p'123456' --default-character-set=utf8 -B school > /tmp/school.$(date +%F).sql
注意:-B 作用:创建数据库和切换到数据库,恢复时不用创建数据库和删表。备份多个库,-B 数据库1 数 据库2 …
恢复:
1)MySQL中用source命令
source /backup/db/school.sql
2)mysql命令恢复
mysql -uroot -p123456 company < /backup/db/school.sql - 示例二:备份恢复单个表
备份:mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
示例:mysqldump -uroot -p school Books >/tmp/Books.$(date+%F).sql
备份多个表:mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名 - 示例三:备份数据结构
-d只备份库结构,不包含数据内容
示例四:增量备份
前提:
1)my.cnf,是要开启MySQL log-bin日志功能,重启MySQL log_bin = /data/mysql/data/mysql-bin
2)存在一个完全备份,生产环境一般凌晨某个时刻进行全备
示例:mysqldump -uroot -p --default-character-set=utf8 --single-transaction -F -B school |gzip > /server/backup/school_$(date +%F).sql.gz InnoDB 表在备份时,通常启用选项 --single-transaction 来保证备份的一致性
MySQL增量恢复案例图解:
准备数据库和表:
create database it DEFAULT CHARACTER SET utf8;
学生表:Student(Sno,Sname,Ssex,Ssage,Ssdept)(学号-主键,姓名,性别,年龄,所在系):
CREATE TABLE `Student` ( `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名', `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄', `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
表中插入数据:
INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
半夜零点手工全备:
mysqldump -uroot -p123456 -F -B it --default-character-set=utf8 --singletransaction -e | gzip > /server/backup/mysql_backup_`date +%F`.sql.gz
备份后继续插入数据:
sleep 60 INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'), (0006,'wangzhao','男',21,'导弹专业');
模拟用户破坏数据:
sleep 30 drop database it;
增量备份–恢复过程
1、检查凌晨备份
2、检查全备后的所有binlog
ls -lrt /usr/local/mysql/data/mysql-bin.*
3、立即刷新并备份出binlog
mysqladmin -uroot -p flush-logs cp /usr/local/mysql/data/mysql-bin.000004 /server/backup/ # 提示:根据时间点及前一个binlog可以知道发现问题时刻前binlog日志为mysql-bin.000004
4、恢复binlog生成sql语句
mysqlbinlog mysql-bin.000004 > bin.log
5、恢复凌晨备份
6、恢复增量备份
mysqlbinlog增量恢复方式
基于时间点恢复
1)指定开始时间到结束时间
myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ --stopdatetime=’2014-10-45 03:10:46’-r time.sql
2)指定开始时间到文件结束
myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ -d esen -r time.sql
3)从文件开头到指定结束时间
myslbinlog mysqlbin.000008 --stop-datetime=’2014-10-45 03:10:46’ -d esen -r time.sql
基于位置点的增量恢复
1)指定开始位置到结束位置
myslbinlog mysqlbin.000008 --start-position=510 --stop-position=1312 -r pos.sql
2)指定开始位置到文件结束
myslbinlog mysqlbin.000008 --start-position=510 -r pos.sql
3)从文件开始位置到指定结束位置
myslbinlog mysqlbin.000008 --stop-position=1312 -r pos.sq
高级备份参数:
-R 备份存储过程及函数 --triggers 备份触发器 -E 备份事件 -F 在备份开始时,刷新一个新binlog日志 --master-data=2 以注释的形式,保存备份开始时间点的binlog的状态信息 示例:[root@mysql mysql]# mysqldump -uroot -p -A -R --triggers --masterdata=2 > /tmp/full.sql [root@mysql mysql]# grep 'CHANGE MASTER' /tmp/full.sql | head -1 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=766;
功能:
(1)在备份时,会自动记录,二进制日志文件名和位置号
0 默认值
1 以change master to命令形式,可以用作主从复制
2 以注释的形式记录,备份时刻的文件名+postion号
(2)自动锁表
(3)如果配合--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行“热“备, 实际上是实现快照备份。
--single-transaction
innodb 存储引擎开启热备(快照备份)功能
master-data 可以自动加锁
(1)在不加--single-transaction ,启动所有表的温备份,所有表都锁定
(2)加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
示例: 备份必加参数 mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gti