Cron调用的Bash同步脚本（sync.sh）MySQL操作失效问题排查

Hey there! Let’s figure out why your MySQL operations aren’t working in the sync.sh script triggered by Cron. Since the file copy and email steps are working fine, the issue is definitely tied to how the script interacts with MySQL. Here are the key areas to check and fix:

1. Cron’s Minimal Environment Breaks MySQL Path #

Cron runs with a stripped-down environment—its PATH variable is way shorter than your regular user shell. If you’re just using mysql in your script instead of the full absolute path, Cron might not be able to find the MySQL executable at all.

• Fix: Run which mysql in your regular shell to get the full path (e.g., /usr/bin/mysql), then replace every instance of mysql in your script with that full path.
  Example: Instead of mysql -u myuser -p mypass mydb < parse_file.sql, use /usr/bin/mysql -u myuser -p mypass mydb < parse_file.sql

2. MySQL Credential Issues in Cron Context #

If you rely on a ~/.my.cnf file for MySQL credentials, Cron might not have access to it. Either Cron runs as a different user (so it looks for the file in a different home directory), or the file’s permissions are too open (MySQL rejects it for security).

• Fixes:
  • Use a restricted my.cnf file: Make sure it has chmod 600 permissions (only the owner can read/write), and place it in the home directory of the user Cron runs as.
  • Alternatively, specify credentials explicitly in the script (but avoid plaintext passwords if possible—use my.cnf for better security).

3. Silent SQL Script Failures (No Error Logging) #

Your SQL script might be failing, but Cron doesn’t show output by default, so you’re missing error messages. Common issues here include relative file paths that don’t resolve correctly in Cron’s context, or stored procedure errors.

• Fixes:
  • Add detailed logging to your MySQL command. Redirect both output and errors to a log file so you can debug:

    /usr/bin/mysql -u myuser -p mypass mydb < /full/path/to/parse_file.sql >> /var/log/sync_mysql_errors.log 2>&1
    

  • Replace all relative paths in your SQL script with absolute paths. For example, instead of LOAD DATA INFILE './uploaded/data.csv', use LOAD DATA INFILE '/home/youruser/upload_dir/data.csv'.

4. Stored Procedure Permissions or Bugs #

If your SQL script calls stored procedures, the MySQL user running the script might lack EXECUTE permissions, or the procedures themselves have bugs that cause silent failures.

• Fixes:
  • Manually log into MySQL as the script’s user and run the stored procedures with the same input data. This will reveal any errors that aren’t showing up in Cron.
  • Grant the necessary permissions: Run GRANT EXECUTE ON PROCEDURE mydb.my_proc TO 'myuser'@'localhost'; if the user doesn’t have execution rights, plus any INSERT/UPDATE/DELETE permissions the procedure needs.

5. Cron User File Access Permissions #

The user running Cron might not have read access to your SQL script, input files, or their parent directories—even if your regular user does.

• Fix: Check permissions with ls -l /path/to/sync.sh and ls -l /path/to/parse_file.sql. Ensure the Cron user has at least read access (r permission) to these files and their parent folders. Adjust with chmod o+r or chown if needed.

6. Replicate Cron’s Environment Manually #

To see exactly what’s happening when Cron runs the script, execute it as the Cron user. For example, if Cron runs as root, use:

su - root -c "/full/path/to/sync.sh"

This will show you any error messages that Cron would normally hide, making it easier to spot the issue.

内容的提问来源于stack exchange，提问作者Wed