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
