ϡɥǥƥʥ ֥

ϡɥǥοǡʪեޥåȡŲLinuxСMySQLʤɤᥤäΤǤθ幭ꡢϡɥƤƤޤ ХǥХϡMacBookAir(13)iPad Air2ݥ DM100(󥰥)OLYMPUS STYLUS XZ-2ʤɡ Ȥʤ顢֥򹹿Ƥޤ

ݡ






(ͳٹ)ȤȤ¿饤󥿡ͥåΥ桼Τǥ⡣





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)

ʴΥ륵Сͳ٤ΤͤѹǤ󥿥륵СξϺ

Τ褦ʾ硢FTPcsvե夲Ƥơʲˡ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_xxxDB̾ɬ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 aaaDBѤΥ桼̾
-p(ѥ)DBѤΥѥɡ-p³(ڡʤ)񤯤
-Lե뤫ɤ߹४ץ
--fields-terminated-by=','csvեζʸϡ,(Ⱦѥ)
aaa_xxxDB̾
/home/aaa/www/test.csvcsvեξ

ȡ

aaa_xxx.test: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

ɽ졢ʲΤ褦˥ǡǼ롣

csvƤǼƤ
csvƤǼƤ

ơmysqlimportˤơDB̾λϤ뤬ơ֥̾λϤʤΤ

ȵ˻פtest.csvγĥҤäΤơ֥̾ʤΤ

Ĥޤꡢե뤬test.txtξ⡢testơ֥̾

ե̾ȥơ֥̾ۤʤϡ

import.phpɤȡǡɵפƤޤ

2ǼƤ
2ǼƤ

ɤˤϡ

--deleteݡȤ˥ơ֥ˤ

Υץɲä롣

ǡǼ˶ˤƤΤǡɵϲǤ롣

ʤ -D Ǥϥ顼Ȥʤ(顼ɽϤʤǼʤ)

--delete ʤʤ

--ץФ-άĤޤꡢ

--delete -D ƱդȤϴְäƤΤ

Ϣmysqlimport --help

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 դʤ

cron5ĤޤǤǤʤ褦ȥѥͥ뤫ǤϤʤС³ޥɤǤС5İʾǽ

Ϣ饤󥿡ͥåȤCRON(1.CRON)

Ϣ饤󥿡ͥåȤCRON(2.CRON塼)





MySQLǽХ֥ ~ΩAtoZ~
MySQLǽХ֥ ~ΩAtoZ~
posted with amazlet at 14.05.23

ɾ
夲󥭥: 170,416
[PR] au WALLETɤξ - auΥץڥɥɤǥåԥ󥰤򤪥ȥˡ

Υȥ꡼ϤƤʥ֥åޡɲ mixiå Share on Tumblr Clip to Evernote






mysqlimport --help ƤʲդƤ

С(Debian)Ǽ¹ԤƤǤ롣

-----

mysqlimport Ver 3.7 Distrib 5.1.73, for debian-linux-gnu (x86_64)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Loads tables from text files in various formats.
The base name of the text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and read the text file directly.
In other cases the client will open the text file.
The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:

--print-defaults
Print the program argument list and exit.

--no-defaults
Don't read default options from any option file.

--defaults-file=#
Only read default options from the given file #.

--defaults-extra-file=#
Read this file after the global files are read.

--character-sets-dir=name
Directory for character set files.

--default-character-set=name
Set the default character set.

-c, --columns=name
Use only these columns to import the data to. Give the column names in a comma separated list.
This is same as giving columns to LOAD DATA INFILE.

-C, --compress
Use compression in server/client protocol.

-#, --debug[=name]
Output debug log. Often this is 'd:t:o,filename'.

--debug-check
Check memory and open file usage at exit.

--debug-info
Print some debug info at exit.

-d, --delete
First delete all rows from table.

--fields-terminated-by=name
Fields in the input file are terminated by the given string.

--fields-enclosed-by=name
Fields in the import file are enclosed by the given character.

--fields-optionally-enclosed-by=name
Fields in the input file are optionally enclosed by the given character.

--fields-escaped-by=name
Fields in the input file are escaped by the given character.

-f, --force
Continue even if we get an SQL error.

-?, --help
Displays this help and exits.

-h, --host=name
Connect to host.

-i, --ignore
If duplicate unique key was found, keep old row.

--ignore-lines=#
Ignore first n lines of data infile.

