Mysql (mariadb) per-base backup

Hello.
I could not find the possibility in a documentation, but maybe anybody knows hot to make per-database backups with urbackup?
Now it backups whole mariadb database in one file, but I would prefer to have different files for different databases, it is much more convenient for restoring.

Any ideas, recipes?

Hi!
Use prefilebackup, you can put a script there, wich makes per database dumps in a directory, and yuo can backup this directory.
Of course you need extra storage for that.
It worked for me, i am not sure, there is any direct way for that.

Unfortunaly I do not have extra space for backup

This would involve listing the databases in the the /usr/local/share/urbackup/scripts/list script.

The second argument to the script that outputs the dump (/usr/local/share/urbackup/scripts/mariadbdump) is the output filename, so depending on that one backup different databases (e.g. make outputname mariadbdump_DATABASENAME.sql, then extract DATABASENAME from $1 in the script).

Thank you, I see it works exactly as I wanted

Thanks to @uroni a complete solution:
/usr/local/etc/urbackup/mariadbdump.conf

#!/bin/sh

#Enable MariaDB dump backup
MARIADB_DUMP_ENABLED=1

#Enable per-base backup
MARIADB_DUMP_PER_BASE=1

#Backup user account
MARIADB_BACKUP_USER=root

#Backup user password
MARIADB_BACKUP_PASSWORD=

MARIADB_DUMP=mysqldump

/usr/local/share/urbackup/scripts/list

#!/bin/sh

CDIR=`dirname $0`

. "/usr/local/etc/urbackup/mariadbdump.conf"
. "/usr/local/etc/urbackup/postgresqldump.conf"
. "/usr/local/etc/urbackup/postgresbase.conf"
. "/usr/local/etc/urbackup/mariadbxtrabackup.conf"

if [ "x$MARIADB_DUMP_ENABLED" != "x0" ]
then
        if [ "x$MARIADB_DUMP_PER_BASE" != "x0" ]
        then
                baselist=`mysql -u $MARIADB_BACKUP_USER -p=$MARIADB_BACKUP_PASSWORD -e 'show databases' -s --skip-column-names | grep -E -v 'information_schema|performance_schema'`
                for i in $baselist
                do
                        echo "scriptname=mariadbdump&outputname=mariadbdump_$i.sql"
                done
        else
                echo "scriptname=mariadbdump&outputname=mariadbdump.sql"
        fi
fi
if [ "x$POSTGRESQL_DUMP_ENABLED" != "x0" ]; then echo "scriptname=postgresqldump&outputname=postgresqldump.sql"; fi
if [ "x$POSTGRESQL_BASE_ENABLED" != "x0" ]; then echo "scriptname=postgresbase&outputname=postgresbase&tar=1&orig_path=$POSTGRESQL_BASE_DIR"; fi
if [ "x$MARIADB_XTRABACKUP_ENABLED" != "x0" ]; then echo "scriptname=mariadbxtrabackup&outputname=mariadbxtrabackup&tar=1&orig_path=$MARIADB_DATADIR"; fi

/usr/local/share/urbackup/scripts/mariadbdump

#!/bin/sh
#
# Copyright (c) 2014-2016 Martin Raiber 
#
# Permission is hereby granted, free of charge, to any person obtaining a 
# copy of this software and associated documentation files (the 
# "Software"), to deal in the Software without restriction, including 
# without limitation the rights to use, copy, modify, merge, publish, 
# distribute, sublicense, and/or sell copies of the Software, and to 
# permit persons to whom the Software is furnished to do so, subject to 
# the following conditions: 
#
# The above copyright notice and this permission notice shall be included 
# in all copies or substantial portions of the Software. 
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS 
# OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF 
# MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY 
# CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, 
# TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
# SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 

set -e

. /usr/local/etc/urbackup/mariadbdump.conf

alias errcho='>&2 echo'

TIME=`date`
database=`echo "$1" | sed 's/mariadbdump_//' | sed -e 's/\.sql$//'`
if [ "$database" != "" ] && [ "x$MARIADB_DUMP_PER_BASE" != "x0" ]
then
        errcho "Starting backup of MariaDB database $database at $TIME..."
        $MARIADB_DUMP --user=$MARIADB_BACKUP_USER --password=$MARIADB_BACKUP_PASSWORD "$database"
        TIME=`date`
        errcho "Backup of MariaDB database $database finished at $TIME."
else
        errcho "Starting backup of all MariaDB databases at $TIME..."
        $MARIADB_DUMP --user=$MARIADB_BACKUP_USER --password=$MARIADB_BACKUP_PASSWORD --all-databases
        TIME=`date`
        errcho "Backup of MariaDB finished at $TIME."
fi

exit 0
1 Like