MySQL备份与恢复常用方法总结（mysqldump/xtrabackup/lvm快照备份/二进制日志及时点恢复）

MySQL备份与恢复常用方法总结

（mysqldump/xtrabackup/lvm快照备份/逻辑备份与恢复/二进制日志及时点恢复）

自言：学习在于总结，把所了解的类似东西放到一起更能加深记忆

一、了解备份相关知识

1）按服务器备份时状态可分为：
     热备份：读、写不受影响；
     温备份：仅可以执行读操作；
     冷备份：离线备份；读、写操作均中止；
2）按服务器备份数据集可分为：
     物理备份：复制数据文件；
     逻辑备份：将数据导出至文本文件中；
3）按服务器备份数据量可分为：
    完全备份：备份全部数据；
    增量备份：仅备份上次完全备份或增量备份以后变化的数据；
    差异备份：仅备份上次完全备份以来变化的数据；
4）我们需要备份些什么数据文件？
   数据、配置文件、二进制文件、事务日志

二、学习相关备份工具

1）mysqldump：逻辑备份工具

2）lvm-snapshot：温备（首先需要先全局锁表，而后创建LVM快照，然后释放锁。）

3）xtrabackup：完全备份、增量备份以及高级部分备份

4）逻辑备份

三、mysqldump备份与恢复实验

    1）准备条件：查看当前数据库状态

mysql> show databases; //查看己有的数据库；
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables; //查看当前数据库mydb中有哪些表；
+----------------+
| Tables_in_mydb |
+----------------+
| tb1            |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tb1;   //查看tb1数据；
+----+-----------+--------+------+
| id | name      | gender | age  |
+----+-----------+--------+------+
|  1 | jun.wang  | m      |   25 |
|  2 | jerry.liu | m      |   27 |
|  3 | windy.ma  | m      |   30 |
|  4 | lilian.wu | f      |   27 |
+----+-----------+--------+------+
4 rows in set (0.00 sec)

mysql> show binary logs; //如果此时你的系统中有多个二进制日志文件，可以用下面一条命令将其手动删除；
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000005 |       120 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql> purge binary logs to ‘master-bin.000005‘; //删除之前的所有二进制日志；

mysql> show master status;  //查看当前正在使用的二进制日志；
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000006 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    2）全量数据备份与增量备份

# mysqldump --lock-all-tables --master-data=2 --all-databases --flush-logs >mydb_all_backup_`date +%F`.sql
    mysqldump常用选项：
        --master-data={0|1|2}
          0：不记录二进制日志文件及路径位置
          1：以change master to 的方式记录位置，可用于恢复后直接启动从服务器
          2：以change master to 的方式记录位置，但默认为被注释
        --lock-all-tables: 锁定所有表
        --flush-logs: 执行日志flush
             如果指定库中的表类型为Innodb则可以使用--single-transaction启动热备；
        --all-databases :备份所有库
        --databases DB_NAME1,DB_NAME2,....: 备份多个库
        --events :事务
        --routines :存储过程，存储函数
        --triggers :触发器

mysql> insert into tb1 (name,gender,age) values(‘tom.chen‘,‘m‘,47);
Query OK, 1 row affected (0.08 sec)

mysql> insert into tb1 (name,gender,age) values(‘tina.li‘,‘f‘,35);
Query OK, 1 row affected (0.03 sec)

# cp /mydata/data/master-bin.000006 /root/mydb_backup/

1.模拟数据全部删除：
# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
# rm -fr /mydata/data/*

2.重新初始化
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

3.my.cnf配置文件
datadir = /mydata/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
innodb_file_per_table = 1
query_cache_type = 1
log-bin = mysql-bin

4.启动mysql服务
# service mysqld start
Starting MySQL..                                           [  OK  ]

5.恢复操作
    1）全量恢复操作
