MySQL数据备份

使用mysqldump命令备份数据库

mysqldump是MySQL一个常用的备份命令，执行命令会将包含数据结构和数据内容转换成相应的CREAT语句和INSTER INTO语句。保存在文件命令中，将来如果需要还原数据，只需要执行该文本中的mysqlSQL语句即可。

mysqldump命令格式

mysqldump -u username -h host -p password dbname[tbname1[tbname2.....]]>filename.sql

　　1.username表示用户名
　　2.host表示登录用户的主机名称，如本机为主机可以省略
　　3.password表示登录密码
　　4.dbname为需要备份的数据库
　　5.tbname为需要备份的数据表，可指定多张表。为可选表，如备份整个数据库则此项省略。
　　6.filename.sql表示备份文件的名称

生成的mysqldump文件如下：

[root@ag-1 hh]# head -50  apyunqing.sql
-- MySQL dump 10.13  Distrib 5.6.36, for Linux (x86_64)
--
-- Host: rm-e4dv83452vng293p1.mysql.rds.ztcloud.ssetech-ops.com    Database: apyunqing
-- ------------------------------------------------------
-- Server version       5.6.34

/*!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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED='05a0d73d-c527-11e8-a31f-6c92bf682bc6:1-215057799,
0b84c679-e14b-11e8-9a9f-6c92bf6742fe:1-71315660,
2591cb6f-e14b-11e8-9aa0-6c92bf684a24:1-13605932,
abeea36d-db2b-11e8-b2b2-6c92bf68600c:1-6442486';

--
-- Table structure for table `act_evt_log`
--

DROP TABLE IF EXISTS `act_evt_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `act_evt_log` (
  `LOG_NR_` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID_',
  `TYPE_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'TYPE_',
  `PROC_DEF_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'PROC_DEF_ID_',
  `PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'PROC_INST_ID_',
  `EXECUTION_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'EXECUTION_ID_',
  `TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'TASK_ID_',
  `TIME_STAMP_` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT 'TIME_STAMP_',
  `USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'USER_ID_',
  `DATA_` longblob COMMENT 'DATA_',
  `LOCK_OWNER_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'LOCK_OWNER_',
  `LOCK_TIME_` timestamp(3) NULL DEFAULT NULL COMMENT 'LOCK_TIME_',
  `IS_PROCESSED_` tinyint(4) DEFAULT '0' COMMENT 'IS_PROCESSED_',
  PRIMARY KEY (`LOG_NR_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ACT_EVT_LOG';
[root@ag-1 hh]# head -60  apyunqing.sql
-- MySQL dump 10.13  Distrib 5.6.36, for Linux (x86_64)
--
-- Host: rm-e4dv83452vng293p1.mysql.rds.ztcloud.ssetech-ops.com    Database: apyunqing
-- ------------------------------------------------------
-- Server version       5.6.34

/*!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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED='05a0d73d-c527-11e8-a31f-6c92bf682bc6:1-215057799,
0b84c679-e14b-11e8-9a9f-6c92bf6742fe:1-71315660,
2591cb6f-e14b-11e8-9aa0-6c92bf684a24:1-13605932,
abeea36d-db2b-11e8-b2b2-6c92bf68600c:1-6442486';

--
-- Table structure for table `act_evt_log`
--

DROP TABLE IF EXISTS `act_evt_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `act_evt_log` (
  `LOG_NR_` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID_',
  `TYPE_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'TYPE_',
  `PROC_DEF_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'PROC_DEF_ID_',
  `PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'PROC_INST_ID_',
  `EXECUTION_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'EXECUTION_ID_',
  `TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'TASK_ID_',
  `TIME_STAMP_` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT 'TIME_STAMP_',
  `USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'USER_ID_',
  `DATA_` longblob COMMENT 'DATA_',
  `LOCK_OWNER_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'LOCK_OWNER_',
  `LOCK_TIME_` timestamp(3) NULL DEFAULT NULL COMMENT 'LOCK_TIME_',
  `IS_PROCESSED_` tinyint(4) DEFAULT '0' COMMENT 'IS_PROCESSED_',
  PRIMARY KEY (`LOG_NR_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ACT_EVT_LOG';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `act_evt_log`
--

LOCK TABLES `act_evt_log` WRITE;
/*!40000 ALTER TABLE `act_evt_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `act_evt_log` ENABLE KEYS */;
UNLOCK TABLES;

