3 # shell script to create Bacula MySQL tables
5 # Copyright (C) 2000-2016 Kern Sibbald
6 # License: BSD 2-Clause; see file LICENSE-FOSS
9 # We cannot provide support for performance issues
10 # if you changed the default schema. In partcular
11 # if you change any of the indexes.
16 # show tables from <database>;
17 # show columns from <table> from <database>;
20 # select user from user;
24 db_name=${db_name:-@db_name@}
26 if mysql $* -f <<END-OF-DATA
29 -- Note, we use BLOB rather than TEXT because in MySQL,
30 -- BLOBs are identical to TEXT except that BLOB is case
31 -- sensitive in sorts, which is what we want, and TEXT
32 -- is case insensitive.
34 CREATE TABLE Filename (
35 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
37 PRIMARY KEY(FilenameId),
42 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
48 -- We strongly recommend to avoid the temptation to add new indexes.
49 -- In general, these will cause very significant performance
50 -- problems in other areas. A better approch is to carefully check
51 -- that all your memory configuation parameters are
52 -- suitable for the size of your installation. If you backup
53 -- millions of files, you need to adapt the database memory
54 -- configuration parameters concerning sorting, joining and global
55 -- memory. By default, sort and join parameters are very small
56 -- (sometimes 8Kb), and having sufficient memory specified by those
57 -- parameters is extremely important to run fast.
60 -- FileIndex can be 0 for FT_DELETED files
61 -- FileNameId can link to Filename.Name='' for directories
63 FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
64 FileIndex INTEGER UNSIGNED DEFAULT 0,
65 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
66 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
67 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
68 DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
69 MarkId INTEGER UNSIGNED DEFAULT 0,
70 LStat TINYBLOB NOT NULL,
74 INDEX (JobId, PathId, FilenameId)
77 CREATE TABLE RestoreObject (
78 RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
79 ObjectName BLOB NOT NULL,
80 RestoreObject LONGBLOB NOT NULL,
81 PluginName TINYBLOB NOT NULL,
82 ObjectLength INTEGER DEFAULT 0,
83 ObjectFullLength INTEGER DEFAULT 0,
84 ObjectIndex INTEGER DEFAULT 0,
85 ObjectType INTEGER DEFAULT 0,
86 FileIndex INTEGER UNSIGNED DEFAULT 0,
87 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
88 ObjectCompression INTEGER DEFAULT 0,
89 PRIMARY KEY(RestoreObjectId),
95 # Possibly add one or more of the following indexes
96 # to the above File table if your Verifies are
97 # too slow, but they can slow down backups.
100 # INDEX (FilenameId),
103 CREATE TABLE MediaType (
104 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
105 MediaType TINYBLOB NOT NULL,
106 ReadOnly TINYINT DEFAULT 0,
107 PRIMARY KEY(MediaTypeId)
110 CREATE TABLE Storage (
111 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
112 Name TINYBLOB NOT NULL,
113 AutoChanger TINYINT DEFAULT 0,
114 PRIMARY KEY(StorageId)
117 CREATE TABLE Device (
118 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
119 Name TINYBLOB NOT NULL,
120 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
121 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
122 DevMounts INTEGER UNSIGNED DEFAULT 0,
123 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
124 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
125 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
126 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
127 DevReadTime BIGINT UNSIGNED DEFAULT 0,
128 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
129 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
130 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
131 CleaningDate DATETIME,
132 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
133 PRIMARY KEY(DeviceId)
138 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
139 Job TINYBLOB NOT NULL,
140 Name TINYBLOB NOT NULL,
141 Type BINARY(1) NOT NULL,
142 Level BINARY(1) NOT NULL,
143 ClientId INTEGER DEFAULT 0 REFERENCES Client,
144 JobStatus BINARY(1) NOT NULL,
148 RealEndTime DATETIME,
149 JobTDate BIGINT UNSIGNED DEFAULT 0,
150 VolSessionId INTEGER UNSIGNED DEFAULT 0,
151 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
152 JobFiles INTEGER UNSIGNED DEFAULT 0,
153 JobBytes BIGINT UNSIGNED DEFAULT 0,
154 ReadBytes BIGINT UNSIGNED DEFAULT 0,
155 JobErrors INTEGER UNSIGNED DEFAULT 0,
156 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
157 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
158 FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet,
159 PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
160 PurgedFiles TINYINT DEFAULT 0,
161 HasBase TINYINT DEFAULT 0,
162 HasCache TINYINT DEFAULT 0,
163 Reviewed TINYINT DEFAULT 0,
165 FileTable CHAR(20) DEFAULT 'File',
170 -- Create a table like Job for long term statistics
171 CREATE TABLE JobHisto (
172 JobId INTEGER UNSIGNED NOT NULL,
173 Job TINYBLOB NOT NULL,
174 Name TINYBLOB NOT NULL,
175 Type BINARY(1) NOT NULL,
176 Level BINARY(1) NOT NULL,
177 ClientId INTEGER DEFAULT 0,
178 JobStatus BINARY(1) NOT NULL,
182 RealEndTime DATETIME,
183 JobTDate BIGINT UNSIGNED DEFAULT 0,
184 VolSessionId INTEGER UNSIGNED DEFAULT 0,
185 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
186 JobFiles INTEGER UNSIGNED DEFAULT 0,
187 JobBytes BIGINT UNSIGNED DEFAULT 0,
188 ReadBytes BIGINT UNSIGNED DEFAULT 0,
189 JobErrors INTEGER UNSIGNED DEFAULT 0,
190 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
191 PoolId INTEGER UNSIGNED DEFAULT 0,
192 FileSetId INTEGER UNSIGNED DEFAULT 0,
193 PriorJobId INTEGER UNSIGNED DEFAULT 0,
194 PurgedFiles TINYINT DEFAULT 0,
195 HasBase TINYINT DEFAULT 0,
196 HasCache TINYINT DEFAULT 0,
197 Reviewed TINYINT DEFAULT 0,
199 FileTable CHAR(20) DEFAULT 'File',
205 CREATE TABLE Location (
206 LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
207 Location TINYBLOB NOT NULL,
208 Cost INTEGER DEFAULT 0,
210 PRIMARY KEY(LocationId)
213 CREATE TABLE LocationLog (
214 LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
216 Comment BLOB NOT NULL,
217 MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
218 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
219 NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
220 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
222 PRIMARY KEY(LocLogId)
227 CREATE TABLE FileSet (
228 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
229 FileSet TINYBLOB NOT NULL,
232 PRIMARY KEY(FileSetId)
235 CREATE TABLE JobMedia (
236 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
237 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
238 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
239 FirstIndex INTEGER UNSIGNED DEFAULT 0,
240 LastIndex INTEGER UNSIGNED DEFAULT 0,
241 StartFile INTEGER UNSIGNED DEFAULT 0,
242 EndFile INTEGER UNSIGNED DEFAULT 0,
243 StartBlock INTEGER UNSIGNED DEFAULT 0,
244 EndBlock INTEGER UNSIGNED DEFAULT 0,
245 VolIndex INTEGER UNSIGNED DEFAULT 0,
246 PRIMARY KEY(JobMediaId),
247 INDEX (JobId, MediaId)
252 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
253 VolumeName TINYBLOB NOT NULL,
254 Slot INTEGER DEFAULT 0,
255 PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
256 MediaType TINYBLOB NOT NULL,
257 MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType,
258 LabelType TINYINT DEFAULT 0,
259 FirstWritten DATETIME,
260 LastWritten DATETIME,
262 VolJobs INTEGER UNSIGNED DEFAULT 0,
263 VolFiles INTEGER UNSIGNED DEFAULT 0,
264 VolBlocks INTEGER UNSIGNED DEFAULT 0,
265 VolMounts INTEGER UNSIGNED DEFAULT 0,
266 VolBytes BIGINT UNSIGNED DEFAULT 0,
267 VolABytes BIGINT UNSIGNED DEFAULT 0,
268 VolAPadding BIGINT UNSIGNED DEFAULT 0,
269 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
270 VolHoles INTEGER UNSIGNED DEFAULT 0,
271 VolParts INTEGER UNSIGNED DEFAULT 0, /* Now used for VolType */
272 VolErrors INTEGER UNSIGNED DEFAULT 0,
273 VolWrites BIGINT UNSIGNED DEFAULT 0,
274 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
275 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
276 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
277 Enabled TINYINT DEFAULT 1,
278 Recycle TINYINT DEFAULT 0,
279 ActionOnPurge TINYINT DEFAULT 0,
280 VolRetention BIGINT UNSIGNED DEFAULT 0,
281 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
282 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
283 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
284 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
285 InChanger TINYINT DEFAULT 0,
286 StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage,
287 DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device,
288 MediaAddressing TINYINT DEFAULT 0,
289 VolReadTime BIGINT UNSIGNED DEFAULT 0,
290 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
291 EndFile INTEGER UNSIGNED DEFAULT 0,
292 EndBlock INTEGER UNSIGNED DEFAULT 0,
293 LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
294 RecycleCount INTEGER UNSIGNED DEFAULT 0,
295 InitialWrite DATETIME,
296 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
297 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
299 PRIMARY KEY(MediaId),
300 UNIQUE (VolumeName(128)),
306 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
307 Name TINYBLOB NOT NULL,
308 NumVols INTEGER UNSIGNED DEFAULT 0,
309 MaxVols INTEGER UNSIGNED DEFAULT 0,
310 UseOnce TINYINT DEFAULT 0,
311 UseCatalog TINYINT DEFAULT 0,
312 AcceptAnyVolume TINYINT DEFAULT 0,
313 VolRetention BIGINT UNSIGNED DEFAULT 0,
314 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
315 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
316 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
317 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
318 AutoPrune TINYINT DEFAULT 0,
319 Recycle TINYINT DEFAULT 0,
320 ActionOnPurge TINYINT DEFAULT 0,
321 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
322 LabelType TINYINT DEFAULT 0,
323 LabelFormat TINYBLOB,
324 Enabled TINYINT DEFAULT 1,
325 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
326 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
327 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
328 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
329 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
330 MigrationTime BIGINT UNSIGNED DEFAULT 0,
336 CREATE TABLE Client (
337 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
338 Name TINYBLOB NOT NULL,
339 Uname TINYBLOB NOT NULL, /* full uname -a of client */
340 AutoPrune TINYINT DEFAULT 0,
341 FileRetention BIGINT UNSIGNED DEFAULT 0,
342 JobRetention BIGINT UNSIGNED DEFAULT 0,
344 PRIMARY KEY(ClientId)
348 LogId INTEGER UNSIGNED AUTO_INCREMENT,
349 JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
351 LogText BLOB NOT NULL,
357 CREATE TABLE BaseFiles (
358 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
359 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
360 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
361 FileId BIGINT UNSIGNED NOT NULL REFERENCES File,
362 FileIndex INTEGER UNSIGNED,
366 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
368 CREATE TABLE UnsavedFiles (
369 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
370 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
371 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
372 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
373 PRIMARY KEY (UnsavedId)
378 CREATE TABLE Counters (
379 Counter TINYBLOB NOT NULL,
380 \`MinValue\` INTEGER DEFAULT 0,
381 \`MaxValue\` INTEGER DEFAULT 0,
382 CurrentValue INTEGER DEFAULT 0,
383 WrapCounter TINYBLOB NOT NULL,
384 PRIMARY KEY (Counter(128))
387 CREATE TABLE CDImages (
388 MediaId INTEGER UNSIGNED NOT NULL,
390 PRIMARY KEY (MediaId)
393 CREATE TABLE Status (
394 JobStatus CHAR(1) BINARY NOT NULL,
397 PRIMARY KEY (JobStatus)
400 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
401 ('C', 'Created, not yet running',15),
404 ('T', 'Completed successfully',10),
405 ('E', 'Terminated with errors',25),
406 ('e', 'Non-fatal error',20),
407 ('f', 'Fatal error',100),
408 ('D', 'Verify found differences',15),
409 ('A', 'Canceled by user',90),
410 ('F', 'Waiting for Client',15),
411 ('S', 'Waiting for Storage daemon',15),
412 ('m', 'Waiting for new media',15),
413 ('M', 'Waiting for media mount',15),
414 ('s', 'Waiting for storage resource',15),
415 ('j', 'Waiting for job resource',15),
416 ('c', 'Waiting for client resource',15),
417 ('d', 'Waiting on maximum jobs',15),
418 ('t', 'Waiting on start time',15),
419 ('p', 'Waiting on higher priority jobs',15),
420 ('i', 'Doing batch insert file records',15),
421 ('I', 'Incomplete Job',25),
422 ('a', 'SD despooling attributes',15);
424 CREATE TABLE PathHierarchy
426 PathId integer NOT NULL,
427 PPathId integer NOT NULL,
428 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
431 CREATE INDEX pathhierarchy_ppathid
432 ON PathHierarchy (PPathId);
434 CREATE TABLE PathVisibility
436 PathId integer NOT NULL,
437 JobId integer UNSIGNED NOT NULL,
439 Files int4 DEFAULT 0,
440 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
442 CREATE INDEX pathvisibility_jobid
443 ON PathVisibility (JobId);
446 CREATE TABLE Snapshot (
447 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
448 Name TINYBLOB NOT NULL,
449 JobId INTEGER UNSIGNED DEFAULT 0,
450 FileSetId INTEGER UNSIGNED DEFAULT 0,
451 CreateTDate BIGINT NOT NULL,
452 CreateDate DATETIME NOT NULL,
453 ClientId INTEGER UNSIGNED DEFAULT 0,
454 Volume TINYBLOB NOT NULL,
455 Device TINYBLOB NOT NULL,
456 Type TINYBLOB NOT NULL,
457 Retention INTEGER DEFAULT 0,
459 primary key (SnapshotId)
462 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255),
468 CREATE TABLE Version (
469 VersionId INTEGER UNSIGNED NOT NULL
472 -- Initialize Version
473 INSERT INTO Version (VersionId) VALUES (15);
477 echo "Creation of Bacula MySQL tables succeeded."
479 echo "Creation of Bacula MySQL tables failed."