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