开发者社区> 白及88> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL cp xtrabackup mysqldump备份过程详解

2016-03-16 3500

简介： <p></p> <p style="padding-top:0px; padding-bottom:0px; margin-top:0px; margin-bottom:0px; clear:both; height:auto; overflow:hidden; color:rgb(85,85,85); font-family:宋体,'Arial Narrow',arial,serif;
+关注继续查看

大纲

一、MySQL备份类型

二、MySQL备份都备份什么？

三、MySQL备份工具

四、MySQL备份策略

五、备份准备工作

六、备份策略具体演示

注：系统版本 CentOS6.4 X86_64，MySQL版本 MySQL 5.5.32，相关软件下载 http://yunpan.cn/QnymShsCMzGg9

一、MySQL备份类型

1.热备份、温备份、冷备份 （根据服务器状态）

  • 热备份：读、写不受影响；

  • 温备份：仅可以执行读操作；

  • 冷备份：离线备份；读、写操作均中止；

2.物理备份与逻辑备份 （从对象来分）

  • 物理备份：复制数据文件；

  • 逻辑备份：将数据导出至文本文件中；

3.完全备份、增量备份、差异备份 （从数据收集来分）

  • 完全备份：备份全部数据；

  • 增量备份：仅备份上次完全备份或增量备份以后变化的数据；

  • 差异备份：仅备份上次完全备份以来变化的数据；

4.逻辑备份的优点：

  • 在备份速度上两种备份要取决于不同的存储引擎

  • 物理备份的还原速度非常快。但是物理备份的最小力度只能做到表

  • 逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理

  • 逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高

  • 逻辑备份也对保持数据的安全性有保证

5.逻辑备份的缺点：

  • 逻辑备份要对RDBMS产生额外的压力，而裸备份无压力

  • 逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩

  • 逻辑备份可能会丢失浮点数的精度信息

6.增量备份与差异备份区别

增量备份与差异备份区别

说明，差异备份要比增量备份占用的空间大，但恢复时比较方便！但我们一般都用增量备份！

二、MySQL备份都备份什么？

我们备份，一般备份以下几个部分：

1.数据文件

2.日志文件（比如事务日志，二进制日志）

3.存储过程，存储函数，触发器

4.配置文件（十分重要，各个配置文件都要备份）

5.用于实现数据库备份的脚本，数据库自身清理的Croutab等……

三、MySQL备份工具

如下图，

Mysql备份工具对比

上面的所有备份工具对比，下面我们就来说一下，常用的备份工具，

1.Mysql自带的备份工具

  • mysqldump 逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢，但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。

  • mysqlhotcopy 物理备份工具，但只支持MyISAM引擎，基本上属于冷备的范畴，物理备份，速度比较快。

2.文件系统备份工具

  • cp 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。

  • lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。

3.其它工具

  • ibbackup 商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。

  • xtrabackup 开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工具。

四、MySQL备份策略

1.策略一：直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库，是最可靠的）

当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。如果你不想关闭服务器，要在执行表检查的同时锁定服务器。如果服务器在运行，相同的制约也适用于拷贝文件，而且你应该使用相同的锁定协议让服务器“安静下来”。当你完成了备份时，需要重启服务器(如果关闭了它)或释放加在表上的锁定(如果你让服务器运行)。要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。

2.策略二：mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份）

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份。

3.策略三：lvs快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，速度快适合比较烦忙的数据库

前提：

  • 数据文件要在逻辑卷上；

  • 此逻辑卷所在卷组必须有足够空间使用快照卷；

  • 数据文件和事务日志要在同一个逻辑卷上；

步骤：

(1).打开会话，施加读锁，锁定所有表；

1
2
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;

(2).通过另一个终端，保存二进制日志文件及相关位置信息；

1
mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info

(3).创建快照卷

1
lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv

(4).释放锁

1
mysql> UNLOCK TABLES;

(5).挂载快照卷，备份

1
2
mount
cp

(6).删除快照卷；

(7).增量备份二进制日志；

4.策略四：xtrabackup 备份数据库，实现完全热备份与增量热备份（MyISAM是温备份，InnoDB是热备份），由于有的数据在设计之初，数据目录没有存放在LVM上，所以不能用LVM作备份，则用xtrabackup代替来备份数据库

