backing up all my mysql databases???

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.

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
             <\-/>
              _/ \_

so just copy the .sql files????

what directory is it stored in usually??

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

# 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

so.. after i copy all the files, i just copy them back into the same directory... once i rebuilt the machine???

then after i copy all my users and passwords, and databases will be the same??

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

thanks