Backup All MySQL Databases

[Matt]

Matt submitted a new resource:

Backup All MySQL Databases - Script to backup all your MySQL Databases to individual files

#! /bin/bash

BACKUP_DIR="/root/dbbackups"
MYSQL_USER="root"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="PASSWORD"
MYSQLDUMP=/usr/bin/mysqldump

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"
done

Read more about this resource...

 

[Miguel Dias]

I was using this code to backup daily individual databases to my own synology using FTP

# Backup database and sent via FTP
#
# Database Data
DBNAME="Name of the DB"
DBUNAME="DB User Name"
DBPASSWD="DB Password"
BKPREFIX="backup prefix"
DIR="/home/.../backup"
# FTP Data
FADRESS="FTP Adress"
FPASSWD="FTP Password"
FDIR="/Directory to store the backup"
#
# Dump the database
Mdate="$(date +"%d-%m-%Y | %H:%M:%S")"
mysqldump -u$DBUNAME -p$DBPASSWD $DBNAME > $DIR/$BKPREFIX.$(/bin/date +%Y-%m-%d).sql
# Compress the database with gzip
/usr/bin/gzip -f $DIR/*.$(/bin/date +%Y-%m-%d).sql
# Remove uncompressed file
rm -r $DIR/*.sql
# Waiting cleaning directory
sleep 30
lftp -c "set ftp:list-options -a;
open ftp://SQL:$FPASSWD@$FADRESS;
lcd $DIR;
cd $FDIR;
mirror --reverse --verbose
"
# Server clean
rm -r $DIR/*.sql.gz

Also was using this with few changes to create a directory backup every month

 

[Matt]

This is the perl version I was using for FTP upload of my databases

#!/usr/bin/perl -w
# Z22SE.co.uk
# Used in conjunction with dbbackup.sh
# Uploads current database backup, and removes files from remote location more than $old
use Net::FTP;
use Date::Manip;

# FTP PARAMETERS
$ftp_backup = 1;
$today = UnixDate("today","%Y-%m-%d");
$old = UnixDate("5 days ago","%Y-%m-%d");
$ftp_host = "BACKUP HOST";
$ftp_port = "21";
$ftp_user = "USERNAME";
$ftp_pwd = "PASSWORD";
$ftp_dir = "databases";
$dir = "/DIRECTORY/OF/CHOICE";

# Array of Database Files to Upload
@files = ("$dir/NAMEOFCHOICE.$today.sql.bz2", "$dir/NAMEOFCHOICE2.$today.sql.bz2");

# Array of Old Database Files to be Deleted
@old_files = ("NAMEOFCHOICE.$old.sql.bz2", "NAMEOFCHOICE2.$old.sql.bz2");

&backup();

# UPLOADING BACKUP TO BACKUP SERVER
sub backup {
if($ftp_backup == 1)
{
        my $ftp = Net::FTP->new($ftp_host, Port => $ftp_port, Debug => 0)
          or die "Cannot connect to server: $@";

$ftp->login($ftp_user, $ftp_pwd)
          or die "Cannot login ", $ftp->message;

        $ftp->cwd($ftp_dir)
          or die "Can't CWD to remote FTP directory ", $ftp->message;

        $ftp->binary();

        foreach $file (@files) {
        $ftp->put($file)
          or warn "Upload failed ", $ftp->message;
        }

        foreach $oldfile (@old_files) {
        $ftp->delete($oldfile)
           or warn "Delete failed ", $ftp->message;
        }

        $ftp->quit();
}
}

 

[sage55]

I was using this code to backup daily individual databases to my own synology using FTP

# Backup database and sent via FTP
#
# Database Data
DBNAME="Name of the DB"
DBUNAME="DB User Name"
DBPASSWD="DB Password"
BKPREFIX="backup prefix"
DIR="/home/.../backup"
# FTP Data
FADRESS="FTP Adress"
FPASSWD="FTP Password"
FDIR="/Directory to store the backup"
#
# Dump the database
Mdate="$(date +"%d-%m-%Y | %H:%M:%S")"
mysqldump -u$DBUNAME -p$DBPASSWD $DBNAME > $DIR/$BKPREFIX.$(/bin/date +%Y-%m-%d).sql
# Compress the database with gzip
/usr/bin/gzip -f $DIR/*.$(/bin/date +%Y-%m-%d).sql
# Remove uncompressed file
rm -r $DIR/*.sql
# Waiting cleaning directory
sleep 30
lftp -c "set ftp:list-options -a;
open ftp://SQL:$FPASSWD@$FADRESS;
lcd $DIR;
cd $FDIR;
mirror --reverse --verbose
"
# Server clean
rm -r $DIR/*.sql.gz

Also was using this with few changes to create a directory backup every month

Miguel Dias thanks for posting this. I have a Synology myself and am really wanting to backup all the files and databases of my sites to my Synology as well for off-site backup since Matt has already configured my 3rd local drive as a local backup point.

This script at least gets me started on the database side. Thanks!