MySQL Partition Manager
MySQL Partition Manager is an open source project for managing partitions for MySQL tables. This code helps you automatically create, maintain and purge partitions with minimal configuration.
Run the attached SQL file into the database of your choice. Please make sure that event scheduling is enabled on the database server.
When you run this file:
This code has been successfully tested on Percona’s flavor of MySQL 5.5 and MySQL 5.6. It should work on previous versions too. We are currently testing it for MySQL 5.7 as well.
Choose a table that you’d like to partition.
The pre-requisite to partition a table is:
The following code shows the settings table:
CREATE TABLE `partition_manager_settings` ( `table` varchar(64) NOT NULL COMMENT 'table name', `column` varchar(64) NOT NULL COMMENT 'numeric column with time info', `granularity` int(10) unsigned NOT NULL COMMENT 'granularity of column, i.e. 1=seconds, 60=minutes...', `increment` int(10) unsigned NOT NULL COMMENT 'seconds per individual partition', `retain` int(10) unsigned NULL COMMENT 'seconds of data to retain, null for infinite', `buffer` int(10) unsigned NULL COMMENT 'seconds of empty future partitions to create', PRIMARY KEY (`table`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=Dynamic;
Column Name | Definition ----------- | ---------- table | table_name column | column that you’d like to partition granularity | factoring granularity in seconds (1 denotes seconds, 60 denotes minutes, 3600 denotes hours) increment | Number of seconds per individual partition (86400 denotes 1 day) retain | Seconds worth of data to retain or null for infinite buffer | Seconds worth of empty feature partitions to maintain
schedule_partition_managerto update the partitioning schedule based on the new table. This is taken care of during the next run automatically, hence its optional.
Copyright 2015 Yahoo Inc. Licensed under the terms of Apache License 2.0. Please see the LICENSE file for terms.