[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Re: Best method for stopping/starting MySQL?





19.10.2009 22:32, Joe kirjoitti:

19.10.2009 20:49, Tim Legg kirjoitti:
Hello again!

I would like to shutdown mysql periodically to make backups of the
databases. I would like to know what is the official Debian way of
stopping and restarting MySQL.




You might also consider a backup method which does not require MySQL to
be stopped. Here's what I use, as I have some InnoDB tables:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html


For some databases, daily:

#!/bin/bash

OF=/data/smb1/backups/mysql/important-$(date +%Y%m%d).sql.bz2

mysqldump -u backup -psecret --databases verses finance Calendar
domestic music MasterDirectory | bzip2 > $OF


or all of them, less often:

#!/bin/bash

OF=/data/smb1/backups/mysql/databases-$(date +%Y%m%d).sql.bz2

mysqldump -A -u backup -psecret | bzip2 > $OF


All my databases are human-driven and the backup jobs run when nothing
else will be happening, so I don't bother locking things, but you
obviously can. This all happens at the database level, so there's no
flushing to disc to worry about.

If you use phpMyAdmin, that can also backup and restore databases online
remotely using a browser, but I don't think it can be scripted locally.


I have this /etc/cron.daily/backup-mysql

---------------------------------------------------------------------------

#!/bin/sh

DATABASES=`mysql -h dbsrv --user=backup -psecret --skip-column-names
--batch -e "show databases" | egrep -v
information_schema|mysql|bacula|eroperhe`
BACKUPDIR=/usr/local/srv/dbbackup


echo [ Backing up databases from MySQL into $BACKUPDIR ]

for DB in $DATABASES; do
        echo -n "  Backing up" $DB..
        mysqldump -h dbsrv --user=backup -psecret --opt $DB | bzip2
>$BACKUPDIR/$DB.sql.bz2
        echo
done
echo [ Done ]

---------------------------------------------------------------------------

It backs up every database except those mentioned in egrep filter. Each
database into its own .sql file. The database is assumed to be in server
dbsrv, that can be left off if it is on localhost.

--
http://www.iki.fi/jarif/

Your boss climbed the corporate ladder, wrong by wrong.

Attachment: pgpiJ1VJsC3mr.pgp
Description: PGP signature


Reply to: