十三、MySQL数据备份与恢复

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

13.1、数据备份

数据备份:目的用于恢复;必须对备份数据做恢复测试。

  备份类型:

  热备份:在线备份,读、写不受影响;

  温备份:仅可以执行读操作;

  冷备份:离线备份;读、写操作均中止;

  物理备份和逻辑备份:

  物理备份:复制数据文件;速度快

  逻辑备份:将数据导出至文本文件中;速度慢、丢失浮点数精度,需要重建索引;方便使用文本处理工具直接对其处理、可移植能力强。

  完全备份、增量备份和差异备份;

  完全备份:备份全部数据;

  差异备份:仅备份上次完全备份以来变化的数据;

  增量备份:仅备份上次完全备份或增量备份以后变化的数据;

  备份策略:选择备份方式;选择备份时间;考虑到恢复成本(恢复时长);备份成本(锁时间、备份时长、备份负载)

  备份对象:数据、配置文件代码(存储过程,存储函数,触发器)、OS相关的配置文件,如crontab配置计划及相关的脚本。

  mysql备份工具:

  mysqldump: 逻辑备份工具、MyISAM(温)、InnoDB(热备份)

  mysqldumper: 多线程的mysqldump,很难实现差异或增量备份;

  mysqlhotcopy:物理备份工具、几乎冷备;仅适用于MyISAM存储引擎

  lvm-snapshot: 接近于热备的工具:因为要先请求全局锁,而后创建快照,并在创建快照完成后释放全局锁;备份和恢复速度较快;很难实现增量备份,并且请求全局需要等待一段时间。数据文件和事务日志要在同一个逻辑卷上。

使用mysqldump命令备份

  MySQLdump是MySQL提供的一个非常有用的数据备份工具。MySQLdump命令执行时,可以将数据库备份成一个文本文件,该文件中实际上包含了多个CREATE和INSERT语句,使用这些语句可以重新创建表和插入数据。

  MySQLdump备份数据库语句的基本语法格式为:

1
mysqldump -u user -h host -p password dbname [tbname,[ tbname...]] >filename.sql
  • 备份单个数据库中的所有表

使用mysqldump命令备份数据库所有表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#创建样例数据库
mysql>  CREATE  DATABASE  booksDB;
Query OK, 1 row affected (0.00 sec)
 
mysql> USE booksDB
Database  changed
mysql>  CREATE  TABLE  books
     -> (
     ->   bk_id   INT  NOT  NULL  PRIMARY  KEY ,
     ->   bk_title  VARCHAR (50)  NOT  NULL ,
     ->   copyright  YEAR  NOT  NULL
     -> );
Query OK, 0  rows  affected (0.05 sec)
 
mysql>  INSERT  INTO  books
     ->  VALUES  (11078,  'Learning MySQL' , 2010),
     -> (11033,  'Study Html' , 2011),
     -> (11035,  'How to use php' , 2003),
     -> (11072,  'Teach youself javascript' , 2005),
     -> (11028,  'Learing C++' , 2005),
     -> (11069,  'MySQL professional' , 2009),
     -> (11026,  'Guide to MySQL 5.5' , 2008),
     -> (11041,  'Inside VC++' , 2011);
Query OK, 8  rows  affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0
 
mysql>  CREATE  TABLE  authors
     -> (
     ->   auth_id      INT  NOT  NULL  PRIMARY  KEY ,
     ->   auth_name   VARCHAR (20),
     ->  auth_gender  CHAR (1)
     -> );
Query OK, 0  rows  affected (0.04 sec)
 
mysql>  INSERT  INTO  authors  
     ->  VALUES  (1001,  'WriterX'  , 'f' ),
     -> (1002,  'WriterA'  , 'f' ),
     -> (1003,  'WriterB'  , 'm' ),
     -> (1004,  'WriterC'  , 'f' ),
     -> (1011,  'WriterD'  , 'f' ),
     -> (1012,  'WriterE'  , 'm' ),
     -> (1013,  'WriterF'  , 'm' ),
     -> (1014,  'WriterG'  , 'f' ),
     -> (1015,  'WriterH'  , 'f' );
