mysql进阶(一) mysql备份
备份的目的:实现灾难恢复:误操作、硬件故障、软件故障、自然灾害、******
注意的要点:
1、能够容忍丢失多少数据
2、恢复数据所用的时间
3、备份需要的时间
4、是否对业务有影响
5、备份时服务器负载
备份类型
完全备份:备份整个数据库的数据
部分备份:备份指定的一部分数据
增量备份:备份上一次备份后增加的数据
差异备份:备份从最后一次完全备份后起发生改变的数据
热备份:读写操作均可以进行
温备份:只读
冷备份:不可写,不可读
逻辑备份:从数据库中导出数据,与存储引擎无关
物理备份:直接复制数据文件
备份需要:
数据
二进制日志,事务日志
配置文件
备份的设计方案:
数据:完全+增量、完全+部分
备份手段:逻辑或物理
常用的备份工具:
mysqldump:mysql自带的备份工具,逻辑备份、完全备份、部分备份、温备
lvm2快照:几乎热备,需要借助cp,tar等工具完成,lvm2只负责生成快照
xtrabackup:支持对InnoDB的热备份(物理备份
tar,cp:冷备份
mysqldump工具使用:逻辑备份工具,在数据小于1G时可以使用,数据大于1G之后则不建议使用
mysqldump database #不会自动创建数据库
mysqldump --databases DB1 #备份指定数据库,会自动创建数据库
mysqldump --all-databases #备份所有数据库,会自动创建数据库
常用选项:
-u#用户
-p#密码
--flush-logs #执行二进制日志文件滚动
--single-transaction#对InnoDB存储引擎进行热备份(只有InnoDB支持)
--master-data=2 #在数据文件中,记录备份那一刻二进制日志文件的位置,
并注释主要用于参考,多用于进行基于时间点还原
--lock-all-tables #在执行备份时锁定所有库的所有表(二选一,必须)
--lock-tables #对单个数据库在备份时锁定所有表(二选一,必须)
例:mysqldump -uroot -p123.com --databases --lock-tables --flush-logs
--master-data=2 test > /tmp/mysql.back.`date "+%F__%R"`
LVM2快照实现数据几乎热备
实现条件:mysql数据存储在LVM逻辑卷中,需要有足够的空间做快照
备份策略:
1、创建全局锁
mysql> FLUSH TABLES WITH READ LOCK;
2、记录二进制日志文件及事件位置
mysql -uroot -p123.com -e 'FLUSH LOGS;'#进行日志滚动
mysql -uroot -p123.com -e 'SHOW MASTER STATUS;'>/tmp/test.`date +%F`
#记录二进制日志位置
3、创建LVM快照
lvcreate -L 1GB -s -n test_snap /dev/vg_test/lv_test
-L 1G #快照大小,一般和数据文件差不多
-s #表示创建快照
-n test_snap #快照名
/dev/vg_test/lv_test #要创建快照的逻辑卷
4、释放全局锁
mysql> UNLOCK TABLES;
5、挂载快照卷
mount /dev/vg_test/test_snap /mnt
6、备份数据并删除快照卷
cp -a /mnt/date/mysql /tmp#复制数据文件
umount /mnt#卸载快照卷
lvremove /dev/vg_test/test_snap #删除快照
7、制定好备份计划,每隔一段时间对二进制文件进行备份
每隔一段时间对mysql的二进制文件进行备份,以实现将来的基于时间点还原数据
例:cp -p /data/mysql-bin.000007 /tmp/mysql_7.sql#备份二进制日志
8、还原
还原时只需要将数据文件复制到mysql的数据目录中,在使用二进制日志对数据进行时间点还原
例:cp -a /tmp/mysql /data/mysql#还原数据
mysql> source /tmp/mysql_7.sql;#进行基于时间点的还原
xtrabackup实现mysql热备:
注意:xtrabackup只能对InnoDB实现热备和增量备份
https://www.percona.com/downloads/XtraBackup/LATEST/#下载地址
安装:
1、配置好epel源
2、yum -y localinstall percona-xtrabackup-2.3.2-1.el6.x86_64.rpm #安装
备份工具:
xtrabackup包含两个主要的工具,即xtrabackup和innobackupex
innobackupex:是xtrabackup重封装的Perl脚本,能对innodb和myisam备份,
对myisam不能实现热备和增量备份
xtrabackup:xtrabackup只能备份innodb和xtradb两种引擎的表,
而不能备份myisam引擎的表
innobackupex基本使用
常用参数:
--help#查看帮助信息
--backup#进行备份操作,可省,默认为执行备份操作
--apply-log#恢复前必须的整理工作
--defaults-file#指定配置文件路径,可省,会自动到数据库中读取
--redo-only #在做整理操作时,未提交的数据不进行回滚,做增量备份恢复时必须加的选项
--copy-back#执行还原操作
--incremental#执行增量备份
--user=name#指定用户名,默认为root
--host=name#指定主机名,默认为host
--port=#指定端口,默认为3306
--password=name#指定密码,默认为空
--databases='db1 bd2' #备份指定的数据库,
--defaults-file=#指定配置文件,可省
--no-timestamp=#自定义备份目录名
incremental-basedir=#基于那一次备份做增量备份
使用实例:
innodb_file_per_table=1 #在mysql配置文件中添加,表示每个表使用单独的表空间文件
datadir=/usr/local/mysql/data#在mysql配置文件中添加,表示指定数据文件位置
mkdir /backups#创建备份文件存放目录
全库完全备份:
innobackupex --user=root --password=123.com /backups/
/backups/#备份文件存放目录
注意:结尾出现completed OK!则为成功,否则则为错误
还原一个完全备份:
innobackupex --apply-log /backups/2017-02-20_04-40-44/#对备份文件进行整理
innobackupex --copy-back /backups/2017-02-20_04-40-44/#还原数据
chown mysql:mysql /usr/local/mysql/*#修改权限
增量备份:
innobackupex --user=root --password=123.com--incremental /backups/
--incremental-basedir=/backups/2017-02-20_04-40-44/
/backups/#增量备份存储位置
--incremental-basedir#指定基于哪一个备份文件进行增量备份,通常都是上一个增量备份
增量备份还原:
innobackupex --apply-log--redo-only /backups/2017-02-20_05-01-49/#整理完全备份
innobackupex --apply-log--redo-only /backups/2017-02-20_05-01-49
--incremental-dir=/backups/2017-02-20_05-04-34#整理第一个增量备份
innobackupex --copy-back /backups/2017-02-20_05-01-49/#还原数据
部分备份:
innobackupex --databases="testdb mysql" /backups/ --user=root --password=123.com
#备份testdb和mysql数据库,不管备份那个数据库都应该备份mysql数据库
还原部分备份:
innobackupex --apply-log /backups/2017-02-20_05-24-07/#准备
innobackupex --copy-back /backups/2017-02-20_05-24-07/#还原
chown mysql:mysql /usr/local/mysql/*#修改权限
页:
[1]