Dear Group:
I have been doing a lot of experimenting with a rather huge database,
and I need to recover from mistakes when necessary. I need to make
live copies.
After spending too long trying unsuccessfully to load DBD::mysql and DBI.pm on
my machine (for mysqlhotcopy), and
and wasting a lot of time recreating indexes, and a lot of disk space & time
using BACKUP, I finally wrote a little
shell script to simply copy a large table and indexes from one database to
another. It works. 70 lines. I used the hotcopy code as a guide.
It's particularly good when you want to copy a huge database before doing
a potentially damaging operation like ALTER TABLE to it. My database is
already 2G data and 2G index, so I don't have time to fiddle with backup
to text. This solution is optimized for big tables. It doesn't require Perl.
Other major advantages of this script over backup or mysqlhotcopy include:
1. Creates the new database and table if necessary (but won't overwrite them)
+. Puts the copied table into a [possibly new] database and checks it's valid.
2. Simple as hell, 70 lines, at least as fast as hotcopy.
3. No options: Usage line sufficient.
4. Doesn't need perl or DBI or DBD::mysql installed to work
5. Keeps the INDEXES intact (for large tables this is *important*)
6. Hotcopy safe, works with mysqld running,
uses LOCK and FLUSH same as hotcopy.
7. Tells you what it's doing, and what the problems are, in English.
"How do I copy a table?" is a FAQ, I hope this is a simple answer. The others
never worked too well for me.
Of course it's a first cut, and I'm not a shell script expert. The if/fi
syntax is quite ugly and there's probably a better idiom. There's very
little checking or flexibility. Corrections welcome.
Steve
---------------------------------------cut here------------------
#!/bin/bash
# copytable olddb newdb table
# (c) Steve Rapaport, 2002.
# Use as desired, keep the credits intact.
set -x
fromdb=$1
todb=$2
table=$3
#===================================
dbdir="/var/lib/mysql/"
topath="${dbdir}${todb}/"
frompath="${dbdir}${fromdb}/"
if [ ! -f $frompath$table.MYD ] ; then
echo "source table ($table.MYD) not found";
echo "Usage: $0 fromdb todb table"
exit 1
fi
if [ -f "${topath}${table}.MYD" ] ; then
echo "Please delete destination table files $table.* first";
echo "Won't overwrite"
exit 1
fi
mysql -e "create database $todb;"
if ! mysql -e "use $todb" ; then
echo "Can't use or create database $todb"
exit 1
fi
mysql -e "use $fromdb; LOCK TABLES $table READ; FLUSH TABLES; FLUSH LOGS;"
if ! mysqldump -d --add-drop-table $fromdb $table > "$table.sql" ; then
echo "schema dump failed";
exit 1
fi
if ! mysql -e "use ${todb}; source ${table}.sql;" ; then
echo "schema copy failed";
exit 1
fi
if ! cp ${frompath}${table}.MYI ${topath}${table}.MYI ; then
echo "index copy failed";
exit 1
fi
if ! cp ${frompath}${table}.MYD ${topath}${table}.MYD ; then
echo "data copy failed";
exit 1
fi
if ! cp ${frompath}${table}.frm ${topath}${table}.frm ; then
echo ".frm copy failed";
exit 1
fi
mysql -e "use $fromdb; UNLOCK TABLES;"
echo "Done. Checking the new table."
if ! mysql -e "use $todb; check table $table;" ; then
echo "table check failed";
exit 1
fi
exit 0
--
Steve Rapaport
still at large
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
