MySQL-06 数据备份和恢复

学习目标

• 数据备份
• 数据恢复
• 数据库迁移
• 导入和导出

 

数据备份

系统意外崩溃或者服务器硬件损坏都有可能导致数据库丢失，因此生产环境中数据备份非常重要。

 

MySQLdump命令备份

该命令可以将数据库备份成一个文件文件，该文件包含了数据定义语句和数据操纵语句，可以执行重新创建数据库表并插入数据。

语法格式：

mysqldump -u User -h Host -pPassword DBname[TableName,[TableName...]] > [dump file.sql]　　

示例：备份myschool数据库

备份文本文件：

--
-- Dumping data for table `subject`
--

LOCK TABLES `subject` WRITE;
/*!40000 ALTER TABLE `subject` DISABLE KEYS */;
INSERT INTO `subject` VALUES (1,'数据库基础',1,20),(2,'Java逻辑基础',1,18),(3,'SQL基础',1,15),(4,'HTML',1,20),(5,'Control+Ajax',2,20),
(6,'项目管理',4,40),(7,'软件工程',3,28),(8,'算法与数据结构',2,15),(9,'C语言',1,25),(10,'*理论',1,18),(11,'C#面向对象',2,16),
(12,'JavaWeb',3,40),(13,'数据挖掘',4,20),(14,'英语',1,10),(15,'JavaScript',1,15),(16,'软件分层架构',3,20);
/*!40000 ALTER TABLE `subject` 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 */;

　　

其中：

--：表示注释内容。

/*!......*/：表示可执行注释，在MySQL中可执行，在其他数据库被当成注释。

40101：表示mysql版本，表明此语句只有在4.01.01版本或者更高版本的MySQL中才能执行。

 

示例：备份myschool.student表

warning：在命令行中输入密码是不安全的。忽略。

 

示例：备份多个数据库

备份多个数据库需要添加“--databases”参数，之后跟随多个数据库名字

如果要备份全部数据库，使用参数：--all-databases  

 

 mysqldump的选项

  

复制数据库目录

备份之前需要LOCK TABLES，然后对表执行FLUSH TABLES。也可以停止mysql服务进行备份。

缺点：1、InnoDB引擎表不支持；2、不同版本MySQL不支持。

 

MySQLhotcopy快速恢复

只能备份MyISAM引擎表。

示例：备份mysql到d:/目录下

mysqlhotcopy -u root -p123 myschool d:/db.sql

　　

数据恢复

将备份的数据进行恢复。

使用MySQL命令恢复

mysql命令的语法格式

mysql -u user -pPassword [DBName] < backdbfilename.sql

　　

如果backdbfilename.sql是mysqldump命令创建的包含数据库语句的文件，执行的时候不需要指定数据库名。

示例：恢复myschool数据库

注意：需要先手动创建myschool数据库并指定。

 

直接复制到数据库目录

 执行前停止mysql服务，拷贝后重启mysql服务。

缺点：不支持InnoDB引擎表。

 

使用mysqlhotcopy快速恢复

将mysqlhotcopy导出的数据拷贝到mysql数据库文件目录下，重启mysql服务即可。Linux中需要指定数据库文件的所有者，使用chown命令。

 

如果恢复的数据库已经存在，需要使用DROP命令删除，再进行恢复。

 

数据库迁移

将数据从一个数据库系统移动到另一个数据库系统上。

相同版本MySQL数据迁移

主版本号相同的数据库迁移，本质是源数据库的备份和目标数据库的恢复过程。

（注意：复制数据库文件只适合于MyISAM类型数据库，不支持InnoDB类型数据库）

示例：将A主机上的MySQL数据库全部迁移到B主机上

mysqldump -h www.a.com -uUser -pPassword DBName | mysql -h www.b.com -uUser -pPassword

"|"：管道连接符。

如果要全部迁移，使用--all-databases参数。

 

不同版本的迁移

建议导出SQL，在目标数据库上执行SQL。

 

表的导出和导入

数据表可以导出为sql、xml或者html文件，同时支持导入。

使用select导出表

语法格式

SELECT  字段 FROM 表名 WHERE 条件 INTO OUTFILE  '文件路径'  [选项]

　　

注意实现：需要修改my.ini的“secure-file-priv=路径名”，MySQL只支持导出到指定目录。

 

使用MySQL命令导出文本文件

语法格式

mysql -uUser -pPassword --execute="SELECT 语句" DBName >FileName.txt

　　

使用LOAD DATA INFILE导入文本文件

语法格式

LOAD DATA INFILE 'filename.txt' INTO TABLE TableName

　　

使用MySQLimport命令导入文本文件

语法格式

mysqlimport -uUser -pPassword DBName fileName.txt

　　

 

综合练习

需求说明

1. 使用mysqldump导出myschool数据库
2. 删除myschool数据
3. 使用mysql命令恢复myschool数据库