Query OK, 9  rows  affected (0.04 sec)
Records: 9  Duplicates: 0  Warnings: 0
 
mysql>  CREATE  TABLE  authorbook
     -> (
     ->   auth_id   INT  NOT  NULL ,
     ->   bk_id    INT  NOT  NULL ,
     ->    PRIMARY  KEY  (auth_id, bk_id),
     ->    FOREIGN  KEY  (auth_id)  REFERENCES  authors (auth_id),
     ->    FOREIGN  KEY  (bk_id)  REFERENCES  books (bk_id)
     -> );
Query OK, 0  rows  affected (0.10 sec)
 
mysql>  INSERT  INTO  authorbook
     ->  VALUES  (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
     -> (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
Query OK, 8  rows  affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

完成数据插入后,输入备份命令:

1
2
[root@mylinux ~]# mysqldump -u root -p booksDB > books.sql
Enter  password :

备份完成后的文件大致内容为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@mylinux ~]# cat books.sql 
-- MySQL dump 10.13  Distrib 5.5.56, for Linux (x86_64)
--
-- Host: localhost    Database: booksDB
-- ------------------------------------------------------
-- Server version       5.5.56-log
 
/*!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 `authorbook`
--
 
DROP  TABLE  IF EXISTS `authorbook`;
/*!40101  SET  @saved_cs_client     = @@character_set_client */;
/*!40101  SET  character_set_client = utf8 */;
CREATE  TABLE  `authorbook` (
   `auth_id`  int (11)  NOT  NULL ,
   `bk_id`  int (11)  NOT  NULL ,
   PRIMARY  KEY  (`auth_id`,`bk_id`),
   KEY  `bk_id` (`bk_id`),
   CONSTRAINT  `authorbook_ibfk_1`  FOREIGN  KEY  (`auth_id`)  REFERENCES  `authors` (`auth_id`),
   CONSTRAINT  `authorbook_ibfk_2`  FOREIGN  KEY  (`bk_id`)  REFERENCES  `books` (`bk_id`)
) ENGINE=InnoDB  DEFAULT  CHARSET=utf8;
/*!40101  SET  character_set_client = @saved_cs_client */;
...

  可以发现,备份文件包含一些信息,文件开头首先声明了备份文件使用的mysqldump版本号;然后是备份账号的名称和主机信息,以及备份的数据库的名称,最后是MySQL服务器的版本号。

  备份文件接下来的部分是一些SET语句,这些语句将一些系统变量值赋值给用户定义变量,以确保被恢复的数据库的系统变量和原来备份时的变量相同。

  备份文件中的'--'开头的是注释语句,以'/*!'开头,以'*/'结尾的语句为可执行的mysql注释,这些语句可以被MySQL执行。

  • 备份数据库中的某个表

备份booksDB数据库中的books表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@mylinux ~] # mysqldump -u root -p booksDB books > books_20170810.sql          
Enter password: 
[root@mylinux ~] # cat books_20170810.sql 
-- MySQL dump 10.13  Distrib 5.5.56,  for  Linux (x86_64)
--
-- Host: localhost    Database: booksDB
-- ------------------------------------------------------
-- Server version       5.5.56-log
 
