mysql备份恢复

xljs 贡献于2016-09-13

作者 hk  创建于2009-06-18 09:39:00   修改者雨林木风  修改于2010-06-17 16:05:00字数16103

文档摘要:在MySQL里面,逻辑备份的最大优点是对于各种存储引擎,都可以用同样的方法来备份;而物理备份则不同,不同的存储引擎有着不同的备份方法。因此,对于不同存储引擎混合的数据库,用逻辑备份会更简单一些。本节将详细介绍逻辑备份以及相应的恢复方法。
关键词:

 备份/恢复策略 需要考虑的一些因素。 l 确定要备份的表的存储引擎是事务型还是非事务性,两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。 l 确定使用全备份还是增量备份。全备份的优点是备份保持最新备份,恢复的时候可以花费更少的时间;缺点是如果数据量大,将会花费很多的时间,并对系统造成较长时间的压力。增量备份则恰恰相反,只需要备份每天的增量日志,备份时间少,对负载压力也小;缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志,恢复时间会长些。 l 可以考虑采取复制的方法来做异地备份,但是记住,复制不能代替备份,它对数据库的误操作也无能为力。 l 要定期做备份,备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。 l 确保MySQL打开log-bin选项,有了BINLOG,MySQL才可以在必要的时候做完整恢复,或基于时间点的恢复,或基于位置的恢复。 l 要经常做备份恢复测试,确保备份是有效的,并且是可以恢复的。 逻辑备份和恢复 在MySQL里面,逻辑备份的最大优点是对于各种存储引擎,都可以用同样的方法来备份;而物理备份则不同,不同的存储引擎有着不同的备份方法。因此,对于不同存储引擎混合的数据库,用逻辑备份会更简单一些。本节将详细介绍逻辑备份以及相应的恢复方法。 备份 MySQL中的逻辑备份是将数据库中的数据备份为一个文本文件,备份的文件可以被查看和编辑。在MySQL中,使用mysqldump工具来完成逻辑备份。有以下3种方法来调用mysqldump: ¡ 备份指定的数据库,或者此数据库中某些表。 shell> mysqldump [options] db_name [tables] ¡ 备份指定的一个或多个数据库。 shell> mysqldump [options] ---database DB1 [DB2 DB3...] ¡ 备份所有数据库。 shell> mysqldump [options] --all-databases 如果没有指定数据库中的任何表,默认导出所有数据库中所有表。以下给出一些使用mysqldump工具进行备份的例子。 (1)备份所有数据库: [zzx@localhost ~]$ mysqldump –u root -p --all-databases > all.sql Enter password: (2)备份数据库test: [zzx@localhost ~]$ mysqldump –u root -p test > test.sql 第 页 共 18 页 Enter password: (3)备份数据库test下的表emp: [zzx@localhost ~]$ mysqldump -uroot -p test emp > emp.sql Enter password: (4)备份数据库test下的表emp和dept: [zzx@localhost ~]$ mysqldump -uroot -p test emp dept > emp_dept.sql Enter password: (5)备份数据库test下的所有表为逗号分割的文本,备份到/tmp: [root@localhost tmp]# mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' [root@localhost tmp]# more emp.txt 1,z1 2,z2 3,z3 4,z4 1,z1 其中mysqldump的选项很多,具体可以使用“--help”参数查看帮助: mysqldump --help 需要强调的是,为了保证数据备份的一致性,MyISAM存储引擎在备份的时候需要加上-l参数,表示将所有表加上读锁,在备份期间,所有表将只能读而不能进行数据更新。但是对于事务存储引擎(InnoDB和BDB)来说,可以采用更好的选项--single-transaction,此选项将使得InnoDB存储引擎得到一个快照(Snapshot),使得备份的数据能够保证一致性。 完全恢复 mysqldump的恢复也很简单,将备份作为输入执行即可,具体语法如下: mysql –uroot –p dbname < bakfile 注意,将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做,语法如下: mysqlbinlog binlog-file | mysql -u root –p*** 以下是一个完整的mysqldump备份与恢复的例子。 (1)上午9点,备份数据库: [root@localhost mysql]# mysqldump -uroot –p –l –F test >test.dmp Enter password: 其中-l参数表示给所有表加读锁,-F表示生成一个新的日志文件,此时,test中emp表的数据如下: mysql> select * from emp order by id; +------+------+ | id | name | +------+------+ | 1 | z1 | | 2 | z2 | | 3 | z3 | | 4 | z4 | +------+------+ 4 rows in set (0.00 sec) 第 页 共 18 页 (2)9点半备份完毕,然后,插入新的数据: mysql> insert into emp values(5,'z5'); Query OK, 1 row affected (0.04 sec) mysql> insert into emp values(6,'z6'); Query OK, 1 row affected (0.04 sec) (3)10点,数据库突然故障,数据无法访问。需要恢复备份: [root@localhost mysql]# mysql -uroot -p test < test.dmp Enter password: 恢复后的数据如下: mysql> select * from emp order by id; +------+------+ | id | name | +------+------+ | 1 | z1 | | 2 | z2 | | 3 | z3 | | 4 | z4 | +------+------+ 4 rows in set (0.00 sec) (4)使用mysqlbinlog恢复自mysqldump备份以来的BINLOG。 (注意:这里有两点关键 1、 恢复之前要把binlog文件拷贝到,备份目录内。在备份目录进行恢复,否则会出现重复DROP,以致恢复不成功的假象 2、 或者可以考虑恢复的时候,加入下面所讲的指定 检查点或者时间的方式来避免错误恢复) [root@localhost mysql]# mysqlbinlog localhost-bin.000015 | mysql -u root –p test Enter password: 查询完全恢复的数据如下: mysql> select * from emp order by id; +------+------+ | id | name | +------+------+ | 1 | z1 | | 2 | z2 | | 3 | z3 | | 4 | z4 | | 5 | z5 | | 6 | z6 | +------+------+ 6 rows in set (0.00 sec) 至此,数据库完全恢复。 基于时间点恢复 第 页 共 18 页 由于误操作,比如误删除了一张表,这时使用完全恢复是没有用的,因为日志里面还存在误操作的语句,我们需要的是恢复到误操作之前的状态,然后跳过误操作语句,再恢复后面执行的语句,完成我们的恢复。这种恢复叫不完全恢复,在MySQL中,不完全恢复分为基于时间点的恢复和基于位置的恢复。 以下是基于时间点恢复的操作步骤。 (1)如果上午10点发生了误操作,可以用以下语句用备份和BINLOG将数据恢复到故障前: shell>mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root –pmypwd (2)跳过故障时的时间点,继续执行后面的BINLOG,完成恢复。 shell>mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456| mysql -u root -pmypwd \ 基于位置恢复 和基于时间点的恢复类似,但是更精确,因为同一个时间点可能有很多条SQL语句同时执行。恢复的操作步骤如下。 (1)在shell下执行如下命令: shell>mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql 该命令将在/tmp目录创建小的文本文件,编辑此文件,找到出错语句前后的位置号,例如前后位置号分别是368312和368315。 (2)恢复了以前的备份文件后,应从命令行输入下面内容: shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \     | mysql -u root -pmypwd shell>mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \     | mysql -u root -pmypwd \ 上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句,因此恢复的数据和相关MySQL日志将反应事务执行的原时间。 物理备份和恢复 物理备份又分为冷备份和热备份两种,和逻辑备份相比,它的最大优点是备份和恢复的速度更快,因为物理备份的原理都是基于文件的cp。本节将介绍MySQL中的物理备份及其恢复的方法。 冷备份 冷备份其实就是停掉数据库服务,cp数据文件的方法。这种方法对MyISAM和InnoDB存储引擎都适合,但是一般很少使用,因为很多应用是不允许长时间停机的。 进行备份的操作如下:停掉MySQL服务,在操作系统级别备份MySQL的数据文件和日志文件到备份目录。 进行恢复的操作如下:首先停掉MySQL服务,在操作系统级别恢复MySQL的数据文件;然后重启MySQL服务,使用mysqlbinlog工具恢复自备份以来的所有BINLOG。 第 页 共 18 页 热备份 MySQL中,对于不同的存储引擎热备份方法也有所不同,下面主要介绍MyISAM和InnoDB两种最常用的存储引擎的热备份方法。 1.MyISAM存储引擎 MyISAM存储引擎的热备份有很多方法,本质其实就是将要备份的表加读锁,然后再cp数据文件到备份目录。常用的有以下两种方法。 方法1:使用mysqlhotcopy工具 mysqlhotcopy是MySQL自带的一个热备份工具,使用方法很简单: shell> mysqlhotcopy db_name [/path/to/new_directory] mysqlhotcopy有很多选项,具体可以使用“--help”查看帮助: mysqlhotcopy --help 方法2:手工锁表copy 在mysqlhotcopy使用不正常的情况下,可以手工来做热备份,操作步骤如下: 首先数据库中所有表加读锁: mysql>flush tables for read ; 然后cp数据文件到备份目录即可。 2.InnoDB存储引擎 ibbackup是Innobase公司(www.innodb.com)的一个热备份工具,专门对InnoDB存储引擎进行物理热备份,此工具是收费的,但可以免费使用1个月。Innobase公司已经于2005年被Oracle公司所收购。 下面简单介绍一下使用ibbackup工具的备份步骤。 (1)编辑用于启动的配置文件my.cnf和用于备份的配置文件backup-my.cnf。 my.cnf的例子如下: [mysqld] datadir = /home/heikki/data innodb_data_home_dir = /home/heikki/data innodb_data_file_path = ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend innodb_log_group_home_dir = /home/heikki/data set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=20M (2)如果想备份到/home/heikki/backup,则backup-my.cnf的例子如下。 [mysqld] datadir = /home/heikki/backup innodb_data_home_dir = /home/heikki/backup innodb_data_file_path = ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend innodb_log_group_home_dir = /home/heikki/backup 第 页 共 18 页 set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=20M (3)开始备份,具体如下: $ ibbackup /home/pekka/my.cnf /home/pekka/backup-my.cnf InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy License A00001 is granted to Innobase Oy Type ibbackup –license for detailed license terms, –help for help Contents of /home/pekka/my.cnf: innodb_data_home_dir got value /home/heikki/data innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend datadir got value /home/heikki/data innodb_log_group_home_dir got value /home/heikki/data innodb_log_files_in_group got value 3 innodb_log_file_size got value 10485760 Contents of /home/pekka/backup-my.cnf: innodb_data_home_dir got value /home/heikki/backup innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend datadir got value /home/heikki/backup innodb_log_group_home_dir got value /home/heikki/backup innodb_log_files_in_group got value 3 innodb_log_file_size got value 10485760 ibbackup: Found checkpoint at lsn 0 268331310 ibbackup: Starting log scan from lsn 0 268331008 040121 17:35:46 ibbackup: Copying log… 040121 17:35:47 ibbackup: Switching to log file 2, lsn 0 272584704 040121 17:35:49 ibbackup: Log copied, lsn 0 282171935 ibbackup: We wait 10 seconds before starting copying the data files… 040121 17:35:59 ibbackup: Copying /home/heikki/data/ibdata1 040121 17:35:59 ibbackup: Switching to log file 0, lsn 0 283068416 040121 17:36:42 ibbackup: Copying /home/heikki/data/ibdata2 040121 17:38:19 ibbackup: Copying /home/heikki/data/ibdata3 ibbackup: A copied database page was modified at 0 284263243 ibbackup: Scanned log up to lsn 0 291666654 ibbackup: Was able to parse the log up to lsn 0 291666654 ibbackup: Maximum page number for a log record 3127 040121 17:42:15 ibbackup: Full backup completed! 注意:ibbackup工具不会覆盖任何重名的文件,因此在新的备份开始之前,需要确保备份目录中没有重名文件,否则备份很可能会失败。 第 页 共 18 页 (4)备份完成后,备份目录下包含有数据文件和日志文件,如下所示: $ ls -lh /home/heikki/backup total 824M -rw-r—– 1 pekka dev 22M Jan 21 17:42 ibbackup_logfile -rw-r—– 1 pekka dev 100M Jan 21 17:36 ibdata1 -rw-r—– 1 pekka dev 200M Jan 21 17:38 ibdata2 -rw-r—– 1 pekka dev 500M Jan 21 17:42 ibdata3 因为在cp数据文件时,文件内容在不断地变化,因此在不同的时间点cp的数据块中的数据很可能是不一致的。因此,ibbackup在备份期间用一个日志文件ibbackup_logfile记录了备份期间数据的变化,在恢复的时候就可以用此日志文件对备份的数据文件进行日志重做,使得备份的数据能够保持完整性和一致性。 当主数据库出现故障时,我们需要用备份进行恢复,恢复的步骤如下。 (1)进行日志重做。如前面所述,利用下面的命令对备份数据进行日志重做。 shell>ibbackup --apply-log /home/pekka/backup-my.cnf (2)恢复后重启数据库服务: shell>./bin/mysqld_saft --defaults-file=/home/pekka/backup-my.cnf & (3)服务重启后,利用BINLOG日志将备份点与故障点之间的剩余数据进行恢复。 mysqlbinlog binlog-file | mysql -u root –p*** ibbackup还有一些其他的功能,比如压缩备份、不完全恢复等,这里就不再赘述。更详细的使用方法读者可以用“ibbackup --help”命令进行查看,或者参阅官方帮助文档(http://www.innodb.com/support/documentation/innodb-hot-backup-manual/)。对于InnoDB和MyISAM混合的数据库,Innobase公司还提供了一个开源的Perl脚本innobackup,它可以将两种存储引擎的表一起进行备份,具体使用方法读者可以参阅上述链接中的文档。 表的导入导出 在数据库的日常维护中,表的导入导出是很频繁的一类操作。本节将对MySQL中这类操作进行详细的介绍,希望读者能够熟练掌握。 导出 在某些情况下,为了一些特定的目的,经常需要将表里的数据导出为某些符号分割的纯数据文本,而不是SQL语句。这些应用可能有以下一些: l 用来作为EXCEL显示; l 单纯为了节省备份空间; l 为了快速的加载数据,LOAD DATA的加载速度比普通的SQL加载要快20倍以上。 为了满足这些应用,可以使用以下两种办法来实现。 方法1:使用SELECT ...INTO OUTFILE ...命令来导出数据,具体语法如下。 mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option]; 其中option参数可以是以下选项: FIELDS TERMINATED BY 'string' (字段分隔符,默认为制表符’\t’); FIELDS [OPTIONALLY] ENCLOSED BY 'char'(字段引用符,如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上。默认不使用引用符 第 页 共 18 页 ); FIELDS ESCAPED BY 'char' (转义字符,默认为’\’); LINES   STARTING BY 'string' (每行前都加此字符串,默认''); LINES   TERMINATED BY 'string'(行结束符,默认为’\n’); 其中char表示此符号只能是单个字符,string表示可以是字符串。 例如,将emp表中数据导出为数据文本,其中,字段分隔符为“,”,字段引用符为“””(双引号),记录结束符为回车符,具体实现如下: mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," enclosed by '"' ; Query OK, 5 rows affected (0.00 sec) mysql> mysql> system more /tmp/emp.txt "1","z1","aa" "2","z1","aa" "3","z1","aa" "4","z1","aa" "1","z1","aa" 发现第一列是数值型,如果不希望字段两边用引号引起,则语句改为: mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ; Query OK, 5 rows affected (0.00 sec) mysql> system more /tmp/emp.txt 1,"z1","aa" 2,"z1","aa" 3,"z1","aa" 4,"z1","aa" 1,"z1","aa" 结果如我们所愿,第一列的双引号被去掉。 下面来测试一下转义字符。转义字符,顾名思义,就是由于含义模糊而需要特殊进行转换的字符,在不同的情况下,需要转义的字符是不一样的。MySQL导出的数据中需要转义的字符主要包括以下3类: l 转义字符本身; l 字段分隔符; l 记录分隔符。 在下面的例子中,对表emp中的name更新为含“\”,字段分隔符,记录分隔符的数据,然后导出: mysql> update emp set name='\\"##!aa' where id=1; Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> system rm /tmp/emp.txt mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ; 第 页 共 18 页 Query OK, 5 rows affected (0.00 sec) mysql> system more /tmp/emp.txt 1,"\\\"##!aa","aa" 2,"z1","aa" 3,"z1","aa" 4,"z1","aa" 1,"\\\"##!aa","aa" mysql> 以上例子中,name中含有转义字符本身“\”,域引用符“””,因此,在输出的数据中我们发现这两种字符前面都加上了转义字符“\”,“\#”变成了“\\\#”。继续进行测试,将id为1的name更新为含有字段分隔符“,”的字符串: mysql> update emp set name='\\"#,#,!aa' where id=1; Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> system rm /tmp/emp.txt mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ; Query OK, 5 rows affected (0.00 sec) mysql> system more /tmp/emp.txt 1,"\\\"#,#,!aa","aa" 2,"z1","aa" 3,"z1","aa" 4,"z1","aa" 1,"\\\"#,#,!aa","aa" 注意:在MySQL客户端连接成功后,如果要执行操作系统的命令,可以用“system+操作系统命令”来进行执行。 这个时候,发现数据中的字符“,”并没有被转义,这是为什么呢?其实仔细想想就明白了,因为每个字符串的两边带有引用符“"”(双引号),所以当MySQL看到数据中的“,”时,由于它处在前半个引用分隔符之后,后半个引用分隔符之前,所以并没有将它作为字段分隔符,而只是作为普通的一个数据字符来对待,因而不需要转义。继续做测试,将输出文件的字段引用符去掉,这个时候,我们的预期是数据中的“,”将成为转义字符而需要加上“\”: mysql> system rm /tmp/emp.txt mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," ; Query OK, 5 rows affected (0.00 sec) mysql> system more /tmp/emp.txt 1,\\"#\,#\,!aa,aa 2,z1,aa 3,z1,aa 4,z1,aa 第 页 共 18 页 1,\\"#\,#\,!aa,aa 果然,现在的“,”前面加上了转义字符“\”。而刚才的引用符“””却没有被转义,因为它已经没有什么歧义,不需要被转义。 通过上面的测试,可以得出以下结论: l 当导出命令中包含字段引用符时,数据中含有转义字符本身和字段引用符的字符需要被转义; l 当导出命令中不包含字段引用符时,数据中含有转义字符本身和字段分隔符的字符需要被转义。 注意:SELECT…INTO OUTFILE...产生的输出文件如果在目标目录下有重名文件,将不会创建成功,源文件不能被自动覆盖。 方法2:用mysqldump导出数据为文本。 mysqldump –u username –T target_dir dbname tablename [option] 其中option参数可以是以下选项: ¡ --fields-terminated-by=name(字段分隔符); ¡ --fields-enclosed-by=name(字段引用符); ¡ --fields-optionally-enclosed-by=name(字段引用符,只用在char、varchar和text等字符型字段上); ¡ --fields-escaped-by=name(转义字符); ¡ --lines-terminated-by=name(记录结束符)。 下面的例子中,采用mysqldump生成了指定分隔符分隔的文本: [root@localhost tmp]# mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' --fields-optionally-enclosed-by '"' [root@localhost tmp]# more /tmp/emp.txt 1,"\\\"#,#,!aa","aa" 2,"z1","aa" 3,"z1","aa" 4,"z1","aa" 1,"\\\"#,#,!aa","aa" 除了生成数据文件emp.txt之外,还生成一个emp.sql文件,里面记录了emp表的创建脚本,记录的内容如下: [root@localhost tmp]# more emp.sql -- MySQL dump 10.11 -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.0.41-community-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' */; 第 页 共 18 页 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `emp` -- DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `id` int(11) default NULL, `name` varchar(10) default NULL, `content` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!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 2007-08-11 20:27:32 可以发现,除多了一个表的创建脚本文件外,mysqldump和SELEC…INTO OUTFILE…的选项和语法非常类似。其实,mysqldump实际调用的就是后者提供的接口,并在其上面添加了一些新的功能而已。 导入 本节只讨论用SELECT… INTO OUTFILE或者mysqldump导出的纯数据文本的导入方法。和导出类似,导入也有两种不同的方法,分别是LOAD DATA INFILE…和mysqlimport,它们的本质是一样的,区别只是在于一个在MySQL内部执行,另一个在MySQL外部执行。 方法1:使用“LOAD DATA INFILE…”命令。 mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option] option可以是以下选项: ¡ FIELDS TERMINATED BY 'string'(字段分隔符,默认为制表符'\t'); ¡ FIELDS [OPTIONALLY] ENCLOSED BY 'char'(字段引用符,如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上。默认不使用引用符); ¡ FIELDS ESCAPED BY 'char'(转义字符,默认为'\'); ¡ LINES STARTING BY 'string'(每行前都加此字符串,默认''); ¡ LINES TERMINATED BY 'string'(行结束符,默认为'\n'); ¡ IGNORE number LINES(忽略输入文件中的前n行数据); ¡ (col_name_or_user_var,...) (按照列出的字段顺序和字段数量加载数据); 第 页 共 18 页 ¡ SET col_name = expr,... 将列做一定的数值转换后再加载。 其中char表示此符号只能是单个字符,string表示可以是字符串。 FILELD和LINES和前面SELECT …INTO OUTFILE…的含义完全相同,不同的是多了几个不同的选项,下面的例子将文件“/tmp/emp.txt”中的数据加载到表emp中: mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ; Query OK, 4 rows affected (0.03 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from emp; +------+------+---------+ | id | name | content | +------+------+---------+ | 2 | z1 | aa | | 3 | z1 | aa | | 4 | z1 | aa | | 1 | z1 | aa | +------+------+---------+ 4 rows in set (0.00 sec) 如果不希望加载文件中的前两行,可以如下操作: mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines; Query OK, 2 rows affected (0.04 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from emp; +------+------+---------+ | id | name | content | +------+------+---------+ | 4 | z1 | aa | | 1 | z1 | aa | +------+------+---------+ 2 rows in set (0.00 sec) 此时数据只加载了两行,对比一下数据文件,可以发现的确只加载了后两行: mysql> system /tmp/emp.txt sh: /tmp/emp.txt: Permission denied mysql> system more /tmp/emp.txt 2,"z1","aa" 3,"z1","aa" 4,"z1","aa" 1,"z1","aa" 如果发现文件中的列顺序和表中的列顺序不符,或者只想加载部分列,可以在命令行中加上列的顺序,如下例所示: mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name); 第 页 共 18 页 Query OK, 2 rows affected (0.05 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from emp; +------+------+---------+ | id | name | content | +------+------+---------+ | 4 | aa | z1 | | 1 | aa | z1 | +------+------+---------+ 2 rows in set (0.00 sec) 可以发现,文件中第二列的内容放到了content里面,第三列的内容放到了name里面。 如果只想加载第一列,字段的列表里面可以只加第一列的名称: mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id); Query OK, 2 rows affected, 2 warnings (0.04 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 2 mysql> select * from emp; +------+------+---------+ | id | name | content | +------+------+---------+ | 4 | NULL | NULL | | 1 | NULL | NULL | +------+------+---------+ 2 rows in set (0.00 sec) 如果希望将id列的内容+10后再加载到表中,可以如下操作: mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' set id=id+10; Query OK, 4 rows affected (0.03 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from emp; +------+------+---------+ | id | name | content | +------+------+---------+ | 12 | z1 | aa | | 13 | z1 | aa | | 14 | z1 | aa | | 11 | z1 | aa | +------+------+---------+ 4 rows in set (0.00 sec) 第 页 共 18 页 方法2:用mysqlimport来实现,具体命令如下。 shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option] 其中option参数可以是以下选项: ¡ --fields-terminated-by=name(字段分隔符); ¡ --fields-enclosed-by=name(字段引用符); ¡ --fields-optionally-enclosed-by=name(字段引用符,只用在char、varchar和text等字符型字段上); ¡ --fields-escaped-by=name(转义字符); ¡ --lines-terminated-by=name(记录结束符); ¡ -- ignore-lines=number(或略前几行)。 这与mysqldump的选项几乎完全相同,这里不再详细介绍,简单来看一个例子: [root@localhost tmp]# mysqlimport -uroot test /tmp/emp.txt --fields-terminated-by=',' --fields-enclosed-by='"' test.emp: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 [root@localhost tmp]# [root@localhost tmp]# mysql -uroot test -e 'select count(10) from emp' +-----------+ | count(10) | +-----------+ | 4 | +-----------+ [root@localhost tmp]# mysql -uroot test -e 'select * from emp' +------+------+---------+ | id | name | content | +------+------+---------+ | 2 | z1 | aa | | 3 | z1 | aa | | 4 | z1 | aa | | 1 | z1 | aa | +------+------+---------+ 注意:如果导入和导出是跨平台操作的(Windows和Linux),那么要注意设置参数line-terminated-by,Windows上设置为line-terminated-by=’\r\n’,Linux上设置为line-terminated-by=’\n’。 第 页 共 18 页

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档