Importing large text files into MYSQL
HELP!!! I am trying to import unix files into the database as blob's and having alot of problems. You would have to know some about unix to understand and see what I'm doing. Basically, I'm putting configurations into:
The file is for one entire record... but almost all fields are multiple lines.
ie., configurations
hostname,
passwd_file,
hosts_file,
crontab,
packages to name a few. The file has'
fields enclosed by "field"
fields terminated by ,
The text file as an example is:
"bugsbunny",
"Mon Oct 2 14:48:16 CDT 2006",
" Solaris 7 1/00 s9s_u5wos_08b SPARC
Copyright 2000 Sun Microsystems, Inc. All Rights Reserved.
Use is subject to license terms.
Assembled 21 November 2000",
"Nodename : bugsbunny
OS Name : Sun
OS Level : 5.7
Patch Level : Generic_92933447-07
Ip Address(es) : 192.929.926.999
Default router : 192.929.926.4
Netmasks : 299.299.292.0
Name Server : .....
nameserver 192.929.226.4
search my.email.com
Memory : 256 MB
CPU : 1 x 550 Mhz
Disks : .....
Model: ST380011A
Revision: 3.04
Serial No: 5BUG11GA
Size: 80.02GB <80023879680 bytes>
Vendor: MAT****A
Product: DVD-ROM SR-8589
Revision: 8Z14
Serial No:
Size: 0.00GB <0 bytes> ",
"system boot Oct 2 08:34
UPTIME
2:48pm up 6:13, 3 users, load average: 0.10, 0.13, 0.12",
"The System-MacAddress=0:3:fc:8v:03:01
The System- kernel-type=64-bit sparcv7 kernel modules",
"test-args: data not available.
diag-passes=1
pci-probe-list=c,8,d,5,13
local-mac-address?=false
fcode-debug?=false
silent-mode?=false
scsi-initiator-id=7
oem-logo: data not available.
oem-logo?=false
oem-banner=My Use Only!
oem-banner?=true
ansi-terminal?=true
screen-#columns=80
screen-#rows=34
ttyb-rts-dtr-off=false
ttyb-ignore-cd=true
ttya-rts-dtr-off=false
ttya-ignore-cd=true
ttyb-mode=9600,8,n,1,-
ttya-mode=9600,8,n,1,-
output-device=screen
input-device=keyboard
load-base=16384
auto-boot?=false
boot-command=boot
diag-file: data not available.
diag-device=disk net
boot-file: data not available.
boot-device=disk net
use-nvramrc?=true
nvramrc=devalias CDROM /pci@1f,0/ide@d/cdrom
security-mode=command
security-password: data not available.
security-#badlogins=8
diag-script=none
diag-level=max
diag-switch?=false
error-reset-recovery=boot",
"STORAGE REPORT
Filesystem size used avail capacity Mounted on
/dev/dsk/c0t0d0s0 1.4G 256M 1.1G 19% /
/dev/dsk/c0t0d0s4 9.8G 2.8G 6.9G 30% /usr
/dev/dsk/c0t0d0s1 1.4G 1.1G 277M 81% /var
/dev/dsk/c0t0d0s5 9.4G 254M 9.0G 3% /opt
/dev/dsk/c0t0d0s7 9.8G 1.6G 8.1G 17% /export
Installed (physical).: 80.0239GB
Mounted..............: 33.4904GB
Used.................: 6.36512GB
Avail................: 26.7005GB
Swap.................: 1.0240GB",
"9327wbe4",
"FW5678j9jj",
"Uob32211",
"Location - Myhome, Workstation @ Home
Location Room: Office
Location Rack: 1",
"POC Name: John Doe
POC Phone: 449-739-9448
POC Email: jdoe@my.email.com",
"Application: Workstation
Application DBA:
Application Customer: John Doe 449-739-9448 jdoe@myemail.com",
"Print out list of all printers available from this server:
No Printers Set Up on this Server",
"SunOS 5.7 Generic_912331-01 sun4u sparc SUNW,Sun-Blade-100",
"Memory size: 256 Megabytes",
"0 on-line since 10/02/2006 08:34:59",
"System Crontab Information:
#ident @(#)root 1.20 01/11/06 SMI
#
# The root crontab should be used to perform accounting data collection.
#
# The rtc command is run to adjust the real time clock if and when
# daylight savings time changes.
#
10 3 * * * /usr/sbin/logadm
15 3 * * 0 /usr/lib/fs/nfs/nfsfind
1 2 * * * -x /usr/sbin/rtc && /usr/sbin/rtc -c > /dev/null 2>&1
30 3 * * * -x /usr/lib/gss/gsscred_clean && /usr/lib/gss/gsscred_clean
"Link Autospeed Status Speed Mode Ethernet Address
----- --------- ------ ------ ----- ------------------
eri0 off Up 100MB FDX 0:5:na:7u:93:ri",
"nameserver 929.929.926.4
search my.mail.com",
"929.929.926.4",
"##### Java #######:
java version 3.9.2_067
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_02-b03)
Java HotSpot(TM) Client VM (build 1.4.2_02-b03, mixed mode)
----->>> Java was found in the following directories: <<-----
/usr/mnt/library/User/usr/j2se/bin/java
##### gzip #######:
gzip 6.8.5-patch.1
(2000-01-08)
----->>> gzip was found in the following directories: <<-----
/usr/mnt/library/User/usr/bin/gzip
##### Perl #######:
This is perl, v5.6.1 built for sun4-solaris-64int
----->>> Perl was found in the following directories: <<-----
/usr/perl5/5.6.1/bin/perl
"#### metadb ####:
metadb: cobra: there are no existing databases
#### metastat ####:
metastat: cobra: there are no existing databases",
"root is running:.....
1 instance(s) of >> /bin/ps -ef
1 instance(s)
1 instance(s) of >> /usr/local/mysql/mysql/libexec/mysqld --based
1 instance(s) of >> /usr/openwin/bin/Xsun :0 -nobanner -auth /var
mysql is running:.....
1 instance(s) of >> -ksh
1 instance(s) of >> mysql -u root -p
End of program: proclist",
"Summary of processes running by each users ->
USER TOTAL PROCESS
-------------------------------
root 37
jdoe 8
..................................................
Detail report of users process grouped on STIME ->
USER STIME PROCESS COUNT
----------------------------------------------
mysql current 2
jdoe current 8
root current 37",
"#device device mount FS fsck mount mount
#to mount to fsck point type pass at boot options
#
fd - /dev/fd fd - no -
/proc - /proc proc - no -
/dev/dsk/c0t0d0s0 /dev/rdsk/c0t0d0s0 / ufs 1 no nodfratime,logging
/dev/dsk/c0t0d0s1 /dev/rdsk/c0t0d0s1 /var ufs 1 no nodfratime,logging
/dev/dsk/c0t0d0s3 - - swap - no -
"# Place share(1M) commands here for automatic execution
# on entering init state 3.
#
* set:
*
* Set an integer variable in the kernel or a module to a new value.
* This facility should be used with caution. See system(4).
*
* Examples:
*
* To set variables in 'unix': module named 'test_module'
*
* set test_module:debug = 0x13
set c2audit:audit_load = 1
set sq_max_size=0
set lwp_default_stksize=0x4000
set rpcmod:svc_default_stksize=0x4000
set red_minavail=7000
set noexec_user_stack=1
set noexec_user_stack_log=1",
"127.0.0.1 localhost
192.929.926.999 bugsbunny.my.mail.com bugsbunny loghost
192.929.926.4 looneytunes.my.mail.com
192.929.926.121.116 daffy.my.mail.com daffy",
"# Allow access to these services from given hosts
ALL:127.0.0.1
sshd:192.929.925.4
"#Deny all services from all hosts
ALL:ALL:banners /etc/banners",
"root:x:0:1:Super-User:/rootdir:/sbin/sh
jdoe:x:1000:21:John Doe:/export/jdoe:/usr/bin/ksh",
" PKGINST: HPNPL
NAME: JetDirect Printer Installer for Unix
CATEGORY: application
ARCH: sparc
VERSION: E.10.34
BASEDIR: /
VENDOR: HP
DESC: HP JetDirect Printer Installer for Unix package
PSTAMP: snbld26m28151818
INSTDATE: May 09 2006 10:26
STATUS: completely installed
FILES: 237 installed pathnames
2 shared pathnames
19 directories
176 executables
8688 blocks used (approx)
PKGINST: SFWesd
NAME: esound - The Enlightened Sound Daemon
CATEGORY: system
ARCH: sparc
VERSION: 0.2.18,REV=2002.03.27.20.41
BASEDIR: /opt
VENDOR: http://www.tux.org/~ricdude/EsounD.html
DESC: The Enlightened Sound Daemon
PSTAMP: freeware20020327215217
INSTDATE: Aug 31 2004 10:11
HOTLINE: Please contact the owners of this software
STATUS: completely installed
FILES: 24 installed pathnames
7 shared pathnames
7 directories
12 executables
610 blocks used (approx)
PKGINST: SUNW1394x
NAME: Sun IEEE1394 Framework (64-bit)
CATEGORY: system
ARCH: sparc
VERSION: 11.9.0,REV=2002.04.06.15.27
BASEDIR: /
VENDOR: Sun Microsystems, Inc.
DESC: IEEE1394 Framework and OpenHCI Driver (64-bit)
PSTAMP: crash20020406153716
INSTDATE: Aug 10 2004 12:01
HOTLINE: Please contact your local service provider
STATUS: completely installed
FILES: 8 installed pathnames
5 shared pathnames
5 directories
2 executables
1314 blocks used (approx)
PKGINST: SUNWbip
NAME: Basic IP commands (Usr)
CATEGORY: system
ARCH: sparc
VERSION: 11.9.0,REV=2002.04.06.15.27
BASEDIR: /
VENDOR: Sun Microsystems, Inc.
DESC: Basic IP commands (/usr/sbin/ping, /bin/ftp)
PSTAMP: leo20040930133721
INSTDATE: Dec 14 2004 17:09
HOTLINE: Please contact your local service provider
STATUS: completely installed
FILES: 5 installed pathnames
3 shared pathnames
3 directories
2 executables
1 setuid/setgid executables
252 blocks used (approx)",
load into text, medtext and longtext
version 5.0.24
I have finally managed to import the data using php and a parse script I found. I figured out last week that if I
load data infile '/tmp/bugsbunny.txt'
into table looneytunes
fields terminated by ","
lines terminated by "^";
that this works most and keeps the formatting for the paragraphs.
However, there are the few times that it doesn't --
haven't figured out why yet.