# mysql </root/mydb_backup/mydb_all_backup_2014-07-12.sql
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mydb;
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1            |
+----------------+
1 row in set (0.00 sec)
mysql> select * from tb1;
+----+-----------+--------+------+
| id | name      | gender | age  |
+----+-----------+--------+------+
|  1 | jun.wang  | m      |   25 |
|  2 | jerry.liu | m      |   27 |
|  3 | windy.ma  | m      |   30 |
|  4 | lilian.wu | f      |   27 |
+----+-----------+--------+------+
4 rows in set (0.00 sec)

    2）增量恢复操作
# mysqlbinlog master-bin.000006 |mysql
mysql> select * from mydb.tb1;
+----+-----------+--------+------+
| id | name      | gender | age  |
+----+-----------+--------+------+
|  1 | jun.wang  | m      |   25 |
|  2 | jerry.liu | m      |   27 |
|  3 | windy.ma  | m      |   30 |
|  4 | lilian.wu | f      |   27 |
|  5 | tom.chen  | m      |   47 |
|  6 | tina.li   | f      |   35 |
+----+-----------+--------+------+
6 rows in set (0.01 sec)

当然这里操作完成我们需要将二进制日志滚动一下，以备以后方便日志恢复；
mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    559316 |
| mysql-bin.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> purge binary logs to ‘mysql-bin.000002‘;
Query OK, 0 rows affected (0.06 sec)

当然对于事务型数据库及表，我们还需要备份事务日志等。

四、lvm-snapshot备份与恢复

由于系统没有可用的lvm分区，所以这里需要加装块磁盘以及创建lvm,就当是回顾一下lvm的使用吧... ...

# service mysqld stop
# fdisk /dev/sdb
Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +5G
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): w
Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (655-2610, default 655):  
Using default value 655
Last cylinder, +cylinders or +size{K,M,G} (655-2610, default 2610): +1G
Command (m for help): t
Partition number (1-4): 2
Hex code (type L to list codes): 8e
Changed system type of partition 2 to 8e (Linux LVM)
Command (m for help): w
# partprobe /dev/sdb
# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created
# pvcreate /dev/sdb2
  Physical volume "/dev/sdb2" successfully created
# vgcreate mydata /dev/sdb1
  Volume group "mydata" successfully created
# vgextend mydata /dev/sdb2
  Volume group "mydata" successfully extended
# lvcreate -L 5G -n mydata mydata
  Logical volume "mydata" created
# lvs
  LV      VG        Attr      LSize  Pool Origin Data%  Move Log Cpy%Sync Convert
  mydata  mydata    -wi-a----  5.00g                                             
  lv_root vg_server -wi-ao--- 15.60g                                             
  lv_swap vg_server -wi-ao---  3.91g
# mkfs.ext4 /dev/mapper/mydata-mydata
# mount /dev/mapper/mydata-mydata /mydata/data/
# vim /etc/fstab   //新加入下面一行；
/dev/mapper/mydata-mydata /mydata/data          ext4    defaults        0 0

初始化数据库
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
# vim my.cnf
datadir = /mydata/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
innodb_file_per_table = 1
query_cache_type = 1
log-bin = mysql-bin
# service mysqld start
为了节省保贵的时间，还是使用上面mysqldump完全备份下来的数据：
mysql> source /root/mydb_backup/mydb_all_backup_2014-07-12.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from mydb.tb1;
+----+-----------+--------+------+
| id | name      | gender | age  |
+----+-----------+--------+------+
|  1 | jun.wang  | m      |   25 |
|  2 | jerry.liu | m      |   27 |
|  3 | windy.ma  | m      |   30 |
|  4 | lilian.wu | f      |   27 |
|  5 | tom.chen  | m      |   47 |
|  6 | tina.li   | f      |   35 |
+----+-----------+--------+------+
6 rows in set (0.00 sec)

mysql> flush tables with read lock;  //此时，其它会话都将无法修改数据；
Query OK, 0 rows affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

# mysql -uroot -p -e ‘SHOW MASTER STATUS\G‘ >/root/mydb_backup/master.info //通过另外终端备份当前二进制日志状态；
# lvcreate -L 50M -s -n mysql_snapshot /dev/mapper/mydata-mydata 
  Rounding up size to full physical extent 52.00 MiB
  Logical volume "mysql_snapshot" created

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

