dbms/README.md

dbms

Collection for database management systems

• mssql-jobs-by-fsaver
• mysqldump-wrapper.sh

---

mssql-jobs-by-fsaver

Description:

running Microsoft SQL Server agent jobs by Effector Saver with handling of executing time and errors

Dependencies:

• SQL Server (tested version 2022 on Windows Server 2022)
• SQL Server Management Studio (tested version 19.1 on Windows Server 2022)
• Effector Saver (tested version 4.11 on Windows Server 2022)

Create maintenance plan with subplan name

example maintanence plane name: Daily-Database_Name
example subplan name: Backup

Microsoft SQL Server Management Studio -> Management -> Maintenance Plans -> New Maintenance Plan -> Name: Daily-Database_Name -> Subplan properties -> Name: Backup

Check auto made agent job and its name

Microsoft SQL Server Management Studio -> SQL Server Agent -> Jobs -> Daily-Database_Name.Backup

Create Effector Saver task

Effector Saver -> Tasks -> Add task -> Other tasks -> Commands -> Name: Daily-Database_Name -> Main preferences -> Add command

Name:

Daily-Database_Name.Backup

Command type:

SQL script

Command text:

SET NOCOUNT ON

DECLARE  @jobNM NVARCHAR(MAX) = 'Daily-Database_Name.Backup',
         @jobID UNIQUEIDENTIFIER,
         @maxID INT

SELECT @jobID = job_id FROM msdb..sysjobs WHERE name = @jobNM
SELECT @maxID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0
SET @maxID = COALESCE(@maxID, -1)

EXEC msdb..sp_start_job @job_id = @jobID
WHILE (SELECT MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0) = @maxID
    WAITFOR DELAY '00:00:01'

SELECT @maxID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0
IF (SELECT run_status FROM msdb..sysjobhistory WHERE instance_id = @maxID) = 0
    RAISERROR (N'#ERROR# Microsoft SQL Server Job finished with error', 16, 1) WITH NOWAIT

---

mysqldump-wrapper.sh

Description:

creating database dump, copying to additional smb share, copies rotating and sending report to email

Dependencies:

• privileged rights
• mysqldump (tested version 5.6.35 on CentOS 7)
• gzip (tested version 1.5 on CentOS 7)
• cifs-utils (tested version 2.08 on CentOS 7)
• Python 3 (tested version 3.9.5 on CentOS 7)
• sendmail.py

POSITION	PARAMETERS	DESCRIPTION	DEFAULT
1	[/path/to/file.conf]	path to config file	REQUIRED

Installing mysqldump-wrapper.sh and setting up crontab

# edit config
sudo tee /usr/local/bin/mysqldump-wrapper.conf > /dev/null <<'EOF'
# mysql connection parameters
db_host=db-server.domain.zone
db_user=db-username
db_pass=db-password
db_name=database

# dump repository parameters
dump_root=/home/user/backup
dump_save=1

# copy smb-repository parameters
smb_host=smb-server.domain.zone
smb_path=smb-share/backup
smb_user=smb-username
smb_domn=smb-domain
smb_pass=smb-password
copy_save=7

# sendmail parameters
smtp_pyth=/usr/local/opt/python-3.9/bin/python3.9
smtp_send=/usr/local/bin/sendmail.py
smtp_host=mail-server.domain.zone
smtp_port=587
smtp_from=mail-from@domain.zone
smtp_pass=mail-password
smtp_dest=mail-dest@domain.zone
EOF

# download
sudo wget https://git.hmp.today/pavel.muhortov/dbms/raw/branch/master/mysqldump-wrapper.sh -O /usr/local/bin/mysqldump-wrapper.sh
sudo chmod +x /usr/local/bin/mysqldump-wrapper.sh

# sudo sh -c "EDITOR=nano crontab -e"
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
00 20 * * * bash /usr/local/bin/mysqldump-wrapper.sh /usr/local/bin/mysqldump-wrapper.conf