backing up all my mysql databases???

I want to back up all my databases in mysql.... but was wondering if there's an easier way to do it other than mysqldump every single database i have into a file??

i'm on a mandrake server... version 8.1

jmingoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ddunleaCommented:
Hi jmingo,

If you just want to restore to the same machine, you can do a dump of the directory it stores it's data in. Won't restore to a windows machine though.
0
steve918Commented:
Hi jmingo,

I have a script that...
shuts down the server,
copies all of the files to a temp directory,
restarts the server
tars and zips the files
and copies them to a windows machine for redundancy via samba

This script requires that I shut down the server for about 30-45 seconds, but It runs at 4:00am in the morning so it's not a problem at all.  That way I have a back up of the actuall mysql database files and all I have to do to perform a restore is to unzip the datafiles in the data directory.

-Steven     O
             <\-/>
              _/ \_
0
jmingoAuthor Commented:
so just copy the .sql files????

what directory is it stored in usually??



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jmingoAuthor Commented:
steve918,

could i see the script that you wrote???

might do the trick...

do you know if i can just copy all the files in the /var/lib/mysql/ directory????

thanks


0
steve918Commented:
yep, just copy all of the files in the /var/lib/mysql directory
I'll post the entire script in a bit, but it basically says something like

/sbin/service/mysqld shutdown
copy -r /var/lib/mysql/* /tmp/
/sbin/service/mysqld start
...zip up files etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve918Commented:
# This Script is pretty sloppy, for a specific purpose for my system
# and you'll probably have to make alot of changes.
# NOTE: It backups up everything in /var/lib/mysql and everything in /var/www/html and
# bzips them and saves them in seperate files on the local machine and copies them to
# a windows machine.


#!/bin/sh
mydate=$(date +%y%m%d)
myyear=$(date +%Y)
mymonth=$(date +%m)
htmldir="/home/steven/backup/html"
mysqldir="/home/steven/backup/mysql"
rhtmldir="/mnt/hatbackup/html"
rmysqldir="/mnt/hatbackup/mysql"

#
# MOUNT REMOTE BACKUP DIRECTORY
#
smbmount //10.10.0.1/HATBACKUP /mnt/hatbackup/ -o guest

#
#  CREATE LOCAL DIRECTORIES
#
if [ -d $htmldir/$myyear ]
then
        echo "Local Html Year Directory Exsists."
else
    echo "Local Html Year Directory Missing.  Creating one..."
        mkdir $htmldir/$myyear
fi

if [ -d $htmldir/$myyear/$mymonth ]
then
        echo "Local Html Month Directory Exsists."
else
        echo "Local Html Month Directory Missing.  Creating one..."
        mkdir $htmldir/$myyear/$mymonth
fi


if [ -d $mysqldir/$myyear ]
then
        echo "Local MySQL Year Directory Exsists."
else
        echo "Local MySQL Year Directory Missing.  Creating one..."
        mkdir $mysqldir/$myyear
fi

if [ -d $mysqldir/$myyear/$mymonth ]
then
        echo "Local MySQL Month Directory Exsists."
else
        echo "Local MySQL Month Directory Missing.  Creating one..."
        mkdir $mysqldir/$myyear/$mymonth
fi

#
#  CREATE REMOTE BACKUP DIRECTORIES
#
if [ -d $rhtmldir/$myyear ]
then
        echo "Remote Html Year Directory Exsists."
else
        echo "Remote Html Year Directory Missing.  Creating one..."
        mkdir $rhtmldir/$myyear
fi

if [ -d $rhtmldir/$myyear/$mymonth ]
then
        echo "Remote Html Month Directory Exsists."
else
        echo "Remote Html Month Directory Missing.  Creating one..."
        mkdir $rhtmldir/$myyear/$mymonth
fi


if [ -d $rmysqldir/$myyear ]
then
        echo "Remote MySQL Year Directory Exsists."
else
        echo "Remote MySQL Year Directory Missing.  Creating one..."
        mkdir $rmysqldir/$myyear
fi

if [ -d $rmysqldir/$myyear/$mymonth ]
then
        echo "Remote MySQL Month Directory Exsists."
else
        echo "Remote MySQL Month Directory Missing.  Creating one..."
        mkdir $rmysqldir/$myyear/$mymonth
fi



mysqldir="$mysqldir/$myyear/$mymonth"
htmldir="$htmldir/$myyear/$mymonth"
rmysqldir="$rmysqldir/$myyear/$mymonth"
rhtmldir="$rhtmldir/$myyear/$mymonth"

cd /var/www/
tar cf $htmldir/html-$mydate.tar html
bzip2 $htmldir/html-$mydate.tar
cd /var/lib/mysql/
/sbin/service mysqld stop
tar cf $mysqldir/mysql-$mydate.tar *
/sbin/service mysqld start
bzip2  $mysqldir/mysql-$mydate.tar

#
# Copy Files To Remote
#
cp $htmldir/html-$mydate.tar.bz2 $rhtmldir
cp $mysqldir/mysql-$mydate.tar.bz2 $rmysqldir
sleep 5
smbumount /mnt/hatbackup
0
jmingoAuthor Commented:
so.. after i copy all the files, i just copy them back into the same directory... once i rebuilt the machine???
0
jmingoAuthor Commented:
then after i copy all my users and passwords, and databases will be the same??
0
steve918Commented:
jmingo,

You won't even have to worry about restoring users and passwords.  If you just copy the backup files back in to the directory it restores the mysql database that stores usersnames and passwords.

-Steven
0
jmingoAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.