MySQL数据备份实践和整理

本文部分内容参考来了以下文章：
https://blog.csdn.net/helloxiaozhe/article/details/77680255
http://c.biancheng.net/view/1092.html

感谢这两位博主的分享

今天主要来整理下关于MySQL的安全备份问题。
最近遇到一个任务，需要对一台服务器上边的数据进行安全备份，为此我特地整理了常见的安全备份方案。
按照常见的备份手段，数据备份可以划分为热备份，冷备份，温备份。
下边我简单讲解下这几种备份的不同：

热备份：直接对运行中的数据做安全导出备份，但是没有做任何额外安全处理，有可能导出的数据具有部分不完整性。
冷备份：这种备份手段就是直接拷贝数据库的物理文件。注意不同版本的mysql数据文件格式可能不同。
温备份：直接对运行中的数据做安全导出备份，期间需要加一把读锁防止其余正在访问的线程影响当前备份数据的完整性。

从备份的数据角度来看，备份数据的格式又分为逻辑备份和裸文件备份，其实就是对应了sql信息和mysql的原始物理文件。
前者简单易懂，但是备份时间较长，后者的实现原理更为复杂，但是功能更为强大。

冷备份的优点：
简单好用，不需要额外导出sql，也不需要重新建立索引等数据。
冷备份的缺点：
由于原始文件里存储量相关的undo段，插入缓冲等信息，所以往往这类文件的体积都会比较大。

mysqldump的实战：
场景1，对单个数据库对指定某张表做热备份。

[root@izwz9ic9ggky8kub9x1ptuz mysql-dump]# mysqldump -uroot -p test-db02 user_money > backup_user_money.sql
Enter password: 
[root@izwz9ic9ggky8kub9x1ptuz mysql-dump]# ls
backup_user_money.sql

这种方式的备份主要是包含了建表的sql和一些数据插入的sql信息。

[root@izwz9ic9ggky8kub9x1ptuz mysql-dump]# cat ./backup_user_money.sql 
-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost    Database: test-db02
-- ------------------------------------------------------
-- Server version	5.7.24

/*!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 `user_money`
--

DROP TABLE IF EXISTS `user_money`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_money` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `money` double(6,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_money`
--

LOCK TABLES `user_money` WRITE;
/*!40000 ALTER TABLE `user_money` DISABLE KEYS */;
INSERT INTO `user_money` VALUES (1,'idea',995);
/*!40000 ALTER TABLE `user_money` 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 2020-04-18 15:53:26
[root@izwz9ic9ggky8kub9x1ptuz mysql-dump]# 

如果需要备份整个数据库的信息，那么可以执行下边这条命令：

[root@izwz9ic9ggky8kub9x1ptuz mysql-dump]# mysqldump -uroot -p mall > mall.sql
Enter password: 
[root@izwz9ic9ggky8kub9x1ptuz mysql-dump]# ls
mall.sql

关于导入导出的sql主要规律如上，剩下的都是参数指令的用法： 备份导出示例： 1、导出数据和表结构——将特定数据库特定表中的数据和表格结构和数据全部返回 ```sql mysqldump --u b_user -h 101.3.20.33 -p'H_password' -P3306 database_di up_subjects > 0101_0630_up_subjects.sql ``` 2、导出表结构却不导出表数据——只返回特定数据库特定表格的表格结构，不返回数据,添加“-d”命令参数

mysqldump --u  b_user -h 101.3.20.33 -p'H_password'  -P3306 -d database_di up_subjects > 0101_0630_up_subjects.sql

3、导出表结构和满足挑顶条件的表数据——只返回特定数据库中特定表的表格结构和满足特定条件的数据

mysqldump --u  b_user -h 101.3.20.33 -p'H_password'  -P3306 database_di up_subjects --where=" ctime>'2017-01-01' and ctime<'2017-06-30'" > 0101_0630_up_subjects.sql

4、导出数据却不导出表结构——只返回特定数据库中特定表格的数据，不返回表格结构，添加“-t”命令参数

mysqldump --u  b_user -h 101.3.20.33 -p'H_password' -t -P3306 database_di up_subjects  >0101_0630_up_subjects.sql

5、导出特定数据库的所有表格的表结构及其数据，添加“–databases ”命令参数

mysqldump  --u  b_user -h 101.3.20.33 -p'H_password' -P3306 --databases test  > all_database.sql

同理，如果需要导入数据的话，可以使用下边这命令： 格式：mysql -h链接ip -P(大写)端口 -u用户名 -p密码 数据库名 < d:XX.sql(路劲) ```sql mysql -uusername -ppassword db1

更多详细信息可以参考这篇文章内容：https://blog.csdn.net/helloxiaozhe/article/details/77680255

编写定时任务crontab的脚本
关于定时任务脚本的编写这里使用的是linux自带的crond提供的功能来实现。
这里我先介绍下什么是crond
crond是linux系统内部自带的一个用于周期性执行定时任务的守护线程。
如何设置crond线程的启动和关闭

[root@localhost ~]# service crond restart
停止 crond： [确定]
正在启动 crond： [确定]
#重新启动crond服务
[root@localhost ~]# chkconfig crond on
#设定crond服务为开机自启动

其实crontab的编写并不复杂，只需要输入crontab -e指令，然后便会有个类似vi的编辑文本框开启供程序员输入相关的定时脚本。

[root@localhost ！]# crontab -e
#进入 crontab 编辑界面。会打开Vim编辑你的任务
* * * * * 执行的任务

这里的五个*参数的含义分别如下所示：

第一个"*"	一小时当中的第几分钟（minute）	0~59
第二个"*"	一天当中的第几小时（hour）	0~23
第三个"*"	一个月当中的第几天（day）	1~31
第四个"*"	一年当中的第几个月（month）	1~12
第五个"*"	一周当中的星期几（week）	0~7（0和7都代表星期日）

关于crontab的更多相关信息可以参考这篇文章：http://c.biancheng.net/view/1092.html

下边是我写的一个每天凌晨进行sql备份的脚本内容：
首先是crontab的内容，非常简答的一段命令，并且将定时任务输出到了日志里面

30 21 * * * /data/mysql-dump/script/backupShell.sh >> /data/mysql-dump/script/backup.log

最后是备份的脚本内容：

echo "$(date) now is begin to backup"
backup_dir="/data/mysql_dump"
time=$(date +%Y%m%d)
mysqldump -uroot -p'数据库密码'  idea-blog > idea-blog-$time.sql
echo $backup_dir/idea-blog-$time.sql

通过这种方式来进行数据库备份。

当然由于笔者只有一台服务器做测试，所以单纯只是备份在了本机上，这样的做法有一定的单点故障风险，所以其实并不是特别推荐，如果有条件的话，可以做成多机备份的方式。目前市面上也有很多成熟的备份方案，不仅仅只限于逻辑数据文件的备份，还有原始文件的备份。这里我列举几项常见的方案：
方案一：mysqldump+binlog: （ 推荐）
完全备份，通过备份二进制日志实现增量备份
方案二: xtrabackup:
对InnoDB：热备，支持完全备份和增量备份
对MyISAM：温备，只支持完全备份
方案三： lvm2快照+binlog：
几乎热备，物理备份