Here is how I have been doing it, for years, and I have used this to
restore from, which has always worked for me.
http://dl.getdropbox.com/u/340087/Drops/10.29.09/mysql_backup-0196a0c2-013914.txt
A quick explanation, and some caveats:
First, set the u and p variables to a mysql user and password that has
read access to all databases. You only need read access, no more, no
less.
I then set the permissions on this script itself to read/write/execute
for root only. There is a small window in which the data is being
dumped in which the permissions of the dump files are a little more
open than I wish them to be. I need to look into how to get MySql to
create the dump with a fixed set of owner, group, and permissions.
There are also ways to do with without exposing a password in a file,
but for my system, this was sufficient.
Here is how it works:
1) Variable for your MySql data dump storage location is set.
2) "show databases" is passed to MySql, in my case, the binary is called
mysql5, you may need to adjust the name and add a path
3) With a list of all your databases, the script can now iterate
through them
all, sending out the database.sql files
4) Right after the .sql files are dumped, permissions are reduced
5) The entire batch of databases are then compressed, with datestamp
as name
6) Archive file is then reduced to lesser permissions
7) Original .sql files are removed, now that there is one archive file
You can hook this to cron, or on OS X to launchd, and have it run on a
schedule. There are a lot of improvements that could be made. For
example, it may be possible to have the dump pipe to a compression on
the fly, which would then only require a quick tar of the data when
done.
Permissions certainly could be dealt with better. I was thinking to
`touch database.sql` with correct permissions, and then have MySql
overwrite that, but still not sure the most elegant and sane way to do
this.
The data could probably be appended to an archive set, saving the step
of removing all the .sql files. You could also locate files older
than x days, and remove them, only storing a fixed number of backups.
I needed backups, and this works for me. My main criteria was that I
wanted all databases backed up, and did not want to have to think
about modifying a script every time I added a new database. This
script is limited by the speed of your database, and the drives you
are dumping to, and will of course affect the performance of the
database as the dumps are happening.
I generally try to run this on a replication server, so I am not
hitting the live database. This way, you can have multiple MySql
machines all replicated to one machine, and then the dumps happen on a
non public facing machine. Add in some RAID and other backup
strategies, and you should be fine.
Feel free to modify the script or comment on improvements, I would
love to make it a better script. The most important thing to me is
that I have been able to use it to recover from.
Script is below:
#!/bin/sh
# Backup all MySql databases
# [10/29/2009 01:37:35 AM] scott@stripped
# I set permissions on this file like so:
# -rwx------ 1 root wheel 864 Oct 20 23:33 mysql_backup
# set date and time
time=`date +%m-%d-%y_%I-%M%p`
u="username"
p="password"
# set path to final destination, needs trailing slash
location="/backups/mysql/"
# set db_list to the list of databases
db_list=`echo "show databases" | /opt/local/bin/mysql5 -N -u$u -p$p`
for db in $db_list;
do
echo "dumping " $db "to " $location$db.sql
/opt/local/bin/mysqldump5 -u$u -p$p --opt $db > $location$db.sql
chown root:wheel $location$db.sql
chmod 0 $location$db.sql
done
echo "changing to directory " $location
cd $location
echo "Now in: `pwd`"
echo "begin tarballing"
tar cvfz $time.tgz *.sql
# set permissions on the final file
chown root:wheel $time.tgz
chmod 0 $time.tgz
echo "removing:"
ls -la $location*.sql
rm $location*.sql
echo "All your MySql Database are Belong to Us";
echo $location$time.tgz
--
Scott * If you contact me off list replace talklists@ with scott@ *
On Oct 28, 2009, at 10:33 PM, Ganeswar Mishra wrote:
> Hi Everyone,
> I am trying to backup a database regularly, without using
> Administrator
> tool in mysql,
> Can anyone help to write a scipt regarding backup database.
>
