3 # shell script to create Bacula MySQL tables
5 # Copyright (C) 2000-2015 Kern Sibbald
6 # License: BSD 2-Clause; see file LICENSE-FOSS
9 # You won't get any support for performance issue if you changed the default
14 db_name=${db_name:-@db_name@}
16 if mysql $* -f <<END-OF-DATA
19 -- Note, we use BLOB rather than TEXT because in MySQL,
20 -- BLOBs are identical to TEXT except that BLOB is case
21 -- sensitive in sorts, which is what we want, and TEXT
22 -- is case insensitive.
24 CREATE TABLE Filename (
25 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
27 PRIMARY KEY(FilenameId),
32 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
38 -- We strongly recommend to avoid the temptation to add new indexes.
39 -- In general, these will cause very significant performance
40 -- problems in other areas. A better approch is to carefully check
41 -- that all your memory configuation parameters are
42 -- suitable for the size of your installation. If you backup
43 -- millions of files, you need to adapt the database memory
44 -- configuration parameters concerning sorting, joining and global
45 -- memory. By default, sort and join parameters are very small
46 -- (sometimes 8Kb), and having sufficient memory specified by those
47 -- parameters is extremely important to run fast.
50 -- FileIndex can be 0 for FT_DELETED files
51 -- FileNameId can link to Filename.Name='' for directories
53 FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
54 FileIndex INTEGER UNSIGNED DEFAULT 0,
55 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
56 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
57 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
58 DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
59 MarkId INTEGER UNSIGNED DEFAULT 0,
60 LStat TINYBLOB NOT NULL,
64 INDEX (JobId, PathId, FilenameId)
67 CREATE TABLE RestoreObject (
68 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
69 ObjectName BLOB NOT NULL,
70 RestoreObject LONGBLOB NOT NULL,
71 PluginName TINYBLOB NOT NULL,
72 ObjectLength INTEGER DEFAULT 0,
73 ObjectFullLength INTEGER DEFAULT 0,
74 ObjectIndex INTEGER DEFAULT 0,
75 ObjectType INTEGER DEFAULT 0,
76 FileIndex INTEGER UNSIGNED DEFAULT 0,
77 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
78 ObjectCompression INTEGER DEFAULT 0,
79 PRIMARY KEY(RestoreObjectId),
85 # Possibly add one or more of the following indexes
86 # to the above File table if your Verifies are
87 # too slow, but they can slow down backups.
93 CREATE TABLE MediaType (
94 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
95 MediaType TINYBLOB NOT NULL,
96 ReadOnly TINYINT DEFAULT 0,
97 PRIMARY KEY(MediaTypeId)
100 CREATE TABLE Storage (
101 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
102 Name TINYBLOB NOT NULL,
103 AutoChanger TINYINT DEFAULT 0,
104 PRIMARY KEY(StorageId)
107 CREATE TABLE Device (
108 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
109 Name TINYBLOB NOT NULL,
110 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
111 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
112 DevMounts INTEGER UNSIGNED DEFAULT 0,
113 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
114 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
115 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
116 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
117 DevReadTime BIGINT UNSIGNED DEFAULT 0,
118 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
119 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
120 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
121 CleaningDate DATETIME,
122 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
123 PRIMARY KEY(DeviceId)
128 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
129 Job TINYBLOB NOT NULL,
130 Name TINYBLOB NOT NULL,
131 Type BINARY(1) NOT NULL,
132 Level BINARY(1) NOT NULL,
133 ClientId INTEGER DEFAULT 0 REFERENCES Client,
134 JobStatus BINARY(1) NOT NULL,
138 RealEndTime DATETIME,
139 JobTDate BIGINT UNSIGNED DEFAULT 0,
140 VolSessionId INTEGER UNSIGNED DEFAULT 0,
141 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
142 JobFiles INTEGER UNSIGNED DEFAULT 0,
143 JobBytes BIGINT UNSIGNED DEFAULT 0,
144 ReadBytes BIGINT UNSIGNED DEFAULT 0,
145 JobErrors INTEGER UNSIGNED DEFAULT 0,
146 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
147 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
148 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
149 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
150 PurgedFiles TINYINT DEFAULT 0,
151 HasBase TINYINT DEFAULT 0,
152 HasCache TINYINT DEFAULT 0,
153 Reviewed TINYINT DEFAULT 0,
155 FileTable CHAR(20) DEFAULT 'File',
160 -- Create a table like Job for long term statistics
161 CREATE TABLE JobHisto (
162 JobId INTEGER UNSIGNED NOT NULL,
163 Job TINYBLOB NOT NULL,
164 Name TINYBLOB NOT NULL,
165 Type BINARY(1) NOT NULL,
166 Level BINARY(1) NOT NULL,
167 ClientId INTEGER DEFAULT 0,
168 JobStatus BINARY(1) NOT NULL,
172 RealEndTime DATETIME,
173 JobTDate BIGINT UNSIGNED DEFAULT 0,
174 VolSessionId INTEGER UNSIGNED DEFAULT 0,
175 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
176 JobFiles INTEGER UNSIGNED DEFAULT 0,
177 JobBytes BIGINT UNSIGNED DEFAULT 0,
178 ReadBytes BIGINT UNSIGNED DEFAULT 0,
179 JobErrors INTEGER UNSIGNED DEFAULT 0,
180 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
181 PoolId INTEGER UNSIGNED DEFAULT 0,
182 FileSetId INTEGER UNSIGNED DEFAULT 0,
183 PriorJobId INTEGER UNSIGNED DEFAULT 0,
184 PurgedFiles TINYINT DEFAULT 0,
185 HasBase TINYINT DEFAULT 0,
186 HasCache TINYINT DEFAULT 0,
187 Reviewed TINYINT DEFAULT 0,
189 FileTable CHAR(20) DEFAULT 'File',
195 CREATE TABLE Location (
196 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
197 Location TINYBLOB NOT NULL,
198 Cost INTEGER DEFAULT 0,
200 PRIMARY KEY(LocationId)
203 CREATE TABLE LocationLog (
204 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
206 Comment BLOB NOT NULL,
207 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
208 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
209 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
210 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
212 PRIMARY KEY(LocLogId)
217 CREATE TABLE FileSet (
218 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
219 FileSet TINYBLOB NOT NULL,
222 PRIMARY KEY(FileSetId)
225 CREATE TABLE JobMedia (
226 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
227 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
228 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
229 FirstIndex INTEGER UNSIGNED DEFAULT 0,
230 LastIndex INTEGER UNSIGNED DEFAULT 0,
231 StartFile INTEGER UNSIGNED DEFAULT 0,
232 EndFile INTEGER UNSIGNED DEFAULT 0,
233 StartBlock INTEGER UNSIGNED DEFAULT 0,
234 EndBlock INTEGER UNSIGNED DEFAULT 0,
235 VolIndex INTEGER UNSIGNED DEFAULT 0,
236 PRIMARY KEY(JobMediaId),
237 INDEX (JobId, MediaId)
242 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
243 VolumeName TINYBLOB NOT NULL,
244 Slot INTEGER DEFAULT 0,
245 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
246 MediaType TINYBLOB NOT NULL,
247 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
248 LabelType TINYINT DEFAULT 0,
249 FirstWritten DATETIME,
250 LastWritten DATETIME,
252 VolJobs INTEGER UNSIGNED DEFAULT 0,
253 VolFiles INTEGER UNSIGNED DEFAULT 0,
254 VolBlocks INTEGER UNSIGNED DEFAULT 0,
255 VolMounts INTEGER UNSIGNED DEFAULT 0,
256 VolBytes BIGINT UNSIGNED DEFAULT 0,
257 VolABytes BIGINT UNSIGNED DEFAULT 0,
258 VolAPadding BIGINT UNSIGNED DEFAULT 0,
259 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
260 VolHoles INTEGER UNSIGNED DEFAULT 0,
261 VolParts INTEGER UNSIGNED DEFAULT 0, /* Now used for VolType */
262 VolErrors INTEGER UNSIGNED DEFAULT 0,
263 VolWrites BIGINT UNSIGNED DEFAULT 0,
264 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
265 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
266 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
267 Enabled TINYINT DEFAULT 1,
268 Recycle TINYINT DEFAULT 0,
269 ActionOnPurge TINYINT DEFAULT 0,
270 VolRetention BIGINT UNSIGNED DEFAULT 0,
271 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
272 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
273 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
274 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
275 InChanger TINYINT DEFAULT 0,
276 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
277 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
278 MediaAddressing TINYINT DEFAULT 0,
279 VolReadTime BIGINT UNSIGNED DEFAULT 0,
280 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
281 EndFile INTEGER UNSIGNED DEFAULT 0,
282 EndBlock INTEGER UNSIGNED DEFAULT 0,
283 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
284 RecycleCount INTEGER UNSIGNED DEFAULT 0,
285 InitialWrite DATETIME,
286 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
287 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
289 PRIMARY KEY(MediaId),
290 UNIQUE (VolumeName(128)),
296 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
297 Name TINYBLOB NOT NULL,
298 NumVols INTEGER UNSIGNED DEFAULT 0,
299 MaxVols INTEGER UNSIGNED DEFAULT 0,
300 UseOnce TINYINT DEFAULT 0,
301 UseCatalog TINYINT DEFAULT 0,
302 AcceptAnyVolume TINYINT DEFAULT 0,
303 VolRetention BIGINT UNSIGNED DEFAULT 0,
304 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
305 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
306 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
307 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
308 AutoPrune TINYINT DEFAULT 0,
309 Recycle TINYINT DEFAULT 0,
310 ActionOnPurge TINYINT DEFAULT 0,
311 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
312 LabelType TINYINT DEFAULT 0,
313 LabelFormat TINYBLOB,
314 Enabled TINYINT DEFAULT 1,
315 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
316 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
317 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
318 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
319 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
320 MigrationTime BIGINT UNSIGNED DEFAULT 0,
326 CREATE TABLE Client (
327 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
328 Name TINYBLOB NOT NULL,
329 Uname TINYBLOB NOT NULL, /* full uname -a of client */
330 AutoPrune TINYINT DEFAULT 0,
331 FileRetention BIGINT UNSIGNED DEFAULT 0,
332 JobRetention BIGINT UNSIGNED DEFAULT 0,
334 PRIMARY KEY(ClientId)
338 LogId INTEGER UNSIGNED AUTO_INCREMENT,
339 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
341 LogText BLOB NOT NULL,
347 CREATE TABLE BaseFiles (
348 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
349 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
350 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
351 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
352 FileIndex INTEGER UNSIGNED,
356 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
358 CREATE TABLE UnsavedFiles (
359 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
360 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
361 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
362 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
363 PRIMARY KEY (UnsavedId)
368 CREATE TABLE Counters (
369 Counter TINYBLOB NOT NULL,
370 \`MinValue\` INTEGER DEFAULT 0,
371 \`MaxValue\` INTEGER DEFAULT 0,
372 CurrentValue INTEGER DEFAULT 0,
373 WrapCounter TINYBLOB NOT NULL,
374 PRIMARY KEY (Counter(128))
377 CREATE TABLE CDImages (
378 MediaId INTEGER UNSIGNED NOT NULL,
380 PRIMARY KEY (MediaId)
383 CREATE TABLE Status (
384 JobStatus CHAR(1) BINARY NOT NULL,
387 PRIMARY KEY (JobStatus)
390 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
391 ('C', 'Created, not yet running',15),
394 ('T', 'Completed successfully',10),
395 ('E', 'Terminated with errors',25),
396 ('e', 'Non-fatal error',20),
397 ('f', 'Fatal error',100),
398 ('D', 'Verify found differences',15),
399 ('A', 'Canceled by user',90),
400 ('F', 'Waiting for Client',15),
401 ('S', 'Waiting for Storage daemon',15),
402 ('m', 'Waiting for new media',15),
403 ('M', 'Waiting for media mount',15),
404 ('s', 'Waiting for storage resource',15),
405 ('j', 'Waiting for job resource',15),
406 ('c', 'Waiting for client resource',15),
407 ('d', 'Waiting on maximum jobs',15),
408 ('t', 'Waiting on start time',15),
409 ('p', 'Waiting on higher priority jobs',15),
410 ('i', 'Doing batch insert file records',15),
411 ('I', 'Incomplete Job',25),
412 ('a', 'SD despooling attributes',15);
414 CREATE TABLE PathHierarchy
416 PathId integer NOT NULL,
417 PPathId integer NOT NULL,
418 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
421 CREATE INDEX pathhierarchy_ppathid
422 ON PathHierarchy (PPathId);
424 CREATE TABLE PathVisibility
426 PathId integer NOT NULL,
427 JobId integer UNSIGNED NOT NULL,
429 Files int4 DEFAULT 0,
430 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
432 CREATE INDEX pathvisibility_jobid
433 ON PathVisibility (JobId);
436 CREATE TABLE Snapshot (
437 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
438 Name TINYBLOB NOT NULL,
439 JobId INTEGER UNSIGNED DEFAULT 0,
440 FileSetId INTEGER UNSIGNED DEFAULT 0,
441 CreateTDate BIGINT NOT NULL,
442 CreateDate DATETIME NOT NULL,
443 ClientId INTEGER UNSIGNED DEFAULT 0,
444 Volume TINYBLOB NOT NULL,
445 Device TINYBLOB NOT NULL,
446 Type TINYBLOB NOT NULL,
447 Retention INTEGER DEFAULT 0,
449 primary key (SnapshotId)
452 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255),
458 CREATE TABLE Version (
459 VersionId INTEGER UNSIGNED NOT NULL
462 -- Initialize Version
463 INSERT INTO Version (VersionId) VALUES (15);
467 echo "Creation of Bacula MySQL tables succeeded."
469 echo "Creation of Bacula MySQL tables failed."