#####################################
常见的数据备份类型和备份工具
mysqldump备份工具
使用LVM逻辑卷快照备份
#####################################
常见的数据备份类型和备份工具
数据的重要性我就不多说了……，常见的数据备份类型是根据不同的备份性质和要求来划分的，他们分别是：根据业务是否可在线、根据物理和逻辑、根据备份范围，下面就来简单的扫盲一下：
根据业务是否可在线
热备份：读写操作不受影响
温备份：仅可以执行读操作
冷备份：离线备份，读写操作均不能进行
根据物理和逻辑
物理备份：直接复制数据文件，速度快(在文件系统级别完成，不需要mysql进程参与)，移植性差。
逻辑备份：将数据导出至文本文件中，速度慢(需要mysql进程参数)，移植性好，丢失浮点数精度，可以方便使用文本处理工具。
根据备份范围
完全备份：备份全部数据
增量备份：仅备份上次完全备份或增量备份以后变化的数据(比增量备份多占据空间，但是还原的时候方便)
差异备份：仅备份上次完全备份以来变化的数据
这里特意盗用一幅图来更加直观的阐述增量备份和差异备份的区别：
完全+增量(还原时间长，复杂，占用空间小)
完全+差异(还原时间短，简单，占用空间大)
他们之间没有约束，比如 完全在线物理备份……
另外备份出的数据要经常测试是否可用，和制定好紧急还原方案………
备份的对象有数据、配置文件、二进制日志、事务日志等……
各个备份工具功能和性能对比如下图所示：
mysqldump备份工具
备份策略：周完全+每日增量(二进制日志)
完全备份
mysql> create database soulboy;
Query OK, 1 row affected (0.00 sec)
mysql> use soulboy;
Database changed
mysql> create table tb1 (id int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into tb1 (name) values ('kobe');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from tb1; #新库
+----+------+
| id | name |
+----+------+
| 1 | kobe |
+----+------+
1 row in set (0.00 sec)
完全备份，比例中表类型是InnoDB
mysqldump -uroot -p --master-data=2 --single-transaction --all-databases > /backup/alldatabases.sql
###########################补充说明###############################
mysqldum只能对MyISAM引擎做温备份，备份之前必须锁定表,如下：
mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/alldatabases.sql
##########################mysqldump选项###########################
-u #指定用户名
-p #指定用户密码
-h #指定主机地址
-A|--all-databases #备份所有数据库
--databases #备份指定数据库
--single-transcation #基于此项可以实现对InnoDB表做热备份，但不需要使用
--lock-all-tables #执行备份时为所有表请求加锁
-E|--events #备份事件调度器代码
--opt #同时启动各种高级选项
-R|--routines #备份存储过程和存储函数
--flush-logs #备份之前刷新日志
--triggers #备份触发器
--master-data=2 #该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将会输出CHANGE第一天
mysql> use soulboy;
Database changed
mysql> insert into tb1 (name) values ('wade');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | kobe |
| 2 | wade |
+----+------+
2 rows in set (0.00 sec)
####滚动二进制日志，进入第二天
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
####备份第一天二进制日志为SQL格式并重命名为firstdays.sql
mysqlbinlog /mydata/data/mysql-bin.000008 > /backup/firstday.sql第二天
#####插入新数据
mysql> insert into tb1 (name) values ('nash');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | kobe |
| 2 | wade |
| 3 | nash |
+----+------+
3 rows in set (0.00 sec)
#####备份第二天二进制日志为SQL格式并重命名为secondday.sql
#####注意生产环境中务必将二进制日志存储在不同于数据目录的设备中mysqlbinlog /mydata/data/mysql-bin.000009 > /backup/secondday.sql模拟故障
cd /mydata/data #####删除数据目录中所有文件 rm -rf * cd /usr/local/mysql #####初始化数据库 scripts/mysql_install_db --user=myslq --datadir=/mydata/data/ #####杀死僵死进程 ps aux | grep mysql kill pid service mysqld restart
数据还原
#####查看当前使用的二进制日志文件以及所处位置 show master status; #####禁止还原过程中记录二进制日志 mysql> set sql_log_bin=0; #####还原完全备份 mysql> source /backup/alldatabases.sql; #####还原第一天备份 mysql> source /backup/firstday.sql; #####还原第二天备份，全部数据完全到此结束 mysql> source /backup/secondday.sql; #####开启二进制日志记录功能 mysql> set sql_log_bin=1; #####测试数据是否还原成功 mysql> use soulboy; mysql> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | kobe | | 2 | wade | | 3 | nash | +----+------+ 3 rows in set (0.00 sec)
使用LVM逻辑卷快照备份
锁表并滚动日志
#####锁表 mysql> FLUSH TABLES WITH READ LOCK; #####滚动二进制日志文件 mysql> FLUSH LOGS; #####即时点恢复时候需要使用，记录LVM时刻二进制日志文件和所处位置 mysql -e 'SHOW MASTER STATUSG' > /backup/master-`date +%F`.info
数据目录和事务文件创建LVM快照
#####对mydata目录进行快照 lvcreate -L 200M -s -p r -n mydata-snap /dev/myvg/mydata #####以只读方式挂载镜像文件 mount /dev/myvg/mydata-snap /mnt -o ro #####将镜像文件内容复制到/save目录中 mkdir /save cp -a /mnt/data/* /save/ #####删除镜像文件 umount /mnt lvremove --force /dev/myvg/mydata-snap #####删除二进制日志文件 rm -rf /save/mysql-bin.*
解锁并插入数据
#####解锁
mysql> UNLOCK TABLES;
#####查看当前表内容
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | kobe |
| 2 | wade |
| 3 | nash |
+----+------+
3 rows in set (0.00 sec)
#####插入新数据，模拟第一天
insert into tb1 (name) values ('firstday');
#####滚动二进制日志文件，模拟第二天
flush logs;
insert into tb1 (name) values ('sencondday');查看快照时间和当前二进制日志信息
#####查看快照时刻的二进制日志记录信息 cat /backup/master-2013-09-19.info *************************** 1. row ***************************File: mysql-bin.000005Position: 107Binlog_Do_DB: Binlog_Ignore_DB: #####查看使用当前二进制日志和所处位置 mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 343 | | | +------------------+----------+--------------+------------------+
基于多个二进制日志生成单个SQL格式文件
#####查看快照时刻所处位置的时间点 [root@localhost save]# mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000005 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #130919 11:46:29 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 130919 11:46:29 #时间 #####基于时间做限定来合并多个日志文件为一个SQL格式的文件 mysqlbinlog --start-datetime='2013-09-19 11:46:29' /mydata/data/mysql-bin.000005 /mydata/data/mysql-bin.000006 > /backup/incremental-`date +%F`.sql
模拟故障
#####停止服务 service mysqld stop #####删除数目目录下所有文件 rm -rf /mydata/data/*
还原快照时刻数据
#####复制快照时刻数据到数据目录 cp -a /save/* /mydata/data/ #####启动服务 service mysqld start #####查看当前表内数据 mysql> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | kobe | | 2 | wade | | 3 | nash | +----+------+
还原增量数据
#####关闭二进制日志记录功能 mysql> set sql_log_bin=0; #####还原增量备份的数据 mysql> source /backup/incremental-2013-09-19.sql #####开启二进制日志记录功能 mysql> set sql_log_bin=0; #####查看表内数据，还原成功 mysql> select * from tb1; +----+------------+ | id | name | +----+------------+ | 1 | kobe | | 2 | wade | | 3 | nash | | 4 | firstday | | 5 | sencondday | +----+------------+ 5 rows in set (0.00 sec)