MySQLConfiguration

mysqld_safe

mysqld_safe is a process that starts mysqld with some extra security functions. On BSD this is the default start process used in the start scripts “/usr/local/etc/rc.d/mysql-server”.

One of the flags it uses is –defaults-extra-file=/var/db/mysql/my.cnf. This is an option file to be read in addition to the usual option files used for mysqld_safe, not mysqld. If it doesn’t exist but the present of another my.cnf exists it doesn’t matter.

The my.cnf file is set in the /usr/local/bin/mysqld_safe script with the following lines:

MY_BASEDIR_VERSION=/usr/local
DATADIR=/var/db/mysql
  if test -r "$MY_BASEDIR_VERSION/etc/my.cnf" && test -r "$DATADIR/my.cnf"
  then
    log_error "WARNING: Found two instances of my.cnf -
$MY_BASEDIR_VERSION/etc/my.cnf and
$DATADIR/my.cnf
IGNORING $DATADIR/my.cnf"

mysqld

log-bin	= mysql-bin
Expire_logs_days	= 1
Transaction-isolation	= READ-COMMITTED
Binlog_format	= MIXED
Event_scheduler	= ON
Innodb_boofer_pool	= 4G
Innodb_file_per_table	= ON
Innodb_flush_logs_at_trx_commit	= 2
Innodb_doublewrite	= false
Innodb_flush_method	= O_DIRECT
Innodb_max_ditry_pages	= 0
Innodb_support_xa	= false
Max_allowed_packets	= 64M

Add MySQL history tables partitioning

Maintenance of partitions should be done from root mysql account so zabbix@ account does not need to have granted permissions allowing create and drop partitions.
To add partitioning need to be done:

Importing partitioning.sql file containing SQL procedures used on create and maintainance partitions.

This file needs to be imported to root account by:

cat partitioning.sql mysql -uroot

Short description imported procedures:

1. partition_create - create a partition on the given table in the given <schema>.partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
2. partition_maintenance - it is responsible for parsing the given parameters and then creating/dropping partitions as needed
3. partition_verify - checks if partitioning is enabled on the given table in the given schema. If it is not enable, it creates a single partition.
   1. Add partitioning can be added by execute in mysql command queries:

      CALL partition_maintenance('zabbix', 'history', 14, 24, 7);
      CALL partition_maintenance('zabbix', 'history_uint', 14, 24, 7);
      ALTER TABLE history_log drop PRIMARY KEY, ADD INDEX (id), DROP INDEX history_log_2, ADD INDEX history_log_2 (itemid, id);
      CALL partition_maintenance('zabbix', 'history_log', 14, 24, 7);
      CALL partition_maintenance('zabbix', 'history_str', 14, 24, 7);
      ALTER TABLE history_text DROP PRIMARY KEY, ADD INDEX (id), DROP INDEX history_text_2, ADD INDEX history_text_2 (itemid, id);
      CALL partition_maintenance('zabbix', 'history_text', 14, 24, 7);

      This:

i. Changes setting of primary keys on history_log and hoistory_text tables and adds initial partitioning
ii. Adds maintaining 14 daily partitions, each one with 24h data and creates 7 new partitions for next 7 days

1. Additionally event maintaining partitioning needs to be created by

   cat partitioning_event.sql | mysql -uroot

   This file contains:

   DELIMITER $$
   CREATE EVENT IF NOT EXISTS `part_manage`
   ON SCHEDULE EVERY 1 DAY
   STARTS '2016-08-02 00:05:00'
   ON COMPLETION PRESERVE
   ENABLE
   COMMENT 'Zabbix partitioning'
   DO BEGIN
   CALL partition_maintenance('zabbix', 'history', 14, 24, 7);
   CALL partition_maintenance('zabbix', 'history_uint', 14, 24, 7);
   CALL partition_maintenance('zabbix', 'history_log', 14, 24, 7);
   CALL partition_maintenance('zabbix', 'history_str', 14, 24, 7);
   CALL partition_maintenance('zabbix', 'history_text', 14, 24, 7);
   END$$
   DELIMITER ;

   If it will be any needs to change how long raw history data needs to be kept above zabbix_part_manager event needs to be dropped, changed settings in this event and event needs to be created again.
2. Disable over zabbix web frontend housekeeping history tables

   Administration->General->Housekeeping-> History -> uncheck “Enable internal housekeeping” -> Update

Other things done:

1. Changed global settings of the inventory mode to “auto”

   Administration->General-->Other-> change “Default host inventory mode” to “Automatic” -> Update

   # Fixed problem with unknown MySQL root password. Reset DB backend root password
   1. Stop mysqld service
   2. Start manually

      /usr/libexec/mysqld basedir=/usr datadir=/var/lib/mysql user=mysql log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid socket=/var/lib/mysql/mysql.sock --skip-grant-tables

   3. Set root password

      mysql> update user set password=PASSWORD("XXXXXXX") where user='root';

   4. killall mysqld
   5. Start mysqld service