path: root/manuals/en/main/howto.tex

\section{Use a dummy device to test the backup}
\label{dummydevice}
\label{TestUsingFifoDevice}
If your are testing your configuration, but don't want to store the backup data,
it is possible to use a dummy FIFO device to test your configuration, see \ilink{Stored configuration}{SetupFifo}.

Obviously, it can not be used to do a restore.

\begin{bconfig}{FIFO Storage Device Configuration}
Device {
  Name = NULL
  Media Type = NULL
  Device Type = Fifo
  Archive Device = /dev/null
  LabelMedia = yes
  Random Access = no
  AutomaticMount = no
  RemovableMedia = no
  MaximumOpenWait = 60
  AlwaysOpen = no
}
\end{bconfig}



\section{Backup Of Third Party Databases}
\index[general]{Backup!of Third Party Databases}
\index[general]{Database!Backup Of Third Party}
\label{BackupOtherDBs}

If you are running a database in production mode on your machine, Bareos will
happily backup the files, but if the database is in use while Bareos is
reading it, you may back it up in an unstable state.

The best solution is to shutdown your database before backing it up, or use
some tool specific to your database to make a valid live copy perhaps by
dumping the database in ASCII format.

\subsection{Backup of MSSQL Databases with Bareos Plugin}
\index[general]{MSSQL Backup}
\index[general]{Database!MSSQL}
\index[general]{Plugin!MSSQL backup}
\label{MSSQL}

\sinceVersion{general}{MSSQL}{13.2.0}

\subsubsection {Preparation}
If you like to use the MSSQL-Plugin to backing up your Databases you need to consider some things:

\begin{itemize}