# lvs
  LV             VG        Attr      LSize  Pool Origin Data%  Move Log Cpy%Sync Convert
  mydata         mydata    owi-aos--  5.00g                                             
  mysql_snapshot mydata    swi-a-s-- 52.00m      mydata   0.02                          
  lv_root        vg_server -wi-ao--- 15.60g                                             
  lv_swap        vg_server -wi-ao---  3.91g                                             
# mkdir snapshot
# mount /dev/mapper/mydata-mysql_snapshot /root/snapshot/
# cd snapshot/
# ls
auto.cnf  ib_logfile0  mydb   mysql-bin.000001  mysql-bin.000003  mysql-bin.index     server102.neo.com.pid
ibdata1   ib_logfile1  mysql  mysql-bin.000002  mysql-bin.000004  performance_schema  test

# mkdir /root/mydb_backup/data
# cp -fr * /root/mydb_backup/data/

# umount /dev/mapper/mydata-mysql_snapshot
# lvremove /dev/mapper/mydata-mysql_snapshot
Do you really want to remove active logical volume mysql_snapshot? [y/n]: y
  Logical volume "mysql_snapshot" successfully removed

模拟数据全部删除
# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
# rm -fr /mydata/data/*
# cp -fr /root/mydb_backup/data/* /mydata/data/
# chown -R mysql.mysql /mydata/data
# service mysqld start
Starting MySQL                                             [  OK  ]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.05 sec)
mysql> select * from mydb.tb1;
+----+-----------+--------+------+
| id | name      | gender | age  |
+----+-----------+--------+------+
|  1 | jun.wang  | m      |   25 |
|  2 | jerry.liu | m      |   27 |
|  3 | windy.ma  | m      |   30 |
|  4 | lilian.wu | f      |   27 |
|  5 | tom.chen  | m      |   47 |
|  6 | tina.li   | f      |   35 |
+----+-----------+--------+------+
6 rows in set (0.06 sec)

注：由于此前没有对数据库进行任何修改数据操作，也没对其进行增量备份，如果你的真实环境做过增量备份，按照上面mysqldump上面恢复二进制方法进行就行了。

五、XtraBackup备份与恢复

1、XtraBackup介绍

    （摘自官网http://www.percona.com/software/percona-xtrabackup）  

    Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:

• Backups that complete quickly and reliably

• Uninterrupted transaction processing during backups

• Savings on disk space and network bandwidth

• Automatic backup verification

• Higher uptime due to faster restore time

首先安装完xtrabackup后，我们还需要了解下面两个工具：

1)xtrabackup 只能备份InnoDB和XtraDB两种数据表，支持在线热备份，可以在不加锁的情况下备份Innodb数据表，不过此工具不能操作Myisam引擎表

2)innobackupex  是一个脚本封装，封装了xtrabackup,能同时处理Innodb和Myisam，但在处理Myisam时需要加一个读锁。

按如上的介绍，由于操作Myisam时需要加读锁，这会堵塞线上服务的写操作，而Innodb没有这样的限制，所以数据库中Innodb表类型所占的比例越大，则越有利。实际应用中一般是直接使用innobackupex方法。

2、安装percona xtrabackup软件包

# yum localinstall percona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm -y

3、完全备份

1）准备：

    1> mysql的配置文件存放路径改为/etc/my.cnf    //* 由于innobackupex只识别/etc/my.cnf配置文件 *//

    2> socket = /var/lib/mysql/mysql.sock      //* 也是因为innobackupex只识别此路径下的sock文件 *//

    3> ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock    //* 这里我也不知如何解释，当配置文件中指定为/var/lib/mysql/mysql.sock时，mysql会去找/tmp/mysql.sock，不知是哪里出了问题，所以暂时就这么解决吧； *//

    4> 创建个数据库与表；

    mysql> select * from mydb.tb1;
    +----+-----------+--------+------+
    | id | name      | gender | age  |
    +----+-----------+--------+------+
    |  1 | jun.wang  | m      |   25 |
    |  2 | jerry.liu | m      |   28 |
    |  3 | windy.ma  | m      |   30 |
    |  4 | lilia.wu  | f      |   27 |
    +----+-----------+--------+------+
    4 rows in set (0.00 sec)

    5> 创建备份目录；

    # mkdir /root/backup/{full_backup,increment} -p

