MySQL BackUp

[Tom Brown]

> I would like to know the following
> 
> 1) if i would like to take mysql incremental backup weekly
> 
> cp /var/lib/mysql/ /backup/mysqlbak/
> 
> how can i do it by above command as i would like to run cron weekly
> for take mysql backup.
> 
> i would like to take backup using cp command only as we have more then
> 1000 databases on one server.
> 
> IS THERE ANY BETTER WAY TO DO THIS

I would not just copy the files i wold script a MySQL export to do this 
as the files alone will not mean you have good consistent backups. I 
personally use the following script to dump all my db's into a directory 
and then zip them. The directory is cycled daily but you can add as many 
as you like and in my case this directory ends up offsite each day anyway.

Let me know how you get on





#!/bin/sh

# Archive all site stuff to seperate location

if [ -f /tmp/site-archive.lock ] ; then
         echo "Locked"
         exit 0
fi

touch /tmp/site-archive.lock

mv -f /opt/backup/archive/*.gz /opt/backup/archive/old

for database in `ls /opt/mysql/data | grep -v hostname | grep -v 
mysql.sock | grep -v my.cnf`
do
   /opt/mysql/bin/mysqldump -e -u root -ppasswordhere $database | gzip 
-9 -c > /opt/backup/archive/$database.sql.gz
done

rm /tmp/site-archive.lock