说明：Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup或ibbackup的一个很好的替代品。

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

  • xtrabackup 只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。

  • innobackupex 是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

特点：

  • 备份过程快速、可靠；

  • 备份过程不会打断正在执行的事务；

  • 能够基于压缩等功能节约磁盘空间和流量；

  • 自动实现备份检验；

  • 还原速度快；

5.策略五：主从复制（replication）实现数据库实时备份（集群中常用）

6.总结

单机备份是完全备份（所有数据库文件）+增量备份（备份二进制日志）相结合！

集群中备份是完全备份（所有数据库文件）+增量备份（备份二进制日志）+主从复制（replication）相结合的方法！

五、备份准备工作

1.查看服务器状态，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1
Connection id:          1
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.32-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 2 min 0 sec
Threads: 1  Questions: 4  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.033

2.查看数据目录存放位置

1
2
3
4
5
6
7
mysql> show variables like '%datadir%';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| datadir       | /mydata/data/ |
+---------------+---------------+
1 row in set (0.01 sec)

3.修改二进制日志的存放位置

(1).建立一目录用于存放二进制日志

1
2
3
4
[root@mysql ~]# mkdir /mybinlog
[root@mysql ~]# chown mysql:mysql /mybinlog
[root@mysql /]#  ll | grep mybinlog       
drwxr-xr-x    2 mysql mysql  4096 7月  22 14:39 mybinlog

(2).修改my.cnf

1
2
3
[root@mysql ~]# vim /etc/my.cnf
log-bin=/mybinlog/mysql-bin #二进制日志目录及文件名前缀
innodb_file_per_table = 1 #启用InnoDB表每表一文件，默认所有库使用一个表空间

(3).重新启动mysqld

1
[root@mysql ~]# service mysqld restart

4.查看新生成的binlog日志

1
2
[root@mysql ~]# ls /mybinlog/
mysql-bin.000001  mysql-bin.index

5.准备一个test库，里面有两张表，t1表和t2表！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
mysql> show table status from test\G #查看两张表的状态
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 167772160
Avg_row_length: 7
    Data_length: 1174405120
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
Auto_increment: NULL
    Create_time: 2013-07-21 19:37:44
    Update_time: 2013-07-21 19:52:48
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
*************************** 2. row ***************************
           Name: t2
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 20971797
Avg_row_length: 31
    Data_length: 667942912
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
Auto_increment: NULL
    Create_time: 2013-07-21 20:00:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
2 rows in set (0.01 sec)

第一张t1表，使用的是MyISAM引擎，其中有1亿多行数据，第二张t2表,使用的是INNODB引擎，其中有2千多万行数据！有博友会问了，你是在做测试怎么会有这么多数据的，下面我就的大家说一下，快速插入1亿条数据的方法！具体操作如下，

t1表，

1
2
3
4
5
6
7
8
mysql>use test; #使用 test数据库
mysql>create table t1 ( #创建一个简单的t1表，里面只有一个字段 id
id int(10) default null
)engine=myisam default charset=utf8;
mysql> show create table t1; #查看创建的表
mysql>insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); #先插入十个数据
mysql>insert into t1 select * from t1; #重复多次便能插入1亿条数据
mysql>select count(*) from t1; #查看插入数据的总数

t2表，

1
2
3
4
5
6
7
mysql>create table t2 (
id int(10) default null
)engine=innodb default charset=utf8;
mysql> show create table t2;
mysql>insert into t2 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql>insert into t2 select * from t2;
mysql>select count(*) from t2;

好了，下面我们就来详细说一说备份与还原！

六、备份策略具体演示

1.策略一：直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库）

(1).标准流程：锁表->刷新表到磁盘->拷贝文件->解锁（注,若有有可能的话，可以先停止数据库，再用cp命令准备，这样备份的数据最可靠）

(2).具体步骤：

a.打开第一个终端，

1
2
3
[root@mysql data]# mysql
mysql> FLUSH TABLES WITH READ LOCK; #刷新表到时磁盘中并读锁
Query OK, 0 rows affected (0.00 sec)

