LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices



Reply
 
Search this Thread
Old 07-14-2008, 01:13 AM   #1
nawuza
Member
 
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66

Rep: Reputation: 15
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.
 
Old 07-14-2008, 02:45 AM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
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
 
Old 07-14-2008, 03:24 AM   #3
nawuza
Member
 
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66

Original Poster
Rep: Reputation: 15
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
 
Old 07-14-2008, 10:44 AM   #4
fotoguy
Senior Member
 
Registered: Mar 2003
Location: Brisbane Queensland Australia
Distribution: KirraMail Live Email Server
Posts: 1,281

Rep: Reputation: 61
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

Last edited by fotoguy; 07-14-2008 at 10:56 AM.
 
Old 07-14-2008, 11:29 AM   #5
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 199Reputation: 199
Quote:
Originally Posted by fotoguy View Post
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
 
Old 07-14-2008, 11:49 AM   #6
forrestt
Senior Member
 
Registered: Mar 2004
Location: Cary, NC, USA
Distribution: Fedora, Kubuntu, RedHat, CentOS, SuSe
Posts: 1,288

Rep: Reputation: 99
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
 
Old 07-14-2008, 11:58 AM   #7
0.o
Member
 
Registered: May 2004
Location: Raleigh, NC
Distribution: Debian, Solaris, HP-UX, AIX
Posts: 208

Rep: Reputation: 35
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.
 
Old 07-14-2008, 04:14 PM   #8
williebens
Member
 
Registered: Jan 2008
Posts: 88

Rep: Reputation: 16
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

Last edited by williebens; 07-14-2008 at 04:15 PM.
 
Old 07-14-2008, 07:02 PM   #9
FranDango
Member
 
Registered: Jun 2008
Posts: 101

Rep: Reputation: 15
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

Last edited by FranDango; 09-20-2008 at 05:59 AM.
 
Old 07-14-2008, 09:58 PM   #10
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 199Reputation: 199
Quote:
Originally Posted by FranDango View Post
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.
 
Old 07-14-2008, 10:51 PM   #11
nawuza
Member
 
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66

Original Poster
Rep: Reputation: 15
thanks a lot you guys...i will try each and every solution that you all write.
 
Old 07-15-2008, 08:03 AM   #12
fotoguy
Senior Member
 
Registered: Mar 2003
Location: Brisbane Queensland Australia
Distribution: KirraMail Live Email Server
Posts: 1,281

Rep: Reputation: 61
Quote:
Originally Posted by trickykid View Post
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
 
Old 07-15-2008, 08:35 AM   #13
nawuza
Member
 
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66

Original Poster
Rep: Reputation: 15
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.

Last edited by nawuza; 07-15-2008 at 08:37 AM.
 
Old 07-15-2008, 08:44 AM   #14
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 199Reputation: 199
Quote:
Originally Posted by nawuza View Post
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.
 
Old 07-15-2008, 09:05 AM   #15
nawuza
Member
 
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66

Original Poster
Rep: Reputation: 15
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?!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: Visual MySQL Database Design in MySQL Workbench LXer Syndicated Linux News 0 06-12-2008 08:00 PM
Create mysql user for backup of database timgerr Linux - Server 2 01-04-2008 11:44 AM
Backup software for MySql database blinton25 Linux - Software 5 06-01-2007 04:40 AM
MySQL :: Auto backup all newly created database Swakoo Linux - General 3 01-22-2007 10:49 PM
How to import a mysql database from backup WindowBreaker Slackware 6 03-17-2006 12:08 AM


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

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration