MySQL的LOCAL DATA INFILE命令

用户需要纪录数据库中记录的变更历史，使用update-log的话，精度太大。使用触发器的话，现行的版本（MySQL 4.0.20）又不支持。只能手动来干。

1. 每天午夜从数据库中取快照
mysqldump -unobody -pnobody
--opt
--skip-add-drop-table --skip-extended-insert --skip-add-locks
--skip-disable-keys --skip-comments
--quote-names $dbname $table_name > $dumpfile_name

2. 取出来的快照和前一天的快照进行比较，产生变更日志文件。

3. 然后把变更插入变更日志表里面。
mysql $dbname -unobody -pnobody -e
"LOAD DATA LOCAL INFILE '$diff_file'
INTO TABLE update_log
FIELDS TERMINATED BY ',' ENCLOSED BY '\\\'' LINES TERMINATED BY '\n'
(database_name,table_name,column_name,before,after)"

幸运的是，需要纪录变更的表里面没有特别复杂的列，例如，备注栏之类。所以从dump文件中分析数据要简单的多。中间比较折腾的地方也少了，不过还是有两个地方折腾人。

1. 默认从数据库中dump出来的列的值是用单引号括起来了，但是倒入用的LOAD DATA LOCAL INFILE命令的默认格式却不是单引号。指定时需要注意（ENCLOSED BY '\\\''）转义符的使用。
虽然dump的时候，可以指定文件格式。但是都会碰到下面讲到的权限问题。

2."SELECT INTO OUTFILE"和"LOAD DATA INFILE"都涉及一个file权限的问题。
The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements.

mysqldump：
--tab=path, -T path This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.

LOAD DATA LOCAL INFILE:
If LOCAL is specified, the file is read by the client program on the client host and sent to the server.
For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege.
Using LOCAL is a bit slower than letting the server access the files directly, because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.

Security Issues with LOAD DATA LOCAL

mysqldump から、綺麗なスキーマを得る方法

mysqldump -unobody -pnobody --opt --skip-add-drop-table --skip-quote-names -d |
perl -pe '
s!(.+ )(int\(|varchar\(|enum\(|datetime|timestamp)!sprintf("%-20s%s", $1, $2)!ge;
s!(.+)(NOT NULL)!sprintf "%-40s%s", $1, $2!eg;
s!(.+)( default )!sprintf("%-60s%s", $1, $2)!ge;
s!(.+)( auto_increment)!sprintf("%-60s%s", $1, $2)!ge
'

-d : --no-data