mysql分表-zabbix3.x
脚本起初参考位置：
脚本下载地址：
脚本说明：
作者：
itnihao
脚本针对的是zabbix2,x，我用的是zabbix3.x ，表结构发生了变化
变化如下：
mysql> show create table history_log\G;
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`), #zabbix3.x 没有这两行了，可是分表的时候，修改的就是这两行
UNIQUE KEY `history_log_2` (`itemid`,`id`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> show create table history_text \G;
*************************** 1. row ***************************
Table: history_text
Create Table: CREATE TABLE `history_text` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`), #zabbix3.x 没有这两行了，可是分表的时候，修改的就是这两行
UNIQUE KEY `history_text_2` (`itemid`,`id`),
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
我使用的方法是（我的zabbix是全新的）
删除这两个表，新建了符合脚本的表，这样搞后，分表成功了，测试也没法问题，但是不知道后续会不会出什么问题，不知道这两行的作用。
脚本如下：
根据自己的实际环境，脚本要修改三个地方
1.数据库的用户名和密码 默认是 zabbix zabbix
2.zabbix_server.conf的路径 默认是 /etc/zabbix/
3.修改数据存放目录
4.. /etc/init.d/zabbix-server 控制文件要存在 并且就是这个名字
中途 切莫轻易中断脚本，容易导致表数据损坏
检查完毕后执行脚本
脚本执行如下：
[root@wang ~]# ./partitiontables.sh
Ready to partition tables.
Ready to update permissions of Zabbix user to create routines
Enter root DB user: zabbix #输入数据库的用户名
Enter zabbix password: 123.com #密码
Warning: Using a password on the command line interface can be insecure.
Do you want to backup the database (recommended) (Y/n): y #是否备份数据库
Enter output file, press return for default of /tmp/zabbix.sql #输入备份完整路径，默认为/tmp/zabbix.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1410 (42000) at line 1: You are not allowed to create a user with GRANT #不用理会
Warning: Using a password on the command line interface can be insecure.
Mysqldump succeeded!, proceeding with upgrade...
Ready to proceed: #列出history保存的天数
Starting yearly partioning at: 2017
and ending at: 2017
With 90 days of daily history
Ready to proceed (Y/n): #那些表将会被修改
y
Altering table: history
Altering table: history_log
Altering table: history_str
Altering table: history_text
Altering table: history_uint
Altering table: trends
Altering table: trends_uint
Creating monthly partitions for table: trends
Creating monthly partitions for table: trends_uint
Creating daily partitions for table: history
Creating daily partitions for table: history_log
Creating daily partitions for table: history_str
Creating daily partitions for table: history_text
Creating daily partitions for table: history_uint
Ready to apply script to database, this may take a while.(Y/n): #选择是否修改那些表
y
Warning: Using a password on the command line interface can be insecure.
Altering tables
history
history_log
history_str
history_text
history_uint
trends
trends_uint
trends
trends_uint
history
history_log
history_str
history_text
history_uint
Installing procedures
Do you want to update the /usr/local/zabbix-3.2.1/etc/zabbix_server.conf
to disable housekeeping (Y/n): y #选择是否修改配置文件
#会自动重启
Do you want to update the crontab (Y/n): y #选择是否添加计划任务
The crontab entry can be either in /etc/cron.daily, or added
to the crontab for root
Do you want to add this to the /etc/cron.daily directory (Y/n): y
Enter email of who should get the daily housekeeping reports:
执行完毕后测试 是否成功
[root@wang ~]# mysql --skip-column-names -B -h localhost -uzabbix -p123.com zabbix -e "CALL create_zabbix_partitions();"
Warning: Using a password on the command line interface can be insecure.
create_partition(zabbix,history,p20170709,1499616000)
create_partition(zabbix,history,p20170710,1499702400)
create_partition(zabbix,history,p20170711,1499788800)
create_partition(zabbix,history,p20170712,1499875200)
create_partition(zabbix,history,p20170713,1499961600)
create_partition(zabbix,history_log,p20170709,1499616000)
create_partition(zabbix,history_log,p20170710,1499702400)
create_partition(zabbix,history_log,p20170711,1499788800)
create_partition(zabbix,history_log,p20170712,1499875200)
create_partition(zabbix,history_log,p20170713,1499961600)
create_partition(zabbix,history_str,p20170709,1499616000)
create_partition(zabbix,history_str,p20170710,1499702400)
create_partition(zabbix,history_str,p20170711,1499788800)
create_partition(zabbix,history_str,p20170712,1499875200)
create_partition(zabbix,history_str,p20170713,1499961600)
create_partition(zabbix,history_text,p20170709,1499616000)
create_partition(zabbix,history_text,p20170710,1499702400)
create_partition(zabbix,history_text,p20170711,1499788800)
create_partition(zabbix,history_text,p20170712,1499875200)
create_partition(zabbix,history_text,p20170713,1499961600)
create_partition(zabbix,history_uint,p20170709,1499616000)
create_partition(zabbix,history_uint,p20170710,1499702400)
create_partition(zabbix,history_uint,p20170711,1499788800)
create_partition(zabbix,history_uint,p20170712,1499875200)
create_partition(zabbix,history_uint,p20170713,1499961600)
create_partition(zabbix,trends,p201709,1506787200)
create_partition(zabbix,trends,p201710,1509465600)
create_partition(zabbix,trends_uint,p201709,1506787200)
create_partition(zabbix,trends_uint,p201710,1509465600)
以上是针对一个新的zabbix的时候，如果是一个工作有段时间的zabbix，数据库里的数据量较大，执行时间天知道有多长，建议先清空历史数据（情况前完整来个备份吧）
mysql> use zabbix;
mysql> truncate table history;
mysql> optimize table history;
mysql> truncate table history_str;
mysql> optimize table history_str;
mysql> truncate table history_unit;
mysql> optimize table history_unit;
mysql> truncate table trends;
mysql> optimize table trends;
mysql> truncate table trends_unit;
mysql> optimize table trends_unit;
