Run a mySQL query as a cron job?

Run a mySQL query as a cron job?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Run a mySQL query as a cron job?

Running a database purge via cron requires moving beyond manual authentication. The core challenge you face is that shell scripts and cron jobs cannot interactively prompt for passwords; they need persistent access to the server. You have three primary paths forward: configuring the MySQL user, executing a PHP script directly from the shell, or using a dedicated task queue if you are in a Laravel ecosystem.

Configuring Passwordless Access

The most robust solution involves creating a dedicated database user with no password requirement. This is often simpler than managing rotating secrets for scheduled tasks. You can create this user via your standard MySQL client or php artisan migrate:fresh --force depending on your stack, but the SQL logic remains consistent:

CREATE USER 'cron_purger'@'localhost' IDENTIFIED BY '';
GRANT DELETE ON database_name.tbl_message TO 'cron_purger'@'localhost';
FLUSH PRIVILEGES;

Once this user exists, you can point your script or cron job directly to it. In a shell environment, you simply invoke the MySQL client with the username flag:

mysql --user=cron_purger -e "DELETE FROM tbl_message WHERE DATEDIFF(NOW(), timestamp) >= 7;"

This approach avoids the need for LOAD DATA INFILE workarounds or complex .my.cnf file permissions, which can sometimes be blocked by security policies on shared hosting or restricted servers.

Executing PHP Directly from Cron

If you prefer to keep your logic inside a PHP file—perhaps because the query logic is part of a larger application—you can invoke the PHP interpreter directly from cron. This bypasses the need for a web server context (like Apache or Nginx) entirely, which is generally safer for background tasks as it doesn't expose your site to unintended requests.

Create a shell script, say /usr/local/bin/purge_messages.sh, that acts as a wrapper:

#!/bin/bash
/usr/bin/php /path/to/your/application/delete_old_messages.php

Make the script executable (chmod +x /usr/local/bin/purge_messages.sh) and add it to your crontab:

0 2 * * * /usr/local/bin/purge_messages.sh >> /var/log/purge_messages.log 2>&1

Crucial Implementation Detail: When running PHP this way, you must ensure your script doesn't rely on the web server's configuration files (like php.ini from /etc/php/7.x/apache2). Your CLI version of PHP might have different extensions loaded or different environment variables set. If your original query used functions like mysql_query, note that these are deprecated and removed in modern PHP versions. You must migrate to PDO or mysqli. Using the legacy mysql_* extension will cause a fatal error on any server running PHP 7.0+, regardless of whether it is accessed via web or CLI.

The Laravel Context

If you are building your application with Laravel, the architecture shifts slightly. While you could run a raw SQL query as shown above, it is best practice to leverage the framework's built-in scheduling system. This allows you to define the job in code rather than managing shell scripts and cron entries manually.

You would define a command or schedule a task within app/Console/Kernel.php. This approach handles environment variable loading automatically (.env files) and integrates seamlessly with your existing database migrations and service providers. It ensures that the logic running nightly is identical to the logic tested during development, reducing the risk of "it works on my machine" discrepancies.

For more complex purging tasks, you might also consider using Laravel's queue system to handle heavy deletions asynchronously, preventing long-running queries from locking tables unnecessarily. However, for a simple cleanup like entries older than a week, a direct scheduled command is often sufficient and efficient.

Final Considerations on Logic

Your original query uses DATEDIFF(NOW(), timestamp) >= 7. While this works, ensure your timestamp column is indexed. Without an index on the deletion condition column, MySQL must scan every row in the table to determine which ones to delete. On large tables, this can cause significant I/O spikes and temporarily lock the table, potentially affecting read operations for other users. If performance becomes an issue, consider adding a composite index or restructuring the data model to optimize the cleanup process.

Our network