2）备份

# innobackupex --user=root /root/backup/full_backup/ 
//*    /root/backup/full_backup:    指定完全备份路径；*//
//*    --user=root:    为安全起见，你也可以创建一个其它权限用户进行备份；*//
... ...    //省略；
innobackupex: Backup created in directory ‘/root/backup/full_backup/2014-07-12_12-00-25‘
innobackupex: MySQL binlog position: filename ‘mysql-bin.000001‘, position 120
140712 12:00:28  innobackupex: Connection to database server closed
140712 12:00:28  innobackupex: completed OK!

在备份的同时，innobackupex还会在备份目录中创建如下文件：
(1)xtrabackup_checkpoints       备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info       mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
(4)xtrabackup_binary            备份中用到的xtrabackup的可执行文件；
(5)backup-my.cnf                备份命令用到的配置选项信息；

3）完全备份恢复

    1> 恢复准备

    在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。而下面命令行中的"--apply-log"就是用于实现该功能的；

# innobackupex --apply-log /root/backup/full_backup/2014-07-12_12-00-25/
... ...    //省略；
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1638422
140712 12:25:32  innobackupex: completed OK!

    2> 恢复

    # rm -fr /mydata/data/*
    # innobackupex --copy-back /root/backup/full_backup/2014-07-12_12-00-25/
    ... ...    //省略；
    innobackupex: Finished copying back files.

    140712 12:30:11  innobackupex: completed OK!
    # chown -R mysql.mysql /mydata/data/*
    mysql> select * from mydb.tb1;    //直接使用，不需要重启mysql服务器；
    +----+-----------+--------+------+
    | id | name      | gender | age  |
    +----+-----------+--------+------+
    |  1 | jun.wang  | m      |   25 |
    |  2 | jerry.liu | m      |   28 |
    |  3 | windy.ma  | m      |   30 |
    |  4 | lilia.wu  | f      |   27 |
    +----+-----------+--------+------+
    4 rows in set (0.00 sec)

4、增量备份

    这里我们做两次增量备份，以便更接近生产环境；

1）第一次增量备份：

mysql> insert into tb1 (name,gender,age) values(‘cherry.dai‘,‘f‘,27),(‘tom.chen‘,‘m‘,35);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
# innobackupex --incremental /root/backup/increment/ --incremental-basedir=/root/backup/full_backup/2014-07-12_13-56-38/
//*    --incremental:    增量备份存放路径，innobackupex会自动在此目录下创建一个新的目录； *//
//*    --incremental-basedir:    对于第一次增量备份，指定完全备份路径即可；对于第一次以后的增量备份需要指定前一次增量备份的存放路径； *//

... ...    //省略；
innobackupex: Backup created in directory ‘/root/backup/increment/2014-07-12_14-02-51‘
innobackupex: MySQL binlog position: filename ‘mysql-bin.000001‘, position 417
140712 14:02:55  innobackupex: Connection to database server closed
140712 14:02:55  innobackupex: completed OK!

2）第二次增量备份：

mysql> create table tb2 (id int auto_increment not null primary key, name char(30) not null);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into tb2 (name) values (‘user1‘),(‘user2‘),(‘user3‘),(‘user4‘);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
# innobackupex --incremental /root/backup/increment/ --incremental-basedir=/root/backup/increment/2014-07-12_14-02-51/
//*    --incremental-basedir:    这里的路径为第一次增量备份的路径； *//

... ...     //省略；
innobackupex: Backup created in directory ‘/root/backup/increment/2014-07-12_14-04-28‘
innobackupex: MySQL binlog position: filename ‘mysql-bin.000001‘, position 861
140712 14:04:31  innobackupex: Connection to database server closed
140712 14:04:31  innobackupex: completed OK!

