Executing a SQL query from a script in Linux

It was necessary recently to write a script that executes a sql query into the MySQL database.
Example content:

# Description, here I wrote for others that the script is added to the crontab, so that it is not moved
mysql -u USER -pPASSWORD -h 127.0.0.1 -e "UPDATE nika_system.abon SET otkl=0 WHERE depozit > '10' AND (otkl='-1' OR otkl='-2');";

In order for the script to run automatically, open the crontab file in any text editor (in the nano editor CTRL+X for the output and y/n for saving or canceling the changes):

sudo nano /etc/crontab

Add the following line to it:

0 9 * * * root /home/nika/scripts/reset_credit_nika.sh > /dev/null 2>&1

Now every day at 9 am the script will be executed.

Here is an example of daily automatic output of data from a sql table into a text file:

#!/bin/bash
mysql -u USER -pPASSWORD -h 192.168.1.1 -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

In /etc/crontab we add:

0 8 * * * root /scripts/freektb.sh > /srv/samba/dir/mirazh/$(date +%Y-%m-%d).txt

For security reasons, it’s better not to specify the password in scripts, see my article – Connecting to MySQL from localhost without entering a password

Script to check for an empty directory

Recently noticed on one of his backup scripts that he stopped working and just created an empty directory with the current date, so there was an idea to add to it a check on an empty directory with a notification sent to the email.

Example of a script for checking for an empty directory, taking into account hidden files:

if [ `ls -a /backups/dir/ | grep -v "^\.$" | grep -v "^\..$" | wc -l` = "0" ]; then echo "Empty dir"; else echo "Non empty dir"; fi

Example of checking the directory with the name of the current date and if it is empty – by sending an email:

if [ `ls -a /backups/`date +%Y-%m-%d`/ | grep -v "^\.$" | grep -v "^\..$" | wc -l` = "0" ]; then (echo "Subject:Empty dir"; echo "Empty dir";) | sendmail info@example.com; else echo "Non empty dir"; fi

Bash script to reboot network devices via telnet

I noticed that some cheap managed network equipment can start working incorrectly in a few days or weeks, so I had an idea to write a reboot script and add it to the cron.

Content of the script:

#!/bin/bash
(
sleep 5
echo "admin"
sleep 5
echo "password"
sleep 5
echo "reboot"
sleep 5
echo "y"
sleep 5
echo "quit"
) | telnet 192.168.1.10

sleep 5 means a pause of 5 seconds after each command, this value is optimal for long thinking equipment. For example, for client switches D-Link DES-3200 pause can be completely removed or set 1.

See also:
Using and configuring CRON

Backup configuration of TP-Link switches

I wrote a script for backup configuration of TP-Link switches.
The script runs on Linux where the TFTP server is running, it is connected via telnet to the switch and the configuration command is sent to the specified TFTP, when the telnet connection is closed, the file is moved to the desired directory, and the last command deletes files longer than 30 days, as more of them I do not need to store.
You can also make a copy of all the files in the cloud every month.
As you can see, before entering a password in the script, you can not pause.

#!/bin/bash
{
echo "PASSWORD";
echo "enable";
echo "PASSWORD";
echo "copy startup-config tftp ip-address 192.168.0.5 filename tplink";
sleep 2;
echo "exit";
sleep 1;
echo "exit";
} | telnet 192.168.0.110

mv /srv/tftp/tplink.cfg /backups/devices/tplink/`date +%Y-%m-%d`_tplink.cfg
find /backups/devices/tplink/ -type f -mtime +30 -exec rm {} \;