b.打开第二个终端  

1
2
[root@mysql data]# mkdir /root/alldb.`date +%F-%H-%M-%S`/ #创建备份目录
[root@mysql data]# cp -rp /mydata/data/* /root/alldb.2013-07-22-13-46-22/ #复制所以的数据库文件

c.在第一个终端解锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> UNLOCK TABLES; #解锁
Query OK, 0 rows affected (0.01 sec)
[root@mysql ~]# ll alldb.2013-07-22-13-46-22/ #查看备份好的数据库
总用量 267468
-rw-rw---- 1 mysql mysql 262221824 7月  21 20:17 ibdata1
-rw-rw---- 1 mysql mysql   5242880 7月  22 13:40 ib_logfile0
-rw-rw---- 1 mysql mysql   5242880 7月  22 13:40 ib_logfile1
drwx------ 2 mysql mysql      4096 7月  20 12:33 mysql
-rw-rw---- 1 mysql mysql     27698 7月  20 12:33 mysql-bin.000001
-rw-rw---- 1 mysql mysql       190 7月  22 13:40 mysql-bin.index
-rw-rw---- 1 mysql mysql      1925 7月  21 13:07 mysql-slow.log
-rw-r----- 1 mysql mysql     21906 7月  22 13:40 mysql.test.com.err
-rw-rw---- 1 mysql mysql         5 7月  22 13:40 mysql.test.com.pid
drwx------ 2 mysql mysql      4096 7月  20 12:33 performance_schema
drwx------ 2 mysql mysql      4096 7月  21 20:00 test

(3).模拟数据库损坏

直接删除数据目录中的所有文件

1
2
3
4
[root@mysql data]# rm -rf *
[root@mysql data]# ll
总用量 0
[root@mysql data]#

(4).具体还原步骤

a.mysql这时是无法停止的

1
2
[root@mysql mydata]# service mysqld stop
ERROR! MySQL server PID file could not be found!

b.查找mysql所有进程   

1
2
3
[root@mysql mydata]# ps aux | grep mysqld
root      2728  0.0  0.1  11300  1520 pts/1    S    15:01   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/mysql.test.com.pid
mysql     3029  0.1  9.1 773908 92312 pts/1    Sl   15:01   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mydata/data/mysql.test.com.err --pid-file=/mydata/data/mysql.test.com.pid --socket=/tmp/mysql.sock --port=3306

c.杀死mysql的所有进程

1
[root@mysql ~]# killall mysqld

d.初始化mysql

