Database Import/Export

The database connection string and the text file with the table name are given directly, and the exp/imp# begin of Exptable#!/bin/kshparallel_limit=5wait_time=10parameters= "direct=y Compress=n rows=y indexes=y statistics=none "; Export parameterstarget_path=./donelogfile_path=./elogsuser_id=$ 1tablelist_file=$2extend_code=$3exptabledata () {echo ' \ n ' echo ' date + '%y-%m-%d%h:%m:%s ' '--Start exporting data table "$ ..." Echo  "Command Line:" Exp userid=******** $PARAMETERS tables=$2 file=$3 log=$4 zip_file=$3.gz rm-f $ #exptabledata $USER _id $TABLE _name_upper $DMP _file $LOG _file $TARGET _path exp userid=$1 $PARAMETERS tables=$2 file=$3 log=$4 #echo ' date + '%y- %m-%d%h:%m:%s "'"--compressed data file "$ $" ... "#if [-S $];then gzip-f $; fi if [-S $ZIP _file];then mv-f $ZIP _file $5/$ZIP _file else mv-f $ $5/$3 fi echo ' date + '%y-%m-%d%h:%m:%s ' "--table" $ "Data export completed!!!  \ n "} #校验用户名/Export the manifest file exists if [" null$user_id "=" null "-o" null$tablelist_file "=" null "]then echo" \ n "echo" Usage: $ userid/paasword[@ServiceName] tables.lst [Extend] "echo" userid/paasword[@SerVicename]--Database link string "echo" tables.lst--Export the table manifest file "echo" Extend--Extension "echo  "\ n" EXIT;FI; #校验导出表清单文件if [!-R $TABLELIST _file]then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" ' "--Error!!!"  echo $TABLELIST _file "--Export table manifest file read Invalid!!!" echo "\ n" exitfi# check target directory if [-D $TARGET _path]then if [!-W $TARGET _path] then echo "\ n" Echo ' date + "%y-%     m-%d%h:%m:%s "'"--Error!!! "     echo $TARGET _path "--Target directory write no permission!!!"  echo "\ n" Exit Fielse mkdir-p $TARGET _path if [! $?]     then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--Error!!!"     echo $TARGET _path "--Target directory creation failed!!!" echo "\ n" Exit fifi# check the log directory if [-D $LOGFILE _path]then if [!-W $LOGFILE _path] then echo "\ n" Echo ' d     Ate + "%y-%m-%d%h:%m:%s" "--Error!!!"     echo $LOGFILE _path "--Log directory write no permission!!!"  echo "\ n" Exit Fielse mkdir-p $LOGFILE _path if [! $?] then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" ' "--Error!!!"Echo $LOGFILE _path"--Log directory creation failed!!! " echo "\ n" Exit Fifiecho "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--data export start ..." echo "Target directory--" $TARGET _pathecho "table clear   Single file--"$TABLELIST _fileecho" \ n "#读取表名称进行数据导出while read Large_tabledo # According to the number of processes sleep read Parallel_limit < Parallel.limit While [' Ps-ef|grep ' exp USERID "|grep-v grep|wc-l '-ge $PARALLEL _limit] do sleep $WAIT _time read Parallel_lim IT < Parallel.limit done Dfile_name= ' echo $LARGE _table|awk-f ' | ' ' {print $;} ' | TR ' [: Upper:] ' [: Lower:] ' table_name= ' echo $LARGE _table|awk-f ' | ' ' {print $;} ' |  TR ' [: Lower:] ' [: Upper:] ' if [' null$dfile_name ' = ' null '-a ' null$table_name ' = ' null '] then continue else if ["Null$table_name" = "NULL"];then table_name= ' echo $DFILE _name|tr ' [: Lower:] ' [: Upper:] '; fi fi if ["Null$extend_co DE "=" NULL "] then dmp_file= $DFILE _name". DMP "log_file= $LOGFILE _path/$DFILE _name". LOG "Else dmp_file= $DFILE _na ME "_" $EXTEND _code ". DMP" log_file= $LOGFILE _path/$Dfile_name "_" $EXTEND _code.log fi exptabledata $USER _id $TABLE _name $DMP _file $LOG _file $TARGET _path &done < $TABL Elist_fileecho "\ n" Echo ' date + '%y-%m-%d%h:%m:%s "'"--end of data export!!! " echo "Target directory--" $TARGET _pathecho "table manifest file--" $TABLELIST _fileecho "\ n" # End of exptable# begin of Imptable#!/bin/kshparal lel_limit=10wait_time=10parameters= "buffer=104857600 ignore=y commit=y full=y"; Export parameterslogfile_path=./  Ilogsindex_path=./indexbackup_path=./bakdoing_path=./doinguser_id=$1datafile_path=$2imptabledata () {echo ' \ n ' Echo ' date + '%y-%m-%d%h:%m:%s ' '--Start importing data file "$" ... "echo" Command line:imp userid=******** "$PARAMETERS indexes=n FIL E=$3 log=$5 #imptabledata $USER _id $DMP _file $INDEX _file $LOG _file #imp userid=$1 $PARAMETERS indexes=y file=$2 indexfil E=$3 imp userid=$1 $PARAMETERS indexes=n file=$2 log=$4 mv-f $ $ Echo ' date + '%y-%m-%d%h:%m:%s ' '--file "$" Import complete!! !\n "} #校验用户名/import the table manifest file exists if [" null$user_id "=" NULL "]then echo" \ n "echo" Usage: \n$0 Userid/paasword[@ServiceName] "echo" userid/paasword[@ServiceName]--Database link string "echo" \ n "EXIT;FI; #校验数据文件存储目录if [! \ (-D $DATAFILE  _path-a-R $DATAFILE _path \)]then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" ' "--Error!!!"  echo $DATAFILE _path "--no Read permission!!! The target directory" echo "\ n" exitfi# check log directory if [-D $LOGFILE _path]then if [!-W $LOGFILE _path] then echo "\ n" Echo ' date + "%y-%m     -%d%h:%m:%s "'"--Error!!! "     echo $LOGFILE _path "--Log directory without write permission!!!"  echo "\ n" Exit Fielse mkdir-p $LOGFILE _path if [! $?]     then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--Error!!!"     echo $LOGFILE _path "--Log directory creation failed!!!" echo "\ n" Exit fifi# check the INDEX directory if [-D $INDEX _path]then if [!-W $INDEX _path] then echo "\ n" Echo ' date + "    %y-%m-%d%h:%m:%s "'"--Error!!! "     echo $INDEX _path "--no Write permission for INDEX directory!!!"  echo "\ n" Exit Fielse mkdir-p $INDEX _path if [! $?]     then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--Error!!!" echo $INDEX _path "--INDEX directory creation failed!!!" echo "\ n" Exit fifi# verify the BACKUP directory if [-D $BACKUP _path]thenif [!-W $BACKUP _path] then echo "\ n" Echo ' date +     "%y-%m-%d%h:%m:%s" "--Error!!!"     echo $BACKUP _path "--no write access to the BACKUP directory!!!"  echo "\ n" Exit Fielse mkdir-p $BACKUP _path if [! $?]     then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--Error!!!"     echo $BACKUP _path "--BACKUP Directory creation failed!!!" echo "\ n" Exit fifi# check DOING directory if [-D $DOING _path]then if [!-W $DOING _path] then echo "\ n" Echo ' date + "     %y-%m-%d%h:%m:%s "'"--Error!!! "     echo $DOING _path "--DOING directory without write permission!!!"  echo "\ n" Exit Fielse mkdir-p $DOING _path if [! $?]     then echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--Error!!!"     echo $DOING _path "--DOING Directory creation failed!!!" echo "\ n" Exit Fifiecho "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" "--Start Import data file directory" $DATAFILE _path "..." echo "\ n" #读取表名称进行数据导入 if [!-X Imptables.stop];then echo "Running" > Imptables.stop;firead imp_stop < Imptables.stopwhile[$IMP _stop! = "STOP"]dols-p $DATAFILE _path|grep ". DMP" > Dmpfile.lstwhile read Dmp_filedo # Sleep based on the number of processes read Parallel_  LIMIT < Parallel.limit while [' Ps-ef|grep ' imp USERID "|grep-v" grep "|wc-l '-ge $PARALLEL _limit]; Do sleep $WAIT _time read Parallel_limit < parallel.limit done table_name=${dmp_file%.*} log_file= $TABLE _name ". Log "index_file= $TABLE _name". sql "If [" null$dmp_file "=" NULL "] then continue fi mv-f $DATAFILE _path/$DMP _file $DOING _path/$DMP _file If [-R $DOING _path/$DMP _file] then imptabledata $USER _id $DOING _path/$DMP _file $INDEX _path/$I Ndex_file $LOGFILE _path/$LOG _file $BACKUP _path/$DMP _file & Else echo "\ n" Echo ' date + "%y-%m-%d%h:%m:%s" ' "--    Error!!! "    echo $DMP _file "--data file read failed!!!" echo "\ n" Fidone < dmpfile.lstif [' WC dmpfile.lst|awk ' {print '} '-eq 0];then sleep 120; Fils-p $DATAFILE _path|grep ". dmp" > Dmpfile.lstread imp_stop < Imptables.stopdoneecho "\ n" Echo ' date + '%y-%m-%d%H: %m:%s "'"--imptAbles.stop when the data importer exits!!! " echo "\ n" # End of Imptable

Database Import/Export