十三、MySQL数据备份与恢复

来源:转载


13.1、数据备份

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

备份类型:

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

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

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

物理备份和逻辑备份:

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

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

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

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

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

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

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

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

mysql备份工具:

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

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

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

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

使用mysqldump命令备份

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

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

mysqldump-uuser-hhost-ppassworddbname[tbname,[tbname...]]>filename.sql

备份单个数据库中的所有表

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

#创建样例数据库
mysql>CREATEDATABASEbooksDB;
QueryOK,1rowaffected(0.00sec)

mysql>USEbooksDB
Databasechanged
mysql>CREATETABLEbooks
->(
->bk_idINTNOTNULLPRIMARYKEY,
->bk_titleVARCHAR(50)NOTNULL,
->copyrightYEARNOTNULL
->);
QueryOK,0rowsaffected(0.05sec)

mysql>INSERTINTObooks
->VALUES(11078,'LearningMySQL',2010),
->(11033,'StudyHtml',2011),
->(11035,'Howtousephp',2003),
->(11072,'Teachyouselfjavascript',2005),
->(11028,'LearingC++',2005),
->(11069,'MySQLprofessional',2009),
->(11026,'GuidetoMySQL5.5',2008),
->(11041,'InsideVC++',2011);
QueryOK,8rowsaffected(0.03sec)
Records:8Duplicates:0Warnings:0

mysql>CREATETABLEauthors
->(
->auth_idINTNOTNULLPRIMARYKEY,
->auth_nameVARCHAR(20),
->auth_genderCHAR(1)
->);
QueryOK,0rowsaffected(0.04sec)

mysql>INSERTINTOauthors
->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');
QueryOK,9rowsaffected(0.04sec)
Records:9Duplicates:0Warnings:0

mysql>CREATETABLEauthorbook
->(
->auth_idINTNOTNULL,
->bk_idINTNOTNULL,
->PRIMARYKEY(auth_id,bk_id),
->FOREIGNKEY(auth_id)REFERENCESauthors(auth_id),
->FOREIGNKEY(bk_id)REFERENCESbooks(bk_id)
->);
QueryOK,0rowsaffected(0.10sec)

mysql>INSERTINTOauthorbook
->VALUES(1001,11033),(1002,11035),(1003,11072),(1004,11028),
->(1011,11078),(1012,11026),(1012,11041),(1014,11069);
QueryOK,8rowsaffected(0.02sec)
Records:8Duplicates:0Warnings:0

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

[[email protected]~]#mysqldump-uroot-pbooksDB>books.sql
Enterpassword:

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

[[email protected]~]#catbooks.sql
--MySQLdump10.13Distrib5.5.56,forLinux(x86_64)
--
--Host:localhostDatabase:booksDB
--------------------------------------------------------
--Serverversion5.5.56-log

/*[email protected][email protected]@CHARACTER_SET_CLIENT*/;
/*[email protected][email protected]@CHARACTER_SET_RESULTS*/;
/*[email protected][email protected]@COLLATION_CONNECTION*/;
/*!40101SETNAMESutf8*/;
/*[email protected][email protected]@TIME_ZONE*/;
/*!40103SETTIME_ZONE='+00:00'*/;
/*[email protected][email protected]@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*[email protected][email protected]@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;
/*[email protected][email protected]@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;
/*[email protected][email protected]@SQL_NOTES,SQL_NOTES=0*/;

--
--Tablestructurefortable`authorbook`
--

