Automating mantenance tasks on MySQL

I'm trying to automate some trivial maintenance tasks for my own MySQL server, and trying also to minimize the effort, so ... Here is the recipe:

Take an excellent generalized stored procedure like the one by Konstantin Osipov, see "Dynamic SQL is Stored Procedures" on MySQLForge (example 4).
Tune it a bit so that it takes into account only non system tables (I'm trying to turn it into something similar to Microsoft's sp_MSforeachtable), here is the code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`sp_4_each_table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_4_each_table`(db_name VARCHAR(64), template VARCHAR(21845))
BEGIN
#
DECLARE done INT DEFAULT 0;
#
DECLARE tname VARCHAR(64);
#
DECLARE c CURSOR FOR
#
SELECT table_name FROM information_schema.TABLES WHERE table_schema=db_name AND table_type = 'BASE TABLE';
#
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done= 1;
#
 
#
OPEN c;
#
REPEAT
#
FETCH c INTO tname;
#
SET @stmt_text=REPLACE(template, " ?", CONCAT(" ", tname));
#
BEGIN
#
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
#
SELECT CONCAT("'", @stmt_text, "' command failed") AS "error";
#
PREPARE stmt FROM @stmt_text;
#
EXECUTE stmt;
#
DEALLOCATE prepare stmt;
#
END;
#
UNTIL done END REPEAT;
#
END $$
 
DELIMITER ; 

Now with the procedure at hand I'm going on with the scheduling part, taking advantage of the great new "EVENT" feature of MySQL 5.1, see it in action:
First of all check if events are enabled;

Code:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.18-beta-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

Add the event to the database, we'll activate events later

Code:

mysql> create event analyze_all
-> on schedule every 1 day
-> starts timestamp '2007-05-27 23:59:59'
-> ends timestamp '2007-05-27 23:59:59' + interval 1 year
-> on completion preserve
-> comment 'updates stats by analyzing tables'
-> do call sp_4_each_table('test', 'analyze table ?');
Query OK, 0 rows affected (0.03 sec)

Check it's existance from the related information_schema table:

Code:

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+---------+
| event_name  | event_type | status  |
+-------------+------------+---------+
| analyze_all | RECURRING  | ENABLED |
+-------------+------------+---------+
1 row in set (0.02 sec)

Ok, the event is here.
Now I'll have to enable events on the server and let it rip, it's very simple
just issue an:

Code:

SET GLOBAL event_scheduler=1;

Or start the server with the:

Code:

--event_scheduler=1

Stopping an event without deleting it's also very simple:

Code:

mysql> alter event analyze_all disable;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+----------+
| event_name  | event_type | status   |
+-------------+------------+----------+
| analyze_all | RECURRING  | DISABLED |
+-------------+------------+----------+
1 row in set (0.00 sec)

Sooner or later I'll show you how this, with "federated tables", "csv storage engine" and "upserts" can really ease and improve an ETL process.

Featured on MySQL Newsletter June 2007. WOW !!!