?
Solved

backing up all my mysql databases???

Posted on 2004-11-17
10
Medium Priority
?
230 Views
Last Modified: 2010-04-20
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

0
Comment
Question by:jmingo
  • 5
  • 4
10 Comments
 
LVL 5

Expert Comment

by:ddunlea
ID: 12604523
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
 
LVL 3

Expert Comment

by:steve918
ID: 12604549
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
 

Author Comment

by:jmingo
ID: 12604571
so just copy the .sql files????

what directory is it stored in usually??



0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:jmingo
ID: 12605759
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
 
LVL 3

Accepted Solution

by:
steve918 earned 100 total points
ID: 12605886
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
 
LVL 3

Expert Comment

by:steve918
ID: 12605977
# 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
 

Author Comment

by:jmingo
ID: 12606255
so.. after i copy all the files, i just copy them back into the same directory... once i rebuilt the machine???
0
 

Author Comment

by:jmingo
ID: 12606322
then after i copy all my users and passwords, and databases will be the same??
0
 
LVL 3

Expert Comment

by:steve918
ID: 12606615
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
 

Author Comment

by:jmingo
ID: 12607509
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the last ten+ years I have seen Linux configuration tools come and go. In the early days there was the tried-and-true, all-powerful linuxconf that many thought would remain the one and only Linux configuration tool until the end of times. Well,…
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question