使用mysqldump进行数据的备份与还原
备份数据的最终目的是为了在出现一些意外情况时,能够通过备份将数据还原,所以单单的备 份数据往往是无法满足还原时的需求的,所以在备份数据库时,除了要备份数据本身,还要备
份相关的数据库环境,如配置文件,定时任务,sudo权限等等相关的信息
一、为什么要备份
1、灾难恢复:硬件故障,软件故障,自然灾害,黑客攻击,误操作
2、测试
二、备份要注意的要点
1、备份需要多少时间(备份过程的时长)
2、能容忍最多丢失多少数据
3、恢复数据需要在多场时间内完成(恢复过程的时长)
4、需要恢复哪些数据
(1)做还原测试,用于测试备份的可用性
(2)还原演练
5、备份负载
三、备份内容
1、数据
2、二进制日志,innodb的事务日志
3、代码(存储过程、存储函数、触发器、时间调度器)
4、服务器的配置文件
四、备份相关术语
1、完全备份(全量备份)(Full Backup)
对某个时间点的所有数据进行一个完全的备份,对应时间点的所有数据都被包含在完全备 份中。(备份整个数据集)
2、部分备份
只备份数据子集
3、增量备份
仅备份最近一次完全备份或增量备份(如果有增量备份)以来变化的数据
4、**差异备份** 仅备份最近一次完全备份以来变化的数据;
5、热备
在数据库正常运行的情况下进行备份,读写操作均可执行(往往依赖于事务日志)(难度 最大)(myisam存储引擎不支持热备,Innodb存储引擎支持热备)
6、冷备
读写操作均不能执行(停数据库后进行文件拷贝即可)
7、物理备份
直接备份数据库所对应的数据文件,与存储引擎无关(cp),相对于逻辑备份来说,性能
更强
8、逻辑备份
从数据库中“导出”数据另存而进行备份
五、设计备份方案
1、数据集:完全+增量+二进制日志|完全+差异+二进制日志
2、备份手段:物理,逻辑(物理备份恢复较快,逻辑备份恢复较慢)
对于备份较大的数据建议物理备份,对于较小的数据建议用逻辑备份。
六、备份工具的选择
1、cp命令或tar命令
在Linux中直接对数据文件进行备份,这种方式只适用于冷备的方式 2、通过select语句进行部分备份
通过select语句将表中的数据导出到指定文件中。 在使用select语句备份的同时,最好将表架构也备份一份,因为还原的时候可以用到。
3、mysqldump+复制binlog:
mysqldump:完全备份(部分备份)(逻辑备份工具) 复制binlog中指定时间范围内的event:增量备份
mysqldump是mysql自带的备份工具,它是一种逻辑备份工具,也就是说,它会将数据 从数据库中读出,转化为对应的sql语句。
mysqldump能够实现完全备份或部分备份 使用innodb表类型的表能够使用mysqldump进行热备 使用myisam表类型的表只能够使用mysqldump进行温备 如果数据量较小,可以选择使用mysqldump。 原理:
通过协议连接到mysql数据库,将需要备份的数据查询出来,将查询出的数据转换成对应 的insert语句,还原这些数据时,只要执行这些insert语句即可将对应的数据还原。
优点:
可以直接使用文本处理工具处理对应的备份数据,因为备份数据已经被mysqldump转换 成了对应的insert语句,所以,我们可以借助文件系统中的文本处理工具对备份数据进行直接 处理。
缺点:
当数据为浮点类型时,会出现精度丢失
它的备份过程属于逻辑备份,其备份速度、恢复速度与物理备份工具相比较慢,而且 mysqldump备份的过程是串行化的(mydumper可并行备份),不会并行的进行备份;当数 据量较大时,其效率较低
4、lvm2快照+复制binlog:
lvm2快照:(做快照的时候不能有任何的数据写入)(几乎热备(备份速度快)) lvm2快照:适用cp或者tar等做物理备份:完全备份 复制binlog中指定时间范围内的event:增量备份
5、xtrabackup|mariabackup:
由Percona提供的支持对InnoDB做热备(物理备份)工具(开源免费的)
支持完全备份、差异备份、增量备份、部分备份等功能
七、备份恢复补充知识
1、Linux上查看及测试配置文件顺序
[root@mysql ~]# mysql --help | grep 'my\.cnf'order of preference, f, $MYSQL_TCP_PORT,/etc/f /etc/mysql/f /usr/etc/f ~/.f
注意:~/.f mysql家目录下的.f 优先级最高。
windows上查看:mysql --help | findstr f
2、 binlog的正确删除方法:
1> reset master;删除所有binlog日志文件(除mysql-bin.index文件)2> purge master logs to mysql-bin.****** 将******编号之前的binlog日志文件删除3> purge master logs before 'yyyy-mm-dd hh24:mi:ss'删除 yyyy-mm-dd hh24:mi:ss日期之前产生的所有日志4> 在f配置文件中设置参数 expire_logs_days=# 即binlog日志的过期天数 过了指定日期之后日志将会被自动删除.
3、mysqlbinlog常用参数
--database 仅仅列出配置的数据库信息--no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令--offset 跳过 log 中 N 个条目--verbose 将日志信息重建为原始的 SQL 陈述。-v 仅仅解释行信息-vv 不但解释行信息,还将 SQL 列类型的注释信息也解析出来--start-datetime 显示从指定的时间或之后的时间的事件。接收 DATETIME 或者 TIMESTRAMP 格式。--base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示,对一些二进制的内容进行屏蔽。AUTO 默认参数,自动显示 BINLOG 中的必要的语句NEVER 不会显示任何的 BINLOG 语句,如果遇到必须显示的 BINLOG 语言,则会报错退出。DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息,过滤到一些 BINLOG 二进制数据。-r, --result-file=name 结果文件,相当于> filename
注意:
MySQL 5.7 中无论是否打开 GTID 的配置,在每次事务开启时,都首先会出 GTID 的一个事务,用于并行复制。所以在确定导出开始事务位置时,要算上这个事件。
在使用 --stop-position 导出时,会导出在指定位置的前一个事件,所以这里要推后一个事务。
对于 DML 的语句,主要结束位置要算上 COMMIT 的位置。
4、mysqlbinlog恢复数据
在执行数据恢复前,如果操作的是生产环境,会有如下的建议:
使用 flush logs 命令,替换当前主库中正在使用的 binlog 文件,好处如下:
1> 可将误删操作,定位在一个 BINLOG 文件中,便于之后的数据分析和恢复。
2> 避免操作正在被使用的 BINLOG 文件,防止发生意外情况。
3> 数据的恢复不要在生产库中执行,先在临时库恢复,确认无误后,再倒回生产库。防止对数据的二次伤害。
八、备份恢复的策略-mysqldump
恢复主要有两个步骤:
1.在临时库中,恢复定期执行的全量备份数据。
2.然后基于全量备份的数据点,通过 BINLOG 来恢复误操作和正常的数据。
(完全备份+增加备份,速度相对较慢,适合中小型数据库)
mysqldump 常用参数
-A, --all-databases 备份所有数据库-B, --databases 备份时多了create database dbname和 use dbname-F, --flush-logs 刷新日志-x, --lock-all-tables 锁表-d, --no-data 不备份数据
1、备份db1数据库并压缩
# mysqldump -uroot -pABCabc123! -B db1 | gzip > db1.sql.gz
2、只备份db1数据库结构
# mysqldump -uroot -pABCabc123! -d -B db1 | gzip > db1_2.sql.gz
3、备份数据库db1的Authors和Books 表
mysqldump -uroot -pABCabc123! db1 Authors Books -r db1_table1.sql
1)还原数据库:
方法1:mysql -uroot -p密码 db1 < db1_table1.sql
还原db1数据库:
zcat db1.sql.gz | mysql -uroot -pABCabc123!
还原db1的Authors和Books 表:
mysql -uroot -pABCabc123! db1 < db1_table1.sql
方法2:mysql命令行使用source
mysql -uroot -pABCabc123! -e 'use db1;source db1_table1.sql;'
2)增量备份:binlog
第一个前提:
1)f,是要开启MySQL log-bin日志功能,重启MySQL
2)存在一个完全备份,生产环境一般凌晨某个时刻进行全备
创建示例数据库:
mysql> create database db3;
创建表
mysql> use db3Database changedmysql> 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;Query OK, 0 rows affected (0.00 sec)
插入测试数据:
mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');Query OK, 4 rows affected (0.02 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from Student;+-----+--------+------+------+-----------------+| Sno | Sname | Ssex | Sage | Sdept |+-----+--------+------+------+-----------------+| 1 | 陆亚 | 男 | 24 | 计算机网络|| 2 | tom | 男 | 26 | 英语 || 3 | 张阳 | 男 | 21 | 物流管理 || 4 | alex | 女 | 22 | 电子商务 |+-----+--------+------+------+-----------------+4 rows in set (0.00 sec)
第二个前提条件:完全备份
[root@node5 ~]# mysqldump -uroot -pABCabc123! -B db3 | xz > db3.$(date +%F).sql.xz
继续插入数据:
mysql> INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from Student;+-----+----------+------+------+-----------------+| Sno | Sname | Ssex | Sage | Sdept |+-----+----------+------+------+-----------------+| 1 | 陆亚| 男 | 24 | 计算机网络|| 2 | tom| 男 | 26 | 英语 || 3 | 张阳| 男 | 21 | 物流管理 || 4 | alex| 女 | 22 | 电子商务 || 5 | xumubin | 男 | 29 | 中文专业 || 6 | wangzhao | 男 | 21 | 导弹专业 |+-----+----------+------+------+-----------------+6 rows in set (0.00 sec)
误操作删除了表Student
mysql> drop table Student;
恢复数据:
1、刷新日志
mysql> flush logs;mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| node5-bin.000003 |154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
2、还原完全备份
[root@node5 ~]# xzcat db3.-07-10.sql.xz | mysql -uroot -pABCabc123!
恢复了 前4条数据
mysql> select * from Student;+-----+--------+------+------+-----------------+| Sno | Sname | Ssex | Sage | Sdept |+-----+--------+------+------+-----------------+| 1 | 陆亚 | 男 | 24 | 计算机网络|| 2 | tom | 男 | 26 | 英语 || 3 | 张阳 | 男 | 21 | 物流管理 || 4 | alex | 女 | 22 | 电子商务 |+-----+--------+------+------+-----------------+4 rows in set (0.00 sec)
3、使用binlog日志恢复全备之后的数据
查看binlog日志
[root@node5 ~]# mysqlbinlog --base64-output=decode-rows -v /usr/local/mysql/data/node5-bin.000002
使用结束时间
mysqlbinlog /usr/local/mysql/data/node5-bin.000002 --start-datetime='-07-10 15:57:48' --stop-datetime='-07-10 15:58:40' -r time1.sql
使用结束位置
mysqlbinlog /usr/local/mysql/data/node5-bin.000002 --start-position=1185 --stop-position=1509 > pos1.sql
还原增量数据:
mysql -uroot -pABCabc123! db3 < time1.sql