--lines-terminated-by=name
Lines in the input file are terminated by the given string.

-L, --local
Read all files through the client.

-l, --lock-tables
Lock all tables for write (this disables threads).

--low-priority
Use LOW_PRIORITY when updating the table.

-p, --password[=name]
Password to use when connecting to server.
If password is not given it's asked from the tty.

-P, --port=#
Port number to use for connection or 0 for default to, in order of preference,
my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).

--protocol=name
The protocol to use for connection (tcp, socket, pipe, memory).

-r, --replace
If duplicate unique key was found, replace old row.

-s, --silent
Be more silent.

-S, --socket=name
The socket file to use for connection.

--ssl
Enable SSL for connection (automatically enabled with other flags).Disable with --skip-ssl.

--ssl-ca=name
CA file in PEM format (check OpenSSL docs, implies --ssl).

--ssl-capath=name
CA directory (check OpenSSL docs, implies --ssl).

--ssl-cert=name
X509 cert in PEM format (implies --ssl).

--ssl-cipher=name
SSL cipher to use (implies --ssl).

--ssl-key=name
X509 key in PEM format (implies --ssl).

--ssl-verify-server-cert
Verify server's "Common Name" in its cert against hostname used when connecting.
This option is disabled by default.

--use-threads=#
Load files in parallel.
The argument is the number of threads to use for loading data.

-u, --user=name
User for login if not current user.

-v, --verbose
Print info about the various stages.

-V, --version
Output version information and exit.

դ٤Ȥϡѥɤꡣ

-pθڡ򳫤ѥɤ񤯤ȡ

ѥɤpasswordξ硢

-ppassword

Ȥ롣

ϢcsvեMySQLؤΥݡ(LOAD DATA LOCAL INFILE,mysqlimport)饤󥿡ͥå

äMySQL  (ץޤμ亮꡼ SEɬ! )
äMySQL (ץޤμ亮꡼ SEɬ! )
posted with amazlet at 14.05.23
̴ϩ
եȥХ󥯥ꥨƥ
夲󥭥: 4,031
[PR] au WALLETɤξ - auΥץڥɥɤǥåԥ󥰤򤪥ȥˡ

Υȥ꡼ϤƤʥ֥åޡɲ mixiå Share on Tumblr Clip to Evernote






phpMyAdmin䥤ݡȡ䥤ݡȤե仲 sqlե夲褦Ȥȡ
̤礭ƼդʤȤ롣

Ĺ:2,048KiB
(Ĺ:2,048KiB)

ϡ̤ξ¤2MBꤵƤ뤿ᡣ

ä2MBȤϥʥ˥Ȥ

Ѥ2MBۤΤ⥤ݡȤǤΤ
phpMyAdminβ̾夫ѹǤphp.iniν񴹤ɬס

php.iniξ

/etc/php5/apache2/php.ini

; Maximum allowed size for uploaded files.
; http://php.net/upload-max-filesize
upload_max_filesize = 2M



upload_max_filesize = 100M

ǤͭˤʤʤΤǡApacheƵư롣

/etc/init.d/apache2 restart

ȡ

Ĺ:8,192KiB
(Ĺ:8,192KiB)

ȤʤꡢäϤƤ뤬10MBǤϤʤ

8MBʾˤϡ⤦1սѹɬפ롣

; Maximum size of POST data that PHP will accept.
; http://php.net/post-max-size
post_max_size = 8M



post_max_size = 100M

ApacheƵưǧ...

Ĺ:100MiB
(Ĺ:100MiB)

Ȥʤꡢ礭ե⥤ݡȤǤ褦ˤʤ롣

¤ͳǡphpMyAdminѹǤʤ褦ˤʤäƤ롣

Ϣphp.iniԽ¸Ǥʤ

ǤPRO Apache WebС  Version 2.4/2.2/2.0б (ǤPRO꡼)
ŵ Ǥ륷꡼Խ
ץ쥹
夲󥭥: 192,337

ƤWebСApacheCentOS 7 (NextPublishing)
ץ쥹R&D (2017-05-26)
夲󥭥: 35,780


[PR] au WALLETɤξ - auΥץڥɥɤǥåԥ󥰤򤪥ȥˡ

Υȥ꡼ϤƤʥ֥åޡɲ mixiå Share on Tumblr Clip to Evernote

ΥڡΥȥåץ