How to backup from a remote server

Question

MySQL

There was a need to make backups from a remote server (with Ubuntu) to my local server (also Ubuntu), where sites are stored, which I already asked on Habré :)

I solved the problem with files using rsync. But what about mysql?

Googled - found nothing interesting. I liked SypexDumper, but it is only for local backups, and I need to do it from a remote server.
I also read about mysqldump, but, as I understand it, it is also for the local server.

Tell me what to use. It is advisable to immediately give links to the manuals. :)

And yes, I'll say right away that I have limited rights on a remote server. So I can't install third party packages.

Answer 1

If you can connect to mysql remotely, then the same mysqldump will go.
But it's easier to dump locally, and then pull it, using the same rsync

Answer 2

Merge mysqldump into a local directory, then the same rsync or scp, after packing it into tar.gz (optional). Three line script.
The Unix-way is about using several small utilities together to solve complex problems.

Answer 3

Make backups of all databases with a script and save to a folder that is synchronized.
Here is a good implementation example written by bezumkin.ru/sections/hosting/261/

Answer 4

We put backupninja on the local server.
Set up a walker to a remote server using ssh key
Set up backup rules for files and mysql
We rejoice :)

Answer 5

automysqlbackup
No complaints so far. Manual

Answer 6

I also read about mysqldump, but, as I understand it, it is also for the local server.

Oh come on. what does this option do in the man page then?

·   --host=host_name, -h host_name
    Dump data from the MySQL server on the given host. The default host is localhost.

Answer 7

I use this library github.com/meskyanichi/backup , the ability to backup different databases, files, archive all this, merge it into services a la S3, Dropbox and just via ftp to a remote server and send messages about the results of work to mail and instant messengers.

Answer 8

Well, do the same sipex dumper and then rsync

Answer 9

Look at the mysqlbackuponline.com/ service . If there is phpMyAdmin on the remote server, this is what you need, because. You don't need to configure anything on the server. Otherwise, SSH access is required, or the appropriate ports must be opened and access granted to mysqldump.

Answer 10

ssh remote.host "mysqldump DATABASE --opt --dump-date --single-transaction | gzip -f" > remote.host-dump.sql.gzip

Options for my taste, gzip is also optional. The simplified version looks like this:

ssh remote.host "mysqldump database" > remote.host-dump.sql

Answer 11

I use this script.
The benefit is compressed data - one file per database.
And you can copy via ssh or ftp to a local machine.

#!/bin/bash


##########################################################
# Common options
##########################################################

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
TAR="$(which tar)"
RM="$(which rm)"


HOST="$(hostname)"
BACKUP_DIRECTORY="/var/www/_DUMP"






##########################################################
# MySQL options
##########################################################
MYSQL_USER="root" # Username
MYSQL_HOST="localhost" # Hostname
MYSQLDUMP_OPTIONS="--opt --default_character_set utf8"
MYSQL_BACKUP_DIR="$BACKUP_DIRECTORY/mysql" # Main directory where backup will be stored
MYSQL_SKIP_BACKUP_DATABASE_LIST="test information_schema mysql" # don't backup these databases


# get MYSQL_PASSWORD
read -s -p "Enter root MySQL password: " MYSQL_PASSWORD
echo "" 1>&2





#############################################################
# Backup MySQL databases
############################################################

# create backup dir
[ ! -d $MYSQL_BACKUP_DIR ] && mkdir -p $MYSQL_BACKUP_DIR || :


# Get MySQL database list
MYSQL_DATABASE_LIST="$($MYSQL -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASSWORD -Bse 'show databases')"

for db in $MYSQL_DATABASE_LIST
do
skipdb=-1

    if [ "$MYSQL_SKIP_BACKUP_DATABASE_LIST" != "" ];
    then
for i in $MYSQL_SKIP_BACKUP_DATABASE_LIST
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
fi

if [ "$skipdb" == "-1" ] ; then
FILE="$MYSQL_BACKUP_DIR/$db.$HOST.sql.gz"

     echo "Dump database: $db"

     # do all job in pipe,
     # connect to mysql using mysqldump for select mysql database
     # and pipe it out to gz file in backup dir
        $MYSQLDUMP $MYSQLDUMP_OPTIONS -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASSWORD $db | $GZIP -9 > $FILE

        echo "Done."
    fi
done