以下脚本的目的是自动清理最旧的MySQL分区表的分区，并按照当前日期新增后一天的分区。

MySQL的分区表的状态：

[root@script-lang script]# mysql -uroot -p'*****' adamhuan
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 115
Server version: 5.6.31-ndb-7.4.12-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+--------------------+
| Tables_in_adamhuan |
+--------------------+
| event_by_day       |
+--------------------+
1 row in set (0.00 sec)

mysql> drop table event_by_day;
Query OK, 0 rows affected (0.18 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table event_by_day(
    -> id int(10),
    -> e_name varchar(40),
    -> e_date int(11),
    -> e_desc varchar(300)
    -> )
    -> PARTITION by range (e_date)(
    -> PARTITION e_date_p1 VALUES less than (unix_timestamp('2013-04-01')),
    -> PARTITION e_date_p2 VALUES less than (unix_timestamp('2013-04-02')),
    -> PARTITION e_date_p3 VALUES less than (unix_timestamp('2013-04-03')),
    -> PARTITION e_date_p4 VALUES less than (unix_timestamp('2013-04-04')),
    -> PARTITION e_date_p5 VALUES less than (unix_timestamp('2013-04-05')),
    -> PARTITION e_date_p6 VALUES less than (unix_timestamp('2013-04-06')),
    -> PARTITION e_date_p7 VALUES less than (unix_timestamp('2013-04-07'))
    -> );
Query OK, 0 rows affected (0.44 sec)

mysql> show tables;
+--------------------+
| Tables_in_adamhuan |
+--------------------+
| event_by_day       |
+--------------------+
1 row in set (0.00 sec)

mysql> desc event_by_day
    -> ;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(10)      | YES  |     | NULL    |       |
| e_name | varchar(40)  | YES  |     | NULL    |       |
| e_date | int(11)      | YES  |     | NULL    |       |
| e_desc | varchar(300) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> 
mysql> SELECT table_schema,table_name,partition_name,partition_ordinal_position,create_time FROM information_schema.partitions WHERE table_name='event_by_day';
+--------------+--------------+----------------+----------------------------+---------------------+
| table_schema | table_name   | partition_name | partition_ordinal_position | create_time         |
+--------------+--------------+----------------+----------------------------+---------------------+
| adamhuan     | event_by_day | e_date_p1      |                          1 | 2016-10-15 06:30:37 |
| adamhuan     | event_by_day | e_date_p2      |                          2 | 2016-10-15 06:30:37 |
| adamhuan     | event_by_day | e_date_p3      |                          3 | 2016-10-15 06:30:37 |
| adamhuan     | event_by_day | e_date_p4      |                          4 | 2016-10-15 06:30:37 |
| adamhuan     | event_by_day | e_date_p5      |                          5 | 2016-10-15 06:30:37 |
| adamhuan     | event_by_day | e_date_p6      |                          6 | 2016-10-15 06:30:37 |
| adamhuan     | event_by_day | e_date_p7      |                          7 | 2016-10-15 06:30:37 |
+--------------+--------------+----------------+----------------------------+---------------------+
7 rows in set (0.00 sec)

mysql>

脚本如下：

[root@script-lang script]# cat shell_mysql_partition_table_delete_add.sh
# file: shell_mysql_partition_table_delete_add.sh
#
# author: adamhuan
# web site: d-prototype.com
#
# type: shell
# db: mysql
# object: partition table
# active: 
# 1. drop old partition
# 2. add new partition by tomorrow date
# os: linux

# Balance

echo "=================="
echo "Script: MySQL_PartitionTable_auto_delete_add"
echo "Begin: "`date`
echo "=================="

# Variables

## Conn
ip="localhost"
port="3306"
user="adamhuan"
password=******
db="adamhuan"
table=event_by_day

## Display
echo "IP: $ip"
echo "Port: $port"
echo "User: $user"
echo "Password: $password"
echo "DB: $db"
echo "Table: $table"

# Logical

## Func: do_sql

function do_sql(){
	# variable
	f_sql_str="$1"

	# display
	#echo "------"
	#echo "Func SQL: $f_sql_str"
	#echo "------"

	# do
	mysql -u $user -p"$password" $db -N -e "$f_sql_str"
}

## Func: get_result

## Func: do_delete_min_partition

function do_delete_min_partition(){
	# variable
	f_str_min_partition="$1"
	f_sql_str="alter table $table drop partition $f_str_min_partition"
	
	echo "@@@@@@@@@@@@@@@@@@@@@@"
	echo "SQL: $f_sql_str"
	echo "@@@@@@@@@@@@@@@@@@@@@@"

	# do
	do_sql "$f_sql_str"
}

## Func: scan_partition

function scan_partition(){
	# variable
	sql_str="SELECT table_schema,table_name,partition_name,partition_ordinal_position,create_time FROM information_schema.partitions WHERE table_name='$table';"

	# do
	do_sql "$sql_str"
}

## Func: calc_next_partition_str

function calc_next_partition_str(){
	# variable
	str_current_max_partition="$1"

	# do
	non_number=`echo $str_current_max_partition | tr -d "[0-9]"`
	split_str=`echo ${non_number: -1}`

	#echo "Func: Full: $str_current_max_partition"
	#echo "Func: Non_number: $non_number"
	#echo "Func: Split Str: $split_str"

	number=`echo $str_current_max_partition | cut -d"$split_str" -f2`
	next_number=$((number+1))

	#echo "Func: Number: $number"
	#echo "Func: Next Number: $next_number"

	next_partition_str=$non_number$next_number
	
	#echo "Func: Next Partition Str: $next_partition_str"

	# return
	#return $next_partition_str
	echo "$next_partition_str"
}

## Func: add nxet partition

function do_add_next_partition(){
	# variable
	f_next_partition_str="$1"
	str_tomorrow=`date -d"tomorrow" +"%F"`
	
	# SQL
	f_sql_str="alter table $table add partition (partition $f_next_partition_str values less than (unix_timestamp('$str_tomorrow')));"

	echo "@@@@@@@@@@@@@@@@@@@@@@"
	echo "SQL: $f_sql_str"
	echo "@@@@@@@@@@@@@@@@@@@@@@"

	# do
	do_sql "$f_sql_str"

}

# Do
echo "=================="
echo "Do Action"
echo "=================="

#sql_str="SELECT table_schema,table_name,partition_name,partition_ordinal_position,create_time FROM information_schema.partitions WHERE table_name='$table';"
#echo "-----------------"
#echo "Do SQL: $sql_str"
#echo "-----------------"

# +++++++++++++++++++++++++++++++++
echo ""
echo "@@@@@@@@@@@"
echo "###Scan Partiton Table: Partition"
# +++++++++++++++++++++++++++++++++
scan_partition

# +++++++++++++++++++++++++++++++++
echo ""
echo "@@@@@@@@@@@"
echo "###Scan Partiton Table: Min Partition"
# +++++++++++++++++++++++++++++++++
sql_str="SELECT min(partition_name) FROM information_schema.partitions WHERE table_name='$table' order by partition_name;"
str_min_partition=`do_sql "$sql_str"`

echo "Min Partition is: $str_min_partition"

# +++++++++++++++++++++++++++++++++
echo ""
echo "@@@@@@@@@@@"
echo "###Scan Partiton Table: Max Partition"
# +++++++++++++++++++++++++++++++++
sql_str="SELECT max(partition_name) FROM information_schema.partitions WHERE table_name='$table' order by partition_name;"
str_max_partition=`do_sql "$sql_str"`

echo "Max Partition is: $str_max_partition"

# +++++++++++++++++++++++++++++++++
echo ""
echo "@@@@@@@@@@@"
echo "###Do delete min partition"
# +++++++++++++++++++++++++++++++++
echo "-- do delete"
do_delete_min_partition "$str_min_partition"
echo "-- after delete"
scan_partition

# +++++++++++++++++++++++++++++++++
echo ""
echo "@@@@@@@@@@@"
echo "###Do add tomorrow partition"
# +++++++++++++++++++++++++++++++++

next_partition_name=`calc_next_partition_str "$str_max_partition"`
echo "-- next partition name is: $next_partition_name"

echo "-- do partition add:"
do_add_next_partition "$next_partition_name"

echo "-- after partition add:"
scan_partition

# Finished
echo "=================="
echo "Finished."
echo "End: "`date`
[root@script-lang script]#

执行效果：

[root@script-lang script]# sh shell_mysql_partition_table_delete_add.sh
==================
Script: MySQL_PartitionTable_auto_delete_add
Begin: Sat Oct 15 08:00:27 CST 2016
==================
IP: localhost
Port: 3306
User: adamhuan
Password: oracle
DB: adamhuan
Table: event_by_day
==================
Do Action
==================

@@@@@@@@@@@
###Scan Partiton Table: Partition
Warning: Using a password on the command line interface can be insecure.
+----------+--------------+-----------+------+---------------------+
| adamhuan | event_by_day | e_date_p1 |    1 | 2016-10-15 08:00:15 |
| adamhuan | event_by_day | e_date_p2 |    2 | 2016-10-15 08:00:15 |
| adamhuan | event_by_day | e_date_p3 |    3 | 2016-10-15 08:00:15 |
| adamhuan | event_by_day | e_date_p4 |    4 | 2016-10-15 08:00:15 |
| adamhuan | event_by_day | e_date_p5 |    5 | 2016-10-15 08:00:15 |
| adamhuan | event_by_day | e_date_p6 |    6 | 2016-10-15 08:00:15 |
| adamhuan | event_by_day | e_date_p7 |    7 | 2016-10-15 08:00:15 |
+----------+--------------+-----------+------+---------------------+

@@@@@@@@@@@
###Scan Partiton Table: Min Partition
Warning: Using a password on the command line interface can be insecure.
Min Partition is: e_date_p1

@@@@@@@@@@@
###Scan Partiton Table: Max Partition
Warning: Using a password on the command line interface can be insecure.
Max Partition is: e_date_p7

@@@@@@@@@@@
###Do delete min partition
-- do delete
@@@@@@@@@@@@@@@@@@@@@@
SQL: alter table event_by_day drop partition e_date_p1
@@@@@@@@@@@@@@@@@@@@@@
Warning: Using a password on the command line interface can be insecure.
-- after delete
Warning: Using a password on the command line interface can be insecure.
+----------+--------------+-----------+------+---------------------+
| adamhuan | event_by_day | e_date_p2 |    1 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p3 |    2 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p4 |    3 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p5 |    4 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p6 |    5 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p7 |    6 | 2016-10-15 08:00:27 |
+----------+--------------+-----------+------+---------------------+

@@@@@@@@@@@
###Do add tomorrow partition
-- next partition name is: e_date_p8
-- do partition add:
@@@@@@@@@@@@@@@@@@@@@@
SQL: alter table event_by_day add partition (partition e_date_p8 values less than (unix_timestamp('2016-10-16')));
@@@@@@@@@@@@@@@@@@@@@@
Warning: Using a password on the command line interface can be insecure.
-- after partition add:
Warning: Using a password on the command line interface can be insecure.
+----------+--------------+-----------+------+---------------------+
| adamhuan | event_by_day | e_date_p2 |    1 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p3 |    2 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p4 |    3 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p5 |    4 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p6 |    5 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p7 |    6 | 2016-10-15 08:00:27 |
| adamhuan | event_by_day | e_date_p8 |    7 | 2016-10-15 08:00:27 |
+----------+--------------+-----------+------+---------------------+
==================
Finished.
End: Sat Oct 15 08:00:27 CST 2016
[root@script-lang script]#

——————————————————
Done。