DROPTABLEIFEXISTS`authorbook`;
/*[email protected][email protected]@character_set_client*/;
/*!40101SETcharacter_set_client=utf8*/;
CREATETABLE`authorbook`(
`auth_id`int(11)NOTNULL,
`bk_id`int(11)NOTNULL,
PRIMARYKEY(`auth_id`,`bk_id`),
KEY`bk_id`(`bk_id`),
CONSTRAINT`authorbook_ibfk_1`FOREIGNKEY(`auth_id`)REFERENCES`authors`(`auth_id`),
CONSTRAINT`authorbook_ibfk_2`FOREIGNKEY(`bk_id`)REFERENCES`books`(`bk_id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
/*[email protected]_cs_client*/;
...

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

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

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

备份数据库中的某个表

备份booksDB数据库中的books表

[[email protected]~]#mysqldump-uroot-pbooksDBbooks>books_20170810.sql
Enterpassword:
[[email protected]~]#catbooks_20170810.sql
--MySQLdump10.13Distrib5.5.56,forLinux(x86_64)
--
--Host:localhostDatabase:booksDB
--------------------------------------------------------
--Serverversion5.5.56-log

/*[email protected][email protected]@CHARACTER_SET_CLIENT*/;
/*[email protected][email protected]@CHARACTER_SET_RESULTS*/;
/*[email protected][email protected]@COLLATION_CONNECTION*/;
/*!40101SETNAMESutf8*/;
/*[email protected][email protected]@TIME_ZONE*/;
/*!40103SETTIME_ZONE='+00:00'*/;
/*[email protected][email protected]@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*[email protected][email protected]@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;
/*[email protected][email protected]@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;
/*[email protected][email protected]@SQL_NOTES,SQL_NOTES=0*/;

--
--Tablestructurefortable`books`
--

备份多个数据库

使用mysqldump备份booksDB和test数据库

[[email protected]~]#mysqldump-uroot-p--databasesbooksDBtest>books_testDB_20170710.sq
l
Enterpassword:

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

[[email protected]~]#mysqldump-uroot-p--all-databases>alldbinMySQL.sql
Enterpassword:

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-keysmysqldump -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.tracemysqldump -uroot -p –all-databases –debugmysqldump -uroot -p –all-databases –debug="d:t:o,/tmp/debug.trace"

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

–default-character-set设置默认字符集,默认值为utf8mysqldump -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类型的表,其语法格式为:

mysqlhotcopydb_name_1,...db_name_n/[ath/tonew_directory

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

mysqlhotcopy-uroot-ptest/usr/backup

13.2、数据恢复

使用MySQL命令恢复

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

mysql-uuser-p[dbname]<filename.sql

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

mysql-uroot-pbooksDB<books.sql

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

sourcefilename

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

usebooksdb;
sourcebooks.sql;

使用mysqlhotcopy快速恢复

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

chown-Rmysql.mysql/var/lib/mysql/dbname

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

cp–R/usr/backup/testusr/local/mysql/data

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

13.3、表的导入和导出

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

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

SELECTcolumnlistFROMtableWHEREconditionINTOOUTFILE'file_name'[OPTIONS]
--OPTIONS选项
FIELDSTERMINATEDBY‘value’
FIELDS[OPTIONALLY]ENCLOSEDBY'value'
FIELDSESCAPEDBY'value'
LINESSTARTINGBY'value'
LINESTERMINATEDBY'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表中的记录导出到文本文件

mysql>SELECT*FROMbooksDB.booksINTOOUTFILE"/tmp/book0.txt";
ERROR1290(HY000):TheMySQLserverisrunningwiththe--secure-file-privoptionsoitcannotexecutethisstatement
mysql>
mysql>showglobalvariableslike'%secure%';
+------------------+-------+
|Variable_name|Value|
+------------------+-------+
|secure_auth|OFF|
|secure_file_priv|NULL|
+------------------+-------+
2rowsinset(0.00sec)

[[email protected]~]#vim/etc/my.cnf
添加secure_file_priv=/tmp/
[[email protected]~]#servicemysqldrestart
ShuttingdownMySQL..SUCCESS!
StartingMySQL..SUCCESS!
[[email protected]~]#
[[email protected]~]#mysql-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or/g.
YourMySQLconnectionidis1
Serverversion:5.5.56-logSourcedistribution

Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'/h'forhelp.Type'/c'toclearthecurrentinputstatement.

mysql>showglobalvariableslike'%secure%';
+------------------+-------+
|Variable_name|Value|
+------------------+-------+
|secure_auth|OFF|
|secure_file_priv|/tmp/|
+------------------+-------+
2rowsinset(0.00sec)

mysql>SELECT*FROMbooksDB.booksINTOOUTFILE"/tmp/book0.txt";
QueryOK,8rowsaffected(0.00sec)

[[email protected]]#catbook0.txt
11026GuidetoMySQL5.52008
11028LearingC++2005
11033StudyHtml2011
11035Howtousephp2003
11041InsideVC++2011
11069MySQLprofessional2009
11072Teachyouselfjavascript2005
11078LearningMySQL2010

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

mysql>SELECT*FROMbooksDB.booksINTOOUTFILE"/tmp/book1.txt"
->FIELDS
->TERMINATEDBY','
->ENCLOSEDBY'/"'
->ESCAPEDBY'/''
->LINES
->TERMINATEDBY'/r/n';
QueryOK,8rowsaffected(0.00sec)

[[email protected]]#catbook1.txt
"11026","GuidetoMySQL5.5","2008"
"11028","LearingC++","2005"
"11033","StudyHtml","2011"
"11035","Howtousephp","2003"
"11041","InsideVC++","2011"
"11069","MySQLprofessional","2009"
"11072","Teachyouselfjavascript","2005"
"11078","LearningMySQL","2010"

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

mysql>SELECT*FROMbooksDB.booksINTOOUTFILE"/tmp/book2.txt"
->LINES
->STARTINGBY'>'
->TERMINATEDBY'<end>/r/n';
QueryOK,8rowsaffected(0.00sec)

[[email protected]]#catbook2.txt
>11026GuidetoMySQL5.52008<end>
>11028LearingC++2005<end>
>11033StudyHtml2011<end>
>11035Howtousephp2003<end>
>11041InsideVC++2011<end>
>11069MySQLprofessional2009<end>
>11072Teachyouselfjavascript2005<end>
>11078LearningMySQL2010<end>

使用mysqldump导出文本文件

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

mysqldump-Tpath-uroot-pdbname[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表中的记录导出到文本文件

[[email protected]~]#mysqldump-T/tmp-uroot-pbooksDBbooks
Enterpassword:
[[email protected]~]#cd/tmp
[[email protected]]#ls
agent_cmd.sockbook1.txtbooks.sqldirperson0.txt
book0.txtbook2.txtbooks.txtfstab
[[email protected]]#catbooks.sql
--MySQLdump10.13Distrib5.5.56,forLinux(x86_64)
--
--Host:localhostDatabase:booksDB
--------------------------------------------------------
--Serverversion5.5.56-log

/*[email protected][email protected]@CHARACTER_SET_CLIENT*/;
/*[email protected][email protected]@CHARACTER_SET_RESULTS*/;
/*[email protected][email protected]@COLLATION_CONNECTION*/;
/*!40101SETNAMESutf8*/;
/*[email protected][email protected]@TIME_ZONE*/;
/*!40103SETTIME_ZONE='+00:00'*/;
/*[email protected][email protected]@SQL_MODE,SQL_MODE=''*/;
/*[email protected][email protected]@SQL_NOTES,SQL_NOTES=0*/;

--
--Tablestructurefortable`books`
--

DROPTABLEIFEXISTS`books`;
/*[email protected][email protected]@character_set_client*/;
/*!40101SETcharacter_set_client=utf8*/;
CREATETABLE`books`(
`bk_id`int(11)NOTNULL,
`bk_title`varchar(50)NOTNULL,
`copyright`year(4)NOTNULL,
PRIMARYKEY(`bk_id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
/*[email protected]_cs_client*/;

/*[email protected]_TIME_ZONE*/;

/*[email protected]_SQL_MODE*/;
/*[email protected]_CHARACTER_SET_CLIENT*/;
/*[email protected]_CHARACTER_SET_RESULTS*/;
/*[email protected]_COLLATION_CONNECTION*/;
/*[email protected]_SQL_NOTES*/;

--Dumpcompletedon2017-08-1117:51:06
[[email protected]]#catbooks.txt
11026GuidetoMySQL5.52008
11028LearingC++2005
11033StudyHtml2011
11035Howtousephp2003
11041InsideVC++2011
11069MySQLprofessional2009
11072Teachyouselfjavascript2005
11078LearningMySQL2010

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

[[email protected]]#mysqldump-T/tmp-uroot-pbooksDBbooks--fields-terminated-by=,-
-fields-optionally-enclosed-by=/"--fields-escaped-by=?--lines-terminated-by=/r/n
Enterpassword:
[[email protected]]#ls
agent_cmd.sockbook1.txtbooks.sqldirperson0.txt
book0.txtbook2.txtbooks.txtfstab
[[email protected]]#catbook.txt
cat:book.txt:没有那个文件或目录
[[email protected]]#catbooks.txt
11026,"GuidetoMySQL5.5",2008
11028,"Lea?ringC++",2005
11033,"StudyHtml",2011
11035,"Howtousephp",2003
11041,"InsideVC++",2011
11069,"MySQLp?rofessional",2009
11072,"Teachyouselfjavasc?ript",2005
11078,"Lea?rningMySQL",2010
[[email protected]]#

使用mysql命令导出文本文件

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

mysql-uroot-p--execute="SELECT语句"dbname>filename.txt

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

[[email protected]]#mysql-uroot-p--execute="SELECT*FROMbooks;"booksDB>/tmp/books
4.txt
Enterpassword:
[[email protected]]#catbooks4.txt
bk_idbk_titlecopyright
11026GuidetoMySQL5.52008
11028LearingC++2005
11033StudyHtml2011
11035Howtousephp2003
11041InsideVC++2011
11069MySQLprofessional2009
11072Teachyouselfjavascript2005
11078LearningMySQL2010

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

[[email protected]]#mysql-uroot-p--vertical--execute="SELECT*FROMbooks;"booksDB>
/tmp/books5.txt
Enterpassword:
[[email protected]]#catbooks5.txt
***************************1.row***************************
bk_id:11026
bk_title:GuidetoMySQL5.5
copyright:2008
***************************2.row***************************
bk_id:11028
bk_title:LearingC++
copyright:2005
***************************3.row***************************
bk_id:11033
bk_title:StudyHtml
copyright:2011
***************************4.row***************************
bk_id:11035
bk_title:Howtousephp
copyright:2003
***************************5.row***************************
bk_id:11041
bk_title:InsideVC++
copyright:2011
***************************6.row***************************
bk_id:11069
bk_title:MySQLprofessional
copyright:2009
***************************7.row***************************
bk_id:11072
bk_title:Teachyouselfjavascript
copyright:2005
***************************8.row***************************
bk_id:11078
bk_title:LearningMySQL
copyright:2010

使用LOAD DATA INFILE导入文本文件

LOAD DATA语句基本格式为:

LOADDATAINFILE'filename.txt'INTOTABLEtablename[OPTIONS][IGNOREnumberLINES]
--OPTIONS选项
FIELDSTERMINATEDBY‘value’
FIELDS[OPTIONALLY]ENCLOSEDBY'value'
FIELDSESCAPEDBY'value'
LINESSTARTINGBY'value'
LINESTERMINATEDBY'value'

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

mysql>USEbooksDB;
Databasechanged

mysql>DELETEFROMbooks;
QueryOK,8rowsaffected(0.02sec)

mysql>LOADDATAINFILE'/tmp/book0.txt'INTOTABLEbooksDB.books;
QueryOK,8rowsaffected(0.01sec)
Records:8Deleted:0Skipped:0Warnings:0

mysql>SELECT*FROMbooks;
+-------+--------------------------+-----------+
|bk_id|bk_title|copyright|
+-------+--------------------------+-----------+
|11026|GuidetoMySQL5.5|2008|
|11028|LearingC++|2005|
|11033|StudyHtml|2011|
|11035|Howtousephp|2003|
|11041|InsideVC++|2011|
|11069|MySQLprofessional|2009|
|11072|Teachyouselfjavascript|2005|
|11078|LearningMySQL|2010|
+-------+--------------------------+-----------+
8rowsinset(0.00sec)

使用mysqlimport导入文本文件

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

mysqlimport-uroot-pdbnamefilename.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”结尾

mysqlimport-uroot-pbooksDB/tmp/books.txt--fields-terminated-by=,--fields-optionally-enclosed-by=/"--fields-escaped-by=?--lines-terminated-by=/r/n

分享给朋友:
您可能感兴趣的文章:
随机阅读: