backup mysql database

nawuza · 07-14-2008, 12:13 AM

i was doing my script..i think that it was all correct but seems that an error when was trying running the script saying bash: bla..bla.:command not found.

Code:

#!/bin/sh


NOW="$(date +%Y-%m-%d_%Hh%M)"


MUSER="root"

MPASS="password"

MHOST="localhost"


MYSQL="$(which mysql)"

MYSQLDUMP="$(which mysqldump)"

GZIP="$(which gzip)"


DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"


for db in $DBS

do
 
FILE= mysql-$db.$NOW
 
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE.sql

done

seems the code is alright.

chrism01 · 07-14-2008, 01:45 AM

It would help if you told us which cmd it complained about ie show the 'exact' error msg.

Anyway, this line

FILE= mysql-$db.$NOW

I'd change to

FILE=mysql-${db}.$NOW

note the removal of leading space, that is wrong for bash assignments, also, disambiguation of $db var when interpolated.
To check exactly what's happening, insert this at the top of your script:

set -xv

nawuza · 07-14-2008, 02:24 AM

still got an error..

Code:

for db in $DBS

do
 
FILE= mysql-${db}.$NOW
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE

done

+ for db in '$DBS'
+ FILE=
+ mysql-information_schema.2008-07-15_16h24
./test2.sh: line 17: 

mysql-information_schema.2008-07-15_16h24: command not found

+ /usr/bin/mysqldump -u root -h localhost -ppassword information_schema

+ /usr/bin/gzip -9
./test2.sh: line 18: 1: ambiguous redirect
mysqldump: Got errno 32 on write

fotoguy · 07-14-2008, 09:44 AM

A quicker way, and less scripting to backup mysql is to backup the mysql folder that contains the databases, depending on which distro you are using, it may be under /var/lib/mysql, something like this will work fine.

Code:

#!/bin/sh
tar -czpf mysql-backup.tar.gz /var/lib/mysql

This will preserve all database permissions as well, then if you need to restore just use:

Code:

cd / && tar -zxf mysql-backup.tar.gz

or a specific database, say test

cd / && tar -zxf mysql-backup.tar.gz var/lib/mysql/test

trickykid · 07-14-2008, 10:29 AM

Quote:

Originally Posted by fotoguy

A quicker way, and less scripting to backup mysql is to backup the mysql folder that contains the databases, depending on which distro you are using, it may be under /var/lib/mysql, something like this will work fine.

Code:

#!/bin/sh
tar -czpf mysql-backup.tar.gz /var/lib/mysql

This will preserve all database permissions as well, then if you need to restore just use:

Code:

cd / && tar -zxf mysql-backup.tar.gz

or a specific database, say test

cd / && tar -zxf mysql-backup.tar.gz var/lib/mysql/test

This is not reliable. If a database or table was getting written to at the time of backup, it could produce unreliable results. The only way to grab backups this way is to either use snapshots or shutdown MySQL, take the backup, then bring it back online. I don't recommend this on a live database, unless you can shut it down.

With mysqldump, you can lock the tables during the dump.

As for the error, try putting back ticks around the Variables that actually run commands.

Try this:

Code:

#!/bin/sh

# Date and File Info
NOW=`$(date +%Y-%m-%d_%Hh%M)`
FILE="mysql-$db.$NOW"

# Connection to MySQL Info
MUSER="root"
MPASS="password"
MHOST="localhost"

# Commands
MYSQL=`$(which mysql)`
MYSQLDUMP=`$(which mysqldump)`
GZIP=`$(which gzip)`

# Connection string to list Databases
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

# Run the MySQL dumps for each Database
for db in $DBS
    do
    $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE.sql
done

forrestt · 07-14-2008, 10:49 AM

The way I backup MySQL databases is to set up replication (this can even be done on the same machine), stop the slave, backup the slave, then restart the slave. I use tar to backup the data directory.

HTH

Forrest

0.o · 07-14-2008, 10:58 AM

Here is a function i wrote to backup a postgres and mysql database to a NFS server.

Code:

exec_backupdb () 
{
        /usr/bin/pg_dumpall > /backup/main/db/pg.fulldb.sql.${TODAY}

        if [ `echo $?` == 0 ]; then
                $ECHO "Postgres backup success $TODAY" >> /tmp/bckup_log

        else

                $ECHO "Postgres backup failure $TODAY" >> /tmp/bckup_log
                /bin/mailx "SOME NUMBER OR EMAIL" -s "Postgres backup failure $HOST $TODAY" </dev/null
        fi

        /usr/bin/mysqldump --all-databases -u root -pcoxdata1 > /backup/main/db/mysql.fulldb.sql.${TODAY}

        if [ `echo $?` == 0 ]; then
                $ECHO "MySQL backup success $TODAY" >> /tmp/bckup_log

        else

                $ECHO "MySQL backup failure $TODAY" >>/tmp/bckup_log
                /bin/mailx "SOME NUMBER OR EMAIL" -s "MySQL backup failure $HOST $TODAY
" </dev/null
        fi
}