1
[root@mysql ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql

e.复制完全备份的数据文件到数据目录中

1
2
[root@mysql test]# alias cp=cp #修改cp别名，不然复制时老是提醒是否覆盖
[root@mysql test]# cp -pr /root/alldb.2013-07-22-13-46-22/* /mydata/data/  #复制完全备份的文件到数据目录中

f.启动mysql数据库

1
2
[root@mysql test]# service mysqld start
Starting MySQL SUCCESS!

g.测试并查看数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[root@mysql test]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution
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> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.01 sec)
mysql> select count(*) from t1;
+-----------+
| count(*)  |
+-----------+
| 167772160 |
+-----------+
1 row in set (0.01 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row in set (9.95 sec)

大家可以看到所有数据都恢复了，嘿嘿！

(5).总结

cp命令，对其进行的备份，速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差，适合小型数据库备份！

2.策略二：mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份）

(1).mysqldump命令详解

1
2
3
4
5
6
7
8
mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql
--all-tables #备份所有库
--lock-all-tables #为所有表加读锁
--routinge #存储过程与函数
--triggers #触发器
--events #记录事件
--master-data=2 #在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在主从复制中才有意义
--flush-logs #日志滚动一次

(2).具体备份过程如下

a.查看备份前的binlog日志

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000022 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

b.备份所有库（完全备份）

1
[root@mysql mybackup]# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql

c.查看备份是否成功    

1
2
3
[root@mysql mybackup]# ll -h
总用量 739M
-rw-r--r-- 1 root root 739M 7月  22 16:31 2013-07-22-16-20.full.sql

d.查看新生成的binlog日志

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

e.插入几条新的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t1;
+-----------+
| count(*)  |
+-----------+
| 167772160 |
+-----------+
1 row in set (0.01 sec)
mysql>  insert into t1 values(167772164),(167772165),(167772166);

f.再次查看binlog日志

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 |      363 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

g.备份二进制日志（增量备份）

1
[root@mysql mybackup]# cp /mybinlog/mysql-bin.000023 /root/mybackup/2013-07-22-16-20.binlog.full.000001

h.查看备份的二进制日志    

1
2
3
4
[root@mysql mybackup]# ll
总用量 756264
-rw-r----- 1 root root       363 7月  22 16:34 2013-07-22-16-20.binlog.full.000001
-rw-r--r-- 1 root root 774402118 7月  22 16:31 2013-07-22-16-20.full.sql

i.模拟数据库损坏

1
2
3
4
5
6
7
8
9
[root@mysql mybackup]# cd /mydata/data/
[root@mysql data]# ls
ibdata1      mysql-bin.000001  mysql-bin.000005  mysql-bin.000009  mysql.test.com.err
ib_logfile0  mysql-bin.000002  mysql-bin.000006  mysql-bin.000010  mysql.test.com.pid
ib_logfile1  mysql-bin.000003  mysql-bin.000007  mysql-bin.index   performance_schema
mysql        mysql-bin.000004  mysql-bin.000008  mysql-slow.log    test
[root@mysql data]# rm -rf * #删除所有数据
[root@mysql data]# ll
总用量 0

(3).具体还原过程如下，

a.查找mysql进程

1
2
3
4
5
[root@mysql data]# ps -aux | grep mysqld
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
root      3599  0.0  0.1  11304  1340 pts/1    S    15:18   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/mysql.test.com.pid
mysql     3901  4.7 21.5 1167384 218684 pts/1  Sl   15:18   3:49 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mydata/data/mysql.test.com.err --pid-file=/mydata/data/mysql.test.com.pid --socket=/tmp/mysql.sock --port=3306
root      4469  0.0  0.0 103244   876 pts/1    S+   16:38   0:00 grep mysqld

b.杀死所有进程     

1
[root@mysql data]# killall mysqld

c.初始化mysql并启动mysql    

1
[root@mysql data]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql

d.因为我们不是全新初始化的，可能会有报错的二进制日志，所有我们这里全部删除

1
[root@mysql data]# rm -rf  /mybinlog/*

e.启动mysql数据库，启动时会重新生成新的二进制日志的

1
[root@mysql ~]# service mysqld start

f.恢复到备份状态,备份前先关闭对恢复过程的二进制日志记录，因为记录恢复语句是毫无意义的

1
2
mysql> set global sql_log_bin=0;
mysq> source /root/mybackup/2013-07-22-16-20.full.sql

g.打开另一个终端查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select count(*) from t1;
+-----------+
| count(*)  |
+-----------+
| 167772163 |
+-----------+
1 row in set (1 min 29.63 sec) #可以看到用mysqldump备份数据，还原myisam引擎时大概需要30s时间(共1亿多条数据，速度不是挺快的)
mysql> show tables;    
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row in set (46.14 sec)  #还原INNODB引擎，大概50s左右(共2千多万条数据)

h.查看最后十条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from t1 order by id desc limit 10;
+-----------+
| id        |
+-----------+
| 167772163 |
| 167772162 |
| 167772161 |
|        10 |
|        10 |
|        10 |
|        10 |
|        10 |
|        10 |
|        10 |
+-----------+
10 rows in set (0.00 sec)

大家可以看到，我们已经恢复到，完全备份时的状态，但我们最后插入的三条数据没有恢复，下面我们恢复，数据库损坏前我们插入的三条数据！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@mysql ~]# mysqlbinlog /root/mybackup/2013-07-22-16-20.binlog.full.000001 | mysql test
[root@mysql ~]# mysql test
mysql> select * from t1 order by id desc limit 10;
+-----------+
| id        |
+-----------+
| 167772166 |
| 167772165 |
| 167772164 |
| 167772163 |
| 167772162 |
| 167772161 |
|        10 |
|        10 |
|        10 |
|        10 |
+-----------+
10 rows in set (47.01 sec)
mysql>

