Debian Codegraph @

manuals/en/main/catmaintenance.tex

raw
1 \chapter{Catalog Maintenance}
2 \label{blb:CatMaintenanceChapter}
3 \index[general]{Maintenance!Catalog }
4 \index[general]{Catalog Maintenance }
5
6 Without proper setup and maintenance, your Catalog may continue to grow
7 indefinitely as you run Jobs and backup Files, and/or it may become
8 very inefficient and slow. How fast the size of your
9 Catalog grows depends on the number of Jobs you run and how many files they
10 backup. By deleting records within the database, you can make space available
11 for the new records that will be added during the next Job. By constantly
12 deleting old expired records (dates older than the Retention period), your
13 database size will remain constant.
14
15 If you started with the default configuration files, they already contain
16 reasonable defaults for a small number of machines (less than 5), so if you
17 fall into that case, catalog maintenance will not be urgent if you have a few
18 hundred megabytes of disk space free. Whatever the case may be, some knowledge
19 of retention periods will be useful.
20 \label{blb:Retention}
21
22 \section{Setting Retention Periods}
23 \index[general]{Setting Retention Periods }
24 \index[general]{Periods!Setting Retention }
25
26 \textbf{\bacula{}} uses three Retention periods: the \textbf{File Retention} period,
27 the \textbf{Job Retention} period, and the \textbf{Volume Retention} period. Of
28 these three, the File Retention period is by far the most important in
29 determining how large your database will become.
30
31 The \textbf{File Retention} and the \textbf{Job Retention} are specified in each
32 Client resource as is shown below. The \textbf{Volume Retention} period is
33 specified in the Pool resource, and the details are given in the next chapter
34 of this manual.
35
36 \begin{bdescription}
37
38 \item [File Retention = \bbracket{time-period-specification}]
39 \index[general]{File Retention }
40 The File Retention record defines the length of time that \bacula{} will keep
41 File records in the Catalog database. When this time period expires, and if
42 \textbf{\bdirectivename{AutoPrune}} is set to \textbf{yes}, \bacula{} will prune (remove) File records
43 that are older than the specified File Retention period. The pruning will
44 occur at the end of a backup Job for the given Client. Note that the Client
45 database record contains a copy of the File and Job retention periods, but
46 \bacula{} uses the current values found in the Director's Client resource to do
47 the pruning.
48
49 Since File records in the database account for probably 80 percent of the
50 size of the database, you should carefully determine exactly what File
51 Retention period you need. Once the File records have been removed from
52 the database, you will no longer be able to restore individual files
53 in a Job. However, with \bacula{} version 1.37 and later, as long as the
54 Job record still exists, you will be able to restore all files in the
55 job.
56
57 Retention periods are specified in seconds, but as a convenience, there are
58 a number of modifiers that permit easy specification in terms of minutes,
59 hours, days, weeks, months, quarters, or years on the record. See the
60 \bilink{Configuration chapter}{blb:Time} of this manual for additional details
61 of modifier specification.
62
63 The default File retention period is \bdefaultvalue{60} days.
64
65 \item [Job Retention = \bbracket{time-period-specification}]
66 \index[general]{Job Retention }
67 The Job Retention record defines the length of time that \textbf{\bacula{}}
68 will keep Job records in the Catalog database. When this time period
69 expires, and if \textbf{\bdirectivename{AutoPrune}} is set to \textbf{yes} \bacula{} will prune
70 (remove) Job records that are older than the specified Job Retention
71 period. Note, if a Job record is selected for pruning, all associated File
72 and JobMedia records will also be pruned regardless of the File Retention
73 period set. As a consequence, you normally will set the File retention
74 period to be less than the Job retention period.
75
76 As mentioned above, once the File records are removed from the database,
77 you will no longer be able to restore individual files from the Job.
78 However, as long as the Job record remains in the database, you will be
79 able to restore all the files backuped for the Job (on version 1.37 and
80 later). As a consequence, it is generally a good idea to retain the Job
81 records much longer than the File records.
82
83 The retention period is specified in seconds, but as a convenience, there
84 are a number of modifiers that permit easy specification in terms of
85 minutes, hours, days, weeks, months, quarters, or years. See the
86 \bilink{Configuration chapter}{blb:Time} of this manual for additional details of
87 modifier specification.
88
89 The default Job Retention period is \bdefaultvalue{180} days.
90
91 \item [AutoPrune = \byesorno{}]
92 \index[general]{AutoPrune }
93 If AutoPrune is set to \bdefaultvalue{yes} (default), \bacula{} will automatically apply
94 the File retention period and the Job retention period for the Client at the
95 end of the Job.
96
97 If you turn this off by setting it to \textbf{no}, your Catalog will grow each
98 time you run a Job.
99 \end{bdescription}
100
101 \label{blb:CompactingMySQL}
102 \section{Compacting Your \mysql{} Database}
103 \index[general]{Database!Compacting Your \mysql{} }
104 \index[general]{Compacting Your \mysql{} Database }
105
106 Over time, as noted above, your database will tend to grow. I've noticed
107 that even though Bacula regularly prunes files, \mysql{} does not
108 effectively use the space, and instead continues growing. To avoid this,
109 from time to time, you must compact your database. Normally, large
110 commercial database such as Oracle have commands that will compact a
111 database to reclaim wasted file space. MySQL has the \textbf{OPTIMIZE TABLE}
112 command that you can use. We leave it to you to explore the utility of the
113 \textbf{OPTIMIZE TABLE} command in \mysql{}.
114
115 All database programs have some means of writing the database out in \acs{ASCII}
116 format and then reloading it. Doing so will re-create the database from
117 scratch producing a compacted result, so below, we show you how you can do
118 this for \mysql{}, \postgresql{}.
119
120 For a \textbf{\mysql{}} database, you could write the \bacula{} database as an \acs{ASCII}
121 file (\bfilename{bacula.sql}) then reload it by doing the following:
122
123 \begin{bVerbatim}
124 mysqldump -f --opt bacula > bacula.sql
125 mysql bacula < bacula.sql
126 rm -f bacula.sql
127 \end{bVerbatim}
128
129 Depending on the size of your database, this will take more or less time and a
130 fair amount of disk space. For example, if I cd to the location of the \mysql{}
131 \bacula{} database (typically \bdirectoryname{/opt/mysql/var} or something similar) and enter:
132
133 \begin{bVerbatim}
134 du bacula
135 \end{bVerbatim}
136
137 I get \textbf{620,644} which means there are that many blocks containing 1024
138 bytes each or approximately 635 MB of data. After doing the \btool{mysqldump}, I
139 had a \bfilename{bacula.sql} file that had \textbf{174,356} blocks, and after doing the \btool{mysql}
140 command to recreate the database, I ended up with a total of \textbf{210,464}
141 blocks rather than the original \textbf{629,644}. In other words, the
142 compressed version of the database took approximately one third of the space
143 of the database that had been in use for about a year.
144
145 As a consequence, I suggest you monitor the size of your database and from
146 time to time (once every six months or year), compress it.
147
148 \label{blb:DatabaseRepair}
149 \label{blb:RepairingMySQL}
150 \section{Repairing Your \mysql{} Database}
151 \index[general]{Database!Repairing Your \mysql{} }
152 \index[general]{Repairing Your \mysql{} Database }
153
154 If you find that you are getting errors writing to your \mysql{} database, or
155 \bacula{} hangs each time it tries to access the database, you should consider
156 running \mysql{}'s database check and repair routines. The program you need to
157 run depends on the type of database indexing you are using. If you are using
158 the default, you will probably want to use \btool{myisamchk}. For more details
159 on how to do this, please consult the \mysql{} document at:
160 \bref{http://www.mysql.com/doc/en/Repair.html}{www.mysql.com/doc/en/Repair.html}.
161
162 If the errors you are getting are simply \acs{SQL} warnings, then you might try
163 running dbcheck before (or possibly after) using the \mysql{} database repair
164 program. It can clean up many of the orphaned record problems, and certain
165 other inconsistencies in the \bacula{} database.
166
167 A typical cause of \mysql{} database problems is if your partition fills. In
168 such a case, you will need to create additional space on the partition or
169 free up some space then repair the database probably using \btool{myisamchk}.
170 Recently my root partition filled and the \mysql{} database was corrupted.
171 Simply running \btool{myisamchk -r} did not fix the problem. However,
172 the following script did the trick for me:
173
174 \begin{bVerbatim}
175 #!/bin/sh
176 for i in *.MYD ; do
177 mv $i x${i}
178 t=`echo $i | cut -f 1 -d '.' -`
179 mysql bacula <<END_OF_DATA
180 set autocommit=1;
181 truncate table $t;
182 quit
183 END_OF_DATA
184 cp x${i} ${i}
185 chown mysql:mysql ${i}
186 myisamchk -r ${t}
187 done
188 \end{bVerbatim}
189
190 I invoked it with the following commands:
191
192 \begin{bVerbatim}
193 cd /var/lib/mysql/bacula
194 ./repair
195 \end{bVerbatim}
196
197 Then after ensuring that the database was correctly fixed, I did:
198 \begin{bVerbatim}
199 cd /var/lib/mysql/bacula
200 rm -f x*.MYD
201 \end{bVerbatim}
202
203 \section{\mysql{} Table is Full}
204 \index[general]{Database!\mysql{} Table is Full}
205 \index[general]{\mysql{} Table is Full}
206
207 If you are running into the error \textbf{The table 'File' is full \ldots{}},
208 it is probably because on version 4.x \mysql{}, the table is limited by
209 default to a maximum size of 4 GB and you have probably run into
210 the limit. The solution can be found at:
211 \bref{http://dev.mysql.com/doc/refman/5.0/en/full-table.html}{dev.mysql.com/doc/refman/5.0/en/full-table.html}
212
213 You can display the maximum length of your table with:
214
215 \begin{bVerbatim}
216 mysql bacula
217 SHOW TABLE STATUS FROM bacula like "File";
218 \end{bVerbatim}
219
220 If the column labeled "Max\_data\_length" is around 4Gb, this is likely
221 to be the source of your problem, and you can modify it with:
222
223 \begin{bVerbatim}
224 mysql bacula
225 ALTER TABLE File MAX_ROWS=281474976710656;
226 \end{bVerbatim}
227
228 Alternatively you can modify your \bfilename{/etc/my.conf} file before creating the
229 \bacula{} tables, and in the \texttt{[mysqld]} section set:
230
231 \begin{bVerbatim}
232 set-variable = myisam_data_pointer_size=6
233 \end{bVerbatim}
234
235 The above myisam data pointer size must be made before you create your
236 \bacula{} tables or it will have no effect.
237
238 The row and pointer size changes should already be the default on \mysql{}
239 version 5.x, so making these changes should only be necessary on \mysql{} 4.x
240 depending on the size of your catalog database.
241
242 \section{\mysql{} Server Has Gone Away}
243 \index[general]{Database!\mysql{} Server Has Gone Away}
244 \index[general]{\mysql{} Server Has Gone Away}
245 If you are having problems with the \mysql{} server disconnecting or with
246 messages saying that your \mysql{} server has gone away, then please read
247 the \mysql{} documentation, which can be found at:
248
249 \bref{http://dev.mysql.com/doc/refman/5.0/en/gone-away.html}
250 {dev.mysql.com/doc/refman/5.0/en/gone-away.html}
251
252 \section{\mysql{} Temporary Tables}
253 When doing backups with large numbers of files, \mysql{} creates some
254 temporary tables. When these tables are small they can be held in
255 system memory, but as they approach some size, they
256 spool off to disk. The default location for these temp tables is
257 \bdirectoryname{/tmp}. Once that space fills up, \bacula{} daemons such as the Storage
258 daemon doing spooling can get strange errors. E.g.
259
260 \begin{bVerbatim}
261 Fatal error: spool.c:402 Spool data read error.
262 Fatal error: backup.c:892 Network send error to SD. ERR=Connection reset by peer
263 \end{bVerbatim}
264
265 What you need to do is setup \mysql{} to use a different (larger) temp
266 directory, which can be set in the \bfilename{/etc/my.cnf} with these variables
267 set:
268
269 \begin{bVerbatim}
270 tmpdir=/path/to/larger/tmpdir
271 bdb_tmpdir=/path/to/larger/tmpdir
272 \end{bVerbatim}
273
274 \label{blb:RepairingPSQL}
275 \section{Repairing Your \postgresql{} Database}
276 \index[general]{Database!Repairing Your \postgresql{} }
277 \index[general]{Repairing Your \postgresql{} Database }
278
279 The same considerations apply that are indicated above for \mysql{}. That is,
280 consult the \postgresql{} documents for how to repair the database, and also
281 consider using \bacula{}'s \btool{dbcheck} program if the conditions are reasonable for
282 using (see above).
283
284 \label{blb:DatabasePerformance}
285 \section{Database Performance Issues}
286 \index[general]{Database Performance Issues}
287 \index[general]{Performance!Database}
288
289 There are a considerable number of ways each of the databases can be
290 tuned to improve the performance. Going from an untuned database to one
291 that is properly tuned can make a difference of a factor of 100 or more
292 in the time to insert or search for records.
293
294 For each of the databases, you may get significant improvements by adding
295 additional indexes. The comments in the \bacula{} make\_xxx\_tables give some
296 indications as to what indexes may be appropriate. Please see below
297 for specific instructions on checking indexes.
298
299 For \mysql{}, what is very important is to use the examine the
300 \bfilename{my.cnf} file (usually in \bfilename{/etc/my.cnf}).
301 You may obtain significant performances by switching to
302 the \bfilename{my-large.cnf} or \bfilename{my-huge.cnf} files that come with the \mysql{} source
303 code.
304
305 For \postgresql{}, you might want to consider turning \texttt{fsync} off. Of course
306 doing so can cause corrupted databases in the event of a machine crash.
307 There are many different ways that you can tune \postgresql{}, the
308 following document discusses a few of them:
309 \bref{http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}
310 {www.varlena.com/varlena/GeneralBits/Tidbits/perf.html}.
311
312 There is also a \postgresql{} FAQ question number 3.3 that may
313 answer some of your questions about how to improve performance
314 of the \postgresql{} engine:
315 \bref{http://www.postgresql.org/docs/faqs.FAQ.html\#3.3}{www.postgresql.org/docs/faqs.FAQ.html\#3.3}.
316 % TODO: verify above is correct. is this okay for book?
317
318 Also for \postgresql{}, look at what ``\texttt{effective\_cache\_size}''. For a 2GB memory
319 machine, you probably want to set it at 131072, but don't set it too high.
320 In addition, for a 2GB system, \texttt{work\_mem = 256000} and
321 \texttt{maintenance\_work\_mem = 256000} seem to be reasonable values. Make
322 sure your \texttt{checkpoint\_segments} is set to at least 8.
323
324
325
326 \section{Performance Issues Indexes}
327 \index[general]{Database Performance Issues Indexes}
328 \index[general]{Performance!Database}
329 One of the most important considerations for improving performance on
330 the \bacula{} database is to ensure that it has all the appropriate indexes.
331 Several users have reported finding that their database did not have
332 all the indexes in the default configuration. In addition, you may
333 find that because of your own usage patterns, you need additional indexes.
334
335 The most important indexes for performance are the two indexes on the
336 \textbf{\btable{File}} table. The first index is on \textbf{\btable{FileId}} and is automatically
337 made because it is the unique key used to access the table. The other
338 one is the (JobId, PathId, Filename) index. If these Indexes
339 are not present, your performance may suffer a lot.
340
341 \subsection{\postgresql{} Indexes}
342 On \postgresql{}, you can check to see if you have the proper indexes using
343 the following commands:
344
345 \begin{bVerbatim}
346 psql bacula
347 select * from pg_indexes where tablename='file';
348 \end{bVerbatim}
349
350 If you do not see output that indicates that all three indexes
351 are created, you can create the two additional indexes using:
352
353 \begin{bVerbatim}
354 psql bacula
355 CREATE INDEX file_jobid_idx on file (jobid);
356 CREATE INDEX file_jpf_idx on file (jobid, pathid, filename);
357 \end{bVerbatim}
358
359 Make sure that you doesn't have an index on File (filenameid, pathid).
360
361 \subsection{\mysql{} Indexes}
362 On \mysql{}, you can check if you have the proper indexes by:
363
364 \begin{bVerbatim}
365 mysql bacula
366 show index from File;
367 \end{bVerbatim}
368
369 If the indexes are not present, especially the JobId index, you can
370 create them with the following commands:
371
372 \begin{bVerbatim}
373 mysql bacula
374 CREATE INDEX file_jobid_idx on File (JobId);
375 CREATE INDEX file_jpf_idx on File (JobId, PathId, Filename(255));
376 \end{bVerbatim}
377
378 Though normally not a problem, you should ensure that the indexes
379 defined for Filename and Path are both set to 255 characters. Some users
380 reported performance problems when their indexes were set to 50 characters.
381 To check, do:
382
383 \begin{bVerbatim}
384 mysql bacula
385 show index from Path;
386 \end{bVerbatim}
387
388 and what is important is that for Filename, you have an index with
389 Key\_name ``Name'' and Sub\_part ``255''. For Path, you should have a Key\_name
390 ``Path'' and Sub\_part ``255''. If one or the other does not exist or the
391 Sub\_part is less that 255, you can drop and recreate the appropriate
392 index with:
393
394 \begin{bVerbatim}
395 mysql bacula
396 DROP INDEX Path on Path;
397 CREATE INDEX Path on Path (Path(255));
398 \end{bVerbatim}
399
400
401 \label{blb:CompactingPostgres}
402 \section{Compacting Your \postgresql{} Database}
403 \index[general]{Database!Compacting Your \postgresql{} }
404 \index[general]{Compacting Your \postgresql{} Database }
405
406 Over time, as noted above, your database will tend to grow. I've noticed that
407 even though \bacula{} regularly prunes files, \postgresql{} has a \bcommandname{VACUUM}
408 command that will compact your database for you. Alternatively you may want to
409 use the \btool{vacuumdb} command, which can be run from a cron job.
410
411 All database programs have some means of writing the database out in \acs{ASCII}
412 format and then reloading it. Doing so will re-create the database from
413 scratch producing a compacted result, so below, we show you how you can do
414 this for \postgresql{}.
415
416 For a \textbf{\postgresql{}} database, you could write the \bacula{} database as an
417 \acs{ASCII} file (\bfilename{bacula.sql}) then reload it by doing the following:
418
419 \begin{bVerbatim}
420 pg_dump -c bacula > bacula.sql
421 cat bacula.sql | psql bacula
422 rm -f bacula.sql
423 \end{bVerbatim}
424
425 Depending on the size of your database, this will take more or less time and a
426 fair amount of disk space. For example, you can \btool{cd} to the location of
427 the \bacula{} database (typically \bdirectoryname{/usr/local/pgsql/data} or possible
428 \bdirectoryname{/var/lib/pgsql/data}) and check the size.
429
430 There are certain \postgresql{} users who do not recommend the above
431 procedure. They have the following to say:
432 \postgresql{} does not
433 need to be dumped/restored to keep the database efficient. A normal
434 process of vacuuming will prevent the database from every getting too
435 large. If you want to fine-tweak the database storage, commands such
436 as \bcommandname{VACUUM FULL}, \bcommandname{REINDEX}, and \bcommandname{CLUSTER} exist specifically to keep you
437 from having to do a dump/restore.
438
439 Finally, you might want to look at the \postgresql{} documentation on
440 this subject at
441 \bref{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}{www.postgresql.org/docs/8.1/interactive/maintenance.html}.
442
443 \section{Migrating from \sqlite{} to \mysql{} or \postgresql{}}
444 \index[general]{\mysql{}!Migrating from \sqlite{} to }
445 \index[general]{Migrating from \sqlite{} to \mysql{} or \postgresql{}}
446
447 On some older Bacula you may begun using Bacula with \sqlite{} then later find
448 that you want to switch to MySQL or Postgres for any of a number of
449 reasons: \sqlite{} is no longer supported by Bacula; \sqlite{} tends to use more
450 disk than \mysql{}; when the database is corrupted it is often more
451 catastrophic than with \mysql{} or \postgresql{}. Several users have succeeded in
452 converting by exporting the \sqlite{} data and then processing it with Perl
453 scripts prior to putting it into \mysql{} or \postgresql{}. This is, however, not
454 always a simple process. Scripts are available on bacula source distribution
455 under \texttt{examples/database}.
456
457
458 \label{blb:BackingUpBacula}
459 \section{Backing Up Your \bacula{} Database}
460 \index[general]{Backing Up Your \bacula{} Database }
461 \index[general]{Database!Backing Up Your \bacula{} }
462
463 If ever the machine on which your \bacula{} database crashes, and you need to
464 restore from backup tapes, one of your first priorities will probably be to
465 recover the database. Although \bacula{} will happily backup your catalog
466 database if it is specified in the FileSet, this is not a very good way to do
467 it, because the database will be saved while \bacula{} is modifying it. Thus the
468 database may be in an unstable state. Worse yet, you will backup the database
469 before all the \bacula{} updates have been applied.
470
471 To resolve these problems, you need to backup the database after all the backup
472 jobs have been run. In addition, you will want to make a copy while \bacula{} is
473 not modifying it. To do so, you can use two scripts provided in the release
474 \bfilename{make\_catalog\_backup.pl} and \bfilename{delete\_catalog\_backup.pl}. These files
475 will be automatically generated along with all the other \bacula{} scripts. The
476 first script will make an \acs{ASCII} copy of your \bacula{} database into \bfilename{bacula.sql}
477 in the working directory you specified in your configuration, and
478 the second will delete the \bfilename{bacula.sql} file.
479
480 The basic sequence of events to make this work correctly is as follows:
481
482 \begin{bitemize}
483 \item Run all your nightly backups
484 \item After running your nightly backups, run a Catalog backup Job
485 \item The Catalog backup job must be scheduled after your last nightly backup
486 \item You use \textbf{RunBeforeJob} to create the \acs{ASCII} backup file and \textbf{RunAfterJob} to clean up
487 \end{bitemize}
488
489 Assuming that you start all your nightly backup jobs at 1:05 am (and that they
490 run one after another), you can do the catalog backup with the following
491 additional Director configuration statements:
492
493 \begin{bVerbatim}
494 # Backup the catalog database (after the nightly save)
495 Job {
496 Name = "BackupCatalog"
497 Type = Backup
498 Client=rufus-fd
499 FileSet="Catalog"
500 Schedule = "WeeklyCycleAfterBackup"
501 Storage = DLTDrive
502 Messages = Standard
503 Pool = Default
504 # WARNING!!! Passing the password via the command line is insecure.
505 # see comments in make_catalog_backup.pl for details.
506 RunBeforeJob = "/opt/bacula/scripts/make_catalog_backup.pl MyCatalog"
507 RunAfterJob = "/opt/bacula/scripts/delete_catalog_backup"
508 Write Bootstrap = "/opt/bacula/working/BackupCatalog.bsr"
509 }
510 # This schedule does the catalog. It starts after the WeeklyCycle
511 Schedule {
512 Name = "WeeklyCycleAfterBackup
513 Run = Level=Full sun-sat at 1:10
514 }
515 # This is the backup of the catalog
516 FileSet {
517 Name = "Catalog"
518 Include {
519 Options {
520 signature=MD5
521 }
522 File = /opt/bacula/working/bacula.sql
523 }
524 }
525 \end{bVerbatim}
526
527 Be sure to write a bootstrap file as in the above example. However, it is preferable
528 to write or copy the bootstrap file to another computer. It will allow
529 you to quickly recover the database backup should that be necessary. If
530 you do not have a bootstrap file, it is still possible to recover your
531 database backup, but it will be more work and take longer.
532
533
534 \label{blb:BackingUpBaculaSecurityConsiderations}
535 \section{Security considerations}
536 \index[general]{Backing Up Your \bacula{} Database - Security Considerations }
537 \index[general]{Database!Backing Up Your \bacula{} Database - Security Considerations }
538
539 We provide \bfilename{make\_catalog\_backup.pl} as an example of what can be used to backup
540 your \bacula{} database. We expect you to take security precautions relevant
541 to your situation. \bfilename{make\_catalog\_backup.pl} is designed to take a password on
542 the command line. This is fine on machines with only trusted users. It is
543 not acceptable on machines without trusted users. Most database systems
544 provide a alternative method, which does not place the password on the
545 command line.
546
547 The \bfilename{make\_catalog\_backup.pl} script contains some warnings about how to use it. Please
548 read those tips.
549
550 To help you get started, we know \postgresql{} has a password file,
551 \bfootref{http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html}{.pgpass}, and
552 we know \mysql{} has
553 \bfootref{http://dev.mysql.com/doc/refman/4.1/en/password-security.html}{.my.cnf}.
554
555 Only you can decide what is appropriate for your situation. We have provided
556 you with a starting point. We hope it helps.
557
558
559 \label{blb:BackingUPOtherDBs}
560 \section{Backing Up Third Party Databases}
561 \index[general]{Backing Up Third Party Databases }
562 \index[general]{Databases!Backing Up Third Party }
563
564 If you are running a database in production mode on your machine, \bacula{} will
565 happily backup the files, but if the database is in use while \bacula{} is
566 reading it, you may back it up in an unstable state.
567
568 The best solution is to shutdown your database before backing it up, or use
569 some tool specific to your database to make a valid live copy perhaps by
570 dumping the database in \acs{ASCII} format. I am not a database expert, so I cannot
571 provide you advice on how to do this, but if you are unsure about how to
572 backup your database, you might try visiting the Backup Central site, which
573 has been renamed Storage Mountain (\bref{http://www.backupcentral.com}{www.backupcentral.com}). In particular,
574 their \bref{http://www.backupcentral.com/toc-free-backup-software.html}{Free Backup and Recovery Software} page has
575 links to scripts that show you how to shutdown and backup most major
576 databases.
577 \label{blb:Size}
578
579 \section{Database Size}
580 \index[general]{Size!Database }
581 \index[general]{Database Size }
582
583 As mentioned above, if you do not do automatic pruning, your Catalog will grow
584 each time you run a Job. Normally, you should decide how long you want File
585 records to be maintained in the Catalog and set the \textbf{File Retention}
586 period to that time. Then you can either wait and see how big your Catalog
587 gets or make a calculation assuming approximately 154 bytes for each File
588 saved and knowing the number of Files that are saved during each backup and
589 the number of Clients you backup.
590
591 For example, suppose you do a backup of two systems, each with 100,000 files.
592 Suppose further that you do a Full backup weekly and an Incremental every day,
593 and that the Incremental backup typically saves 4,000 files. The size of your
594 database after a month can roughly be calculated as:
595
596 \begin{bVerbatim}
597 Size = 154 * No. Systems * (100,000 * 4 + 10,000 * 26)
598 \end{bVerbatim}
599
600 where we have assumed four weeks in a month and 26 incremental backups per month.
601 This would give the following:
602
603 \begin{bVerbatim}
604 Size = 154 * 2 * (100,000 * 4 + 10,000 * 26)
605 \end{bVerbatim}
606 or
607 \begin{bVerbatim}
608 Size = 308 * (400,000 + 260,000)
609 \end{bVerbatim}
610 or
611 \begin{bVerbatim}
612 Size = 203,280,000 bytes
613 \end{bVerbatim}
614
615 So for the above two systems, we should expect to have a database size of
616 approximately 200 Megabytes. Of course, this will vary according to how many
617 files are actually backed up.
618
619 Below are some statistics for a \mysql{} database containing Job records for five
620 Clients beginning September 2001 through May 2002 (8.5 months) and File
621 records for the last 80 days. (Older File records have been pruned). For these
622 systems, only the user files and system files that change are backed up. The
623 core part of the system is assumed to be easily reloaded from the Red Hat rpms.
624
625
626 In the list below, the files (corresponding to \bacula{} tables) with the
627 extension \bfilename{.MYD} contain the data records whereas files with the extension \bfilename{.MYI}
628 contain indexes.
629
630 You will note that the File records (containing the file attributes) make up
631 the large bulk of the number of records as well as the space used (459 Mega
632 Bytes including the indexes). As a consequence, the most important Retention
633 period will be the \textbf{File Retention} period. A quick calculation shows that
634 for each File that is saved, the database grows by approximately 150 bytes.
635
636 \begin{bVerbatim}
637 Size in
638 Bytes Records File
639 ============ ========= ===========
640 168 5 Client.MYD
641 3,072 Client.MYI
642 344,394,684 3,080,191 File.MYD
643 115,280,896 File.MYI
644 2,590,316 106,902 Filename.MYD
645 3,026,944 Filename.MYI
646 184 4 FileSet.MYD
647 2,048 FileSet.MYI
648 49,062 1,326 JobMedia.MYD
649 30,720 JobMedia.MYI
650 141,752 1,378 Job.MYD
651 13,312 Job.MYI
652 1,004 11 Media.MYD
653 3,072 Media.MYI
654 1,299,512 22,233 Path.MYD
655 581,632 Path.MYI
656 36 1 Pool.MYD
657 3,072 Pool.MYI
658 5 1 Version.MYD
659 1,024 Version.MYI
660 \end{bVerbatim}
661
662 This database has a total size of approximately 450 Megabytes.