Backup MySQL database

monkiki

New Member
Nov 21, 2013
9
1
1
Hi there,

I new to Proxmox and I wonder if the snapshot backup mode also contains a memory dump. The problem of backuping only the filesystem while a database like MySQL is running is that data keep on memory is flushed sometime and the backup may contain outdated data.

Or perhaps anyone can recommend me another backup strategy...

Thanks in advance.
 
Hi,
I find your question very interesting.
My backups have been done in the middle of the night when I know no one is using the servers.
Now that I was programming to do backups during the daylight, this will be a problem for the reason you just pointed.

This will not be the answer, but for my servers running mysql I use the http://sourceforge.net/projects/automysqlbackup/.

Best regards,
 
If you want a true backup use mysqldump to dump the database to the file system. You could schedule mysqldump to run 1 hour before doing the proxmox backup
 
If you want a true backup use mysqldump to dump the database to the file system. You could schedule mysqldump to run 1 hour before doing the proxmox backup
Hi,

The question is not how to make the backup, but how to deal with the in-memory databases when backuping up your VM in a Snapshot mode.

Best regards,
 
I was thinking in this option, but also would be interesting using the hook script (--script option of vzdump) to make this dump automatically on every snapshot. Sadly I can see little documentation about this script :(
 
Good point. I understand that it is not only related to MySQL.

Anyone know how to deal with in-memory databases when backuping?
 
Good point. I understand that it is not only related to MySQL.

Anyone know how to deal with in-memory databases when backuping?


Running mysql with FLUSH TABLES WITH READ LOCK before the backup will resolve the problem.
 
According to MySQL documentation, after the backup you need to execute "UNLOCK TABLES".
 
Hi,
I just did a couple of tests on running servers with mysql and snapshot backups, and set crash reports to see what the deal wasI had .
The tables don't need to get unlocked, they become available right after the backup finishes.
The application that relies on the database does crash, and become unresponsive, although everything came back as well.
Like it has been posted before, this is not only related to MySQL, but I had issues with Java applications and Oracle as well.

Best regards,
 
I don't understand what do you mean: you say that tables don't need to be unlocked, but the application crash. Anyway it's not good news. Are you running a Java Application server or a PHP based application?

Cheers.
 
Hi, I'm running a Java application server that relies on MySQL and another on Oracle.
Maybe it's all related, but as soon as the backup finishes, everything came back to normal without the need for the tabled to be unlocked.
I'm still taking notes and advancing on the tests, but I see a problem here, where the snapshot mode can cause data corruption.

Best regards,
 
Please, keep me informed about your test.

AFAIK, a backup best practice is dumping the database before the snapshot. This way you can restore it in case of disaster.
 
  • Like
Reactions: mjw
If you can afford the downtime it should be an easy solution to just use the "stop" backup mode. That should stop the DBMS, then the OS and thus give you a consistent state to back up. You may want to tune some things (e.g. GRUB timeout) to make the VM boot faster. @tom (proxmox staff member) suggests snapshot mode so maybe there are other downsides that I'm not aware of.

For snapshot mode there is a document from Red Hat that describes that QEMU guest agent will run commands from /etc/qemu-ga/fsfreeze-hook. So you should add you DB dump script there for maximum consistency (at least in CentOS and friends).

I didn't test any of this yet but I run multiple MariaDB, PostgreSQL and Neo4j VMs so I'll have to get this right.

have a great day! :)
 
In memory databases are a real pain. Even if you have a backup you still don't have an working ha solution without dataloss. Think about a crash of one hypervisor. Everything is RAM is lost.

I don't understand why people use in memory databases for production. Why not using big caches in RAM and have the data persistent on disk?

Then the caches are lost but the data is stimm working without a problem.

mysqldump is ok for small databases but if you need to backup some TB of data you will not have enough time to get the data into the dump. You will never have the time to restore from a dumpfile. No business can have a downtime for one day or so.

An other way is to use replication. I don't know if this is possible with mysql but with postgresql i can wait for the commit until the data is written on at least one slave. But this does not help for an user who will delete oder modify data.
 
Another possible way is to do a logfile switch before the snapshot is created. Depends on the load of the system when (or how long before) you need to do this. Followed by a tripple-sync and you should have a crash-consistent database with (hopefully) not much in the online transaction logfile (or how that is called in mysql). This is still not good, but better than more entries in the online logfile.
 
The Proxmox backup hook script is awesome. Here is my script to copy to external USB drives with or without PGP.

Code:
#!/usr/bin/perl -w

# Proxmox removeable media backup script. Schedule using STOP mode. zfs-snapshots not supported.

# Place this script in /usr/local/bin/backup-hook.pl
# Install command: echo "script: /usr/local/bin/backup-hook.pl" >> /etc/vzdump.conf
# requires: gpg pks keys (gpg-keygen) for encryption. Set $recipient and enable $encryption=1
# requires: usbmount (apt-get install usbmount)