/*!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 `books`
--
  • 备份多个数据库

使用mysqldump备份booksDB和test数据库

1
2
3
[root@mylinux ~] # mysqldump -u root -p --databases  booksDB test> books_testDB_20170710.sq
l
Enter password:

使用mysqldump备份服务器中的所有数据库

1
2
[root@mylinux ~] # mysqldump  -u root -p --all-databases > alldbinMySQL.sql          
Enter password:

  mysqldump还有一些其他选型可以用来指定备份过程,常用选项有:


–all-databases , -A 
导出全部数据库
mysqldump -uroot -p –all-databases

–all-tablespaces , -Y
导出全部表空间
mysqldump -uroot -p –all-databases –all-tablespaces

–no-tablespaces , -y
不导出任何表空间信息
mysqldump -uroot -p –all-databases –no-tablespaces

–add-drop-database
每个数据库创建之前添加drop数据库语句
mysqldump -uroot -p –all-databases –add-drop-database

–add-drop-table
每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用–skip-add-drop-table取消选项)
mysqldump -uroot -p –all-databases (默认添加drop语句)
mysqldump -uroot -p –all-databases –skip-add-drop-table (取消drop语句)

–add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用–skip-add-locks取消选项)
mysqldump -uroot -p –all-databases (默认添加LOCK语句)
mysqldump -uroot -p –all-databases –skip-add-locks (取消LOCK语句)

–comments
附加注释信息。
默认为打开,可以用–skip-comments取消mysqldump -uroot -p –all-databases (默认记录注释)mysqldump -uroot -p –all-databases –skip-comments (取消注释)

–compact
导出更少的输出信息(用于调试),去掉注释和头尾等结构,可以使用选项:–skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys
mysqldump -uroot -p –all-databases –compact

–complete-insert, -c
使用完整的insert语句(包含列名称)。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。
mysqldump -uroot -p –all-databases –complete-insert

–compress, -C在客户端和服务器之间启用压缩传递所有信息mysqldump -uroot -p –all-databases –compress

–databases, -B
导出几个数据库。参数后面所有名字参量都被看作数据库名。
mysqldump -uroot -p –databases test mysql

–debug
输出debug信息,用于调试。
默认值为:d:t:o,/tmp/mysqldump.trace
mysqldump -uroot -p –all-databases –debug
mysqldump -uroot -p –all-databases –debug="d:t:o,/tmp/debug.trace"

–debug-info
输出调试信息并退出
mysqldump -uroot -p –all-databases –debug-info

–default-character-set
设置默认字符集,默认值为utf8
mysqldump -uroot -p –all-databases –default-character-set=latin1

–delayed-insert
采用延时插入方式(INSERT DELAYED)导出数据
mysqldump -uroot -p –all-databases –delayed-insert

–events, -E
导出事件
mysqldump -uroot -p –all-databases –events

–flush-logs
开始导出之前刷新日志
请注意:假如一次导出多个数据库(使用选项–databases或者–all-databases),将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。因此,如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。
mysqldump -uroot -p –all-databases –flush-logs

–flush-privileges
在导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
mysqldump -uroot -p –all-databases –flush-privileges

–force
在导出过程中忽略出现的SQL错误
mysqldump -uroot -p –all-databases –force

–host, -h
需要导出的主机信息
mysqldump -uroot -p –host=localhost –all-databases

–ignore-table
不导出指定表。
指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。
例如:–ignore-table=database.table1 –ignore-table=database.table2 ……
mysqldump -uroot -p –host=localhost –all-databases –ignore-table=mysql.user

–lock-all-tables, -x
提交请求锁定所有数据库中的所有表,以保证数据的一致性。
这是一个全局读锁,并且自动关闭–single-transaction 和–lock-tables 选项。
mysqldump -uroot -p –host=localhost –all-databases –lock-all-tables

–lock-tables, -l
开始导出前,锁定所有表。
用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,–single-transaction是一个更好的选择,因为它根本不需要锁定表。
请注意当导出多个数据库时,–lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump -uroot -p –host=localhost –all-databases –lock-tables

–no-create-db, -n
只导出数据,而不添加CREATE DATABASE 语句。
mysqldump -uroot -p –host=localhost –all-databases –no-create-db

–no-create-info, -t只导出数据,而不添加CREATE TABLE 语句
mysqldump -uroot -p –host=localhost –all-databases –no-create-info

–no-data, -d
不导出任何数据,只导出数据库表结构
mysqldump -uroot -p –host=localhost –all-databases –no-data

–password, -p
连接数据库密码

–port, -P
连接数据库端口号

–user, -u
指定连接的用户名。



作者:HoLau
链接:http://www.jianshu.com/p/18db0fbb02b8


使用mysqlhotcopy工具快速备份

  MySQLhotcopy是一个Perl脚本。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库后单个表的最快的途径,但是只能运行在数据库目录所在的机器上,并且只能备份MyISAM类型的表,其语法格式为:

1
mysqlhotcopy db_name_1,...db_name_n /[ath/ to  new_directory

使用mysqlhotcopy备份test数据库到/usr/backup目录下

1
mysqlhotcopy  -u root -p test /usr/backup


13.2、数据恢复

使用MySQL命令恢复

 对于已经备份的包含CREATE 、INSERT语句的文本,可以使用MySQL命令导入到数据库中。MySQL命令直接执行文件汇总的这些语句,其语法为:

1
mysql -u  user  -p [dbname] <filename.sql

将books.sql文件中的备份导入到数据库中

1
mysql -u root-p booksDB < books.sql

 执行该语句前,必须先在MySQL服务器中创建booksDB数据库,如果不存在恢复过程将会出错。如果已经登录MySQL服务器,可以使用source命令导入SQL文件,语法格式为:

1
source filename

使用root用户登录到服务器,然后使用souce导入本地的备份文件books.sql

1
2
use booksdb;
source books.sql;


使用mysqlhotcopy快速恢复

 MySQLhotcopy备份后的文件可以用来恢复数据库,在MySQL服务器停止运行时,将备份的数据库文件复制到MySQL存放数据的位置,重启MySQL服务器即可。如果以根用户执行该操作,必须指定数据库文件的所有者,输入语句为:

1
chown -R mysql.mysql /var/lib/mysql/dbname

从mysqlhotcopy拷贝的备份恢复数据库

1
cp –R  /usr/backup/test usr/ local /mysql/data

执行该语句,重启服务器,MySQL将恢复到备份状态。


13.3、表的导入和导出

使用SELECT...INTO  OUTFILE导出文本文件

 MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据的到处操作。该文件被创建到服务器主机上,因此必须拥有文件写入权限,才能使用此语法。SELECT..INTO OUTFILE语句基本格式为:

1
2
3
4
5
6
7
SELECT  columnlist  FROM  table  WHERE  condition  INTO  OUTFILE  'file_name'  [OPTIONS]
  --OPTIONS 选项
   FIELDS TERMINATED  BY  ‘value’
   FIELDS [OPTIONALLY] ENCLOSED  BY  'value'
   FIELDS ESCAPED  BY  'value'
   LINES STARTING  BY  'value'
   LINES TERMINATED  BY  'value'

FIELDS TERMINATED BY ‘value’:设置字段间的分隔符

FIELDS [OPTIONALLY] ENCLOSED BY 'value':设置字段的包围符,只能为单个字符

FIELDS ESCAPED BY 'value':设置如何写入或读取特殊字符,只能为单个字符

LINES STARTING BY 'value':设置每行数据开头的字符

LINES TERMINATED BY 'value':设置每行数据结尾的字符

 FIELDS和LINES两个子句自选,如果都被指定,FIELDS必须位于LINES的前面。

  SELECT..INTO OUTFILE语句可以快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件。不能使用SELECT..INTO OUTFILE。应该在主机上使用比如'mysql -e 'SELECT...' > fine_name'的命,来生成文件。

使用SELECT...INTO OUTFILE将test数据库中的books表中的记录导出到文本文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
mysql>  SELECT  *   FROM  booksDB.books  INTO  OUTFILE  "/tmp/book0.txt" ;  
ERROR 1290 (HY000): The MySQL server  is  running  with  the  --secure-file-priv option so it cannot execute this statement
mysql> 
mysql>  show  global  variables  like  '%secure%' ;  
+ ------------------+-------+
| Variable_name    | Value |
+ ------------------+-------+
| secure_auth      |  OFF    |
| secure_file_priv |  NULL   |
+ ------------------+-------+
rows  in  set  (0.00 sec)
 
[root@mylinux ~]# vim /etc/my.cnf
    添加 secure_file_priv=/tmp/
[root@mylinux ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@mylinux ~]# 
[root@mylinux ~]# mysql -u root -p
Enter  password
Welcome  to  the MySQL monitor.  Commands  end  with  or  \g.
Your MySQL  connection  id  is  1
Server version: 5.5.56-log Source distribution
 
Copyright (c) 2000, 2017, 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>  show  global  variables  like  '%secure%' ;                     
+ ------------------+-------+
| Variable_name    | Value |
+ ------------------+-------+
| secure_auth      |  OFF    |
| secure_file_priv | /tmp/ |
+ ------------------+-------+
rows  in  set  (0.00 sec)
 
mysql>  SELECT  *   FROM  booksDB.books  INTO  OUTFILE  "/tmp/book0.txt" ;            
Query OK, 8  rows  affected (0.00 sec)
 
[root@mylinux tmp]# cat book0.txt 
11026   Guide  to  MySQL 5.5      2008
11028   Learing C++     2005
11033   Study Html      2011
11035   How  to  use php  2003
11041   Inside VC++     2011
11069   MySQL professional      2009
11072   Teach youself javascript        2005
11078   Learning MySQL  2010


使用SELECT...INTO OUTFILE将booksDB数据库中的books表中的记录导出到文本文件,使用FIELDS选项和LINES选项,要求字段之间使用逗号‘,’间隔,所有字段值用双引号括起来,定义转义字符定义为单引号‘\’’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql>  SELECT  FROM  booksDB.books  INTO  OUTFILE  "/tmp/book1.txt"
     ->   FIELDS 
     ->     TERMINATED  BY  ','
     ->     ENCLOSED  BY  '\"'
     ->     ESCAPED  BY  '\''
     ->   LINES 
     ->     TERMINATED  BY  '\r\n' ;
Query OK, 8  rows  affected (0.00 sec)
 
[root@mylinux tmp]# cat book1.txt 
"11026 "," Guide  to  MySQL 5.5 "," 2008 "
" 11028 "," Learing C++ "," 2005 "
" 11033 "," Study Html "," 2011 "
" 11035 "," How  to  use php "," 2003 "
" 11041 "," Inside VC++ "," 2011 "
" 11069 "," MySQL professional "," 2009 "
" 11072 "," Teach youself javascript "," 2005 "
" 11078 "," Learning MySQL "," 2010"


使用SELECT...INTO OUTFILE将booksDB数据库中的books表中的记录导出到文本文件,使用LINES选项,要求每行记录以字符串“> ”开始,以“<end>”字符串结尾

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql>  SELECT  FROM  booksDB.books  INTO  OUTFILE  "/tmp/book2.txt"
     ->   LINES 
     ->     STARTING  BY  '> '
     ->     TERMINATED  BY  '<end>\r\n' ;
Query OK, 8  rows  affected (0.00 sec)
 
[root@mylinux tmp]# cat book2.txt  
> 11026 Guide  to  MySQL 5.5      2008< end >
> 11028 Learing C++     2005< end >
> 11033 Study Html      2011< end >
> 11035 How  to  use php  2003< end >
> 11041 Inside VC++     2011< end >
> 11069 MySQL professional      2009< end >
> 11072 Teach youself javascript        2005< end >
> 11078 Learning MySQL  2010< end >


使用mysqldump导出文本文件

 mysqldump创建一个包含创建表的CREATE TABLE语句的tablename.sql文件和一个包含其数据的tablename.txt文件,其导出文本文件的基本语法为:

1
2
3
4
5
6
7
mysqldump -T path -u root -p dbname [tables] [options]  #指定T才可以导出纯文本文件
--options 选项
--fields-terninated-by=value
--fields-enclosed-by=value
--fields-optionally-by=value
--fields-escaped-by=value
--lines-terninated-by=value

使用mysqldump将booksDB数据库中的books表中的记录导出到文本文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
[root@mylinux ~] # mysqldump -T /tmp -u root -p booksDB books
Enter password: 
[root@mylinux ~] # cd /tmp
[root@mylinux tmp] # ls
agent_cmd.sock  book1.txt  books.sql   dir     person0.txt
book0.txt       book2.txt  books.txt  fstab
[root@mylinux tmp] # cat books.sql 
-- MySQL dump 10.13  Distrib 5.5.56,  for  Linux (x86_64)
--
-- Host: localhost    Database: booksDB
-- ------------------------------------------------------
-- Server version       5.5.56-log
 
/*!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'  */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE= ''  */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure  for  table `books`
--
 
DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books` (
   `bk_id` int(11) NOT NULL,
   `bk_title` varchar(50) NOT NULL,
   `copyright` year(4) NOT NULL,
   PRIMARY KEY (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2017-08-11 17:51:06
[root@mylinux tmp] # cat books.txt 
11026   Guide to MySQL 5.5      2008
11028   Learing C++     2005
11033   Study Html      2011
11035   How to use php  2003
11041   Inside VC++     2011
11069   MySQL professional      2009
11072   Teach youself javascript        2005
11078   Learning MySQL  2010


使用mysqldump命令将test数据库中的person表中的记录导出到文本文件,使用FIELDS选项,要求字段之间使用逗号‘,’间隔,所有字符类型字段值用双引号括起来,定义转义字符定义为问号‘?’,每行记录以回车换行符“\r\n”结尾

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@mylinux tmp] # mysqldump -T /tmp  -u root -p booksDB books --fields-terminated-by=, -
-fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n
Enter password: 
[root@mylinux tmp] # ls
agent_cmd.sock  book1.txt  books.sql   dir     person0.txt
book0.txt       book2.txt  books.txt  fstab
[root@mylinux tmp] # cat book.txt
cat : book.txt: 没有那个文件或目录
[root@mylinux tmp] # cat books.txt
11026, "Guide to MySQL 5.5" ,2008
11028, "Lea?ring C++" ,2005
11033, "Study Html" ,2011
11035, "How to use php" ,2003
11041, "Inside VC++" ,2011
11069, "MySQL p?rofessional" ,2009
11072, "Teach youself javasc?ript" ,2005
11078, "Lea?rning MySQL" ,2010
[root@mylinux tmp] #


使用mysql命令导出文本文件

  如果MySQL服务器是单独的机器,用户是在一个client上进行操作,用户要把数据结果导入到client机器上,可以使用mysql -e语句,其语法格式为:

1
mysql -u root -p --execute= "SELECT 语句"  dbname >filename.txt

使用mysql语句导出booksDB数据库中books表中的记录到文本文件

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@mylinux tmp] # mysql -u root -p --execute="SELECT * FROM books;" booksDB > /tmp/books
4.txt
Enter password: 
[root@mylinux tmp] # cat books4.txt
bk_id   bk_title        copyright
11026   Guide to MySQL 5.5      2008
11028   Learing C++     2005
11033   Study Html      2011
11035   How to use php  2003
11041   Inside VC++     2011
11069   MySQL professional      2009
11072   Teach youself javascript        2005
11078   Learning MySQL  2010


使用mysql命令导出booksDB数据库中books表中的记录到文本文件,使用--vertical参数显示结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@mylinux tmp] # mysql -u root -p --vertical --execute="SELECT * FROM books;" booksDB >
  /tmp/books5 .txt 
Enter password: 
[root@mylinux tmp] # cat books5.txt 
*************************** 1. row ***************************
     bk_id: 11026
  bk_title: Guide to MySQL 5.5
copyright: 2008
*************************** 2. row ***************************
     bk_id: 11028
  bk_title: Learing C++
copyright: 2005
*************************** 3. row ***************************
     bk_id: 11033
  bk_title: Study Html
copyright: 2011
*************************** 4. row ***************************
     bk_id: 11035
  bk_title: How to use php
copyright: 2003
*************************** 5. row ***************************
     bk_id: 11041
  bk_title: Inside VC++
copyright: 2011
*************************** 6. row ***************************
     bk_id: 11069
  bk_title: MySQL professional
copyright: 2009
*************************** 7. row ***************************
     bk_id: 11072
  bk_title: Teach youself javascript
copyright: 2005
*************************** 8. row ***************************
     bk_id: 11078
  bk_title: Learning MySQL
copyright: 2010


使用LOAD DATA INFILE导入文本文件

LOAD DATA语句基本格式为:

1
2
3
4
5
6
7
LOAD  DATA INFILE  'filename.txt'  INTO  TABLE  tablename  [OPTIONS] [ IGNORE  number LINES]
  --OPTIONS 选项
   FIELDS TERMINATED  BY  ‘value’
   FIELDS [OPTIONALLY] ENCLOSED  BY  'value'
   FIELDS ESCAPED  BY  'value'
   LINES STARTING  BY  'value'
   LINES TERMINATED  BY  'value'

使用LOAD DATA命令将/tmp/books0.txt文件中的数据导入到booksDB数据库中的books表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> USE booksDB;
Database  changed
 
mysql>  DELETE  FROM  books;
Query OK, 8  rows  affected (0.02 sec)
 
mysql>  LOAD  DATA  INFILE  '/tmp/book0.txt'  INTO  TABLE  booksDB.books; 
Query OK, 8  rows  affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql>  SELECT  * FROM  books;
+ -------+--------------------------+-----------+
| bk_id | bk_title                 | copyright |
+ -------+--------------------------+-----------+
| 11026 | Guide  to  MySQL 5.5       |      2008 |
| 11028 | Learing C++              |      2005 |
| 11033 | Study Html               |      2011 |
| 11035 | How  to  use php           |      2003 |
| 11041 | Inside VC++              |      2011 |
| 11069 | MySQL professional       |      2009 |
| 11072 | Teach youself javascript |      2005 |
| 11078 | Learning MySQL           |      2010 |
+ -------+--------------------------+-----------+
rows  in  set  (0.00 sec)


使用mysqlimport导入文本文件

  使用MySQLimport可以导入文本文件,并不需要登录MySQL客户端,其命令的基本语法格式为:

1
2
3
4
5
6
7
mysqlimport -u root -p dbname filename.txt [options]
--options 选项
--fields-terninated-by=value
--fields-enclosed-by=value
--fields-optionally-by=value
--fields-escaped-by=value
--lines-terninated-by=value

使用mysqlimport命令将/tmp目录下的books.txt文件内容导入到booksDB数据库中,字段之间使用逗号‘,’间隔,字符类型字段值用双引号括起来,定义转义字符定义为问号‘?’,每行记录以回车换行符“\r\n”结尾

1
mysqlimport -u root -p booksDB  /tmp/books .txt --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n



本文转自 梦想成大牛 51CTO博客,原文链接:http://blog.51cto.com/yinsuifeng/1955548,如需转载请自行联系原作者

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
192 0
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(备份+恢复篇)(二)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(备份+恢复篇)
73 1
|
2月前
|
关系型数据库 MySQL Shell
在Centos7中利用Shell脚本:实现MySQL的数据备份
在Centos7中利用Shell脚本:实现MySQL的数据备份
|
2月前
|
SQL 关系型数据库 MySQL
mysql数据库备份 与恢复 win下的mysql数据备份
mysql数据库备份 与恢复 win下的mysql数据备份
31 1
|
2月前
|
存储 固态存储 关系型数据库
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(系统底层优化篇)(一)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(系统底层优化篇)
56 1
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(数据恢复补充篇)(一)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(数据恢复补充篇)
47 0
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(备份+恢复篇)(一)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(备份+恢复篇)
39 1
|
2月前
|
缓存 网络协议 关系型数据库
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(系统底层优化篇)(二)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(系统底层优化篇)
43 0
|
2月前
|
关系型数据库 MySQL Shell
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(数据恢复补充篇)(二)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(数据恢复补充篇)
30 0
|
2月前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复(mysqldump, mysqlbinlog)
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复(mysqldump, mysqlbinlog)
71 0