http://Check-these.info/MysqlTips.html

Replace USERNAME, PASSWORD, SERVER, and DBNAME with your data.

Save it as "butable.cgi" and set permission of 700 (or 755).

As CGI: http://example.com/protected/butable.cgi?TABLENAME

As a command: ./butable.cgi TABLENAME

#!/bin/sh
echo
mysql -s -u"USERNAME" -p"PASSWORD" -h"SERVER" -e "select * from  $1;" "DBNAME" >"$1".dat 2>backup.err

Restore (The DB and table must exists, already):

Replace USERNAME, PASSWORD, SERVER, and DBNAME with your data.

Save it as "restable.cgi" and set permission of 700 (or 755).

As CGI: http://example.com/protected/restable.cgi?TABLENAME

As a command: ./restable.cgi TABLENAME

#!/bin/sh
echo
mysql -v -v -v -u"USERNAME" -p"PASSWORD" -h"SERVER" -e "TRUNCATE $1; 
  LOAD DATA LOCAL INFILE '$1.dat' INTO TABLE $1;" "DBNAME"  2>restore.err

Backup all tables in given DB. It will create "table.create" (CREATE TABEL sql lines), and TABLENAME.dat for each table, all in the current directory.

Replace USERNAME, PASSWORD, SERVER, and DBNAME with your data.

Save it as "budb.cgi" and set permission of 700 (or 755).

As CGI: http://example.com/protected/budb.cgi

As a command: ./budb.cgi

#!/bin/sh
echo
U=USERNAME
P=PASSWORD
H=SERVER
D=DBNAME

M="mysql -s -u$U -p$P -h$H -D$D -e"
TL=`$M "SHOW TABLES"`
for t in $TL
do 
 C=$C"SHOW CREATE TABLE $t;"
 $M "SELECT * FROM $t;"  >$t.dat
done
$M "$C" |cut -f 2 |sed 's/\\n//g' >table.create

Restore entire DB It uses "table.create" (CREATE TABEL sql lines), and TABLENAME.dat for each table, all in the current directory.

Replace USERNAME, PASSWORD, SERVER, and DBNAME with your data.

Save it as "resdb.cgi" and set permission of 700 (or 755).

As CGI: http://example.com/protected/resdb.cgi

As a command: ./resdb.cgi

#!/bin/sh
echo
U=USERNAME
P=PASSWORD
H=mysql0X.powweb.com
D=DBNAME

M="mysql -v -v -v -u$U -p$P -h$H -D$D -e"
while : ;do
 read -r ct || break
 t=${ct#*TABLE ?}
 t=${t%%? *}
 $M "DROP TABLE IF EXISTS $t; $ct; 
   LOAD DATA LOCAL INFILE '$t.dat' INTO TABLE $t"
done <table.create

I tested these with a DB containing 2 tables each having 171,244 rows. Datafile for each table.dat were about 3.3 Mbytes. Backup took a few seconds, and restore took several seconds (One table had an index).

These methods are even more efficient than using mysqldump and restore.cgi.

Backup all tables in given DB on SERVER, using USER, PASS, to from DATAPATH It will create "table.create" (CREATE TABEL sql lines), and TABLENAME.dat for each table, all in the current directory.

Save it as "budb" and set permission of 700 (or 755).

usage: ./budb USER PASS SERVER DB [DATAPATH]

#!/bin/sh
case $5 in '') ;; *) if [ ! -d "$5" ];then mkdir "$5";fi;cd "$5";;esac
M="mysql -s -u$1 -p$2 -h$3 -D$4 -e"
TL=`$M "SHOW TABLES"`
for t in $TL
do 
 C=$C"SHOW CREATE TABLE $t;"
 $M "SELECT * FROM $t;"  >$t.dat
done
$M "$C" |cut -f 2 |sed 's/\\n//g' >table.create

Restore entire DB on SERVER, using USER, PASS, from DATAPATH. It uses "table.create" (CREATE TABEL sql lines), and TABLENAME.dat for each table, all in the current directory.

Save it as "resdb" and set permission of 700 (or 755).

usage: ./resdb.cgi USER PASS SERVER DB [DATAPATH]

#!/bin/sh
case $5 in '') ;; *) if [ ! -d "$5" ];
  then echo "Error: '$5' not found";exit;fi;cd "$5";;esac
M="mysql -v -v -v -u$1 -p$2 -h$3 -D$4 -e"
while : ;do
 read -r ct || break
 t=${ct#*TABLE ?}
 t=${t%%? *}
 $M "DROP TABLE IF EXISTS $t;$ct; 
   LOAD DATA LOCAL INFILE '$t.dat' INTO TABLE $t" 2>&1
done <table.create

I tested these with a DB containing 2 tables each having 171,244 rows. Datafile for each table.dat were about 3.3 Mbytes. Backup took a few seconds, and restore took several seconds (One table had an index).

This page is http://Check-these.info/MysqlTips.html