#
# shell script to create Bacula MySQL tables
#
-bindir=@SQL_BINDIR@
+# Copyright (C) 2000-2015 Kern Sibbald
+# License: BSD 2-Clause; see file LICENSE-FOSS
+#
+# Important note:
+# You won't get any support for performance issue if you changed the default
+# schema.
+#
+bindir=@MYSQL_BINDIR@
+PATH="$bindir:$PATH"
db_name=${db_name:-@db_name@}
-if $bindir/mysql $* -f <<END-OF-DATA
+if mysql $* -f <<END-OF-DATA
USE ${db_name};
--
-- Note, we use BLOB rather than TEXT because in MySQL,
INDEX (Path(255))
);
+-- We strongly recommend to avoid the temptation to add new indexes.
+-- In general, these will cause very significant performance
+-- problems in other areas. A better approch is to carefully check
+-- that all your memory configuation parameters are
+-- suitable for the size of your installation. If you backup
+-- millions of files, you need to adapt the database memory
+-- configuration parameters concerning sorting, joining and global
+-- memory. By default, sort and join parameters are very small
+-- (sometimes 8Kb), and having sufficient memory specified by those
+-- parameters is extremely important to run fast.
+
-- In File table
-- FileIndex can be 0 for FT_DELETED files
-- FileNameId can link to Filename.Name='' for directories
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
+ DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
MarkId INTEGER UNSIGNED DEFAULT 0,
LStat TINYBLOB NOT NULL,
MD5 TINYBLOB,
INDEX (JobId, PathId, FilenameId)
);
+CREATE TABLE RestoreObject (
+ RestoreObjectId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ ObjectName BLOB NOT NULL,
+ RestoreObject LONGBLOB NOT NULL,
+ PluginName TINYBLOB NOT NULL,
+ ObjectLength INTEGER DEFAULT 0,
+ ObjectFullLength INTEGER DEFAULT 0,
+ ObjectIndex INTEGER DEFAULT 0,
+ ObjectType INTEGER DEFAULT 0,
+ FileIndex INTEGER UNSIGNED DEFAULT 0,
+ JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
+ ObjectCompression INTEGER DEFAULT 0,
+ PRIMARY KEY(RestoreObjectId),
+ INDEX (JobId)
+ );
+
+
#
# Possibly add one or more of the following indexes
# to the above File table if your Verifies are
-# too slow.
+# too slow, but they can slow down backups.
#
# INDEX (PathId),
# INDEX (FilenameId),
-# INDEX (FilenameId, PathId)
-# INDEX (JobId),
#
CREATE TABLE MediaType (
PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
PurgedFiles TINYINT DEFAULT 0,
HasBase TINYINT DEFAULT 0,
+ HasCache TINYINT DEFAULT 0,
+ Reviewed TINYINT DEFAULT 0,
+ Comment BLOB,
+ FileTable CHAR(20) DEFAULT 'File',
PRIMARY KEY(JobId),
INDEX (Name(128))
);
PriorJobId INTEGER UNSIGNED DEFAULT 0,
PurgedFiles TINYINT DEFAULT 0,
HasBase TINYINT DEFAULT 0,
- INDEX (StartTime)
+ HasCache TINYINT DEFAULT 0,
+ Reviewed TINYINT DEFAULT 0,
+ Comment BLOB,
+ FileTable CHAR(20) DEFAULT 'File',
+ INDEX (JobId),
+ INDEX (StartTime),
+ INDEX (JobTDate)
);
CREATE TABLE Location (
StartBlock INTEGER UNSIGNED DEFAULT 0,
EndBlock INTEGER UNSIGNED DEFAULT 0,
VolIndex INTEGER UNSIGNED DEFAULT 0,
- Copy INTEGER UNSIGNED DEFAULT 0,
- Stripe INTEGER UNSIGNED DEFAULT 0,
PRIMARY KEY(JobMediaId),
INDEX (JobId, MediaId)
);
VolBlocks INTEGER UNSIGNED DEFAULT 0,
VolMounts INTEGER UNSIGNED DEFAULT 0,
VolBytes BIGINT UNSIGNED DEFAULT 0,
- VolParts INTEGER UNSIGNED DEFAULT 0,
+ VolABytes BIGINT UNSIGNED DEFAULT 0,
+ VolAPadding BIGINT UNSIGNED DEFAULT 0,
+ VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
+ VolHoles INTEGER UNSIGNED DEFAULT 0,
+ VolParts INTEGER UNSIGNED DEFAULT 0, /* Now used for VolType */
VolErrors INTEGER UNSIGNED DEFAULT 0,
- VolWrites INTEGER UNSIGNED DEFAULT 0,
+ VolWrites BIGINT UNSIGNED DEFAULT 0,
VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
Enabled TINYINT DEFAULT 1,
Recycle TINYINT DEFAULT 0,
- ActionOnPurge TINYINT DEFAULT 0,
+ ActionOnPurge TINYINT DEFAULT 0,
VolRetention BIGINT UNSIGNED DEFAULT 0,
VolUseDuration BIGINT UNSIGNED DEFAULT 0,
MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
Comment BLOB,
PRIMARY KEY(MediaId),
UNIQUE (VolumeName(128)),
- INDEX (PoolId)
+ INDEX (PoolId),
+ INDEX (StorageId)
);
CREATE TABLE Pool (
MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
AutoPrune TINYINT DEFAULT 0,
Recycle TINYINT DEFAULT 0,
- ActionOnPurge TINYINT DEFAULT 0,
+ ActionOnPurge TINYINT DEFAULT 0,
PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
LabelType TINYINT DEFAULT 0,
LabelFormat TINYBLOB,
PRIMARY KEY(BaseId)
);
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+
CREATE TABLE UnsavedFiles (
UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
CREATE TABLE Counters (
Counter TINYBLOB NOT NULL,
- MinValue INTEGER DEFAULT 0,
- MaxValue INTEGER DEFAULT 0,
+ \`MinValue\` INTEGER DEFAULT 0,
+ \`MaxValue\` INTEGER DEFAULT 0,
CurrentValue INTEGER DEFAULT 0,
WrapCounter TINYBLOB NOT NULL,
PRIMARY KEY (Counter(128))
CREATE TABLE Status (
JobStatus CHAR(1) BINARY NOT NULL,
- JobStatusLong BLOB,
+ JobStatusLong BLOB,
+ Severity INT,
PRIMARY KEY (JobStatus)
);
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
- ('C', 'Created, not yet running'),
- ('R', 'Running'),
- ('B', 'Blocked'),
- ('T', 'Completed successfully'),
- ('E', 'Terminated with errors'),
- ('e', 'Non-fatal error'),
- ('f', 'Fatal error'),
- ('D', 'Verify found differences'),
- ('A', 'Canceled by user'),
- ('F', 'Waiting for Client'),
- ('S', 'Waiting for Storage daemon'),
- ('m', 'Waiting for new media'),
- ('M', 'Waiting for media mount'),
- ('s', 'Waiting for storage resource'),
- ('j', 'Waiting for job resource'),
- ('c', 'Waiting for client resource'),
- ('d', 'Waiting on maximum jobs'),
- ('t', 'Waiting on start time'),
- ('p', 'Waiting on higher priority jobs'),
- ('i', 'Doing batch insert file records'),
- ('a', 'SD despooling attributes');
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+ ('C', 'Created, not yet running',15),
+ ('R', 'Running',15),
+ ('B', 'Blocked',15),
+ ('T', 'Completed successfully',10),
+ ('E', 'Terminated with errors',25),
+ ('e', 'Non-fatal error',20),
+ ('f', 'Fatal error',100),
+ ('D', 'Verify found differences',15),
+ ('A', 'Canceled by user',90),
+ ('F', 'Waiting for Client',15),
+ ('S', 'Waiting for Storage daemon',15),
+ ('m', 'Waiting for new media',15),
+ ('M', 'Waiting for media mount',15),
+ ('s', 'Waiting for storage resource',15),
+ ('j', 'Waiting for job resource',15),
+ ('c', 'Waiting for client resource',15),
+ ('d', 'Waiting on maximum jobs',15),
+ ('t', 'Waiting on start time',15),
+ ('p', 'Waiting on higher priority jobs',15),
+ ('i', 'Doing batch insert file records',15),
+ ('I', 'Incomplete Job',25),
+ ('a', 'SD despooling attributes',15);
+
+CREATE TABLE PathHierarchy
+(
+ PathId integer NOT NULL,
+ PPathId integer NOT NULL,
+ CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
+);
+
+CREATE INDEX pathhierarchy_ppathid
+ ON PathHierarchy (PPathId);
+
+CREATE TABLE PathVisibility
+(
+ PathId integer NOT NULL,
+ JobId integer UNSIGNED NOT NULL,
+ Size int8 DEFAULT 0,
+ Files int4 DEFAULT 0,
+ CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
+);
+CREATE INDEX pathvisibility_jobid
+ ON PathVisibility (JobId);
+
+
+CREATE TABLE Snapshot (
+ SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
+ Name TINYBLOB NOT NULL,
+ JobId INTEGER UNSIGNED DEFAULT 0,
+ FileSetId INTEGER UNSIGNED DEFAULT 0,
+ CreateTDate BIGINT NOT NULL,
+ CreateDate DATETIME NOT NULL,
+ ClientId INTEGER UNSIGNED DEFAULT 0,
+ Volume TINYBLOB NOT NULL,
+ Device TINYBLOB NOT NULL,
+ Type TINYBLOB NOT NULL,
+ Retention INTEGER DEFAULT 0,
+ Comment BLOB,
+ primary key (SnapshotId)
+);
+
+CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255),
+ Volume(255),
+ Name(255));
+
+
CREATE TABLE Version (
VersionId INTEGER UNSIGNED NOT NULL
);
-- Initialize Version
-INSERT INTO Version (VersionId) VALUES (11);
+INSERT INTO Version (VersionId) VALUES (15);
END-OF-DATA
then
projects / bacula / bacula / blobdiff