Recently I met a big trouble in one of my server it have 1.5TB data in a table but I need only last month data in the particular table.So I have planned to delete old records in the table but If am deleting means it will take so many days to purge it so I have searched and find a solution in Partitioning table after that I have deleted the unwanted partition and got free.
Here I have given the process for mysql partitioning.
Partition can be done by using primary key only and the value should be unique.otherwise it will throw and error as "A PRIMARY KEY must include all columns in the table's partitioning function"
create a table with partition.
ex:
CREATE TABLE testnew (
Id int(20) unsigned NOT NULL,
name varchar(20),
PRIMARY KEY (Id)
)
PARTITION BY RANGE ( ID ) (
PARTITION first VALUES LESS THAN (1000),
PARTITION second VALUES LESS THAN (2000),
PARTITION third VALUES LESS THAN (3000),
PARTITION fourth VALUES LESS THAN (4000),
PARTITION fifth VALUES LESS THAN (MAXVALUE))
);
It will create a table with partiton using column "ID".Here the records coming below 1000 will goes to first partition and like records will come from 1001 to 2000 will goes to second partition and if the records will come more than 4000 means it will go to fifth partition here this fifth partition is unlimited.
After the partition we can see some new files in mysql data directory.
-rw-rw---- 1 mysql mysql 56 Nov 9 03:08 testnew.frm
-rw-rw---- 1 mysql mysql 56 Nov 9 03:08 testnew.par
-rw-rw---- 1 mysql mysql 96K Nov 9 03:08 testnew#P#first.ibd
-rw-rw---- 1 mysql mysql 96K Nov 9 03:08 testnew#P#third.ibd
-rw-rw---- 1 mysql mysql 96K Nov 9 03:08 testnew#P#second.ibd
-rw-rw---- 1 mysql mysql 96K Nov 9 03:08 testnew#P#fourth.ibd
-rw-rw---- 1 mysql mysql 96K Nov 9 03:08 testnew#P#fifth.ibd
We can create partiton by date also,
CREATE TABLE blue (
Id int(20) unsigned NOT NULL,
name int(20),
lastdate datetime NOT NULL DEFAULT 0,
PRIMARY KEY (ID,name,lastdate)
)
PARTITION BY RANGE ( TO_DAYS(lastdate) ) (
PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01'))
);
I will insert records by month wise.We will have partition for each and every month.
Add partition for existing table by alter command,
Alter Command :
mysql>alter
table blue PARTITION BY RANGE ( ID )
(
PARTITION first VALUES LESS THAN (1000),
PARTITION second VALUES LESS THAN (2000),
PARTITION third VALUES LESS THAN (3000),
PARTITION fourth VALUES LESS THAN (4000),
PARTITION fifth VALUES LESS THAN (MAXVALUE)) );
Remove all partition without loss of data:
mysql>ALTER TABLE testnew remove partitioning;
Remove particular partition:
mysql>ALTER TABLE testnew drop partition first;
Reorganize the partition table:
mysql>ALTER
TABLE testnew REORGANIZE PARTITION first INTO (partition first values
less than (500),partition firstone values less than (1000));
Alter the existing table with pt-online-schema-change tool for live servers without locking the incoming queries.
#pt-online-schema-change --execute --user=admin --password=welcome --host=localhost --alter "partition by range(ID) (PARTITION first VALUES LESS THAN (1000), PARTITION second VALUES LESS THAN (2000), PARTITION third VALUES LESS THAN (3000), PARTITION fourth VALUES LESS THAN (4000), PARTITION fifth VALUES LESS THAN (MAXVALUE)))" D=test,t=blue
