一. 备份的目的
做灾难恢复:对损坏的数据进行恢复和还原
需求改变:因需求改变而需要把数据还原到改变以前
测试:测试新功能是否可用
二、备份需要考虑的问题
可以容忍丢失多长时间的数据;
恢复数据要在多长时间内完;
恢复的时候是否需要持续提供服务;
恢复的对象,是整个库,多个表,还是单个库,单个表。
三、备份的类型
1.根据是否需要数据库离线
冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;
温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;
热备(hot backup):备份的同时,业务不受影响。
注:
这种类型的备份,取决于业务的需求,而不是备份工具
MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具
2、根据要备份的数据集合的范围
完全备份:full backup,备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。
差异备份:differential backup 上次完全备份以来改变了的数据。
建议的恢复策略:
完全+增量+二进制日志
完全+差异+二进制日志
3、根据备份数据或文件
1.物理备份:直接备份数据文件
备份和恢复操作都比较简单,能够跨mysql的版本,
恢复速度快,属于文件系统级别的
不要假设备份一定可用,要测试
mysql>check tables;检测表是否可用
2.逻辑备份: 备份表中的数据和代码
恢复简单、
备份的结果为ASCII文件,可以编辑
与存储引擎无关
可以通过网络备份和恢复
备份或恢复都需要mysql服务器进程参与
备份结果占据更多的空间,
浮点数可能会丢失精度
还原之后,缩影需要重建
四:备份的对象
1、 数据;
2、配置文件;
3、代码:存储过程、存储函数、触发器
4、os相关的配置文件
5、复制相关的配置
6、二进制日志
五、备份和恢复的实现
1、利用select into outfile实现数据的备份与还原
mysql> use hqq;//打开数据库
mysql> select * from tb1 into outfile f:/DB/Backup/tb1.bak;//备份数据
mysql> delete from tb1;//删除表中所有数据
mysql> load data local infile 'f:/DB/Backup/tb1.bak' into table tb1;//导入备份文件
注意
如果出现中文乱码 请加上character set utf8
load data local infile 'f:/DB/Backup/tb1.bak' into table tb1 character set utf8;
2、利用mysqldump工具对数据进行备份和还原
mysqldump 常用来做温备,所以我们首先需要对想备份的数据施加读锁,
2.1 施加读锁的方式
1.直接在备份的时候添加选项
–lock-all-tables
是对要备份的数据库的所有表施加读锁
–lock-table
仅对单张表施加读锁,即使是备份整个数据库,它也是在我们备份某张表的时候才对该表施加读锁,因此适用于备份单张表
2.在服务器端书写命令
mysql> flush tables with read lock;
施加锁,表示把位于内存上的表统统都同步到磁盘上去,然后施加读锁
mysql> flush tables with read lock;
释放读锁
但这对于InnoDB存储引擎来讲,虽然你也能够请求道读锁,但是不代表它的所有数据都已经同步到磁盘上,因此当面对InnoDB的时候,我们要使用mysql> show engine innodb status; 看看InnoDB所有的数据都已经同步到磁盘上去了,才进行备份操作。
2.2备份的策略
完全备份+增量备份+二进制日志
1.先给数据库做完全备份
C:\Users\heqianqian>mysqldump -uroot --single-transaction --master-data=2 --databases hqq > f:/DB/Backup/hqq.sql
–single-transaction: 基于此选项能实现热备InnoDB表;因此,不需要同时使用–lock-all-tables;
–master-data=2 记录备份那一时刻的二进制日志的位置,并且注释掉,1是不注释的
–databases hellodb 指定备份的数据库
2.在数据库更新数据
mysql> create table user(id int,name varchar(100));
Query OK, 0 rows affected (0.46 sec)
mysql> insert into user(id,name) values(1,'hqq');
Query OK, 1 row affected (0.08 sec)
mysql> insert into user(id,name) values(2,'mary');
Query OK, 1 row affected (0.16 sec)
mysql> insert into user(id,name) values(3,'jack');
Query OK, 1 row affected (0.07 sec)
mysql> insert into user(id,name) values(4,'lucky');
Query OK, 1 row affected (0.06 sec)
3.进行增量备份
先查看完全备份文件里边记录的位置
打开刚才生成的备份文件 找到下面这一行
-- -- Position to start replication or point-in-time recovery from --
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=120;
可以知道日志文件是mysql-bin.000007 备份文件里记录的位置是120
4.查看当前记录的位置
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1123 | | | | +------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
可以看到当前位置是1123
由此可以知道刚才进行更新操作的记录位置是120-1123
5.完成增量备份
mysqlbinlog --no-defaults --start-position=120 --stop-position=1123 /data/mysql-bin.000013 > f:/DB/Backup/hqq_add.sql
6.再对数据库进行更新操作
mysql> drop table t1;
Query OK, 0 rows affected (0.22 sec)
7.进行二进制文件备份
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1236 | | | | +------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysqlbinlog --no-defaults --start-position=1123 --stop-position=1236 /data/mysql-bin.000013 > f:/DB/Backup/hqq_bin.sql
8.关闭日志文件 并且回滚日志
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.03 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.19 sec)
9.模拟数据库损坏
mysql> drop database hqq;
Query OK, 2 rows affected (0.36 sec)
10.恢复数据
mysql> source f:/DB/Backup/hqq.sql
mysql> source f:/DB/Backup/hqq_add.sql
mysql> source f:/DB/Backup/hqq_bin.sql
注:
1、真正在生产环境中,我们应该导出的是整个mysql服务器中的数据,而不是单个库,因此应该使用–all-databases
2、在导出二进制日志的时候,可以直接复制文件即可,但是要注意的是,备份之前滚动下日志。
3、利用lvm快照实现几乎热备的数据备份与恢复