备份类型:
热备份:读写不受影响
温备份:仅可执行读备份
冷备份:离线备份,读写均不能执行,关机备份
物理备份和逻辑备份
物理备份:复制数据文件,速度快。
逻辑备份:将数据导出之文本文件中,必要时候,把他还原回去。
完全备份、增量备份、差异备份
完全备份:备份全部数据
增量备份:仅备份上次完全备份以来变化的数据
差异备份:仅备份上次备份或增量备份以后变化的数据
备份是什么?数据、配置文件、二进制日志、事务日志、-+
MyISAM:不支持热备份,借助逻辑卷(LV),否则只能用温备份。
Innodb:支持热备份,必须得使用一些工具(mysqldummp/xtrabackup开源)。
mysqldump:逻辑备份工具,MyISAM(温),Innodb(热备份)
mysqlhotcopy:物理备份工具、温备份/冷备份
cp:冷备份
LV:逻辑卷的快照功能,几乎热备份
mysqldump备份
原理:将整个表中的数据备份为批量插入的insert语句
在备份数据之前必须要做的防护措施
1、将缓冲区中的数据同步到存储硬盘中去 、锁表:flush tables with read lock
2、解锁:unlock tables
3、关闭二进制日志:set sql_log_bin=0;
4、滚动日志:FLUSH LOGS;
命令选项:
--master-data={0|1|2}
0:不记录二进制日志文件及其路径
1:以chnage master to 的方式记录位置,可用于恢复后直接启动从服务器
2:以chnage master to 的方式记录位置,一般默认为被注释掉
Ionndb热备份
--single-transaction --->启动热备分
--all-databases --->备份所有库
--databases 库名,库名 --->备份指定库
MyISAM温备份
--lock-all-tabls --->执行温备份
--lock-tables --->只备份一张表
备份库或者表
[root@pc2 ~]# mysqldump -uroot -p jiaowu jij > /root/ jiaowu.sql
关键字 选项 库名 表名 导出的路径 备份后的文件名
还原库 或者表
还原之前必须得先创建一个新库(sun)
[root@pc2 ~]# mysql -uroot -p -hlocalhost sun < biao.sql ;
指定库名 导入的路径 备份的文件名
备份策略
每周完全备份+每日增量备份
完全备份:mysqldump
增量备份:备份二进制文件文件(备份之前flush logs)
当我们把整所有库都删掉时,要先去初始化数据库
若启动不起来:killall mysqld
逻辑备份
1、浮点数据丢失精度
2、备份出的数据更占用存储空间,压缩后可 大大节省空间
3、不适合对大数据做完全备份
select备份
备份:select * into outfile '/tmp/ss.txt' from courses;
还原的时候注意:要恢复的表格式要事先创建好,然后才能还原。
还原 load data infile '/tmp/sss.txt' into table 表名
使用LVM快照进行数据库备份
set sql_log_bin=0; --->关闭记录二进制文件,开启=1
show master status; --->查看当前二进制日志
SHOW BINLOG EVENTS IN '日志名'; --->查看二进制日志中的事件
前提:
1、数据文件在逻辑卷上
2、此逻辑卷所在卷组必须有足够的空间够快照卷使用
3、数据文件和事务日志要在同一个逻辑卷中
过程
1、启动一个事务:start transaction;
2、在一个表中插入数据:insert into tutors (Tname) values ('stu001');
3、提交事务:commit;
4、刷新表并且给所有表加锁:flush tables with read lock; 并且执行一次flush logs,滚动日志
5、通过另外一个终端保留二进制日志文件及相关位置信息
mysql -urooot -p -hlocalhost -e 'show master status\G' > /路径/master.info
6、创建快照卷
lvcreate -L 大小 -s -p -r -n名称 lv名称 卷组路径
7、释放锁 mysql> unlock tables;
8、挂载快照卷,备份
mount 、 cp -a
9、删除快照卷
10、增量备份二进制日志
二进制日志相关的几个选项
若进不去mysql字符界面:
[client]
socket=/tmp/mysql.sock
xtrabackup数据库备份
下载xtrabackup这个工具:www.percona.com
安装之前会有依赖包:libev-4.15-1.el6.rf.i686.rpm
perl-DBD-MySQL
使用rpm安装xtrabackup :rpm -ivh percona-xtrabackup-2.3.4-1.el6.i686.rpm
xtrabackup
--defaults-file= //mysql主配置文件
--user=root //mysql 用户
--password='oracle' //mysql密码
--socket=/var/lib/mysql/mysql.sock /data/mysql/backup/full //mysqlsock文件,可选!
备份
innobackupex --user=root /usr 备份整个数据库,备份文件放在/usr/下面
备份完成之后,他会在/usr/目录下创建一个以当前时间命令的文件夹!
在这个文件夹里面都有
xtrabackup_binlog_info:二进制文件信息
xtrabackup_logfile :纯数据文件,
xtrabackup_checkpoints :检查点,
backup-my.cnf :备份命令用到的配置选项信息
现在模拟mysql数据库损坏,service mysqld stop ,然后把/mydata/data目录下的所有数据删除!
在备份之后需要做一些准备操作,在能进行还原
1、将那些已经提交的事务同步到数据文件,从日志文件同步到数据文件。
2、将那些尚未提交的事务给他做回滚
使用--apply-log实现回滚,将那些已经提交的事务同步到数据文件
准备操作
innobackupex --appli-log /usr/2016-05-16_16-57-49/
关键字 选项 指定备份目录
当出现completed OK时,你的准备操作已经完成!
当准备操作完成之后又出现新的数据怎么办?
做即时点还原
首先在mysql中做滚动日志,flush logs;
然后把数据目录中的二进制文件读取出来拷贝到另一个地方,以后可以拿来用就可以了!
cp mysql-bin.000005 /root/ (当前位置在/mydata/data)
还原
还原前要保证数据目录中必须是空的!
要想在一个完全备份中恢复数据,只需要使用--copy-back
innobackupex --copy-back /usr/2016-05-16_16-57-49/
选项 备份文件路径
当出现completed OK!时,还原成功!
然后修改数据目录的权限 chown -R mysql:mysql /mydata/data/
然后再进行即时还原,把我们原先cp出去的那个二进制日志文件制作成sql脚本,导入到mysql中!把mysql还原到崩溃的那一刻。
找到cp出去的那个二进制日志文件,读取那个文件并保存为sql脚本
mysqlbinlog /tmp/mysql-bin.000002 > /tmp/sd.sql
进入mysql页面中:
set sql_log_bin=0;
source /tmp/sd.sql; :执行这个cp出去的sql脚本。
set sql_log__bin=1; :还原回设置
这就是xtrabackup +二进制日志进行备份!
xtrabackup支持对Innodb存储引擎做增量备份。
使用xtrabackup实现增量备份
先进行一次完全备份!
要想实现增量备份,加一个选项 --incermental-dir
第一次增量备份:innobackupex --incremental /存放路径 --incremental-basedir 完全备份文件路径
第二次增量备份:innobackupex --incremental /存放路径 --incremental-basedir 上一次增量备份文件的路径
准备操作!
innobackupex --apply-log --redo-only 完全备份文件夹位置
对第一次增量备份合并到完全备份中去
innobackupex --apply-log --redo-only 指定完全备份文件位置 --incremental-dir=第一次增量备份文件位置
对第二次增量本分合并到完全备份中去
innobackupex --apply-log --redo-only 指定完全备份文件位置 --incremental-dir=第二次增量备份文件位置
现在还原的时候,只还原完全备份就可以了,因为,所有的redo操作都已经合并到完全备份中去了。
使用xtrabackup实现增量备份还原
破坏mysql,把数据目录中的文件都删了!
innobackupex --copy-back 指定完全备份位置