informatica:linux:zabbix
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
informatica:linux:zabbix [2014/07/29 06:29] – [Autenticacion LDAP] javi | informatica:linux:zabbix [2015/03/14 12:47] – [Housekeeper y particionado] javi | ||
---|---|---|---|
Line 116: | Line 116: | ||
7. Crear el script de arranque: | 7. Crear el script de arranque: | ||
- | |||
- | **TODO**: el script que se provee para debian creo recordar que no funciona del todo bien, en concreto la parte de " | ||
sudo ln -s / | sudo ln -s / | ||
Line 401: | Line 399: | ||
http:// | http:// | ||
- | Es MUY recomendable: | + | http:// |
+ | |||
+ | https:// | ||
+ | |||
+ | 1. Habilitar file per table | ||
+ | |||
+ | http:// | ||
+ | |||
+ | 2. Cambiar indices (puede ser lento y bloquear inserts): | ||
+ | < | ||
+ | mysql -u zabbix2014 -p | ||
+ | use zabbix2014; | ||
+ | Alter table history_text drop primary key, add index (id), drop index history_text_2, | ||
+ | Alter table history_log drop primary key, add index (id), drop index history_log_2, | ||
+ | </ | ||
+ | |||
+ | 3. Crear procedures: | ||
+ | < | ||
+ | DELIMITER $$ | ||
+ | CREATE PROCEDURE `partition_create`(SCHEMANAME 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_description >= CLOCK; | ||
+ | |||
+ | IF RETROWS = 0 THEN | ||
+ | /* | ||
+ | 1. Print a message indicating that a partition was created. | ||
+ | 2. Create the SQL to create the partition. | ||
+ | 3. Execute the SQL from #2. | ||
+ | */ | ||
+ | SELECT CONCAT( " | ||
+ | SET @SQL = CONCAT( 'ALTER TABLE ', SCHEMANAME, ' | ||
+ | PREPARE STMT FROM @SQL; | ||
+ | EXECUTE STMT; | ||
+ | DEALLOCATE PREPARE STMT; | ||
+ | END IF; | ||
+ | END$$ | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | DELIMITER $$ | ||
+ | CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), | ||
+ | BEGIN | ||
+ | /* | ||
+ | | ||
+ | | ||
+ | | ||
+ | */ | ||
+ | DECLARE done INT DEFAULT FALSE; | ||
+ | DECLARE drop_part_name VARCHAR(16); | ||
+ | |||
+ | /* | ||
+ | Get a list of all the partitions that are older than the date | ||
+ | in DELETE_BELOW_PARTITION_DATE. | ||
+ | a " | ||
+ | */ | ||
+ | DECLARE myCursor CURSOR FOR | ||
+ | SELECT partition_name | ||
+ | FROM information_schema.partitions | ||
+ | WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE; | ||
+ | DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; | ||
+ | |||
+ | /* | ||
+ | | ||
+ | | ||
+ | | ||
+ | */ | ||
+ | SET @alter_header = CONCAT(" | ||
+ | SET @drop_partitions = ""; | ||
+ | |||
+ | /* | ||
+ | Start looping through all the partitions that are too old. | ||
+ | */ | ||
+ | OPEN myCursor; | ||
+ | read_loop: LOOP | ||
+ | FETCH myCursor INTO drop_part_name; | ||
+ | IF done THEN | ||
+ | LEAVE read_loop; | ||
+ | END IF; | ||
+ | SET @drop_partitions = IF(@drop_partitions = "", | ||
+ | END LOOP; | ||
+ | IF @drop_partitions != "" | ||
+ | /* | ||
+ | 1. Build the SQL to drop all the necessary partitions. | ||
+ | 2. Run the SQL to drop the partitions. | ||
+ | 3. Print out the table partitions that were deleted. | ||
+ | */ | ||
+ | SET @full_sql = CONCAT(@alter_header, | ||
+ | PREPARE STMT FROM @full_sql; | ||
+ | EXECUTE STMT; | ||
+ | DEALLOCATE PREPARE STMT; | ||
+ | |||
+ | SELECT CONCAT(SCHEMANAME, | ||
+ | ELSE | ||
+ | /* | ||
+ | No partitions are being deleted, so print out " | ||
+ | that no changes were made. | ||
+ | */ | ||
+ | SELECT CONCAT(SCHEMANAME, | ||
+ | END IF; | ||
+ | END$$ | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | DELIMITER $$ | ||
+ | CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), | ||
+ | BEGIN | ||
+ | DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16); | ||
+ | DECLARE PARTITION_NAME VARCHAR(16); | ||
+ | DECLARE LESS_THAN_TIMESTAMP INT; | ||
+ | DECLARE CUR_TIME INT; | ||
+ | |||
+ | CALL partition_verify(SCHEMA_NAME, | ||
+ | SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), | ||
+ | |||
+ | SET @__interval = 1; | ||
+ | create_loop: | ||
+ | IF @__interval > CREATE_NEXT_INTERVALS THEN | ||
+ | LEAVE create_loop; | ||
+ | END IF; | ||
+ | |||
+ | SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600); | ||
+ | SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, ' | ||
+ | CALL partition_create(SCHEMA_NAME, | ||
+ | SET @__interval=@__interval+1; | ||
+ | END LOOP; | ||
+ | |||
+ | SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), | ||
+ | CALL partition_drop(SCHEMA_NAME, | ||
+ | |||
+ | END$$ | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | DELIMITER $$ | ||
+ | CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), | ||
+ | BEGIN | ||
+ | DECLARE PARTITION_NAME VARCHAR(16); | ||
+ | DECLARE RETROWS INT(11); | ||
+ | DECLARE FUTURE_TIMESTAMP TIMESTAMP; | ||
+ | |||
+ | /* | ||
+ | * Check if any partitions exist for the given SCHEMANAME.TABLENAME. | ||
+ | */ | ||
+ | SELECT COUNT(1) INTO RETROWS | ||
+ | FROM information_schema.partitions | ||
+ | WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL; | ||
+ | |||
+ | /* | ||
+ | * If partitions do not exist, go ahead and partition the table | ||
+ | */ | ||
+ | IF RETROWS = 1 THEN | ||
+ | /* | ||
+ | * Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below which we will store values. | ||
+ | * We begin partitioning based on the beginning of a day. This is because we don't want to generate a random partition | ||
+ | * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could | ||
+ | * end up creating a partition now named " | ||
+ | */ | ||
+ | SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, | ||
+ | SET PARTITION_NAME = DATE_FORMAT(CURDATE(), | ||
+ | |||
+ | -- Create the partitioning query | ||
+ | SET @__PARTITION_SQL = CONCAT(" | ||
+ | SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, | ||
+ | |||
+ | -- Run the partitioning query | ||
+ | PREPARE STMT FROM @__PARTITION_SQL; | ||
+ | EXECUTE STMT; | ||
+ | DEALLOCATE PREPARE STMT; | ||
+ | END IF; | ||
+ | END$$ | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | Este ultimo los gobierna todos, por tanto hay que seleccionar los valores que se adapte a nuestras necesidades: | ||
+ | |||
+ | < | ||
+ | DELIMITER $$ | ||
+ | CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32)) | ||
+ | BEGIN | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | CALL partition_maintenance(SCHEMA_NAME, | ||
+ | END$$ | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | 4. Dar permisos de ejecucion al ultimo procedimiento. Vamos a dar al mismo usuario que puede leer la base de datos de zabbix: | ||
+ | |||
+ | < | ||
+ | GRANT EXECUTE ON PROCEDURE zabbix2014.partition_maintenance_all TO ' | ||
+ | flush privileges; | ||
+ | exit; | ||
+ | </ | ||
+ | |||
+ | 5. Creamos archivo para poder iniciar sesion mysql sin contrasenya: | ||
+ | |||
+ | vim ~/.my.cnf | ||
+ | |||
+ | Con el siguiente contenido: | ||
+ | |||
+ | < | ||
+ | [client] | ||
+ | user=zabbix2014 | ||
+ | password=zabbix2014 | ||
+ | </ | ||
+ | |||
+ | Ajustamos permisos: | ||
+ | |||
+ | chmod 0600 ~/.my.cnf | ||
+ | |||
+ | 6. Creamos un script para ser llamado desde cron: | ||
+ | |||
+ | < | ||
+ | sudo vim / | ||
+ | </ | ||
+ | |||
+ | Con el siguiente contenido (ajustar variables): | ||
+ | |||
+ | < | ||
+ | # | ||
+ | |||
+ | LOG="/ | ||
+ | STRING_START=" | ||
+ | STRING_END=" | ||
+ | MYSQL_USER=" | ||
+ | MYSQL_DB=$MYSQL_USER | ||
+ | MYSQL_PROCEDURE=" | ||
+ | |||
+ | [[ ! $LOG ]]; touch $LOG | ||
+ | DATE=" | ||
+ | |||
+ | echo -e " | ||
+ | |||
+ | #mysql -u $MYSQL_USER -e "CALL partition_maintenance_all(' | ||
+ | #mysql -u $MYSQL_USER -e "CALL partition_maintenance_all('" | ||
+ | mysql -u $MYSQL_USER -e "CALL " | ||
+ | |||
+ | DATE=" | ||
+ | |||
+ | echo -e " | ||
+ | </ | ||
+ | |||
+ | Ajustamos permisos y logs: | ||
+ | |||
+ | < | ||
+ | sudo chown usuario: | ||
+ | chmod +x / | ||
+ | sudo touch / | ||
+ | sudo chown root: | ||
+ | chmod 660 / | ||
+ | </ | ||
- | 1. [[http:// | + | Y anyadimos la entrada a cron, para ser ejecutada, por ejemplo, cada dia: |
- | 2. [[https://www.zabbix.org/wiki/Docs/howto/ | + | 13 4 * * * /opt/partition_maintenance_all.sh >> |
====== Comandos varios ====== | ====== Comandos varios ====== | ||