..........省略............

/*!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-07-17 10:00:33
[root@ag-1 hh]# tail -20   apyunqing.sql
--
-- Dumping data for table `zone_topology_identity_map`
--

LOCK TABLES `zone_topology_identity_map` WRITE;
/*!40000 ALTER TABLE `zone_topology_identity_map` DISABLE KEYS */;
/*!40000 ALTER TABLE `zone_topology_identity_map` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!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-07-17 10:00:33　

Mysqldump还有一些其他参数

mysqldump还有一些其他参数可以用来指定备份过程，

　　-add-drop-table               在每个CREATE TABLE语句前添加DROP TABLE语句，默认式打开的，可以用-skip-add-drop-table取消
　　-add-locks                    该选项会在INSTER 语句中绑定一个LOCK	TABLE和UNLOCK TABLE语句，防止记录再次导入时，其他用户对表进行的操作，默认式打开的
　　-t或者                         -no-create-info 只导入数据，而不添加CREATE TABLE语句
　　-c或者                         -complete-insert 在每个INSTER语句的列上加上列名，在数据导入另一数据库时有用
　　-d或者                         -no-data 不写表的任何信息，只转存储表的结构
　　-opt                           该选项式速记，等同于指定：
                                  　　 --add-drop-tables
　　　　　　　　　　　　　　　　　　　   　　--add-locking
　　　　　　　　　　　　　　　　　　　   　　--create-option
　　　　　　　　　　　　　　　　　　　　 　　--disable-keys--extended-insert
　　　　　　　　　　　　　　　　　　　　 　　--lock-tabless--quick
　　　　　　　　　　　　　　　　　　　　 　　--set-charset 
　　　　　　　　　　　　　　　　　　　　　　它可以快速进行存储产生一个能很快导入MySQL服务器的转入文件。

　　

msyqldump提供许多参数，包括用于调试和压缩的，这里只是列出部分参数，运行一下帮助命令可以获取更多选项。

语法：

　　mysqldump   --help

使用mysqldump命令恢复数据库

对于备份数据库后包含有建库、建表、插入数据等SQL语句的文本文件，可以通过mysql命令还原到新的数据库中，实现数据库的恢复。改命令语法如下：

mysql -u username -p [dbname] < filename.sql

其中：
　　usename表示用户名
　　dbname表示数据库名
　　filename.sql为数据库备份后的文件
改命令成功后，备份文件中的语句将指定的数据库中恢复原有数据。
示例：

mysql -u root -p schoolDB < /data/myschool.sql

其他方法恢复数据库

登录看到mysql后使用source命令恢复数据库
语法：

source file
其中，filename为数据库备份文件

　　

通过复制文件实现数据备份和恢复

MySQL服务器中的数据在磁盘中式以文件形式保存的，所以可以直接复制MySQL数据的存储目录及文件进行备份。MySQL默认的数据文件存储目录在不同的操作系统下所用不同。
由于MySQL服务器的数据文件在服务器运行期间，总是处于打开和使用状态，这样导致文件副本备份不一定总是有效。因此，在复制数据文件之前，需要先停止MySQL服务。
这种方法虽然简单，但并不是最好的方法。一般情况下，MySQL服务在使用过程中不允许被停止，并且这种方法对InnoDB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同的版本的服务器中，不同版本可能不兼容。

注意：

　　使用这种方法备份数据库时，为了保证所备份数据的完整性，在停止MSQL服务器之前，需要先执行FLUSH TABLES语句将所用数据写入到文件中。

表数据导出到文本文件

通过对表数据的导出和导入，可以实现MySQL数据库服务器与其他数据服务器之间移动数据。导入操作，是指将数据表复制到文本文。数据导出的方式有多种，这里主要介绍使用SELECT....INTO OUTFILE 语句导入数据。其语法如下。
语法：

SELECT clumnlist FROM tablename 
[WHERE contine]
INTO OUTFILE 'filename' [OPTION];

从上述语法中可以看出，改导入语法分成一下两部分
　　(1)普通的数据查询语句，主要用来实现查询所用导入到文件中的数据
　　(2)通过参数filename指定导出数据的目标文件
　　(3)通过参数filename指定导入数据的目标文件
示例：

SELECT * FROM 'result'
WHERE 'subjectNo' =
(SELECT 'student' FROM 'subject' WHERE 'subjectName' = 'Logic java')
INTO OUTFILE 'd:ackup
esult_Java.txt'

知识扩展：
为了导出的文本文件可读性更好，可以在SELECT ...... INTO OUTFILE 在语句后置相应参数选项，常用参数选项如下：
　　FILEDS  TERMINATED  BY  'string'：用来设置字段的分割符为字段对象（string），默认为" "
　　FILEDS  [OPTIONNALLY]  ENCLOSED  BY 'char'：用来设置括上字段值的字符号，如果使用了OPTIONALLY,则只有CHAR和VARCHAR等字符数据字段备包括。默认情况下使用任何符号。
　　FILEDS  ESCAPED  BY  'char'：用来设置转义字符的字符号，默认情况使用""字符
　　LINES    STARTING  BY  'char'：用来设置每行开头个的字符号，默认情况下不使用任何符号
　　LINES    TERMINATED  BY  'string' 用来设置每行结束的字符号，默认情况不使用" " 字符串
使用示例：

USE myschool;
SELECT * FROM 'result'
WHERE 'subjectNo' =
(SELECT 'subjectNo' FROM 'subject' WHERE 'subjectName' = 'Logic Java')
INTO OUTFILE 'd:ackup
esult_Java.txt2'
FILEDS
TERMINATED BY '、'
ENCLOSED BY '"'
LINES 
STARTING BY '>'
TERMINATED BY '
';

文本文件导入数据表

所谓导入操作，是指导入从文本文件加载MySQL数据库表里，同样，导入数据的方式也有多种。本小结介绍使用LOAD DATA INFILE语句实现数据的导入，语法如下。
语法

LOAD DATA INFILE filename INTO TABLE TABLENAME [OPTION];　　

其中：
　　filename 用来指定文本文件的路径和特征
　　tablename用来之指定导入表的名称
导入示例：

USE myschoolDB;
/*创建result表结构*/
CREATE TABLE 'result'(
'studentNo' INT(4) NOT NULL COMMENT '学号',
'subjectNo' INT(4) NOT NULL COMMENT '课程编号',
'examData' DATETIME NOT NULL COMMENT '考试日期',
'studentResult' INT(4) NOT NULL COMMENT '考试成绩',
PRIMARY KEY ('studentNo','subjectNo','examDate')

)ENGINE=INNODB DEFAULT CHARSET=utf8;
/*导入数据*/
LOAD DATA INFILE 'd:ackup
esult_Java.txt2' INTO TABLE result;
/*查看result表数据*/
SELECT * FROM result;

如果在导入文件使用了参数选项改变了显示格式，那么导入数据时同样需要设置相应参数。查用参数选项与使用SELECT ......INTO OUTFILE 导入数据的参数相对应，例如：

/*删除result表数据，保证式空表*/
DELECT FROM result;
/*导入数据*/
LOAD DATA INFILE 'd:/backup/result_Java2.txt' INTO TABLE 'result'
FILDS
FILEDS
TERMINATED BY '、'
ENCLOSED BY '"'
LINES 
STARTING BY '>'
TERMINATED BY '
';