daily pastebin goal
77%
SHARE
TWEET

zabbix 2.0 database partitioning script

a guest May 30th, 2012 2,762 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/bin/bash
  2. #
  3. # This script will partition your zabbix database to improve the efficiency.
  4. # It will also create stored procedures to do the necessary housekeeping,
  5. # and create a cronjob to do this on a daily basis
  6. #
  7. # This script inspired by the following:
  8. #       http://zabbixzone.com/zabbix/partitioning-tables/
  9. #
  10. # While the basic SQL is from the above page, this script both creates the necessary
  11. # SQL for the desired tables, and can create new partitions as the time goes on
  12. # assuming that the cronjob has been properly entered.
  13. #
  14.  
  15.  
  16. function usage {
  17. cat <<_EOF_
  18.  
  19. $0      [-h host][-u user][-p password][-d min_days][-y startyear]
  20.  
  21.         -h host         database host
  22.         -u user         db user
  23.         -p password     user password
  24.         -d min_days     Minimum number of days of history to keep
  25.         -m min_months   Minimum number of months to keep trends
  26.         -y startyear    First year to set up with partitions
  27.  
  28.  
  29. After running this script, don't forget to disable housekeeping if
  30. you didn't have the script disable it, and add the following cronjob
  31.  
  32.         ### Option: DisableHousekeeping
  33.         #       If set to 1, disables housekeeping.
  34.         #
  35.         # Mandatory: no
  36.         # Range: 0-1
  37.         ################### Uncomment and change the following line to 1 in
  38.         ################### Then restart the zabbix server
  39.         DisableHousekeeping=1
  40.  
  41.  
  42. Cron job
  43.  
  44. 0 0 * * *  /etc/zabbix/cron.d/housekeeping.sh
  45.  
  46.  
  47. _EOF_
  48.         exit
  49. }
  50.  
  51. SQL="/tmp/partition.sql"
  52.  
  53. #
  54. # How long to keep the daily history
  55. #
  56. daily_history_min=90
  57.  
  58. #
  59. # How long to keep the monthly history (months)
  60. #
  61. monthy_history_min=12
  62.  
  63. #
  64. # Years to create the monthly partitions for
  65. #
  66. first_year=`date +"%Y"`
  67. last_year=$first_year
  68. cur_month=`date +"%m"`
  69. if [ $cur_month -eq 12 ]; then
  70.         last_year=$((first_year+1))
  71.         cur_month=1
  72. fi
  73.  
  74. y=`date +"%Y"`
  75.        
  76. DUMP_FILE=/tmp/zabbix.sql
  77. DBHOST=localhost
  78. DBUSER=root
  79. DBPASS=
  80. while getopts "m:h:u:p:d:y:?h" flag; do
  81.         case $flag in
  82.                 h)      DBHOST=$OPTARG ;;
  83.                 u)      DBUSER=$OPTARG ;;
  84.                 p)      DBPASS=$OPTARG ;;
  85.                 d)      h=$OPTARG
  86.                         if [ $h -gt 0 ] 2>/dev/null; then
  87.                                 daily_history_min=$h
  88.                         else
  89.                                 echo "Invalid daily history min, exiting"
  90.                                 exit 1
  91.                         fi
  92.                         ;;
  93.                 m)      h=$OPTARG
  94.                         if [ $h -gt 0 ] 2>/dev/null; then
  95.                                 monthy_history_min=$h
  96.                         else
  97.                                 echo "Invalid monthly history min, exiting"
  98.                                 exit 1
  99.                         fi
  100.                         ;;
  101.  
  102.                 y)      yy=$OPTARG
  103.                         if [ $yy -lt $y -a $yy -gt 2000 ] 2>/dev/null; then
  104.                                 first_year=$yy
  105.                         else
  106.                                 echo "Invalid year, exiting"
  107.                                 exit 1
  108.                         fi
  109.                         ;;
  110.                 ?|h)    usage ;;
  111.         esac
  112. done
  113. shift $((OPTIND-1))
  114.  
  115. echo "Ready to partition tables."
  116.  
  117. echo -e "\nReady to update permissions of Zabbix user to create routines\n"
  118. echo -n "Enter root DB user: "
  119. read DBADMINUSER
  120. echo -n "Enter $DBADMINUSER password: "
  121. read DBADMINPASS
  122. mysql -B -h localhost -u $DBADMINUSER -p$DBADMINPASS -e "GRANT CREATE ROUTINE ON zabbix.* TO 'zabbix'@'localhost';"
  123. echo -e "\n"
  124.  
  125.  
  126. echo -ne "\nDo you want to backup the database (recommended) (Y/n): "
  127. read yn
  128. if [ "$yn" != "n" -a "$yn" != "N" ]; then
  129.         echo -e "\nEnter output file, press return for default of $DUMP_FILE"
  130.         read df
  131.         [ "$df" != "" ] && DUMP_FILE=$df
  132.  
  133.         #
  134.         # Lock tables is needed for a good mysqldump
  135.         #
  136.         echo "GRANT LOCK TABLES ON zabbix.* TO '${DBUSER}'@'${DBHOST}' IDENTIFIED BY '${DBPASS}';" | mysql -h${DBHOST} -u${DBADMINUSER} --password=${DBADMINPASS}
  137.  
  138.         mysqldump --opt -h ${DBHOST} -u ${DBUSER} -p${DBPASS} zabbix --result-file=${DUMP_FILE}
  139.         rc=$?
  140.         if [ $rc -ne 0 ]; then
  141.                 echo "Error during mysqldump, rc: $rc"
  142.                 echo "Do you wish to continue (y/N): "
  143.                 read yn
  144.                 [ "yn" != "y" -a "$yn" != "Y" ] && exit
  145.         else
  146.                 echo "Mysqldump succeeded!, proceeding with upgrade..."
  147.         fi
  148. else
  149.         echo "Are you certain you have a backup (y/N): "
  150.         read yn
  151.         [ "$yn" != 'y' -a "$yn" != "Y" ] && exit
  152. fi
  153.  
  154. echo -e "\n\nReady to proceed:"
  155.        
  156. echo -e "\nStarting yearly partioning at: $first_year"
  157. echo "and ending at: $last_year"
  158. echo "With $daily_history_min days of daily history"
  159. echo -e "\n\nReady to proceed (Y/n): "
  160. read yn
  161. [ "$yn" = 'n' -o "$yn" = "N" ] && exit
  162.  
  163.  
  164.  
  165. DAILY="history history_log history_str history_text history_uint"
  166. DAILY_IDS="itemid id itemid id itemid"
  167.  
  168. MONTHLY="acknowledges alerts auditlog events service_alarms"
  169. MONTHLY_IDS="acknowledgeid alertid auditid eventid servicealarmid"
  170.  
  171. TABLES="$DAILY $MONTHLY"
  172. IDS="$DAILY_IDS $MONTHLY_IDS"
  173.  
  174. CONSTRAINT_TABLES="acknowledges alerts auditlog service_alarms auditlog_details"
  175. CONSTRAINTS="c_acknowledges_1/c_acknowledges_2 c_alerts_1/c_alerts_2/c_alerts_3/c_alerts_4 c_auditlog_1 c_service_alarms_1 c_auditlog_details_1"
  176.  
  177. echo "Use zabbix;  SELECT 'Altering tables';" >$SQL
  178.  
  179. cnt=0
  180. for i in $CONSTRAINT_TABLES; do
  181.         cnt=$(($cnt+1))
  182.         for constraint in $(echo $CONSTRAINTS |cut -f$cnt -d" " |awk -F/ '{for (i=1; i <= NF; i++) {if ($i != "") {print $i}}}'); do
  183.                 echo "ALTER TABLE $i DROP FOREIGN KEY $constraint;" >>$SQL
  184.         done
  185. done
  186.  
  187. cnt=0
  188. for i in $TABLES; do
  189.         echo "Altering table: $i"
  190.         echo "SELECT '$i';" >>$SQL
  191.         cnt=$((cnt+1))
  192.         case $i in
  193.                 history_log)
  194.                         echo "ALTER TABLE $i DROP KEY history_log_2;" >>$SQL
  195.                         echo "ALTER TABLE $i ADD KEY history_log_2(itemid, id);" >>$SQL
  196.                         id=`echo $IDS | cut -f$cnt -d" "`
  197.                         echo "ALTER TABLE $i DROP PRIMARY KEY, ADD KEY ${i}id ($id);" >>$SQL
  198.                         ;;
  199.                 history_text)
  200.                         echo "ALTER TABLE $i DROP KEY history_text_2;" >>$SQL
  201.                         echo "ALTER TABLE $i ADD KEY history_text_2 (itemid, clock);" >>$SQL
  202.                         id=`echo $IDS | cut -f$cnt -d" "`
  203.                         echo "ALTER TABLE $i DROP PRIMARY KEY, ADD KEY ${i}id ($id);" >>$SQL
  204.                         ;;
  205.  
  206.                 acknowledges|alerts|auditlog|events|service_alarms)
  207.                         id=`echo $IDS | cut -f$cnt -d" "`
  208.                         echo "ALTER TABLE $i DROP PRIMARY KEY, ADD KEY ${i}id ($id);" >>$SQL
  209.                         ;;
  210.         esac
  211. done
  212. echo -en "\n"
  213. echo -en "\n" >>$SQL
  214. for i in $MONTHLY; do
  215.         echo "Creating monthly partitions for table: $i"
  216.         echo "SELECT '$i';" >>$SQL
  217.         echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL
  218.         for y in `seq $first_year $last_year`; do
  219.                 last_month=12
  220.                 [ $y -eq $last_year ] && last_month=$((cur_month+1))
  221.                 for m in `seq 1 $last_month`; do
  222.                         [ $m -lt 10 ] && m="0$m"
  223.                         pname="p${y}${m}"
  224.                         echo -n "PARTITION $pname  VALUES LESS THAN (UNIX_TIMESTAMP(\"$y-$m-01 00:00:00\"))" >>$SQL
  225.                         [ $m -ne $last_month -o $y -ne $last_year ] && echo -n "," >>$SQL
  226.                         echo -ne "\n" >>$SQL
  227.                 done
  228.         done
  229.         echo ");" >>$SQL
  230. done
  231.  
  232. echo -en "\n"
  233. for i in $DAILY; do
  234.         echo "Creating daily partitions for table: $i"
  235.         echo "SELECT '$i';" >>$SQL
  236.         echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL
  237.         for d in `seq -$daily_history_min 2`; do
  238.                 ds=`date +"%Y-%m-%d" -d "$d day"`
  239.                 pname=`date +"%Y%m%d" -d "$d day"`
  240.                 echo -n "PARTITION p$pname  VALUES LESS THAN (UNIX_TIMESTAMP(\"$ds 00:00:00\"))" >>$SQL
  241.                 [ $d -ne 2 ] && echo -n "," >>$SQL
  242.                 echo -ne "\n" >>$SQL
  243.         done
  244.         echo ");" >>$SQL
  245. done
  246.  
  247.  
  248.  
  249. ###############################################################
  250. cat >>$SQL <<_EOF_
  251. SELECT "Installing procedures";
  252.  
  253. /**************************************************************
  254.   MySQL Auto Partitioning Procedure for Zabbix 1.8
  255.   http://zabbixzone.com/zabbix/partitioning-tables/
  256.  
  257.   Author:  Ricardo Santos (rsantos at gmail.com)
  258.   Version: 20110518
  259. **************************************************************/
  260. DELIMITER //
  261. DROP PROCEDURE IF EXISTS zabbix.create_zabbix_partitions; //
  262. CREATE PROCEDURE zabbix.create_zabbix_partitions ()
  263. BEGIN
  264. _EOF_
  265.  
  266. ###############################################################
  267.  
  268. for i in $DAILY; do
  269.         echo "  CALL zabbix.create_next_partitions(\"zabbix\",\"$i\");" >>$SQL
  270.         echo "  CALL zabbix.drop_old_partitions(\"zabbix\",\"$i\");" >>$SQL
  271. done
  272. echo -en "\n" >>$SQL
  273. for i in $MONTHLY; do
  274.         echo "  CALL zabbix.create_next_monthly_partitions(\"zabbix\",\"$i\");" >>$SQL
  275.         echo "  CALL zabbix.drop_old_monthly_partitions(\"zabbix\",\"$i\");" >>$SQL
  276. done
  277.  
  278. ###############################################################
  279. cat >>$SQL <<_EOF_
  280. END //
  281.  
  282. DROP PROCEDURE IF EXISTS zabbix.create_next_partitions; //
  283. CREATE PROCEDURE zabbix.create_next_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
  284. BEGIN
  285.         DECLARE NEXTCLOCK timestamp;
  286.         DECLARE PARTITIONNAME varchar(16);
  287.         DECLARE CLOCK int;
  288.         SET @totaldays = 7;
  289.         SET @i = 1;
  290.         createloop: LOOP
  291.                 SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
  292.                 SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
  293.                 SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
  294.                 CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
  295.                 SET @i=@i+1;
  296.                 IF @i > @totaldays THEN
  297.                         LEAVE createloop;
  298.                 END IF;
  299.         END LOOP;
  300. END //
  301.  
  302.  
  303. DROP PROCEDURE IF EXISTS zabbix.drop_old_partitions; //
  304. CREATE PROCEDURE zabbix.drop_old_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
  305. BEGIN
  306.         DECLARE OLDCLOCK timestamp;
  307.         DECLARE PARTITIONNAME varchar(16);
  308.         DECLARE CLOCK int;
  309.         SET @mindays = $daily_history_min;
  310.         SET @maxdays = @mindays+4;
  311.         SET @i = @maxdays;
  312.         droploop: LOOP
  313.                 SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
  314.                 SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
  315.                 CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
  316.                 SET @i=@i-1;
  317.                 IF @i <= @mindays THEN
  318.                         LEAVE droploop;
  319.                 END IF;
  320.         END LOOP;
  321. END //
  322.  
  323. DROP PROCEDURE IF EXISTS zabbix.create_next_monthly_partitions; //
  324. CREATE PROCEDURE zabbix.create_next_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
  325. BEGIN
  326.         DECLARE NEXTCLOCK timestamp;
  327.         DECLARE PARTITIONNAME varchar(16);
  328.         DECLARE CLOCK int;
  329.         SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
  330.         SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' );
  331.         SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00'));
  332.         CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
  333. END //
  334.  
  335. DROP PROCEDURE IF EXISTS zabbix.drop_old_monthly_partitions; //
  336. CREATE PROCEDURE zabbix.drop_old_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
  337. BEGIN
  338.         DECLARE OLDCLOCK timestamp;
  339.         DECLARE PARTITIONNAME varchar(16);
  340.         DECLARE CLOCK int;
  341.         SET @minmonths = $monthy_history_min;
  342.         SET @maxmonths = @minmonths+24;
  343.         SET @i = @maxmonths;
  344.         droploop: LOOP
  345.                 SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
  346.                 SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );
  347.                 CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
  348.                 SET @i=@i-1;
  349.                 IF @i <= @minmonths THEN
  350.                         LEAVE droploop;
  351.                 END IF;
  352.         END LOOP;
  353. END //
  354.  
  355. DROP PROCEDURE IF EXISTS zabbix.create_partition; //
  356. CREATE PROCEDURE zabbix.create_partition (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
  357. BEGIN
  358.         DECLARE RETROWS int;
  359.         SELECT COUNT(1) INTO RETROWS
  360.                 FROM information_schema.partitions
  361.                 WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;
  362.  
  363.         IF RETROWS = 0 THEN
  364.                 SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
  365.                 SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
  366.                                 ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
  367.                 PREPARE STMT FROM @sql;
  368.                 EXECUTE STMT;
  369.                 DEALLOCATE PREPARE STMT;
  370.         END IF;
  371. END //
  372.  
  373. DROP PROCEDURE IF EXISTS zabbix.drop_partition; //
  374. CREATE PROCEDURE zabbix.drop_partition (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
  375. BEGIN
  376.         DECLARE RETROWS int;
  377.         SELECT COUNT(1) INTO RETROWS
  378.                 FROM information_schema.partitions
  379.                 WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;
  380.  
  381.         IF RETROWS = 1 THEN
  382.                 SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
  383.                 SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
  384.                                 ' DROP PARTITION ', PARTITIONNAME, ';' );
  385.                 PREPARE STMT FROM @sql;
  386.                 EXECUTE STMT;
  387.                 DEALLOCATE PREPARE STMT;
  388.         END IF;
  389. END //
  390. DELIMITER ;
  391. _EOF_
  392.  
  393. echo -e "\n\nReady to apply script to database (Y/n): "
  394. read yn
  395. if [ "$yn" != "n" -a "$yn" != "N" ]; then
  396.         echo -en "\nProceeding, please wait.  This may take a while\n\n"
  397.         mysql --skip-column-names -h ${DBHOST} -u ${DBUSER} -p${DBPASS} <$SQL
  398. fi
  399.  
  400. conf=/etc/zabbix/zabbix_server.conf
  401. echo -e "\nDo you want to update the /etc/zabbix/zabbix_server.conf"
  402. echo -n "to disable housekeeping (Y/n): "
  403. read yn
  404. if [ "$yn" != "n" -a "$yn" != "N" ]; then
  405.         cp $conf ${conf}.bak
  406.         sed  -i "s/^# DisableHousekeeping=0/DisableHousekeeping=1/" $conf
  407.         sed  -i "s/^DisableHousekeeping=0/DisableHousekeeping=1/" $conf
  408.         /etc/init.d/zabbix_server stop
  409.         sleep 5
  410.         /etc/init.d/zabbix_server start
  411. fi
  412.  
  413. tmpfile=/tmp/cron$$
  414. echo -ne "\nDo you want to update the crontab (Y/n): "
  415. read yn
  416. if [ "$yn" != "n" -a "$yn" != "N" ]; then
  417.         where=
  418.         while [ "$where" = "" ]; do
  419.                 echo "The crontab entry can be either in /etc/cron.daily, or added"
  420.                 echo -e "to the crontab for root\n"
  421.                 echo -n "Do you want to add this to the /etc/cron.daily directory (Y/n): "
  422.                 read where
  423.                 [ "$where" = "" -o "$where" = "y" ] && where="Y"
  424.                 if [ "$where" != "y" -a "$where" != "Y" -a "$where" != "n" -a "$where" != "N" ]; then
  425.                         where=""
  426.                         echo "Response not recognized, please try again"
  427.                 fi
  428.         done
  429.  
  430.         echo -en "\nEnter email of who should get the daily housekeeping reports: "
  431.         read mailto
  432.         [ "$mailto" = "" ] && mailto=root
  433.         mkdir -p /etc/zabbix/cron.d
  434.         cat >/etc/zabbix/cron.d/housekeeping.sh <<_EOF_
  435. #!/bin/bash
  436.  
  437. MAILTO=$mailto
  438. tmpfile=/tmp/housekeeping\$\$
  439.  
  440. date >\$tmpfile
  441. /usr/bin/mysql --skip-column-names -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_zabbix_partitions();" >>\$tmpfile 2>&1
  442. /bin/mail -s "Zabbix MySql Partition Housekeeping" \$MAILTO <\$tmpfile
  443. rm -f \$tmpfile
  444. _EOF_
  445.         chmod +x /etc/zabbix/cron.d/housekeeping.sh
  446.         chown -R zabbix.zabbix /etc/zabbix
  447.         if [ "$where" = "Y" ]; then
  448.                 cat >/etc/cron.daily/zabbix.housekeeping <<_EOF_
  449. #!/bin/bash
  450. /etc/zabbix/cron.d/housekeeping.sh
  451. _EOF_
  452.                 chmod +x /etc/cron.daily/zabbix.housekeeping
  453.         else
  454.                 crontab -l >$tmpfile
  455.                 cat >>$tmpfile <<_EOF_
  456. 0 0 * * *  /etc/zabbix/cron.d/housekeeping.sh
  457. _EOF_
  458.                 crontab $tmpfile
  459.                 rm $tmpfile
  460.         fi
  461. fi
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top