SoFunction
Updated on 2025-03-03

Implementation steps for using Cron to execute SQL tasks regularly in Linux

Preface

The demonstration data needs to be updated every day, and I don’t want to execute it manually, and I think of the following solutions

  • navicat creates a timed task
  • Java service execution regularly
  • linux crontab executes sql scripts regularly

1. Plan analysis

I chose the third option

  • Scheme 1 requires local navicat to be run before timing can be executed. When the computer is not turned on on weekends or leave, the data cannot be updated.
  • Solution 2 is high cost, and service means that you are not pure O(∩_∩)O
  • Plan 3 is not bad, what? Won't? I'll teach you!

2. Use steps

My database is kingbase, which is adjusted according to my own database adaptability

1. Prepare the script

  • sql file 
-- Weather monitoring data
UPDATE "natural_monitor"."weather_monitor_data" 
SET data_time = data_time :: TIME + CURRENT_DATE;
  • Execution file of SQL filesql_script.sh 
#!/bin/bash
cd /data/Kingbase/ES/V8/KESRealPro/V008R006C007B0024/Server/bin
password=Database Password ./ksql -p 54321 -d Database name -U Database account -f /data/cron/*.sql

Script execution

crontab -e

0 12 * * * /data/cron/sql_script.sh >> /data/cron/log_info.out 2>&1 &

Step on a pit

  • Don't know the ksql path
    Because the driver installed by kingbase is pgsql, the direction I started to check was psql, but there was no file on the server. Later, I found that kingbase corresponds to ksql
cd /
find -name ksql

  1. No permission to execute ksql
    The path to the ksql is/data/Kingbase/ES/V8/KESRealPro/V008R006C007B0024/Server/bin
    So run the script to changepassword=Database password./data/Kingbase/ES/V8/KESRealPro/V008R006C007B0024/Server/bin/ksql -p 54321 -d Database name -U Database account -f /data/cron/*.sql, the ksql path cannot be found.
    Trying to change the server user to kingbase still invalid.
    Solution:First cd to the ksql directory, and then execute ./ksql

  2. The /data/cron/update_status_nohup.out directory does not have permission
    Use root user to executecrontab, the crontab is isolated between each user.

  3. Remember to calibrate the server time, or query the server time

This is the end of this article about the implementation steps of using Cron to execute SQL tasks in Linux. For more related content on Linux Cron to execute SQL regularly, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!