3）增量备份恢复：

    1）模拟数据损坏：

# service mysqld stop
Shutting down MySQL....                                    [  OK  ]
# rm -fr /mydata/data/*

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

“准备"增量备份与整理完全备份有着一些不同，尤其要注意的是：
(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
(2)基于所有的备份将未提交的事务进行“回滚”。

2）还原准备：
# innobackupex --apply-log --redo-only /root/backup/full_backup/2014-07-12_13-56-38/
//*    --apply-log:    前面己提到；*//
//*    --redo-only:    对于多次增量备份仅提交；*//
//*    /root/backup/full_backup/2014-07-12_13-56-38/:    完全备份目录；*//

... ...    //*    省略；*//
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1638934
140712 14:16:46  innobackupex: completed OK!

3）第一次备份与完全备份合并：
# innobackupex --apply-log --redo-only /root/backup/full_backup/2014-07-12_13-56-38/ --incremental-dir=/root/backup/increment/2014-07-12_14-02-51/
//*    /root/backup/full_backup/2014-07-12_13-56-38/:    准备好后的完全备份目录；*//
//*    /root/backup/increment/2014-07-12_14-02-51/:    第一次增量备份目录；*//

... ...    //*    省略；*//
innobackupex: Copying ‘/root/backup/increment/2014-07-12_14-02-51/mydb/tb1.frm‘ to ‘/root/backup/full_backup/2014-07-12_13-56-38/mydb/tb1.frm‘
innobackupex: Copying ‘/root/backup/increment/2014-07-12_14-02-51/mydb/db.opt‘ to ‘/root/backup/full_backup/2014-07-12_13-56-38/mydb/db.opt‘
140712 14:18:44  innobackupex: completed OK!

4）第二次备份与完全备份合并：
# innobackupex --apply-log --redo-only /root/backup/full_backup/2014-07-12_13-56-38/ --incremental-dir=/root/backup/increment/2014-07-12_14-04-28/
//*    /root/backup/full_backup/2014-07-12_13-56-38/:    准备好后的完全备份目录；*//
//*    /root/backup/increment/2014-07-12_14-04-28/    第二次增量备份目录；*//
... ...    //*    省略； *//
innobackupex: Copying ‘/root/backup/increment/2014-07-12_14-04-28/mydb/tb2.frm‘ to ‘/root/backup/full_backup/2014-07-12_13-56-38/mydb/tb2.frm‘
innobackupex: Copying ‘/root/backup/increment/2014-07-12_14-04-28/mydb/tb1.frm‘ to ‘/root/backup/full_backup/2014-07-12_13-56-38/mydb/tb1.frm‘
innobackupex: Copying ‘/root/backup/increment/2014-07-12_14-04-28/mydb/db.opt‘ to ‘/root/backup/full_backup/2014-07-12_13-56-38/mydb/db.opt‘
140712 14:19:38  innobackupex: completed OK!

5）将多次合并后的数据恢复：
# innobackupex --copy-back /root/backup/full_backup/2014-07-12_13-56-38/
... ...    //*    省略；*//
innobackupex: Starting to copy InnoDB log files
innobackupex: in ‘/root/backup/full_backup/2014-07-12_13-56-38‘
innobackupex: back to original InnoDB log directory ‘/mydata/data‘
innobackupex: Copying ‘/root/backup/full_backup/2014-07-12_13-56-38/ib_logfile1‘ to ‘/mydata/data/ib_logfile1‘
innobackupex: Copying ‘/root/backup/full_backup/2014-07-12_13-56-38/ib_logfile0‘ to ‘/mydata/data/ib_logfile0‘
innobackupex: Finished copying back files.
140712 14:24:04  innobackupex: completed OK!

6）启动mysql服务，检查数据：
# chown -R mysql.mysql /mydata/data/*
# service mysqld start
Starting MySQL.                                            [  OK  ]
mysql> use mydb;
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1            |
| tb2            |
+----------------+
2 rows in set (0.00 sec)

5、xtrabackup高级用法：部分表备份与恢复；

    XtraBackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持--stream选项，即不支持将数据通过管道传输给其它程序进行处理。
    还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用--copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。当然，有些情况下，部分备份也可以直接通过--copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。

    创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。
    使用--include
使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：
# innobackupex --include=‘^mydb[.]tb1‘  /path/to/backup

    使用--tables-file
此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：
# echo -e ‘mydb.tb1\mydb.tb2‘ > /tmp/tables.txt
# innobackupex --tables-file=/tmp/tables.txt  /path/to/backup

    使用--databases
此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：
# innobackupex --databases="mydb testdb"  /path/to/backup

1）重置mysql服务器：（为了不影响实验，我们将之前所有操作全部删除重置）

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
# mysql mydb <mydb_full_2014-07-12.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from mydb;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1            |
| tb2            |
+----------------+
2 rows in set (0.00 sec)

2）考虑到安全性，我们还是将整个数据做一次完全备份，然后进行指定表备份；

# innobackupex --user=root backup/    //* 完全备份；*//
innobackupex: Backup created in directory ‘/root/backup/2014-07-13_11-54-05‘
innobackupex: MySQL binlog position: filename ‘mysql-bin.000003‘, position 2071
140713 11:54:08  innobackupex: Connection to database server closed
140713 11:54:08  innobackupex: completed OK!

# innobackupex --include=‘mydb.tb1‘ backup/2014-07-13_11-54-05    //* 对指定的表进行指定路径备份；*//
... ...    //* 省略；*//
innobackupex: Backup created in directory ‘/root/backup/2014-07-13_11-54-05/2014-07-13_11-54-53‘
innobackupex: MySQL binlog position: filename ‘mysql-bin.000003‘, position 2071
140713 11:54:56  innobackupex: Connection to database server closed
140713 11:54:56  innobackupex: completed OK!

# innobackupex --apply-log --export /root/backup/2014-07-13_11-54-05/2014-07-13_11-54-53/    //* 整理部分备份数据；*//
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1666070
140713 11:57:27  innobackupex: completed OK!

3）还原数据或迁移部分数据

注意，对于还原部分表或数据，需要创建与原表一样的数据结构；

mysql> create database testdb; //* 由于资源有限，这里就另创建一个数据库来演示；*//
Query OK, 1 row affected (0.00 sec)
mysql> use mydb;
Database changed
mysql> show create table tb1;
| Table | Create Table
| tb1   | CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  `gender` enum(‘f‘,‘m‘) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)

mysql> use testdb;
mysql> CREATE TABLE `tb1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` char(30) NOT NULL,
    ->   `gender` enum(‘f‘,‘m‘) DEFAULT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table tb1 discard tablespace;    //* 由于我们要还原备份的表，所以需要将新创建的表空间文件移除； *//
Query OK, 0 rows affected (0.00 sec)

# pwd
/root/backup/2014-07-13_11-54-05/2014-07-13_11-54-53/mydb
# ls
tb1.cfg  tb1.exp  tb1.frm  tb1.ibd
# cp tb1.exp tb1.ibd /mydata/data/testdb/    //* 复制备份时路径下的.exp .ibd文件；*//
# ll
total 128
-rw-rw---- 1 mysql mysql    65 Jul 13 11:59 db.opt
-rw-r--r-- 1 root  root  16384 Jul 13 12:05 tb1.exp
-rw-rw---- 1 mysql mysql  8654 Jul 13 12:02 tb1.frm
-rw-r----- 1 root  root  98304 Jul 13 12:05 tb1.ibd
# chown -R mysql.mysql .        //* 修改权限，下面会报错；*//

mysql> alter table tb1 import tablespace;   //* 导入表空间；*// 
Query OK, 0 rows affected, 1 warning (0.30 sec)
mysql> select * from tb1;
+----+------------+--------+------+
| id | name       | gender | age  |
+----+------------+--------+------+
|  1 | jun.wang   | m      |   25 |
|  2 | jerry.liu  | m      |   28 |
|  3 | windy.ma   | m      |   30 |
|  4 | lilia      | f      |   27 |
|  5 | cherry.dai | f      |   27 |
|  6 | tom.chen   | m      |   35 |
+----+------------+--------+------+
6 rows in set (0.00 sec)

