innobackupex备份工具使用

逻辑备份

在MySQL中逻辑备份的最大优点是对各种存储引擎都可以用同样的方法来备份。而物理备份则不同,不同的存储引擎有着不同的备份方法。Mysql中的逻辑备份是将数据库中的数据备份为一个文本文件,备份的文件可以被查看和编辑,在mysql中常用mysqldump工具来完成逻辑备份。

mysqldump的使用方法:

查看测试数据库信息

mysql> show databases;
+——————–+
|Database|
+——————–+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+——————–+
6rowsinset(0.01 sec)

1.备份指定的数据库: suzhou

[root@anan tmp]# mysqldump -u root -p suzhou  >  ./dbbak/suzhou.sql–备份suzhou 数据库
Enterpassword:
[root@anan tmp]#
[root@anan tmp]# cd dbbak/ && ls
suzhou.sql–备份生成的文件

2.备份指定的某个库中的某个表:tb1

[root@anan dbbak]# mysqldump -u root -p suzhou tb1 > ./tb1.sql
Enterpassword:
[root@anan dbbak]# ls
suzhou.sql  tb1.sql

3.备份所有数据库

mysqldump -uroot -p–all-database > ./alldb.sql
Enterpassword:
[root@anan dbbak]# ls
alldb.sql  suzhou.sql  tb1.sql

需要强调的是,为了保证数据备份的一致性,MyISAM存储引擎在备份时需要加上 -l (lock table)参数,表示将所有表加上读锁。在备份期间,所有表将引擎只能读不能进行更新数据。但是对于事物存储引擎(InnoDB和BDB)来说,可以使用–single-transaction,该选项将使InnoDB引擎得到一个快照,使得备份的数据能够保证一致性。

完全恢复

mysqldump的恢复也比较简单,将备份的数据文件作为输入导入。但是注意,备份恢复后数据并不完整,因为备份后数据库中又会产生新的数据记录,需要将备份后的日志进行重写。使用方法:mysqlbinlog binlog-file | mysql -u -p

下面进行一次完整的备份与恢复
新建一个数据库 data1,库中有tb1,信息如下:

mysql>select*fromtb1;
+——+——+
| id   |name|
+——+——+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+——+——+
3rowsinset(0.00 sec)

备份data1数据库

mysql> show master status;
+——————-+———-+————–+——————+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000010 |     2935 |              |                  |
+——————-+———-+————–+——————+
1 rowinset(0.00 sec)

备份前先查看一下当前使用的bin-log日志文件编号

[root@anan dbbak]# mysqldump -uroot -p -l -F data1 > ./data1.sql
Enterpassword:
[root@anan dbbak]# ls
data1.sql

备份指令中使用的-l :锁定所有表为只读状态; -F : 刷新日志文件
再看bin-log文件信息:

mysql> show master status;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000011 | 107 | | |
+——————-+———-+————–+——————+

下面再对data1库中写入数据,写入后bin-log文件中的position的数值会产生变化。

mysql> insert into tb1 values (4,’D’),(5,’E’),(6,’F’);
Query OK, 3 rows affected (0.03 sec)
再看bin-log文件的position值
mysql> show master status;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000011 | 313 | | |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)

删除数据库data1,模拟故障

mysql>dropdatabasedata1;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+——————–+
|Database|
+——————–+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+——————–+
6rowsinset(0.00 sec)

data1 库已经被删除了

恢复数据库 data1

[root@anan dbbak]# mysql -uroot -p  data1 < ./data1.sql
Enterpassword:
mysqldump: Got error: 1049: Unknowndatabase’data1’whenselecting thedatabase

【恢复的数据库必须要在数据库中存在,恢复前先新建一个同名的数据库 data1】
【注意:mysqldump只用来备份数据,恢复数据使用 mysql指令,第一次恢复大意地使用mysqldump指令来恢复,怎么也没有成功,也没有报错,还是得细心才是】

[root@anan dbbak]# mysql -u root -p data1 < ./data1.sql        –恢复备份
Enter password:

查看恢复后的数据如下,后写入的数据还没有

mysql> select * from tb1;
+——+——+
| id | name |
+——+——+
| 1 | A |
| 2 | B |
| 3 | C |
+——+——+
3 rows in set (0.00 sec)

恢复备份后的数据,由于失误操作,日志文件中存在误操作的语句,所以可以可以使用基于时间点和基于位置的恢复。下面以位置恢复。
恢复前需要先看新写入的数据在bin-log日志文件中的position值: mysqlbinlog master-bin.000011

