#Database backups

#Shared settings for all database types

There are 2 required values in the jobs/backup.job

dir-localdumps = /var/iml-backup/backup
keep-days = 7

This defines the backup target for sql dumps and how long they will be kept locally.

There is an optional value to define the target directory for archived dumps. This value is used for couchdb2 only.

dir-dbarchive = /var/iml-backup/archive

see backup.job

#Profiles

In the folder plugins/localdump/profiles/ are several ini files.

They describe a database environment by the [detect] section.

If a profile matches then a database will be dumped or restored. For the detection you can verify

• existing files
• running processes
• open tcp port
  • localhost or a remote system
  • on a local machine (if no hostname is given) you can check the process using this port

After installation there are some ini files that contain a common

#File names

File convention: <DBTYPE>_<PROFILE>.ini

• <DBTYPE> must match a database dumper in plugins/localdump/ without extension “.sh”

• <DBTYPE>_<PROFILE> is the target folder to store backups.

  • <PROFILE> is a custom name to
  • _<PROFILE> can be empty to mark a local database of the given type.

#Detect

To detect the existance of you can use the following keys. If all found detect entries match it is counted as detected.

Key	Type	Description
binary	string	Binary that must be found in path. Use comma to separate multiple binaries.
file[]	string	full path of a file. It must match “type”. It can be given multiple times.
process	regex	A binary to be matched in the process list
tcp	integer	A tcp port number that must be in use
tcp-process	regex	A process name offering the local tcp port (regex for last column in netstat -tulpen). If process names can differ depending on an os use a pipe to set alternatives eg `postgres
tcp-target	string	A hostname for tcp port check, eg localhost
type	regex	a matching regex for `file -b FILE

Example:

Mysql detection in a docker container:

[detect]
process = 'mysqld|mariadb'
tcp = 13306
tcp-target = localhost
tcp-process = 'rootlesskit'

Multiple Sqlite files

[detect]
file[] = "/home/axel/data/docker/ciserver/data/imldeployment/data/database/logs.db"
file[] = "/home/axel/data/docker/ciserver/public_html/valuestore/data/versioncache.db"
type = "sqlite"

#Setter

If a profile detection was successful then values in the [set] section will be applied.

Key	Type	Description
su	string	User for su command; used for postgres
dbuser	string	Database user; for replacement
dbpassword	string	Password of database user; for replacement
params	string	cli paramaters für dump / restore tools.
env	string	extend environment with some variables, eg. export var1=“something here”. After backup/ restore this variables will be unset

Replacements that can be used for values params and env:

Key	Description
{dbpassword}	value of [set] -> dbpassword
{dbuser}	value of [set] -> dbuser
{tcp-port}	value of [detect] -> tcp-port
{tcp-target}	value of [detect] -> tcp-target

[set]

su = ''
dbuser = 'root'
dbpassword = '12345678'
params = '--port={tcp-port} --password={dbpassword} --user={dbuser} --host={tcp-target}'

env = 'export var1="happy meal"; export var2="new"; export var3="year!"'

#Backup sqlite

Sqlite files can be located anywhere in the filesystem. That’s why the cannot be located with an auto detection.

• In the detect section set type = "sqlite"
• Per database file set a line with the file[] = prefix

This is the plugins/localdump/profile/sqlite.ini.example:

# ======================================================================
#
# LOCAL SQLITE DATABASES
#
# ======================================================================

[detect]

# the filetype to detect using file command
type = "sqlite"

# list of files to backup
file[] = "/var/www/database/logs.db"
file[] = "/var/www/database/another.db"


[set]

su = ''
dbuser = ''
dbpassword = ''

params = ''
env = ''

# ----------------------------------------------------------------------

#Database types

Since March 2024 the database backup uses profiles that can define a database locally or remote. Database services that can be connected via tcp port to a target host can be accessed remotely as well now. You can setup a backup host in your network that fetches the databases from the other hosts or a doxker container.

The following feature table is incomplete.

Action \ database type	couchdb2	mysql	postgres	sqlite	ldap
local database - dump	✅ yes	✅ yes	✅ yes	✅ yes	✅ yes
local database - restore	✅ yes	✅ yes	✅ yes	✅ yes	❌ no
local docker - dump	✅ yes	✅ yes	✅ yes	✅ yes	❌ no
local docker - restore	✅ yes	✅ yes	◻️ ???	✅ yes	❌ no
remote database - dump	✅ yes	✅ yes	✅ yes	❌ no	❌ no
remote database - restore	✅ yes	✅ yes	◻️ ???	❌ no	❌ no