总结Mysql备份与恢复
*******************************************************************基础理论篇
一、数据备份的重要性
二、备份类型
三、数据库备份策略
实际操作篇
一、准备测试环境
二、使用mysqldump命令备份与恢复数据库
三、使用LVM镜像备份数据
四、使用Xtrabackup进行MySQL备份与恢复
*******************************************************************
基础理论篇
一、备份数据的重要性
在任何一家企业中，数据都是无价的，如果企业的数据丢失或损坏，我们之前也没有对数据进行备份，那么此时对与企业来说损失是惨重的，如果恰恰损坏或丢失的数据是企业的核心数据那么此时企业的前景都是未卜的，由此可见数据备份对于一家企业是多么的重要。
二、备份类型
[*] 完全备份：备份整个数据库
[*] 增量备份：数据库从上一次完全备份或者最近一次的增量备份以来改变的内容的备份
[*] 差异备份：基于的是最近一次的完整数据备份，差异备份仅捕获自该次完整备份后发生更改的数据。
三、数据库备份策略
3.1、备份类型组合
[*] 采用完整备份和增量备份相结合的自动化备份策略
[*] 采用完整备份和差异备份相结合的自动化备份策略
3.2、备份周期
[*] 每周定期自动完整备份一次数据，每天自动增量备份或差异备份数据
3.3、备份地点
[*] 采用异机备份放式，即将数据自动备份到数据库之外的其它服务器
3.4、数据恢复测试
[*] 备份完数据以后应定期恢复测试，以保证备份数据的完整性
实际操作篇
一、准备测试环境
由于mysql默认是将二进制日志和数据是放在同一目录下的，当数据损坏时，二进制日志也有可能遭遇破坏，为了保证二进制日志的安全性，将数据与二进制分开存放
1.1、创建二进制日志目录并修改权限
# mkdir /mybinlog
# chmod mysql:mysql /mybinlog/
1.2、修改mysql的主配置文件
# sed -i s@log-bin=mysql-bin@log-bin=/mybinlog/mysql-bin@g/etc/my.cnf 1.3、启动mysql服务
# service mysqld start
Starting MySQL.. SUCCESS
1.4、创建数据库与表（用户后期备份测试使用）
# mysql -uroot
mysql> create database xiaodong;
Query OK, 1 row affected (0.00 sec)
mysql> use xiaodong;
Database changed
mysql> create table tb1 (id int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb1 (name) values ('wu song'),('song jiang');
Query OK, 2 rows affected (0.03 sec)
Records: 2Duplicates: 0Warnings: 0
mysql> select * from tb1;
+----+------------+
|>
+----+------------+
|1 | wu song |
|2 | song jiang |
+----+------------+
2 rows in set (0.00 sec)
1.5、创建备份数据库的目录
# mkdir /backup 二、使用mysqldump命令备份与恢复数据库
2.1、备份数据库
# mysqldump -uroot --databases xiaodong--lock-all-tables --master-data=2 --events > /backup/mysql-`date +%F`.sql 注释：
--all-databases : 备份所有数据库
--lock-all-tables : 备份时锁定所有表
--master-data=2 : 在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在 主从复制中才有意义
--databases ：此选项备份单个数据库，并且在恢复时无需先创建数据库，直接导入就可以
2.2、备份完之后数据库中增加了新表tb2
mysql> select database(); ##查看默认数据库
+------------+
| database() |
+------------+
| xiaodong |
+------------+
1 row in set (0.00 sec)
mysql>create table tb2 (id int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb2 (name) values ('yang guo'),('xiao long nv');
Query OK, 2 rows affected (0.01 sec)
Records: 2Duplicates: 0Warnings: 0
mysql> select * from tb2;
+----+--------------+
|>
+----+--------------+
|1 | yang guo |
|2 | xiao long nv |
+----+--------------+
2 rows in set (0.00 sec)
2.3、此时管理员误操作将数据库删除
mysql> drop database xiaodong;
Query OK, 2 rows affected (0.00 sec)
mysql>
假如删除的数据库对于企业来说很重要，此时我们想要恢复数据，这该怎么办呢？ 可能你会立刻想到，之前我们不是备份过数据库嘛，直接恢复不就可以了嘛。但是请不要忘记，tb2表是在数据备份之后创建的，显然只是简单的恢复是无法满足我们的需求的，此时就需要用到二进制日志；
2.4、查看完整备份文件中的MASTER_LOG_POS的值
# less /backup/mysql-2013-09-02.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=581;
-- Current Database: `xiaodong`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xiaodong` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `xiaodong`;
注释：MASTER_LOG_FILE指定日志文件
MASTER_LOG_POS 指定数据的还原点
注意：如果之前备份的时候没有加--master-data=2 则不会出现MASTER_LOG_POS的值，此值对于数据恢复及其重要。
2.5、查询二进制日志以确定数据还原点并导出日志
# mysqlbinlog --start-position581 /mybinlog/mysql-bin.000001
# at 967 ##需要还原581到967的数据
#1309024:09:50 server>
SET TIMESTAMP=1378066190/*!*/;
drop database xiaodong ## 删除了数据库，无需恢复
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# mysqlbinlog --start-position581 --stop-position 967 /mybinlog/mysql-bin.000001 > /tmp/mysql-bin.sq
2.6、数据恢复（完整备份+二进制日志恢复数据）
# mysql -uroot < /backup/mysql-2013-09-02.sql
# mysql -uroot < /tmp/mysql-bin.sq
2.7、验证数据库是否恢复成功
# mysql -uroot
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| xiaodong |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from xiaodong.tb1
-> ;
+----+------------+
|>
+----+------------+
|1 | wu song |
|2 | song jiang |
+----+------------+
2 rows in set (0.00 sec)
mysql> select * from xiaodong.tb2;
+----+--------------+
|>
+----+--------------+
|1 | yang guo |
|2 | xiao long nv |
+----+--------------+
2 rows in set (0.00 sec)三、shiyong
三、使用LVM镜像备份数据
3.1 Lvm的创建（详细的过程请参考LVM详解）
# pvcreate sdb{1,2,3}
# vgcreatetestvg /dev/sdb{1,2,3}
# lvcreate -L 1G -n testlv testvg
# mke2fs -t ext4 /dev/testvg/testlv
# mount /dev/testvg/testlv /mydata/
# mkdir /mydata/data
# chown -R mysql.mysql /mydata/data/
3.2、重新初始化数据库并启动服务
# mkdir -pv /mydata/mybinlog #创建二进制目录
# chown mysql.mysql -pv /mydata/mybinlog/
# cd /usr/local/mysql/
# ./scripts/mysql_install_db --user=mysql--datadir=/mydata/data/
# sed -i s@log-bin=/mybinlog/mysql-bin@log-bin=/mydata/mybinlog/mysql-bin@g /etc/my.cnf #修改二进制日志目录
# service mysqld start
3.3、在数据库中创建数据
# mysql -uroot
mysql> set session sql_log_bin=0; #关闭二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql> source /backup/mysql-2013-09-02.sql #将之前的完整备份导入
mysql> set session sql_log_bin=1; #开启二进制日志
3.4、创建LVM镜像
# mysql
mysql> flush tables with read lock; #施加全局锁
Query OK, 0 rows affected (0.00 sec)
mysql> show master status; #记录下状态值
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
注意：此时不能退出数据库，退出全局锁就会失效，我们重新打开一个终端来继续下面的操作
# lvcreate -L 1G -s -p r -n mydata-snap /dev/testvg/testlv
Logical volume "mydata-snap" created
#mount /dev/testvg/mydata-snap /mnt/#挂载镜像文件
mount: block device /dev/mapper/testvg-mydata--snap is write-protected, mounting read-only
#cd /mnt
# tar jcvf /backup/snap.tar.gz *
# cd /
#umount /mnt
#rm -rf /dev/testvg/mydata-snap
重新回到上一个终端，取消全局锁或直接退出数据库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
3.5、继续修改数据库创建tb2表
mysql> select database(); ##查看默认数据库
+------------+
| database() |
+------------+
| xiaodong |
+------------+
1 row in set (0.00 sec)
mysql>create table tb2 (id int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb2 (name) values ('yang guo'),('xiao long nv');
Query OK, 2 rows affected (0.01 sec)
Records: 2Duplicates: 0Warnings: 0
mysql> select * from tb2;
+----+--------------+
|>
+----+--------------+
|1 | yang guo |
|2 | xiao long nv |
+----+--------------+
2 rows in set (0.00 sec)
3.6、增量备份数据
# mysqlbinlog --start-position 107 /mydata/mybinlog/mysql-bin.000003 > /backup/mysql-bin-`date +%F`.log 注释：
--start-position 107 是在创建Lvm镜像时记录的日志
3.7、此时管理员误删除了数据库
mysql> drop database xiaodong;
Query OK, 2 rows affected (0.07 sec)
3.8、恢复数据
# cd /backup/
# mkdir snap
# tar xf snap.tar.gz -C snap
# cd snap
#cp * /mydata/data/ -a #将数据库复制到数据目录中
# mysql < /backup/mysql-bin-2013-09-02.log 导入增量备份
3.9、验证数据库是否恢复成功
# mysql -uroot
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| xiaodong |
+--------------------+
5rows inset(0.00sec)
mysql> select * from xiaodong.tb1
-> ;
+----+------------+
|>
+----+------------+
|1| wu song |
|2| song jiang |
+----+------------+
2rows inset(0.00sec)
mysql> select * from xiaodong.tb2;
+----+--------------+
|>
+----+--------------+
|1| yang guo |
|2| xiao long nv |
+----+--------------+
2rows inset(0.00sec)
四、使用Xtrabackup进行MySQL备份与恢复
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。xtrabackup下载地址://www.percona.com/software/percona-xtrabackup/
4.1、下载安装xtrabackup此处使用percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm
# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm
warning: percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key>
error: Failed dependencies:
perl(DBD::mysql) is needed by percona-xtrabackup-2.1.4-656.rhel6.x86_64
perl(Time::HiRes) is needed by percona-xtrabackup-2.1.4-656.rhel6.x86_64
4.1.1、安装软件包的时候出现了问题，需要先解决依赖关系
# yum install perl-DBD-MySQLperl-Time-HiRes -y 4.1.2、再次安装xtrabackup
# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm 4.2、创建备份数据的最小权限用户
mysql> CREATE USER 'bkpuser'@'localhost'>
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'@'localhost';
mysql> GRANT>
mysql> FLUSH PRIVILEGES;
4.3、完整备份一次mysql
# innobackupex --user=bkpuser --password=s3cret /backup/
**********************
compact FALSE
rebuild-indexes FALSE
rebuild-threads 1
incremental-force-scan FALSE
defaults-group mysqld
xtrabackup: Error: Please set parameter 'datadir'
innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 416.
备份时候出现问题
4.3.1、解决问题
# service mysqld stop
# vim /etc/my.cnf
datadir = /mydata/data #添加此行指定数据目录
# service mysqld start
4.3.2、再次备份mysql
# innobackupex --user=bkpuser --password=s3cret /backup/
innobackupex: Backup created in directory '/backup/2013-09-02_23-05-53'
innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 107
130902 23:05:56innobackupex: Connection to database server closed
130902 23:05:56innobackupex: completed OK!
4.4、查看Mysql备份目录
# cd /backup/
# ls
2013-09-02_23-14-28
# cd 2013-09-02_23-14-28/
# ls
backup-my.cnfmysql test xtrabackup_binary xtrabackup_checkpoints
ibdata1 performance_schemaxiaodongxtrabackup_binlog_infoxtrabackup_logfile
注释：
xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
backup-my.cnf —— 备份命令用到的配置选项信息；
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处与不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
4.5、准备(prepare)一个完全备份
# innobackupex --apply-log /backup/2013-09-02_23-14-28/
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130902 23:36:04InnoDB: Starting shutdown...
130902 23:36:08InnoDB: Shutdown completed; log sequence number 1619980
130902 23:36:08innobackupex: completed OK!
4.6、模拟数据库损坏并恢复
# service mysqld stop
# rm -rf /mydata/data/*
# innobackupex --copy-back /backup/2013-09-02_23-14-28/
出现如下几行说明恢复完成
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2013-09-02_23-14-28'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Copying '/backup/2013-09-02_23-14-28/ib_logfile1' to '/mydata/data'
innobackupex: Copying '/backup/2013-09-02_23-14-28/ib_logfile0' to '/mydata/data'
innobackupex: Finished copying back files.
130902 23:43:20innobackupex: completed OK!
# chown -R mysql.mysql /mydata/data/*
# service mysqld start
4.7、测试数据是否恢复成功
# mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| xiaodong |
+--------------------+
5 rows in set (0.00 sec)
mysql> use xiaodong;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_xiaodong |
+--------------------+
| tb1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from tb1;
+----+------------+
|>
+----+------------+
|1 | wu song |
|2 | song jiang |
+----+------------+
2 rows in set (0.01 sec)
mysql>
4.8、使用innobackupex进行增量备份
4.8.1、在数据库中新增加一个表
mysql> select database(); ##查看默认数据库
+------------+
| database() |
+------------+
| xiaodong |
+------------+
1 row in set (0.00 sec)
mysql>create table tb2 (id int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb2 (name) values ('yang guo'),('xiao long nv');
Query OK, 2 rows affected (0.01 sec)
Records: 2Duplicates: 0Warnings: 0
mysql> select * from tb2;
+----+--------------+
|>
+----+--------------+
|1 | yang guo |
|2 | xiao long nv |
+----+--------------+
2 rows in set (0.00 sec)
4.8.2、增量备份数据
# mkdir /backupadd #创建增量备份目录
# innobackupex --user=bkpuser --password=s3cret --incremental /backupadd/ --incremental-basedir=/backup/2013-09-02_23-14-28/
出现以下信息说明备份成功
innobackupex: Backup created in directory '/backupadd/2013-09-02_23-56-24'
innobackupex: MySQL binlog position: filename 'mysql-bin.000016', position 493
130902 23:56:28innobackupex: Connection to database server closed
130902 23:56:28innobackupex: completed OK!
注释：
--incremental指定增量备份的目录
--incremental-basedir指定基于哪个备份（完整备份或上一次增量备份）做增量备份。
注意：增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
如果数据库再次发生修改，我们依然需要在次增量备份，备份命令与第一次增量备份的命令相同，只不过此选项--incremental-basedir 需要指定上一次的增量备份的数据，而不是完全备份
4.9、此时我们继续修改数据库，但是没有做增量备份
# mysql
mysql> user xiaodong;
mysql> create table tb3 (id int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+--------------------+
| Tables_in_xiaodong |
+--------------------+
| tb1 |
| tb2 |
| tb3 |
+--------------------+
3 rows in set (0.00 sec)
mysql> drop table tb2;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table tb3;
Query OK, 0 rows affected (0.01 sec)
4.10、模拟此时数据库意外故障。
# sercie mysqld stop
# rm -rf /mydata/data/*
注意：由于我的二进制日志与数据目录没有在同一个目录下，所以直接删除了数据目录，如果在同一目录，切记一定要copy出来，不然数据就无法恢复。
4.11、实现数据恢复
4.11.1、准备(prepare)增量备份
增量备份与完全备份有着一些不同，尤其要注意的是：
(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
(2)基于所有的备份将未提交的事务进行“回滚”。
# innobackupex --apply-log --redo-only/backup/2013-09-02_23-14-28/#完整备份
出现以下信息说明成功
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
1309030:42:23InnoDB: Starting shutdown...
1309030:42:23InnoDB: Shutdown completed; log sequence number 1619980
130903 00:42:23innobackupex: completed OK!
# innobackupex --apply-log --redo-only/backup/2013-09-02_23-14-28/ --incremental-dir=/backupadd/2013-09-03_00-06-59/#增量备份
出现以下信息说明成功
innobackupex: Copying '/backupadd/2013-09-03_00-06-59/mysql/time_zone_leap_second.frm' to '/backup/2013-09-02_23-14-28/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/backupadd/2013-09-03_00-06-59/mysql/servers.frm' to '/backup/2013-09-02_23-14-28/mysql/servers.frm'
130903 00:43:12innobackupex: completed OK!
注释：
--redo-only只是事物提交，不回滚事物
--incremental-dir指定增量备份的文件
如果有多个增量备份，只需要修改--incremental-dir后面的值就可以。
4.11.2、查看增量备份是否准备完成
# cat /backup/2013-09-02_23-14-28/xtrabackup_checkpoints #完整备份
backup_type = full-prepared
from_lsn = 0
to_lsn = 1622723
last_lsn = 1622723
compact = 0
# cat /backupadd/2013-09-03_00-06-59/xtrabackup_checkpoints #增量备份
backup_type = incremental
from_lsn = 1618177
to_lsn = 1622723
last_lsn = 1622723
compact = 0
#
增量备份中的to_lsn = 1622723 与完整备份中to_lsn = 1622723 值相等。说明准备完成
注释：
backup_type 指定备份类型
from_lsn 开始日志序列号
to_lsn 结束日志序列号
last_lsn 最后一个日志序列号
compact 是否开启压缩功能
4.11.3、查看二进制日志文件及至备份这一刻为止二进制日志事件的位置是否一致
# cat /backup/2013-09-02_23-14-28/xtrabackup_binlog_info #完整备份
mysql-bin.000016 493
# cat /backupadd/2013-09-03_00-06-59/xtrabackup_binlog_info #增量备份
mysql-bin.000016 493
#
4.11.4、恢复数据，并启动服务
# innobackupex --copy-back /backup/2013-09-02_23-14-28/
# chown -R mysql.mysql/mydata/data/*
# service mysqld start
#mysql
mysql> use xiaodong;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_xiaodong |
+--------------------+
| tb1 |
| tb2 |
+--------------------+
2 rows in set (0.00 sec)
由于我们是在增量备份之前创建的tb2，所以tb2被恢复了，但是我们后来将tb2删除了，此时我们需要二进制日志恢复后面的操作
4.11.5、查看完整备份中的二进制的位置信息
# cat /backup/2013-09-02_23-14-28/xtrabackup_binlog_info
mysql-bin.000016 493 #记录备份的位置
4.11.6、将二进制日志中还原点之后的数据导出
# mysqlbinlog --start-position 493 /mydata/mybinlog/mysql-bin.000016> /tmp/mysql-bin.sq 4.12、还原数据并验证
mysql> source /tmp/mysql-bin.sq
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
mysql> use xiaodong;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_xiaodong |
+--------------------+
| tb1 |
+--------------------+
1 row in set (0.00 sec)
mysql>
总结：
在数据恢复过程中，二进制日志起到的作用及其重要，在数据库安装的的时候，切记将二进制日志与数据文件分开存放，并实时的备份二进制日志。在数据的备份与还原中最难理解的也就是使用二进制日志来恢复数据库，只要确定日志还原点，那么恢复数据应该不是问题。欢迎博友们共同探讨数据的备份与恢复的相关话题，以确保将企业的损失降低到最低。。。。
页:
[1]