Shell script to simply compress a folder and copy it

I want to make a Shell script that will compress (with zin and tar) the folder /var/lib/mysql/
and copy it once per day in the folder /root/sqlbackup/ with the days date like : /root/sqlbackup/backup2005-07-02/backup.zip and tar  etc
 
LVL 2
MaRiOsGRAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

umbraeCommented:
Easiest way:

#!/bin/sh
cp -r /var/lib/mysql/ /root/sqlbackup/mysql
tar -czf /root/sqlbackup/`date +%Y-%m-%d`backup.tgz /root/sqlbackup/mysql
rm -r /root/sqlbackup/mysql

and just add it to your cron jobs to run once a day.

-Umbrae
umbraeCommented:
Although it should be noted that if you really want to copy your databases, its considered a better idea to use mysqlhotcopy, because if you're doing any updates when you're copying your database, it can become corrupt in the backup.

One way that I copied all my databases via mysqlhotcopy:

#!/bin/sh

MYSQLDESTDIR=/root/sqlbackup/`date +%Y-%m-%d`

#This assumes root has passwordless access to mysql to show databases
ALLDBS=`mysql -Bse "show databases"`

#Create our tempdir
mkdir $MYSQLDESTDIR

#Fill our tempdir with the DB's
for curdb in $ALLDBS
do
  mysqlhotcopy -q $curdb $MYSQLDESTDIR
done

#Create your backup
tar -czf /root/sqlbackup/`date +%Y-%m-%d`backup.tgz $MYSQLDESTDIR

#Remove our temp DB dir
rm -r $MYSQLDESTDIR



HTH,
-Umbrae

MaRiOsGRAuthor Commented:
Sql nees authentication
should I change the line
 mysqlhotcopy -q $curdb $MYSQLDESTDIR

to mysqlhotcopy -u admin -p mypassword -q $curdb $MYSQLDESTDIR
 
???

also how we do the script to compress them in zip also ,not only tgz.



Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

umbraeCommented:
Yeah, you can change mysqlhotcopy to that and it should work fine.

Also, tgz stands for tarred and gzipped. Gzip does a good job, and I don't think you'd get any benefits from zipping it also, but if you wanted to you could just use the 'zip' command to do so after its a .tgz

(if you're worried you won't be able to decompress, you use tar -xzf <file> to decompress)
CodedKSenior Software EngineerCommented:
Listenig.

Geia sou patrioti :)
MaRiOsGRAuthor Commented:
umbrae

when I run the script i get

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

And since ie changed
  mysqlhotcopy -u admin -p password -q $curdb $MYSQLDESTDIR

and the error says user root i think it must be   this one&#347; fault :

mysql -Bse show databases




MaRiOsGRAuthor Commented:
I run mysql -Bse show databases by itself in the terminal and i get tha same error :
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

So i changed this to mysql -Bse show databases -p password and now I get the mysql help page..
so -Bse show databases must not be right :(

umbraeCommented:
You can just similarly add -u <username> -p <password> to that one as well then. (Additionally, I think when you're doing that for mysql theres no space between -p and <password>. So if that doesn't work, try removing the space between -p and <password>.)

HTH,
-Umbrae
umbraeCommented:
Exactly: mysql -u root -p<password> -Bse "show databases"
MaRiOsGRAuthor Commented:
I cant make it work

heres the script :

#!/bin/sh

MYSQLDESTDIR=/root/sqlbackup/`date +%Y-%m-%d`

#This assumes root has passwordless access to mysql to show databases
ALLDBS=`mysql -u admin -p mypassword -Bse "show databases"`

#Create our tempdir
mkdir $MYSQLDESTDIR

#Fill our tempdir with the DB's
for curdb in $ALLDBS
do
  mysqlhotcopy -u admin -p mypassword  -q $curdb $MYSQLDESTDIR
done

#Create your backup
tar -czf /root/sqlbackup/`date +%Y-%m-%d`backup.tgz $MYSQLDESTDIR

#Remove our temp DB dir
rm -r $MYSQLDESTDIR


When I run it it asks for Password, i type mypass and I get this :

ERROR 1049: Unknown database 'mypassword'
tar: Removing leading `/' from member names

MaRiOsGRAuthor Commented:
Ok i ve changed it to like this :

#!/bin/sh

MYSQLDESTDIR=/root/sqlbackup/`date +%Y-%m-%d`

#This assumes root has passwordless access to mysql to show databases
ALLDBS=`mysql -u admin -pmypass -Bse "show databases"`

#Create our tempdir
mkdir $MYSQLDESTDIR

#Fill our tempdir with the DB's
for curdb in $ALLDBS
do
  mysqlhotcopy -u admin -p mypass -q $curdb $MYSQLDESTDIR
done

#Create your backup
tar -czf /root/sqlbackup/`date +%Y-%m-%d`backup.tgz $MYSQLDESTDIR

#Remove our temp DB dir
rm -r $MYSQLDESTDIR

It works...but i get this error now :

'sitebuilder' is an empty database
No tables to hot-copy at /usr/bin/mysqlhotcopy line 369.
tar: Removing leading `/' from member names


The databases u can see if u run mysql -u admin -pmypass -Bse "show databases"  are :

horde
mdbill
mysql
newsgr
phpmyadmin_xMUtbXfaM9Pr
psa
sitebuilder
sql24
webforward





umbraeCommented:
Those aren't errors, friend. :) If you notice, it should've made the backup file correctly. Thats warnings, but not errors.

When it says 'no tables to copy', it's referring to the empty database you have (That database doesn't have any tables, so backing it up would be pointless - theres nothing to backup!)

And removing leading / from member names just has to do with how we're using tar (using an absolute path). It doesn't harm anything.

If you want to not see these messages, when you call this script, you can just use

myscript.sh > /dev/null 2>&1

to redirect output to the trash (meaning you won't see it)

HTH,
-Umbrae
MaRiOsGRAuthor Commented:
you are right but then I do ls
and there are no new files or folders created :(
MaRiOsGRAuthor Commented:
I was looking to wrong folder lol sorry!!!
it works great thankS!!!!

now only the zip thing remains to be done!

#Create your backup in zip
zip  /root/sqlbackup/`date +%Y-%m-%d`backup.zip $MYSQLDESTDIR
will this work ?
MaRiOsGRAuthor Commented:
I added this :

zip  /root/sqlbackup/`date +%Y-%m-%d`backup.zip $MYSQLDESTDIR

and it created the zip file but only with some folders inside
all the databases folders and files were missing.
umbraeCommented:
Like I said before, making it zip is negligable (I actually did a test, and it makes it less than 1% more compressed), but if you really want to you use:

zip -r thezipfile.zip thedirectorytozip

Taken from the man page found at:
http://www.lowfatlinux.com/linux-zip-manual.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScottMcNeeCommented:
Another Alternative is to use an existing collection of shell scripts that do a great deal of the work for you. I have found this very handy.

http://sourceforge.net/projects/automysqlbackup/

This collection of scripts simply need to be placed in the relevant crond folder ie. daily, hourly etc. The run creating sql dumps of the DB. Zipping the files up and automatically rotating the Archive. Via bzip or gzip.
MaRiOsGRAuthor Commented:
ok umbrae I changed it to  

zip -r  /root/sqlbackup/`date +%Y-%m-%d`backup.zip $MYSQLDESTDIR    

and it works fine
MaRiOsGRAuthor Commented:
ScottMcNee thanx for the link i ll check it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.