MySQL | MySQL 数据库系统（四）- 数据库的备份与恢复

前言

大家在日常的工作中，备份数据其实是信息安全管理重要的工作之一。那么，我们在这篇文章中将介绍一下数据库的备份与恢复。MySQL 数据库的备份同时有多种方式。第一：直接打包数据库文件夹/etc/local/mysql/data,或者我们使用工具来进行备份。

1、备份数据库

通过mysqldump命令可以将指定的库、指定的表或全库导出为SQL脚本。当需要升级MySQL服务器时，可以将原有的库信息导出，然后直接在升级后的MySQL服务器中导入。

执行导出操作

使用命令mysqldump导出数据时，默认会直接在终端显示。若要保存到文件，还需要结合>重定向输出的操作。

语法基本格式

1)导出指定库中的部分表。

mysqldump [选项] 库名 [表名 1] [表名 2] ··· > /备份路径/备份文件名

2)导出一个或多个完整的库，包括所有表。

mysqldump [选项] --databases 库名 1 [库名 2] ··· > /备份路径/备份文件名

3)备份 MySQL 服务器中的所有库

mysqldump [选项] --all-databases > /备份路径/备份文件名

常用的选项包括-u，-p，分别用于指定数据库用户名和密码。

将采用上述语法基本格式 1，将MySQL库中的user表导出为mysql-user.sql文件，并采用语法基本格式 2，将整个auth库导出为auth.sql文件，两种语法基本格式的所有操作都需要root用户进行验证。

[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password: 
[root@localhost ~]# mysqldump -u root -p --databases auth > auth.sql
Enter password: 

当要备份整个MySQL服务器中的所有库时，可采用上述语法基本格式 3，当导出的数据量较大时，可添加选项–opt来进行优化执行速度。

将创建备份文件all-data.sql，且其中包括MySQL服务器中的所有库。

[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password: 

查看备份文件内容

通过工具mysqldump导出的SQL脚本是文本文件，/···/或–开头的部分表示注释信息。

使用grep，less，cat等工具可查看详细的脚本内容。

将过滤出auth.sql脚本中的数据库操作语句。

[root@localhost ~]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `servers`;
CREATE TABLE `servers` (
  `Server_name` char(64) NOT NULL,
  `Host` char(64) NOT NULL,
  `Db` char(64) NOT NULL,
  `Username` char(64) NOT NULL,
  `Password` char(64) NOT NULL,
  `Port` int(4) DEFAULT NULL,
  `Socket` char(64) DEFAULT NULL,
  `Wrapper` char(64) NOT NULL,
  `Owner` char(64) NOT NULL,
  PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';
LOCK TABLES `servers` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_name` char(16) NOT NULL,
  `user_passwd` char(48) DEFAULT '',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('jacktian','*B2B366CA5C4697F31D4C55D61F0B17E70E5664EC');
UNLOCK TABLES;

2、恢复数据库

在恢复时，可通过命令mysql对其进行导入。

语法基本格式

mysql [选项] [库名] [表名] < /备份路径/备份文件名

当备份文件中只包含表的备份，不包含库的语句时，则需要在导入时必须指定库名，且目标库必须存在。

可以从备份文件mysql-user.sql中将表导入test库。

[root@localhost ~]# mysql -u root -p test < mysql-user.sql
Enter password: 
[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;                                   ## 验证导入结果
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql> 

若备份文件中已经包括了完整的库信息，则执行导入操作时无须指定库名，可以从备份文件auth.sql恢复auth库。

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auth     ib_logfile0  localhost.err  mongodb  mysql-bin.000001  mysql-bin.000003  mysql-bin.index  performance_schema
ibdata1  ib_logfile1  localhost.pid  mysql    mysql-bin.000002  mysql-bin.000004  mysql.error.log  test
[root@localhost data]# mv auth /tmp/                           ## 移动 auth 库，进行模拟故障操作。
[root@localhost data]# ls -ld auth
ls: 无法访问auth: 没有那个文件或目录
[root@localhost data]# mysql -u root -p < ~/auth.sql           ## 执行导入恢复操作。
Enter password: 
ERROR 1050 (42S01) at line 63: Table '`auth`.`users`' already exists
[root@localhost data]# ls -ld auth                             ## 确认恢复后的结果。
drwx------. 2 mysql mysql 4096 8月   1 05:17 auth
[root@localhost data]# 

参考链接 ：

MySQL | MySQL 数据库系统（四）- 数据库的备份与恢复 : https://mp.weixin.qq.com/s/rNIIXpTkvXjedBNlGTl3_g

https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzAwMjg1NjY3Nw==&action=getalbum&album_id=1336981987902717953&subscene=&scenenote=https%3A%2F%2Fmp.weixin.qq.com%2Fs%3F__biz%3DMzAwMjg1NjY3Nw%3D%3D%26mid%3D2247487008%26idx%3D2%26sn%3D91d8cdbe14164ce4cdf0f5cbef47f18d%26key%3Dd15d59280c22c89d9fba480ac906b2dda669adc79ac6545ee4f3b29c5a8d5db59e477411e19cd6d69c1eae1b811063063940e7f9ab2a232db5e21cf923a8f2fa9e89bf06a25ece6d8b47d210310501b6%26ascene%3D1%26uin%3DMTI1MjI0NDMwNQ%253D%253D%26devicetype%3DWindows%2B7%2Bx64%26version%3D62090070%26lang%3Dzh_CN%26exportkey%3DA8GKSNq38%252B5yW3r7yFYWeGA%253D%26pass_ticket%3D1R%252BU4%252FTI6B1mA0911XhGiPjt%252Bj5pV1zfJaK4RDV%252BQ9zez1Zu5XWoVLLOlGXxQUa1%26winzoom%3D1#wechat_redirect

MySQL | MySQL 数据库系统（一） https://mp.weixin.qq.com/s?__biz=MzAwMjg1NjY3Nw==&mid=2247487019&idx=1&sn=f0117cd6772af05f25d12f1716492ff5&source=41#wechat_redirect

MySQL | MySQL 数据库系统（二）- SQL语句的基本操作 https://mp.weixin.qq.com/s?__biz=MzAwMjg1NjY3Nw==&mid=2247487014&idx=2&sn=d2406f1980fd33d3412fd828e2ee4d2a&source=41#wechat_redirect