---------- Forwarded message ----------
From:
Bartosz.C <bartosz.c AT gmail DOT com>Date: 10 December 2010 13:11
Subject: Re: [Bacula-users] Huge MySQL database file
To: Dan Langille <
dan AT langille DOT org>
On 10 December 2010 00:25, Dan Langille
<dan AT langille DOT org> wrote:
On 12/9/2010 6:51 AM, Bartosz.C wrote:
<mailto:
dan AT langille DOT org>> wrote:
On 12/9/2010 6:15 AM, Bartosz.C wrote:
Hi,
I have Bacula backup + MySQL database for Bacula. It works about
half year.
I have noticed that file of database has 160GB. Is there any
option to
reduce this size.
I think that most information in this file is useless - fe
backup that
no longer exist.
How to reduce size of that database file?
Can I configure Bacula that it can be deleted themselvs such no
needed data?
Bartosz.
Look at the prune / auto prune directives.
--
> In my config in every Client and Pool content is AutoPrune = yes
>
> Client {
> Name = waw-samba-fd
> Address = 192.168.201.5
> FDPort = 9102
> Catalog = MyCatalog
> Password = "xxx" # password for FileDaemon
> File Retention = 30 days # 30 days
> Job Retention = 6 months # six months
> AutoPrune = yes # Prune expired Jobs/Files
> }
>
> ################## end of clients ###################
>
>
> ################## Pools #####################
>
> Pool {
> Name = sys_waw_samba_codz
> Pool Type = Backup
> Recycle = yes
> AutoPrune = yes
> Volume Retention = 6 days
> Maximum Volume Jobs = 1
> Label Format = sys_waw_samba_codz-
> Maximum Volumes = 8
> }
>
> And so on, and so on on every Clients and Pools. But it not working
> like I think it should.
Replying at the bottom keeps everything in easily-read order.
According to this, everything older than 6 days will be pruned from the
database. Are your job emails showing something similar to this:
09-Dec 07:58 bacula-dir JobId 41183: Begin pruning Jobs older than 3 years .
09-Dec 07:58 bacula-dir JobId 41183: No Jobs found to prune.
09-Dec 07:58 bacula-dir JobId 41183: Begin pruning Jobs.
09-Dec 07:58 bacula-dir JobId 41183: No Files found to prune.
09-Dec 07:58 bacula-dir JobId 41183: End auto prune.
But, hopefully, finding something to prune.
>Replying at the bottom keeps everything in easily-read order.
yup, I am sorry for that.
regarding one of answer of my question:
mysql> SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
-> CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 /
1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;
+--------------------+---------------------------------------+------------+
| Database | Table | Size |
+--------------------+---------------------------------------+------------+
| information_schema | CHARACTER_SETS | 0.00 Mb |
| information_schema | COLLATIONS | 0.00 Mb |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | 0.00 Mb |
| information_schema | COLUMNS | 0.00 Mb |
| information_schema | COLUMN_PRIVILEGES | 0.00 Mb |
| information_schema | KEY_COLUMN_USAGE | 0.00 Mb |
| information_schema | PROFILING | 0.00 Mb |
| information_schema | ROUTINES | 0.00 Mb |
| information_schema | SCHEMATA | 0.00 Mb |
| information_schema | SCHEMA_PRIVILEGES | 0.00 Mb |
| information_schema | STATISTICS | 0.00 Mb |
| information_schema | TABLES | 0.00 Mb |
| information_schema | TABLE_CONSTRAINTS | 0.00 Mb |
| information_schema | TABLE_PRIVILEGES | 0.00 Mb |
| information_schema | TRIGGERS | 0.00 Mb |
| information_schema | USER_PRIVILEGES | 0.00 Mb |
| information_schema | VIEWS | 0.00 Mb |
| bacula | BaseFiles | 0.00 Mb |
| bacula | CDImages | 0.00 Mb |
| bacula | Client | 0.00 Mb |
| bacula | Counters | 0.00 Mb |
| bacula | Device | 0.00 Mb |
| bacula | File | 1582.75 Mb |
| bacula | FileSet | 0.00 Mb |
| bacula | Filename | 48.04 Mb |
| bacula | Job | 0.19 Mb |
| bacula | JobMedia | 0.17 Mb |
| bacula | Location | 0.00 Mb |
| bacula | LocationLog | 0.00 Mb |
| bacula | Log | 0.00 Mb |
| bacula | Media | 0.06 Mb |
| bacula | MediaType | 0.00 Mb |
| bacula | Path | 63.23 Mb |
| bacula | Pool | 0.01 Mb |
| bacula | Status | 0.00 Mb |
| bacula | Storage | 0.00 Mb |
| bacula | UnsavedFiles | 0.00 Mb |
| bacula | Version | 0.00 Mb |
| mysql | columns_priv | 0.00 Mb |
| mysql | db | 0.01 Mb |
| mysql | func | 0.00 Mb |
| mysql | help_category | 0.02 Mb |
| mysql | help_keyword | 0.09 Mb |
| mysql | help_relation | 0.02 Mb |
| mysql | help_topic | 0.35 Mb |
| mysql | host | 0.00 Mb |
| mysql | proc | 0.00 Mb |
| mysql | procs_priv | 0.00 Mb |
| mysql | tables_priv | 0.00 Mb |
| mysql | time_zone | 0.00 Mb |
| mysql | time_zone_leap_second | 0.00 Mb |
| mysql | time_zone_name | 0.00 Mb |
| mysql | time_zone_transition | 0.00 Mb |
| mysql | time_zone_transition_type | 0.00 Mb |
| mysql | user | 0.00 Mb |
+--------------------+---------------------------------------+------------+
55 rows in set (0.28 sec)
So all together should be about 1,6 GB - the bigest table is File but it have only 1582 Mb
Backup is my mysql database file.
~#ls -sh | grep backup
149G backup
So its much more than 1,6.
>According to this, everything older than 6 days will be pruned from the
database. (...)
My standard returning email:
09-Dec 20:19 backup-dir JobId 5603: Start Backup JobId 5603, Job=sys_awia_codz.2010-12-09_20.19.00.17
09-Dec 21:05 backup-dir JobId 5603: There are no more Jobs associated with Volume "sys_awia_codz-0046". Marking it purged.
09-Dec 21:05 backup-dir JobId 5603: All records pruned from Volume "sys_awia_codz-0046"; marking it "Purged"
09-Dec 21:05 backup-dir JobId 5603: Recycled volume "sys_awia_codz-0046"
09-Dec 21:05 backup-dir JobId 5603: Using Device "backup-file-usb"
09-Dec 21:05 backup-sd JobId 5603: Recycled volume "sys_awia_codz-0046"
on device "backup-file-usb" (/virtual/backup-main), all previous data
lost.
09-Dec 21:05 backup-dir JobId 5603: Max Volume jobs exceeded. Marking Volume "sys_awia_codz-0046" as Used.
09-Dec 21:08 backup-sd JobId 5603: Job write elapsed time = 00:03:03,
Transfer rate = 795.2 K bytes/second 09-Dec 21:08 backup-dir JobId 5603:
Bacula backup-dir 2.4.4 (28Dec08): 09-Dec-2010 21:08:47
Build OS: i486-pc-linux-gnu debian 5.0
JobId: 5603
Job: sys_awia_codz.2010-12-09_20.19.00.17
Backup Level: Full
Client: "awia-fd" i486-pc-linux-gnu,debian,4.0
FileSet: "system_awia" 2010-03-25 09:21:38
Pool: "sys_awia_codz" (From Job resource)
Storage: "backup-main" (From Job resource)
Scheduled time: 09-Dec-2010 20:19:00
Start time: 09-Dec-2010 21:05:28
End time: 09-Dec-2010 21:08:47
Elapsed time: 3 mins 19 secs
Priority: 10
FD Files Written: 18,038
SD Files Written: 18,038
FD Bytes Written: 142,565,680 (142.5 MB)
SD Bytes Written: 145,532,444 (145.5 MB)
Rate: 716.4 KB/s
Software Compression: 88.8 %
VSS: no
Storage Encryption: no
Volume name(s): sys_awia_codz-0046
Volume Session Id: 13
Volume Session Time: 1291892115
Last Volume Bytes: 146,411,303 (146.4 MB)
Non-fatal FD errors: 0
SD Errors: 0
FD termination status: OK
SD termination status: OK
Termination: Backup OK
09-Dec 21:08 backup-dir JobId 5603: Begin pruning Jobs.
09-Dec 21:08 backup-dir JobId 5603: Pruned 3 Jobs for client awia-fd from catalog.
09-Dec 21:08 backup-dir JobId 5603: Begin pruning Files.
09-Dec 21:08 backup-dir JobId 5603: No Files found to prune.
09-Dec 21:08 backup-dir JobId 5603: End auto prune.
Bartosz.
