As a stored procedures fan, I use MySQL stored procedures to
get some of my DBA tasks accomplished. To make it simple, I
have a template stored procedure that can be customized for
several purposes.
The template syntax contains cursor, continue handler, loop, if
condition and prepared statement. Thought it may be useful for
others – at least, who are searching for the MySQL Stored
Procedure syntax – so I’m publishing this post!
Use case: Reorganize tables partition:
In MySQL Cluster, it is required to reorganize all NDB tables’ partitions after adding new data nodes to rebalance the data across all nodes. Also the tables need to be optimized afterwards to reclaim the memory space. For this task I use the following procedure:
DROP PROCEDURE IF EXISTS reorganize_tables;
DELIMITER //
CREATE PROCEDURE reorganize_tables (IN db_name VARCHAR(50))
BEGIN
…
[Read more]