+++ /dev/null
-%%
-%%
-
-\chapter{Catalog Maintenance}
-\label{CatMaintenanceChapter}
-\index[general]{Maintenance!Catalog }
-\index[general]{Catalog Maintenance }
-
-Without proper setup and maintenance, your Catalog may continue to grow
-indefinitely as you run Jobs and backup Files, and/or it may become
-very inefficient and slow. How fast the size of your
-Catalog grows depends on the number of Jobs you run and how many files they
-backup. By deleting records within the database, you can make space available
-for the new records that will be added during the next Job. By constantly
-deleting old expired records (dates older than the Retention period), your
-database size will remain constant.
-
-If you started with the default configuration files, they already contain
-reasonable defaults for a small number of machines (less than 5), so if you
-fall into that case, catalog maintenance will not be urgent if you have a few
-hundred megabytes of disk space free. Whatever the case may be, some knowledge
-of retention periods will be useful.
-\label{Retention}
-
-\section{Setting Retention Periods}
-\index[general]{Setting Retention Periods }
-\index[general]{Periods!Setting Retention }
-
-{\bf Bacula} uses three Retention periods: the {\bf File Retention} period,
-the {\bf Job Retention} period, and the {\bf Volume Retention} period. Of
-these three, the File Retention period is by far the most important in
-determining how large your database will become.
-
-The {\bf File Retention} and the {\bf Job Retention} are specified in each
-Client resource as is shown below. The {\bf Volume Retention} period is
-specified in the Pool resource, and the details are given in the next chapter
-of this manual.
-
-\begin{description}
-
-\item [File Retention = \lt{}time-period-specification\gt{}]
- \index[general]{File Retention }
- The File Retention record defines the length of time that Bacula will keep
-File records in the Catalog database. When this time period expires, and if
-{\bf AutoPrune} is set to {\bf yes}, Bacula will prune (remove) File records
-that are older than the specified File Retention period. The pruning will
-occur at the end of a backup Job for the given Client. Note that the Client
-database record contains a copy of the File and Job retention periods, but
-Bacula uses the current values found in the Director's Client resource to do
-the pruning.
-
-Since File records in the database account for probably 80 percent of the
-size of the database, you should carefully determine exactly what File
-Retention period you need. Once the File records have been removed from
-the database, you will no longer be able to restore individual files
-in a Job. However, with Bacula version 1.37 and later, as long as the
-Job record still exists, you will be able to restore all files in the
-job.
-
-Retention periods are specified in seconds, but as a convenience, there are
-a number of modifiers that permit easy specification in terms of minutes,
-hours, days, weeks, months, quarters, or years on the record. See the
-\ilink{ Configuration chapter}{Time} of this manual for additional details
-of modifier specification.
-
-The default File retention period is 60 days.
-
-\item [Job Retention = \lt{}time-period-specification\gt{}]
- \index[general]{Job Retention }
- The Job Retention record defines the length of time that {\bf Bacula}
-will keep Job records in the Catalog database. When this time period
-expires, and if {\bf AutoPrune} is set to {\bf yes} Bacula will prune
-(remove) Job records that are older than the specified Job Retention
-period. Note, if a Job record is selected for pruning, all associated File
-and JobMedia records will also be pruned regardless of the File Retention
-period set. As a consequence, you normally will set the File retention
-period to be less than the Job retention period.
-
-As mentioned above, once the File records are removed from the database,
-you will no longer be able to restore individual files from the Job.
-However, as long as the Job record remains in the database, you will be
-able to restore all the files backuped for the Job (on version 1.37 and
-later). As a consequence, it is generally a good idea to retain the Job
-records much longer than the File records.
-
-The retention period is specified in seconds, but as a convenience, there
-are a number of modifiers that permit easy specification in terms of
-minutes, hours, days, weeks, months, quarters, or years. See the \ilink{
-Configuration chapter}{Time} of this manual for additional details of
-modifier specification.
-
-The default Job Retention period is 180 days.
-
-\item [AutoPrune = \lt{}yes/no\gt{}]
- \index[general]{AutoPrune }
- If AutoPrune is set to {\bf yes} (default), Bacula will automatically apply
-the File retention period and the Job retention period for the Client at the
-end of the Job.
-
-If you turn this off by setting it to {\bf no}, your Catalog will grow each
-time you run a Job.
-\end{description}
-
-\label{CompactingMySQL}
-\section{Compacting Your MySQL Database}
-\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.
-
-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.
-
-For a {\bf MySQL} database, you could write the Bacula database as an ASCII
-file (bacula.sql) then reload it by doing the following:
-
-\footnotesize
-\begin{verbatim}
-mysqldump -f --opt bacula > bacula.sql
-mysql bacula < bacula.sql
-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, if I cd to the location of the MySQL
-Bacula database (typically /opt/mysql/var or something similar) and enter:
-
-\footnotesize
-\begin{verbatim}
-du bacula
-\end{verbatim}
-\normalsize
-
-I get {\bf 620,644} which means there are that many blocks containing 1024
-bytes each or approximately 635 MB of data. After doing the {\bf mysqldump}, I
-had a bacula.sql file that had {\bf 174,356} blocks, and after doing the {\bf
-mysql} command to recreate the database, I ended up with a total of {\bf
-210,464} blocks rather than the original {\bf 629,644}. In other words, the
-compressed version of the database took approximately one third of the space
-of the database that had been in use for about a year.
-
-As a consequence, I suggest you monitor the size of your database and from
-time to time (once every six months or year), compress it.
-
-\label{DatabaseRepair}
-\label{RepairingMySQL}
-\section{Repairing Your MySQL Database}
-\index[general]{Database!Repairing Your MySQL }
-\index[general]{Repairing Your MySQL Database }
-
-If you find that you are getting errors writing to your MySQL database, or
-Bacula hangs each time it tries to access the database, you should consider
-running MySQL's database check and repair routines. The program you need to
-run depends on the type of database indexing you are using. If you are using
-the default, you will probably want to use {\bf myisamchk}. For more details
-on how to do this, please consult the MySQL document at:
-\elink{
-http://www.mysql.com/doc/en/Repair.html}
-{http://www.mysql.com/doc/en/Repair.html}.
-
-If the errors you are getting are simply SQL warnings, then you might try
-running dbcheck before (or possibly after) using the MySQL database repair
-program. It can clean up many of the orphaned record problems, and certain
-other inconsistencies in the Bacula database.
-
-A typical cause of MySQL database problems is if your partition fills. In
-such a case, you will need to create additional space on the partition or
-free up some space then repair the database probably using {\bf myisamchk}.
-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}
-
-\section{MySQL Temporary Tables}
-When doing backups with large numbers of files, MySQL creates some
-temporary tables. When these tables are small they can be held in
-system memory, but as they approach some size, they
-spool off to disk. The default location for these temp tables is
-/tmp. Once that space fills up, Bacula daemons such as the Storage
-daemon doing spooling can get strange errors. E.g.
-
-\footnotesize
-\begin{verbatim}
-Fatal error: spool.c:402 Spool data read error.
-Fatal error: backup.c:892 Network send error to SD. ERR=Connection reset by
-peer
-\end{verbatim}
-\normalsize
-
-What you need to do is setup MySQL to use a different (larger) temp
-directory, which can be set in the /etc/my.cnf with these variables
-set:
-
-\footnotesize
-\begin{verbatim}
- tmpdir=/path/to/larger/tmpdir
- bdb_tmpdir=/path/to/larger/tmpdir
-\end{verbatim}
-\normalsize
-
-\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 two 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
-one is the (JobId, PathId, Filename) 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_jpf_idx on file (jobid, pathid, filenameid);
-\end{verbatim}
-\normalsize
-
-Make sure that you doesn't have an index on File (filenameid, pathid).
-
-\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 (JobId, 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 Path, 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}
-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 }
-\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 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}.
-
-\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
- # WARNING!!! Passing the password via the command line is insecure.
- # see comments in make_catalog_backup for details.
- 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{BackingUpBaculaSecurityConsiderations}
-\section{Security considerations}
-\index[general]{Backing Up Your Bacula Database - Security Considerations }
-\index[general]{Database!Backing Up Your Bacula Database - Security Considerations }
-
-We provide make\_catalog\_backup as an example of what can be used to backup
-your Bacula database. We expect you to take security precautions relevant
-to your situation. make\_catalog\_backup is designed to take a password on
-the command line. This is fine on machines with only trusted users. It is
-not acceptable on machines without trusted users. Most database systems
-provide a alternative method, which does not place the password on the
-command line.
-
-The make\_catalog\_backup script contains some warnings about how to use it. Please
-read those tips.
-
-To help you get started, we know PostgreSQL has a password file,
-\elink{
-.pgpass}{http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html}, and
-we know MySQL has
-\elink{ .my.cnf}{http://dev.mysql.com/doc/refman/4.1/en/password-security.html}.
-
-Only you can decide what is appropriate for your situation. We have provided
-you with a starting point. We hope it helps.
-
-
-\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.
projects / bacula / docs / blobdiff