CREATE TABLE IF NOT EXISTS `stage_backup` (
`Guid` char(255) NOT NULL DEFAULT '',
`Package` char(255) DEFAULT NULL,
`Strategy_Id` char(255) NOT NULL,
`Status` char(50) DEFAULT NULL,
`Backupname` char(255) DEFAULT NULL,
`Remoteconnection` char(50) DEFAULT NULL,
`TargetDatabase` text DEFAULT NULL,
`Host` text DEFAULT NULL,
`Filename` text DEFAULT NULL,
`Filesize` text DEFAULT NULL,
`Campaign_id` int(11) DEFAULT NULL,
`Result1` text DEFAULT NULL,
`Result2` text DEFAULT NULL,
`Result3` text DEFAULT NULL,
`Param1` text DEFAULT NULL,
`Param2` text DEFAULT NULL,
`Param3` text DEFAULT NULL,
`Param4` text DEFAULT NULL,
`Param5` text DEFAULT NULL,
`Param6` text DEFAULT NULL,
`Param7` text DEFAULT NULL,
`Param8` text DEFAULT NULL,
`Param9` text DEFAULT NULL,
`Step_id` int(11) NOT NULL DEFAULT 0,
`RDG` int(11) DEFAULT NULL,
`Thread` int(11) DEFAULT 0,
`WorkerPid` int(11) NOT NULL DEFAULT 0,
`DispatcherName` char(255) DEFAULT NULL,
`DispatcherPid` int(11) NOT NULL DEFAULT 0,
`ReturnPid` int(11) NOT NULL DEFAULT 0,
`Instance` char(255) DEFAULT NULL,
`CPULoad` int(11) DEFAULT 0,
`MemoryLoad` int(11) DEFAULT 0,
`DiskLoad` int(11) DEFAULT 0,
`TimeScheduled` datetime DEFAULT NULL,
`TimeAdded` timestamp NULL DEFAULT current_timestamp(),
`TimeThreaded` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`TimeRun` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
`Checkoutname` char(255) DEFAULT NULL,
PRIMARY KEY (`Guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `data_backup_log` (
`LogId` BIGINT NOT NULL AUTO_INCREMENT,
`Package` VARCHAR(255) NOT NULL,
`SourceName` VARCHAR(255) NOT NULL,
`BackupType` VARCHAR(50) NOT NULL,
`DatabaseName` VARCHAR(255) NOT NULL,
`Status` VARCHAR(50) NOT NULL DEFAULT 'OK',
`SizeBytes` BIGINT NOT NULL DEFAULT 0,
`FilePath` VARCHAR(1024) DEFAULT NULL,
`DurationSeconds` INT DEFAULT NULL,
`ErrorMessage` TEXT DEFAULT NULL,
`BackupDate` DATE NOT NULL,
`StartTime` DATETIME NOT NULL,
`EndTime` DATETIME DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`LogId`),
UNIQUE KEY `idx_daily_dedup`
(`Package`, `SourceName`,
`DatabaseName`, `BackupDate`),
KEY `idx_log_date` (`BackupDate`),
KEY `idx_log_source` (`SourceName`,
`BackupDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_backup` (
`BackupName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`BackupType` VARCHAR(50) NOT NULL DEFAULT 'MARIADB',
`Remoteconnection` VARCHAR(255) DEFAULT NULL,
`DatabaseMask` VARCHAR(255) DEFAULT NULL,
`TableMask` VARCHAR(255) DEFAULT '*',
`Active` CHAR(1) NOT NULL DEFAULT 'Y',
`Archived` CHAR(1) NOT NULL DEFAULT 'N',
`BackupTime` TIME NOT NULL DEFAULT '20:00:00',
`BackupFreq` INT NOT NULL DEFAULT 1,
`BackupFolder` VARCHAR(255) DEFAULT NULL,
`BackDescription` VARCHAR(255) DEFAULT '',
`FileMask` VARCHAR(255) DEFAULT '*.*',
`FileName` VARCHAR(255) DEFAULT NULL,
`FileSize` VARCHAR(255) DEFAULT NULL,
`SetPreSql` TEXT DEFAULT NULL,
`SourceFolder` INT DEFAULT 30,
`HistoryWindow` INT DEFAULT 30,
`ExcludeTimeStart` TIME DEFAULT NULL,
`ExcludeTimeEnd` TIME DEFAULT NULL,
`LastBackup` DATE DEFAULT NULL,
`LastCheck` DATE DEFAULT NULL,
`LastTrigger` DATETIME DEFAULT NULL,
`Status` VARCHAR(255) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`NotifyEmail` VARCHAR(500) DEFAULT NULL,
PRIMARY KEY (`BackupName`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT * from def_Backup
WHERE
BackupName = '{backup_code}'
AND Package = '{package}'
SELECT DATEDIFF(NOW(),TimeCompleted) AS Lag FROM stage_backup
WHERE
remoteconnection = '{remote_connection}'
AND STATUS = 'DONE' AND Result1 > 0
AND DATEDIFF(NOW(),TimeCompleted) > 0
AND Package = '{package}'
ORDER BY TimeCompleted desc
UPDATE def_Backup set LastCheck = now(),
Filename = '{zip_file}', Filesize = '{filesize}'
WHERE
DatabaseMask = '{database_mask}'
AND remoteconnection = '{remote_connection}'
SELECT count(*) from def_Backup where DatabaseMask = '{database_mask}'
and Remoteconnection = '{remote_connection}'
and Archived = 'Y'
SHOW TABLES
SELECT data_length + index_length
FROM information_schema.TABLES
WHERE table_schema = '{database_name}' AND table_name = '{table_name}'
UPDATE stage_backup
SET
Status = '{status}', TimeRun = now(), TimeCompleted = now(),
Result1 = '{result1}', Result2 = '{result2}'
WHERE
(Guid='{guid}' OR TargetDatabase = '{target_database}')
SELECT SUM(data_length + index_length)
FROM information_schema.TABLES
WHERE table_schema = '{database_name}'
DROP DATABASE `{database_name}`
SELECT COUNT(*) FROM stage_backup
WHERE remoteconnection = '{remote_connection}'
AND TargetDatabase = '{database}'
AND (DATEDIFF(NOW(),TimeCompleted) < 2 OR COALESCE(STATUS,'') = '')
INSERT IGNORE into Stage_Backup
(remoteconnection, package, BackupName, Guid, TargetDatabase, Host, TimeScheduled, RDG)
VALUES
( '{remote_connection}','{package}','{backup_name}','{guid}','{database}','{host}',
concat(date(now()),' ','{backup_time}'),
'{rdg}'
)
SELECT remoteconnection, Databasemask,
backupname,
COALESCE(BackupType, '') AS BackupType
FROM def_Backup
WHERE Backupname LIKE '{backup_name}'
AND Package = '{package}'
AND COALESCE(Active, 'Y') = 'Y'
SELECT @@hostname
SHOW DATABASES
INSERT ignore into def_Backup (BackupName,remoteconnection,
Package,BackupType,Active,DatabaseMask)
VALUES
('{backup_name}','{remote_connection}',
'{package}','RECORD','Y','{database_mask}')
SELECT guid, backupname, remoteconnection, targetdatabase,
coalesce(param1,'') As Param1, coalesce(param2,'') As Param2, coalesce(param3,'') As Param3,
coalesce(param4,'') As Param4, coalesce(param5,'') As Param5, coalesce(param6,'') As Param6,
coalesce(param7,'') As Param7, coalesce(param8,'') As Param8, coalesce(param9,'') As Param9
FROM STAGE_BACKUP
WHERE
COALESCE(Status, '') = ''
AND coalesce(Package) in ('','{package}')
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
FROM information_schema.TABLES
WHERE table_schema = '{db}'
GROUP BY table_schema
SELECT LogId, SizeBytes, Status
FROM data_backup_log
WHERE Package = '{package}'
AND SourceName = '{source_name}'
AND DatabaseName = '{database_name}'
AND BackupDate = CURDATE()
LIMIT 1
INSERT INTO data_backup_log (
Package, SourceName, BackupType,
DatabaseName, Status, SizeBytes,
FilePath, DurationSeconds,
ErrorMessage, BackupDate,
StartTime, EndTime, Module
) VALUES (
'{package}', '{source_name}',
'{backup_type}', '{database_name}',
'{status}', {size_bytes},
'{file_path}', {duration_seconds},
{error_message}, CURDATE(),
'{start_time}', '{end_time}',
'ObjBackup'
)
ON DUPLICATE KEY UPDATE
Status = '{status}',
SizeBytes = {size_bytes},
FilePath = '{file_path}',
DurationSeconds = {duration_seconds},
ErrorMessage = {error_message},
EndTime = '{end_time}'
SELECT SizeBytes, BackupDate, Status
FROM data_backup_log
WHERE Package = '{package}'
AND SourceName = '{source_name}'
AND DatabaseName = '{database_name}'
AND BackupDate < CURDATE()
ORDER BY BackupDate DESC
LIMIT 1
SELECT SourceName, BackupType,
DatabaseName, Status, SizeBytes,
BackupDate, DurationSeconds
FROM data_backup_log
WHERE Package = '{package}'
ORDER BY BackupDate DESC, SourceName
LIMIT {limit}
SELECT COUNT(*)
FROM def_backup
WHERE BackupName = '{backup_name}'
AND Package = '{package}'
INSERT INTO def_backup (
BackupName, Package, BackupType,
Remoteconnection, DatabaseMask,
Active, BackDescription, Module
) VALUES (
'{backup_name}', '{package}',
'{backup_type}', '{remote_connection}',
'{database_mask}', 'Y',
'{description}', 'ObjBackup'
)
ON DUPLICATE KEY UPDATE
BackupType = '{backup_type}',
Remoteconnection = '{remote_connection}',
DatabaseMask = '{database_mask}',
Module = 'ObjBackup'
SELECT BackupName, BackupType,
Remoteconnection, DatabaseMask,
Active
FROM def_backup
WHERE Package = '{package}'
ORDER BY BackupName
SELECT DISTINCT NotifyEmail
FROM def_backup
WHERE Package = '{package}'
AND Active = 'Y'
AND NotifyEmail IS NOT NULL
AND NotifyEmail != ''
INSERT IGNORE INTO def_notify (
NotifyCode, Package,
NotifySlackSend,
NotifySlackConnection,
NotifySmsConnection,
NotifySmsList
) VALUES (
'{notify_code}', '{package}',
'Y', '{package}', '', ''
)