[root@anan mysqldata]# mysqlbinlog master-bin.000011 | cat -n | grep -C 5’insert’
1/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
2/!40019 SET @@session.max_insert_delayed_threads=0/;
3/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
4 DELIMITER/!/;
5 #at4
6 #180308 2:28:18 server id 2 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 180308 2:28:18
7 # Warning: this binlogiseitherinuseorwasnotclosed properly.

24/!/;
25 #at176
26 #180308 2:33:31 server id 2 end_log_pos 286 Query thread_id=13 exec_time=0 error_code=0
27 use data1/!/;
28SETTIMESTAMP=1520447611/!/;
29insertintotb1values(4,’D’),(5,’E’),(6,’F’)
30/!/;
31 #at286
32 #180308 2:33:31 server id 2 end_log_pos 313 Xid = 611
33COMMIT/!/;
34 #at313

213BEGIN
214/!/;
215 #at3393
216 #180308 2:33:31 server id 2 end_log_pos 3503 Query thread_id=13 exec_time=4490 error_code=0
217SETTIMESTAMP=1520447611/!/;
218insertintotb1values(4,’D’),(5,’E’),(6,’F’)
219/!/;
220 #at3503
221 #180308 2:33:31 server id 2 end_log_pos 3530 Xid = 916
222COMMIT/!/;
223 DELIMITER ;使用日志文件恢复后写入的数据 ;使用mysqlbinlog 指令
[root@anan mysqldata]# mysqlbinlog –start-position=’176′ –stop-position=’286′ ./master-bin.000011 | mysql -u root -p
Enter password:

再看数据库信息

mysql> select * from tb1;
+——+——+
| id | name |
+——+——+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+——+——+
6 rows in set (0.01 sec)

物理备份和恢复

物理备份又分为冷备和热备两种,和逻辑备份相比,它最大的优点是备份和恢复的速度更快,因为物理备份的原来是基于文件的cp。

冷备份
冷备份其实就是停掉数据库服务,cp数据文件的方法,这种方法对MyISAM和InnoDB引擎都适合,但是一般很少用,因为很多应用是不允许停机的。

热备份
在MySQL中,对于不同的存储引擎热备的方法也有所不同,下面主要介绍MyISAM和InnoDB两种常用的存储引擎的热备方法。

MyISAM存储引擎
MyISAM存储引擎的热备方法本质其实就是将要备份的表加读锁,然后在cp数据文件到备份目录,常用的方法有以下两种:
方法1:使用mysqlhotcopy工具
方法2:使用手动锁表copy
首先将数据库中的所有表加读锁: flush tables for read ; 然后再cp数据文件到备份目录即可。

InnoDB存储引擎
InnoDB存储引擎数据库可以使用Xtrabackup工具来备份
下面仔细介绍xtarbackup热备工具的使用:

xtarbackup热备工具的使用
xtrabackup是percona公司参与开发的一款基于InnoDB的在线热备工具,具有开源,免费,支持在线热备,备份恢复速度快,占用磁盘空间小等特点。
xtrabackup包含两个主要的工具。即xtrabackup和innobackupex,两者区别如下:
xtrabackup:只能备份InnoDB存储引擎和XtraDB两种数据表,而不能备份MyISAM数据表。
Innobackupex:是一个封装了xtrabackup的perl脚本,支持同时备份InnoDB和MyISAM,但在对MyISAM备份时需要加一个全局的读锁
备份前查看数据库的存储引擎是什么:

mysql> show variableslike’%engine’;
+————————+——–+
| Variable_name          | Value  |
+————————+——–+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+————————+——–+
2rowsinset(0.00 sec)

innobackupex的参数比较多,详细的参数可以参考学习下面一位博友的文章,写的很详细
https://www.cnblogs.com/zhoujinyi/p/5893333.html

进行全备

