在生产环境中,对数据库进行备份是十分重要的,数据库运行异常或者是运维人员误操作,造成数据丢失,可以将备份进行快速恢复,最大程度地降低损失。当然,数据库地备份也有多种方法,例如简单地冷备和稍微复杂点的逻辑备份,都可以借助工具轻松实现。根据不同的业务场景,选择合适的备份策略,已备后患。这里主要介绍了一些针对Mysql的备份方法,着重总结了使用XtraBackup工具中的innobackupex方法,对数据库进行逻辑增量备份,以及恢复方法。
一、数据库备份概述
1、数据备份方式
(1)简单备份
—冷备:将数据库直接拷贝,打包压缩,cp、tar、........
—热备:mysqlhotcopy
(2)逻辑备份
—mysqldump
—mysql
2、数据备份策略
(1)全量备份
备份数据库全部内容
一台数据库服务器上的所有数据
一个库的所有数据
一张表的所有数据
(2)差异备份
备份自上一次完全备份之后有变化的数据
(3)增量备份
备份自上一次备份(包含完全备份、差异备份、增量备份)之后有变化的数据
3、为什么要对数据做备份
数据库出现异常,数据丢失时,用备份文件恢复数据。
4、备份策略选择
完全备份+差异备份
完全备份+增量备份
5、备份选择
数据库备份要考虑地因素:
备份方式
备份策略
备份时间
备份频率
二、物理备份
1、物理备份
直接拷贝库或表的文件。
全库备份:
cp -r /var/lib/mysql/mysql /bakdir/
单表备份:
cp /var/lib/mysql/mysql/user. /bakdir/
压缩:
tar -zcvf /bakdir/mysql.tar.gz /var/lib/mysql/mysql/*
3、hotcopy物理热备
—只适用于myisam引擎
安装依赖包
`#yum -y install perl-DBD-MySQL ``
命令格式:mysqlhotcopy -u[用户名] -p[密码] [库名] [备份位置]
#mkdir /mydata
#mysqlhotcopy -u root -p 999 userdb /mydata
4、恢复操作
恢复命令:
cp -rp [备份库] [数据库目录]
cp -r [备份文件或文件夹] [对应的数据库数据目录下]
chown -R mysql:mysql /var/lib/mysql/目录名或文件
三、逻辑备份
1、逻辑备份的含义
备份时根据已有的数据生成对应的sql命令,把sql命令保存到备份文件。
2、备份操作
格式:mysqldump -u[管理员名] -p[密码] [数据库名] > 目录/备份文件名.sql
(1)数据库名的表示方式
--all-databases :一台数据库服务器上的所有数据
数据库名 :一个库的所有数据
库名 表名 :一张表的所有数据
-B 数据库1 数据库2 数据库n:一起备份多个库的数据
例:
` #mysqldump -uroot -p999 bbsdb_copy a > userdb-a.sql`
3、恢复操作
格式:mysql -u[管理用户] -p[密码] [库名] < 名.sql
注意:
恢复数据时,如果备份库中有创建库的命令时,可以不指定恢复库名。
备份单库时没有建库命令,所以需要指定恢复的库名。
备份多库时有建库命令,所以不需要指定恢复的库名。
例:
`#mysql -uroot -p999 bbsdb_copy < userdb-a.sql`
4、备份数据库的脚本和首期性计划任务
#crontab -e //加入周期性计划任务
00 2 * * 1 /root/allbakdb.sh &> /dev/null
将userdb库在每周一的凌晨2点进行备份。
vim /root/allbakdb.sh
#!/bin/bash
day=`date +%F`
if [ ! -e /bakdbdir ];then
mkdir /bakdbdir
fi
mysqldump -uroot -p999 userdb > /bakdbdir/${day}-userdb.sql
:wq
#chmod +x /root/allbakdb.sh
四、实时增量备份
启用mysql服务的binlog日志实现对数据的增量备份
1、binlog简介
类型
用途
配置
二进制日志
是mysql服务日志中的一种。记录客户端连接数据库服务器后,执行的除查询之外的sql命令。记录所有更改数据的操作。
在配置文件/etc/my.conf中配置log-bin=目录/mlg,默认在数据目录下产生的日志为mlg.00000n
查询binlog记录位置的命令 : select desc show
不是查询的sql命令如下:
create,use,insert into ,delete,drop
2、使用binlog日志实时备份
(1)采用binlog日志的好处
—掌握所有更改操作,必要时可用于数据恢复
—数据库主从复制的必要条件
(2)Binlog相关文件
日志文件编号范围为1~999999
Mysql-bin.index
—记录日志列表
Mysql-bin.000001
—第1份二进制日志,日志大于500M时会新建一个
Mysql-bin.000002
—第2份二进制日志
(3)生成新的binlog日志文件
mysql> flush logs;
#mysql -uroot -p999 -e "flush logs"
#mysqldump -uroot -p999 --flush-logs [库名] > xxx.sql
创建完全逻辑备份的同时,创建一个新的binlog日志文件,在备份之后的数据库操作(除查询外)会记录到日志文件中。当需要恢复数据库的文件时,先恢复备份的数据库文件,再执行日志文件中的sql命令,来最终恢复完整的数据库文件。
service mysql restart
(4)删除已有的binlog日志文件
mysql>purge master logs to "mysql.000003"; //删除plj.000003之前的所有日志
mysql>reset master; //删除当前所有的binlog日志文件,创建一个新的日志文件
rm -rf 文件 //这样删除后,日志名单还是在index索引文件中
(5)查看binlog日志文件内容
#tail mysql.000003`
3、Binlog恢复数据
(1)使用binlog恢复数据
格式:mysqlbinlog [选项] [binlog日志名] | mysql -uroot -p999 [数据库名]
选项:
字符偏移量
--start-position=pos值
--stop-position=pos值
sql命令执行时间
--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
当数据库异常,数据丢失时,可以考虑用binlog日志恢复数据。
例:
#mysqlbinlog --start-position=964 --stop-position=1144 plj.000002 | mysql -uroot -p999
(2)编写脚本
完全备份+增量备份
要求:每周一23点完全备份数据库userdb
每周的周二至周日 23:30 对userdb库做增量备份
每周一23点完全备份数据库,采用逻辑备份mysqldump
# vim /root/userdbbak.sh
#!/bin/bash
day=`date +%F`
[ ! -e /mybakdata/bak$day ]&& mkdir /mybakdata/bak$day
mysqldump -uroot -p123 userdb > /mybakdata/bak$day/userdb-$day.sql
每周的周二至周日 23:30 对userdb库做增量备份
# vim /root/userdbbaklog.sh
#!/bin/bash
day=`date +%F`
mysqldump -uroot -p123 --flush-logs userdb > /mybakdata/bak$day/userdblog-$day.sql
周期性任务
# crontab -e
0 23 * * 1 /root/userdbbak.sh
30 23 * * 2-7 /root/userdbbaklog.sh
五、innobackupex备份
1、常用的MySQL备份工具对比
(1)Myhotcopy的局限性
—仅适用于MyISAM引擎
—备份过程中,数据插入和更新造作会被挂起
(2)Mysqldump的不足
—效率较低,备份和还原速度慢
—备份过程中,数据插入和更新操作会被挂起
总结:
这两种方式备份,都会造成数据库正常的数据插入和更新操作被挂起,对正在运行的业务造成很大的影响。对于一些随时间段强度不同的业务,可以考虑在业务量小,不受影响的时间端进行备份,比如在夜间备份。但是对于zabbix这样的数据库来说,对于数据库的读写请求一直都是很强的,这种备份方式显然时不行的。这时要考虑在线热备份工具。
(3)XtraBackup介绍
—在线热备份工具,备份过程中不锁库表,
—只备份表记录,不备份表结构
—表的存储引擎必须是InnoDB/XtraDB
—必须先有一次完全备份,这样再执行备份时才知道那些数据是新产生。
—备份没有建库和表的命令,所以恢复的时候,需要有库和表
主要含两个组件:
—xtrabackup:C程序,支持InnoDB/XtraDB
—innobackupex:以Perl脚本封装xtrabackup,还支持MyISAM
3、安装XtraBackup
安装依赖包:
#yum -y install perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 libev-devel libev
# rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
4、innobackupex基本选项
innobackupex常用参数
--compact 创建一个不包含第二索引(除了主键之外的索引)的备份
--decompress 解压之前所有以–compress参数备份出来的带有.qp格式的备份文件,--parallel参数会允许同时解锁或解压多个文件。需要安装qpress软件。
--defaults-file=[MY.CNF] 配置文件的路径
--incremental-basedir 以上一次全量或增量备份的路径,作为增量备份的基础。指定这个参数的同时,应该同样指定--incremental参数
--incremental 创建增量备份,当指定这个参数的时候,应该指定--incremental-lsn或--incremental-basedir参数,否则将会备份到--incremental-basedir路径
--apply-log 在备份目录下,通过应用名称为xtrabackup_logfile的交易日志文件来准备备份。同时,创建新的交易日志。
--use-memory=# 指定数据库恢复时使用的内存大小,需要搭配--apply-log参数。
--redo-only 当准备数据库的全备或合并增量备份时,需要指定这个参数。这个参数实际上执行的是xtrabackup --apply-log-only,会让xtrabackup跳过回滚节点,只做“redo”步骤。当数据库需要应用增量备份时,需要指定这个参数。
--incremental-dir=DIRECTORY 指定增量备份的目录,需要搭配--incremental参数。
--no-timestamp 这个参数会让xtrabackup在备份的时候不创建带有时间格式的子文件夹。当指定了这个参数,备份会直接创建在指定的备份目录下。
--stream=STREAMNAME 指定流备份的格式。备份将会以指定格式输出到STDOUT。目前支持的格式有tar 和 xbstream。如果指定了这个参数,后面需要接tmpdir目录作为处理流的一个中间目录。
--slave-info 当备份一个作为复制环境的服务器时,这个参数会自动将CHANGE MASTER语句写到备份中,在恢复备份后,不必执行CHANGE MASTER语句。
--tables-file=FILE 这个参数会接受一个字符串,这个字符串指定了一个文件,这个文件包含了要备份的表名,格式如database.table,一行一个。
--use-memory=# 这个参数用于在准备备份时,xtrabackup执行crash recovery所使用的内存大小。这个参数仅和--apply-log搭配时才生效。
5、开启部分备份
部分备份需要开启innodb_file_per_table,在my.cnf配置文件中加上:
innodb_file_per_table=1
6、innobackupex备份
(1)开启mysql的binlog
# vim /etc/my.cnf
log-bin=/node/mysql/mysql-bin/mysql01
server-id = 01 //这里的id一定要设置,配置主从也是需要的
(2)创建由于备份的用户
# mysql -uroot -p123456
mysql> create user pxb@'localhost' identified by 'backmysql';
mysql> grant reload,process,lock tables,replication client on *.* to pxb@localhost;
创建备份的目录
# mkdir /node/balkmysql/
(3)生成备份
第一次完全备份:
# innobackupex --user=pxb --password=backmysql --socket=/tmp/mysql.sock /node/backmysql/
# cat /node/backmysql/2018-01-27_09-02-36/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 40673780
last_lsn = 40675586
compact = 0
recover_binlog_info = 0
--socket 指定mysql的socket文件位置
在备份目录下生成一个备份时间的目录,备份数据存在该目录下,可以用--no-timestamp取消自动设置时间目录,自定义备份目录名。虽然可以使用 --stream=tar 加 | gzip > 的方式直接打包并压缩,但是备份速度就下降了,所以我宁愿快速备份,之后再去处理打包、压缩。
第一次增量备份:
`# innobackupex --user=pxb --password=backmysql --socket=/tmp/mysql.sock --incremental /node/backmysql/inc --incremental-basedir=/node/backmysql/2018-01-27_09-02-36`
--incremental-basedir=/data/backup_db/test_incre 第一次增备指定全备路径
--incremental 参数说明这是一次增备,并指定增备路径
# cat /node/backmysql/inc/2018-01-27_09-03-44/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 40673780
to_lsn = 40688563
last_lsn = 40688975
compact = 0
recover_binlog_info = 0
第二次增量备份:
第二次增备是在第一次增备的基础上,所以 --incremental-basedir 指定的是第一次增备目录,最后指定的第二次增备目录。
# innobackupex --user=pxb --password=backmysql --socket=/tmp/mysql.sock --incremental /node/backmysql/inc --incremental-basedir=/node/backmysql/inc/2018-01-27_09-03-44
# cat /node/backmysql/inc/2018-01-27_09-10-21/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 40688563
to_lsn = 40757020
last_lsn = 40757039
compact = 0
recover_binlog_info = 0
7、备份恢复
(1)全备恢复
关闭数据库
# /etc/init.d/mysqld stop
Shutting down MySQL... SUCCESS!
将原来的数据目录移走
# mv /node/mysql/data data.old
# innobackupex --apply-log 2018-01-27_09-02-36
在备份上应用日志,一般没有看到报错且最后显示 OK 就没有问题 ( --use-memory 使用该参数加快速度 )
# innobackupex --copy-back 2018-01-27_09-02-36
将备份还原到 my.cnf 指定的 datadir 中,不指定 --defaults-file,默认 /etc/my.cnf
# chown -R mysql.mysql /node/mysql/data/
修改数据目录权限
(2)增备恢复
关闭数据库
# /etc/init.d/mysqld stop
Shutting down MySQL... SUCCESS!
将原来的数据目录移走
# mv /node/mysql/data data.old
进行增量备份
# innobackupex --apply-log --redo-only 2018-01-27_09-02-36
指定全备目录,--redo-only 对已提交的事务进行前滚,仅最后一次增备恢复不需要添加该参数
# innobackupex --apply-log --redo-only 2018-01-27_09-02-36 --incremental-dir=/node/backmysql/inc/2018-01-27_09-03-44
--incremental-dir 指定第一次增备目录 ( 绝对路径 )
# innobackupex --apply-log 2018-01-27_09-02-36 --incremental-dir=/node/backmysql/inc/2018-01-27_09-10-21
指定第二次增备目录,最后一次增备还原不需要添加 --redo-only 参数
# innobackupex --copy-back 2018-01-27_09-02-36
最后一步,复制整合后的全备文件,位置为 my.cnf 中 datadir 指定路径
# chown -R mysql.mysql /node/mysql/data/
修改数据目录权限
# /etc/init.d/mysqld start
启动数据库,查看数据是否恢复
# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| zabbix |
+--------------------+