Triggers y monitorización en mariadb

GIT info, libro y esquemas de trabajo
28 de febrero de 2023
Monitorizando servicios systemd
24 de marzo de 2023
GIT info, libro y esquemas de trabajo
28 de febrero de 2023
Monitorizando servicios systemd
24 de marzo de 2023

1.- Primero creamos la tabla de notificaciones (multitabla):

CREATE TABLE notification (
notification_id int(10) unsigned NOT NULL AUTO_INCREMENT,
tablename VARCHAR(20) NULL,
sent tinyint(1) NULL,
PRIMARY KEY (notification_id)
);

2.- Creamos trigger indicando la tabla a monitorizar (multitabla):

DELIMITER $$
CREATE TRIGGER t_notification_insert_permissions
AFTER INSERT ON permissions
FOR EACH ROW 
BEGIN 
    INSERT INTO `notification_queue` (`sent`,`tablename`) VALUES (0,'permissions');
END$$
DELIMITER ; 

DELIMITER $$
CREATE TRIGGER t_notification_insert_customer
AFTER INSERT ON customer
FOR EACH ROW 
BEGIN 
    INSERT INTO `notification_queue` (`sent`,`tablename`) VALUES (0,'customer');
END$$
DELIMITER ; 

3.- Poner cron en /etc/cron.d/ddbb-monitor

<code>* * * * * root /opt/scripts/ddbb-monitor.sh >/dev/null 2>&1</code>

4.- Poner script en /opt/scripts/ddbb-monitor.sh (para 1 tabla, quitar campo tablename de la tabla de notificaciones)

#!/bin/bash

DB_USER='admin'
DB_PASS='admin'
DB_NAME='test_log'

ID=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "SELECT notification_id FROM notification_queue WHERE sent=0 LIMIT 1;"`

if [[ ! -z $ID ]]
then
    RESULT=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "UPDATE notification_queue SET sent=1 WHERE notification_id = $ID;"`

    date=$(date '+%d-%m-%Y %H:%M:%S')
    echo "WARNING the table users has been modified the $date" >> /var/log/ddbb-monitor.log

fi

5.- Poner script en /opt/scripts/ddbb-monitor.sh (multitabla)

#!/bin/bash

DB_USER='admin'
DB_PASS='admin'
DB_NAME='test'

ID=(`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "SELECT notification_id, tablename FROM notification_queue WHERE sent=0 ORDER BY notification_id DESC LIMIT 1;"`)

if [[ ! -z ${ID[0]} ]] && [[ ${ID[1]} == 'customer' ]]
then
    RESULT=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "UPDATE notification_queue SET sent=1 WHERE notification_id = ${ID[0]};"`

    date=$(date '+%d-%m-%Y %H:%M:%S')
    echo "WARNING the table customer has been modified the $date" >> /var/log/ddbb-monitor.log
fi

if [[ ! -z ${ID[0]} ]] && [[ ${ID[1]} == 'permissions' ]]
then
    RESULT=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "UPDATE notification_queue SET sent=1 WHERE notification_id = ${ID[0]};"`

    date=$(date '+%d-%m-%Y %H:%M:%S')
    echo "WARNING the table permissions has been modified the $date" >> /var/log/ddbb-monitor.log
fi

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Triggers y monitorización en mariadb
Este sitio web utiliza cookies para mejorar su experiencia. Al utilizar este sitio web, usted acepta nuestra Política de privacidad.
Leer más