LinuxQuestions.org - backup mysql database

Page 1 of 2

Show 50 post(s) from this thread on one page

LinuxQuestions.org (/questions/)

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - backup mysql database (https://www.linuxquestions.org/questions/linux-newbie-8/backup-mysql-database-655568/)

nawuza

07-14-2008 12:13 AM

backup mysql database

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 (Post 3213998)

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 (Post 3214511)

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 (Post 3214049)

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 (Post 3215065)

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?!

All times are GMT -5. The time now is 07:03 PM.

Page 1 of 2

Show 50 post(s) from this thread on one page