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 –all-tablespaces , -Y –no-tablespaces , -y –add-drop-database –add-drop-table –add-locks –comments –compact –complete-insert, -c –compress, -C在客户端和服务器之间启用压缩传递所有信息mysqldump -uroot -p –all-databases –compress –databases, -B –debug –debug-info –default-character-set –delayed-insert –events, -E –flush-logs –flush-privileges –force –host, -h –ignore-table –lock-all-tables, -x –lock-tables, -l –no-create-db, -n –no-create-info, -t只导出数据，而不添加CREATE TABLE 语句 –no-data, -d –password, -p –port, -P –user, -u
|
使用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
|
+
------------------+-------+
2
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/ |
+
------------------+-------+
2
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 |
+
-------+--------------------------+-----------+
8
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，如需转载请自行联系原作者
