-
@zabbix数据库历史与趋势数据占用优化（mysql存储查询）
1.数据库大小查询
1）数据库大小查询
进入数据库，切换到information_schema 库（这个库存放了其他的数据库的关联信息）
#切入数据库信息库 mysql> information_schema #查询所有数据的大小 mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables; #查询zabbix库的大小 mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix'; #查询zabbix库hosts表大小 mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix' and table_name='hosts';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
2）数据导入不记录binlog
临时关闭binlog日志记录
#查看binlog状态 mysql> show variables like "sql_log_bin"; #临时关闭binlog日志 mysql> set sql_log_bin=0; #临时开启binlog日志 mysql> set sql_log_bin=1; #刷新字段 mysql> flush privileges;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
3）数据库查看语句
#查看数据库的创建语句 mysql> SHOW CREATE DATABASE zabbix #查看表的创建语句 mysql> SHOW CREATE TABLE history #查看表结构 mysql> DESC student- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
4）mysqldump（逻辑备份）
#排除某些表备份数据库（格式：--ignore-table=databases.tables） shell> mysqldump -u"root" -p"password" -h"192.168.10.10" -P"3306" zabbix --single-transaction --ignore-table=zabbix.history > date.sql #排除某些表，压缩备份数据库 shell> mysqldump -u"root" -p"password" zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_uint --ignore-table=zabbix.trends_uint --ignore-table=zabbix.trends | gzip > zabbix.sql.gz #不包含数据导出数据库 shell> mysqldump -u"root" -p"zabbbix" -d databases > databases$(date +%F).sql #导出单个数据表结构（不包含数据） shell> mysqldump -h localhost -u"root" -p"password" -d database table > table$(date +%F).sql #单独备份多张表的表结构(只备份表结构) shell> mysqldump -u"root" -p"password" -d zabbix zabbix.history zabbix.history_uint zabbix.trends_uint zabbix.trends > table$(date +%F).sql #mysql导入数据（注意sql文件的路径） mysql> source /root/zabbix.sql; #使用zcat导入压缩sql.gz数据 zcat ./mysql.sql.gz |mysql -u"root" -p"password" database- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
2.zabbix案例
1）磁盘占用优化
假设zabbix占用空间大，需要优化
zabbix占用大的表 一般为两个表：
history：历史数据存储表
trends：趋势数据存储表#查看zabbix库容量 mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix'; +-----------+ | data | +-----------+ | 1760.81MB | +-----------+ 1 row in set (0.01 sec) #查询zabbix库表大小，并排序 mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='zabbix' order by length desc; +----------------------------+-------------+--------------+------------+------------+------------+ | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | length | TABLE_ROWS | total_size | +----------------------------+-------------+--------------+------------+------------+------------+ | history_uint | 1125138432 | 503332864 | 1628471296 | 9288216 | 1553.031MB | | trends_uint | 360185856 | 0 | 360185856 | 4578474 | 343.500MB | | history | 192610304 | 89915392 | 282525696 | 3206240 | 269.438MB | | trends | 92454912 | 0 | 92454912 | 1207380 | 88.172MB | | history_str | 46219264 | 25247744 | 71467008 | 822685 | 68.156MB | | alerts | 6832128 | 2867200 | 9699328 | 14900 | 9.250MB | | items | 4669440 | 2359296 | 7028736 | 6470 | 6.703MB | | events | 3686400 | 3162112 | 6848512 | 26401 | 6.531MB | | history_text | 3162112 | 16384 | 3178496 | 387 | 3.031MB | | triggers | 2605056 | 294912 | 2899968 | 2860 | 2.766MB | | event_recovery | 1589248 | 540672 | 2129920 | 10712 | 2.031MB | | images | 1589248 | 16384 | 1605632 | 138 | 1.531MB | | functions | 409600 | 475136 | 884736 | 5965 | 0.844MB | | items_applications | 360448 | 458752 | 819200 | 5626 | 0.781MB | | item_preproc | 475136 | 212992 | 688128 | 5571 | 0.656MB | | item_discovery | 360448 | 294912 | 655360 | 3818 | 0.625MB | | auditlog | 311296 | 180224 | 491520 | 2709 | 0.469MB | | graphs_items | 229376 | 196608 | 425984 | 2523 | 0.406MB | | graphs | 180224 | 114688 | 294912 | 863 | 0.281MB | | trigger_depends | 114688 | 147456 | 262144 | 1362 | 0.250MB | | item_rtdata | 196608 | 0 | 196608 | 3021 | 0.188MB | | profiles | 114688 | 81920 | 196608 | 700 | 0.188MB | | hosts | 98304 | 98304 | 196608 | 192 | 0.188MB | | item_application_prototype | 81920 | 114688 | 196608 | 945 | 0.188MB | | hostmacro | 98304 | 81920 | 180224 | 749 | 0.172MB | | item_condition | 114688 | 49152 | 163840 | 897 | 0.156MB | | applications | 81920 | 49152 | 131072 | 894 | 0.125MB | | mappings | 81920 | 49152 | 131072 | 1139 | 0.125MB | | widget_field | 16384 | 98304 | 114688 | 134 | 0.109MB | | media_type | 98304 | 16384 | 114688 | 10 | 0.109MB | | sysmaps_elements | 16384 | 81920 | 98304 | 0 | 0.094MB | | auditlog_details | 81920 | 16384 | 98304 | 476 | 0.094MB | | trigger_discovery | 65536 | 16384 | 81920 | 780 | 0.078MB | | application_template | 49152 | 32768 | 81920 | 417 | 0.078MB | | sysmaps | 16384 | 65536 | 81920 | 0 | 0.078MB | | event_tag | 65536 | 16384 | 81920 | 565 | 0.078MB | | httptest | 16384 | 65536 | 81920 | 2 | 0.078MB | | sysmaps_links | 16384 | 49152 | 65536 | 0 | 0.063MB | | problem | 16384 | 49152 | 65536 | 95 | 0.063MB | | scripts | 16384 | 49152 | 65536 | 3 | 0.063MB | | screens_items | 49152 | 16384 | 65536 | 211 | 0.063MB | | group_prototype | 16384 | 49152 | 65536 | 14 | 0.063MB | | acknowledges | 16384 | 49152 | 65536 | 17 | 0.063MB | | event_suppress | 16384 | 49152 | 65536 | 0 | 0.063MB | | escalations | 16384 | 49152 | 65536 | 9 | 0.063MB | | host_discovery | 16384 | 32768 | 49152 | 4 | 0.047MB | | slideshow_user | 16384 | 32768 | 49152 | 0 | 0.047MB | | correlation | 16384 | 32768 | 49152 | 0 | 0.047MB | | proxy_dhistory | 16384 | 32768 | 49152 | 0 | 0.047MB | | slides | 16384 | 32768 | 49152 | 0 | 0.047MB | | sysmaps_link_triggers | 16384 | 32768 | 49152 | 0 | 0.047MB | | application_prototype | 16384 | 32768 | 49152 | 141 | 0.047MB | | icon_mapping | 16384 | 32768 | 49152 | 0 | 0.047MB | | opcommand_hst | 16384 | 32768 | 49152 | 0 | 0.047MB | | service_alarms | 16384 | 32768 | 49152 | 0 | 0.047MB | | sysmap_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB | | application_discovery | 16384 | 32768 | 49152 | 84 | 0.047MB | | icon_map | 16384 | 32768 | 49152 | 0 | 0.047MB | | dservices | 16384 | 32768 | 49152 | 35 | 0.047MB | | opcommand_grp | 16384 | 32768 | 49152 | 0 | 0.047MB | | media | 16384 | 32768 | 49152 | 3 | 0.047MB | | sysmap_user | 16384 | 32768 | 49152 | 0 | 0.047MB | | httptestitem | 16384 | 32768 | 49152 | 6 | 0.047MB | | services_links | 16384 | 32768 | 49152 | 0 | 0.047MB | | drules | 16384 | 32768 | 49152 | 0 | 0.047MB | | optemplate | 16384 | 32768 | 49152 | 0 | 0.047MB | | hosts_templates | 16384 | 32768 | 49152 | 191 | 0.047MB | | actions | 16384 | 32768 | 49152 | 7 | 0.047MB | | config | 16384 | 32768 | 49152 | 0 | 0.047MB | | maintenances_windows | 16384 | 32768 | 49152 | 0 | 0.047MB | | screens | 16384 | 32768 | 49152 | 53 | 0.047MB | | opmessage_usr | 16384 | 32768 | 49152 | 6 | 0.047MB | | hosts_groups | 16384 | 32768 | 49152 | 196 | 0.047MB | | users_groups | 16384 | 32768 | 49152 | 4 | 0.047MB | | maintenances_hosts | 16384 | 32768 | 49152 | 0 | 0.047MB | | screen_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB | | opmessage_grp | 16384 | 32768 | 49152 | 4 | 0.047MB | | httpstepitem | 16384 | 32768 | 49152 | 6 | 0.047MB | | autoreg_host | 16384 | 32768 | 49152 | 0 | 0.047MB | | maintenances_groups | 16384 | 32768 | 49152 | 0 | 0.047MB | | screen_user | 16384 | 32768 | 49152 | 0 | 0.047MB | | sysmap_element_trigger | 16384 | 32768 | 49152 | 0 | 0.047MB | | dashboard_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB | | maintenances | 16384 | 32768 | 49152 | 0 | 0.047MB | | rights | 16384 | 32768 | 49152 | 23 | 0.047MB | | slideshows | 16384 | 32768 | 49152 | 0 | 0.047MB | | dashboard_user | 16384 | 32768 | 49152 | 1 | 0.047MB | | task | 16384 | 32768 | 49152 | 0 | 0.047MB | | slideshow_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB | | opgroup | 16384 | 32768 | 49152 | 1 | 0.047MB | | interface | 16384 | 32768 | 49152 | 28 | 0.047MB | | tag_filter | 16384 | 32768 | 49152 | 0 | 0.047MB | | operations | 16384 | 16384 | 32768 | 16 | 0.031MB | | lld_macro_path | 16384 | 16384 | 32768 | 58 | 0.031MB | | proxy_history | 16384 | 16384 | 32768 | 0 | 0.031MB | | opconditions | 16384 | 16384 | 32768 | 0 | 0.031MB | | corr_operation | 16384 | 16384 | 32768 | 0 | 0.031MB | | services | 16384 | 16384 | 32768 | 0 | 0.031MB | | globalmacro | 16384 | 16384 | 32768 | 0 | 0.031MB | | proxy_autoreg_host | 16384 | 16384 | 32768 | 0 | 0.031MB | | sessions | 16384 | 16384 | 32768 | 175 | 0.031MB | | services_times | 16384 | 16384 | 32768 | 0 | 0.031MB | | expressions | 16384 | 16384 | 32768 | 10 | 0.031MB | | corr_condition | 16384 | 16384 | 32768 | 0 | 0.031MB | | widget | 16384 | 16384 | 32768 | 15 | 0.031MB | | problem_tag | 16384 | 16384 | 32768 | 10 | 0.031MB | | opcommand | 16384 | 16384 | 32768 | 0 | 0.031MB | | history_log | 16384 | 16384 | 32768 | 56 | 0.031MB | | config_autoreg_tls | 16384 | 16384 | 32768 | 0 | 0.031MB | | valuemaps | 16384 | 16384 | 32768 | 102 | 0.031MB | | sysmap_url | 16384 | 16384 | 32768 | 0 | 0.031MB | | httptest_field | 16384 | 16384 | 32768 | 0 | 0.031MB | | dhosts | 16384 | 16384 | 32768 | 35 | 0.031MB | | media_type_param | 16384 | 16384 | 32768 | 123 | 0.031MB | | corr_condition_group | 16384 | 16384 | 32768 | 0 | 0.031MB | | usrgrp | 16384 | 16384 | 32768 | 6 | 0.031MB | | sysmap_shape | 16384 | 16384 | 32768 | 0 | 0.031MB | | dchecks | 16384 | 16384 | 32768 | 1 | 0.031MB | | group_discovery | 16384 | 16384 | 32768 | 0 | 0.031MB | | conditions | 16384 | 16384 | 32768 | 51 | 0.031MB | | sysmap_element_url | 16384 | 16384 | 32768 | 0 | 0.031MB | | users | 16384 | 16384 | 32768 | 3 | 0.031MB | | opmessage | 16384 | 16384 | 32768 | 13 | 0.031MB | | httpstep_field | 16384 | 16384 | 32768 | 0 | 0.031MB | | interface_discovery | 16384 | 16384 | 32768 | 0 | 0.031MB | | host_tag | 16384 | 16384 | 32768 | 5 | 0.031MB | | httpstep | 16384 | 16384 | 32768 | 2 | 0.031MB | | graph_theme | 16384 | 16384 | 32768 | 4 | 0.031MB | | regexps | 16384 | 16384 | 32768 | 5 | 0.031MB | | trigger_tag | 16384 | 16384 | 32768 | 85 | 0.031MB | | maintenance_tag | 16384 | 16384 | 32768 | 0 | 0.031MB | | hstgrp | 16384 | 16384 | 32768 | 25 | 0.031MB | | dashboard | 16384 | 16384 | 32768 | 2 | 0.031MB | | graph_discovery | 16384 | 16384 | 32768 | 235 | 0.031MB | | globalvars | 16384 | 0 | 16384 | 0 | 0.016MB | | ids | 16384 | 0 | 16384 | 56 | 0.016MB | | corr_condition_tagvalue | 16384 | 0 | 16384 | 0 | 0.016MB | | corr_condition_tagpair | 16384 | 0 | 16384 | 0 | 0.016MB | | timeperiods | 16384 | 0 | 16384 | 0 | 0.016MB | | housekeeper | 16384 | 0 | 16384 | 0 | 0.016MB | | task_remote_command_result | 16384 | 0 | 16384 | 0 | 0.016MB | | corr_condition_tag | 16384 | 0 | 16384 | 0 | 0.016MB | | task_remote_command | 16384 | 0 | 16384 | 0 | 0.016MB | | task_close_problem | 16384 | 0 | 16384 | 0 | 0.016MB | | dbversion | 16384 | 0 | 16384 | 0 | 0.016MB | | task_check_now | 16384 | 0 | 16384 | 0 | 0.016MB | | task_acknowledge | 16384 | 0 | 16384 | 0 | 0.016MB | | opinventory | 16384 | 0 | 16384 | 0 | 0.016MB | | host_inventory | 16384 | 0 | 16384 | 0 | 0.016MB | +----------------------------+-------------+--------------+------------+------------+------------+ 149 rows in set (0.01 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
2）优化表的大小
清理表的村存储数据：
表数据只需要保留一个月，删除某个表超时一个月的数据；
删除表数据，需要获取获取时间戳，删除表数据后，执行optimize table table_name 立刻释放磁盘空间#删除之前的1656864000存储的数据，通过时间戳换算，数据过大，可能很慢 mysql> delete from zabbix.history_uint where clock < 1656864000; Query OK, 184017 rows affected (1 min 13.99 sec) #立即释放磁盘占用空间 mysql> optimize table zabbix.history_uint; +---------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------+----------+----------+-------------------------------------------------------------------+ | zabbix.history_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead | | zabbix.history_uint | optimize | status | OK | +---------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (3 min 7.93 sec) #继续查看表的大小（确认到表已经减少了） mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix' and table_name='history_uint'; +----------+ | data | +----------+ | 542.98MB | +----------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
3）服务器上查询确认表大小
筛选 查看数据库大小
#文件大小查看(查看zabbix库中表的ibd文件大小) shell> du -s /var/lib/mysql/zabbix/* |sort -rn |head -10 847876 /var/lib/mysql/zabbix/history_uint.ibd 368644 /var/lib/mysql/zabbix/trends_uint.ibd 290820 /var/lib/mysql/zabbix/history.ibd 135172 /var/lib/mysql/zabbix/history_str.ibd 110596 /var/lib/mysql/zabbix/trends.ibd 57348 /var/lib/mysql/zabbix/history_text.ibd 18436 /var/lib/mysql/zabbix/alerts.ibd 14340 /var/lib/mysql/zabbix/events.ibd 14336 /var/lib/mysql/zabbix/items.ibd 10244 /var/lib/mysql/zabbix/event_recovery.ibd- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
4）数据库分区自动化脚本
#数据库分区脚本 shell> wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh #执行脚本即可（脚本配置无问题） shell> ./partitiontables_gt_zbx34.sh #查看分区后的表数据（history） mysql> use zabbix; mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='history';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
脚本基础应用配置：
#优化脚本（基础配置） shell> egrep "^[^#]" partitiontables_gt_zbx34.sh EMAIL="zeny@foxmail.com" #邮箱 daily_history_min=30 #历史数据存储保留时间 monthly_history_min=12 #趋势数据存储时间配置 first_year=`date +"%Y"` last_year=$first_year cur_month=`date +"%m"|sed 's/^0*//'` if [ $cur_month -eq 12 ]; then last_year=$((first_year+1)) cur_month=1 fi y=`date +"%Y"` SQL="/tmp/partition.sql" PATHTOCRON="/usr/local/zabbix/cron.d" PATHTOMAILBIN="/usr/bin/mail" DUMP_FILE=/tmp/zabbix.sql function usage { cat <<_EOF_ $0 [-h host][-u user][-p password][-d min_days][-y startyear][-n][-s][-e email_address][-b] -h host database host -u user db user -p password user password -d min_days Minimum number of days of history to keep (default: $daily_history_min) -m min_months Minimum number of months to keep trends (default: $monthly_history_min) -y startyear First year to set up with partitions -n noninteractive Run without questions - careful, make sure you know what is going to happen. Needs my.cnf with correct permissions. -b backup Create backup of DB in $DUMP_FILE before alterations (only works with non-interactive mode, -n) -s simulate Create SQL file that would be executed for examination ($SQL) -e email Email address to receive partition update report (default: $EMAIL) After running this script, don't forget to disable housekeeping if you didn't have the script disable it, and add the following cronjob ### Option: DisableHousekeeping # If set to 1, disables housekeeping. # # Mandatory: no # Range: 0-1 ################### Uncomment and change the following line to 1 in ################### Then restart the zabbix server DisableHousekeeping=1 Cron job 0 0 * * * $PATHTOCRON/housekeeping.sh _EOF_ exit } DBHOST=localhost #数据库连接地址 DBUSER=zabbix #数据库连接用户 DBPASS=zabbix #数据库登录密码 SIMULATE=0 NONINTERACTIVE=0 BACKUP=0 while getopts "m:nsbe:h:u:p:d:y:?h" flag; do case $flag in h) DBHOST=$OPTARG ;; u) DBUSER=$OPTARG ;; p) DBPASS=$OPTARG ;; e) EMAIL=$OPTARG ;; s) SIMULATE=1 ;; n) NONINTERACTIVE=1 ;; b) BACKUP=1 ;; d) h=$OPTARG if [ $h -gt 0 ] 2>/dev/null; then daily_history_min=$h else echo "Invalid daily history min, exiting" exit 1 fi ;; m) h=$OPTARG if [ $h -gt 0 ] 2>/dev/null; then monthly_history_min=$h else echo "Invalid monthly history min, exiting" exit 1 fi ;; y) yy=$OPTARG if [ $yy -lt $y -a $yy -gt 2000 ] 2>/dev/null; then first_year=$yy else echo "Invalid year, exiting" exit 1 fi ;; ?|h) usage ;; esac done shift $((OPTIND-1)) if [ $NONINTERACTIVE != 1 ]; then echo "Ready to partition tables." fi if [ $SIMULATE = 0 ]; then if [ $NONINTERACTIVE = 1 ]; then mysql -B -h $DBHOST -e "GRANT CREATE ROUTINE ON zabbix.* TO '$DBUSER'@'localhost';" mysql -h $DBHOST -e "GRANT LOCK TABLES ON zabbix.* TO '$DBUSER'@'$DBHOST' IDENTIFIED BY '$DBPASS';" if [ $BACKUP = 1 ]; then mysqldump --opt -h $DBHOST -u $DBUSER -p$DBPASS zabbix --result-file=$DUMP_FILE rc=$? if [ $rc -ne 0 ]; then echo "Error during mysqldump, exit code: $rc" fi fi else echo -e "\nReady to update permissions of Zabbix user to create routines\n" echo -n "Enter root DB user: " read DBADMINUSER echo -n "Enter $DBADMINUSER password: " read DBADMINPASS mysql -B -h $DBHOST -u $DBADMINUSER -p$DBADMINPASS -e "GRANT CREATE ROUTINE ON zabbix.* TO '$DBUSER'@'localhost';" echo -e "\n" echo -ne "\nDo you want to backup the database (recommended) (Y/n): " read yn if [ "$yn" != "n" -a "$yn" != "N" ]; then echo -e "\nEnter output file, press return for default of $DUMP_FILE" read df [ "$df" != "" ] && DUMP_FILE=$df # # Lock tables is needed for a good mysqldump # echo "GRANT LOCK TABLES ON zabbix.* TO '${DBUSER}'@'${DBHOST}' IDENTIFIED BY '${DBPASS}';" | mysql -h${DBHOST} -u${DBADMINUSER} --password=${DBADMINPASS} mysqldump --opt -h ${DBHOST} -u ${DBUSER} -p${DBPASS} zabbix --result-file=${DUMP_FILE} rc=$? if [ $rc -ne 0 ]; then echo "Error during mysqldump, rc: $rc" echo "Do you wish to continue (y/N): " read yn [ "yn" != "y" -a "$yn" != "Y" ] && exit else echo "Mysqldump succeeded!, proceeding with upgrade..." fi else echo "Are you certain you have a backup (y/N): " read yn [ "$yn" != 'y' -a "$yn" != "Y" ] && exit fi fi fi if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -e "\n\nReady to proceed:" echo -e "\nStarting yearly partioning at: $first_year" echo "and ending at: $last_year" echo "With $daily_history_min days of daily history" echo -e "\n\nReady to proceed (Y/n): " read yn [ "$yn" = 'n' -o "$yn" = "N" ] && exit fi DAILY="history history_log history_str history_text history_uint" DAILY_IDS="itemid id itemid id itemid" MONTHLY="trends trends_uint" MONTHLY_IDS="" TABLES="$DAILY $MONTHLY" IDS="$DAILY_IDS $MONTHLY_IDS" if [ $NONINTERACTIVE != 1 ]; then echo "Use zabbix; SELECT 'Altering tables';" >$SQL else echo "Use zabbix;" >$SQL fi cnt=0 for i in $TABLES; do if [ $NONINTERACTIVE != 1 ]; then echo "Altering table: $i" echo "SELECT '$i';" >>$SQL fi cnt=$((cnt+1)) case $i in history_log) #echo "ALTER TABLE $i DROP KEY history_log_2;" >>$SQL #echo "ALTER TABLE $i ADD KEY history_log_2(itemid, id);" >>$SQL #echo "ALTER TABLE $i DROP PRIMARY KEY ;" >>$SQL #id=`echo $IDS | cut -f$cnt -d" "` #echo "ALTER TABLE $i ADD KEY ${i}id ($id);" >>$SQL ;; history_text) #echo "ALTER TABLE $i DROP KEY history_text_2;" >>$SQL #echo "ALTER TABLE $i ADD KEY history_text_2 (itemid, clock);" >>$SQL #echo "ALTER TABLE $i DROP PRIMARY KEY ;" >>$SQL #id=`echo $IDS | cut -f$cnt -d" "` #echo "ALTER TABLE $i ADD KEY ${i}id ($id);" >>$SQL ;; esac done echo -en "\n" >>$SQL for i in $MONTHLY; do if [ $NONINTERACTIVE != 1 ]; then echo "Creating monthly partitions for table: $i" echo "SELECT '$i';" >>$SQL fi echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL for y in `seq $first_year $last_year`; do last_month=12 [ $y -eq $last_year ] && last_month=$((cur_month+1)) for m in `seq 1 $last_month`; do [ $m -lt 10 ] && m="0$m" ms=`date +"%Y-%m-01" -d "$m/01/$y +1 month"` pname="p${y}${m}" echo -n "PARTITION $pname VALUES LESS THAN (UNIX_TIMESTAMP(\"$ms 00:00:00\"))" >>$SQL [ $m -ne $last_month -o $y -ne $last_year ] && echo -n "," >>$SQL echo -ne "\n" >>$SQL done done echo ");" >>$SQL done for i in $DAILY; do if [ $NONINTERACTIVE != 1 ]; then echo "Creating daily partitions for table: $i" echo "SELECT '$i';" >>$SQL fi echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL for d in `seq -$daily_history_min 2`; do ds=`date +"%Y-%m-%d" -d "$d day +1 day"` pname=`date +"%Y%m%d" -d "$d day"` echo -n "PARTITION p$pname VALUES LESS THAN (UNIX_TIMESTAMP(\"$ds 00:00:00\"))" >>$SQL [ $d -ne 2 ] && echo -n "," >>$SQL echo -ne "\n" >>$SQL done echo ");" >>$SQL done if [ $NONINTERACTIVE != 1 ]; then cat >>$SQL <<_EOF_ SELECT "Installing procedures"; _EOF_ fi cat >>$SQL <<_EOF_ /************************************************************** MySQL Auto Partitioning Procedure for Zabbix 1.8 http://zabbixzone.com/zabbix/partitioning-tables/ Author: Ricardo Santos (rsantos at gmail.com) Version: 20110518 **************************************************************/ DELIMITER // DROP PROCEDURE IF EXISTS zabbix.create_zabbix_partitions; // CREATE PROCEDURE zabbix.create_zabbix_partitions () BEGIN _EOF_ for i in $DAILY; do echo " CALL zabbix.create_next_partitions(\"zabbix\",\"$i\");" >>$SQL echo " CALL zabbix.drop_old_partitions(\"zabbix\",\"$i\");" >>$SQL done echo -en "\n" >>$SQL for i in $MONTHLY; do echo " CALL zabbix.create_next_monthly_partitions(\"zabbix\",\"$i\");" >>$SQL echo " CALL zabbix.drop_old_monthly_partitions(\"zabbix\",\"$i\");" >>$SQL done cat >>$SQL <<_EOF_ END // DROP PROCEDURE IF EXISTS zabbix.create_next_partitions; // CREATE PROCEDURE zabbix.create_next_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE NEXTCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @totaldays = 7; SET @i = 1; createloop: LOOP SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY); SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' ); SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00')); CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK ); SET @i=@i+1; IF @i > @totaldays THEN LEAVE createloop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.drop_old_partitions; // CREATE PROCEDURE zabbix.drop_old_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE OLDCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @mindays = $daily_history_min; SET @maxdays = @mindays+4; SET @i = @maxdays; droploop: LOOP SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY); SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' ); CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME ); SET @i=@i-1; IF @i <= @mindays THEN LEAVE droploop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.create_next_monthly_partitions; // CREATE PROCEDURE zabbix.create_next_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE NEXTCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @totalmonths = 3; SET @i = 1; createloop: LOOP SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH); SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' ); SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00')); CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK ); SET @i=@i+1; IF @i > @totalmonths THEN LEAVE createloop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.drop_old_monthly_partitions; // CREATE PROCEDURE zabbix.drop_old_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE OLDCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @minmonths = $monthly_history_min; SET @maxmonths = @minmonths+24; SET @i = @maxmonths; droploop: LOOP SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH); SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' ); CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME ); SET @i=@i-1; IF @i <= @minmonths THEN LEAVE droploop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.create_partition; // CREATE PROCEDURE zabbix.create_partition (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int) BEGIN DECLARE RETROWS int; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME; IF RETROWS = 0 THEN SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg; SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' ); PREPARE STMT FROM @sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END // DROP PROCEDURE IF EXISTS zabbix.drop_partition; // CREATE PROCEDURE zabbix.drop_partition (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64)) BEGIN DECLARE RETROWS int; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME; IF RETROWS = 1 THEN SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg; SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' DROP PARTITION ', PARTITIONNAME, ';' ); PREPARE STMT FROM @sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END // DELIMITER ; _EOF_ if [ $SIMULATE = 1 ]; then exit 0 fi if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -e "\n\nReady to apply script to database, this may take a while.(Y/n): " read yn fi if [ "$yn" != "n" -a "$yn" != "N" ]; then mysql --skip-column-names -h ${DBHOST} -u ${DBUSER} -p${DBPASS} <$SQL fi conf=/etc/zabbix/zabbix_server.conf if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -e "\nIf Zabbix Version = 2.0 \nDo you want to update the /etc/zabbix/zabbix_server.conf" echo -n "to disable housekeeping (Y/n): " read yn fi if [ "$yn" != "n" -a "$yn" != "N" ]; then cp $conf ${conf}.bak sed -i "s/^# DisableHousekeeping=0/DisableHousekeeping=1/" $conf sed -i "s/^DisableHousekeeping=0/DisableHousekeeping=1/" $conf /etc/init.d/zabbix-server stop sleep 5 /etc/init.d/zabbix-server start 2>&1 > /dev/null fi tmpfile=/tmp/cron$$ if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -ne "\nDo you want to update the crontab (Y/n): " read yn fi if [ "$yn" != "n" -a "$yn" != "N" ]; then where= while [ "$where" = "" ]; do if [ $NONINTERACTIVE = 1 ]; then where='Y' else echo "The crontab entry can be either in /etc/cron.daily, or added" echo -e "to the crontab for root\n" echo -n "Do you want to add this to the /etc/cron.daily directory (Y/n): " read where fi [ "$where" = "" -o "$where" = "y" ] && where="Y" if [ "$where" != "y" -a "$where" != "Y" -a "$where" != "n" -a "$where" != "N" ]; then where="" echo "Response not recognized, please try again" fi done if [ $NONINTERACTIVE != 1 ]; then echo -en "\nEnter email of who should get the daily housekeeping reports: " read mailto fi [ "$mailto" = "" ] && mailto=$EMAIL mkdir -p $PATHTOCRON cat >$PATHTOCRON/housekeeping.sh <<_EOF_ MAILTO=$mailto tmpfile=/tmp/housekeeping\$\$ date >\$tmpfile /usr/bin/mysql --skip-column-names -B -h localhost -u${DBUSER} -p${DBPASS} zabbix -e "CALL create_zabbix_partitions();" >>\$tmpfile 2>&1 $PATHTOMAILBIN -s "Zabbix MySql Partition Housekeeping" \$MAILTO <\$tmpfile rm -f \$tmpfile _EOF_ chmod +x $PATHTOCRON/housekeeping.sh chown -R zabbix.zabbix /usr/local/zabbix if [ "$where" = "Y" ]; then cat >/etc/cron.daily/zabbixhousekeeping <<_EOF_ $PATHTOCRON/housekeeping.sh _EOF_ chmod +x /etc/cron.daily/zabbixhousekeeping else crontab -l >$tmpfile cat >>$tmpfile <<_EOF_ 0 0 * * * $PATHTOCRON/housekeeping.sh _EOF_ crontab $tmpfile rm $tmpfile fi fi- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
-
相关阅读:
Python 哪种方式循环最快，或许颠覆你的认知
search——single list
elasticsearch-6.8.5升级至6.8.22
在Ubuntu 20.04上安装和配置MySQL 8：详细指南和远程访问设置
gin异常处理实践
K8S 1.20 弃用 Docker 评估之 Docker CLI 的替代产品
Android 屏幕适配
sed与awk
基于Python的ArcGIS流程化数据处理及应用开发
Java：三种代理模式示例
- 原文地址：https://blog.csdn.net/weixin_55972781/article/details/126688378