%%
%%

\chapter{Katalog Verwaltung}
\label{CatMaintenanceChapter}
\index[general]{Verwaltung!Katalog }
\index[general]{Katalog Verwaltung}

Ohne eine ordnungsgem\"{a}{\ss}e Einrichtung und Verwaltung kann es sein,
dass Ihr Katalog immer gr\"{o}{\ss}er wird wenn Jobs laufen und Daten gesichert werden.
Zudem kann der Katalog ineffizient und langsam werden. Wie schnell der Katalog w\"{a}chst,
h\"{a}ngt von der Anzahl der Jobs und der Menge der dabei gesicherten Dateien ab.
Durch das L\"{o}schen von Eintr\"{a}gen im Katalog kann Platz geschaffen werden f\"{u}r
neue Eintr\"{a}ge der folgenden Jobs. Durch regelm\"{a}{\ss}iges l\"{o}schen alter abgelaufener
Daten (\"{a}lter als durch die Aufbewahrungszeitr\"{a}ume (Retention Periods) angegeben),
wird daf\"{u}r gesorgt, dass die Katalog-Datenbank eine konstante Gr\"{o}{\ss}e beibeh\"{a}lt.

Sie k\"{o}nnen mit der vorgegebenen Konfiguration beginnen, sie enth\"{a}lt bereits
sinnvolle Vorgaben f\"{u}r eine kleine Anzahl von Clients (kleiner 5), in diesem Fall
wird die Katalogwartung, wenn Sie einige hundert Megabytes freien Plattenplatz haben,
nicht dringlich sein. Was aber auch immer der Fall ist, einiges Wissen \"{u}ber
die Retention Periods/Aufbewahrungszeitr\"{a}ume der Daten im Katalog und auf den Volumes ist hilfreich.

\section{Einstellung der Aufbewahrungszeitr\"{a}ume}
\label{Retention}
\index[general]{Einstellung der Aufbewahrungszeitr\"{a}ume }
\index[general]{Zeitr\"{a}ume!Einstellung der Aufbewahrungs- }

Bacula benutzt drei verschiedene Aufbewahrungszeitr\"{a}ume:
die {\bf File Retention}: der Aufbewahrungszeitraum f\"{u}r Dateien,
die {\bf Job Retention}: der Aufbewahrungszeitraum f\"{u}r Jobs und 
die {\bf Volume Retention}: der Aufbewahrungszeitraum f\"{u}r Volumes.
Von diesen drei ist der Aufbewahrungszeitraum f\"{u}r Dateien der entscheidende,
wenn es darum geht, wie gro{\ss} die Datenbank werden wird.

Die {\bf File Retention} und die {\bf Job Retention} werden in der Client-Konfiguration,
wie unten gezeigt, angegeben. Die {\bf Volume Retention} wird in der Pool-Konfiguration
angegeben, genauere Informationen dazu finden Sie im n\"{a}chsten Kapitel dieses Handbuchs.

\begin{description}

\item [File Retention = \lt{}time-period-specification\gt{}]
   \index[dir]{File Retention  }
   Der Aufbewahrungszeitraum f\"{u}r Dateien gibt die Zeitspanne an, die die
Datei-Eintr\"{a}ge in der Katalog-Datenbank aufbewahrt werden.
Wenn {\bf AutoPrune} in der Client-Konfiguration auf {\bf yes} gesetzt ist,
wird Bacula die Katalog-Eintr\"{a}ge der Dateien l\"{o}schen, die \"{a}lter als
dieser Zeitraum sind. Das L\"{o}schen erfolgt nach Beendigung eines Jobs des entsprechenden Clients.
Bitte beachten Sie, dass die Client-Datenbank-Eintr\"{a}ge eine Kopie der Aufbewahrungszeitr\"{a}ume
f\"{u}r Dateien und Jobs enthalten, Bacula aber die Zeitr\"{a}ume aus der aktuellen Client-Konfiguration
des Director-Dienstes benutzt um alte Katalog-Eintr\"{a}ge zu l\"{o}schen.

Da die Datei-Eintr\"{a}ge ca. 80 Prozent der Katalog-Datenbankgr\"{o}{\ss}e ausmachen,
sollten Sie sorgf\"{a}lltig ermitteln \"{u}ber welchen Zeitraum Sie die Eintr\"{a}ge aufbewahren wollen.
Nachdem die Datei-Eintr\"{a}ge  ge\"{o}scht wurden, ist es nicht mehr m\"{o}glich einzelne dieser Dateien
mit einem R\"{u}cksicherungs-Job wiederherzustellen, aber die Bacula-Versionen 1.37 und sp\"{a}ter
sind in der Lage, aufgrund des Job-Eintrags im Katalog, alle Dateien des Jobs zur\"{u}ckzusichern
solange der Job-Eintrag im Katalog vorhanden ist.

Aufbewahrungszeitr\"{a}ume werden in Sekunden angegeben, aber der Einfachheit halber sind auch
eine Nummer von Hilfsangaben vorhanden, so dass man Minuten, Stunden, Tage, Wochen,
Monate, Quartale und Jahre konfigurieren kann. Lesen Sie bitte das \ilink{Konfigurations-Kapitel}{Time}
dieses Handbuchs um mehr \"{u}ber diese Hilfsangaben zu erfahren.

Der Standardwert der Aufbewahrungszeit f\"{u}r Dateien ist 60 Tage.

\item [Job Retention = \lt{}time-period-specification\gt{}]
   \index[dir]{Job Retention  }
   Der Aufbewahrungszeitraum f\"{u}r Jobs gibt die Zeitspanne an, die die
Job-Eintr\"{a}ge in der Katalog-Datenbank aufbewahrt werden.
Wenn {\bf AutoPrune} in der Client-Konfiguration auf {\bf yes} gesetzt ist,
wird Bacula die Katalog-Eintr\"{a}ge der Jobs l\"{o}schen, die \"{a}lter als
dieser Zeitraum sind. Beachten Sie, dass wenn ein Job-Eintrag ge\"{o}scht wird,
auch alle zu diesem Job geh\"{o}renden Datei- und JobMedia-Eintr\"{a}ge aus dem
Katalog gel\"{o}scht werden. Dies passiert unabh\"{a}ngig von der Aufbewahrungszeit f\"{u}r Dateien,
infolge dessen wird die Aufbewahrungszeit f\"{u}r Dateien normalerweise k\"{u}rzer sein als f\"{u}r Jobs.

Wie oben erw\"{a}hnt, sind Sie nicht mehr in der Lage einzelne Dateien eines Jobs zur\"{u}ckzusichern,
wenn die Datei-Eintr\"{a}ge aus der Katalog-Datenbank entfernt wurden. Jedoch, solange der Job-Eintrag
im Katalog vorhanden ist, k\"{o}nnen Sie immer noch den kompletten Job mit allen Dateien wiederherstellen
(mit Bacula-Version 1.37 und gr\"{o}{\ss}er). Daher ist es eine gute Idee, die Job-Eintr\"{a}ge im Katalog
l\"{a}nger als die Datei-Eintr\"{a}ge aufzubewahren.

Aufbewahrungszeitr\"{a}ume werden in Sekunden angegeben, aber der Einfachheit halber sind auch
eine Nummer von Hilfsangaben vorhanden, so dass man Minuten, Stunden, Tage, Wochen,
Monate, Quartale und Jahre konfigurieren kann. Lesen Sie bitte das \ilink{Konfigurations-Kapitel}{Time}
dieses Handbuchs um mehr \"{u}ber diese Hilfsangaben zu erfahren.

Der Standardwert der Aufbewahrungszeit f\"{u}r Jobs ist 180 Tage.

\item [AutoPrune = \lt{}yes/no\gt{}]
   \index[dir]{AutoPrune  }
   Wenn AutoPrune auf  {\bf yes} (Standard) gesetzt ist, wird Bacula nach jedem Job
automatisch \"{u}berpr\"{u}fen, ob die Aufbewahrungszeit f\"{u}r bestimmte Dateien und/oder Jobs
des gerade gesicherten Clients abgelaufen ist und diese aus dem Katalog entfernen.
Falls Sie AutoPrune durch das Setzen auf {\bf no} ausschalten, wird Ihre Katalog-Datenbank mit jedem
gelaufenen Job immer gr\"{o}{\ss}er werden.
\end{description}

\label{CompactingMySQL}
\section{Komprimieren Ihrer MySQL Datenbank}
\index[general]{Datenbank!Komprimieren Ihrer MySQL }
\index[general]{Komprimieren Ihrer MySQL Datenbank }

Mit der Zeit, wie oben schon angemerkt, wird Ihre Datenbank dazu neigen zu wachsen.
Auch wenn Bacula regelm\"{a}{\ss}ig Datei-Eintr\"{a}ge l\"{o}scht, wird die {\bf MySQL}-Datenbank
st\"{a}ndig gr\"{o}{\ss}er werden. Um dies zu vermeiden, muss die Datenbank komprimiert werden.
Normalerweise kennen gro{\ss}e kommerzielle Datenbanken, wie Oracle, bestimmte Kommandos
um den verschwendeten Festplattenplatz wieder freizugeben. MySQL hat das {\bf OPTIMIZE TABLE}
Kommando und bei SQLite (Version 2.8.4 und gr\"{o}{\ss}er) k\"{o}nnen Sie das {\bf VACUUM}
Kommando zu diesem Zweck benutzen. Wir \"{u}berlassen es Ihnen, die N\"{u}tzlichkeit von
{\bf OPTIMIZE TABLE} oder {\bf VACUUM} zu ermitteln.

Alle Datenbanken haben Hilfsmittel, um die enthaltenen Daten im ASCII-Format in eine Datei zu schreiben
und diese Datei dann auch wieder einzulesen. Wenn man das tut, wird die Datenbank erneut erzeugt, was ein
sehr kompaktes Datenbank-Format als Ergebnis hat. Weiter unten zeigen wir Ihnen, wie Sie das bei
MySQL, SQLite und PostgreSQL durchf\"{u}hren k\"{o}nnen.

Bei einer {\bf MySQL} Datenbank k\"{o}nnen Sie den Inhalt der Katalog-Datenbank mit den folgenden Kommandos
in eine ASCII-Datei (bacula.sql) schreiben und neu in die Datenbank importieren:

\footnotesize
\begin{verbatim}
mysqldump -f --opt bacula > bacula.sql
mysql bacula < bacula.sql
rm -f bacula.sql
\end{verbatim}
\normalsize

Abh\"{a}gig von der Gr\"{o}{\ss}e Ihrer Datenbank, wird dies mehr oder weniger Zeit und auch Festplattenplatz
ben\"{o}tigen. Zum Beispiel, wenn ich in das Verzeichnis wechsle, wo meine MySQL-Datenabnk liegt (typischerweise
/var/lib/mysql) und diese Kommando ausf\"{u}hre:

\footnotesize
\begin{verbatim}
du bacula
\end{verbatim}
\normalsize

bekomme ich die Ausgabe {\bf 620,644}, was bedeutet dass das Verzeichnis bacula 620.644 Bl\"{o}cke
von 1024 Bytes auf der Festplatte belegt, meine Datenbank enth\"{a}lt also ca. 635 MB an Daten.
Nachdem ich das {\bf mysqldump} ausgef\"{u}hrt habe, ist die dabei entstandene Datei bacula.sql
{\bf 174.356} Bl\"{o}cke gro{\ss}, wenn diese Datei wieder mit dem Kommando {\bf mysql bacula < bacula.sql}
in die Datenbank importiert wird, ergibt sich eine Datenbankgr\"{o}{\ss}e von nur noch {\bf 210.464}
Bl\"{o}cken. Mit anderen Worten, die komprimierte Version meiner Datenbank, die seit ca. 1 Jahr
in Benutzung ist, ist ungef\"{a}hr nur noch ein Drittel so gro{\ss} wie vorher.

Als Konsequenz wird empfohlen, auf die Gr\"{o}{\ss}e der Datenbank zu achten und sie von Zeit zu Zeit
(alle sechs Monate oder j\"{a}hrlich) zu komprimieren.

\label{DatabaseRepair}
\label{RepairingMySQL}
\section{Reparatur Ihrer MySQL Datenbank}
\index[general]{Datenbank!Reparatur Ihrer MySQL }
\index[general]{Reparatur Ihrer MySQL Datenbank }

Wenn Sie bemerken, dass das Schreiben der MySQL-Datenbank zu Fehlern f\"{u}hrt,
oder das der Director-Dienst h\"{a}ngt, wenn er auf die Datenbank zugreift,
sollten Sie sich die MySQL Datenbank\"{u}berpr\"{u}fungs- und Reparaturprogramme ansehen.
Welches Programm Sie laufen lassen sollten, h\"{a}ngt von der von Ihnen benutzten Datenbank-
Indizierung zusammen. Wenn Sie das Standardverfahren nutzen, werden Sie vermutlich {\bf myisamchk}
laufen lassen. F\"{a}r n\"{a}here Information lesen Sie bitte auch:
\elink{http://dev.mysql.com/doc/refman/5.1/de/client-utility-programs.html}
{http://dev.mysql.com/doc/refman/5.1/de/client-utility-programs.html}. 

Falls die auftretenden Fehler einfache SQL-Warnungen sind, sollten Sie zuerst das von Bacula mitgelieferte
dbcheck-Programm ausf\"{u}hren, bevor Sie die MySQL-Datenbank-Reparaturprogramme nutzen.
Dieses Programm kann verwaiste Datenbankeintr\"{a}ge finden und andere Inkonsistenzen in der
Katalog-Datenbank beheben.

Eine typische Ursache von Datenbankproblemen ist das Volllaufen einer Partition.
In solch einem Fall muss entweder zus\"{a}tzlicher Platz geschaffen werden, oder
belegter Platz freigegeben werden, bevor die Datenbank mit {\bf myisamchk} repariert werden kann.

Recently my root partition filled and the MySQL database was corrupted.
Simply running {\bf myisamchk -r} did not fix the problem. However,
the following script did the trick for me:

\footnotesize
\begin{verbatim}
#!/bin/sh
for i in *.MYD ; do
  mv $i x${i}
  t=`echo $i | cut -f 1 -d '.' -`
  mysql bacula <<END_OF_DATA
set autocommit=1;
truncate table $t;
quit
END_OF_DATA
  cp x${i} ${i}
  chown mysql:mysql ${i}
  myisamchk -r ${t}
done
\end{verbatim}
\normalsize

I invoked it with the following commands:

\footnotesize
\begin{verbatim}
cd /var/lib/mysql/bacula
./repair
\end{verbatim}
\normalsize

Then after ensuring that the database was correctly fixed, I did:
\footnotesize
\begin{verbatim}
cd /var/lib/mysql/bacula
rm -f x*.MYD
\end{verbatim}
\normalsize

\section{MySQL Table is Full}
\index[general]{Database!MySQL Table is Full}
\index[general]{MySQL Table is Full}

If you are running into the error {\bf The table 'File' is full ...}, 
it is probably because on version 4.x MySQL, the table is limited by   
default to a maximum size of 4 GB and you have probably run into
the limit. The solution can be found at:
\elink{http://dev.mysql.com/doc/refman/5.0/en/full-table.html}
{http://dev.mysql.com/doc/refman/5.0/en/full-table.html}

You can display the maximum length of your table with:

\footnotesize
\begin{verbatim}
mysql bacula
SHOW TABLE STATUS FROM bacula like "File";
\end{verbatim}
\normalsize

If the column labeled "Max\_data\_length" is around 4Gb, this is likely
to be the source of your problem, and you can modify it with:

\footnotesize
\begin{verbatim}
mysql bacula
ALTER TABLE File MAX_ROWS=281474976710656;
\end{verbatim}
\normalsize

Alternatively you can modify your /etc/my.conf file before creating the
Bacula tables, and in the [mysqld] section set:

\footnotesize                                 
\begin{verbatim}
set-variable = myisam_data_pointer_size=6
\end{verbatim}
\normalsize

The above myisam data pointer size must be made before you create your
Bacula tables or it will have no effect.

The row and pointer size changes should already be the default on MySQL
version 5.x, so making these changes should only be necessary on MySQL 4.x
depending on the size of your catalog database.

\section{MySQL Server Has Gone Away}
\index[general]{Database!MySQL Server Has Gone Away}
\index[general]{MySQL Server Has Gone Away}
If you are having problems with the MySQL server disconnecting or with
messages saying that your MySQL server has gone away, then please read
the MySQL documentation, which can be found at:

\elink{http://dev.mysql.com/doc/refman/5.0/en/gone-away.html}
{http://dev.mysql.com/doc/refman/5.0/en/gone-away.html}


\label{RepairingPSQL}
\section{Repairing Your PostgreSQL Database}
\index[general]{Database!Repairing Your PostgreSQL }
\index[general]{Repairing Your PostgreSQL Database }

The same considerations apply that are indicated above for MySQL. That is,
consult the PostgreSQL documents for how to repair the database, and also
consider using Bacula's dbcheck program if the conditions are reasonable for
using (see above). 

\label{DatabasePerformance}
\section{Database Performance Issues}
\index[general]{Database Performance Issues}
\index[general]{Performance!Database}

There are a considerable number of ways each of the databases can be
tuned to improve the performance. Going from an untuned database to one
that is properly tuned can make a difference of a factor of 100 or more
in the time to insert or search for records.

For each of the databases, you may get significant improvements by adding
additional indexes. The comments in the Bacula make\_xxx\_tables give some
indications as to what indexes may be appropriate.  Please see below
for specific instructions on checking indexes.

For MySQL, what is very important is to use the examine the    
my.cnf file (usually in /etc/my.cnf).
You may obtain significant performances by switching to
the my-large.cnf or my-huge.cnf files that come with the MySQL source
code.

For SQLite3, one significant factor in improving the performance is
to ensure that there is a "PRAGMA synchronous = NORMAL;" statement.
This reduces the number of times that the database flushes the in memory
cache to disk. There are other settings for this PRAGMA that can 
give even further performance improvements at the risk of a database
corruption if your system crashes.

For PostgreSQL, you might want to consider turning fsync off.  Of course
doing so can cause corrupted databases in the event of a machine crash.
There are many different ways that you can tune PostgreSQL, the
following document discusses a few of them:
\elink{
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}
{http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}.

There is also a PostgreSQL FAQ question number 3.3 that may
answer some of your questions about how to improve performance
of the PostgreSQL engine:
\elink{
http://www.postgresql.org/docs/faqs.FAQ.html\#3.3}
{http://www.postgresql.org/docs/faqs.FAQ.html\#3.3}.
% TODO: verify above is correct. is this okay for book?

Also for PostgreSQL, look at what "effective\_cache\_size". For a 2GB memory 
machine, you probably want to set it at 131072, but don't set it too high.
In addition, for a 2GB system, work\_mem = 256000 and
maintenance\_work\_mem = 256000 seem to be reasonable values.  Make
sure your checkpoint\_segments is set to at least 8.



\section{Performance Issues Indexes}
\index[general]{Database Performance Issues Indexes}
\index[general]{Performance!Database}
One of the most important considerations for improving performance on
the Bacula database is to ensure that it has all the appropriate indexes.
Several users have reported finding that their database did not have
all the indexes in the default configuration.  In addition, you may
find that because of your own usage patterns, you need additional indexes.

The most important indexes for performance are the three indexes on the
{\bf File} table.  The first index is on {\bf FileId} and is automatically
made because it is the unique key used to access the table.  The other
two are the JobId index and the (Filename, PathId) index.  If these Indexes
are not present, your performance may suffer a lot.

\subsection{PostgreSQL Indexes}
On PostgreSQL, you can check to see if you have the proper indexes using
the following commands:

\footnotesize
\begin{verbatim}
psql bacula
select * from pg_indexes where tablename='file';
\end{verbatim}
\normalsize

If you do not see output that indicates that all three indexes
are created, you can create the two additional indexes using:

\footnotesize
\begin{verbatim}
psql bacula
CREATE INDEX file_jobid_idx on file (jobid);
CREATE INDEX file_fp_idx on file (filenameid, pathid);
\end{verbatim}
\normalsize

\subsection{MySQL Indexes}
On MySQL, you can check if you have the proper indexes by:

\footnotesize
\begin{verbatim}
mysql bacula
show index from File;
\end{verbatim}
\normalsize

If the indexes are not present, especially the JobId index, you can
create them with the following commands:

\footnotesize
\begin{verbatim}
mysql bacula
CREATE INDEX file_jobid_idx on File (JobId);
CREATE INDEX file_jpf_idx on File (Job, FilenameId, PathId);
\end{verbatim}
\normalsize

Though normally not a problem, you should ensure that the indexes 
defined for Filename and Path are both set to 255 characters. Some users 
reported performance problems when their indexes were set to 50 characters.
To check, do:

\footnotesize
\begin{verbatim}
mysql bacula
show index from Filename;
show index from Path;
\end{verbatim}
\normalsize

and what is important is that for Filename, you have an index with
Key\_name "Name" and Sub\_part "255". For Pth, you should have a Key\_name
"Path" and Sub\_part "255".  If one or the other does not exist or the
Sub\_part is less that 255, you can drop and recreate the appropriate
index with:

\footnotesize
\begin{verbatim}
mysql bacula
DROP INDEX Path on Path;
CREATE INDEX Path on Path (Path(255);

DROP INDEX Name on Filename;
CREATE INDEX Name on Filename (Name(255));
\end{verbatim}
\normalsize


\subsection{SQLite Indexes}
On SQLite, you can check if you have the proper indexes by:

\footnotesize
\begin{verbatim}
sqlite <path>bacula.db
select * from sqlite_master where type='index' and tbl_name='File';
\end{verbatim}
\normalsize

If the indexes are not present, especially the JobId index, you can
create them with the following commands:

\footnotesize
\begin{verbatim}
mysql bacula
CREATE INDEX file_jobid_idx on File (JobId);
CREATE INDEX file_jfp_idx on File (Job, FilenameId, PathId);
\end{verbatim}
\normalsize



\label{CompactingPostgres}
\section{Compacting Your PostgreSQL Database}
\index[general]{Database!Compacting Your PostgreSQL }
\index[general]{Compacting Your PostgreSQL Database }

Over time, as noted above, your database will tend to grow. I've noticed that
even though Bacula regularly prunes files, PostgreSQL has a {\bf VACUUM}
command that will compact your database for you. Alternatively you may want to
use the {\bf vacuumdb} command, which can be run from a cron job. 

All database programs have some means of writing the database out in ASCII
format and then reloading it. Doing so will re-create the database from
scratch producing a compacted result, so below, we show you how you can do
this for PostgreSQL. 

For a {\bf PostgreSQL} database, you could write the Bacula database as an
ASCII file (bacula.sql) then reload it by doing the following: 

\footnotesize
\begin{verbatim}
pg_dump -c bacula > bacula.sql
cat bacula.sql | psql bacula
rm -f bacula.sql
\end{verbatim}
\normalsize

Depending on the size of your database, this will take more or less time and a
fair amount of disk space. For example, you can {\bf cd} to the location of
the Bacula database (typically /usr/local/pgsql/data or possible
/var/lib/pgsql/data) and check the size. 

There are certain PostgreSQL users who do not recommend the above 
procedure. They have the following to say:
PostgreSQL does not
need to be dumped/restored to keep the database efficient.  A normal
process of vacuuming will prevent the database from every getting too
large.  If you want to fine-tweak the database storage, commands such
as VACUUM FULL, REINDEX, and CLUSTER exist specifically to keep you
from having to do a dump/restore.

Finally, you might want to look at the PostgreSQL documentation on
this subject at
\elink{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}
{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}.  

\section{Compacting Your SQLite Database}
\index[general]{Compacting Your SQLite Database }
\index[general]{Database!Compacting Your SQLite }

First please read the previous section that explains why it is necessary to
compress a database. SQLite version 2.8.4 and greater have the {\bf Vacuum}
command for compacting the database. 

\footnotesize
\begin{verbatim}
cd {\bf working-directory}
echo 'vacuum;' | sqlite bacula.db
\end{verbatim}
\normalsize

As an alternative, you can use the following commands, adapted to your system:


\footnotesize
\begin{verbatim}
cd {\bf working-directory}
echo '.dump' | sqlite bacula.db > bacula.sql
rm -f bacula.db
sqlite bacula.db < bacula.sql
rm -f bacula.sql
\end{verbatim}
\normalsize

Where {\bf working-directory} is the directory that you specified in the
Director's configuration file. Note, in the case of SQLite, it is necessary to
completely delete (rm) the old database before creating a new compressed
version. 

\section{Migrating from SQLite to MySQL}
\index[general]{MySQL!Migrating from SQLite to }
\index[general]{Migrating from SQLite to MySQL }

You may begin using Bacula with SQLite then later find that you want to switch
to MySQL for any of a number of reasons: SQLite tends to use more disk than
MySQL; when the database is corrupted it is often more catastrophic than
with MySQL or PostgreSQL.
Several users have succeeded in converting from SQLite to MySQL by
exporting the MySQL data and then processing it with Perl scripts
prior to putting it into MySQL. This is, however, not a simple
process.

\label{BackingUpBacula}
\section{Backing Up Your Bacula Database}
\index[general]{Backing Up Your Bacula Database }
\index[general]{Database!Backing Up Your Bacula }

If ever the machine on which your Bacula database crashes, and you need to
restore from backup tapes, one of your first priorities will probably be to
recover the database. Although Bacula will happily backup your catalog
database if it is specified in the FileSet, this is not a very good way to do
it, because the database will be saved while Bacula is modifying it. Thus the
database may be in an instable state. Worse yet, you will backup the database
before all the Bacula updates have been applied. 

To resolve these problems, you need to backup the database after all the backup
jobs have been run. In addition, you will want to make a copy while Bacula is
not modifying it. To do so, you can use two scripts provided in the release
{\bf make\_catalog\_backup} and {\bf delete\_catalog\_backup}. These files
will be automatically generated along with all the other Bacula scripts. The
first script will make an ASCII copy of your Bacula database into {\bf
bacula.sql} in the working directory you specified in your configuration, and
the second will delete the {\bf bacula.sql} file. 

The basic sequence of events to make this work correctly is as follows: 

\begin{itemize}
\item Run all your nightly backups  
\item After running your nightly backups, run a Catalog backup Job  
\item The Catalog backup job must be scheduled after your last nightly backup 

\item You use {\bf RunBeforeJob} to create the ASCII  backup file and {\bf
   RunAfterJob} to clean up 
\end{itemize}

Assuming that you start all your nightly backup jobs at 1:05 am (and that they
run one after another), you can do the catalog backup with the following
additional Director configuration statements: 

\footnotesize
\begin{verbatim}
# Backup the catalog database (after the nightly save)
Job {
  Name = "BackupCatalog"
  Type = Backup
  Client=rufus-fd
  FileSet="Catalog"
  Schedule = "WeeklyCycleAfterBackup"
  Storage = DLTDrive
  Messages = Standard
  Pool = Default
  RunBeforeJob = "/home/kern/bacula/bin/make_catalog_backup"
  RunAfterJob  = "/home/kern/bacula/bin/delete_catalog_backup"
  Write Bootstrap = "/home/kern/bacula/working/BackupCatalog.bsr"
}
# This schedule does the catalog. It starts after the WeeklyCycle
Schedule {
  Name = "WeeklyCycleAfterBackup
  Run = Level=Full sun-sat at 1:10
}
# This is the backup of the catalog
FileSet {
  Name = "Catalog"
  Include {
    Options {
      signature=MD5
    }
    File = \lt{}working_directory\gt{}/bacula.sql
  }
}
\end{verbatim}
\normalsize

Be sure to write a bootstrap file as in the above example. However, it is preferable
to write or copy the bootstrap file to another computer. It will allow
you to quickly recover the database backup should that be necessary.  If
you do not have a bootstrap file, it is still possible to recover your
database backup, but it will be more work and take longer. 

\label{BackingUPOtherDBs}
\section{Backing Up Third Party Databases}
\index[general]{Backing Up Third Party Databases }
\index[general]{Databases!Backing Up Third Party }

If you are running a database in production mode on your machine, Bacula will
happily backup the files, but if the database is in use while Bacula 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. I am not a database expert, so I cannot
provide you advice on how to do this, but if you are unsure about how to
backup your database, you might try visiting the Backup Central site, which
has been renamed Storage Mountain (www.backupcentral.com). In particular,
their 
\elink{ Free Backup and Recovery
Software}{http://www.backupcentral.com/toc-free-backup-software.html} page has
links to scripts that show you how to shutdown and backup most major
databases. 
\label{Size}

\section{Database Size}
\index[general]{Size!Database }
\index[general]{Database Size }

As mentioned above, if you do not do automatic pruning, your Catalog will grow
each time you run a Job. Normally, you should decide how long you want File
records to be maintained in the Catalog and set the {\bf File Retention}
period to that time. Then you can either wait and see how big your Catalog
gets or make a calculation assuming approximately 154 bytes for each File
saved and knowing the number of Files that are saved during each backup and
the number of Clients you backup. 

For example, suppose you do a backup of two systems, each with 100,000 files.
Suppose further that you do a Full backup weekly and an Incremental every day,
and that the Incremental backup typically saves 4,000 files. The size of your
database after a month can roughly be calculated as: 

\footnotesize
\begin{verbatim}
   Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26)
\end{verbatim}
\normalsize

where we have assumed four weeks in a month and 26 incremental backups per month.
This would give the following: 

\footnotesize
\begin{verbatim}
   Size = 154 * 2 * (100,000 * 4 + 10,000 * 26)
or
   Size = 308 * (400,000 + 260,000)
or
   Size = 203,280,000 bytes
\end{verbatim}
\normalsize

So for the above two systems, we should expect to have a database size of
approximately 200 Megabytes. Of course, this will vary according to how many
files are actually backed up. 

Below are some statistics for a MySQL database containing Job records for five
Clients beginning September 2001 through May 2002 (8.5 months) and File
records for the last 80 days. (Older File records have been pruned). For these
systems, only the user files and system files that change are backed up. The
core part of the system is assumed to be easily reloaded from the Red Hat rpms.


In the list below, the files (corresponding to Bacula Tables) with the
extension .MYD contain the data records whereas files with the extension .MYI
contain indexes. 

You will note that the File records (containing the file attributes) make up
the large bulk of the number of records as well as the space used (459 Mega
Bytes including the indexes). As a consequence, the most important Retention
period will be the {\bf File Retention} period. A quick calculation shows that
for each File that is saved, the database grows by approximately 150 bytes. 

\footnotesize
\begin{verbatim}
      Size in
       Bytes   Records    File
 ============  =========  ===========
          168          5  Client.MYD
        3,072             Client.MYI
  344,394,684  3,080,191  File.MYD
  115,280,896             File.MYI
    2,590,316    106,902  Filename.MYD
    3,026,944             Filename.MYI
          184          4  FileSet.MYD
        2,048             FileSet.MYI
       49,062      1,326  JobMedia.MYD
       30,720             JobMedia.MYI
      141,752      1,378  Job.MYD
       13,312             Job.MYI
        1,004         11  Media.MYD
        3,072             Media.MYI
    1,299,512     22,233  Path.MYD
      581,632             Path.MYI
           36          1  Pool.MYD
        3,072             Pool.MYI
            5          1  Version.MYD
        1,024             Version.MYI
\end{verbatim}
\normalsize

This database has a total size of approximately 450 Megabytes. 

If we were using SQLite, the determination of the total database size would be
much easier since it is a single file, but we would have less insight to the
size of the individual tables as we have in this case. 

Note, SQLite databases may be as much as 50\% larger than MySQL databases due
to the fact that all data is stored as ASCII strings. That is even binary
integers are stored as ASCII strings, and this seems to increase the space
needed. 
