MySQL备份与恢复+策略方案选择

MySQL备份与恢复

数据库备份的分类

从物理与逻辑的角度

(1)物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机各份(热备份)。

• 冷备份:在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。
• 热备份:在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。

(2)逻辑备份:指对数据库逻辑组件(如表等数据库对象)的备份。

从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份。

(1)完全备份:每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些。

(2)差异备份:备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。

(3)增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

逻辑备份的特点是：直接生成SQL语句，在恢复的时候执行备份的SQL语句实现数据库数据的重现。

物理备份的特点是：拷贝相关数据文件。

这二种备份差异：逻辑备份备份、还原慢，但备份文件占用的空间小；物理备份备份还原快，备份文件占用空间大。

逻辑备份的主要工具：mysqldump，MySQL自带的备份命令，不需要额外安装。

物理备份工具主要有：xtrabackup，第三方工具，需要额外安装。

MySQL备份工具介绍

mysqldump

命令格式

MySQL自带的一个很好使用的数据库备份命令，语法如下：

mysqldump -u 用户名 -p 数据库名 > 备份文件名

**注意：**mysqldump全量备份不会备份系统库，即不会备份：information_schema、performance_schema、sys

实例说明

准备数据：

-- 查看备份前数据
[root@izbp1aosct92mcgcq5dhqpz home]# mysql -uroot -pWo123456. -e "use crm_db;show tables;select *from userinfo;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_crm_db |
+------------------+
| userinfo         |
+------------------+
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | zhangsan |   18 |
+----+----------+------+

-- 执行备份命令
[root@izbp1aosct92mcgcq5dhqpz home]# mysqldump -uroot -pWo123456. crm_db > /home/crm_db_bak.sql

-- 查看备份结果
[root@izbp1aosct92mcgcq5dhqpz home]# egrep -v "#|\*|--|^$" /home/crm_db_bak.sql
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `userinfo` WRITE;
INSERT INTO `userinfo` VALUES (1,'zhangsan',18);
UNLOCK TABLES;

参数说明

上面使用mysqldump简单的方式进行备份，mysqldump还有很多参数。

1、–default-character-set

该参数指定了备份的字符集，可解决备份乱码的问题。

[root@hadoop-slave1 temp]# mysqldump -uroot -pWo123456. --default-character-set=utf8 crm_db > /home/crm_db_bak.sql

2、-A, --all-databases

作用是备份所有的库，例如：

[root@hadoop-slave1 /]# mysqldump -uroot -pWo123456 -A > /home/all.sql

注意：不会备份系统库，即不会备份：information_schema、performance_schema、sys

3、–databases, -B

参数后面所有名字参量都被看作数据库名。

-B参数的作用是在备份中增加了：

• 创建数据库
• 连接数据库（use database）

[root@izbp1aosct92mcgcq5dhqpz home]# mysqldump -uroot -pWo123456. -B crm_db > /home/crm_db_bak.sql

-- 查看内容
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crm_db` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `crm_db`;

DROP TABLE IF EXISTS `userinfo`;

CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOCK TABLES `userinfo` WRITE;

INSERT INTO `userinfo` VALUES (1,'zhangsan',18);

UNLOCK TABLES;

可以看到这与之前的不同在于多了以下两条语句：

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crm_db`;      -- 创建数据库
USE `crm_db`;                                           -- 链接数据库

4、–compact

该参数可以优化备份文件大小，导出更少的输出信息。去掉注释和头尾等结构。

该参数适合测试时使用。

[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 --compact crm_db > /temp/crm_db_compat_bak_.sql

--查看完整的备份内容
[root@izbp1aosct92mcgcq5dhqpz home]# cat crm_db_compat_bak_.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `userinfo` VALUES (1,'zhangsan',18);

5、gzip

gzip可以对MySQL备份文件的内容进行压缩

[root@izbp1aosct92mcgcq5dhqpz home]# mysqldump -uroot -pWo123456. -B crm_db|gzip > /home/crm_db_bak_gzip.sql.gz

-- 可以查看普通备份和压缩后的文件大小比较：
[root@izbp1aosct92mcgcq5dhqpz home]# ll
-rw-r--r-- 1 root  root   766 5月  22 10:38 crm_db_bak_gzip.sql.gz
-rw-r--r-- 1 root  root  2056 5月  22 10:29 crm_db_bak.sql

6、-F, --flush-logs

用于刷新 binlog参数，在/etc/my.cnf配置文件中如果启用了log-bin就会在安装的数据目录中生成binlog日志信息。

在没有进行备份时，和binlog相关的文件有：

-rw-r----- 1 mysql mysql      154 5月  22 10:58 mysqlbin.000001
-rw-r----- 1 mysql mysql       18 5月  22 10:58 mysqlbin.index

使用-F参数进行备份：

[root@izbp1aosct92mcgcq5dhqpz mysql]# mysqldump -uroot -pWo123456. -A -B -F > /home/all.sql

-- 查看binlog相关文件
-rw-r----- 1 mysql mysql      200 5月  22 11:04 mysqlbin.000001
-rw-r----- 1 mysql mysql      200 5月  22 11:04 mysqlbin.000002
-rw-r----- 1 mysql mysql      154 5月  22 11:04 mysqlbin.000003
-rw-r----- 1 mysql mysql       54 5月  22 11:04 mysqlbin.index

binlog文件被刷新了，也就意味着如果进行数据恢复，只需要从mysql-bin.000003之后进行增量恢复，这个文件之前的已经进行了全量备份了（在all.sql文件中）。

这个参数主要就是切割binlog日志，记录备份的位置，防止恢复数据进行全量恢复而出错。

7、–master-data

这个参数也是记录binlog的位置，找到恢复的点，例如：

[root@izbp1aosct92mcgcq5dhqpz mysql]# mysqldump -uroot -pWo123456. --compact --master-data -B crm_db  > /home/crm_db.sql

-- 查看备份的文件内容
[root@izbp1aosct92mcgcq5dhqpz home]# cat crm_db.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000003', MASTER_LOG_POS=154;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crm_db` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `crm_db`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;