MySQL备份概述
备份和冗余有什么区别?
- 备份:能够防止由于机械故障以及人为误操作带来的数据丢失,例如将数据库文件保存在了其它
- 冗余:数据有多份冗余,但不等备份,只能防止机械故障带来的数据丢失,例如主备模式、数据库集群。
备份什么
- 数据库:一堆物理文件的集合;日志文件+数据文件+配置文件
- DB BINLOG my.cnf
备份过程须考虑的因素
- 必须制定详细的备份计划(策略)(备份频率、时间点、周期)
- 备份数据应该放在非数据库本地,并建议有多份副本
- 必须做好数据恢复的演练(每隔一段时间,对备份的数据在测试环境中进行模拟恢复,保证当出现数据灾难的时候能够及时恢复数据。)
- 根据数据应用的场合、特点选择正确的备份工具。
- 数据的一致性
- 服务的可用性
备份类型
逻辑备份
- 备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL)。
- 适用于中小型数据库,效率相对较低。一般在数据库正常提供服务的前提下进行,如:mysqldump、mydumper、into outfile (表的导出导入)等。
物理备份
- 直接复制数据库文件
- 适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。
- 一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份);如: tar、cp、xtrabackup(数据库可以正常提供服务) 、lvm snapshot、rsync等
在线热备(冗余)
- MySQL的replication架构,如M-S |M-S-S |M-M-S等
- 实时在线备份
备份工具
社区版安装包中的备份工具:
-
mysqldump(逻辑备份,只能全量备份)
- 企业版和社区版都包含
- 本质上使用SQL语句描述数据库及数据并导出
- 在MYISAM引擎上锁表,Innodb引擎上锁行
- 数据量很大时不推荐使用
-
mysqlhotcopy (物理备份工具)
- 企业版和社区版都包含
- perl写的一个脚本,本质上是使用锁表语句后再拷贝数据
- 只支持MYISAM数据引擎
企业版安装包中的备份工具:
-
mysqlbackup
- 在线备份
- 增量备份
- 部分备份
- 在某个特定时间的一致性状态的备份
-
第三方备份工具:
-
XtraBackup和innobackupex(物理备份)
* Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。
- Xtrabackup有两个主要的工具: xtrabackup、innobackupex
- xtrabackup只能备份InnoDB和XtraDB两种数据表,不能备份myisam类型的表。
- Xtrabackup有两个主要的工具: xtrabackup、innobackupex
-
-
mydumper(逻辑备份)
- 多线程备份工具 https://launchpad.net/mydumper/mydumper-0.9.1.tar,gz 2015-11-06 (最后更新时间)
备份方法
- 完全备份(全备)
- 增量备份(增量备份基于全量备份)
MySQL的逻辑备份
mysqldump备份
- 本质:导出的是sql语句文件
- 优点:无论是什么存储引擎,都可以用mysqldump备成sql语句
- 缺点:速度较慢,导入时可能会出现格式不兼容的突发状况.无法直接做增量备份
- 提供三种级别的备份,表级,库级和全库级
基本语法
#表级别备份
mysqldump [OPTIONS] database [tables]
#库级别备份
mysqldump[OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
#全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]
常用参数
常用参数:
--f1ush-logs, -F 开始备份前刷新日志
--flush-privileges 备份包含mysql数据库时刷新授权表
--lock-all-tables ,-x 服务可用性(针对所有库所有表)
--lock-tables,-l 备份前锁表(针对要备份的库)
--single-transaction 适用InnoDB引擎,保证一致性,服务可用性
--master-data=2
表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行;
--master-data=1
表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行;
--master-data参数其他说明:
1)恢复时会执行,默认是1
2)需要RELOAD privilege并必须打开二进制文件
3)这个选项会自动打开--lock-all-tables,关闭--lock-tables
举例说明
表级备份:
# mysqldump -p db01 emp > /mysqlbak/emp.sql 备份单个表
# mysqldump -p db01 emp dept > /mysqlbak/dept_emp.sql备份多个表
表级恢复:
#mysql -p db01 </mysqlbak/emp.sql;
或者在mysql数据库内使用s ource命令来执行外部的sql文件
mysql>source /mysqlbak/dept_emp.sql;
库级备份:
#mysqldump -p --databases db01 >/mysqlbak/db01.sql 备份单个库
#mysqldump -p --databases db01 db02 >/mysqlbak/db01_02.sql 备份多个库
库级恢复:
# mysql -p </mysqlbak/db01.sql
mysql> source /mysqlbak/db01.sql
全库级备份:
mysqldump -p --all-databases --master-data --single-transaction > all.sql
或
mysqldump -p -A --master-data --single-transaction > all.sql
使用drop 命令删除mysql或其它数据库,模拟数据库被删除意外
全库级恢复
#mysql -p < /mysqlbak/all.sql
模拟删除了mysql数据库:不重启mysqld服务与重启mysqld服务
总结:
- mysqldump工具备份的是SQL语句,故备份不需要停服务
- 使用备份文件恢复时,要保证数据库处于运行状态
- 只能实现全库,指定库,表级别的某一时刻的备份,本身不能增量备份
- 适用于中小型数据库
mysqldump+binlog增量备份
结合二进制日志增量备份核心思路:
- 备份的目的为了数据出问题进行恢复
- 二进制日志文件默认会记录下所有对数据库数据变化的操作(增、删、改)
- 二进制文件同时还会记录具体sql执行时的环境、时间以及起始位置和结束位置(pos值)
- 通过获取二进制文件里的SQL操作来进行数据的恢复
思路
- 先做全量备份(mysqldump)
- 对数据库做更改操作
- 模拟故障进行测试验证
- 备份二进制日志文件
- 数据恢复
- 测试验证
实战案例:恢复误删除的表
案例说明:每天2:30做完全备份,早上10:00误删除students,10:10才发现故障,现需要将数据库还原到10:10的状态,且恢复被删除的students表
#完全备份
[root@localhost mysqlbak]# mysqldump -p --single-transaction --flush-logs --master-data=2 --all-databases >/mysqlbak/all_`date +%F_%T`.sql
Enter password:
[root@localhost mysqlbak]# ll
总用量 1593168
-rw-r--r--. 1 root root 1631400589 12月 4 13:20 all_2022-12-04_13:19:44.sql
#完全备份后数据更新
mysql> insert into dept values(7,'测试部') ;
Query OK, 1 row affected (0.04 sec)
mysql> insert into dept values(8,'实施运维部');
#10:00误删除了一个重要的表
mysql> drop table emp;
Query OK, 0 rows affected (0.04 sec)
#后续其它表继续更新
mysql> use db02;
mysql> insert into emp values(7,'曹操',40,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into emp values(8,'刘备',45,1);
Query OK, 1 row affected (0.04 sec)
mysql> select * from emp;
+----+-----------+------+---------+
| id | name | age | dept_id |
+----+-----------+------+---------+
| 1 | 张无忌 | 20 | 1 |
| 2 | 杨逍 | 33 | 1 |
| 3 | 赵敏 | 18 | 2 |
| 4 | 常遇春 | 43 | 2 |
| 5 | 小昭 | 19 | 3 |
| 6 | 韦一笑 | 48 | 3 |
| 7 | 曹操 | 40 | 1 |
| 8 | 刘备 | 45 | 1 |
+----+-----------+------+---------+
8 rows in set (0.01 sec)
#10:10发现表删除,进行还原
#停止数据库访问
#从完全备份中,找到二进制位置
[root@localhost mysqlbak]# grep '\-\- CHANGE MASTER TO' /mysqlbak/all_2022-12-04_13:19:44.sql;
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=154;
#备份从完全备份后的二进制日志
[root@localhost mysql] mysqlbinlog --no-defaults --start-position=154 /var/lib/mysql/binlog.000003 > /mysqlbak/inc.sql
#找到误删除的语句,从备份中删除此语句
[root@localhost mysql] vim /data/inc.sql
#DROP TABLE `emp` /* generated by server */
#如果文件过大,可以使用sed实现
[root@localhost mysql] sed -i.bak '/^DROP TABLE/d' /data/inc.sql
#利用完全备份和修改过的二进制日志进行还原
[root@localhost mysql] mysql -uroot -p
mysql> set sql_log_bin=0;
mysql> source /mysqlbak/all_2022-12-04_13:19:44.sql;
mysql> source /mysqlbak/inc.sql;
mysql> set sql_log_bin=1;
- 完全备份注意开启日志功能,修改my.cnf文件,修改[mysqld]配置
log_bin=binlog server-id=1
总结
- mysqldump工具只能恢复到备份那一时刻
- 通过二进制日志来恢复到最新数据
- 二进制日志也可以跳过sql恢复
- 二进制日志恢复数据的重点就是找对相应的position值或者时间点
定时任务&定时备份
crond 是linux下用来周期性的执行某种任务或等待处理某些事件的一个守护进程,与windows下的计划任务类似,当安装完成操作系统后,默认会安装此服务 工具,并且会自动启动crond进程,crond进程每分钟会定期检查是否有要执行的任务,如果有要执行的任务,则自动执行该任务。
任务调度分类
系统任务调度的配置文件
Linux下的任务调度分为两类,系统任务调度和用户任务调度。
系统任务调度:系统周期性所要执行的工作,比如写缓存数据到硬盘、日志清理等。在/etc目录下有一个crontab文件,这个就是系统任务调度的配置文件。
[root@localhost mysqlbak]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
用户定义的crontab
用户任务调度:用户定期要执行的工作,比如用户数据备份、定时邮件提醒等。用户可以使用 crontab 工具来定制自己的计划任务。所有用户定义的crontab 文件都被保存在 /var/spool/cron目录中。其文件名与用户名一致。
使用者权限文件:
文件:/etc/cron.deny
说明:该文件中所列用户不允许使用crontab命令
文件:/etc/cron.allow
说明:该文件中所列用户允许使用crontab命令
文件:
/var/spool/cron/
说明:
所有用户crontab文件存放的目录,以用户名命名
crontab文件的含义:
用户所建立的crontab文件中,每一行都代表一项任务,每行的每个字段代表一项设置,它的格式共分为六个字段,前五段是时间设定段,第六段是要执行的命令段,格式如下:
minute hour day month week command
其中:
minute: 表示分钟,可以是从0到59之间的任何整数。
hour:表示小时,可以是从0到23之间的任何整数。
day:表示日期,可以是从1到31之间的任何整数。
month:表示月份,可以是从1到12之间的任何整数。
week:表示星期几,可以是从0到7之间的任何整数,这里的0或7代表星期日。
command:要执行的命令,可以是系统命令,也可以是自己编写的脚本文件 在以上各个字段中,还可以使用以下特殊字符:
星号():代表所有可能的值,例如month字段如果是星号,则表示在满足其它字段的制约条件后每月都执行该命令操作。
逗号(,):可以用逗号隔开的值指定一个列表范围,例如,“1,2,5,7,8,9”
中杠(-):可以用整数之间的中杠表示一个整数范围,例如“2-6”表示“2,3,4,5,6”
正斜线(/):可以用正斜线指定时间的间隔频率,例如“0-23/2”表示每两小时执行一次。同时正斜线可以和星号一起使用,例如/10,如果用在minute字段,表示每十分钟执行一次。
crond服务
安装crontab:
yum install crontabs
服务操作说明:
/sbin/service crond start //启动服务
/sbin/service crond stop //关闭服务
/sbin/service crond restart //重启服务
/sbin/service crond reload //重新载入配置
/sbin/service crond status //启动服务
查看crontab服务是否已设置为开机启动,执行命令:
ntsysv
加入开机自动启动:
chkconfig –level 35 crond on
crontab命令详解
命令格式:
crontab [-u user] file
crontab [-u user] [ -e | -l | -r ]
命令功能:
通过crontab 命令,我们可以在固定的间隔时间执行指定的系统指令或 shell script脚本。时间间隔的单位可以是分钟、小时、日、月、周及以上的任意组合。这个命令非常设合周期性的日志分析或数据备份等工作。
命令参数:
-u user:用来设定某个用户的crontab服务,例如,“-u ixdba”表示设定ixdba用户的crontab服务,此参数一般有root用户来运行。
file:file是命令文件的名字,表示将file做为crontab的任务列表文件并载入crontab。如果在命令行中没有指定这个文件,crontab命令将接受标准输入(键盘)上键入的命令,并将它们载入crontab。
-e:编辑某个用户的crontab文件内容。如果不指定用户,则表示编辑当前用户的crontab文件。
-l:显示某个用户的crontab文件内容,如果不指定用户,则表示显示当前用户的crontab文件内容。
-r:从/var/spool/cron目录中删除某个用户的crontab文件,如果不指定用户,则默认删除当前用户的crontab文件。
-i:在删除用户的crontab文件时给确认提示。
常用方法:
创建一个新的crontab文件
在考虑向cron进程提交一个crontab文件之前,首先要做的一件事情就是设置环境变量EDITOR。cron进程根据它来确定使用哪个编辑器编辑 crontab文件。99%的UNIX和LINUX用户都使用vi,如果你也是这样,那么你就编辑$HOME目录下的. profile文件,在其 中加入这样一行:
EDITOR=vi; export EDITOR
然后保存并退出。不妨创建一个名为 cron的文件,其中是用户名,例如, davecron。在该文件中加入如下的内容。
(put your own initials here)echo the date to the console every
15minutes between 6pm and 6am
0,15,30,45 18-06 * * * /bin/echo ‘date’ > /dev/console
保存并退出。确信前面5个域用空格分隔。
在上面的例子中,系统将每隔1 5分钟向控制台输出一次当前时间。如果系统崩溃或挂起,从最后所显示的时间就可以一眼看出系统是什么时间停止工作的。在有些 系统中,用tty1来表示控制台,可以根据实际情况对上面的例子进行相应的修改。为了提交你刚刚创建的crontab文件,可以把这个新创建的文件作为 cron命令的参数:
$ crontab davecron
现在该文件已经提交给cron进程,它将每隔1 5分钟运行一次。
同时,新创建文件的一个副本已经被放在/var/spool/cron目录中,文件名就是用户名(即dave)。
列出crontab文件
为了列出crontab文件,可以用:
$ crontab -l
0,15,30,45,18-06 * * * /bin/echo date
> dev/tty1
你将会看到和上面类似的内容。可以使用这种方法在$ H O M E目录中对crontab文件做一备份:
$ crontab -l > $HOME/mycron
这样,一旦不小心误删了crontab文件,可以用上一节所讲述的方法迅速恢复。
编辑crontab文件
如果希望添加、删除或编辑crontab文件中的条目,而E D I TO R环境变量又设置为v i,那么就可以用v i来编辑crontab文件,相应的命令为:
$ crontab -e
可以像使用v i编辑其他任何文件那样修改crontab文件并退出。如果修改了某些条目或添加了新的条目,那么在保存该文件时, c r o n会对其进行必要的完整性检查。如果其中的某个域出现了超出允许范围的值,它会提示你。
我们在编辑crontab文件时,没准会加入新的条目。例如,加入下面的一条:
DT:delete core files,at 3.30am on 1,7,14,21,26,26 days of each month
30 3 1,7,14,21,26 * * /bin/find -name “core’ -exec rm {} ;
现在保存并退出。最好在crontab文件的每一个条目之上加入一条注释,这样就可以知道它的功能、运行时间,更为重要的是,知道这是哪位用户的作业。
现在让我们使用前面讲过的crontab -l命令列出它的全部信息:
$ crontab -l
(crondave installed on Tue May 4 13:07:43 1999)
DT:ech the date to the console every 30 minites
0,15,30,45 18-06 * * * /bin/echo date
> /dev/tty1
DT:delete core files,at 3.30am on 1,7,14,21,26,26 days of each month
30 3 1,7,14,21,26 * * /bin/find -name “core’ -exec rm {} ;
删除crontab文件
要删除crontab文件,可以用:
$ crontab -r
恢复丢失的crontab文件
如果不小心误删了crontab文件,假设你在自己的$ H O M E目录下还有一个备份,那么可以将其拷到/var/spool/cron/,其中是用户名。如果由于权限问题无法完成拷贝,可以用:
$ crontab
其中,是你在H O M E目录中保存一个该文件的副本。我就有过类似的经历,有数次误删了crontab文件(因为r键紧挨在e键的右边)。这就是为什么有些系统文档建议不要直接编辑crontab文件,而是编辑该文件的一个副本,然后重新提交新的文件。有些crontab的变体有些怪异,所以在使用crontab命令时要格外小心。如果遗漏了任何选项,crontab可能会打开一个空文件,或者看起来像是个空文件。这时敲delete键退出,不要按,否则你将丢失crontab文件。
定时备份
创建脚本并赋权
# 编辑mysql_backup.sh之后的文件内容
[root@localhost mysqlbak]# cat mysql_backup.sh
mysqldump -psasa --single-transaction --flush-logs --master-data=2 --all-databases >/mysqlbak/all_`date +%F_%T`.sql
[root@localhost mysqlbak]# pwd
/mysqlbak
#添加可执行权限
[root@localhost mysqlbak]# chmod +x mysql_backup.sh
#查看文件权限
[root@localhost mysqlbak]# ll mysql_backup.sh
-rwxr-xr-x. 1 root root 117 11月 24 10:24 mysql_backup.sh
创建计划
#创建计划,* * * * *每分钟执行一次
[root@localhost mysqlbak]# crontab -e
* * * * * /mysqlbak/mysql_backup.sh
#查询计划
[root@localhost mysqlbak]# crontab -l
* * * * * /mysqlbak/mysql_backup.sh