I am sure you can alter this to do about anything you want.

williebens · 07-14-2008, 03:14 PM

Hello nawuza:

Here are the two files I use to backup mysql server.
The first one, dblist.sh goes into the database server and list all the databases there.
Then the second one takes the output of dblist.sh and backups the databases listed by dblist.sh
Just run dblist.sh first, and then dbbackups.sh

Call this file: dblist.sh
#!/bin/bash

DBPASS='mysqlpassword'
SQL='show databases;'

echo $SQL | mysql -u root --pass=$DBPASS > /opt/backup/192.168.1.123/full-backups/db-list.txt

Call this file: db-backups.sh
#!/bin/bash
# Set a value that we can use for a datestamp
DATE=`date +%Y-%m-%d`

# Our Base backup directory
BASEBACKUP="/opt/backup/192.168.1.123/full-backups/databases"
DBPASS="mysqlpassowrd"

for DATABASE in `cat /opt/backup/192.168.1.123/full-backups/db-list.txt`
do
# This is where we throw our backups.
FILEDIR="$BASEBACKUP/$DATABASE"

# Test to see if our backup directory exists.
# If not, create it.
if [ ! -d $FILEDIR ]
then
mkdir -p $FILEDIR
fi

echo -n "Exporting database: $DATABASE"

/usr/bin/mysqldump --user=root --pass=$DBPASS --opt $DATABASE > $FILEDIR/$DATABASE-$DATE.sql
tar cvzpf $FILEDIR/$DATABASE-$DATE.sql.tar.gz $FILEDIR/$DATABASE-$DATE.sql
rm -f $FILEDIR/$DATABASE-$DATE.sql
echo " ......[ Done ] "
done

cp -p /etc/my.cnf $BASEBACKUP

I hope this helps.
Thanks.
--Willie

FranDango · 07-14-2008, 06:02 PM

I would use --password='your-password' on the mysqldump line. On my system -p leads to request from the shell to enter a password.

Linux Archive

trickykid · 07-14-2008, 08:58 PM

Quote:

Originally Posted by FranDango

I would use --password='your-password' on the mysqldump line. On my system -p leads to request from the shell to enter a password.

Are you putting a space after the -p? If so, don't put a space.

nawuza · 07-14-2008, 09:51 PM

thanks a lot you guys...i will try each and every solution that you all write.

fotoguy · 07-15-2008, 07:03 AM

Quote:

Originally Posted by trickykid

This is not reliable. If a database or table was getting written to at the time of backup, it could produce unreliable results.

Ok that is one thing I didn't think of, thanks for pointing that out

nawuza · 07-15-2008, 07:35 AM

my idea to back up mysql database is like this.i just want to play it around in my local only.

1-i tar the mysql folder in the var/lib/mysql,the name of the tar is todays date.
2-i move the tar file into the my site in http://localhost/backup
3-then my other linux machine which have other script that wget http://localhost/backup/today.tgz

how do i know the file that the other machine download completely the tar file.how do i checked it?!sorry if my explanation are not easy to understand.

trickykid · 07-15-2008, 07:44 AM

Quote:

Originally Posted by nawuza

my idea to back up mysql database is like this.i just want to play it around in my local only.

1-i tar the mysql folder in the var/lib/mysql,the name of the tar is todays date.
2-i move the tar file into the my site in http://localhost/backup
3-then my other linux machine which have other script that wget http://localhost/backup/today.tgz

how do i know the file that the other machine download completely the tar file.how do i checked it?!sorry if my explanation are not easy to understand.

Remember, if you want to backup doing a cold type backup by just tarballing the /var/lib/mysql files and directories, you need to shutdown mysql, then back these up, then restart mysql.

As for checking if the other machine is done, well, you'll probably have to write a script to check and see if there is a process that is moving the file, or copying, however it's being run or copied over.

nawuza · 07-15-2008, 08:05 AM

Ok..shutdown the mysql service before backup it.

can you give me a guide on how to check whether the file is download completely?!for example the during the transferring tar file somehow occur a problem. how do it face the problem?!