use strict;
use File::Basename;

## Set these user variables before use
my $removable = "/media/usb/dump/";
my $mediatest = "/media/usb/lost+found/"; #Check for presence of removable media mounted.
my $encrypted = 1;
my $recipient = "Test User"; #gpg private key full name
my $debuglog = 0;
my $phase = shift;

if ($phase eq 'job-start' ||
    $phase eq 'job-end'  ||
    $phase eq 'job-abort') {

    my $dumpdir = $ENV{DUMPDIR};
    my $storeid = $ENV{STOREID};

    if ($debuglog) { print "HOOK-ENV: phase=$phase;dumpdir=$dumpdir;storeid=$storeid\n"; }

} elsif ($phase eq 'backup-start' ||
     $phase eq 'backup-end' ||
     $phase eq 'backup-abort' ||
     $phase eq 'log-end' ||
     $phase eq 'pre-stop' ||
     $phase eq 'pre-restart' ||
         $phase eq 'post-restart') {

    my $mode = shift; # stop/suspend/snapshot
    my $vmid = shift;
    my $vmtype = $ENV{VMTYPE}; # openvz/qemu
    my $dumpdir = $ENV{DUMPDIR};
    my $storeid = $ENV{STOREID};
    my $hostname = $ENV{HOSTNAME};
    my $tarfile = $ENV{TARFILE}; # tarfile fullpath available starting in phase 'backup-start'
    my $filename = basename($tarfile);
    my $logfile = $ENV{LOGFILE}; # logfile is only available in phase 'log-end'

    if ($debuglog) { print "HOOK-ENV: phase=$phase;dumpdir=$dumpdir;storeid=$storeid\n"; }

    if ($phase eq 'post-restart' &&  $mode eq 'stop') {

    ## Check for removable media by presence of lost+found folder EXT2/3/4
    system ("[ -d ".$mediatest." ]") == 0 ||
        die "Removable media not mounted. Skipping USB copy.";

    if ($encrypted) {

        ## Encrypt using gpg. Set recipient variable above.
            system ("gpg2 -v --batch --encrypt --compress-algo none --recipient '$recipient' --output $tarfile.gpg $tarfile") == 0 ||
                die "Error encryption failed.";

            # Remove older backups from removeable media
        system ("rm -f -v " . $removable . "vzdump-$vmtype-$vmid-*");

        # Copy encrypted to removable. Do not die on failure to ensure cleanup
        system ("rsync -av --stats $tarfile.gpg $removable") == 0 ||
                print "Error copy to removable media failed.";

            # Always remove temp encrypted file
        system ("rm -f -v $tarfile.gpg");

    } else {

        # Non-encrpyted copy to removable
            system ("cp -v $tarfile $removable") == 0 ||
                die "Error copy backup file to removable media failed.";

    }

    # Print directory listing to log
    print "Removable media folder ".$removable;
    system ("ls -lh $removable");
    }

} else {

    die "got unknown phase '$phase'";

}

exit (0);

Here is a linux bash script I run to backup all the MySQL databases and upload them to S3.

Code:
#!/bin/bash

# Backs up all databases and uploads to Amazon S3

main() {
#Defaults
 dbserver=127.0.0.1
 user=backupuser
 password=mypassword
 bakdir=/tmp/
 comp_pgm='xz -9e'
 comp_ext=xz
 bucket=mys3bucket

    for tbl in `mysql -h $dbserver -u $user -p$password -Bse 'show databases;' `
    do
        if [ "$tbl" == "information_schema" ] || [ "$tbl" == "performance_schema" ] || [ "$tbl" == "mysql" ] || [ "$tbl" == "test" ]
        then
            continue
        fi
        echo "Backing up database: $tbl"
        mysql_backup "$tbl"
        s3cmd --encrypt put $filename s3://$bucket
                rm $filename
    done

}


# Create a compressed copy of mysql database and store it in var filename
function mysql_backup {

    filename=$bakdir$1-myservername-`/bin/date +\%Y\%m\%d`.sql.$comp_ext

    mysqldump     --no-create-db \
            --opt \
            --add-drop-table \
            -Q \
            -h $dbserver \
            -u $user \
            -p$password \
            $1 | \
            $comp_pgm > \
            $filename

}

main "$@"
 
Last edited:

About

The Proxmox community has been around for many years and offers help and support for Proxmox VE, Proxmox Backup Server, and Proxmox Mail Gateway.
We think our community is one of the best thanks to people like you!

Get your subscription!

The Proxmox team works very hard to make sure you are running the best software and getting stable updates and security enhancements, as well as quick enterprise support. Tens of thousands of happy customers have a Proxmox subscription. Get yours easily in our online shop.

Buy now!