¼«»ª(¼«Í³ÅÙ¹â)¤ò»È¤¦¤³¤È¤¬Â¿¤¤¤¬¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Î¥æ¡¼¥¶¡¼¤«¤é¼ÁÌä¤ò¼õ¤±¤¿¤Î¤Ç¥á¥â¡£
WEB¥µ¡¼¥Ð¡¼Æâ¤Ëcsv¥Õ¥¡¥¤¥ë¤òÃÖ¤¤¤Æ¡¢¤½¤ì¤òDB(MySQL)¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë¡£
phpMyAdmin¤Î¥¤¥ó¥Ý¡¼¥Èµ¡Ç½¤ò»È¤¨¤Ð²Äǽ¤À¤¬¡¢php¥×¥í¥°¥é¥à¤Ç¼Â¹Ô¤·¤¿¤¤¡£
¤È¤Ê¤ë¤È¡¢LOAD DATA LOCAL INFILE¤¬¹Í¤¨¤é¤ì¤ë¡£
¡ LOAD DATA INFILE
¥¤¥ó¥Ý¡¼¥ÈÀè¤ÎDB¤¬¤¢¤ë¥µ¡¼¥Ð¡¼¤Ëcsv¥Õ¥¡¥¤¥ë¤¬¤¢¤ë¾ì¹ç
¢ LOAD DATA LOCAL INFILE
¥¯¥é¥¤¥¢¥ó¥È¤Ë¤¢¤ëcsv¥Õ¥¡¥¤¥ë¤ò¥µ¡¼¥Ð¡¼¤ÎDB¤Ø
MySQLÁ´µ¡Ç½¥Ð¥¤¥Ö¥ë¤Î381¥Ú¡¼¥¸
¤³¤Î¾ì¹ç¤Ï¡¤¬»ÈÍѲÄǽ¤È»×¤¨¤ë¤¬¡¢csv¥Õ¥¡¥¤¥ë¤Î¤¢¤ë¥µ¡¼¥Ð¡¼(WEB¥µ¡¼¥Ð¡¼)¤ÈMySQL¤Î¤¢¤ë¥µ¡¼¥Ð¡¼(DB¥µ¡¼¥Ð¡¼)¤¬Ê̤ξì¹ç(¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥ÈÅù)¤Ï¡¢¤³¤ÎÊýË¡¤Ç¤ÏÉÔ²Äǽ¤Ç¤Ï¡©
¥Í¥Ã¥È¤ÇÄ´¤Ù¤ë¤È¡¢À®¸ù¤·¤¿¤È¤«¤·¤Ê¤«¤Ã¤¿¤È¤«¡¢mysqlimport¤ÇÀ®¸ù¤·¤¿Åù¤Î¾ðÊ󤬤¢¤ë¤¬¡¢Æ±¤¸¥ì¥ó¥¿¥ë¥µ¡¼¥Ð¡¼¶È¼Ô¤Ç¤â¡¢DB¤Î¥Ð¡¼¥¸¥ç¥ó¤äÀßÄ꤬°Û¤Ê¤ë¤Î¤Ç¡¢¼ÂºÝ¤Ï¼«Ê¬¤Ç»î¤·¤Æ¤ß¤Ê¤¤¤È¤Ê¤é¤Ê¤¤¡£
¤Þ¤¿¡¢¾ðÊó¤Î»þ´ü¤ËÃí°Õ¡£
°ÊÁ°¤Ï͸ú¤Ç¤â¡¢º£¤Ï̵¸ú¤È¤¤¤¦¤³¤È¤¬¤¢¤ë¤«¤é¤À¡£
phpMyAdmin¤Ç¤Î¥¤¥ó¥Ý¡¼¥È¤Ç¤Ï¡¢ºÇÂ祵¥¤¥º¤ÎÀ©¸Â¤¬¤¢¤ë(ºÇĹ¡§16MiB)¡£
¼«¸Ê´ÉÍý¤Î¥í¡¼¥«¥ë¥µ¡¼¥Ð¡¼Åù¡¢¼«Í³Å٤Τ¢¤ë¾ì¹ç¤Ï¾å¸ÂÃͤòÊѹ¹¤Ç¤¤ë¤¬¡¢¥ì¥ó¥¿¥ë¥µ¡¼¥Ð¡¼¤Î¾ì¹ç¤Ïº¤Æñ¤À¤í¤¦¡£
¤½¤Î¤è¤¦¤Ê¾ì¹ç¡¢FTP¤Çcsv¥Õ¥¡¥¤¥ë¤ò¾å¤²¤Æ¤ª¤¤¤Æ¡¢°Ê²¼¤ÎÊýË¡¤ÇDB¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë¤È¤è¤¤¤À¤í¤¦¡£
php¤Ç¥×¥í¥°¥é¥à¤ò½ñ¤¤¤Æ¤ª¤¯¤È¡¢¥³¥Þ¥ó¥ÉÂǤ¿¤Ê¤¯¤Æ¤â¤¤¤¤¤·¤Í¡£
°Ê²¼¡¢¼ê½ç¡£
WEB¥µ¡¼¥Ð¡¼¾å¤Î /home/aaa/www/ ¤Ë test.csv ¤òÃÖ¤¯¡£
aaa¤Ï¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Ç¤Î¥¢¥«¥¦¥ó¥È̾¡£
test.csv¤ÎÃæ¿È¤Ï
1,first
2,second
3,third
4,forth
5,fifth
¤È¤¤¤¦¡¢¥«¥ó¥Þ¶èÀÚ¤ê¤Î´Êñ¤Ê¤â¤Î¡£
mysql***.db.sakura.ne.jp - aaa_xxx - test
¤Ë¡¢°Ê²¼¤Î¤è¤¦¤Ê¥Æ¡¼¥Ö¥ë(test)¤òºîÀ®¤·¤Æ¤ª¤¯¡£
¡¦id int
¡¦name text
***¤Ï¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤«¤é³ä¤êÅö¤Æ¤é¤ì¤¿DB¤ÎÈֹ档
aaa_xxx¤ÏDB̾¤À¤¬¡¢É¬¤ºaaa_¤¬Éղ䵤ì¤ë¤Î¤Ï¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Î»ÅÍÍ¡£
WEB¥µ¡¼¥Ð¡¼¤Îtest.csv¤ÈƱ¤¸¥Ç¥£¥ì¥¯¥È¥ê¤Ë
¡ã?PHP
$import = "mysqlimport -h mysql***.db.sakura.ne.jp -u aaa -p(¥Ñ¥¹¥ï¡¼¥É) -L --fields-terminated-by=',' aaa_xxx /home/aaa/www/test.csv" ;
system($import) ;
?¡ä
¤È¤¤¤¦php¥Õ¥¡¥¤¥ë(import.php¤È¤¹¤ë)¤òÃÖ¤¡¢¤³¤ì¤ò¼Â¹Ô¡£
¡¦-h mysql***.db.sakura.ne.jp¡¡¥Û¥¹¥È
¡¦-u aaa¡¡DB¥¢¥¯¥»¥¹ÍѤΥ桼¥¶Ì¾
¡¦-p(¥Ñ¥¹¥ï¡¼¥É)¡¡DB¥¢¥¯¥»¥¹ÍѤΥѥ¹¥ï¡¼¥É¡¢-p¤Ë³¤±¤Æ(¥¹¥Ú¡¼¥¹¤Ê¤·¤Ë)½ñ¤¯¤³¤È
¡¦-L¡¡¥í¡¼¥«¥ë¥Õ¥¡¥¤¥ë¤«¤éÆɤ߹þ¤à¥ª¥×¥·¥ç¥ó
¡¦--fields-terminated-by=','¡¡csv¥Õ¥¡¥¤¥ë¤Î¶èÀÚʸ»ú¡¢º£²ó¤Ï¡Ö,(Ⱦ³Ñ¥«¥ó¥Þ)¡×
¡¦aaa_xxx¡¡DB̾
¡¦/home/aaa/www/test.csv¡¡csv¥Õ¥¡¥¤¥ë¤Î¾ì½ê
¤¹¤ë¤È¡¢
aaa_xxx.test: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
¤Èɽ¼¨¤µ¤ì¡¢°Ê²¼¤Î¤è¤¦¤Ë¥Ç¡¼¥¿¤¬³ÊǼ¤µ¤ì¤ë¡£
csv¤ÎÆâÍƤ¬³ÊǼ¤µ¤ì¤Æ¤¤¤ë
¤µ¤Æ¡¢¾å¤Îmysqlimport¡Á¤Ë¤Æ¡¢DB̾¤Î»ØÄê¤Ï¤¢¤ë¤¬¡¢¥Æ¡¼¥Ö¥ë̾¤Î»ØÄê¤Ï¤Ê¤¤¤Î¤«¡©
¤Èµ¿Ìä¤Ë»×¤¦¤¬¡¢test.csv¤Î³ÈÄ¥»Ò¤ò¼è¤Ã¤¿¤â¤Î¤¬¡¢¥Æ¡¼¥Ö¥ë̾¤Ê¤Î¤À¡£
¤Ä¤Þ¤ê¡¢¥Õ¥¡¥¤¥ë¤¬test.txt¤Î¾ì¹ç¤â¡¢test¤¬¥Æ¡¼¥Ö¥ë̾¡£
¥Õ¥¡¥¤¥ë̾¤È¥Æ¡¼¥Ö¥ë̾¤¬°Û¤Ê¤ë¾ì¹ç¤Ï¡©
import.php¤ò¥ê¥í¡¼¥É¤¹¤ë¤È¡¢¥Ç¡¼¥¿¤¬¡ÖÄɵ¡×¤µ¤ì¤Æ¤·¤Þ¤¦¡£
2²ó³ÊǼ¤µ¤ì¤Æ¤¤¤ë
¤³¤ì¤òËɤ°¤Ë¤Ï¡¢
--delete¡¡¥¤¥ó¥Ý¡¼¥È¤¹¤ëÁ°¤Ë¥Æ¡¼¥Ö¥ë¤ò¶õ¤Ë¤¹¤ë
¤Î¥ª¥×¥·¥ç¥ó¤òÄɲ乤롣
¤³¤ì¤Ç¡¢³ÊǼÁ°¤Ë¶õ¤Ë¤·¤Æ¤¯¤ì¤ë¤Î¤Ç¡¢Äɵ¤Ï²óÈò¤Ç¤¤ë¡£
¤Ê¤ª¡¢¸¶°ø¤ÏÉÔÌÀ¤À¤¬ -D ¤Ç¤Ï¥¨¥é¡¼¤È¤Ê¤ë(¥¨¥é¡¼É½¼¨¤Ï¤Ê¤¤¤¬³ÊǼ¤µ¤ì¤Ê¤¤)¡£
--delete ¤Ê¤éÌäÂê¤Ê¤·¡£
--¥ª¥×¥·¥ç¥ó¤ËÂФ·¡¢-¾Êά·Á¡¢¤Ä¤Þ¤ê¡¢
--delete ¤È -D ¤ÏƱ°Õ¤È¤¤¤¦Íý²ò¤Ï´Ö°ã¤Ã¤Æ¤¤¤ë¤Î¤«¡©
´ØÏ¢¡§mysqlimport --help¤ÎÆâÍÆ csv¥Õ¥¡¥¤¥ë¤ÎÆâÍƤò¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë
¹àÌܤζèÀÚ¤ê¤Ï¡Ö,(¥«¥ó¥Þ)¡×¤Ç»ØÄꤷ¤Æ¤¤¤ë¤¬¡¢¥ì¥³¡¼¥É¤Î¶èÀÚ¤ê¤Ï¡Ö--lines-terminated-by="¡ü"¡×¤Ç¹Ô¤¦¡£
Î㤨¤Ð¡¢¹ÔƬ¤Ë¡Ö^¡×¤¬Æþ¤Ã¤Æ¤¤¤ë(¹ÔËö¤Ç¤â¤è¤¤)¡¢°Ê²¼¤Î¤è¤¦¤Ê¥Ç¡¼¥¿¤Î¾ì¹ç¡¢
^1,aaa,xxx
^2,bbb,yyy
^3,ccc,ddd
¡Ö--lines-terminated-by="^"¡×¤È¤¹¤ë¤³¤È¤Ç¡¢²þ¹ÔÉ÷¤Ë°·¤¨¤ë¡£
¤Ä¤Þ¤ê¡¢°Ê²¼¤Î¤è¤¦¤Ë²þ¹Ô¤¬¤Ê¤¤¾ì¹ç¤Ç¤â¡¢
^1,aaa,xxx^2,bbb,yyy^3,ccc,ddd
¡Ö--lines-terminated-by="^"¡×¤ÇÀڤ뤳¤È¤¬²Äǽ¤À¡£
1·ï¤Î¥ì¥³¡¼¥É¤¬Ã»¤¤¾ì¹ç¤Ï¡¢²þ¹Ô¤Ë¤è¤Ã¤Æ¥ì¥³¡¼¥É¤Î½ªÃ¼¤¬¸«¤¨¤ë¤¬¡¢1·ï¤Î¥ì¥³¡¼¥É¤¬Èó¾ï¤ËŤ¤¾ì¹ç¡¢¤É¤³¤Þ¤Ç¤¬¥ì¥³¡¼¥É¤Î¶èÀڤ꤫ʬ¤«¤é¤Ê¤¯¤Ê¤ë¤Î¤Ç¡¢¶èÀÚ¤êʸ»ú¤òÆþ¤ì¤Æ¤ª¤¯¤È¡¢Æ°ºî³Îǧ¤ÎºÝ¤Ë¤âÌòΩ¤Ä¡£
¥ª¥×¥·¥ç¥ó¤Ë´Ø¤·¤Æ¤Ï¡¢°Ê²¼¤ò»²¾È¤Î¥³¥È¡£
´ØÏ¢¡§mysqlimport --help¤ÎÆâÍÆ csv¥Õ¥¡¥¤¥ë¤ÎÆâÍƤò¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë
¤³¤Îphp¥Õ¥¡¥¤¥ë¤òÄê´üŪ¤Ë¼Â¹Ô¤¹¤ë¤Ë¤Ï¡¢cron¤ËÅÐÏ¿¤¹¤ì¤Ð¤è¤¤¡£
cd /home/aaa/www ; /usr/local/bin/php test.php > /dev/null
test.php¤Î¤¢¤ë¥Ç¥£¥ì¥¯¥È¥ê ; PHP¤Î¥³¥Þ¥ó¥É¥Ñ¥¹ ¥×¥í¥°¥é¥à¤òµ¤·¤¿php¥Õ¥¡¥¤¥ë ¥á¡¼¥ëÀßÄê
ºÇ¸å¤Î ¡ä/dev/null ¤Ï postmaster@aaa.sakura.ne.jp ¤Ø¥á¡¼¥ë¤òÁ÷¤é¤Ê¤¤¾ì¹ç¤Ë½ñ¤¯¡£
¤½¤·¤Æ¡¢cron¤¬¼Â¹Ô¤Ç¤¤ë¤è¤¦¤Ë¡¢test.php¤Î¥Ñ¡¼¥ß¥Ã¥·¥ç¥ó¤ò755¤ËÀßÄê¡£
ÀßÄê¸å¤Ï¡¢»ØÄꤷ¤¿´Ö³Ö¤ä»þ´Ö¤Ë¡¢ÌäÂê¤Ê¤¯DB¤Ë³ÊǼ¤µ¤ì¤ë¤«¤ò³Îǧ¤¹¤ë¡£
¥á¡¼¥ë¤òÁ÷¤ëÀßÄê¤Ç¤¢¤ì¤Ð Permission denied Åù¤Î¥¨¥é¡¼¤¬ postmaster@aaa.sakura.ne.jp ¤ËÁ÷¤é¤ì¤ë¤Î¤Ç¡¢³Î¼Â¤ËÆ°¤¯¤³¤È¤ò³Îǧ¤¹¤ë¤Þ¤Ç¤Ï ¡ä/dev/null ¤òÉÕ¤±¤Ê¤¤Êý¤¬¤¤¤¤¡£
cron¤Ï5¸Ä¤Þ¤Ç¤·¤«ÀßÄê¤Ç¤¤Ê¤¤¤è¤¦¤À¤¬¡¢¥³¥ó¥È¥í¡¼¥ë¥Ñ¥Í¥ë¤«¤é¤Ç¤Ï¤Ê¤¯¡¢¥µ¡¼¥Ð¡¼¤ËÀܳ¤·¥³¥Þ¥ó¥É¤Ç¤ä¤ì¤Ð¡¢5¸Ä°Ê¾å²Äǽ¡©
¾åµ¤òcron¤Ç¼Â¹Ô¤·¤¿»þ¡¢Æ°ºî¤ÏÀ®¸ù¤¹¤ë¤¬¡¢
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
¤È¤¤¤¦¥á¡¼¥ë¤¬¡¢´ÉÍý¼Ô°¸¤ËÆϤ¯¤³¤È¤¬¤¢¤ë¡£
cron¤Ë¡Ö1> /dev/null¡×¤òÉÕ¤±¤Æ¤¤¤¿¤È¤·¤Æ¤â¤À¡£
¥³¥Þ¥ó¥É¥é¥¤¥ó¤Ë¥Ñ¥¹¥ï¡¼¥É¤ò´Þ¤ó¤Ç¤¤¤ë¤Î¤Ç¡¢°ÂÁ´¤Ç¤Ï¤Ê¤¤¤È¤¤¤¦¥³¥È¤À¡£
¤³¤ì¤ò²óÈò¤¹¤ë¤Ë¤Ï¡¢¥Ñ¥¹¥ï¡¼¥É¤òÊÌ¥Õ¥¡¥¤¥ë¤Ëµ¤·¤ÆÃÖ¤¡¢¥³¥Þ¥ó¥É¥é¥¤¥ó¤Ç¥½¥ì¤òÆɤ߼è¤ì¤Ð¤è¤¤¡£
¥Ñ¥¹¥ï¡¼¥É¤À¤±¤òÊÌ¥Õ¥¡¥¤¥ë¤Ë¤¹¤ì¤Ð¡Ö°ÂÁ´¤Ç¤Ï¤Ê¤¤¡×¤È¤Ï¸À¤ï¤ì¤Ê¤¯¤Ê¤ë¤¬¡¢¤É¤¦¤»¤Ê¤é¡¢¥æ¡¼¥¶¡¼Ì¾¤È¥Û¥¹¥È¤âÊÌ¥Õ¥¡¥¤¥ë¤Ë¤·¤¿Êý¤¬¡¢¤è¤ê°ÂÁ´¤À¤í¤¦¡£
¤Þ¤º¡¢°Ê²¼¤Î¥í¥°¥¤¥ó¾ðÊó¤òµ¤·¤¿¥Õ¥¡¥¤¥ë¤ò¡¢login.conf(̾¾Î¤ÏǤ°Õ)¤È¤·¤ÆÊݸ¤¹¤ë¡£
¡Ý¡Ý¡Ý¡Ý¡Ý
[client]
user = ¥æ¡¼¥¶¡¼Ì¾
password = ¥Ñ¥¹¥ï¡¼¥É
host = ¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥Û¥¹¥È
¡Ý¡Ý¡Ý¡Ý¡Ý
ÅöÁ³¡¢login.conf¤Ï°ÂÁ´¤Ê¾ì½ê¤ËÀßÃÖ¤¹¤ëɬÍפ¬¤¢¤ë¡£
¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Ê¤é¡¢
/home/¥æ¡¼¥¶¡¼Ì¾/www
¤Îwww¤ÎÃ椬¸ø³«¥Ç¥£¥ì¥¯¥È¥ê¤À¤¬¡¢¤½¤Î¾å(www¤ÎÃæ¤Ç¤Ï¤Ê¤¯Æ±³¬ÁØ)¤ËÃÖ¤±¤Ð°ÂÁ´¤À¤í¤¦¡£
¤µ¤é¤Ë¡¢¥Õ¥¡¥¤¥ë¤Î¥Ñ¡¼¥ß¥Ã¥·¥ç¥ó¤ò400(½êͼԤÎÆɤ߼è¤ê¤Î¤ß)¤Ë¥·¥Æ¥ª¥¯¡£
¤½¤·¤Æ¡¢¾å½Ò¤Î¥³¥Þ¥ó¥É¥é¥¤¥ó¤«¤é¡Ö-h(¥Û¥¹¥È) -u(¥æ¡¼¥¶¡¼) -p(¥Ñ¥¹¥ï¡¼¥É)¡×¤òºï½ü¤·¡¢Âå¤ï¤ê¤Ë
--defaults-extra-file=/home/¥æ¡¼¥¶¡¼Ì¾/login.conf
¤òÄɲ乤롣
¤½¤¦¤¹¤ì¤Ð¡¢¥³¥Þ¥ó¥É¥é¥¤¥ó¤Ë¥í¥°¥¤¥ó¾ðÊó¤ò´Þ¤Þ¤Ê¤¤¤Î¤Ç¡¢°ÂÁ´¤Ç¤Ï¤Ê¤¤¤È¤Ï¥¤¥ï¥ì¥Ê¤¯¤Ê¤ë¡£
´ØÏ¢¡§¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤ÎCRON¤ÎÀßÄê²èÌÌ(1.CRON¤ÎÀßÄê)
´ØÏ¢¡§¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤ÎCRON¤ÎÀßÄê²èÌÌ(2.CRON¥¹¥±¥¸¥å¡¼¥ëÀßÄê)
[PR] au PAY / au WALLET ¥«¡¼¥É ¾ðÊó
WEB¥µ¡¼¥Ð¡¼Æâ¤Ëcsv¥Õ¥¡¥¤¥ë¤òÃÖ¤¤¤Æ¡¢¤½¤ì¤òDB(MySQL)¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë¡£
phpMyAdmin¤Î¥¤¥ó¥Ý¡¼¥Èµ¡Ç½¤ò»È¤¨¤Ð²Äǽ¤À¤¬¡¢php¥×¥í¥°¥é¥à¤Ç¼Â¹Ô¤·¤¿¤¤¡£
¤È¤Ê¤ë¤È¡¢LOAD DATA LOCAL INFILE¤¬¹Í¤¨¤é¤ì¤ë¡£
¡ LOAD DATA INFILE
¥¤¥ó¥Ý¡¼¥ÈÀè¤ÎDB¤¬¤¢¤ë¥µ¡¼¥Ð¡¼¤Ëcsv¥Õ¥¡¥¤¥ë¤¬¤¢¤ë¾ì¹ç
¢ LOAD DATA LOCAL INFILE
¥¯¥é¥¤¥¢¥ó¥È¤Ë¤¢¤ëcsv¥Õ¥¡¥¤¥ë¤ò¥µ¡¼¥Ð¡¼¤ÎDB¤Ø
MySQLÁ´µ¡Ç½¥Ð¥¤¥Ö¥ë¤Î381¥Ú¡¼¥¸
¤³¤Î¾ì¹ç¤Ï¡¤¬»ÈÍѲÄǽ¤È»×¤¨¤ë¤¬¡¢csv¥Õ¥¡¥¤¥ë¤Î¤¢¤ë¥µ¡¼¥Ð¡¼(WEB¥µ¡¼¥Ð¡¼)¤ÈMySQL¤Î¤¢¤ë¥µ¡¼¥Ð¡¼(DB¥µ¡¼¥Ð¡¼)¤¬Ê̤ξì¹ç(¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥ÈÅù)¤Ï¡¢¤³¤ÎÊýË¡¤Ç¤ÏÉÔ²Äǽ¤Ç¤Ï¡©
¥Í¥Ã¥È¤ÇÄ´¤Ù¤ë¤È¡¢À®¸ù¤·¤¿¤È¤«¤·¤Ê¤«¤Ã¤¿¤È¤«¡¢mysqlimport¤ÇÀ®¸ù¤·¤¿Åù¤Î¾ðÊ󤬤¢¤ë¤¬¡¢Æ±¤¸¥ì¥ó¥¿¥ë¥µ¡¼¥Ð¡¼¶È¼Ô¤Ç¤â¡¢DB¤Î¥Ð¡¼¥¸¥ç¥ó¤äÀßÄ꤬°Û¤Ê¤ë¤Î¤Ç¡¢¼ÂºÝ¤Ï¼«Ê¬¤Ç»î¤·¤Æ¤ß¤Ê¤¤¤È¤Ê¤é¤Ê¤¤¡£
¤Þ¤¿¡¢¾ðÊó¤Î»þ´ü¤ËÃí°Õ¡£
°ÊÁ°¤Ï͸ú¤Ç¤â¡¢º£¤Ï̵¸ú¤È¤¤¤¦¤³¤È¤¬¤¢¤ë¤«¤é¤À¡£
phpMyAdmin¤Ç¤Î¥¤¥ó¥Ý¡¼¥È¤Ç¤Ï¡¢ºÇÂ祵¥¤¥º¤ÎÀ©¸Â¤¬¤¢¤ë(ºÇĹ¡§16MiB)¡£
¼«¸Ê´ÉÍý¤Î¥í¡¼¥«¥ë¥µ¡¼¥Ð¡¼Åù¡¢¼«Í³Å٤Τ¢¤ë¾ì¹ç¤Ï¾å¸ÂÃͤòÊѹ¹¤Ç¤¤ë¤¬¡¢¥ì¥ó¥¿¥ë¥µ¡¼¥Ð¡¼¤Î¾ì¹ç¤Ïº¤Æñ¤À¤í¤¦¡£
¤½¤Î¤è¤¦¤Ê¾ì¹ç¡¢FTP¤Çcsv¥Õ¥¡¥¤¥ë¤ò¾å¤²¤Æ¤ª¤¤¤Æ¡¢°Ê²¼¤ÎÊýË¡¤ÇDB¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë¤È¤è¤¤¤À¤í¤¦¡£
php¤Ç¥×¥í¥°¥é¥à¤ò½ñ¤¤¤Æ¤ª¤¯¤È¡¢¥³¥Þ¥ó¥ÉÂǤ¿¤Ê¤¯¤Æ¤â¤¤¤¤¤·¤Í¡£
°Ê²¼¡¢¼ê½ç¡£
csv¥Õ¥¡¥¤¥ë¤Î½àÈ÷
WEB¥µ¡¼¥Ð¡¼¾å¤Î /home/aaa/www/ ¤Ë test.csv ¤òÃÖ¤¯¡£
aaa¤Ï¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Ç¤Î¥¢¥«¥¦¥ó¥È̾¡£
test.csv¤ÎÃæ¿È¤Ï
1,first
2,second
3,third
4,forth
5,fifth
¤È¤¤¤¦¡¢¥«¥ó¥Þ¶èÀÚ¤ê¤Î´Êñ¤Ê¤â¤Î¡£
DB¤Î½àÈ÷
mysql***.db.sakura.ne.jp - aaa_xxx - test
¤Ë¡¢°Ê²¼¤Î¤è¤¦¤Ê¥Æ¡¼¥Ö¥ë(test)¤òºîÀ®¤·¤Æ¤ª¤¯¡£
¡¦id int
¡¦name text
***¤Ï¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤«¤é³ä¤êÅö¤Æ¤é¤ì¤¿DB¤ÎÈֹ档
aaa_xxx¤ÏDB̾¤À¤¬¡¢É¬¤ºaaa_¤¬Éղ䵤ì¤ë¤Î¤Ï¡¢¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Î»ÅÍÍ¡£
¥³¥Þ¥ó¥É¤òµ¤·¤¿php¥Õ¥¡¥¤¥ë¤òºîÀ®¡¢¼Â¹Ô
WEB¥µ¡¼¥Ð¡¼¤Îtest.csv¤ÈƱ¤¸¥Ç¥£¥ì¥¯¥È¥ê¤Ë
¡ã?PHP
$import = "mysqlimport -h mysql***.db.sakura.ne.jp -u aaa -p(¥Ñ¥¹¥ï¡¼¥É) -L --fields-terminated-by=',' aaa_xxx /home/aaa/www/test.csv" ;
system($import) ;
?¡ä
¤È¤¤¤¦php¥Õ¥¡¥¤¥ë(import.php¤È¤¹¤ë)¤òÃÖ¤¡¢¤³¤ì¤ò¼Â¹Ô¡£
¡¦-h mysql***.db.sakura.ne.jp¡¡¥Û¥¹¥È
¡¦-u aaa¡¡DB¥¢¥¯¥»¥¹ÍѤΥ桼¥¶Ì¾
¡¦-p(¥Ñ¥¹¥ï¡¼¥É)¡¡DB¥¢¥¯¥»¥¹ÍѤΥѥ¹¥ï¡¼¥É¡¢-p¤Ë³¤±¤Æ(¥¹¥Ú¡¼¥¹¤Ê¤·¤Ë)½ñ¤¯¤³¤È
¡¦-L¡¡¥í¡¼¥«¥ë¥Õ¥¡¥¤¥ë¤«¤éÆɤ߹þ¤à¥ª¥×¥·¥ç¥ó
¡¦--fields-terminated-by=','¡¡csv¥Õ¥¡¥¤¥ë¤Î¶èÀÚʸ»ú¡¢º£²ó¤Ï¡Ö,(Ⱦ³Ñ¥«¥ó¥Þ)¡×
¡¦aaa_xxx¡¡DB̾
¡¦/home/aaa/www/test.csv¡¡csv¥Õ¥¡¥¤¥ë¤Î¾ì½ê
¤¹¤ë¤È¡¢
aaa_xxx.test: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
¤Èɽ¼¨¤µ¤ì¡¢°Ê²¼¤Î¤è¤¦¤Ë¥Ç¡¼¥¿¤¬³ÊǼ¤µ¤ì¤ë¡£
csv¤ÎÆâÍƤ¬³ÊǼ¤µ¤ì¤Æ¤¤¤ë
¤µ¤Æ¡¢¾å¤Îmysqlimport¡Á¤Ë¤Æ¡¢DB̾¤Î»ØÄê¤Ï¤¢¤ë¤¬¡¢¥Æ¡¼¥Ö¥ë̾¤Î»ØÄê¤Ï¤Ê¤¤¤Î¤«¡©
¤Èµ¿Ìä¤Ë»×¤¦¤¬¡¢test.csv¤Î³ÈÄ¥»Ò¤ò¼è¤Ã¤¿¤â¤Î¤¬¡¢¥Æ¡¼¥Ö¥ë̾¤Ê¤Î¤À¡£
¤Ä¤Þ¤ê¡¢¥Õ¥¡¥¤¥ë¤¬test.txt¤Î¾ì¹ç¤â¡¢test¤¬¥Æ¡¼¥Ö¥ë̾¡£
¥Õ¥¡¥¤¥ë̾¤È¥Æ¡¼¥Ö¥ë̾¤¬°Û¤Ê¤ë¾ì¹ç¤Ï¡©
import.php¤ò¥ê¥í¡¼¥É¤¹¤ë¤È¡¢¥Ç¡¼¥¿¤¬¡ÖÄɵ¡×¤µ¤ì¤Æ¤·¤Þ¤¦¡£
2²ó³ÊǼ¤µ¤ì¤Æ¤¤¤ë
¤³¤ì¤òËɤ°¤Ë¤Ï¡¢
--delete¡¡¥¤¥ó¥Ý¡¼¥È¤¹¤ëÁ°¤Ë¥Æ¡¼¥Ö¥ë¤ò¶õ¤Ë¤¹¤ë
¤Î¥ª¥×¥·¥ç¥ó¤òÄɲ乤롣
¤³¤ì¤Ç¡¢³ÊǼÁ°¤Ë¶õ¤Ë¤·¤Æ¤¯¤ì¤ë¤Î¤Ç¡¢Äɵ¤Ï²óÈò¤Ç¤¤ë¡£
¤Ê¤ª¡¢¸¶°ø¤ÏÉÔÌÀ¤À¤¬ -D ¤Ç¤Ï¥¨¥é¡¼¤È¤Ê¤ë(¥¨¥é¡¼É½¼¨¤Ï¤Ê¤¤¤¬³ÊǼ¤µ¤ì¤Ê¤¤)¡£
--delete ¤Ê¤éÌäÂê¤Ê¤·¡£
--¥ª¥×¥·¥ç¥ó¤ËÂФ·¡¢-¾Êά·Á¡¢¤Ä¤Þ¤ê¡¢
--delete ¤È -D ¤ÏƱ°Õ¤È¤¤¤¦Íý²ò¤Ï´Ö°ã¤Ã¤Æ¤¤¤ë¤Î¤«¡©
´ØÏ¢¡§mysqlimport --help¤ÎÆâÍÆ csv¥Õ¥¡¥¤¥ë¤ÎÆâÍƤò¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë
¥ì¥³¡¼¥É¤Î¶èÀÚ¤ê
¹àÌܤζèÀÚ¤ê¤Ï¡Ö,(¥«¥ó¥Þ)¡×¤Ç»ØÄꤷ¤Æ¤¤¤ë¤¬¡¢¥ì¥³¡¼¥É¤Î¶èÀÚ¤ê¤Ï¡Ö--lines-terminated-by="¡ü"¡×¤Ç¹Ô¤¦¡£
Î㤨¤Ð¡¢¹ÔƬ¤Ë¡Ö^¡×¤¬Æþ¤Ã¤Æ¤¤¤ë(¹ÔËö¤Ç¤â¤è¤¤)¡¢°Ê²¼¤Î¤è¤¦¤Ê¥Ç¡¼¥¿¤Î¾ì¹ç¡¢
^1,aaa,xxx
^2,bbb,yyy
^3,ccc,ddd
¡Ö--lines-terminated-by="^"¡×¤È¤¹¤ë¤³¤È¤Ç¡¢²þ¹ÔÉ÷¤Ë°·¤¨¤ë¡£
¤Ä¤Þ¤ê¡¢°Ê²¼¤Î¤è¤¦¤Ë²þ¹Ô¤¬¤Ê¤¤¾ì¹ç¤Ç¤â¡¢
^1,aaa,xxx^2,bbb,yyy^3,ccc,ddd
¡Ö--lines-terminated-by="^"¡×¤ÇÀڤ뤳¤È¤¬²Äǽ¤À¡£
1·ï¤Î¥ì¥³¡¼¥É¤¬Ã»¤¤¾ì¹ç¤Ï¡¢²þ¹Ô¤Ë¤è¤Ã¤Æ¥ì¥³¡¼¥É¤Î½ªÃ¼¤¬¸«¤¨¤ë¤¬¡¢1·ï¤Î¥ì¥³¡¼¥É¤¬Èó¾ï¤ËŤ¤¾ì¹ç¡¢¤É¤³¤Þ¤Ç¤¬¥ì¥³¡¼¥É¤Î¶èÀڤ꤫ʬ¤«¤é¤Ê¤¯¤Ê¤ë¤Î¤Ç¡¢¶èÀÚ¤êʸ»ú¤òÆþ¤ì¤Æ¤ª¤¯¤È¡¢Æ°ºî³Îǧ¤ÎºÝ¤Ë¤âÌòΩ¤Ä¡£
¥ª¥×¥·¥ç¥ó¤Ë´Ø¤·¤Æ¤Ï¡¢°Ê²¼¤ò»²¾È¤Î¥³¥È¡£
´ØÏ¢¡§mysqlimport --help¤ÎÆâÍÆ csv¥Õ¥¡¥¤¥ë¤ÎÆâÍƤò¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë¥¤¥ó¥Ý¡¼¥È¤¹¤ë
cron¤ËÅÐÏ¿¡¢¼«Æ°¼Â¹Ô
¤³¤Îphp¥Õ¥¡¥¤¥ë¤òÄê´üŪ¤Ë¼Â¹Ô¤¹¤ë¤Ë¤Ï¡¢cron¤ËÅÐÏ¿¤¹¤ì¤Ð¤è¤¤¡£
cd /home/aaa/www ; /usr/local/bin/php test.php > /dev/null
test.php¤Î¤¢¤ë¥Ç¥£¥ì¥¯¥È¥ê ; PHP¤Î¥³¥Þ¥ó¥É¥Ñ¥¹ ¥×¥í¥°¥é¥à¤òµ¤·¤¿php¥Õ¥¡¥¤¥ë ¥á¡¼¥ëÀßÄê
ºÇ¸å¤Î ¡ä/dev/null ¤Ï postmaster@aaa.sakura.ne.jp ¤Ø¥á¡¼¥ë¤òÁ÷¤é¤Ê¤¤¾ì¹ç¤Ë½ñ¤¯¡£
¤½¤·¤Æ¡¢cron¤¬¼Â¹Ô¤Ç¤¤ë¤è¤¦¤Ë¡¢test.php¤Î¥Ñ¡¼¥ß¥Ã¥·¥ç¥ó¤ò755¤ËÀßÄê¡£
ÀßÄê¸å¤Ï¡¢»ØÄꤷ¤¿´Ö³Ö¤ä»þ´Ö¤Ë¡¢ÌäÂê¤Ê¤¯DB¤Ë³ÊǼ¤µ¤ì¤ë¤«¤ò³Îǧ¤¹¤ë¡£
¥á¡¼¥ë¤òÁ÷¤ëÀßÄê¤Ç¤¢¤ì¤Ð Permission denied Åù¤Î¥¨¥é¡¼¤¬ postmaster@aaa.sakura.ne.jp ¤ËÁ÷¤é¤ì¤ë¤Î¤Ç¡¢³Î¼Â¤ËÆ°¤¯¤³¤È¤ò³Îǧ¤¹¤ë¤Þ¤Ç¤Ï ¡ä/dev/null ¤òÉÕ¤±¤Ê¤¤Êý¤¬¤¤¤¤¡£
cron¤Ï5¸Ä¤Þ¤Ç¤·¤«ÀßÄê¤Ç¤¤Ê¤¤¤è¤¦¤À¤¬¡¢¥³¥ó¥È¥í¡¼¥ë¥Ñ¥Í¥ë¤«¤é¤Ç¤Ï¤Ê¤¯¡¢¥µ¡¼¥Ð¡¼¤ËÀܳ¤·¥³¥Þ¥ó¥É¤Ç¤ä¤ì¤Ð¡¢5¸Ä°Ê¾å²Äǽ¡©
insecure(°ÂÁ´¤Ç¤Ï¤Ê¤¤)¤È¸À¤ï¤ì¤ë¾ì¹ç
¾åµ¤òcron¤Ç¼Â¹Ô¤·¤¿»þ¡¢Æ°ºî¤ÏÀ®¸ù¤¹¤ë¤¬¡¢
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
¤È¤¤¤¦¥á¡¼¥ë¤¬¡¢´ÉÍý¼Ô°¸¤ËÆϤ¯¤³¤È¤¬¤¢¤ë¡£
cron¤Ë¡Ö1> /dev/null¡×¤òÉÕ¤±¤Æ¤¤¤¿¤È¤·¤Æ¤â¤À¡£
¥³¥Þ¥ó¥É¥é¥¤¥ó¤Ë¥Ñ¥¹¥ï¡¼¥É¤ò´Þ¤ó¤Ç¤¤¤ë¤Î¤Ç¡¢°ÂÁ´¤Ç¤Ï¤Ê¤¤¤È¤¤¤¦¥³¥È¤À¡£
¤³¤ì¤ò²óÈò¤¹¤ë¤Ë¤Ï¡¢¥Ñ¥¹¥ï¡¼¥É¤òÊÌ¥Õ¥¡¥¤¥ë¤Ëµ¤·¤ÆÃÖ¤¡¢¥³¥Þ¥ó¥É¥é¥¤¥ó¤Ç¥½¥ì¤òÆɤ߼è¤ì¤Ð¤è¤¤¡£
¥Ñ¥¹¥ï¡¼¥É¤À¤±¤òÊÌ¥Õ¥¡¥¤¥ë¤Ë¤¹¤ì¤Ð¡Ö°ÂÁ´¤Ç¤Ï¤Ê¤¤¡×¤È¤Ï¸À¤ï¤ì¤Ê¤¯¤Ê¤ë¤¬¡¢¤É¤¦¤»¤Ê¤é¡¢¥æ¡¼¥¶¡¼Ì¾¤È¥Û¥¹¥È¤âÊÌ¥Õ¥¡¥¤¥ë¤Ë¤·¤¿Êý¤¬¡¢¤è¤ê°ÂÁ´¤À¤í¤¦¡£
¤Þ¤º¡¢°Ê²¼¤Î¥í¥°¥¤¥ó¾ðÊó¤òµ¤·¤¿¥Õ¥¡¥¤¥ë¤ò¡¢login.conf(̾¾Î¤ÏǤ°Õ)¤È¤·¤ÆÊݸ¤¹¤ë¡£
¡Ý¡Ý¡Ý¡Ý¡Ý
[client]
user = ¥æ¡¼¥¶¡¼Ì¾
password = ¥Ñ¥¹¥ï¡¼¥É
host = ¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥Û¥¹¥È
¡Ý¡Ý¡Ý¡Ý¡Ý
ÅöÁ³¡¢login.conf¤Ï°ÂÁ´¤Ê¾ì½ê¤ËÀßÃÖ¤¹¤ëɬÍפ¬¤¢¤ë¡£
¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤Ê¤é¡¢
/home/¥æ¡¼¥¶¡¼Ì¾/www
¤Îwww¤ÎÃ椬¸ø³«¥Ç¥£¥ì¥¯¥È¥ê¤À¤¬¡¢¤½¤Î¾å(www¤ÎÃæ¤Ç¤Ï¤Ê¤¯Æ±³¬ÁØ)¤ËÃÖ¤±¤Ð°ÂÁ´¤À¤í¤¦¡£
¤µ¤é¤Ë¡¢¥Õ¥¡¥¤¥ë¤Î¥Ñ¡¼¥ß¥Ã¥·¥ç¥ó¤ò400(½êͼԤÎÆɤ߼è¤ê¤Î¤ß)¤Ë¥·¥Æ¥ª¥¯¡£
¤½¤·¤Æ¡¢¾å½Ò¤Î¥³¥Þ¥ó¥É¥é¥¤¥ó¤«¤é¡Ö-h(¥Û¥¹¥È) -u(¥æ¡¼¥¶¡¼) -p(¥Ñ¥¹¥ï¡¼¥É)¡×¤òºï½ü¤·¡¢Âå¤ï¤ê¤Ë
--defaults-extra-file=/home/¥æ¡¼¥¶¡¼Ì¾/login.conf
¤òÄɲ乤롣
¤½¤¦¤¹¤ì¤Ð¡¢¥³¥Þ¥ó¥É¥é¥¤¥ó¤Ë¥í¥°¥¤¥ó¾ðÊó¤ò´Þ¤Þ¤Ê¤¤¤Î¤Ç¡¢°ÂÁ´¤Ç¤Ï¤Ê¤¤¤È¤Ï¥¤¥ï¥ì¥Ê¤¯¤Ê¤ë¡£
´ØÏ¢¡§¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤ÎCRON¤ÎÀßÄê²èÌÌ(1.CRON¤ÎÀßÄê)
´ØÏ¢¡§¤µ¤¯¤é¥¤¥ó¥¿¡¼¥Í¥Ã¥È¤ÎCRON¤ÎÀßÄê²èÌÌ(2.CRON¥¹¥±¥¸¥å¡¼¥ëÀßÄê)
MySQLÁ´µ¡Ç½¥Ð¥¤¥Ö¥ë ~¸½¾ì¤ÇÌòΩ¤ÄAtoZ~
posted with amazlet at 14.05.23
ÎëÌÚ ·¼½¤
µ»½ÑɾÏÀ¼Ò
Çä¤ê¾å¤²¥é¥ó¥¥ó¥°: 170,416
µ»½ÑɾÏÀ¼Ò
Çä¤ê¾å¤²¥é¥ó¥¥ó¥°: 170,416