1. mysql 설치
cd $SOURCE/mysql-5.1.73
export CFLAGS="-g -O3 -fno-omit-frame-pointer"
export CXX="gcc"
export CXXFLAGS=" -g -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-pointer"
./configure --prefix=/usr/local/mysql_5.1 --with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static --with-charset=euckr --without-debug \
--enable-assembler --enable-local-infile --with-readline \
--with-plugins=innobase --with-big-tables --with-plugins=partition
make
make install
2. 확인
mysql ((none))>show variables like '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
=======================================================================================
파티션 분할 테스트
1. partition table 설정(uidx 값)
alter table chk_kopx_history PARTITION BY RANGE (uidx)
(
PARTITION p202005 VALUES LESS THAN (6867072),
PARTITION p202006 VALUES LESS THAN (7763739),
PARTITION p202007 VALUES LESS THAN (8747177),
PARTITION p202008 VALUES LESS THAN (9748633),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
2. 파티션 분할 확인
3. 파티션 추가 작업(pmax 파티션을 추가로 분할)
alter table chk_kopx_history REORGANIZE PARTITION pmax INTO
(
PARTITION p202205_2 VALUES LESS THAN (39930091),
PARTITION p202207 VALUES LESS THAN (13633138),
PARTITION p202208 VALUES LESS THAN (13633138),
PARTITION p202209 VALUES LESS THAN (13633138),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
4. 파티션 별 카운트 갯수
select PARTITION_NAME,table_rows from information_schema.partitions where table_name ='chk_kopx_history';
5. 파티션 자동 분할(crontab)
vi /root/partition.sh
========================================================================================
#/bin/sh
Fday=`date +"%Y-%m-01"`;
LOG=/root/partition_$(/bin/date +\%Y\%m\%d).txt
DBROOTPW='zhxhdlelqlfnxm'
echo `date` "partition Start" >> $LOG
# 오늘 날짜의 uidx 값 추출
uidx=`/usr/local/mysql/bin/mysql -N -uroot -p${DBROOTPW} kotoi -e "select MIN(uidx) as uidx from chk_kopx_history where reg_date='${Fday}';"`
# 파티션 분할
Ymonth=`date -d '1 month ago' +'%Y%m'`
table_a="alter table chk_kopx_history REORGANIZE PARTITION pmax INTO
(
PARTITION p${Ymonth} VALUES LESS THAN (${uidx}),
PARTITION pmax VALUES LESS THAN MAXVALUE
)"
/usr/local/mysql/bin/mysql -uroot -p${DBROOTPW} kotoi -e "${table_a}"
# 파티션 분할 확인
P_check="select PARTITION_NAME,table_rows from information_schema.partitions where table_name ='chk_kopx_history';"
/usr/local/mysql/bin/mysql -uroot -p${DBROOTPW} kotoi -e "${P_check}" >> $LOG
echo `date` "partition Finish" >> $LOG
============================================================================================