Use MySQL stored procedures, the Event Scheduler, and SELECT … INTO OUTFILE to generate and deliver recurring query results without manual intervention.
Run a saved query on a schedule, export the result set (CSV, JSON, table), and deliver or store it automatically—no manual clicks.
Automate whenever stakeholders need the same KPI list, sales summary, or inventory snapshot at fixed intervals (hourly, daily, weekly).
Execute SET GLOBAL event_scheduler = ON;
or start MySQL with --event-scheduler=ON
. Verify with SHOW PROCESSLIST;
.
Pack complex joins and calculations in a procedure so the event simply calls it. This keeps code reusable and testable.
DELIMITER $$
CREATE PROCEDURE daily_sales_report()
BEGIN
SELECT c.id AS customer_id,
c.name,
SUM(o.total_amount) AS daily_total
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE DATE(o.order_date) = CURDATE()
GROUP BY c.id, c.name
INTO OUTFILE '/var/lib/mysql-files/daily_sales_'.DATE_FORMAT(CURDATE(),'%Y%m%d').'.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
END $$
DELIMITER ;
CREATE EVENT IF NOT EXISTS ev_daily_sales
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY
DO CALL daily_sales_report();
Use SELECT … INTO OUTFILE
inside the procedure. Ensure the MySQL user has FILE
privilege and the output path is writable.
After export, call an external script via a cron job, AWS Lambda, or your CI runner to pick up the file and send it via SMTP or S3.
Write to a sandbox directory, revoke FILE
from non-admin roles, log event execution with SHOW EVENTS
, and version-control procedure code.
Event not firing: Scheduler off. Run SET GLOBAL event_scheduler = ON;
.
Permission denied writing file: Grant FILE
privilege and verify directory permissions.
CSV missing headers: Add a UNION header row or export via a script that prepends column names.
Enable the scheduler, place report logic in a stored procedure, and attach a recurring event. You now have hands-free, reproducible MySQL reports.
Yes. Create a separate event for each stored procedure and specify unique schedules (e.g., EVERY 1 HOUR, EVERY 1 WEEK).
Events created with ON COMPLETION PRESERVE are saved in the mysql.event table and restart automatically when the server comes back online.
Prepend a header row using UNION ALL or post-process the file with a shell script that echoes headers before appending the data.