MySQL 备份与还原详解
大纲
一、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备份工具
如下图，
上面的所有备份工具对比，下面我们就来说一下，常用的备份工具，
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）实现数据库实时备份（集群中常用）
说明，MySQL主从复制会单独写一篇博客讲解，今天就说到这，嘿嘿！^_^……
