#!/bin/sh # # Mini SQL dump (restore) utility - minisqldump.cgi ver='0.36b' # # This script works as a CGI and also as a command line tool. # It does dump/restore entire MySQL DB using the most efficient manner. # Written in almost pure Shellscript. # # For the dump, it uses simple "Select * From `table`;" >table.dat # and it generate "Drop table... Create Table..." similar to 'mysqldump'. # # For the restore, it uses "LOAD DATA LOCAL INFILE ..." and the data file # created by the dump operation. # # Note: It can use 'mysqldump' for backup if 'mysqldump' method is chosen, # and it can restore from files created by 'mysqldump', too. # # The table creation sql file and the table data files can be gathered in # a user specified format, instead of dumping to the currect directory. # .zip, .tar, .tar.gz, .tgz, .tar.bz2, .tbz2 are supported. # # When invoked as a CGI, it shows simple form to input parameters. # This script accepts both GET and POST method. # # Installation: # # Simply, copy this file to a well protected directory as 'minisqldump.cgi' # and set permission to 700 (or 755 in non-SuExec environment). # No editing required (unless mysql commands are in non-standard path). # Juse make sure to protect it at least with password (for CGI) # # Usage: # # As a CGI: Access https://example.com/safedir/minisqldump.cgi # and enter parameters in the form. # # Or supply the parameter in the address bar or link. # https://example.com/safedir/minisqldump.cgi?u=USER&p=PASS&h=SERVER&d=DBNAME&f=FILEPATH # First five parameters can be blank if you edit the Config section # and setup default parameters (that can be overridden). # # Add &r=1 if you want to restore the database. # Use t=table1+table2+rable5 if you want to specify table to backup/restore in URL. # # You should use this script with HTTPS as much as possible. # Otherwise, you will be sending the password in a clear text ..... # # # As a Command: minisqldump.cgi [-m] [-r] USER|- PASS|- SERVER|- DBNAME|- [FILEPATH|-] [table1 [table2] ...] # Add -r option BEFORE other parameters if you want to restore the database. # Use -m option if you want to use 'mysqldump' instead of Quick(default) method. # You can use a dash in places of USER,PASS,SERVER,DBBANE,and FILEPATH # if you edit Config section and setup default parameters for them. # Optional tablenames can be specified after FILEPATH (or a dash) to list # tables you want to backup/restore. Without these, all tables will be treated. # # Perform `minisqldump.cgi -h` for more info, or read this source code. # # # In both CGI and command line, FILEPATH parameter is an option. # If you specify FILEPATH ending with one of the compression format supported, # minisqldump.cgo will create the file and delete temporary dir/files. # # Saving the backup files in the current directory in zip format. # ex. minisqldump.cgi USER PASS SERVER DBNAME minibackup.zip # # Saving the backup files in /home/USER/ in tar format. # ex. minisqldump.cgi USER PASS SERVER DBNAME ~/minibackup.tar # # Restoring from tgz file in upper dir # ex(restore). minisqldump.cgi -r USER PASS SERVER DBNAME ../minibackup.tgz # # Using 'mysqldump' instead of quick & efficient default method. # 'mysqldump' method may offer better compatibility in some case. # ex(backup). minisqldump.cgi -m USER PASS SERVER DBNAME backup.sql # ex(restore). minisqldump.cgi -m -r USER PASS SERVER DBNAME backup.sql # # # If you specify other FILEPATH, minisqldump.cgi will create it as a directory # (if it doesn't exist) and simply place all files in it, and leave it. # # Saving the backup files in a directory 'bu2005-10-15' without compression # ex. minisqldump.cgi USER PASS SERVER DBNAME bu2005-10-15 # # # WARNING: Although I've tested many times, this script is still under testing # and you should keep a backup of your DB using mysqldump, time to time. # # Advantage of this script: # # Unlike mysqldump. minisqldump does both backup and restore, and do it # without character encoding conversion in a smaller data format. # Also, it uses "LOAD DATA LOCAL INFILE..." which is more efficient. # And it compress/extract the backup automatically if specified. # It supports 5 storing/compression formats of your choice, too. # # So, minisqldump should work quicker for both backup and restoring # using less resources. # # Latest version will be available at: # http://check-these.info/MiniSqlDump.html # http://check-these.info/tools/minisqldump_cgi.txt # CHECK='http://check-these.info' DATE=`date "+%y-%m-%d"` # ###### Config section ########## # If you want to avoid entering some fields, replcase USERNAME and/or # other strings according to your own MySQL DB setting, # and remove # to activate. # Note: DO NOT put SPACES around equal sign =, please. # If you set PASSWORD here, make sure to set permission of 700. # # U='USERNAME' # P='PASSWORD' # H='MYSQLSERVER' # D='DBNAME' # The backup will be gathered & commpressed in the file in current directory # if you don't specify 5th commandline option or File/path form field. # You can use .zip .tar. .tar.zg (.tgz) .tar.bz2 (.tbz2) or other name, # whick will be used for the directory of none compressed data files. # F='backup.tgz' # MySQL commands path and file name. Usually, you don't have to change these. # MYPATH="/usr/local/bin" MYSQL="${MYPATH}mysql" MYDUMP="${MYPATH}mysqldump" ps='ps uxww' up='uptime' # ###### END config ######### waitbg () { CC=''; CCC='0' while :;do CC="${CC}x"; CCC=$(( $CCC + 1 )) case `jobs` in '') break;; esac sleep 3; echo -n '.' case "$CC" in "xxxxxxxxxx") CC=''; echo;;esac case "$CCC" in "$Max") echo "$Mess"; kill $!;Killed='1'; return;; esac done } ######## dump function ########## dumpdb () { echo -e "u='$u', p='********', h='$h', d='$d', f='$f', t='$t', r='$r' m='$m', res='$res'" ### Use mysqldump if -m optiont or m=1 is detected ### case "$m" in 1) if [ -z "$f" ];then f="backup.sql";fi echo "Dumpimg with 'mysqldump' (backupfile='$f') ..." case "$t" in '+') t='';;esac ff="$f" case "$f" in *.tar.gz|*.tar.bz2|*.zip|*.tar|*.gz|*.tgz|*.bz2|*.tbz2) ff="${f%.*}" case "$ff" in *.tar) ff="${ff%.*}";; esac $MYDUMP -u$u -p$p -h$h $d $t >$ff & Max='300'; Mess="mysqldump running too long. Terminating." waitbg; case "$Killed" in '1') return;;esac echo "Done";echo "Compressing ..." case "$f" in *.zip) zip "$ff" "$ff" &;; *.tar.gz|*.tgz) tar czvf "$f" "$ff" &;; *.tar.bz2|*.tbz2) tar cjvf "$f" "$ff" &;; *.gz) gzip -f "$ff" &;; *.bz2) bzip2 -f "$ff" &;; esac Max='300'; Mess="Compression process running too long. Terminating." waitbg; case "$Killed" in '1') return;;esac case "$f" in *.zip|*.tar.gz|*.tgz|*.tar.bz2|*.tbz2) rm $ff ;;esac ;; *) $MYDUMP -u$u -p$p -h$h $d $t >$f;; esac echo "Done.";ls -al "$f"; return;; esac ### Quick method ### case "$f" in # Create (temporary) directory for storing dump data '') ;; *.tar.gz|*.tar.bz2) dbdir="${f%.*.*}"; if [ -f "$dbdir" ];then rm "$dbdir";fi; mkdir "$dbdir";cd "$dbdir";; *.zip|*.tar|*.gz|*.tgz|*.bz2|*.tbz2) dbdir="${f%.*}"; if [ -f "$dbdir" ];then rm "$dbdir";fi; mkdir "$dbdir";cd "$dbdir";; *) if [ ! -d "$f" ];then if [ -f "$f" ];then rm "$f";fi;mkdir "$f";fi;cd "$f";; esac if [ "$?" != "0" ];then echo "Error: unknown reason ...";return;fi M="$MYSQL --quick -s -u$u -p$p -h$h -D$d -e" # Get the list of tables TL=`$M "SHOW TABLES"` C='' # Construct 'Show Create Table' command and dump each table data for tn in $TL do case "$t" in *" $tn "*|"+") # If the table matches the list case "$t" in "+") ;; *) t="${t%[\ ]$tn[\ ]*}${t##*[\ ]$tn}" ;;esac C=$C"SHOW CREATE TABLE $tn;" $M "SELECT * FROM $tn;" >$tn.dat echo "Table '$tn' saved." ;; esac done case "$t" in *[!\ +]*) echo "Error: Specified table(s) $t not found in the database.";; esac $M "$C" |cut -f 2 |sed 's/\\n//g' >$d.tables.sql # Save table creating SQL # Compress if specified and delete temporary directory if [ -n "$f" ];then cd .. ;fi if [ -n "$dbdir" ];then case $f in *.zip) zip -r "$dbdir" "$dbdir";; *.tar) tar cvf "$f" "$dbdir";; *.gz|*.tgz) tar czvf "$f" "$dbdir";; *.bz2|*.tbz2) tar cjvf "$f" "$dbdir";; esac rm -rf "$dbdir" fi if [ -n "$f" ];then ls -al "$f";fi # Show the detail of resulted file(s) } ######## restore function ########## restoredb () { echo -e "u='$u', p='********', h='$h', d='$d', f='$f', t='$t', r='$r' m='$m', res='$res'" ### Use simple mysql INSERT if -m option or m=1 is detected ### case "$m" in 1) if [ -z "$f" ];then f="backup.sql";fi case "$t" in '+') t='';;esac case "$f" in *.zip) ff=`unzip -o "$f"`;echo "***$ff***";ff="${ff#${ff%:*}}" ff="${ff%${ff##*[!\ ]}}";ff="${ff#:[\ ]}";echo "***$ff***";; *.tar) ff=`tar xvf "$f"`;; *.tar.gz|*.tgz) ff=`tar xzvf "$f"`;; *.gz) ff=`gunzip -l "$f"`; ff=${ff##*\ };gunzip -c $f >$ff ;; *.tar.bz2|*.tbz2) ff=`tar xjvf "$f"`;; *.bz2) ff=`bunzip2 -l "$f"`;ff=${ff##*\ };bunzip2 -c $f >$ff ;; esac #echo "DBG ff=$ff"; # case "$res" in 1) echo -n "Using 'restore.cgi' (backupfile='$f') ..." # ./restore.cgi "$f" "$u" "$p" "$h" "$d";echo "Done.";return;; # esac case $t in *[!\ ]*) echo "All tables in given sql file will be restored eventhough you specified these tables:$t";; esac case "$f" in *.tar.gz|*.tar.bz2|*.zip|*.gz|*.tgz|*.bz2|*.tbz2) echo "Restoring with 'mysql' (backupfile='$ff', decompressed from '$f' ) ..." $MYSQL -u$u -p$p -h$h $d <$ff && rm "$ff";; *) echo "Restoring with 'mysql' (backupfile='$f') ..."; $MYSQL -u$u -p$p -h$h $d <$f;; esac echo "Done." $MYSQL -v -v -v -u$u -p$p -h$h $d -e "Show Table Status;"; return;; esac ### Quick method ### # Check if the file (directory) exists when its specified error () { echo "Error: '$f' not found";return 1; } case "$f" in *.tar.gz|*.tar.bz2) dbdir="${f%.*.*}";; *.zip|*.tar|*.gz|*.tgz|*.bz2|*.tbz2) dbdir="${f%.*}";; esac # Extract files if compressed format is specified case "$f" in '') ;; *.zip) if [ ! -f "$f" ];then error;return;fi;unzip "$f" && cd "$dbdir";; *.tar) if [ ! -f "$f" ];then error;return;fi;tar xvf "$f" && cd "$dbdir";; *.gz|*.tgz) if [ ! -f "$f" ];then error;return;fi;tar xzvf "$f" && cd "$dbdir";; *.bz2|*.tbz2) if [ ! -f "$f" ];then error;return;fi;tar xjvf "$f" && cd "$dbdir";; *) if [ ! -d "$f" ]; then error;return;fi;cd "$f";; esac if [ "$?" != "0" ]; then echo "Error: possibly wrong fromat '$f'"; exit;fi # Drop, Create, and Load Data for each table M="$MYSQL -v -v -v -u$u -p$p -h$h -D$d -e" dd="$d.tables.sql" if [ ! -r $dd ];then for dd in *.tables.sql ;do break;done;fi echo "Restoring using $dd" while read -r ct do tn=${ct#*TABLE ?} ; tn=${tn%%? *} case "$t" in *" $tn "*|"+") case "$t" in "+") ;; *) t="${t%[\ ]$tn[\ ]*}${t##*[\ ]$tn}" ;;esac $M "DROP TABLE IF EXISTS $tn;$ct; LOAD DATA LOCAL INFILE '$tn.dat' INTO TABLE $tn" 2>&1 ;; esac done <$dd case "$t" in *[!\ +]*) echo "Error: Specified table(s) $t not found in the backup.";; esac # Remove temporary directory if it's there if [ -n "$dbdir" ]; then cd .. && rm -rf "$dbdir";fi } ####### helper function(s) ####### updatenow () { # Install/update function exec 2>&1 case "$UF" in *_[pc][hg][pi].txt) RF="${UF%_*}.${UF##*_}" RF="${RF%.txt}";; *)RF="$UF";;esac if [ -f "$RF" ];then if [ ! -w "$RF" ];then echo "Error: The file: $RF isn't writalbe. Please change the permission to 700 (or 755)"; if [ -n "$AS_CGI" ];then return;else exit;fi fi cp -vf "$RF" "$RF.$DATE" fi echo "Downloading current version ...." wget -t 1 -nd "http://serv.check-these.info/tools/$UF" && sleep 2 && chmod 700 "$UF" && mv -vf "$UF" "$RF" && echo "Update done" || echo "Update failed. Please retry later if it was downloading problem. If the problem persists, contact author: minisqldump (atmark) check-these.info "; case "$RF" in *.cgi) export SCRIPT_FILENAME=""; ./$RF -V ;;esac if [ -n "$AS_CGI" ];then echo '
';return;else exit;fi } version="MiniSqlDump version:$ver $CHECK/MiniSqlDump.html" usage (){ # Show 'usage' message echo "$version Usage: minisqldump.cgi -V|--version|-h|--help|-w|--update | [-m][-r][-ps][-noup] USER|- PASS|- SERVER|- DBNAME|- [FILEPATH|-] [TABLE1[TABLE2] ...] -V, --version Show version. -h, --help Show this help. -x, --extensive Show this help and info from the script. -w, --update Update this script, automatically. Note: This will remove any configuration you might have done. -m Use mysqldump method instead of default Quick method. -r Restore from the backup. -noup Don't run 'uptime' before and after the operation. -ps Run 'ps uxww' after the operation. It may reaveal password. USER Username for the MySQL server. Use hyphen - for the default value. PASS Password for the MySQL server. Use hyphen - for the default value. SERVER Name or IP of the MySQL server. Use hyphen - for the default value. DBNAME Name of the database. Use hyphen - for the default value. FILEPATH Directory or filepath of backup. Use hyphen - for the default value. If you omit this, files will be saved in the current directory. You must specify this if you want to select some tables. TABLE1 ,TABLE2.. List of tables (separated by a space). Without these, all tables will be backuped/restored. However, it has no effect on 'mysqldump' method restore. " } ######## Main routine ########## cmd="dumpdb" restore="restoredb" r0=" checked" m0="$r0" #UF=${0##*/} ; UF="${UF%.*}_${UF##*.}.txt" ; UF='minisqldump_cgi.txt' # Send header and parse QUERY_STRING if invoked as a CGI if [ -n "$SCRIPT_FILENAME" -a "${SCRIPT_FILENAME##*/}" = "${0##*/}" ];then echo "Content-type: text/html MiniSqlDump

MiniSqlDump(restore) utility v${ver}

"
  
  AS_CGI=1
  exec 2>&1
  # Get query data from QUERY_STRING (GET method) or stdin (POST method)
  case "$REQUEST_METHOD" in
    [gG][eE][tT])  qs="$QUERY_STRING";;
 #    *) while : ;do read -r post || break; qs="$qs$post";done ;;
    # Read only one line
    *) read -r qs;;
  esac
  qsl="${#qs}"
  while : ;do  # Primitive form parsing. No special chars treated
    C=$(($C + 1)); pair="${qs%%[&]*}"; qsp="$qs";qs="${qs#*[&]}"
    id="${pair%%=*}"; v="${pair#*=}"