大家可以看到，已经恢复我们最后增加的三条数据！

i.最后，打开二进制记录并查看恢复状况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> set global sql_log_bin=1;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

(4).总结：

基于mysqldump通常我们就是完整备份+二进制日志来进行恢复的！

3.策略三：lvs快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，速度快适合比较烦忙的数据库！

说明：要求你的MySQL的数据目录必须在lvm卷上！

具体步骤如下，

(1).在MySQL中为所有表加读锁，不要关闭终端，否则锁将失效,滚动日志

1
2
3
4
5
6
7
8
9
10
11
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

(2).另开一终端速度建立快照,我的那个卷组是/dev/myvg/mydata

1
[root@mysql ~]# lvcreate -L 2G -n mysql-snap -s -p r /dev/myvg/mydata

(3).速度释放读锁

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

(4).挂载快照，拷备出来，卸载快照，删除快照

1
2
3
4
5
[root@mysql ~]# mount /dev/myvg/mysql-snap /mnt
[root@mysql ~]# mkdir /root/mybackup/lvm
[root@mysql ~]# cp -pR /mnt/* /root/mybackup/lvm/
[root@mysql ~]# umount /mnt
[root@mysql ~]# lvremove /dev/myvg/mysql-snap

(5).就这样一次完整备份就完成了，下面来测试能否正常使用

1
2
3
4
[root@mysql ~]# service mysqld stop
[root@mysql ~]# rm -rf  /mydata/*
[root@mysql ~]# cp -Rp /root/mybackup/lvm/* /mydata/
[root@mysql ~]# service mysqld start #如果能正常启动代表没有问题

(6).如果在完整备份后MySQL出现故障，与mysqldump一样，先恢复上次的完整备份，再利用二进制日志恢复，找到完整备份时的二进制位置，把从那时到故障前的日志用mysqlbinlog导出来，然后再导入到MySQL中。这个同mysqldump中实验一致就不重复了。

(7).总结

用lvm的快照来备份速度是非常快的，而且几乎热备，恢复也很快速，操作也简单，完整恢复后再将相应二进制恢复即可。

4.策略四：xtrabackup 备份数据库，实现完全热备份与增量热备份（MyISAM是温备份，InnoDB是热备份）

(1).安装percona-xtrabackup-2.1.3-608所需的依赖包

1
[root@mysql ~]# yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.*  -y

(2).解压软件包键入命令文件目录

1
2
3
4
5
6
7
8
9
10
11
[root@mysql ~]# tar xf percona-xtrabackup-2.1.3-608.tar.gz
[root@mysql src]# cd percona-xtrabackup-2.1.3/bin/
[root@mysql bin]# ll
总用量 112284
-rwxr-xr-x 2 root root   110738 5月  23 02:50 innobackupex
lrwxrwxrwx 2 root root       12 7月  23 04:48 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x 2 root root  2211237 5月  23 02:50 xbcrypt
-rwxr-xr-x 2 root root  2285672 5月  23 02:50 xbstream
-rwxr-xr-x 2 root root 13033745 5月  23 02:50 xtrabackup
-rwxr-xr-x 2 root root 16333506 5月  23 02:28 xtrabackup_55
-rwxr-xr-x 2 root root 80988093 5月  23 02:40 xtrabackup_56

(3).将innobackupex、xtrabackup等文件copy到mysql程序目录下/bin、目录

1
[root@mysql bin]# cp -pl * /usr/local/mysql/bin/

(4).将mysql安装目录下的文件做软链接到/usr/bin/目录下。这个比变量方便，这样就完成了部署安装

1
[root@mysql bin]# ln -sv /usr/local/mysql/bin/* /usr/bin/

(5).测试

1
2
3
4
[root@mysql bin]# innobackupex
innobackupex        innobackupex-1.5.1
[root@mysql bin]# xtrabackup
xtrabackup     xtrabackup_55  xtrabackup_56

(6).查看innobackupex选项

1
[root@mysql ~]# innobackupex --help

(7).设置mysql密码

1
[root@mysql ~]# mysqladmin -uroot password 123456

(8).全库备份

1
[root@mysql ~]# innobackupex --host=localhost --user=root --password=123456 /root/mybackup/xtrabackup/

报错，

1
2
xtrabackup: Error: Please set parameter 'datadir'
innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 389.

解决方法，

1
2
3
[root@mysql data]# vim /etc/my.cnf
#增加一行
datadir = /mydata/data

再次执行成功，

1
2
[root@mysql xtrabackup]# innobackupex --host=localhost --user=root --password=123456  --defaults-file=/etc/my.cnf /root/mybackup/xtrabackup/
130723 05:29:13  innobackupex: completed OK!

(9).查看备份文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@mysql xtrabackup]# ll
总用量 4
drwxr-xr-x 5 root root 4096 7月  23 05:33 2013-07-23_05-32-51
[root@mysql xtrabackup]# cd 2013-07-23_05-32-51/
[root@mysql 2013-07-23_05-32-51]# ll
总用量 190496
-rw-r--r-- 1 root root       260 7月  23 05:32 backup-my.cnf
-rw-r----- 1 root root 195035136 7月  23 05:32 ibdata1
drwxr-xr-x 2 root root      4096 7月  23 05:33 mysql
drwxr-xr-x 2 root root      4096 7月  23 05:33 performance_schema
drwx------ 2 root root      4096 7月  23 05:33 test
-rw-r--r-- 1 root root        13 7月  23 05:33 xtrabackup_binary
-rw-r--r-- 1 root root        23 7月  23 05:33 xtrabackup_binlog_info
-rw-r----- 1 root root        95 7月  23 05:33 xtrabackup_checkpoints
-rw-r----- 1 root root      2560 7月  23 05:33 xtrabackup_logfile

数据会完整备份到/root/mybackup/xtrabackup/中目录名字为当前的日期，xtrabackup会备份所有的InnoDB表，MyISAM表只是复制表结构文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。除了保存数据外还生成了一些xtrabackup需要的数据文件，详解如下：

  • xtrabackup_checkpoints  备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

  • xtrabackup_binlog_info mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

  • xtrabackup_binary 备份中用到的xtrabackup的可执行文件。

  • backup-my.cnf 备份命令用到的配置选项信息。

  • xtrabackup_logfile 记录标准输出信息xtrabackup_logfile

(10).测试恢复MySQL,用xtrabackup来完整恢复

1
2
3
4
5
6
7
8
[root@mysql data]# service mysqld stop
[root@mysql data]# rm -rf /mydata/data/*
[root@mysql data]# innobackupex --apply-log /root/mybackup/xtrabackup/2013-07-23_05-48-03/
#--apply-log 的意义在于把备份时没commit的事务撤销，已经commit的但还在事务日志中的应用到数据库
[root@mysql data]# innobackupex --copy-back /root/mybackup/xtrabackup/2013-07-23_05-48-03/
#--copy-back数据库恢复，后面跟上备份目录的位置
[root@mysql data]# chown -R mysql:mysql /mydata/data
[root@mysql data]# service mysqld start #如果能启动代表恢复正常

(11).在表中新增一些数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> insert into t1 values (123),(456),(789);
#查看一下数据
mysql> use test;
Database changed
mysql> select * from t1 order by id desc limit 10;
+------+
| id   |
+------+
|  789 |
|  456 |
|  333 |
|  222 |
|  123 |
|  111 |
|   33 |
|   22 |
|   11 |
|   10 |
+------+
10 rows in set (9.47 sec)

(12).增量备份

1
2
3
4
[root@mysql data]# innobackupex --user=root --password=123456 --incremental --incremental-basedir=/root/mybackup/xtrabackup/2013-07-23_05-48-03/ /root/mybackup/xtrabackup/
#--incremental 指定是增量备份
#--incremental-basedir 指定基于哪个完整备份做增量备份，最后是增量备份保存的目录
注：增量备份只能对InnoDB引擎做增量备份，对MyISAM的表是完全复制

(13).测试增量备份恢复

