script/command for taking automatic backup of mysql database

[Chris Stark]

On Tue, 2005-08-02 at 14:58 +0530, Ankush Grover wrote:
> hey friends,
> 
> I am using FC3 with mysql 4.1(downloaded from mysql.com site).
>  
>  I want a script which automatically takes backup of the mysql
> database.I have one database "dental" and I want to setup a cron job
> for the database backup .
> 
> The command given in the script is :
> 
> mysqldump -u root -p  *F298CEE6BE03DAEB0D515166F40879C6DD1D24F3 
> dental > /var/database/dental.sql
> 
> If I try to execute the script from the command line it asks for the
> password where as the password is given in the command in my case the
> password for root user is  *F298CEE6BE03DAEB0D515166F40879C6DD1D24F3"
> .
> 
>  How can I avoid asking for password .Moreover I can't keep the
> password without encryption in the script.
> 
> Is there anyway  the script does not asks for the password and the
> password if given as argument or passed  in the script is encrypted ?
> 
> Encryption is done with the mysql   "select password('ankush') ' command.
> 
> 
> 
> Thanks & Regards
> 
> Ankush Grover
> 

Try this...

--begin--

#!/bin/sh
# Chris' quick-n-dirty MySQL backup script

# where to store your backups
BU_PATH="/home/sqlbackups"

# space-separated names of the databases you are backing up:
DBNAMES="webstats hrdata mysql"

# the mysql username that will be used
# to execute the backup
BU_USERNAME="mysqluser"
BU_PASSWORD="secret"

# the location of the mysqldump utility
DUMP=/usr/bin/mysqldump

# make a datestamp in YYYYMMDD format
DATE=`date +%Y%m%d`

for dbname in `echo $DBNAMES`
do
	# set the initial increment value to zero
	INC="0"

	# make the datastamp based filename
	BU_FILENAME="$dbname-$DATE"

	# increment if the filename already exists...
	while [ -f $BU_PATH/$BU_FILENAME-$INC.sql.gz ]
	do
		INC=`expr $INC + 1`
	done

	# make it all happen...
	$DUMP $dbname -u $BU_USERNAME --password=$BU_PASSWORD -c | gzip > $BU_PATH/$BU_FILENAME-$INC.sql.gz
done

echo
exit 0

--end--

Edit and save this script in your /etc/cron.d/daily folder with 0700
permissions, owned by root so anyone logging into your system won't be
able to view your password.

If I were you, I'd create a mysql user that has just enough privileges
to run this backup script (I don't remember off the top of my head, so
I'll leave that to you to look up). Running with root isn't necessary,
and it introduces the risk of having a script on your filesystem with
an unencrypted admin-level password.

Aloha,
-- 
Chris Stark
Musician, Linux User, & Grad Student
http://chrisstark.com/