Mysql备份相关

 

目录

数据备份

1.使用mysqldump命令备份

1.备份一个数据库

2.备份多个数据库

3.备份所有数据库

2.直接复制整个数据库目录

3.使用mysqlhotcopy工具快速备份

数据还原

1.还原使用mysqldump命令备份的数据库的语法 

2.还原直接复制目录的备份

完整备份&增量备份&差异备份

---

数据备份 

• 使用mysqldump命令备份

mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

• 备份一个数据库

mysqldump基本语法：

mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql

其中：

dbname参数表示数据库的名称； table1和table2参数表示需要备份的表的名称，为空则整个数据库备份； BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件；

backup1.sql脚本如下：

-- MySQL dump 10.13  Distrib 8.0.14, for Win64 (x86_64)
--
-- Host: localhost    Database: polls
-- ------------------------------------------------------
-- Server version	8.0.14

/*!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 */;
 SET NAMES utf8mb4 ;
/*!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 `polls_question`
--

DROP TABLE IF EXISTS `polls_question`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `polls_question` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_text` varchar(200) NOT NULL,
  `pub_date` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `polls_question`
--

LOCK TABLES `polls_question` WRITE;
/*!40000 ALTER TABLE `polls_question` DISABLE KEYS */;
INSERT INTO `polls_question` VALUES (1,'What\'s new?','2019-02-14 06:15:11.155733'),(2,'What\'s it','2019-02-14 16:25:01.000000');
/*!40000 ALTER TABLE `polls_question` 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 2019-04-29 20:41:19

 

文件的开头会记录MySQL的版本、备份的主机名和数据库名。

文件中以“--”开头的都是SQL语言的注释，以"/*!40101"等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比4.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就会被当做注释。其他类似。 

 语法：

• 备份多个数据库

mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

加上了--databases选项，然后后面跟多个数据库

mysqldump -u root -p --databases test mysql > D:\backup.sql 

• 备份所有数据库

mysqldump命令备份所有数据库的语法如下：

mysqldump -u username -p -all-databases > BackupName.sql

　　示例：

mysqldump -u -root -p -all-databases > D:\all.sql

 

• 直接复制整个数据库目录

MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。

注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。

• 使用mysqlhotcopy工具快速备份

一看名字就知道是热备份。因此,mysqlhotcopy支持不停止MySQL服务器备份。而且，mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用。其使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。

原理：先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。

命令格式如下：

[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 backupDir/

• dbname：数据库名称；
• backupDir：备份到哪个文件夹下；

　　常用选项：

• --help：查看mysqlhotcopy帮助；
• --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件加上_old；
• --keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧的文件更名；
• --flushlog：本次辈分之后，将对数据库的更新记录到日志中；
• --noindices：只备份数据文件，不备份索引文件；
• --user=用户名：用来指定用户名，可以用-u代替；
• --password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p之间没有空格；
• --port=端口号：用来指定访问端口，可以用-P代替；
• --socket=socket文件：用来指定socket文件，可以用-S代替；

　　mysqlhotcopy并非mysql自带，需要安装Perl的数据库接口包；下载地址为:http://dev.mysql.com/downloads/dbi.html

　　目前，该工具也仅仅能够备份MyISAM类型的表。

 

数据还原

 

• 还原使用mysqldump命令备份的数据库的语法 

mysql -u root -p [dbname] < backup.sq

示例：

mysql -u root -p polls< C:\backup.sql

通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制

 

完整备份&增量备份&差异备份

 

• 还原直接复制目录的备份

• 完整备份：每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较     

特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

• 增量备份：在第一次完整备份之后，第二次开始每次都将添加了存档属性的文件进行备份，并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢？这就是为了下一次备份的时候判断是否有文件变化，因为用户在每次备份以后修改这些被清除存档属性的文件，存档属性就会自动加上，相当于用户告诉系统，这些文件有变化，你下一次就备份这些文件，其他没有存档属性的就不需要备份，这就是增量备份的工作机制。

（相当于机器人把地板打扫干净了，你踩过，就会有脚印（增加标记），下次机器人就把脚 印记录下来，并且把脚印打扫干净（清除标记），始终保持地板干净。机器人每次记录并打 扫的脚印就相当于每次增量备份的内容）

特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长。

• 差异备份：在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。因此，差异备份在备份完毕之后不需要清除文件的存档属性，因为这些文件和下一次备份没有什么关系，它仅仅和第一次完整备份的数据进行比较（第一次完整备份之后是清除存档属性的）。

（相当于第一次机器人把地板打扫干净了，你踩过，就会有脚印，机器人就把脚印记录下来，但不打扫，下次你又有踩脏的，机器人就把你这几次所有踩脏的地方都记录下来，始终不打扫，每次都这样。机器人每次记录的内容就相当于差异备份的内容）

特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

　　

简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。

　　

• 举例：

　　假设指定备份目录周一包含A、B、C三个文件。周一的时候，做了完整备份。周二新增了D文件，同时A文件发生变化，变成A1文件；周三新增了E文件，同时A文件变成了A2，B文件变成B1；周四新增了F文件，A2变成了A3，D变成了D1，同时删掉了C文件。

　　

　　不同备份方式下的备份情况（假设每天做一次备份）：

　　

       完整备份：周一，备份ABC三个文件；周二，备份A1、B、C、D四个文件；周三，备份A2、B1、C、D、E五个文件；

　　周四，备份A3、B1、D1、E、F五个文件。

　　

　　增量备份：周一，备份ABC三个文件，完整备份；周二，备份与周一相比有变化的文件，即备份A1和D两个文件；周三，备份与之前所有版本相比有变化的文件，即备份A2，B1，E三个文件；周四，备份与之前所有版本相比有变化的文件，即备份A3，D1，F三个文件并删除C。

　　

　　差异备份：周一，备份ABC三个文件，完整备份；周二，备份与周一相比有变化的文件，即备份A1和D两个文件；周三，备份与周一相比有变化的文件，即备份A2、B1、D、E四个文件；周四，备份与周一相比有变化的文件，即备份A3、B1、D1、E、F五个文件并删除C。