469,358 Members | 1,633 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,358 developers. It's quick & easy.

MYSQL table backups

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/2...-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/2...-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"

Apr 29 '06 #1
6 1507
This is no PHP.

The -add-drop-table switch for mysqldump is wrong: should be
--add-drop-table. Also check out the other switches.

Apr 30 '06 #2
If you're just doing backups, you can also just tarzip the mysql data dir
and do a quick and dirty backup that way.
May 1 '06 #3
Jimbus wrote:
If you're just doing backups, you can also just tarzip the mysql data dir
and do a quick and dirty backup that way.


That's not safe at all. For instance - if MySQL is upgraded, your backups may
suddenly be unusable.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 1 '06 #4
>> If you're just doing backups, you can also just tarzip the mysql data dir
and do a quick and dirty backup that way.

"dirty" is a good word for it. Unless you make sure that all changes
have been flushed to disk, and that no other changes are made during
making the backup, what you save may not be up to date (or even
consistent). For example, shut down the server (cleanly) first.
That's not safe at all. For instance - if MySQL is upgraded, your backups may
suddenly be unusable.


To the extent that the backups are good at all, you can still restore
those backups on a machine running the old version, then mysqldump,
and restore on the new version. This is, of course, a lot of
trouble compared to doing it right with mysqldump the first time.

Gordon L. Burditt
May 1 '06 #5
Gordon Burditt wrote:
If you're just doing backups, you can also just tarzip the mysql data dir
and do a quick and dirty backup that way.

"dirty" is a good word for it. Unless you make sure that all changes
have been flushed to disk, and that no other changes are made during
making the backup, what you save may not be up to date (or even
consistent). For example, shut down the server (cleanly) first.

That's not safe at all. For instance - if MySQL is upgraded, your backups may
suddenly be unusable.

To the extent that the backups are good at all, you can still restore
those backups on a machine running the old version, then mysqldump,
and restore on the new version. This is, of course, a lot of
trouble compared to doing it right with mysqldump the first time.

Gordon L. Burditt


If you have a machine running the old version, that is.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 1 '06 #6
On Mon, 01 May 2006 17:09:46 +0000, Gordon Burditt wrote:
If you're just doing backups, you can also just tarzip the mysql data
dir and do a quick and dirty backup that way.


"dirty" is a good word for it. Unless you make sure that all changes have
been flushed to disk, and that no other changes are made during making the
backup, what you save may not be up to date (or even consistent). For
example, shut down the server (cleanly) first.


Or use mysqlhotcopy and leave the server running...

http://dev.mysql.com/doc/refman/5.0/...qlhotcopy.html

Cheers,
Andy

--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

May 1 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by James | last post: by
4 posts views Thread by news | last post: by
4 posts views Thread by Federico | last post: by
5 posts views Thread by Troels Arvin | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.