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

フィルターとしての Perl

フィルターとしての Perl

テキストの改行コード

perl tips

from Perl Online Document

split /PATTERN/,EXPR,LIMIT
split /PATTERN/,EXPR
split /PATTERN/
split

Splits the string EXPR into a list of strings and returns that list. By default, empty leading fields are preserved, and empty trailing ones are deleted. (If all fields are empty, they are considered to be trailing.)

If LIMIT is specified and positive, it represents the maximum number of fields the EXPR will be split into, though the actual number of fields returned depends on the number of times PATTERN matches within EXPR. If LIMIT is unspecified or zero, trailing null fields are stripped (which potential users of pop would do well to remember). If LIMIT is negative, it is treated as if an arbitrarily large LIMIT had been specified. Note that splitting an EXPR that evaluates to the empty string always returns the empty list, regardless of the LIMIT specified.


my $hoge = 'a,b,c,,';
my @foo = split /,/, $hoge; # @foo = ('a', 'b', 'c')
my @bar = split /,/, $hoge, -1; # @bar = ('a', 'b', 'c', '', '')