1
2
3
4
5
6
[root@mysql data]# service mysqld stop
[root@mysql data]# rm -rf /mydata/data/*
[root@mysql data]# innobackupex --apply-log --redo-only
#--redo-only 指的是把备份时commit的但还在事务日志中的应用到时数据，但是还没提交的不撤消，
因为这个事务可能在增量备份中提交，假如的撤消了增量备份中就提交不，因为事务已经不完整
#/root/mybackup/xtrabackup/2013-07-23_05-48-03/ 是完全备份的目录

(14).将增量备份全部并到完整备份中去

1
2
3
[root@mysql data]# innobackupex --apply-log /root/mybackup/xtrabackup/2013-07-23_05-48-03/ --incremental-dir=/root/mybackup/xtrabackup/2013-07-23_06-05-37/
#/root/mybackup/xtrabackup/2013-07-23_05-48-03/ 这个是完整备份的目录
#--incremental-dir 后跟的是增量备份的目录

注：这个会使增量备份中的的数据合并到完整备份中，如果还有增量备份，继续合并，恢复时恢复完整备份即可

(15).恢复数据，并起动MySQL

1
2
3
[root@mysql data]# innobackupex --copy-back /root/mybackup/xtrabackup/2013-07-23_05-48-03/
[root@mysql data]# chown -R mysql:mysql /mydata/data/
[root@mysql data]# service mysqld start

(16).查看数据有没丢失

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@mysql data]# mysql -uroot -p123456 test
mysql> use test;
Database changed
mysql> select * from t1 order by id desc limit 10;
+------+
| id   |
+------+
|  789 |
|  456 |
|  333 |
|  222 |
|  123 |
|  111 |
|   33 |
|   22 |
|   11 |
|   10 |
+------+
10 rows in set (9.47 sec)

所有数据全部恢复！

17.总结

如果在增量备份后数据库出现故障，我们需要通过完整备份+到现在为止的所有增量备份+最后一次增量备份到现在的二进制日志来恢复。

18.附注

单独备份：

1
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test /root/mybackup

备份并打包压缩：

1
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ | gzip > /root/mybackup/testdb.tar.gz

带时间戳：

1
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ | gzip > /root/mybackup/`date +%F`_testdb.tar.gz

备份信息输出重定向到文件：

1
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ 2>/root/mybackup/test.log | gzip 1>/root/mybackup/test.tar.gz

说明：

1
2
3
4
5
6
7
--stream #指定流的格式，目前只支持tar
--database=test #单独对test数据库做备份 ，若是不添加此参数那就那就是对全库做
2>/root/mybackup/test.log #输出信息写入日志中
1>/root/mybackup/test.tar.gz #打包压缩存储到该文件中
解压 tar -izxvf 要加-i参数，官方解释 innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。
此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。


5.策略五：主从复制（replication）实现数据库实时备份（集群中常用）


参考：




版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

