MySQL备份之二

lvs快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，速度快适合比较烦忙的数据库！
说明：要求你的MySQL的数据目录必须在lvm卷上！
具体步骤如下，
(1).在MySQL中为所有表加读锁，不要关闭终端，否则锁将失效,滚动日志，记录二进制文件及相关信息
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
[root@mysql ~]# lvcreate -L 2G -n mysql-snap -s -p r /dev/myvg/mydata
(3).速度释放读锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
(4).挂载快照，拷备出来，卸载快照，删除快照
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
#增量备份二进制文件
就这样一次完整备份就完成了，下面来测试能否正常使用
[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所需的依赖包
[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).解压软件包键入命令文件目录
[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、目录
[root@mysql bin]# cp -pl * /usr/local/mysql/bin/
(4).将mysql安装目录下的文件做软链接到/usr/bin/目录下。这个比变量方便，这样就完成了部署安装
[root@mysql bin]# ln -sv /usr/local/mysql/bin/* /usr/bin/
(5).测试
[root@mysql bin]# innobackupex
innobackupex        innobackupex-1.5.1
[root@mysql bin]# xtrabackup
xtrabackup     xtrabackup_55  xtrabackup_56
(6).查看innobackupex选项
[root@mysql ~]# innobackupex --help
(7).设置mysql密码
[root@mysql ~]# mysqladmin -uroot password 123456
(8).全库备份
[root@mysql ~]# innobackupex --host=localhost --user=root --password=123456 /root/mybackup/xtrabackup/
报错
xtrabackup: Error: Please set parameter 'datadir'
innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 389.
解决方法
[root@mysql data]# vim /etc/my.cnf
#增加一行
datadir = /mydata/data
再次执行成功
[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).查看备份文件
[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来完整恢复
[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).在表中新增一些数据
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).增量备份
[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).测试增量备份恢复
[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).将增量备份全部并到完整备份中去
[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
[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).查看数据有没丢失


[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.附注
单独备份：
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test /root/mybackup
备份并打包压缩：
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ | gzip > /root/mybackup/testdb.tar.gz
带时间戳：
innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test --stream=tar /root/mybackup/ | gzip > /root/mybackup/`date +%F`_testdb.tar.gz
备份信息输出重定向到文件：
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
说明：
--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）实现数据库实时备份（集群中常用）