How to automatically backup mysql?

Question

MySQL

I want to make mysql backups of some databases.
Something to automatically make a backup, send it to the mailbox and drop it on my computer.

How can it be implemented?

I have a free vps server.

Answer 1

I use the "cron + mysqldump + dropbox" bundle, as a result, automatic backup delivery to my machine and backup storage in the cloud.

Answer 2

If such questions arise, then you should try
Sypex Dumper .
Of course, it is still beta, but it works very stable. Able to run from cron and upload files via ftp anywhere. Sending to soap can be implemented independently)

Answer 3

try www.opennet.ru/dev/fsbackup/

Answer 4

We go around all the database in a loop and remove the dumps

#!/bin/bash
USER="root"
PASSWORD="123123"
mkdir /var/backup/database/`date +%F`;
for DB in `mysql -u$USER -p$PASSWORD -N -e 'show databases' | awk '{print $1}'`; do
     mysqldump --user=$USER --host=$HOST --password=$PASSWORD ${DB} | gzip > /var/backup/database/`date +%F`/${DB}.sql.gz;
        echo "${DB} Backup";
    done
done

Answer 5

not aesthetically pleasing =) but... ps (relevant for small databases, lock-tables locks the database during the dump process)
bash script, add to cron:

#!/bin/sh
mysqldump -u<LOGIN> -p<PASS>  --lock-tables --opt <BASENAME> > /sqldata/dump.sql
cd /sqldata/
tar -zcvf sqldata.tgz *.sql
perl sendtoemail.pl

The script requires the /sqldata folder, sendtoemail.pl is located in /sqldata.
contents of sendtoemail.pl, MIME::Lite is required:

#!/usr/bin/perl -w
use MIME::Lite;

$msg = MIME::Lite->new(
  From    => '[email protected]',
  To      => '[email protected]',
  Subject => 'sqldata.tgz MySQL backup!',
  Type    => 'text/plain',
  Data    => "Here are the MySQL database backups.");

$msg->attach(Type=>"application/x-tar",
             Path =>"/sqldata/sqldata.tgz",
             Filename =>"sqldata.tgz");

$msg->send;

Answer 6

I can also recommend AutoMySQLBackup .
Configure and hang up in crowns. Able to send dumps to the mail - the MAILCONTENT = "files" parameter, mutt is required for work.