#echo -e "DBG $C:\nqss=$qss\nqs=$qs\npair=$pair\nid=$id\nv=$v"    
    case "$id" in  '');;  *[!0-9a-zA-Z_-.]*) echo "Error:
You can't use special characters in parameter name '$id'";exit 3;;
      "$pair") eval "$id=''";; *) eval "$id='$v'";; 
    esac
    case "$qs" in ''|\&|"$qsp") break;;esac
    case "$C" in '50') echo "Error: Max loop reached $C";exit 2;;esac
  done
  case "$c" in "w") UF="$fn"; updatenow;qsl=0;;esac
  case "$m" in 1) m1="$m0";m0='';; esac
  case "$res" in 1) res0="$r0";; esac
  case "$r" in 1) cmd="$restore";r1=$r0;r0='' 2>&1;; esac
  # Replace %20 and + in $t with a space.
  while :;do case $t in *%20*)t="${t%%[%]20*} ${t#*[%]20}";; *)break;;esac;done 
  while : ;do case $t in *+*) t="${t%%+*} ${t#*+}";; *)break;; esac;done
  host=`hostname`
  case "$host" in *powweb*) pow=1;; *) ps='' ;;esac
else   # Invoked as a command
  pssave="$ps";ps=''
  case "$#" in '0') usage;exit;;esac
  while :; do case "$1" in  #Check and consume options
    '-m') shift; m1=$m0;m0='';m=1;; 
    '-res') shift; res0="$r0";res=1;; 
    '-r') cmd="$restore"; shift; r1=$r0;r0='';r=1;;
    '-ps') shift;ps="$pssave";; # run ps. ps may reaveal the password
    '-nops') shift;ps='';; # Don't run ps. (Default in the command line mode)
    '-noup') shift;up='';; # Don't run uptime.
    '-V'|'--version') shift; echo $version;exit;;
    '-h'|'--help') shift;usage;exit;;
    '-x'|'--extensive') shift;usage;echo;grep '^#\( \|$\)' $0;exit;;
    '-w'|'--update') updatenow;exit;; # Get the new version. Auto update.
    '-'?*) echo -e "Error: unknown option:$1\n";usage;exit;;
    *) break;;
  esac;done
  qsl="$#"
  if [ "$qsl" -gt "3" ];then 
     u="$1";p="$2";h="$3";d="$4";f="$5";shift 4
    if [ -n "$1" -a -n "$2" ];then shift;
      while : ;do case $1 in '') break;; *) t="$t $1"; shift;; esac;done
      case "$t" in " "*) t=${t#?};;esac
    fi
  fi
fi

 # Check parameters, use the default if it's defined
usave="$u"; psave="$p"; hsave="$h"; dsave="$d"
case "$u" in ''|'-') u="$U" ;; esac
case "$p" in ''|'-') p="$P" ;; esac
case "$h" in ''|'-') h="$H" ;; esac
case "$d" in ''|'-') d="$D" ;; esac
case "$f" in ''|'-') f="$F" ;; esac