存储 Oracle 关系型数据库 MySQL 数据库
云数据库 MySQL 版xtrabackup 云数据库 MySQL 版mysqldump
开发者社区 > 数据库 > 文章
数据库备份
作者高分内容
更多
项目经理 架构师的区别 5176 数据库设计 2149 系统架构： 2500 怎样挖掘用户需求 1733 CASE 工具有哪些 3101
相关文章
xaubllxwtvaqiu
[MySQL FAQ]系列 — mysqldump加-w参数备份
[MySQL FAQ]系列 — mysqldump加-w参数备份
0 0
程序猿v
shell脚本使用mysqldump+mysql同步线上库和测试库数据
shell脚本使用mysqldump+mysql同步线上库和测试库数据
0 0
MySQL技术
【MySQL】探测mysqldump详细过程
相信大家对mysqldump应该不陌生，但是大家对mysqldump的原理及备份过程真的熟悉吗？今天，我们一起来深入理解一下mysqldump的备份原理及备份过程以及不同参数产生的效果。
0 0
MySQL技术
【MySQL】mysqldump备份与恢复
mysqldump常用于MySQL数据库逻辑备份。
0 0
琦彦
MySQL：使用mysqldump在Windows数据库定时备份
MySQL：使用mysqldump在Windows数据库定时备份
0 0
红目香薰
mysql还原数据mysqldump
mysql还原数据mysqldump
0 0
-技术小能手-
[MySQL FAQ]系列 — mysqldump加-w参数备份
[MySQL FAQ]系列 — mysqldump加-w参数备份
0 0
重庆八怪
MySQL：mysqldump 导出数据异常重启及drop栈帧
1507 0
幸福的红帆
mysql主从复制-mysqldump
633 0
云栖希望。
Mysql导出表结构及表数据 mysqldump用法
1020 0
+关注
白及88
白及
文章
问答
作者高分内容
更多
项目经理 架构师的区别 5176 数据库设计 2149 系统架构： 2500 怎样挖掘用户需求 1733 CASE 工具有哪些 3101
文章排行榜
最热
最新
1 承上启下：基于全域漏斗分析的主搜深度统一粗排 91460
2 复杂链表的复制 90594
3 【Transform3D】转换详解（看完就会） 88136
4 【flexbox弹性布局学习指南】CSS热门布局方案 68821
5 如何构建标签画像工程体系及实现方案 63096
6 DCM：中间件家族迎来新成员 61163
7 系统的混乱并业务本身之复杂，我们并不擅长处理『简单』 48267
8 超越10倍开发者 48135
9 ⚡Three.js-在场景中导入3D动画模型 46102
10 Request&Response 39090
11 《JavaScript高级程序设计》__ 作用域&内存 37892
12 影响成长的关键思考 34899
13 K8S学习-当我们部署应用的时候都发生了什么？（第一篇） 30678
14 【从零开始学习深度学习】12. 什么是模型的训练误差？基于三阶多项式的欠拟合与过拟合训练过程演示 27890
15 阿里云物联网平台设备分发实战 33496
16 ChatGPT 常见错误原因及解决方案：报错、回答不完整等 21606
17 Python3，5行代码，让你拥有无限量壁纸美图，终于告别手动下载了。 16278
18 Python3，区区9行代码批量提取PDF文件的指定内容，我被震惊了.... 5176
19 Python3，9行代码，对比两个Excel数据差异，并把差异结果重新保存。 8395
20 深聊性能测试，从入门到放弃之： Windows系统性能监控(一) 性能监视器介绍及使用。 9371
1 【数据设计与实现】第5章：同步与互斥 53
2 Python库函数pandas读取excel数据 54
3 谈谈《流浪地球2》中如何正确的“重启互联网” 1711
4 2023阿里云学生服务器免费申请流程 128
5 2023 年8个ChatGPT 的替代品 88
6 java: 错误: 无效的源发行版：17 110
7 YAML与Spring Boot 208
8 AI给你送年画啦！每一张都是独一无二 102
9 关于2023年1月1日起扣税规则说明 73
10 第3期乘风伯乐奖--寻找百位乘风者伯乐，邀请新博主入驻即可获奖 61
11 使用ChatGPT帮我们写一篇论文，最后查重的重复率会是多少？ 74
12 中原银行对金融行业实时数仓的现状与发展趋势思考 269
13 系列征文3｜算法领域主题征文开始啦！ 52
14 玖章算术CEO叶正盛：程序员必须掌握的数据库原理 133
15 大火的4D Radar数据集及基线模型汇总 53
16 海思3559万能平台搭建：RTSP实时播放的支持 55
17 万字长文 | 多目标跟踪最新综述（基于Transformer/图模型/检测和关联/孪生网络）（下） 78
18 Windows和Ubuntu双系统安装（非虚拟机） 175
19 关系代数和SQL语法 55
20 2023跨年代码（烟花+自定义文字+背景音乐+雪花+倒计时） 803
相关课程
更多
MySQL实战进阶
531
7
去学习
Java Spring Boot 2.6.0开发实战-1024程序员节创造营公益课
858
5
去学习
云数据库RDS MySQL从入门到高阶
1363
7
去学习
数据库及SQL/MySQL基础
4375
22
去学习
Linux MySQL服务器搭建与应用
12261
5
去学习
MySQL高级应用 - 索引和锁
15001
65
去学习
推荐文章
2022藏经阁年度电子书榜单出炉！ 参与应用引擎 SAE评测，赢取CHERRY机械键盘等好礼 “第益课”大学生技术公益 乘风者计划邀您入驻社区，精彩权益即刻享
相关电子书
更多
让 MySQL 原生分布式触手可及
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载