By: Daniel Farina | Comments | Related: > SQL Server on Linux
Problem
In a previous tip we looked at how to use Cron to schedule tasks for SQL Server on Linux. In this tip we take a deeper look to help answer these questions: Is there is any security configuration that I can use? Is there a way to view a log of cron work? How I can check if a job was successful or failed? In this tip I will answer those questions.
Solution
In my previous tip in this series I introduced you to the cron daemon and its configuration file, crontab. I omitted many items in order to make the introduction simple. My intentions with that tip were mostly to introduce you to the format of crontab file. Now I will cover more advanced topics.
Advanced Cron Topics
It is not enough to just know the crontab file format to use cron. Think about it. It is pointless to schedule a job if you won’t be able to know whether it was successful or failed.
Here is a list of topics I will cover in this tip:
- Crontab Variables
- Crontab Logging
- Crontab Security
- Crontab Shortcuts
Let’s start.
Crontab Variables
You can define and use variables in a crontab file. There are predefined variables where the name has a specific use. Take a look at the next table. Some of those variables have a default value which can be overridden by redefining it. Also, you can assign an empty string if you want to blank out the contents of a variable (i.e. VARIABLE="").
| Variable | Description | Example | Default Value |
|---|---|---|---|
| SHELL | The shell used to execute each command in the crontab file. | SHELL=/bin/bash | /bin/sh |
| PATH | Indicates the path to the directories in which cron will look for the command to execute. This path is different from the global path of the system or the user. | PATH= /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin | /usr/bin:/bin |
| MAILTO | The addresses (separated with a comma) that will receive by mail the log of failed tasks. When this variable is declared as empty (i.e. MAILTO="") no mail is delivered. | [email protected] | Mail is sent to the owner of the crontab. |
| HOME | This is the root directory for cron. | HOME=/ | Its value is taken from the /etc/passwd file for the user. |
| LOGNAME | This is the user name that the job is running from. Its value cannot be changed. | N/A | N/A |
Something important to note is that cron doesn’t perform variable substitution. In layman’s terms, you can’t assign a variable the sum or composition of other variables. Let’s consider the following example:
HOME=/home/daniel PATH=HOME:/bin/:/usr/bin
On the previous example the user who wrote the crontab lines wants the PATH variable to contain the directory specified in the HOME variable and additionally /bin and /usr/bin directories. But since there is no variable substitution in cron, the PATH will take "HOME:/bin/:/usr/bin" as a literal. The proper way to write the previous lines is as follows:
HOME=/home/daniel PATH=/home/daniel:/bin/:/usr/bin
In the next image you will see a crontab file with SHELL and PATH variables declared.
The next code fragment shows how to use a variable in a job definition. It is declaring a variable SQLDATA with the path to SQL Server backups and each day at midnight moves the backup files to an external device.
SQLDATA=/var/opt/mssql/data 0 0 * * * mv $SQLDATA/*.bak /mnt/
Cron Logging
Cron jobs are logged by default to the file /var/log/syslog, which is the file where all services log statuses and messages. There are different ways you can view its content.
The next script uses the grep command to search the pattern "cron" in the file /var/log/syslog (the -i is to make the search case insensitive).
grep -i cron /var/log/syslog
Also, we can use a pipe to send the output to the tail command if we are interested only in the last 10 lines.
grep -i cron /var/log/syslog | tail
The systemctl command shows the status of cron service as well as its last entries in syslog.
systemctl status cron
Also, we can use journalctl (a command that queries the system journal) and pass the system unit cron as a parameter. It has the peculiarity that shows system reboots.
journalctl -u cron
Creating a cron.log File
We can create a single file that holds the cron messages by editing the /etc/rsyslog.d/50-default.conf file and uncommenting the line that starts with #cron.*. On the next image you can see that line.
After you uncomment that line and save the changes you should run the following command to restart the rsyslog service.
systemctl restart rsyslog
Crontab Security
Cron has two files, /etc/cron.allow and /etc/cron.deny that we can use to configure and manage the users allowed to have a personal crontab. The system-wide crontab will work regardless of the contents of those files. Those files have the same format which is one user per line. Here is how they work.
- cron.allow - defines the users that are able to have their crontab file and therefore use cron.
- cron.deny - contains all the users that are forbidden to have their crontab file and therefore use cron.
- Depending on your distribution you may not have one of those files but you can create them.
In order to protect the system-wide crontab you have to set the proper permissions to the /etc/crontab file. The following command will give read and write permissions only for the root user and no other user will be able to read the file.
chmod 600 /etc/crontab
Crontab Shortcuts
Cron also has special folders that serve as shortcuts to schedule jobs at specific times. In the next table I enumerate those folders.
| Folder | Description |
|---|---|
| /etc/cron.daily | The scripts on this folder run daily. |
| /etc/cron.hourly | The scripts on this folder run hourly. |
| /etc/cron.monthly | The scripts on this folder run monthly. |
| /etc/cron.weekly | The scripts on this folder run weekly. |
The system-wide crontab is what executes the content of each of those folders, so it may be useful to set those folders with root only permission to avoid the chance that a user puts malicious scripts in those folders.
If you want to know at which specific time the scripts in those folders will execute, you have to look at the /etc/crontab file. The next code is the content of my Ubuntu’s /etc/crontab file.
# m h dom mon dow user command 17 * * * * root cd / && run-parts --report /etc/cron.hourly 25 6 * * * root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily ) 47 6 * * 7 root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly ) 52 6 1 * * root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )
Based on the previous example, contents of /etc/cron.hourly folder will start to execute at minute 17 every hour. Content of /etc/cron.weekly directory will run daily at 06:25 AM. The scripts in /etc/cron.weekly folder will run at 06:47 AM on Sunday each week. Finally, the scripts in /etc/monthly will execute at 06:52 AM the first day of the month.
Next Steps
- Read my previous tip in this series.
- New to Linux? This is where you must begin: Getting Started with SQL Server on Linux.
- After reading the previous tip I suggest you read 7 Things Every SQL Server DBA Should Know About Linux.
- In the following tip you will find the most used commands for SQL Server DBA’s: Top 10 Linux Commands for SQL Server DBAs.
- Also, if you want to learn other Linux administration basics you can read my previous tips where I cover how to check for CPU Usage, Disk I/O and Disk Space.
- For more information regarding SQL Server and Linux check the SQL Server on Linux Tips Category.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
