Zabbix partition deleting accident


Jephe Wu - http://linuxtechres.blogspot.com

Env: Zabbix 1.8.5 on CentOS 6.2 64bit.
config: daily and monthly partition has been configured on zabbix database, please refer to http://zabbixzone.com/zabbix/partitioning-tables/ and http://pastebin.com/nY8WG9Fr
accident: changed the minimum value of month from 12 to 2 to keep only 2 months data for trends and trends_uint tables.
e.g. the current month is Apr, deleted 201106-201201, left 201202-201204 plus the future months till 201204 objective: undo the deletion

Concept: restore the whole set of data from tape, then export the trends and trends_uint tables by mysqldump, create partitions again then import back to production database.


After incident, the table trends partition becomes this:
CREATE TABLE `trends` (
  `itemid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `num` int(11) NOT NULL DEFAULT '0',
  `value_min` double(16,4) NOT NULL DEFAULT '0.0000',
  `value_avg` double(16,4) NOT NULL DEFAULT '0.0000',
  `value_max` double(16,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p201202 VALUES LESS THAN (1330520400) ENGINE = InnoDB,
 PARTITION p201203 VALUES LESS THAN (1333198800) ENGINE = InnoDB,
 PARTITION p201204 VALUES LESS THAN (1335794400) ENGINE = InnoDB,
 PARTITION p201205 VALUES LESS THAN (1338472800) ENGINE = InnoDB,
 PARTITION p201206 VALUES LESS THAN (1341064800) ENGINE = InnoDB,
 PARTITION p201207 VALUES LESS THAN (1343743200) ENGINE = InnoDB,
 PARTITION p201208 VALUES LESS THAN (1346421600) ENGINE = InnoDB,
 PARTITION p201209 VALUES LESS THAN (1349013600) ENGINE = InnoDB,
 PARTITION p201210 VALUES LESS THAN (1351688400) ENGINE = InnoDB,
 PARTITION p201211 VALUES LESS THAN (1354280400) ENGINE = InnoDB,
 PARTITION p201212 VALUES LESS THAN (1356958800) ENGINE = InnoDB,
 PARTITION p201301 VALUES LESS THAN (1359637200) ENGINE = InnoDB,
 PARTITION p201302 VALUES LESS THAN (1362056400) ENGINE = InnoDB,
 PARTITION p201303 VALUES LESS THAN (1364734800) ENGINE = InnoDB,
 PARTITION p201304 VALUES LESS THAN (1367330400) ENGINE = InnoDB) */ |


Steps: 


1. restore the whole set of backup data from tape, export the deleted partition data as .sql file.
  mysqldump -uzabbix -pzabbix zabbix --no-create-info --skip-add-locks -w "clock >=1309442400 and clock <1312120800" trends > trends_201107.sql
  mysqldump -uzabbix -pzabbix zabbix --no-create-info --skip-add-locks -w "clock >=1312120800 and clock <1314799200" trends > trends_201108.sql
  mysqldump -uzabbix -pzabbix zabbix --no-create-info --skip-add-locks -w "clock >=1314799200 and clock <1317391200" trends > trends_201109.sql
  ...
  mysqldump -uzabbix -pzabbix zabbix --no-create-info --skip-add-locks -w "clock >=1309442400 and clock <1312120800" trends_uint > 


trends_uint_201107.sql
  mysqldump -uzabbix -pzabbix zabbix --no-create-info --skip-add-locks -w "clock >=1312120800 and clock <1314799200" trends_uint > 


trends_uint_201108.sql
  mysqldump -uzabbix -pzabbix zabbix --no-create-info --skip-add-locks -w "clock >=1314799200 and clock <1317391200" trends_uint > 


trends_uint_201109.sql
  ...

2. create partiton back first ( it will use temporary name during creation, after finish, it will use trends.* name back again)

echo "ALTER TABLE trends PARTITION BY RANGE( clock ) (
 PARTITION p201106 VALUES LESS THAN (1309442400) ENGINE = InnoDB,
 PARTITION p201107 VALUES LESS THAN (1312120800) ENGINE = InnoDB,
 PARTITION p201108 VALUES LESS THAN (1314799200) ENGINE = InnoDB,
 PARTITION p201109 VALUES LESS THAN (1317391200) ENGINE = InnoDB,
 PARTITION p201110 VALUES LESS THAN (1320066000) ENGINE = InnoDB,
 PARTITION p201111 VALUES LESS THAN (1322658000) ENGINE = InnoDB,
 PARTITION p201112 VALUES LESS THAN (1325336400) ENGINE = InnoDB,
 PARTITION p201201 VALUES LESS THAN (1328014800) ENGINE = InnoDB,
 PARTITION p201202 VALUES LESS THAN (1330520400) ENGINE = InnoDB,
 PARTITION p201203 VALUES LESS THAN (1333198800) ENGINE = InnoDB,
 PARTITION p201204 VALUES LESS THAN (1335794400) ENGINE = InnoDB,
 PARTITION p201205 VALUES LESS THAN (1338472800) ENGINE = InnoDB,
 PARTITION p201206 VALUES LESS THAN (1341064800) ENGINE = InnoDB,
 PARTITION p201207 VALUES LESS THAN (1343743200) ENGINE = InnoDB,
 PARTITION p201208 VALUES LESS THAN (1346421600) ENGINE = InnoDB,
 PARTITION p201209 VALUES LESS THAN (1349013600) ENGINE = InnoDB,
 PARTITION p201210 VALUES LESS THAN (1351688400) ENGINE = InnoDB,
 PARTITION p201211 VALUES LESS THAN (1354280400) ENGINE = InnoDB,
 PARTITION p201212 VALUES LESS THAN (1356958800) ENGINE = InnoDB,
 PARTITION p201301 VALUES LESS THAN (1359637200) ENGINE = InnoDB,
 PARTITION p201302 VALUES LESS THAN (1362056400) ENGINE = InnoDB,
 PARTITION p201303 VALUES LESS THAN (1364734800) ENGINE = InnoDB,
 PARTITION p201304 VALUES LESS THAN (1367330400) ENGINE = InnoDB )" | mysql -u zabbix -pzabbix zabbix


echo "ALTER TABLE trends_uint PARTITION BY RANGE( clock ) (
 PARTITION p201106 VALUES LESS THAN (1309442400) ENGINE = InnoDB,
 PARTITION p201107 VALUES LESS THAN (1312120800) ENGINE = InnoDB,
 PARTITION p201108 VALUES LESS THAN (1314799200) ENGINE = InnoDB,
 PARTITION p201109 VALUES LESS THAN (1317391200) ENGINE = InnoDB,
 PARTITION p201110 VALUES LESS THAN (1320066000) ENGINE = InnoDB,
 PARTITION p201111 VALUES LESS THAN (1322658000) ENGINE = InnoDB,
 PARTITION p201112 VALUES LESS THAN (1325336400) ENGINE = InnoDB,
 PARTITION p201201 VALUES LESS THAN (1328014800) ENGINE = InnoDB,
 PARTITION p201202 VALUES LESS THAN (1330520400) ENGINE = InnoDB,
 PARTITION p201203 VALUES LESS THAN (1333198800) ENGINE = InnoDB,
 PARTITION p201204 VALUES LESS THAN (1335794400) ENGINE = InnoDB,
 PARTITION p201205 VALUES LESS THAN (1338472800) ENGINE = InnoDB,
 PARTITION p201206 VALUES LESS THAN (1341064800) ENGINE = InnoDB,
 PARTITION p201207 VALUES LESS THAN (1343743200) ENGINE = InnoDB,
 PARTITION p201208 VALUES LESS THAN (1346421600) ENGINE = InnoDB,
 PARTITION p201209 VALUES LESS THAN (1349013600) ENGINE = InnoDB,
 PARTITION p201210 VALUES LESS THAN (1351688400) ENGINE = InnoDB,
 PARTITION p201211 VALUES LESS THAN (1354280400) ENGINE = InnoDB,
 PARTITION p201212 VALUES LESS THAN (1356958800) ENGINE = InnoDB,
 PARTITION p201301 VALUES LESS THAN (1359637200) ENGINE = InnoDB,
 PARTITION p201302 VALUES LESS THAN (1362056400) ENGINE = InnoDB,
 PARTITION p201303 VALUES LESS THAN (1364734800) ENGINE = InnoDB,
 PARTITION p201304 VALUES LESS THAN (1367330400) ENGINE = InnoDB )" | mysql -u zabbix -pzabbix zabbix


or there's a better way, just split partition as follows (less disruptive way, need to re-arrange less data)


echo "ALTER TABLE trends REORGANIZE PARTITION p201202 INTO 
( PARTITION p201106 VALUES LESS THAN (1309442400) ENGINE = InnoDB,
 PARTITION p201107 VALUES LESS THAN (1312120800) ENGINE = InnoDB,
 PARTITION p201108 VALUES LESS THAN (1314799200) ENGINE = InnoDB,
 PARTITION p201109 VALUES LESS THAN (1317391200) ENGINE = InnoDB,
 PARTITION p201110 VALUES LESS THAN (1320066000) ENGINE = InnoDB,
 PARTITION p201111 VALUES LESS THAN (1322658000) ENGINE = InnoDB,
 PARTITION p201112 VALUES LESS THAN (1325336400) ENGINE = InnoDB,
 PARTITION p201201 VALUES LESS THAN (1328014800) ENGINE = InnoDB,
 PARTITION p201202 VALUES LESS THAN (1330520400) ENGINE = InnoDB)" | mysql -u zabbix -pzabbix zabbix


echo "ALTER TABLE trends_uint REORGANIZE PARTITION p201202 INTO 
( PARTITION p201106 VALUES LESS THAN (1309442400) ENGINE = InnoDB,
 PARTITION p201107 VALUES LESS THAN (1312120800) ENGINE = InnoDB,
 PARTITION p201108 VALUES LESS THAN (1314799200) ENGINE = InnoDB,
 PARTITION p201109 VALUES LESS THAN (1317391200) ENGINE = InnoDB,
 PARTITION p201110 VALUES LESS THAN (1320066000) ENGINE = InnoDB,
 PARTITION p201111 VALUES LESS THAN (1322658000) ENGINE = InnoDB,
 PARTITION p201112 VALUES LESS THAN (1325336400) ENGINE = InnoDB,
 PARTITION p201201 VALUES LESS THAN (1328014800) ENGINE = InnoDB,
 PARTITION p201202 VALUES LESS THAN (1330520400) ENGINE = InnoDB)" | mysql -u zabbix -pzabbix zabbix

If the partitioned table data is too large, you can reorganize or rebuild partition to reduce size as follows:

According to my test, after running

 'echo "ALTER TABLE trends_uint REORGANIZE PARTITION p201202 INTO ( PARTITION p201202 VALUES LESS THAN (1330520400) ENGINE = InnoDB)" | mysql -u zabbix -pzabbix zabbix'

, it reduce size to almost half of it.

you can also use rebuild partition just for reducing size.

echo "ALTER TABLE trends_uint rebuild PARTITION p201203" | mysql -uzabbix -pzabbix zabbix


3. import back to the database, for example
mysql -uzabbix -pzabbix zabbix < trends_201201.sql

4. references

mysql -uroot -ppasword -e "select * from mysql.proc"  
mysql -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_monthly_zabbix_partitions();" 
mysql -uroot -ppassword < createpartitionproc.sql 
mysql -uzabbix -pzabbix zabbix -e "show create table trends"
mysql> alter table trends_uint add partition ( partition p201201 VALUES LESS THAN (1328014800) ENGINE = InnoDB);
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition


http://zabbixzone.com/zabbix/history-and-trends/
http://zabbixzone.com/zabbix/partitioning-tables/
http://pastebin.com/nY8WG9Fr
http://zabbixzone.com/zabbix/making-your-frontend-faster/
http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/


mysqldump -u username -ppassword [--no-create-info] [--no-data]  database_name > dump.sql
mysqldump -u username -ppassword --all-databases > dump.sql

selinux should be disabled, otherwise web setup wizard is unable to connect to database
centos 6.2 minumal install installs iptables and selinux by default

strings trends_uint.frm # check partition info
strings trends_uint.par # check partition info

============
[root@db01 trends_partition_data]# mysql -uzabbix -pzabbix zabbix < trends_201201.sql
ERROR 1062 (23000) at line 42: Duplicate entry '100100000089189-1326373200' for key 'PRIMARY'
 solution: delete 1201 month partition data first, then import again.
---------
delete from trends where clock >= 1325336400 and clock < 1328014800;


[root@db01 etc]# mysql -uzabbix -pzabbix zabbix < trends_201201.sql 


echo "delete from trends where clock >= 1325336400 and clock < 1328014800" | mysql -uzabbix -pzabbix zabbix
echo "delete from trends_uint where clock >= 1325336400 and clock < 1328014800" | mysql -uzabbix -pzabbix zabbix
----------

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
/*!40101 SET character_set_client = @saved_cs_client */;

If you encounter some error like character set client, use above in the front of sql data file for insert, remove 'lock table trends write' also

5. some command output
[root@db01 etc]# mysql -uzabbix -pzabbix zabbix -e "alter table trends repair patition p201106"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right

syntax to use near 'patition p201106' at line 1
[root@db01 etc]# mysql -uzabbix -pzabbix zabbix -e "alter table trends repair partition p201106"
+---------------+--------+----------+---------------------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                                |
+---------------+--------+----------+---------------------------------------------------------+
| zabbix.trends | repair | note     | The storage engine for the table doesn't support repair |
+---------------+--------+----------+---------------------------------------------------------+
[root@db01 etc]# mysql -uzabbix -pzabbix zabbix -e "alter table trends analyze partition p201106"
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| zabbix.trends | analyze | status   | OK       |
+---------------+---------+----------+----------+
[root@db01 etc]# mysql -uzabbix -pzabbix zabbix -e "alter table trends check partition p201106"
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| zabbix.trends | check | status   | OK       |
+---------------+-------+----------+----------+
[root@db01 etc]# mysql -uzabbix -pzabbix zabbix -e "alter table trends optimize partition p201106"
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.trends | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.trends | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+

Refer to http://dev.mysql.com/doc/refman/5.1/en/alter-table-partition-operations.html for alter table partition commands

Only a single instance of any one of the following options can be used in a given ALTER TABLE statement: PARTITION BY, ADD PARTITION, DROP PARTITION, TRUNCATE PARTITION, REORGANIZE PARTITION, or COALESCE PARTITION, ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, REMOVE PARTITIONING.

at 5/07/2012 10:47:00 AM

Labels: , ,