if [ "$qsl" != "0" ];then 
  if [ -z "$u" -o -z "$p" -o -z "$h" -o -z "$d" ];then 
    echo -e "Error: missing parameter(s)
u='$u', p='********', h='$h', d='$d', f='$f', t='$t', r='$r'"
  else 
    tsave="$t"; case "$t" in '') t='+';; *) t=" $t ";;esac
    hostname;$up; $cmd; $up; $ps; # Execut the command
  fi
fi

 # Input form (if invoked as a CGI). Modify as you wish.
if [ -n "$AS_CGI" ];then 
  if [ "$qsl" != "0" ];then echo "
";fi
  echo "

Method:       Quick(default)  mysqldump #1
Username:      #2
Password:      #2
Server name:   #2
DB name:       #2
File/Path:     #2, #3
Tables:        #4
Action:       Dump  Restore
Restart MiniSqlDump
#1 Quick(default) method is similar to '--tab=path, -T path' option of mysqldump, but it isn't restricted to the same server.
It creates one SQL file containing all table creation commands instead of creating a file for each table.
Also, it creates 'tablename.dat', instead of 'tablename.txt'.

#2 These valuse can be left blank (for the security and/or convenience) if you have edited the "Config section" of the script.

Similarly, these parameters can be abbreviated in command line mode by placing a dash '-' for eash item.
ex. minisqldump.cgi - - - database123 backup2004-10-17.tgz table1 table2 table3
(In this example, USERNAME,PASSWORD,MYSQLSERVER you set in the script will be used)

