innobackupex 备份文件中 没有mysql库_Mysql数据库的备份和恢复—XtraBackup工具中的innobackupex备份恢复方法...

在生产环境中，对数据库进行备份是十分重要的，数据库运行异常或者是运维人员误操作，造成数据丢失，可以将备份进行快速恢复，最大程度地降低损失。当然，数据库地备份也有多种方法，例如简单地冷备和稍微复杂点的逻辑备份，都可以借助工具轻松实现。根据不同的业务场景，选择合适的备份策略，已备后患。这里主要介绍了一些针对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 |

+--------------------+