how to fast drop partition table in mysql - EXPLAINED!

So your file system is full (or will be very soon) and there are serious possibility that your mysql database will stop. You have to drop some old partitions.
There are two ways to do it.

First way - slow way

server#mysql
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
.
.
mysql> use database_name;
Database changed
mysql> alter table table_name drop partition partition_name;

Remember that partiotion_name has to be name of oldest partition so when you drop that partition consistency of data stored in database will be preserved.
In my case this look like this


mysql> alter table SystemEvents drop partition p20140701;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
In case that you have more then few partition do drop, you can drop more partitions in one mysql row or my can drop partition one by one. This is up to you. This can be quite boring and time consuming.

Different way - fast way

This way require just list text manipulation skill.

As you know you can execute mysql command by just redirecting conf file in mysql. Syntax goes like this

mysql database_name < conf_file

So how to write that file?

1.List partition that you want to drop.

In my case this look like this

server# ls /var/lib/mysql/Syslog/|grep 201407|grep MYI |awk -F# '{print $3}'|awk -F. '{print $1}' >list.txt

server#cat list

p20140702
p20140703
p20140704
.

.

p20140728
p20140729
p20140730

2.Add mysql command in first line of that file

alter table SystemEvents drop partition p20140702
p20140703
p20140704
.

.

p20140728
p20140729
p20140730

3.And coma (,) as last character in every  line.

server#awk '{print $0","}' line.txt >line_1.txt

4.Close mysql syntax by adding ; at the end of last line.

Your file should look like this

server#cat line_1.txt

alter table SystemEvents drop partition p20140702,
p20140703,
p20140704,
.

.

p20140728,
p20140729,
p20140730;

5. Lets drop partition

server#mysql Syslog <line_1.txt
server#

And that is that!