Mysql数据库备份—-Percona XtraBackup实现单表单库备份

layout: post
title: Mysql数据库备份—-Percona XtraBackup实现单表单库备份
date: 2018-02-27
tags: [“Mariadb”,”软件服务”]

一、Percona XtraBackup实现全量和增量备份

二、实验简单步骤

1. 单表备份
2. 删除表
3. 恢复表

三、实验步骤

1、备份单表

[root@localhost ~]# innobackupex --include=test.test /backup/test_table
180227 12:13:53 innobackupex: Starting the backup operation
 
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
.
.
.
.
180227 12:13:55 Backup created in directory '/backup/test_table/2018-02-27_12-13-53/'
MySQL binlog position: filename 'localhost-bin.000001', position '332', GTID of the last change ''
180227 12:13:55 [00] Writing /backup/test_table/2018-02-27_12-13-53/backup-my.cnf
180227 12:13:55 [00]        ...done
180227 12:13:55 [00] Writing /backup/test_table/2018-02-27_12-13-53/xtrabackup_info
180227 12:13:55 [00]        ...done
xtrabackup: Transaction log of lsn (1622559) to (1622568) was copied.
180227 12:13:55 completed OK!
 
[root@localhost 2018-02-27_12-13-53]# cd /backup/test_table/2018-02-27_12-13-53/
[root@localhost 2018-02-27_12-13-53]# ll
总用量 12308
-rw-r----- 1 root root      424 2月  27 12:13 backup-my.cnf
-rw-r----- 1 root root 12582912 2月  27 12:13 ibdata1
drwxr-x--- 2 root root       38 2月  27 12:13 test
-rw-r----- 1 root root       26 2月  27 12:13 xtrabackup_binlog_info
-rw-r----- 1 root root      113 2月  27 12:13 xtrabackup_checkpoints
-rw-r----- 1 root root      508 2月  27 12:13 xtrabackup_info
-rw-r----- 1 root root     2560 2月  27 12:13 xtrabackup_logfile

2、删除test表

[root@localhost 2018-02-27_12-13-53]# mysql -e 'drop tables test.test;'

3、恢复test表

首先需要创建表结构

MariaDB [(none)]> use test;
MariaDB [test]> CREATE TABLE IF NOT EXISTS `test`(
    -> `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    -> `runoob_title` VARCHAR(100) NOT NULL,
    -> `runoob_author` VARCHAR(40) NOT NULL,
    -> `submission_date` DATE,
    -> PRIMARY KEY ( `runoob_id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

复制表数据到数据库中

[root@localhost test]# innobackupex --apply-log --export /backup/test_table/2018-02-27_12-13-53/ #整理表数据
[root@localhost test]# cd /backup/test_table/2018-02-27_12-13-53/test
[root@localhost test]# cp ./* /var/lib/mysql/test/
[root@localhost test]# chown mysql.mysql /var/lib/mysql/test/*
 

注意权限问题！

登录mysql数据库，恢复test表

[root@localhost test]# mysql
MariaDB [(none)]> use test;
MariaDB [test]> alter table test discard tablespace;
MariaDB [test]> alter table test import tablespace;

注意： XtraBackup实现单表单库备份，需要基于mariadb10即以上版本才可以实现单表的备份，其他版本可以通过mysqldump来实现数据的备份。