#3 Use one of following extension for compressed (or just grouped, in case of .tar) backup: .zip .tar .tar.gz .tar.bz2 .tgz .tbz2

#4 List tables separatd by a space. Leave it blank for backuop/restore all tables in the database.
If 'mysqldump' method is chosen for the restore action, this field will be igonored and all talbes in the backup will be retored.

Utility Links:


Update This program, MiniSqlDump: * Current file will be save as 'minisqldump.$DATE'.
If you made any change, you will need to copy them to the new version.

More info about MiniSqlDump

Install/Update MiniSqlAdmin  Run MiniSqlAdmin  More info about MiniSqlAdmin

Install/Update MiniTar  Run MiniTar  More info about MiniTar

" if [ -n "$pow" ];then # More info for PowWeb users. echo "Install/Update ExtraTools  Run ExtraTools

Note:

PowWeb's OPS has MySQL backup and import utility.  OPS->Packages->MySQL
It is very easy and convenient if you want to download ready-made compressed backup
and upload & restore from the data you have in your machine.
* Currently, you need to decompress the file (on your machine) to use the import feature.

The backup created by 'mysqldump' method of this script is compatible with PowWeb's utility.
This script can restore the daily backup generated by PowWeb once you upload it
(via FTP or upload script) to somewhere in your site, too.
(It'll decompress the backup for you, automatically, if the file has proper extension.)

For taking the backup instantly, this tool is more convenient
and restore operation is much quicker with default 'Quick' method,
but you need to download to your machine to have the backup really secure.

Please report BUG or problem to PowWeb forum & PM me if you don't get reply.
" else echo "
Please report BUG or problem to minisqldump AT-MARK check-these.info

" fi echo "
For other tools and tips: check-these,info   powered by Shellscript
" fi # END