前提条件是主从同步操作完成（主从同步的前提是两个数据库表结构必须一样）
先看一下mysql配置文件
vi /usr/local/mysql/my.cnf
配置内容：------------------------------------------------------------------------------------
[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8
[mysql]
no-auto-rehash
#default-storage-engine=INNODB
default-character-set=utf8
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysql3306.pid
log-error= /usr/local/mysql/logs/error.log
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 1500
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 2250
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 96M
max_heap_table_size = 96M
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
event_scheduler=ON
###***slowqueryparameters
long_query_time = 0.1
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/logs/slow.log
###***binlogparameters
log-bin=mysql-bin
binlog_cache_size=4M
max_binlog_cache_size=8M
max_binlog_size=1024M
binlog_format=MIXED
expire_logs_days=7
###***master-slavereplicationparameters
server-id=1
binlog-do-db=zabbix #只同步zabbix数据库
innodb_buffer_pool_size = 128M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
#slave-skip-errors=all
[mysqldump]
quick
max_allowed_packet=32M
配置文件下载：https://pan.baidu.com/s/1Qd5oWsAQYESzn3pne8yBkg 密码:wanb
首先删除表
use zabbix
drop table history;
drop table history_text;
drop table history_log;
drop table history_str;
drop table history_uint;
drop table trends;
drop table trends_uint;
新建数据表
范例：(1471910400)==(UNIX_TIMESTAMP("2017-04-01 00:00:00"))
注意：时间设置为自修改日期到月底。
执行以下操作新建表：
CREATE TABLE `history` (
`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
ROW_FORMAT=COMPACT
PARTITION BY RANGE (`clock`)
(PARTITION p20171101 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-1 23:59:00")) ENGINE = InnoDB,
PARTITION p20171102 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-2 23:59:00")) ENGINE = InnoDB,
PARTITION p20171103 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-3 23:59:00")) ENGINE = InnoDB,
PARTITION p20171104 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-4 23:59:00")) ENGINE = InnoDB,
PARTITION p20171105 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-5 23:59:00")) ENGINE = InnoDB,
PARTITION p20171106 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-6 23:59:00")) ENGINE = InnoDB,
PARTITION p20171107 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-7 23:59:00")) ENGINE = InnoDB,
PARTITION p20171108 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-8 23:59:00")) ENGINE = InnoDB,
PARTITION p20171109 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-9 23:59:00")) ENGINE = InnoDB,
PARTITION p20171110 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-10 23:59:00")) ENGINE = InnoDB,
PARTITION p20171111 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-11 23:59:00")) ENGINE = InnoDB,
PARTITION p20171112 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-12 23:59:00")) ENGINE = InnoDB,
PARTITION p20171113 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-13 23:59:00")) ENGINE = InnoDB,
PARTITION p20171114 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-14 23:59:00")) ENGINE = InnoDB,
PARTITION p20171115 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-15 23:59:00")) ENGINE = InnoDB,
PARTITION p20171116 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-16 23:59:00")) ENGINE = InnoDB,
PARTITION p20171117 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-17 23:59:00")) ENGINE = InnoDB,
PARTITION p20171118 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-18 23:59:00")) ENGINE = InnoDB,
PARTITION p20171119 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-19 23:59:00")) ENGINE = InnoDB,
PARTITION p20171120 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-20 23:59:00")) ENGINE = InnoDB,
PARTITION p20171121 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-21 23:59:00")) ENGINE = InnoDB,
PARTITION p20171122 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-22 23:59:00")) ENGINE = InnoDB,
PARTITION p20171123 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-23 23:59:00")) ENGINE = InnoDB,
PARTITION p20171124 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-24 23:59:00")) ENGINE = InnoDB,
PARTITION p20171125 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-25 23:59:00")) ENGINE = InnoDB,
PARTITION p20171126 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-26 23:59:00")) ENGINE = InnoDB,
PARTITION p20171127 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-27 23:59:00")) ENGINE = InnoDB,
PARTITION p20171128 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-28 23:59:00")) ENGINE = InnoDB,
PARTITION p20171129 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-29 23:59:00")) ENGINE = InnoDB,
PARTITION p20171130 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-30 23:59:00")) ENGINE = InnoDB
);
。。。。。。
sql文件根据实际情况修改
链接:https://pan.baidu.com/s/1vUPTQOIAWTHRN1Y1LWnkbQ 密码:cgxx
依次创建新的表结构
修改自动分区脚本中用户名和密码，拷贝自动分区脚本到指定目录下
主从都需要添加
chmod 755 autopartitions.sh
chown mysql.mysql autopartitions.sh
3.设置后台计划任务自动分区
执行crontab -e
01 01 * * * /data/mysqldb/autopartitions.sh
脚本下载
链接:https://pan.baidu.com/s/18N8rkh2vDhltq9DVjq9EhQ 密码:7o0l
是否成功可以参考mysql配置文件中对应库文件下是否有基于日期的文件
主库
从库
