Posted by frank78 on 04/29/06 23:59

Hi everyone,

I am having a little bit of trouble backing up some mySQL tables. I've
been trying to adapt a script I found on the internet at
http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/ . However, i
have been having trouble (under bash) on getting it to work.

I know my mysql account has full privileges to all account information.
Using the script below, it even prints out all the table names,
however, when I run this script through the terminal, I get an odd
error saying "mysqldump: error 1044: access denied for user
'USERNAME'@'localhost' to databse '?add-drop-table' when selecting the
database"

It shouldn't be a privileges problem, and because it loops through
every database, I know the script can see every table, but it cannot
dump out the contents of each.

Anyone have a suggestion or alternative?

Thanks much!
Frank

# MYSQL Backup Script
# Contains portions of code from
http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/

export d=$(date +'%Y-%m-%d')
export savepath='/home/idyllico/archive/mysql'
export usr='USERNAME'
export pwd="PASSWORD"

echo "mySQL Backup Script"

mkdir -p $savepath/$d

echo "Dumping entire database.."
mysqldump -add-drop-table -allow-keywords -all-databases -u$usr
-p$pwd > $savepath/$d/all.sql

echo "Dumping individual tables..."

for a in `echo "show databases" | mysql -u$usr -p$pwd | grep -v
Database`;
do
mkdir -p $savepath/$d/$a
echo "Dumping database: $a"
for i in `echo "show tables" | mysql -u$usr -p$pwd $a| grep -v
Tables_in_`;
do
echo " * Dumping table: $i"
mysqldump -add-drop-table -allow-keywords -q -a -c -u$usr -p$pwd $a
$i > $savepath/$d/$a/$i.sql
done
done

echo "Archiving Files..."
tar -C$savepath -c -f$savepath/$d.tar $d
gzip $savepath/$d.tar
echo "Deleting Temp Files"
rm -rf $savepath/$d
echo "Complete"

[Back to original message]