\index[general]{Database!Compacting Your MySQL }
\index[general]{Compacting Your MySQL Database }
-Over time, as noted above, your database will tend to grow. I've noticed that
-even though Bacula regularly prunes files, {\bf MySQL} does not effectively
-use the space, and instead continues growing. To avoid this, from time to
-time, you must compact your database. Normally, large commercial database such
-as Oracle have commands that will compact a database to reclaim wasted file
-space. MySQL has the {\bf OPTIMIZE TABLE} command that you can use, and SQLite
-version 2.8.4 and greater has the {\bf VACUUM} command. We leave it to you to
-explore the utility of the {\bf OPTIMIZE TABLE} command in MySQL.
+Over time, as noted above, your database will tend to grow. I've noticed
+that even though Bacula regularly prunes files, {\bf MySQL} does not
+effectively use the space, and instead continues growing. To avoid this,
+from time to time, you must compact your database. Normally, large
+commercial database such as Oracle have commands that will compact a
+database to reclaim wasted file space. MySQL has the {\bf OPTIMIZE TABLE}
+command that you can use. We leave it to you to explore the utility of the
+{\bf OPTIMIZE TABLE} command in MySQL.
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 MySQL, PostgreSQL and SQLite.
+this for MySQL and PostgreSQL.
For a {\bf MySQL} database, you could write the Bacula database as an ASCII
file (bacula.sql) then reload it by doing the following:
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
\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}
-sqlite <path>/bacula.db
-CREATE INDEX file_jobid_idx on File (JobId);
-CREATE INDEX file_jfp_idx on File (JobId, PathId, FilenameId);
-\end{verbatim}
-\normalsize
-
-
-
\label{CompactingPostgres}
\section{Compacting Your PostgreSQL Database}
\index[general]{Database!Compacting Your PostgreSQL }
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 or PostgreSQL}
\index[general]{MySQL!Migrating from SQLite to }
\index[general]{Migrating from SQLite to MySQL or PostgreSQL}
-You may begin using Bacula with SQLite then later find that you want to switch
-to MySQL or Postgres 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 by
-exporting the SQLite data and then processing it with Perl scripts prior to
-putting it into MySQL or PostgreSQL. This is, however, not a simple process.
-Scripts are available on bacula source distribution under
-\texttt{examples/database}.
+On some older Bacula you may begun using Bacula with SQLite then later find
+that you want to switch to MySQL or Postgres for any of a number of
+reasons: SQLite is no longer supported by Bacula; 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 by exporting the SQLite data and then processing it with Perl
+scripts prior to putting it into MySQL or PostgreSQL. This is, however, not
+a simple process. Scripts are available on bacula source distribution
+under \texttt{examples/database}.
\label{BackingUpBacula}
\section{Backing Up Your Bacula Database}
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.
\index[general]{Catalog Resource}
The Catalog Resource defines what catalog to use for the current job.
-Currently, Bacula can only handle a single database server (SQLite, MySQL,
+Currently, Bacula can only handle a single database server (MySQL or
PostgreSQL) that is defined when configuring {\bf Bacula}. However, there
may be as many Catalogs (databases) defined as you wish. For example, you
may want each Client to have its own Catalog database, or you may want
backup jobs to use one database and verify or restore jobs to use another
database.
-Since SQLite is compiled in, it always runs on the same machine
-as the Director and the database must be directly accessible (mounted) from
-the Director. However, since both MySQL and PostgreSQL are networked
+Since both MySQL and PostgreSQL are networked
databases, they may reside either on the same machine as the Director
or on a different machine on the network. See below for more details.
\index[dir]{DB Socket}
\index[dir]{Directive!DB Socket}
This is the name of a socket to use on the local host to connect to the
- database. This directive is used only by MySQL and is ignored by SQLite.
+ database. This directive is used only by MySQL.
Normally, if neither {\bf DB Socket} or {\bf DB Address} are specified, MySQL
will use the default socket. If the DB Socket is specified, the
MySQL server must reside on the same machine as the Director.
This is the host address of the database server. Normally, you would specify
this instead of {\bf DB Socket} if the database server is on another machine.
In that case, you will also specify {\bf DB Port}. This directive is used
- only by MySQL and PostgreSQL and is ignored by SQLite if provided.
+ only by MySQL and PostgreSQL.
This directive is optional.
\item [DB Port = \lt{}port\gt{}]
\index[dir]{Directive!DBPort}
This defines the port to be used in conjunction with {\bf DB Address} to
access the database if it is on another machine. This directive is used only
- by MySQL and PostgreSQL and is ignored by SQLite if provided. This
+ by MySQL and PostgreSQL.
directive is optional.
%% \item [Multiple Connections = \lt{}yes\vb{}no\gt{}]
%% same Catalog will use a single connection to the catalog. It will be shared,
%% and Bacula will allow only one Job at a time to communicate. If you set this
%% directive to yes, Bacula will permit multiple connections to the database,
-%% and the database must be multi-thread capable. For SQLite and PostgreSQL,
+%% and the database must be multi-thread capable. For and PostgreSQL,
%% this is no problem. For MySQL, you must be *very* careful to have the
%% multi-thread version of the client library loaded on your system. When this
%% directive is set yes, each Job will have a separate connection to the
%% database, and the database will control the interaction between the
different
%% Jobs. This can significantly speed up the database operations if you are
-%% running multiple simultaneous jobs. In addition, for SQLite and PostgreSQL,
+%% running multiple simultaneous jobs. In addition, for and PostgreSQL,
%% Bacula will automatically enable transactions. This can significantly speed
%% up insertion of attributes in the database either for a single Job or
%% multiple simultaneous Jobs.
\begin{verbatim}
Catalog
{
- Name = SQLite
+ Name = MySQL
dbname = bacula;
user = bacula;
password = "" # no password = no security
tar and bru, because the catalog maintains a record of all Volumes used,
all Jobs run, and all Files saved, permitting efficient restoration and
Volume management. Bacula currently supports three different databases,
- MySQL, PostgreSQL, and SQLite, one of which must be chosen when building
+ MySQL, and PostgreSQL one of which must be chosen when building
Bacula.
- The three SQL databases currently supported (MySQL, PostgreSQL or
- SQLite) provide quite a number of features, including rapid indexing,
+ The two SQL databases currently supported (MySQL, and PostgreSQL)
+ provide quite a number of features, including rapid indexing,
arbitrary queries, and security. Although the Bacula project plans to support other
major SQL databases, the current Bacula implementation interfaces only
- to MySQL, PostgreSQL and SQLite. For the technical and porting details
+ to MySQL, and PostgreSQL. For the technical and porting details
see the Catalog Services Design Document in the developer's documented.
The packages for MySQL and PostgreSQL are available for several operating
document for the details. For more information on PostgreSQL, please
see: \elink{www.postgresql.org}{http://www.postgresql.org}.
- Configuring and building SQLite is even easier. For the details of
- configuring SQLite, please see the \ilink{Installing and Configuring SQLite}{SqlLiteChapter} chapter of this document.
-
\subsection*{Bacula Monitor}
\label{MonDef}
A Bacula Monitor service is the program that allows the
To perform a successful save or restore, the following four daemons must be
configured and running: the Director daemon, the File daemon, the Storage
- daemon, and the Catalog service (MySQL, PostgreSQL or SQLite).
+ daemon, and the Catalog service (MySQL, or PostgreSQL).
\section{Bacula Configuration}
\index[general]{Configuration!Bacula }
In general, you will need the Bacula source release, and if you want to run
a Windows client, you will need the Bacula Windows binary release.
-However, Bacula needs certain third party packages (such as {\bf MySQL},
-{\bf PostgreSQL}, or {\bf SQLite} to build and run
+However, Bacula needs certain third party packages (such as {\bf MySQL} or
+{\bf PostgreSQL} to build and run
properly depending on the
options you specify. Normally, {\bf MySQL} and {\bf PostgreSQL} are
packages that can be installed on your distribution. However, if you do
\hline
\multicolumn{1}{|c| }{\bf 3rd Party Package} & \multicolumn{1}{c| }{\bf depkgs}
& \multicolumn{1}{c| }{\bf depkgs-qt} \\
- \hline {SQLite3 } & \multicolumn{1}{c| }{X } & \multicolumn{1}{c| }{ }\\
\hline {mtx } & \multicolumn{1}{c| }{X } & \multicolumn{1}{c| }{ } \\
\hline {qt4 } & \multicolumn{1}{c| }{ } & \multicolumn{1}{c| }{X } \\
\hline
\footnotesize
\begin{verbatim}
cd bacula/depkgs
-make sqlite
+make qt4
\end{verbatim}
\normalsize
-will configure and build only the SQLite package.
+will configure and build only the QT4 package.
You should build the packages that you will require in {\bf depkgs} a
prior to configuring and building Bacula, since Bacula will need
included in the package prior to building Bacula. Please read the INSTALL
file for more details.
-Even if you do not use SQLite, you might find it worthwhile to build {\bf mtx}
+You might find it worthwhile to build {\bf mtx}
because the {\bf tapeinfo} program that comes with it can often provide you
with valuable information about your SCSI tape drive (e.g. compression,
min/max block sizes, ...). Note, most distros provide {\bf mtx} as part of
can skip this phase provided that you have built the thread safe libraries.
And you have already installed the additional rpms noted above.
- SQLite is not supported on Solaris. This is because it
- frequently fails with bus errors. However SQLite3 may work.
-
\item Detar the Bacula source code preferably into the {\bf bacula} directory
discussed above.
\ilink{Installing and Configuring MySQL Phase II}{mysql_phase2} or
create the Bacula PostgreSQL database and tables
\ilink{Configuring PostgreSQL II}{PostgreSQL_configure} or alternatively if you are using
- SQLite \ilink{Installing and Configuring SQLite Phase II}{phase2}.
\item Start Bacula ({\bf ./bacula start}) Note. the next chapter shows you
how to do this in detail.
\index[general]{What Database to Use?}
\index[general]{Use!What Database to}
-Before building Bacula you need to decide if you want to use SQLite, MySQL, or
-PostgreSQL. If you are not already running MySQL or PostgreSQL, you might
-want to start by testing with SQLite (not supported on Solaris).
-This will greatly simplify the setup for you
-because SQLite is compiled into Bacula an requires no administration. It
-performs well and is suitable for small to medium sized installations (maximum
-10-20 machines). However, we should note that a number of users have
-had unexplained database corruption with SQLite. For that reason, we
-recommend that you install either PostgreSQL or MySQL for production
-work.
-
If you wish to use MySQL as the Bacula catalog, please see the
\ilink{Installing and Configuring MySQL}{MySqlChapter} chapter of this
manual. You will need to install MySQL prior to continuing with the
configuration of Bacula. MySQL is a high quality database that is very
efficient and is suitable for small and medium sized installation (up to
-2,000,000 files per job). It is slightly more complicated than SQLite to setup
-and administer because it has a number of sophisticated features such as
-userids and passwords. It runs as a separate process, is truly professional and
-can manage a database of any size.
+2,000,000 files per job).
If you wish to use PostgreSQL as the Bacula catalog, please see the
\ilink{Installing and Configuring PostgreSQL}{PostgreSqlChapter} chapter of
many optimized PostgreSQL functions, and can run more than 10 time faster on
jobs having millions of files than MySQL (Specially in during restore, accurate
mode, bvfs queries and when the database server is not on the same host than
-the Director). It's possible to switch from MySQL/SQLite to PostgreSQL, but it
-requires some DBA knowledge.
-
-If you wish to use SQLite as the Bacula catalog, please see
-\ilink{Installing and Configuring SQLite}{SqlLiteChapter} chapter of
-this manual. SQLite is not supported on Solaris.
+the Director).
\section{Quick Start}
\index[general]{Quick Start}
thread safe. If you find that batch mode is not enabled on your Bacula
installation, then your database most likely does not support threads.
- SQLite2 is not thread safe. Batch insert cannot be enabled when using
- SQLite2
-
- On most systems, MySQL, PostgreSQL and SQLite3 are thread safe.
+ On most systems, MySQL and PostgreSQLare thread safe.
To verify that your PostgreSQL is thread safe, you can try this
(change the path to point to your particular installed libpq.a;
explicitly need to use this option so that Bacula does not attempt
to reference OS function calls that do not exist.
-\item [ {-}{\-}with-sqlite3=\lt{}sqlite3-path\gt{}]
- \index[general]{{-}{\-}with-sqlite3}
- This enables use of the SQLite version 3.x database. The {\bf
- sqlite3-path} is not normally specified as Bacula looks for the
- necessary components in a standard location ({\bf depkgs/sqlite3}). See
- \ilink{Installing and Configuring SQLite}{SqlLiteChapter} chapter of
- this manual for more details. SQLite3 is not supported on Solaris.
-
\item [ {-}{\-}with-mysql=\lt{}mysql-path\gt{}]
\index[general]{{-}{\-}with-mysql}
This enables building of the Catalog services for Bacula. It assumes
simply use {\bf {-}{\-}with-postgresql}.
Note, for Bacula to be configured properly, you must specify one
- of the three database options supported. That is:
- {-}{\-}with-sqlite3, {-}{\-}with-mysql, or
+ of the two database options supported. That is:
{-}{\-}with-postgresql, otherwise the ./configure will fail.
\smallskip
- Note: SQLite is no longer supported. The code remains in Bacula but
- we no longer test it.
\item [ {-}{\-}with-openssl=\lt{}path\gt{}]
\section{Red Hat}
\index[general]{Red Hat}
-Using SQLite:
-
-\footnotesize
-\begin{verbatim}
-
-CFLAGS="-g -Wall" ./configure \
- --sbindir=/opt/bacula/bin \
- --sysconfdir=/opt/bacula/etc \
- --enable-smartalloc \
- --with-sqlite=$HOME/bacula/depkgs/sqlite \
- --with-working-dir=/opt/bacula/working \
- --with-pid-dir=/opt/bacula/working \
- --with-subsys-dir=/opt/bacula/working \
- --enable-bat \
- --enable-readline
-\end{verbatim}
-\normalsize
-
or
\footnotesize
./configure}.
Since the File daemon does not access the Catalog database, you can remove
-the {\bf \verb:--:with-mysql} or {\bf \verb:--:with-sqlite} options, then
+the {\bf \verb:--:with-mysql} ption, then
add {\bf \verb:--:enable-client-only}. This will compile only the
necessary libraries and the client programs and thus avoids the necessity
of installing one or another of those database programs to build the File
After doing a {\bf make install} the following files will be installed on your
system (more or less). The exact files and location (directory) for each file
-depends on your {\bf ./configure} command (e.g. if you are using SQLite instead
-of MySQL, some of the files will be different).
+depends on your {\bf ./configure} command.
NOTE: it is quite probable that this list is out of date. But it is a
starting point.
\include{mysql} % catalog
\include{postgresql} % catalog
-\include{sqlite} % catalog
+%\include{sqlite} % catalog
\include{catmaintenance} %catalog
\include{tapetesting} % install
The new \texttt{.estimate} command can be used to get statistics about a
job to run. The command uses the database to approximate the size and the
number of files of the next job. On a PostgreSQL database, the command
-uses regression slope to compute values. On SQLite or MySQL, where these
+uses regression slope to compute values. On MySQL, where these
statistical functions are not available, the command uses a simple
``average'' estimation. The correlation number is given for each value.
./configure can be run with multiple database configure options.
\begin{verbatim}
- --with-sqlite3
--with-mysql
--with-postgresql
\end{verbatim}
\begin{itemize}
\item postgresql
\item mysql
-\item sqlite3
\end{itemize}
Each configured backend generates a file named:
interface with the rest of SQL code. From now on there is a strict
boundary between the SQL code and the low-level database functions. This
new interface should also make it easier to add a new backend for a
-currently unsupported database. As part of the rewrite the SQLite 2 code
-was removed (e.g. only SQLite 3 is now supported). An extra bonus of the
-new code is that you can configure multiple backends in the configure and
-build all backends in one compile session and select the correct database
-backend at install time. This should make it a lot easier for packages
-maintainers.
+currently unsupported database. An extra bonus of the new code is that you
+can configure multiple backends in the configure and build all backends in
+one compile session and select the correct database backend at install
+time. This should make it a lot easier for packages maintainers.
removed from the code.
\begin{itemize}
\item Gnome console
-\item Support for SQLite 2
+\item Support for SQLite
\end{itemize}
\subsection{Misc Changes}
\subsection{libdbi Framework}
\index[general]{libdbi Framework}
As a general guideline, Bacula has support for a few catalog database drivers
-(MySQL, PostgreSQL, SQLite)
+(MySQL and PostgreSQL)
coded natively by the Bacula team. With the libdbi implementation, which is a
Bacula driver that uses libdbi to access the catalog, we have an open field to
use many different kinds database engines following the needs of users.
\begin{itemize}
\item PostgreSQL, with and without batch insert
\item Mysql, with and without batch insert
- \item SQLite
- \item SQLite3
\end{itemize}
In the future, we will test and approve to use others databases engines
The parameter {\bf dbdriver} indicates that we will use the driver dbi with a
mysql database. Currently the drivers supported by Bacula are: postgresql,
-mysql, sqlite, sqlite3; these are the names that may be added to string "dbi:".
+amd mysql; these are the names that may be added to string "dbi:".
The following limitations apply when Bacula is set to use the libdbi framework:
- Not tested on the Win32 platform
\begin{verbatim}
$ dbcheck -B
catalog=MyCatalog
- db_type=SQLite
+ db_type=MySQL
db_name=regress
db_driver=
db_user=regress
\begin{itemize}
\item MySQL 4.1
\item PostgreSQL 7.4
- \item SQLite 3
\end{itemize}
\item If you want to build the Win32 binaries, please see the
the bootstrap file.
\item If you have the Bootstrap file, you should now be back up and running,
if you do not have a Bootstrap file, continue with the suggestions below.
-\item Using {\bf bscan} scan the last set of backup tapes into your MySQL,
- PostgreSQL or SQLite database.
+\item Using {\bf bscan} scan the last set of backup tapes into your MySQL
+ or PostgreSQL database.
\item Start Bacula, and using the Console {\bf restore} command, restore the
last valid copy of the Bacula database and the Bacula configuration
files.
\item[Problem]
My database is broken.
\item[Solution]
- For SQLite, use the vacuum command to try to fix the database. For either
- MySQL or PostgreSQL, see the vendor's documentation. They have specific tools
- that check and repair databases, see the \ilink{database repair}{DatabaseRepair} sections of this manual for links to vendor
- information.
+ For either MySQL or PostgreSQL, see the vendor's documentation. They
+ have specific tools that check and repair databases, see the
+ \ilink{database repair}{DatabaseRepair} sections of this manual for
+ links to vendor information.
Assuming the above does not resolve the problem, you will need to restore
or rebuild your catalog. Note, if it is a matter of some
projects / bacula / docs / commitdiff