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 |
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
Are you new to LinuxQuestions.org? Visit the following links:
Site Howto |
Site FAQ |
Sitemap |
Register Now
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
|
 |
|
07-14-2008, 12:13 AM
|
#1
|
Member
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66
Rep:
|
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.
|
|
|
07-14-2008, 01:45 AM
|
#2
|
LQ Guru
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.x
Posts: 18,442
|
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
|
|
|
07-14-2008, 02:24 AM
|
#3
|
Member
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66
Original Poster
Rep:
|
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
|
|
|
07-14-2008, 09:44 AM
|
#4
|
Senior Member
Registered: Mar 2003
Location: Brisbane Queensland Australia
Distribution: Custom Debian Live ISO's
Posts: 1,291
Rep:
|
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 09:56 AM.
|
|
|
07-14-2008, 10:29 AM
|
#5
|
LQ Guru
Registered: Jan 2001
Posts: 24,149
|
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
|
|
|
07-14-2008, 10:49 AM
|
#6
|
Senior Member
Registered: Mar 2004
Location: Cary, NC, USA
Distribution: Fedora, Kubuntu, RedHat, CentOS, SuSe
Posts: 1,288
Rep:
|
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
|
|
|
07-14-2008, 10:58 AM
|
#7
|
Member
Registered: May 2004
Location: Raleigh, NC
Distribution: Debian, Solaris, HP-UX, AIX
Posts: 208
Rep:
|
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.
|
|
|
07-14-2008, 03:14 PM
|
#8
|
Member
Registered: Jan 2008
Posts: 88
Rep:
|
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 03:15 PM.
|
|
|
07-14-2008, 06:02 PM
|
#9
|
Member
Registered: Jun 2008
Posts: 101
Rep:
|
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 04:59 AM.
|
|
|
07-14-2008, 08:58 PM
|
#10
|
LQ Guru
Registered: Jan 2001
Posts: 24,149
|
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.
|
|
|
07-14-2008, 09:51 PM
|
#11
|
Member
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66
Original Poster
Rep:
|
thanks a lot you guys...i will try each and every solution that you all write.
|
|
|
07-15-2008, 07:03 AM
|
#12
|
Senior Member
Registered: Mar 2003
Location: Brisbane Queensland Australia
Distribution: Custom Debian Live ISO's
Posts: 1,291
Rep:
|
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
|
|
|
07-15-2008, 07:35 AM
|
#13
|
Member
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66
Original Poster
Rep:
|
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 07:37 AM.
|
|
|
07-15-2008, 07:44 AM
|
#14
|
LQ Guru
Registered: Jan 2001
Posts: 24,149
|
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.
|
|
|
07-15-2008, 08:05 AM
|
#15
|
Member
Registered: Jan 2007
Location: High Castle
Distribution: FC8
Posts: 66
Original Poster
Rep:
|
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 02:01 PM.
|
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
|
Latest Threads
LQ News
|
|