[root@anan dbbak]# innobackupex–defaults-file=/etc/my.cnf –user=root –password=fsz… ./
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
andPercona Ireland Ltd 2009-2012.AllRights Reserved.
This softwareispublished under
the GNU GENERALPUBLICLICENSE Version 2, June 1991.
180308 06:11:41 innobackupex: ConnectingtoMySQL serverwithDSN’dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup’as’root'(usingpassword: YES).
180308 06:11:41 innobackupex: ConnectedtoMySQL server
IMPORTANT: Pleasecheckthat the backup run completes successfully.
Attheendofa successful backup run innobackupex
prints”completed OK!”.
innobackupex: Using mysql server version 5.5.32-log
innobackupex: Created backup directory /tmp/dbbak/2018-03-08_06-11-41
180308 06:11:41 innobackupex: Starting ibbackupwithcommand: xtrabackup_55–defaults-file=”/etc/my.cnf” –defaults-group=”mysqld” –backup –suspend-at-end –target-dir=/tmp/dbbak/2018-03-08_06-11-41 –tmpdir=/tmp
innobackupex: Waitingforibbackup (pid=3736)tosuspend
innobackupex: Suspend file’/tmp/dbbak/2018-03-08_06-11-41/xtrabackup_suspended_2′
xtrabackup_55 version 2.1.2forPercona Server 5.5.16 Linux (x86_64) (revision id: 611)
xtrabackup: uses posix_fadvise().
xtrabackup: cdto/usr/local/mysqldata
xtrabackup: Target instanceisassumedasfollowings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880

log scanned upto(1645580)
[01] Copying ./ibdata1to/tmp/dbbak/2018-03-08_06-11-41/ibdata1
[01] …done
log scanned upto(1645580)
180308 06:11:43 innobackupex: Continuingafteribbackup has suspended
180308 06:11:43 innobackupex: Startingtolockalltables…
180308 06:11:43 innobackupex:Alltables lockedandflushedtodisk
180308 06:11:43 innobackupex: Startingtobackup non-InnoDB tablesandfiles
innobackupex:insubdirectoriesof’/usr/local/mysqldata’
innobackupex: Backing up files’/usr/local/mysqldata/performance_schema/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(18 files)
innobackupex: Backing up file’/usr/local/mysqldata/data1/db.opt’
innobackupex: Backing up file’/usr/local/mysqldata/data1/tb1.frm’
innobackupex: Backing up file’/usr/local/mysqldata/sysdb/db.opt’
innobackupex: Backing up file’/usr/local/mysqldata/sysdb/tb1.frm’
innobackupex: Backing up files’/usr/local/mysqldata/mysql/
.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(72 files)
log scanned upto(1645580)
innobackupex: Backing up file’/usr/local/mysqldata/suzhou/db.opt’
innobackupex: Backing up file’/usr/local/mysqldata/suzhou/tb1.frm’
innobackupex: Backing up file’/usr/local/mysqldata/suzhou/fengsuzhou.frm’
180308 06:11:44 innobackupex: Finished backing up non-InnoDB tablesandfiles
180308 06:11:44 innobackupex: Waitingforlog copyingtofinish
xtrabackup: The latestcheckpoint (forincremental):’1645580′
xtrabackup: Stopping log copying thread.
.>> log scanned upto(1645580)
xtrabackup:Transactionlogoflsn (1645580)to(1645580) was copied.
180308 06:11:45 innobackupex:Alltables unlocked
innobackupex: Backup createdindirectory’/tmp/dbbak/2018-03-08_06-11-41′
innobackupex: MySQL binlog position: filename’master-bin.000011′, position 3736
180308 06:11:45 innobackupex:Connectiontodatabaseserver closed
180308 06:11:45 innobackupex: completed OK!

完整的备份过程信息可以细细查看备份后在备份目录会生成一个时间戳目录,备份的数据文件就放在该目录中

热备恢复

恢复前可以测试一下把原来的数据目录清空然后恢复看看效果

1.先关闭服务