说明：为何部分数据要如此麻烦？我们知道对于InnoDB来说，直接复制.ibd文件是起不了作用的，就如同之前直接删除库目录下的文件，之后在创建同名的表时，就会报错。悲剧的是现在都没找到很好的解决办法，如果你那样做了，找到解决方法后记得分享一下。。。

六、逻辑备份与恢复

我们知道了逻辑备份是将数据导出到文件中，然后利用导出的文件恢复数据，下面我们一起来了解一下；

mysql> select * from tb1;
+----+------------+--------+------+
| id | name       | gender | age  |
+----+------------+--------+------+
|  1 | jun.wang   | m      |   25 |
|  2 | jerry.liu  | m      |   28 |
|  3 | windy.ma   | m      |   30 |
|  4 | lilia      | f      |   27 |
|  5 | cherry.dai | f      |   27 |
|  6 | tom.chen   | m      |   35 |
+----+------------+--------+------+
6 rows in set (0.00 sec)
mysql> select * from tb1 into outfile ‘/tmp/tb1_backup.txt‘;    //*导出来mysql用户有权限的目录；*//
Query OK, 6 rows affected (0.01 sec)
# cat /tmp/tb1_backup.txt 
1    jun.wang    m    25
2    jerry.liu    m    28
3    windy.ma    m    30
4    lilia    f    27
5    cherry.dai    f    27
6    tom.chen    m    35
mysql> create table tb5 like tb1;    //* 由于导出的文件为文本文件，如果我们还需要导入这个表，则需要将其表结构创建出来；*//
Query OK, 0 rows affected (0.03 sec)
mysql> load data infile ‘/tmp/tb1_backup.txt‘ into table tb5;    //* 文件导入到表中；*//
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tb5;
+----+------------+--------+------+
| id | name       | gender | age  |
+----+------------+--------+------+
|  1 | jun.wang   | m      |   25 |
|  2 | jerry.liu  | m      |   28 |
|  3 | windy.ma   | m      |   30 |
|  4 | lilia      | f      |   27 |
|  5 | cherry.dai | f      |   27 |
|  6 | tom.chen   | m      |   35 |
+----+------------+--------+------+
6 rows in set (0.00 sec)

六、总结

无论使用哪种备份与恢复，在生产环境中都无法做到及时点恢复，此时我们需要利用二进制日志进行恢复；所以请务必确保二进制日志文件的安全。

下面来复习一下二进制日志恢复；

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     69375 |
| mysql-bin.000002 |   1164429 |
| mysql-bin.000003 |      2024 |
| mysql-bin.000004 |       120 |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into tb2 (name) values(‘user5‘),(‘user6‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      384 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# cp /mydata/data/mysql-bin.000004 /root/
# service mysqld stop
Shutting down MySQL....                                    [  OK  ]
# rm -fr /mydata/data/*

如果此时数据损坏
我们首先使用前面的方法将数据还原，然后利用二进日志进行数据恢复；

# mysqlbinlog --start-position=120 --stop-position=384 /root/mysql-bin.000004 |mysql
//*    --start-position:    从数据操作起始点；*//
//*    --stop-position:    到数据操作后结束点；*//
//*    注意在xtrabackup软件中使用innobackupex备份时，可以查看备份目录中xtrabackup_binlog_info文件中有记录备份时二进制日志所记录和position位置，跟据这个位置我们可恢复到没做增量备份的数据；*//       
mysql> select * from mydb.tb2;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
+----+-------+
6 rows in set (0.00 sec)

本文出自 “jun.wang” 博客，请务必保留此出处http://junwang.blog.51cto.com/5050337/1437365

MySQL备份与恢复常用方法总结（mysqldump/xtrabackup/lvm快照备份/二进制日志及时点恢复）,古老的榕树,5-wow.com