\item {\bf Database Mode}\\
The database need to run in {\bf Full Recovery Mode}. Otherwise you are not able to use differential and incremental backups or to use point in time recovery.\\
\warning{If you set the databases into the mentionend mode you have to consider some maintance facts. The database doesn't shrink or delete the logs unanttended, so you have to shrink them manual once a week and you have to truncate the logs once in a month.}

\item {\bf Security and Access}\\
For connections you can use a SQL-User or a integrated systemaccount (Windows NT user). Both connection types are supported.
	\begin{itemize}
        \item Standard Security \\
You have to provide user credentials within your options which do belong to user with the sufficent right performing restores and backups from the database. This way stands for an extra backup/restore user.
	\item Trusted Security \\
	You use a systemaccount which have the sufficent rights to performing backups and restores on a database. This systemaccount have to be same account like the {\bf bareos-filedeamon} runs on.
	\end{itemize}
\item{\bf Permissions and Roles}
	\begin{itemize}
	\item Server-Role\\
	The user should be in the groups {\bf sysadmin} and {\bf dbcreator}.
	\item Database permissions\\
	The user have to be a {\bf backupoperator} and {\bf dbowner} of the database which you like to backup.
	\end{itemize}
\end{itemize}
There is no difference for the rights and roles between using a systemaccount (trusted security method) or a extra backup user (standard security method).
Please keep in mind if you use the trusted security method you have to use the same system account like the bareos-filedeamon runs on.

\subsubsection{MSSQL Plugin Installation}
\label{MssqlPluginInstallation}

For Bareos $<$ 14.2, install the Bareos MSSQL plugin onto the MSSQL server you want to backup.
Bareos $>=$ 14.2 also allows to backup remote MSSQL servers (option \parameter{serveraddress}). 

\paragraph{Bareos Windows-Installer}

Install the Bareos filedaemon including the component "Bareos FileDameon Plugins".
Make sure, that you install the file daemon {\bf without the "compatible" option}.

\paragraph{Manual install}

After downloading the plugin you need to copy it into \path|C:\Program Files\Bareos\Plugins|.
Then you need to define the plugin directory and which plugin the {\bf bareos-filedaemon} should use.
You have to edit the {\bf bareos-filedaemon} resource in  \path|C:\Program Data\bareos-fd.conf| as follows:

\begin{bconfig}{MSSQL plugin configuration}
FileDaemon {
  Name = mssqlserver-fd
  Maximum Concurrent Jobs = 20

  # remove comment in next line to load plugins from specified directory
  Plugin Directory = "C:/Program Files/Bareos/Plugins"

  Plugin Names = "mssqlvdi"
  compatible = no  # this is the default since bareos 15
}
\end{bconfig}

%\warning{The Bareos filedaemon and Bareos director have to be the same version.}

\subsubsection{Plugin Test}

\begin{bconsole}{status client=mssqlserver-fd}
*<input>status client=mssqlserver-fd</input>
Connecting to Client mssqlserver-fd at 192.168.10.101:9102

mssqlserver-fd Version: 13.2.2 (12 November 2013)  VSS Linux Cross-compile Win64
Daemon started 18-Nov-13 11:51. Jobs: run=0 running=0.
Microsoft Windows Server 2012 Standard Edition (build 9200), 64-bit
 Heap: heap=0 smbytes=20,320 max_bytes=20,522 bufs=71 max_bufs=73
 Sizeof: boffset_t=8 size_t=8 debug=0 trace=1 bwlimit=0kB/s
Plugin Info:
 Plugin     : mssqlvdi-fd.dll
 Description: Bareos MSSQL VDI Windows File Daemon Plugin
 Version    : 1, Date: July 2013
 Author     : Zilvinas Krapavickas
 License    : Bareos AGPLv3
 Usage      :
  mssqlvdi:
  serveraddress=<hostname>:
  instance=<instance name>:
  database=<database name>:
  username=<database username>:
  password=<database password>:
  norecovery=<yes|no>:
  replace=<yes|no>:
  recoverafterrestore=<yes|no>:
  stopbeforemark=<log sequence number specification>:
  stopatmark=<log sequence number specification>:
  stopat=<timestamp>

 examples:
  timestamp: 'Apr 15, 2020 12:00 AM'
  log sequence number: 'lsn:15000000040000037'
\end{bconsole}

\subsubsection{Configure the FileSet}
To use the plugin you need to configure it in the fileset as a plugin resource. For each database instance you need to define a exclusive backup job and fileset.

\begin{bconfig}{MSSQL FileSet}
Fileset {
  Name = "Mssql"
  Enable VSS = no
  Include {
    Options {
      Signature = MD5
    }
    Plugin = "mssqlvdi:instance=default:database=myDatabase:username=bareos:password=bareos"
  }
}
\end{bconfig}

In this example we use the standard security method for the connection.

Used options in the plugin string are:

\begin{description}
  \item[mssqlvdi]
  This is the reference to the MSSQL plugin.
  \item[serveraddress] (\sinceVersion{fd}{MSSQL: serveraddress}{14.2.2}) Defines the server address to connect to (if empty defaults to localhost).
  \item[instance] Defines the instance within the database server.
  \item[database] Defines the database that should get backuped.
  \item[username and password] Username and Password are required, when the connection is done using a MSSQL user. If the systemaccount the bareos-fd runs with has succifient permissions, this is not required.
\end{description}

It is recommend to define an additional restore job. 

For every database separate job and FileSet are required.

\subsubsection{Run Backups}

Here you can see an example for a backup:

\begin{bconsole}{run MSSQL backup job}
*<input>run job=MSSQLBak</input>
Using Catalog "MyCatalog"
Run Backup job
JobName:  MSSQLBak
Level:    Full
Client:   mssqlserver-fd
Format:   Native
FileSet:  Mssql
Pool:     File (From Job resource)
Storage:  File (From Job resource)
When:     2013-11-21 09:48:27
Priority: 10
OK to run? (yes/mod/no): <input>yes</input>
Job queued. JobId=7
You have no messages.
*<input>mess</input>
21-Nov 09:48 bareos-dir JobId 7: Start Backup JobId 7, Job=MSSQLBak.2013-11-21_09.48.30_04
21-Nov 09:48 bareos-dir JobId 7: Using Device "FileStorage" to write.
21-Nov 09:49 bareos-sd JobId 7: Volume "test1" previously written, moving to end of data.
21-Nov 09:49 bareos-sd JobId 7: Ready to append to end of Volume "test1" size=2300114868
21-Nov 09:49 bareos-sd JobId 7: Elapsed time=00:00:27, Transfer rate=7.364 M Bytes/second

21-Nov 09:49 bareos-dir JobId 7: Bareos bareos-dir 13.4.0 (01Oct13):
  Build OS:               x86_64-pc-linux-gnu debian Debian GNU/Linux 7.0 (wheezy)
  JobId:                  7
  Job:                    MSSQLBak.2013-11-21_09.48.30_04
  Backup Level:           Full
  Client:                 "mssqlserver-fd" 13.2.2 (12Nov13) Microsoft Windows Server 2012 Standard Edition (build 9200), 64-bit,Cross-compile,Win64
  FileSet:                "Mssql" 2013-11-04 23:00:01
  Pool:                   "File" (From Job resource)
  Catalog:                "MyCatalog" (From Client resource)
  Storage:                "File" (From Job resource)
  Scheduled time:         21-Nov-2013 09:48:27
  Start time:             21-Nov-2013 09:49:13
  End time:               21-Nov-2013 09:49:41
  Elapsed time:           28 secs
  Priority:               10
  FD Files Written:       1
  SD Files Written:       1
  FD Bytes Written:       198,836,224 (198.8 MB)
  SD Bytes Written:       198,836,435 (198.8 MB)
  Rate:                   7101.3 KB/s
  Software Compression:   None
  VSS:                    no
  Encryption:             no
  Accurate:               no
  Volume name(s):         test1
  Volume Session Id:      1
  Volume Session Time:    1384961357
  Last Volume Bytes:      2,499,099,145 (2.499 GB)
  Non-fatal FD errors:    0
  SD Errors:              0
  FD termination status:  OK
  SD termination status:  OK
  Termination:            Backup OK
\end{bconsole}

At least you gain a full backup which contains the follow:

\footnotesize
\begin{verbatim}
@MSSQL/
@MSSQL/default/
@MSSQL/default/myDatabase/
@MSSQL/default/myDatabase/db-full
\end{verbatim}
\normalsize

So if you perform your first full backup your are capable to perfom differntial and incremental backups.\\

Differntial FileSet example:

\footnotesize
\begin{verbatim}
/@MSSQL/
/@MSSQL/default/
/@MSSQL/default/myDatabase/
/@MSSQL/default/myDatabase/db-full
/@MSSQL/default/myDatabase/db-diff
\end{verbatim}
\normalsize

Incremental FileSet example:

\footnotesize
\begin{verbatim}
*@MSSQL/
  *default/
    *myDatabase/
      *db-diff
      *db-full
      *log-2013-11-21 17:32:20
\end{verbatim}
\normalsize

\subsubsection{Restores}

If you want to perfom a restore of a full backup without differentials or incrementals you have
some options which helps you to restore even the corrupted database still exist.
But you have to specifiy the options like plugin, instance and database during every backup.

\begin{description}
  \item[replace=\textless yes{\textbar}no\textgreater]
  With this option you can replace the database if it still exist.
  \item[instance]
  Defines the server instance whithin the database is running.
  \item[database]
  Defines the database you want to backup.
\end{description}

If you want to restore the actual backup to a set of backup files which you can use to
restore a database under an new name or perform any kind of special operations using
for example the sql management studio, you can use a where setting for the restore
other then '/'. When the where is set to '/' it will restore to the Virtual Device Interface
(VDI).

When you specify for restore a where path which is lets say 'c:/temp' the plugin will restore
the selected backup files under a relocated path under c:/temp/@MSSQL@/...

Example for a full restore:

\begin{bconsole}{restore MSSQL database}
*<input>restore client=mssqlserver-fd</input>
Using Catalog "MyCatalog"

First you select one or more JobIds that contain files
to be restored. You will be presented several methods
of specifying the JobIds. Then you will be allowed to
select which files from those JobIds are to be restored.

To select the JobIds, you have the following choices:
     1: List last 20 Jobs run
     2: List Jobs where a given File is saved
     3: Enter list of comma separated JobIds to select
     4: Enter SQL list command
     5: Select the most recent backup for a client
     6: Select backup for a client before a specified time
     7: Enter a list of files to restore
     8: Enter a list of files to restore before a specified time
     9: Find the JobIds of the most recent backup for a client
    10: Find the JobIds for a backup for a client before a specified time
    11: Enter a list of directories to restore for found JobIds
    12: Select full restore to a specified Job date
    13: Cancel
Select item:  (1-13): <input>5</input>
Automatically selected FileSet: Mssql
+-------+-------+----------+-------------+---------------------+------------+
| JobId | Level | JobFiles | JobBytes    | StartTime           | VolumeName |
+-------+-------+----------+-------------+---------------------+------------+
|     8 | F     |        1 | 198,836,224 | 2013-11-21 09:52:28 | test1      |
+-------+-------+----------+-------------+---------------------+------------+
You have selected the following JobId: 8

Building directory tree for JobId(s) 8 ...
1 files inserted into the tree.

You are now entering file selection mode where you add (mark) and
remove (unmark) files to be restored. No files are initially added, unless
you used the "all" keyword on the command line.
Enter "done" to leave this mode.

cwd is: /
$ <input>mark *</input>
1 file marked.
$ <input>done</input>
Bootstrap records written to /var/lib/bareos/bareos-dir.restore.4.bsr

The job will require the following
   Volume(s)                 Storage(s)                SD Device(s)
===========================================================================

    test1                     File                      FileStorage

Volumes marked with "*" are online.


1 file selected to be restored.

The defined Restore Job resources are:
     1: RestoreMSSQL
     2: RestoreFiles
Select Restore Job (1-2): <input>1</input>
Using Catalog "MyCatalog"
Run Restore job
JobName:         RestoreMSSQL
Bootstrap:       /var/lib/bareos/bareos-dir.restore.4.bsr
Where:           /
Replace:         Always
FileSet:         Mssql
Backup Client:   mssqlserver-fd
Restore Client:  mssqlserver-fd
Format:          Native
Storage:         File
When:            2013-11-21 17:12:05
Catalog:         MyCatalog
Priority:        10
Plugin Options:  *None*
OK to run? (yes/mod/no): <input>mod</input>
Parameters to modify:
     1: Level
     2: Storage
     3: Job
     4: FileSet
     5: Restore Client
     6: Backup Format
     7: When
     8: Priority
     9: Bootstrap
    10: Where
    11: File Relocation
    12: Replace
    13: JobId
    14: Plugin Options
Select parameter to modify (1-14): <input>14</input>
Please enter Plugin Options string: <input>mssqlvdi:instance=default:database=myDatabase:replace=yes</input>
Run Restore job
JobName:         RestoreMSSQL
Bootstrap:       /var/lib/bareos/bareos-dir.restore.4.bsr
Where:           /
Replace:         Always
FileSet:         Mssql
Backup Client:   mssqlserver-fd
Restore Client:  mssqlserver-fd
Format:          Native
Storage:         File
When:            2013-11-21 17:12:05
Catalog:         MyCatalog
Priority:        10
Plugin Options:  mssqlvdi:instance=default:database=myDatabase:replace=yes
OK to run? (yes/mod/no): <input>yes</input>
Job queued. JobId=10
You have messages.
*<input>mess</input>
21-Nov 17:12 bareos-dir JobId 10: Start Restore Job RestoreMSSQL.2013-11-21_17.12.26_11
21-Nov 17:12 bareos-dir JobId 10: Using Device "FileStorage" to read.
21-Nov 17:13 damorgan-sd JobId 10: Ready to read from volume "test1" on device "FileStorage" (/storage).
21-Nov 17:13 damorgan-sd JobId 10: Forward spacing Volume "test1" to file:block 0:2499099145.
21-Nov 17:13 damorgan-sd JobId 10: End of Volume at file 0 on device "FileStorage" (/storage), Volume "test1"
21-Nov 17:13 damorgan-sd JobId 10: End of all volumes.
21-Nov 17:13 bareos-dir JobId 10: Bareos bareos-dir 13.4.0 (01Oct13):
  Build OS:               x86_64-pc-linux-gnu debian Debian GNU/Linux 7.0 (wheezy)
  JobId:                  10
  Job:                    RestoreMSSQL.2013-11-21_17.12.26_11
  Restore Client:         mssqlserver-fd
  Start time:             21-Nov-2013 17:12:28
  End time:               21-Nov-2013 17:13:21
  Files Expected:         1
  Files Restored:         1
  Bytes Restored:         198,836,224
  Rate:                   3751.6 KB/s
  FD Errors:              0
  FD termination status:  OK
  SD termination status:  OK
  Termination:            Restore OK
\end{bconsole}

\paragraph{Restore a Backup Chain}

If you like to restore a specific state or a whole chain consists of full, incremental and differential backups you need to use the "norecovery=yes" option. After this the database is in "recovery mode". You can also use a option which put the database right after the restore back into the right mode. If you like to restore certains point with protocols or
"LSN" it it not recommend to work with this option.

\begin{description}
  \item[norecovery=\textless yes{\textbar}no\textgreater]
  This option must be set to yes, if the database server should not do a automatic recovery after the backup. Instead, additional manual maintenace operations are possible.
  \item[recoverafterrestore=\textless yes{\textbar}no\textgreater]
  With this command the database is right after backup in the correct mode. If you not use this you have to use the followed tsql statement:
  \footnotesize
  \begin{verbatim}
    Restore DATABASE yourDatabase WITH RECOVERY
    GO
  \end{verbatim}
  \normalsize
  \item[stopbeforemark=\textless log sequence number specification\textgreater]
  used for point in time recovery.
  \item[stopatmark=\textless log sequence number specification\textgreater]
  used for point in time recovery.
  \item[stopat=\textless timestamp\textgreater]
  used for point in time recovery.
\end{description}

Followed is a example for a restore of full, differential and incremental backup with a replace of the original database:

\begin{bconsole}{restore MSSQL database chain}
*<input>restore client=mssqlserver-fd</input>

First you select one or more JobIds that contain files
to be restored. You will be presented several methods
of specifying the JobIds. Then you will be allowed to
select which files from those JobIds are to be restored.

To select the JobIds, you have the following choices:
     1: List last 20 Jobs run
     2: List Jobs where a given File is saved
     3: Enter list of comma separated JobIds to select
     4: Enter SQL list command
     5: Select the most recent backup for a client
     6: Select backup for a client before a specified time
     7: Enter a list of files to restore
     8: Enter a list of files to restore before a specified time
     9: Find the JobIds of the most recent backup for a client
    10: Find the JobIds for a backup for a client before a specified time
    11: Enter a list of directories to restore for found JobIds
    12: Select full restore to a specified Job date
    13: Cancel
Select item:  (1-13): <input>5</input>
Automatically selected FileSet: Mssql
+-------+-------+----------+-------------+---------------------+------------+
| JobId | Level | JobFiles | JobBytes    | StartTime           | VolumeName |
+-------+-------+----------+-------------+---------------------+------------+
|     8 | F     |        1 | 198,836,224 | 2013-11-21 09:52:28 | test1      |
|    11 | D     |        1 |   2,555,904 | 2013-11-21 17:19:45 | test1      |
|    12 | I     |        1 |     720,896 | 2013-11-21 17:29:39 | test1      |
+-------+-------+----------+-------------+---------------------+------------+
You have selected the following JobIds: 8,11,12

Building directory tree for JobId(s) 8,11,12 ...
3 files inserted into the tree.

You are now entering file selection mode where you add (mark) and
remove (unmark) files to be restored. No files are initially added, unless
you used the "all" keyword on the command line.
Enter "done" to leave this mode.

cwd is: /
$ <input>mark *</input>
3 files marked.
$ <input>lsmark</input>
*@MSSQL/
  *default/
    *myDatabase/
      *db-diff
      *db-full
      *log-2013-11-21 17:32:20
$ <input>done</input>
Bootstrap records written to /var/lib/bareos/bareos-dir.restore.6.bsr

The job will require the following
   Volume(s)                 Storage(s)                SD Device(s)
===========================================================================

    test1                     File                      FileStorage

Volumes marked with "*" are online.


1 file selected to be restored.

The defined Restore Job resources are:
     1: RestoreMSSQL
     2: RestoreFiles
Select Restore Job (1-2): <input>1</input>
Run Restore job
JobName:         RestoreMSSQL
Bootstrap:       /var/lib/bareos/bareos-dir.restore.6.bsr
Where:           /
Replace:         Always
FileSet:         Mssql
Backup Client:   mssqlserver-fd
Restore Client:  mssqlserver-fd
Format:          Native
Storage:         File
When:            2013-11-21 17:34:23
Catalog:         MyCatalog
Priority:        10
Plugin Options:  *None*
OK to run? (yes/mod/no): <input>mod</input>
Parameters to modify:
     1: Level
     2: Storage
     3: Job
     4: FileSet
     5: Restore Client
     6: Backup Format
     7: When
     8: Priority
     9: Bootstrap
    10: Where
    11: File Relocation
    12: Replace
    13: JobId
    14: Plugin Options
Select parameter to modify (1-14): <input>14</input>
Please enter Plugin Options string: <input>mssqlvdi:instance=default:database=myDatabase:replace=yes:norecovery=yes</input>
Run Restore job
JobName:         RestoreMSSQL
Bootstrap:       /var/lib/bareos/bareos-dir.restore.6.bsr
Where:           /
Replace:         Always
FileSet:         Mssql
Backup Client:   mssqlserver-fd
Restore Client:  mssqlserver-fd
Format:          Native
Storage:         File
When:            2013-11-21 17:34:23
Catalog:         MyCatalog
Priority:        10
Plugin Options:  mssqlvdi:instance=default:database=myDatabase:replace=yes:norecovery=yes
OK to run? (yes/mod/no): <input>yes</input>
Job queued. JobId=14
21-Nov 17:34 bareos-dir JobId 14: Start Restore Job RestoreMSSQL.2013-11-21_17.34.40_16
21-Nov 17:34 bareos-dir JobId 14: Using Device "FileStorage" to read.
21-Nov 17:35 damorgan-sd JobId 14: Ready to read from volume "test1" on device "FileStorage" (/storage).
21-Nov 17:35 damorgan-sd JobId 14: Forward spacing Volume "test1" to file:block 0:2499099145.
21-Nov 17:35 damorgan-sd JobId 14: End of Volume at file 0 on device "FileStorage" (/storage), Volume "test1"
21-Nov 17:35 damorgan-sd JobId 14: End of all volumes.
21-Nov 17:35 bareos-dir JobId 14: Bareos bareos-dir 13.4.0 (01Oct13):
  Build OS:               x86_64-pc-linux-gnu debian Debian GNU/Linux 7.0 (wheezy)
  JobId:                  14
  Job:                    RestoreMSSQL.2013-11-21_17.34.40_16
  Restore Client:         mssqlserver-fd
  Start time:             21-Nov-2013 17:34:42
  End time:               21-Nov-2013 17:35:36
  Files Expected:         1
  Files Restored:         3
  Bytes Restored:         202,113,024
  Rate:                   3742.8 KB/s
  FD Errors:              0
  FD termination status:  OK
  SD termination status:  OK
  Termination:            Restore OK
\end{bconsole}
\hide{$}


\subsection{Backup of a PostgreSQL Database}
\index[general]{PostgreSQL!Backup}
\index[general]{Database!PostgreSQL!Backup}
\label{backup-postgresql}

In this section, we describe different methods how to do backups of the PostgreSQL databases.


\subsubsection{Backup of a PostgreSQL Database by using the RunScript directive}
\index[general]{RunScript!Example}

One method to backup a PostgreSQL database is to use the \command{pg_dumpall} tool to dump the database
into a file and then backup it as a normal file. After the backup, the file can be
removed. It may also be an option not to remove it, so that the latest version is
always available immediately. On the next job run it will be overwritten anyway.

This can be done by using \linkResourceDirective{Dir}{Job}{Run Script} directives inside a
Job Resource, for example:

\begin{bconfig}{RunScript job resource for a PostgreSQL backup}
Job {
  Name = "BackupDatabase"
  JobDefs = "DefaultJob"
  Client = dbserver-fd
  Level = Full
  FileSet="Database"

  # This creates a dump of our database in the local filesystem on the client
  RunScript {
    FailJobOnError = Yes
    RunsOnClient = Yes
    RunsWhen = Before
    Command = "sh -c 'pg_dumpall -U postgres > /var/lib/bareos/postgresql_dump.sql'"
  }

  # This deletes the dump in our local filesystem on the client
  RunScript {
    RunsOnSuccess = Yes
    RunsOnClient = Yes
    RunsWhen = After
    Command = "rm /var/lib/bareos/postgresql_dump.sql"
  }
}

FileSet {
  Name = "Database"
  Include {
    Options {
      signature = MD5
      compression = gzip
    }
    # database dump file
    File = "/var/lib/bareos/postgresql_dump.sql"
  }
}
\end{bconfig}

Note that redirecting the \command{pg_dumpall} output to a file requires
to run the whole command line through a shell, otherwise the \command{pg_dumpall}
would not know what do with the \command{>} character and the job would fail.
As no shell features like redirection or piping are used for the \command{rm},
the \command{sh -c} is not needed there.
See \linkResourceDirective{Dir}{Job}{Run Script} for more details.

\subsubsection{Backup of a PostgreSQL Databases by using the bpipe plugin}
\index[general]{bpipe!PostgreSQL backup}

Instead of creating a temporary database dump file,
the bpipe plugin can be used. 
For general information about bpipe, see the \nameref{bpipe} section. 
The bpipe plugin is configured inside the \linkResourceDirective{Dir}{FileSet}{Include} section of a File Set, e.g.:
\begin{bconfig}{bpipe directive for PostgreSQL backup}
FileSet {
  Name = "postgresql-all"
  Include {
    Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=pg_dumpall -U postgres:writer=psql -U postgres"
    Options {
      signature = MD5
      compression = gzip
    }
  }
}
\end{bconfig}

This causes the File Daemon to call bpipe plugin, which will write its data into the "pseudo" file \file{/POSTGRESQL/dump.sql} by
calling the program \command{pg_dumpall -U postgres} to read the data during backup. The \command{pg_dumpall} command outputs all 
the data for the database, which will be read by the plugin and stored in the backup. During restore, the data that was backed up will
be sent to the program specified in the last field, which in this case is psql. When psql is called, it will read the data sent to it by 
the plugin then write it back to the same database from which it came from.

This can also be used, to backup a database that is running on a remote host:
\begin{bconfig}{bpipe directive to backup a PostgreSQL database that is running on a remote host}
FileSet {
  Name = "postgresql-remote"
  Include {
    Plugin = "bpipe:file=/POSTGRESQL/dump.sql:reader=pg_dumpall -h <hostname> -U <username> -W <password>:writer=psql -h <hostname> -U <username> -W <password>"
    Options {
      signature = MD5
      compression = gzip
    }
  }
}
\end{bconfig}

\subsubsection{Backup of a PostgreSQL Databases by using the PGSQL-Plugin}
\index[general]{Plugin!PostgreSQL Backup}
\label{backup-postgresql-plugin}

The PGSQL-Plugin supports an online (Hot) backup of database files and database transaction logs (WAL) archiving (with pgsql-archlog) and backup. With online database and transaction logs the backup plugin can perform Poin-In-Time-Restore up to a single selected transaction or date/time. 

Database recovery is performed fully automatic with dedicated pgsql-restore utility.

For a full description, see \url{https://github.com/bareos/contrib-pgsql-plugin/wiki}.



\subsection{Backup of a MySQL Database}
\index[general]{MySQL!Backup}
\index[general]{Database!MySQL!Backup}
\label{backup-mysql}

In this section, we describe different methods to do a full backup of a MySQL database.

\subsubsection{Backup of MySQL Databases using the Bareos MySQL Percona xtrabackup Plugin}
\index[general]{Plugin!MySQL Backup}
\index[general]{Percona xtrabackup}
\index[general]{xtrabackup}
\label{backup-mysql-xtrabackup}

This plugin is available since \sinceVersion{fd}{MySQL Incremental Backup Plugin for using Percona xtrabackup}{16.2.4},
it uses the \command{xtrabackup} tool from Percona to perform full and incremental hot-backups of MySQL / MariaDB tables of type InnoDB.
It can also backup MyISAM tables but only as full backups. On restore it requires a preparation using the xtrabackup tools, before the tables can be restored. 
If you simply want to backup full dumps, then using \nameref{backup-mysql-python} is the easier way.

\paragraph{Prerequisites}
% \newline

Install the xtrabackup tool from Percona. Documentation and packages are available here: \url{https://www.percona.com/software/mysql-database/percona-xtrabackup}.
The plugin was successfully tested with xtrabackup versions 2.3.5 and 2.4.4.

As it is a Python plugin,
it will also require to have the package \package{bareos-filedaemon-python-plugin} installed on the \bareosFd, where you run it.

For authentication the \file{.mycnf} file of the user running the \bareosFd.
Before proceeding, make sure that xtrabackup can connect to the database and create backups.

\paragraph{Installation}
% \newline

Make sure you have met the prerequisites.
Install the files \file{BareosFdPercona.py} and \file{bareos-fd-percona.py} in your Bareos plugin directory (usually \directory{/usr/lib64/bareos/plugins}). These files are available
in the Git repository \url{https://github.com/bareos/bareos-contrib/tree/master/fd-plugins/bareos_percona}.

\paragraph{Configuration}
% \newline

Activate your plugin directory in the fd resource conf on the client. See \nameref{fdPlugins} for more about plugins in general.

\begin{bareosConfigResource}{bareos-fd}{client}{mysql-fd}
Client {
  Name = mysql-fd
  #...
  Plugin Directory = /usr/lib64/bareos/plugins
  Plugin Name = "python"
}
\end{bareosConfigResource}

Now include the plugin as command-plugin in the Fileset resource:

\begin{bareosConfigResource}{bareos-dir}{fileset}{mysql}
FileSet {
    Name = "mysql"
    Include  {
        Options {
            compression=GZIP
            signature = MD5
        }
        File = /etc
        #...
        Plugin = "python:module_path=/usr/lib64/bareos/plugins:module_name=bareos-fd-percona:mycnf=/root/.my.cnf"
    }
}
\end{bareosConfigResource}

If used this way, the plugin will call xtrabackup to create a backup of all databases in the xbstream format.
This stream will be processed by Bareos. If job level is incremental,
xtrabackup will perform an incremental backup since the last backup -- for InnoDB tables.
If you have MyISAM tables, you will get a full backup of those.

You can append options to the plugin call as key=value pairs, separated by ':'.
The following options are available:

\begin{itemize}
 \item With \parameter{mycnf} you can make xtrabackup use a special mycnf-file with login credentials.
 \item \parameter{dumpbinary} lets you modify the default command xtrabackup.
 \item \parameter{dumpoptions} to modify the options for xtrabackup. Default setting is:  \command{--backup --datadir=/var/lib/mysql/ --stream=xbstream --extra-lsndir=/tmp/individual_tempdir}
 \item \parameter{restorecommand} to modify the command for restore. Default setting is: \command{xbstream -x -C}
 \item \parameter{strictIncremental}: By default (false), an incremental backup will create data, even if the Log Sequence Number (LSN) wasn't increased since last backup. 
 This is to ensure, that eventual 
 changes to MYISAM tables get into the backup. MYISAM does not support incremental backups, you will always get a full bakcup of these tables. 
 If set to true, no data will be written into backup, if the LSN wasn't changed.
 \end{itemize}
 
\paragraph{Restore}

With the usual Bareos restore mechanism a file-hierarchy will be created on the restore client under the default restore location:

\directory{/tmp/bareos-restores/_percona/}

Each restore job gets an own subdirectory, because Percona expects an empty directory. In that subdirectory, a new directory is created for every backup job that was part of the Full-Incremental sequence.

The naming scheme is: \directory{fromLSN_toLSN_jobid}

Example:
\begin{verbatim}
/tmp/bareos-restores/_percona/351/
|-- 00000000000000000000_00000000000010129154_0000000334
|-- 00000000000010129154_00000000000010142295_0000000335
|-- 00000000000010142295_00000000000010201260_0000000338
\end{verbatim}

This example shows the restore tree for restore job with ID 351. First subdirectory has all files from the first full backup job with ID 334. 
It starts at LSN 0 and goes until LSN 10129154.

Next line is the first incremental job with ID 335, starting at LSN 10129154 until 10142295. The third line is the 2nd incremental job with ID 338.

To further prepare the restored files, use the \command{xtrabackup --prepare} command. 
Read \url{https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/incremental_backups.html} for more information.


\subsubsection{Backup of MySQL Databases using the Python MySQL plugin}
\index[general]{Plugin!MySQL Backup}
\label{backup-mysql-python}

The Pyhton plugin from \url{https://github.com/bareos/bareos-contrib/tree/master/fd-plugins/mysql-python} makes a backup of all or selected 
MySQL databases from the \bareosFd or any other MySQL server. It makes use of the mysqldump command and basically grabs data from mysqldump via pipe.
This plugin is suitable to backup database dumps. 
If you prefer to use mechanisms like incremental hot-backups of InnoDB tables, please use the Bareos MySQL / MariaDB Percona xtrabackup Plugin (see \nameref{backup-mysql-xtrabackup}).

Following settings must be done on the Bareos client (\bareosFd):
\begin{itemize}
  \item install and enable the \bareosFd Python plugin
  \item install the Python MySQL plugin (for some platforms it is available prepackaged from \contribDownloadBareosOrg, on the other platforms: copy the plugin files to the Bareos Plugin Directory)
  \item disable bacula compatibility (default for Bareos $>=$ 15.2)
\end{itemize}

\begin{bareosConfigResource}{bareos-fd}{client}{mysql-fd}
Client {
  Name = mysql-fd
  ...
  Plugin Directory = /usr/lib64/bareos/plugins
  Plugin Name = "python"
  compatible = no
}
\end{bareosConfigResource}

Configure the plugin in the \bareosDir:

\begin{bareosConfigResource}{bareos-dir}{fileset}{mysql}
FileSet {
    Name = "mysql"
    Include {
      Options {
        signature = MD5
        compression = lz4
      }
      Plugin = "python:module_path=/usr/lib64/bareos/plugins:module_name=bareos-fd-mysql:db=test,wikidb"
      #Plugin = "python:module_path=/usr/lib64/bareos/plugins:module_name=bareos-fd-mysql:mysqlhost=dbhost:mysqluser=bareos:mysqlpassword=bareos"
    }
}
\end{bareosConfigResource}

In the above example the plugin creates and saves a dump from the databases called \argument{test} and \argument{wikidb},
running on the file-daemon. The commented example below
specifies an explicit MySQL server called \parameter{dbhost}, and connects with user \argument{bareos}, password \argument{bareos}, to create and save a backup of all databases.

The plugin creates a pipe internally, thus no extra space on disk is needed. You will find one file per database in the backups in the virtual directory \path|/_mysqlbackups_|.

List of supported options:
\begin{description}
 \item[db] comma separated list of databases to save, where each database will be stored in a separate file. If ommited, all databases will be saved.
 \item[dumpbinary] command (with or without full path) to create the dumps. Default: \argument{mysqldump}
 \item[dumpoptions] options for dumpbinary, default: \bquote{\argument{--events --single-transaction}}
 \item[drop\_and\_recreate] if not set to \argument{false}, adds \argument{--add-drop-database --databases} to dumpoptions
 \item[mysqlhost] MySQL host to connect to, default: \argument{localhost}
 \item[mysqluser] MySQL user. Default: unset, the user running the file-daemon will be used (usually root)
 \item[mysqlpassword] MySQL password. Default: unset (better use \file{my.cnf} to store passwords)
\end{description}

On restore, the database dumps are restored to the subdirectory \path|_mysqlbackups_| in the restore path. The database restore must be triggered manually (\command{mysql < _mysqlbackups_/DATABASENAME.sql}).


\subsubsection{Backup of a MySQL Database by using the RunScript directive}
\index[general]{RunScript!Example}

One method to backup a MySQL database is to use the \command{mysqldump} tool to dump the database
into a file and then backup it as a normal file. After the backup, the file can be
removed. It may also be an option not to remove it, so that the latest version is
always available immediately. On the next job run it will be overwritten anyway.

This can be done by using \linkResourceDirective{Dir}{Job}{Run Script} directives, for example:
\begin{bconfig}{RunScript job resource for a MySQL backup}
Job {
  Name = "BackupDatabase"
  JobDefs = "DefaultJob"
  Client = dbserver-fd
  Level = Full
  FileSet="Database"

  # This creates a dump of our database in the local filesystem on the Client
  RunScript {
    FailJobOnError = Yes
    RunsOnClient = Yes
    RunsWhen = Before
    Command = "sh -c 'mysqldump --user=<username> --password=<password> --opt --all-databases > /var/lib/bareos/mysql_dump.sql'"
  }

  # This deletes the dump in the local filesystem on the Client
  RunScript {
    RunsOnSuccess = Yes
    RunsOnClient = Yes
    RunsWhen = After
    Command = "rm /var/lib/bareos/mysql_dump.sql"
  }
}

FileSet {
  Name = "Database"
  Include {
    Options {
      signature = MD5
      compression = gzip
    }
  # database dump file
  File = "/var/lib/bareos/mysql_dump.sql" 
  }
}
\end{bconfig}

Note that redirecting the \command{mysqldump} output to a file requires
to run the whole command line through a shell, otherwise the \command{mysqldump}
would not know what do with the \command{>} character and the job would fail.
As no shell features like redirection or piping are used for the \command{rm},
the \command{sh -c} is not needed there.
See \linkResourceDirective{Dir}{Job}{Run Script} for more details.


\subsubsection{Backup of a MySQL Database by using the bpipe plugin}
\index[general]{bpipe!MySQL backup}

Instead of creating a temporary database dump file,
the bpipe plugin can be used. 
For general information about bpipe, see the \nameref{bpipe} section. 
The bpipe plugin is configured inside the Include section of a File Set, e.g.:
\begin{bconfig}{bpipe fileset for MySQL backup}
FileSet {
  Name = "mysql-all"
  Include {
    Plugin = "bpipe:file=/MYSQL/dump.sql:reader=mysqldump --user=<user> --password=<password> --opt --all-databases:writer=mysql --user=<user> --password=<password>"
    Options {
      signature = MD5
      compression = gzip
    }
  }
}
\end{bconfig}

This can also be used, to backup a database that is running on a remote host:
\begin{bconfig}{bpipe directive to backup a MySQL database that is running on a remote host}
FileSet{
  Name = "mysql-all"
  Include {
    Plugin = "bpipe:file=/MYSQL/dump.sql:reader=mysqldump --host=<hostname> --user=<user> --password=<password> --opt --all-databases:writer=mysql --host=<hostname> --user=<user> --password=<password>"
    Options {
      signature = MD5
      compression = gzip
    }
  }
}
\end{bconfig}

If you do not want a direct restore of your data in your plugin directive, as shown in the examples above, 
there is the possibility to restore the dump to the filesystem first, which offers you more control over the restore process, e.g.:
\begin{bconfig}{bpipe directive to backup a MySQL database and restore the dump to the filesystem first}
FileSet{
  Name = "mysql-all"
  Include {
    Plugin = "bpipe:file=/MYSQL/dump.sql:reader=mysqldump --host=<hostname> --user=<user> --password=<password> --opt --all-databases:writer=/usr/lib/bareos/scripts/bpipe-restore.sh"
    Options {
      signature = MD5
      compression = gzip
    }
  }
}
\end{bconfig}

A very simple corresponding shell script (\command{bpipe-restore.sh}) to the method above might look like the following one:
\begin{bconfig}{bpipe shell script for a restore to filesystem}
#!/bin/bash
cat - > /tmp/dump.sql
exit 0
\end{bconfig}