service mysqld stop
2.重命名数据目录并创建原同名目录
mv mysqldata mysqldatabak
mkdir mysqldata3.还原后再重新修改新的数据目录的权限chown -R mysql:mysql mysqldata/*4.启动服务service mysqld start

还原数据

提交或者回滚事务

[root@anan dbbak]# innobackupex–apply-log ./2018-03-08_06-11-41/

#上面这指令是利用–apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

恢复数据

[root@anan dbbak]# innobackupex–defaults-file=/etc/my.cnf –copy-back ./2018-03-08_06-11-41/

还可以使用 【–database=“db1 db2 db3”】选项备份指定的数据库,多个数据库用空格隔开还可以使用 【–databases=”db1.tb1 db2.tb2 db3.tb3″】方式备份不同库的不同表,同样多库用空格隔开
最后检查数据和查看数据目录

mysql>select*fromtb1;
+——+——+
| id   |name|
+——+——+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+——+——+
9rowsinset(0.01 sec)

创建增量备

在进行增量部分前先进行一次基础备份

innobackupex–user=root –password=fsz… –defaults-file=/etc/my.cnf /tmp/dbbak/

基础备份完成后,再对数据库进行一些数据操作,这里添加一个新表

mysql>createtabletb2 (
-> idint(10),
->namevarchar(10)
-> );
Query OK, 0rowsaffected (0.07 sec)
mysql>insertintotb2values(1,’A’);
Query OK, 1 row affected (0.04 sec)
接着进行第一次增量备份

第一次增量备份\

innobackupex–user=root –password=fsz… –defaults-file=/etc/my.cnf  –incremental /tmp/dbbak/increment_one/  
–incremental-basedir=/tmp/dbbak/2018-03-08_10-07-07/

第一次增量备份后,再接着写两条数据进去,然后进行第二次增量备份

mysql>insertintotb2values(2,’B’),(3,’C’);
Query OK, 2rowsaffected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql>SELECT*FROMtb2;
+——+——+
| id   |name|
+——+——+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+——+——+
3rowsinset(0.00 sec)

最终有三条数据

第二次增量备份

innobackupex–user=root –password=fsz… –defaults-file=/etc/my.cnf –incremental /tmp/dbbak/increment_two/ –incremental-basedir=/tmp/dbbak/increment_one/2018-03-08_10-12-39/

两次增量备份后备份目录生成备份文件如下

[root@anan dbbak]# tree -d ./
.
├── 2018-03-08_10-07-07–基础备份
│   ├── data1
│   ├── mysql
│   ├── performance_schema
│   ├── suzhou
│   ├── sysdb
│   └── test
├── increment_one
│   └── 2018-03-08_10-12-39–第一次增量备份
│       ├── data1
│       ├── mysql
│       ├── performance_schema
│       ├── suzhou
│       ├── sysdb
│       └── test
└── increment_two
└── 2018-03-08_10-13-43–第二次增量备份
├── data1
├── mysql
├── performance_schema
├── suzhou
├── sysdb
└── test

增量备份恢复过程大致如下:
1. 恢复基础备份(全备)
2. 恢复增量备份到基础备份(开始恢复的增量备份需要增加 –redo-only 参数,到恢复最后一次增量备份要去掉 –redo-only 参数)
3. 对整体的基础备份进行恢复,回滚那些未提交的数据。

恢复基础备份数据(注意:这里一定要加–redo-only 参数该参数的意思是只应用xtrabackup日志中已经提交的事务数据,不回滚还未提交的数据)\

innobackupex–apply-log –redo-only /tmp/dbbak/2018-03-08_10-07-07/

将第一次增量备份increment_one应用到基础备份 2018-03-08_10-07-07 中

innobackupex–apply-log –redo-only /tmp/dbbak/2018-03-08_10-07-07/ –incremental-dir=/tmp/dbbak/increment_one/2018-03-08_10-12-39/

将第二次增量备份 increment_two 应用到 基础备份 2018-03-08_10-07-07,(注意:恢复最后一次增量备份需要去掉–redo-only 参数,回滚xtrabackup日志中那些未提交的数据)

innobackupex –apply-log /tmp/dbbak/2018-03-08_10-07-07/ –incremental-dir=/tmp/dbbak/increment_two/2018-03-08_10-13-43/

把所有合在一起的基础备份进行一次 apply 操作,回滚未提交的数据;

利用–apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

innobackupex–apply-log /tmp/dbbak/2018-03-08_10-07-07

把恢复完的备份文件复制到数据文件目录中,赋权,然后重启服务大致步骤如下:

  1. 关闭服务
    service mysqld stop
  2. 备份原数据目录
    mv mysqldata mysqldata.old
  3. 创建新的同名数据目录
    mkdir mysqldata
  4. 复制备份数据文件
    innobackupex–defaults-file=/etc/my.cnf –copy-back /tmp/dbbak/2018-03-08_10-07-07/
  5. 对新的数据目录赋权
    chown -R mysql:mysql ./mysqldata
  6. 重启服务
    service mysqld start
    最后查看恢复后的数据
Databasechanged
mysql> show tables;
+—————–+
| Tables_in_data1 |
+—————–+
| tb1             |
| tb2             |
+—————–+
2rowsinset(0.00 sec)
mysql>select*fromtb2;
+——+——+
| id   |name|
+——+——+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+——+——+
3rowsinset(0.03 sec)
点赞