zabbix 数据库分表操作

一、数据迁移：

1、数据同步到新的磁盘上，先停止mysql(不停止同步的话就有问题)：
  systemctl stop mariadb
  rsync -av /var/lib/mysql/ /mysql_data/

2、修改mysql的配置文件/etc/my.cnf：
  datadir=/mysql_data

3、启动mysql：
  systemctl stop mariadb

二、数据库分表：

1、查看表空间占用情况：
select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';

2、一般都是这几个表很大history、history_str、history_text、history_uint、trends、trends_uint，先为每个表创建空表（数据很大的话执行的时间太长）：
history：
CREATE TABLE `history_20190619` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

history_str：
CREATE TABLE `history_str_20190619` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

history_text：
CREATE TABLE `history_text_20190619` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` text COLLATE utf8_bin NOT NULL,
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

history_uint：
CREATE TABLE `history_uint_20190619` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

trends：
CREATE TABLE `trends_20190619` (
  `itemid` bigint(20) unsigned NOT NULL,
  `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 COLLATE=utf8_bin;

trends_uint：
CREATE TABLE `trends_uint_20190619` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `num` int(11) NOT NULL DEFAULT '0',
  `value_min` bigint(20) unsigned NOT NULL DEFAULT '0',
  `value_avg` bigint(20) unsigned NOT NULL DEFAULT '0',
  `value_max` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3、重命名表：
rename table history to history_back;
rename table history_20190619 to history;

rename table history_str to history_str_back;
rename table history_str_20190619 to history_str;

rename table history_text to history_text_back;
rename table history_text_20190619 to history_text;

rename table history_uint to history_uint_back;
rename table history_uint_20190619 to history_uint;

rename table trends to trends_back;
rename table trends_20190619 to trends;

rename table trends_uint to trends_uint_back;
rename table trends_uint_20190619 to trends_uint;

4、将下面sql复制到partition.sql中，执行：
mysql  -uzabbix -pzabbix zabbix  < partition.sql
5、添加到定时任务：
01 01 * * *  mysql  -uzabbix -pzabbix zabbix -e"CALL partition_maintenance_all('zabbix')" &>/dev/null
6、手动执行：
mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix')" &> /root/partition.log&  

innodb_file_per_table 

分表sql