【mysql备份】01、mysql备份和恢复基础
一、mysql备份和恢复概述1、为什么要备份
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种：
硬件故障
软件故障
自然灾害
******
测试：服务器/硬件升级时，
审计：分析过去某一时刻的数据
误操作 (占比最大)
备份的目的是用于恢复，对备份好的数据有必要做周期性的恢复测试
为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略：
[*] 能够容忍丢失多少数据
[*] 恢复数据需要多长时间
[*] 需要恢复哪一些数据
2、数据的备份类型
根据备份时，数据库服务器是否在线：
热备份：hot back；在线备份，读写操作不受影响
# 最复杂，只有基于事务的存储引擎才支持热备；需要使用专门的工具才能实现
温备份：在线备份，读操作可继续进行，但写操作不允许；
冷备份：cold backup；离线备份，数据库服务器离线，备份期间不能为业务提供读写服务；
根据备份数据的数据集：
完全备份：full backup
备份整个数据集( 即某个完整个库 )、
部分备份：partial backup
备份部分数据集(例如: 只备份一个或多个表)
根据备份时是否备份整个数据还是仅备份变化的数据：
增量备份：incremental backup
备份自上一次备份以来(增量或完全)以来变化的数据;
特点: 节约空间、还原麻烦
差异备份：differential backup
备份自上一次完全备份以来变化的数据
特点: 浪费空间、还原比增量备份简单
根据备份时所使用接口：直接备份数据文件还是通过mysql服务器将数据导出的方式，将备份分为：
物理备份：physical backup
通过直接复制数据文件进行的备份；
一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果；
恢复时直接将数据文件存放在数据目录就可以（要兼容：存储引擎要一样）
优点：
速度快
缺点：
跨平台能力没有逻辑备份好
逻辑备份：logical backup
通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度（数据从二进制转换为文本））
如：mysqldump，mydumper，phpMyAdmin
优点：
与存储引擎无关
备份的数据文件方便使用文本工具2次加工
恢复过程简单
可以使用网络来备份和恢复
mysqldump实现备份时语句灵活
缺点：
由于存储为文本数据，备份文件比原文件大，压缩可避免此问题
无法保证还原后数据与原数据一模一样
Schema和数据存储一起、巨大的SQL语句、单个巨大的备份文件
索引需要重建（大的数据库需要大量的时间和CPU资源）
注意：
数据量大时（超过10G），不要使用逻辑备份（太麻烦）；
3、制定备份策略时需要考虑的因素
选择备份方式
选择备份时间
考虑恢复成本：
恢复时长
备份成本：
持锁时长
备份时长
备份负载
能够容忍丢失的数据量
备份的对象：
一般情况下, 我们需要备份的数据分为以下几种：
数据
额外的数据（二进制日志、InnoDB事务日志）
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件
4、常用的备份工具
mysqldump：
逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备；
很难实现差异或增量备份；
单线程，性能较差，备份和恢复过程较慢，
mysqldumper：
percona公司提供的多线程的mysqldump，提升速度
cp, tar等归档复制工具:
物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
使用直接拷贝数据库文件的方式进行打包备份，需要注意的是执行步骤：锁表、备份、解表。这样才可以保证在复制期间数据库的数据不会发生变化，如果在复制数据库的过程中还有数据写入，就会造成数据不一致，备份的数据就无法使用。恢复也很简单，直接拷贝到之前的数据库文件的存放目录即可。
lvm-snapshot:
物理备份；几乎热备：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁
使用cp,tar等工具进行物理备份，备份和恢复速度较快
很难实现增量备份，并且请求全局锁需要等待一段时间，在繁忙的服务器上尤其如此；
SELECT clause INTO OUTFILE '/path/to/somefile' 备份部分数据，保存的文件中不是sql脚本，恢复时需要使用：LOAD DATA INFILE '/path/to/somfile'
部分备份工具，不会备份关系定义，仅备份表中的数据
逻辑部分，速度快于mysqldump
innobackup：商业备份工具
InnoDB存储引擎支持热备，增量备份
MyISAM温备，完全备份
物理备份，速度快
xtrabackup：由percona提供的开源备份
InnoDB存储引擎支持热备，增量备份
MyISAM温备，完全备份
物理备份，速度快
mysqlhotcopy:
逻辑备份；吹牛工具，名不副实，几乎冷备，仅支持MyISAM存储引擎
5、备份策略
不同的场景下, 我们可以制定不同的备份方案对数据库进行备份，常用的方案为：
直接cp，tar复制数据库文件
数据量小（5G以下）
mysqldump + 复制BIN LOGS
数据量较小（10G以下）；对数据库进行完全备份，然后定期备份BINARY LOG达到增量备份的效果
lvm2快照 + 复制BIN LOGS
数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
xtrabackup
如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
二、实战
1、使用cp、tar进行备份
查看数据库信息：
MariaDB > SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB > USE mydb
Database changed
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1 |
| zy |
+----------------+
2 rows in set (0.01 sec)
MariaDB > SHOW TABLE STATUS;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| t1 | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2017-02-22 16:22:17 | 2017-02-22 16:22:17 | NULL | gbk_chinese_ci | NULL | | |
| zy | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2017-02-22 16:17:46 | NULL | NULL | gbk_chinese_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
2 rows in set (0.00 sec)
MariaDB > SELECT * FROM t1;
Empty set (0.00 sec)
MariaDB > SELECT * FROM zy;
+-----+--------------+-------+--------+-------------------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+-------------------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
| 2 | Huang RONG | NULL| F | Chilian shenzhang | 19 |
| 3 | xj1 | ABC1| M | XX1 |127 |
| 4 | xj | ABC | M | XX | 28 |
+-----+--------------+-------+--------+-------------------+------+
4 rows in set (0.00 sec)
MariaDB > SELECT * FROM t1;
+------+------+
| ID | Age|
+------+------+
| 1 | xie|
+------+------+
1 row in set (0.00 sec)
向数据库施加全局读锁：
MariaDB > FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.05 sec)
备份数据文件：
# ls
aria_log.00000001ib_logfile0 mydb mysql-bin.000002mysql-bin.000005mysql-bin.000008Node5.pid
aria_log_control ib_logfile1 mysql mysql-bin.000003mysql-bin.000006mysql-bin.index performance_schema
ibdata1 multi-master.infomysql-bin.000001mysql-bin.000004mysql-bin.000007Node5.err test
### 在生产环境中把日志文件和数据文件分开存放
# tar czf /tmp/`date +%Y:%m:%d-%T`.tar.gz *
解除锁，并模拟误操作删除数据：
MariaDB > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
MariaDB > DROP DATABASE mydb;
Query OK, 2 rows affected (0.16 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
恢复数据：
# ls 2017\:02\:22-17\:03\:13.tar.gz
2017:02:22-17:03:13.tar.gz
# tar --force-local -xf 2017\:02\:22-17\:03\:13.tar.gz -C src
### 文件名中有冒号，用tar解压需要加--force-local选项
# cp -a /tmp/src/mydb .
### 只恢复删除的库文件数据
重新连接并查看：
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> USE mydb
Database changed
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1 |
| zy |
+----------------+
2 rows in set (0.00 sec)
MariaDB > SHOW TABLE STATUS;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+-----------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+-----------------------------------------+
| t1 | MyISAM | 10 | Dynamic | 1 | 20 | 20 | 281474976710655 | 1024 | 0 | NULL | 2017-02-22 16:22:17 | 2017-02-22 16:26:35 | NULL | gbk_chinese_ci | NULL | | |
| zy | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Table 'mydb.zy' doesn't exist in engine |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+-----------------------------------------+
2 rows in set, 1 warning (0.00 sec)
MariaDB > SELECT * FROM t1;
+------+------+
| ID | Age|
+------+------+
| 1 | xie|
+------+------+
1 row in set (0.00 sec)
MariaDB > SELECT * FROM zy;
ERROR 1932 (42S02): Table 'mydb.zy' doesn't exist in engine
如果你从my.cnf文件删除innodb_file_per_table行，并重启服务器，InnoDB在共享的表空间文件里再次创建表。
innodb_file_per_table只影响表的创建。如果你用这个选项启动服务器，新表被用.ibd文件来创建，但是你仍旧能访问在共享表空间里的表。如果你删掉这个选项，新表在共享表空间内创建，但你仍旧可以访问任何用多表空间创建的表。
InnoDB总是需要共享标空间。.ibd文件对InnoDB不足以去运行，共享表空间包含熟悉的ibdata文件，InnoDB把内部数据字典和未作日志放在这个文件中。
注释：你不能像对MyISAM一样，在数据目录之间随意地移动.ibd文件。这是因为表定义被存在InnoDB共享表空间内，而且InnoDB必须保持事务ID和日志顺序号的一致性。
再复制之前备份好的事务日志和inodb共享的表空间文件到数据目录下：
# cp -a /tmp/src/ib_logfile0 .
cp: overwrite `./ib_logfile0'? y
# cp -a /tmp/src/ib_logfile1 .
cp: overwrite `./ib_logfile1'? y
# cp -a /tmp/src/ibdata1 .
cp: overwrite `./ibdata1'? y
MariaDB [(none)]> select * from mydb.zy; # 成功恢复了
+-----+--------------+-------+--------+-------------------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+-------------------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
| 2 | Huang RONG | NULL| F | Chilian shenzhang | 19 |
| 3 | xj1 | ABC1| M | XX1 |127 |
| 4 | xj | ABC | M | XX | 28 |
+-----+--------------+-------+--------+-------------------+------+
4 rows in set (0.01 sec)
MariaDB [(none)]>
使用cp或tar备份时建议复制所有的数据目录下的文件，以防万一。
2、使用mysqldump备份
mysqldump: 客户端工具，通过mysql协议连接至mysqld；
使用格式：
mysqldump db_name
# 不指定表时，默认会备份该库下所有表，恢复时需要指定恢复至哪个库中
mysqldump --databases db_name ...
# 备份指定的多个库，恢复不需要手动创建数据库
mysqldump --all-databases
# 备份所有库，恢复不需要手动创建数据库
常用的选项：
-A, --all-databases：备份所有库#自动创建库
-B, --databases db_name1 db_name2 ...： 备份指定的数据库 #自动创建库
-x, --lock-all-tables：锁定所有表，做温备
--single-transaction：启动一个大的单一事务能够实现对innodb表热备，使用了这个选项就不要再使用-x选项了。
-C, --compress：压缩传输；
其它选项：
-E, --events：备份指定库的事件调度器；
-R, --routines：备份存储过程和存储函数；
--triggers：备份触发器
--master-data[=#]：
主从复制架构中，复制时的同步位置标记：
#为0时，表示不记录
#为1时：记录CHANGE MASTER TO语句不被注释；
#为2时：记录为注释的CHANGE MASTER TO语句；
-F，--flush-logs：锁定表之后执行flush logs命令，滚动二进制日志文件，备份时常用的选项，
注意：备份时要加锁
实例：
查看数据库信息：
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use mydb
Database changed
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1 |
| zy |
+----------------+
2 rows in set (0.00 sec)
MariaDB > SELECT * FROM t1;
+------+------+
| ID | Age|
+------+------+
| 1 | xie|
+------+------+
1 row in set (0.00 sec)
MariaDB >
使用mysqldump备份单个表：
# mysqldump -p123 -x mydb t1
### 没有设置重定向时，会直接将SQL语句输出在屏幕上
-- MySQL dump 10.16Distrib 10.1.21-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: localhost
-- ------------------------------------------------------
-- Server version10.1.21-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`ID` int(11) DEFAULT NULL,
`Age` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'xie');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-02-22 21:43:09
# mysqldump -p123 -x mydb t1 > /tmp/t1.sql
# ls /tmp/t1.sql
/tmp/t1.sql
### 可以使用vim 进行二次编辑，但是如果是几个G的文件千万别使用vim打开
# vim /tmp/t1.sql
模拟误操作删除数据：
MariaDB > DELETE FROM t1;
Query OK, 1 row affected (0.00 sec)
MariaDB > SELECT * FROM t1;
Empty set (0.00 sec)
MariaDB > DESC t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES| | NULL | |
| Age | varchar(20) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB >
还原，并查看：
# mysql -p123< /tmp/t1.sql
ERROR 1046 (3D000) at line 22: No database selected
### 注意，还原时是使用mysql而不是mysqldump并需要指定还原至哪个库中
# mysql -p123mydb < /tmp/t1.sql
MariaDB [(none)]> SELECT * FROM mydb.t1;
+------+------+
| ID | Age|
+------+------+
| 1 | xie|
+------+------+
1 row in set (0.00 sec)
备份所有库：
###备份所有库
# mysqldump -p123 -x -A > /tmp/all.sql
### 误操作删除库、表、表中数据
MariaDB > drop table t1;
Query OK, 0 rows affected (0.00 sec)
MariaDB > SELECT * from zy;
+-----+--------------+-------+--------+-------------------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+-------------------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
| 2 | Huang RONG | NULL| F | Chilian shenzhang | 19 |
| 3 | xj1 | ABC1| M | XX1 |127 |
| 4 | xj | ABC | M | XX | 28 |
+-----+--------------+-------+--------+-------------------+------+
4 rows in set (0.00 sec)
MariaDB > DELETE FROM zy WHERE Name="xj";
Query OK, 1 row affected (0.01 sec)
MariaDB > drop database test;
Query OK, 1 row affected (0.01 sec)
MariaDB > SELECT * from zy;
+-----+--------------+-------+--------+-------------------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+-------------------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
| 2 | Huang RONG | NULL| F | Chilian shenzhang | 19 |
| 3 | xj1 | ABC1| M | XX1 |127 |
+-----+--------------+-------+--------+-------------------+------+
3 rows in set (0.00 sec)
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| zy |
+----------------+
1 row in set (0.00 sec)
### 恢复数据
# mysql -p123 < /tmp/all.sql
### 查看
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use mydb
Database changed
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1 |
| zy |
+----------------+
2 rows in set (0.00 sec)
MariaDB > SELECT * FROM T1;
ERROR 1146 (42S02): Table 'mydb.T1' doesn't exist
MariaDB > SELECT * FROM t1;
+------+------+
| ID | Age|
+------+------+
| 1 | xie|
+------+------+
1 row in set (0.00 sec)
MariaDB > SELECT * FROM zy;
+-----+--------------+-------+--------+-------------------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+-------------------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
| 2 | Huang RONG | NULL| F | Chilian shenzhang | 19 |
| 3 | xj1 | ABC1| M | XX1 |127 |
| 4 | xj | ABC | M | XX | 28 |
+-----+--------------+-------+--------+-------------------+------+
4 rows in set (0.00 sec)
MariaDB >
备份1个或多个库：
# mysqldump -p123 -x--databases mydb test > /tmp/2.sql
# mysql -p123 < /tmp/2.sql
2）InnoDB热备
上面都是mysqldump温备，对myisam和innodb存储引擎都适用，现在只针对innodb的热备
--single-transaction：启动一个大的单一事务能够实现对innodb表热备，使用了这个选项就不要再使用-x选项了。
# mysqldump -p123 --single-transaction mydb > /tmp/w.sql 总结mysqldump的用法：
备份:
请求锁：-x 温备或使用--singe-transaction进行innodb表热备
滚动日志：-F
选定要备份的库：-B，-A
记录二进制日志文件及位置：--master-data={1|2}
恢复：
建议关闭二进制日志，关闭其它用户连接
完全备份 + 各二进制日志文件中至此刻的事件
mysqldump + 复制BIN LOGS 实现即时点恢复：
1）查看数据库信息
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 366 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2）使用mysqldump做完全备份
# mysqldump -p123 -x -A -F --master-data=2 > /tmp/bf/all.sql 3）模拟操作
MariaDB > CREATE TABLE test(Name varchar(20));
Query OK, 0 rows affected (0.01 sec)
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| test |
+----------------+
1 row in set (0.01 sec)
MariaDB > DROP DATABASE mydb; # 误操作
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
这里我们要恢复test表的数据时，但完全备份的.sql脚本里只有mydb数据库，而没有test表，这里就需要使用二进制日志来实现即时点还原了
4）备份二进制日志（可以看作是增量备份）
### 将服务器离线，不能再运行有用户连上来进行读写操作，备份从完全备份开始的位置点到误操作之前的二进制日志
## 先查看完全备份的.sql脚本，查看备份后开始的位置点和二进制日志文件，找到误操作时的位置点
# vim /tmp/bf/all.sql
### 找到
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=366;
# mysqlbinlog --start-position=366mysql-bin.000004
##### 找到
# at 502
#170223 21:12:42 server id 1end_log_pos 540 GTID 0-1-3 ddl
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
# at 540
#170223 21:12:42 server id 1end_log_pos 621 Querythread_id=4exec_time=0error_code=0
SET TIMESTAMP=1487855562/*!*/;
DROP DATABASE mydb
/*!*/;
### 可以看到事件位置540-621执行了误操作，那我们备份从完全备份后开始的位置点366到误操作上一个位置点540段的二进制日志就可以了。
# mysqlbinlog --start-position=366 --stop-position=540 mysql-bin.000004 > /tmp/zl.sql
5）恢复数据
### 暂时关闭二进制日志；将恢复操作不记录二进制
MariaDB [(none)]> SET SESSION sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
### 导入完全备份
MariaDB [(none)]> source /tmp/bf/all.sql
### 查看数据
MariaDB > SHOW DATABASES; # 使用完全备份的.sql脚本已经恢复了mydb库，但表还没有恢复
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB > use mydb;
Database changed
MariaDB > SHOW TABLES;
Empty set (0.00 sec)
MariaDB > SHOW MASTER STATUS; # 二进制日志没有增加
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 621 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB > SOURCE /tmp/zl.sql # 再导入备份的二进制脚本
MariaDB > SHOW TABLES; # 数据就完全恢复了
+----------------+
| Tables_in_mydb |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
MariaDB > SET session sql_log_bin=1;# 开启二进制日志，不开启也没关系，这个设置仅对当前会话有效，退出就好了
Query OK, 0 rows affected (0.00 sec)
3、使用LVM的快照实现几乎热备
lvm快照的知识点可以查看：http://xiexiaojun.blog.51cto.com/2305291/1881003
1）请求锁定所有表
mysql> flush tables with read lock;
2）记录二进制日志及事件位置
mysql> show master status;
3）穿件快照
lvcreate -L> 4）释放锁
mysql> unlock tables;
5）挂载快照卷，复制数据进行备份
cp，rsync，tar等命令
6）备份完成之后，删除快照卷
或者用现成的集成命令工具mylvmbackup(可以集成上面的命令集合，自动完成备份)
mylvmbackup --user=dba --password=xxx--mycnf=/etc/my.cnf --vgname=testvg--lvname=testlv --backuptype=tar --lvsize=100M --backupdir=/var/lib/backup
4、mylvmbackup
mylvmbackup 是可以用来快速备份 MySQL 数据文件的 Perl 脚本
项目地址：https://launchpad.net/
1）安装依赖包
yum install perl-TimeDate perl-Config-IniFiles perl-DBD-MySQL perl-File-Copy-Recursive perl-MIME-Lite 2）下载并安装mylvmbackup
wget
# rpm -ivh mylvmbackup-0.16-0.noarch.rpm
warning: mylvmbackup-0.16-0.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID b27291f2: NOKEY
Preparing... ###########################################
1:mylvmbackup ###########################################
# rpm -ql mylvmbackup
/etc/mylvmbackup.conf
/usr/bin/mylvmbackup
/usr/share/doc/mylvmbackup-0.16
/usr/share/doc/mylvmbackup-0.16/COPYING
/usr/share/doc/mylvmbackup-0.16/CREDITS
/usr/share/doc/mylvmbackup-0.16/ChangeLog
/usr/share/doc/mylvmbackup-0.16/INSTALL
/usr/share/doc/mylvmbackup-0.16/README
/usr/share/doc/mylvmbackup-0.16/TODO
/usr/share/man/man1/mylvmbackup.1.gz
/usr/share/mylvmbackup
/usr/share/mylvmbackup/backupfailure.pm
/usr/share/mylvmbackup/logerr.pm
/usr/share/mylvmbackup/preflush.pm
3）修改配置文件
vi /etc/mylvmbackup.conf
user=root
password=123.com
host=localhost
port=3306
socket=/tmp/mysql.sock
mycnf=/etc/my.cnf
vgname=myvg #卷组的名字
lvname=mylv #逻辑卷的名字
lvsize=5G #备份时创建的snapshot的大小
mountdir=/opt/snap_mnt #snapshot的mount位置
backupdir=/backup #备份打包后的存放位置
relpath=mysqldata #如果只是想备份逻辑卷下的某个目录的话，则要指出来
lvcreate=/usr/sbin/lvcreate
lvremove=/usr/sbin/lvremove
mount=/bin/mount
tar=/bin/tar
umount=/bin/umount
prefix=backup #备份打包文件的命名前缀
suffix=_mysql #定义备份文件名后缀
4）备份数据库
mylvmbackup
页:
[1]