分类目录归档:运维

升级zabbix3.2到4.2

以前源码安装的3.2版本现在要升级到最新4.2版本,由于不是在原有基础上进行的升级,所以无法直接yum upgrade,只能在新环境上安装完毕以后进行迁移。

zabbix安装

基于之前配置好的本地源,这里就可以很方便的用到yum

yum install zabbix-server-mysql zabbix-web-mysql -y

安装完毕以后zabbix默认采用的是mariadb,这里换成以前用的mysql

yum remove mariadb-server -y
yum install mysql-server -y

如果是初始化的数据库,则执行下面语句导入数据库

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix

-- for proxy
zcat /usr/share/doc/zabbix-proxy-mysql*/schema.sql.gz | mysql -uzabbix -p zabbix

这里zabbix很简单就安装完毕了。

Zabbix Proxy 安装

yum install zabbix-proxy-mysql -y
yum install mysql-community-server -y

systemctl start mysqld
systemctl enable mysqld

systemctl enable zabbix-proxy
systemctl start zabbix-proxy

初始化mysql并设置root密码

mysql_secure_installation

创建数据库

create database zabbix_proxy  character set utf8 collate utf8_bin;
grant all privileges on zabbix_proxy.* to zabbix@'%' identified by 'zabbix';

flush privileges;

导入proxy数据

zcat /usr/share/doc/zabbix-proxy-mysql*/schema.sql.gz | mysql -uzabbix -p zabbix_proxy

修改配置文件/etc/zabbix/zabbix_proxy.conf

Server=<zabbix server ip>
Hostname=zabbix_proxy
DBName=zabbix_proxy
DBUser=zabbix
DBPassword=zabbix

配置 Zabbix Server Proxy

Administration -> Proxies -> Create proxy
Proxy name: zabbix_proxy
Proxy mode: Active
Proxy address: <zabbix proxy ip>

修改 zabbix_agent 配置指向 zabbix_proxy
vim /etc/zabbix/zabbix_agentd.conf

Server=<zabbix proxy ip>,<zabbix server ip>
ServerActive=<zabbix proxy ip>
Hostname= <hostname>

在 zabbix_server 中添加代理主机

Configuration -> Hosts -> Create host
Monitored by proxy: zabbix_proxy

备份原zabbix数据

停掉zabbix

systemctl stop zabbix-server
systemctl stop zabbix-proxy

备份zabbix数据库

mysqldump -uroot -p -B zabbix --force --opt |gzip > zabbix.sql.gz

备份配置文件, PHP文件和Zabbix二进制文件
— 配置文件

mkdir /opt/zabbix-backup/
cp /etc/zabbix/zabbix_server.conf /opt/zabbix-backup/
cp /etc/httpd/conf.d/zabbix.conf  /opt/zabbix-backup/

— php文件和zabbix文件

cp -R /usr/share/zabbix/ /opt/zabbix-backup/
cp -R /usr/share/doc/zabbix-* /opt/zabbix-backup/

如果只是原基础上升级

yum upgrade zabbix-server-mysql zabbix-web-mysql zabbix-agent

--启动zabbix服务
systemctl start zabbix-server
systemctl start zabbix-proxy
systemctl start zabbix-agent

数据导入zabbix4.2

停掉zabbix4.2

systemctl stop zabbix-server
systemctl stop zabbix-proxy

导入mysql数据

mysql -uroot -p123456 < /root/zabbix_0527.sql

重新启动zabbix4.2

-- 启动zabbix服务
systemctl start zabbix-server
systemctl start zabbix-proxy
systemctl start zabbix-agent

在zabbix server启动的过程中,会自动判断当前数据库的版本,如果发现不一致,则会对其自动升级,更新相关表结构

30044:20190527:214417.505 using configuration file: /etc/zabbix/zabbix_server.conf
 30044:20190527:214417.511 current database version (mandatory/optional): 04020000/04020000
 30044:20190527:214417.511 required mandatory version: 04020000
 30044:20190527:214418.763 starting event name update forced by database upgrade
 30044:20190527:214418.804 completed 1% of event name update
 30044:20190527:214418.805 completed 2% of event name update
 30044:20190527:214418.805 completed 3% of event name update
 30044:20190527:214418.806 completed 4% of event name update
 30044:20190527:214418.806 completed 5% of event name update
 30044:20190527:214418.807 completed 6% of event name update
 30044:20190527:214418.808 completed 7% of event name update
 30044:20190527:214418.808 completed 8% of event name update
 30044:20190527:214418.809 completed 9% of event name update
 30044:20190527:214418.809 completed 10% of event name update
 30044:20190527:214418.810 completed 11% of event name update
 30044:20190527:214418.811 completed 12% of event name update
 30044:20190527:214419.259 completed 13% of event name update
 30044:20190527:214419.260 completed 14% of event name update
 30044:20190527:214419.260 completed 15% of event name update
 30044:20190527:214419.261 completed 16% of event name update
 30044:20190527:214419.261 completed 17% of event name update
 30044:20190527:214419.262 completed 18% of event name update
 30044:20190527:214419.263 completed 19% of event name update
 30044:20190527:214419.263 completed 20% of event name update
 30044:20190527:214419.264 completed 21% of event name update
 30044:20190527:214419.264 completed 22% of event name update
 30044:20190527:214419.265 completed 23% of event name update
 30044:20190527:214419.658 completed 24% of event name update
 30044:20190527:214419.658 completed 25% of event name update
 --- more

 30044:20190527:214423.599 completed 100% of event name update
 30044:20190527:214423.799 event name update completed

优化zabbix server配置

vim /etc/php.ini修改下面参数

max_execution_time = 300
post_max_size = 16M
max_input_vars = 10000
always_populate_raw_post_data = -1
date.timezone = Asia/Shanghai

-- 重启服务
service httpd restart

vim /etc/zabbix/zabbix_server.conf

StartPollers=160
StartTrappers=20
StartPingers=100
StartDiscoverers=20
StartDBSyncers=16
HistoryCacheSize=1024M
TrendCacheSize=1024M
Timeout=30
ValueCacheSize=512M

-- 重启服务
service zabbix-server restart

vim /etc/my.cnf,个人使用的配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid

# replication
server-id=1
binlog_format = row
expire_logs_days = 30
gtid_mode = ON
enforce_gtid_consistency = ON
master-verify-checksum = 1
log-slave-updates = ON
log-bin = /var/lib/mysql/mysql-bin
log_bin_index = /var/lib/mysql/mysql-bin.index

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# logs
log-error=/var/log/mysqld.log
slow_query_log_file= /var/log/mysql-slow.log
slow_query_log= 1
long_query_time= 20

key_buffer_size = 256M
table_open_cache = 512
sort_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 512K
thread_cache_size = 64

# network
connect_timeout= 60
wait_timeout= 28800
max_connections= 2048
max_allowed_packet= 64M
max_connect_errors= 1000

# limits
tmp_table_size= 512M
max_heap_table_size= 256M

# innodb
innodb_buffer_pool_size = 10G #一般设为服务器物理内存的70%-80%
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table=1
innodb_flush_method= O_DIRECT
innodb_io_capacity= 2000

# other stuff
event_scheduler= 1
query_cache_type= 0
group_concat_max_len = 1000000
tmpdir = /tmp/mysqltmp
log_timestamps= SYSTEM

-- 重启服务
service mysqld restart

Mysql优化

zabbix一般来说最大的瓶颈都在于数据库层面,大量数据的读写导致压力很大,所以可以历史数据表进行分区处理。

由于即使关闭了前端的housekeeping,zabbix server依旧会写相关信息到housekeeper表中,所以将其关闭

ALTER TABLE housekeeper ENGINE = BLACKHOLE;

首先需要对7张表做一个分区的初始化操作。

ALTER TABLE `history` PARTITION BY RANGE ( clock)
(PARTITION p2019_11_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_02 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-04 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-06 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-07 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `history_log` PARTITION BY RANGE ( clock)
(PARTITION p2019_11_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_02 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-04 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-06 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-07 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `history_str` PARTITION BY RANGE ( clock)
(PARTITION p2019_11_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_02 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-04 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-06 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-07 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `history_text` PARTITION BY RANGE ( clock)
(PARTITION p2019_11_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_02 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-04 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-06 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-07 00:00:00")) ENGINE = InnoDB);


ALTER TABLE `history_uint` PARTITION BY RANGE ( clock)
(PARTITION p2019_11_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-02 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_02 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-03 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-04 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-05 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-06 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-07 00:00:00")) ENGINE = InnoDB);


ALTER TABLE `trends` PARTITION BY RANGE ( clock)
(PARTITION p2019_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_07 VALUES LESS THAN (UNIX_TIMESTAMP("2019-08-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_08 VALUES LESS THAN (UNIX_TIMESTAMP("2019-09-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_09 VALUES LESS THAN (UNIX_TIMESTAMP("2019-10-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_10 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11 VALUES LESS THAN (UNIX_TIMESTAMP("2019-12-01 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock)
(PARTITION p2019_06 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_07 VALUES LESS THAN (UNIX_TIMESTAMP("2019-08-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_08 VALUES LESS THAN (UNIX_TIMESTAMP("2019-09-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_09 VALUES LESS THAN (UNIX_TIMESTAMP("2019-10-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_10 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-01 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11 VALUES LESS THAN (UNIX_TIMESTAMP("2019-12-01 00:00:00")) ENGINE = InnoDB);

这里采用外部perl脚本,首先需要安装相关依赖

yum install perl-Sys-Syslog perl-DateTime -y

cat part.pl

#!/usr/bin/perl

use strict;
use Data::Dumper;
use DBI;
use Sys::Syslog qw(:standard :macros);
use DateTime;
use POSIX qw(strftime);

openlog("mysql_zbx_part", "ndelay,pid", LOG_LOCAL0);

my $db_schema = 'zabbix';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/var/lib/mysql/mysql.sock';
my $db_user_name = 'zabbix';
my $db_password = 'zabbix';
my $tables = {  'history' => { 'period' => 'day', 'keep_history' => '30'},
        'history_log' => { 'period' => 'day', 'keep_history' => '30'},
        'history_str' => { 'period' => 'day', 'keep_history' => '30'},
        'history_text' => { 'period' => 'day', 'keep_history' => '30'},
        'history_uint' => { 'period' => 'day', 'keep_history' => '30'},
        'trends' => { 'period' => 'month', 'keep_history' => '2'},
        'trends_uint' => { 'period' => 'month', 'keep_history' => '2'},

# comment next 5 lines if you partition zabbix database starting from 2.2
# they usually used for zabbix database before 2.2

#      'acknowledges' => { 'period' => 'month', 'keep_history' => '23'},
#      'alerts' => { 'period' => 'month', 'keep_history' => '6'},
#      'auditlog' => { 'period' => 'month', 'keep_history' => '24'},
#      'events' => { 'period' => 'month', 'keep_history' => '12'},
#      'service_alarms' => { 'period' => 'month', 'keep_history' => '6'},
        };
my $amount_partitions = 10;

my $curr_tz = 'Asia/Shanghai';

my $part_tables;

my $dbh = DBI->connect($dsn, $db_user_name, $db_password, {'ShowErrorStatement' => 1});

unless ( check_have_partition() ) {
    print "Your installation of MySQL does not support table partitioning.\n";
    syslog(LOG_CRIT, 'Your installation of MySQL does not support table partitioning.');
    exit 1;
}

my $sth = $dbh->prepare(qq{SELECT table_name, partition_name, lower(partition_method) as partition_method,
                    rtrim(ltrim(partition_expression)) as partition_expression,
                    partition_description, table_rows
                FROM information_schema.partitions
                WHERE partition_name IS NOT NULL AND table_schema = ?});
$sth->execute($db_schema);

while (my $row =  $sth->fetchrow_hashref()) {
    $part_tables->{$row->{'table_name'}}->{$row->{'partition_name'}} = $row;
}

$sth->finish();

foreach my $key (sort keys %{$tables}) {
    unless (defined($part_tables->{$key})) {
        syslog(LOG_ERR, 'Partitioning for "'.$key.'" is not found! The table might be not partitioned.');
        next;
    }

    create_next_partition($key, $part_tables->{$key}, $tables->{$key}->{'period'});
    remove_old_partitions($key, $part_tables->{$key}, $tables->{$key}->{'period'}, $tables->{$key}->{'keep_history'})
}

delete_old_data();

$dbh->disconnect();

sub check_have_partition {
    my $result = 0;
# MySQL 5.5
    #my $sth = $dbh->prepare(qq{SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'have_partitioning'});
# MySQL 5.6
    my $sth = $dbh->prepare(qq{SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition'});

    $sth->execute();

    my $row = $sth->fetchrow_array();

    $sth->finish();

# MySQL 5.5
    #return 1 if $row eq 'YES';
# MySQL 5.6
    return 1 if $row eq 'ACTIVE';
}

sub create_next_partition {
    my $table_name = shift;
    my $table_part = shift;
    my $period = shift;

    for (my $curr_part = 0; $curr_part < $amount_partitions; $curr_part++) {
        my $next_name = name_next_part($tables->{$table_name}->{'period'}, $curr_part);
        my $found = 0;

        foreach my $partition (sort keys %{$table_part}) {
            if ($next_name eq $partition) {
                syslog(LOG_INFO, "Next partition for $table_name table has already been created. It is $next_name");
                $found = 1;
            }
        }

        if ( $found == 0 ) {
            syslog(LOG_INFO, "Creating a partition for $table_name table ($next_name)");
            my $query = 'ALTER TABLE '."$db_schema.$table_name".' ADD PARTITION (PARTITION '.$next_name.
                        ' VALUES less than (UNIX_TIMESTAMP("'.date_next_part($tables->{$table_name}->{'period'}, $curr_part).'") div 1))';
            syslog(LOG_DEBUG, $query);
            $dbh->do($query);
        }
    }
}

sub remove_old_partitions {
    my $table_name = shift;
    my $table_part = shift;
    my $period = shift;
    my $keep_history = shift;

    my $curr_date = DateTime->now;
    $curr_date->set_time_zone( $curr_tz );

    if ( $period eq 'day' ) {
        $curr_date->add(days => -$keep_history);
        $curr_date->add(hours => -$curr_date->strftime('%H'));
        $curr_date->add(minutes => -$curr_date->strftime('%M'));
        $curr_date->add(seconds => -$curr_date->strftime('%S'));
    }
    elsif ( $period eq 'week' ) {
    }
    elsif ( $period eq 'month' ) {
        $curr_date->add(months => -$keep_history);

        $curr_date->add(days => -$curr_date->strftime('%d')+1);
        $curr_date->add(hours => -$curr_date->strftime('%H'));
        $curr_date->add(minutes => -$curr_date->strftime('%M'));
        $curr_date->add(seconds => -$curr_date->strftime('%S'));
    }

    foreach my $partition (sort keys %{$table_part}) {
        if ($table_part->{$partition}->{'partition_description'} <= $curr_date->epoch) {
            syslog(LOG_INFO, "Removing old $partition partition from $table_name table");

            my $query = "ALTER TABLE $db_schema.$table_name DROP PARTITION $partition";

            syslog(LOG_DEBUG, $query);
            $dbh->do($query);
        }
    }
}

sub name_next_part {
    my $period = shift;
    my $curr_part = shift;

    my $name_template;

    my $curr_date = DateTime->now;
    $curr_date->set_time_zone( $curr_tz );

    if ( $period eq 'day' ) {
        my $curr_date = $curr_date->truncate( to => 'day' );
        $curr_date->add(days => 1 + $curr_part);

        $name_template = $curr_date->strftime('p%Y_%m_%d');
    }
    elsif ($period eq 'week') {
        my $curr_date = $curr_date->truncate( to => 'week' );
        $curr_date->add(days => 7 * $curr_part);

        $name_template = $curr_date->strftime('p%Y_%m_w%W');
    }
    elsif ($period eq 'month') {
        my $curr_date = $curr_date->truncate( to => 'month' );
        $curr_date->add(months => 1 + $curr_part);

        $name_template = $curr_date->strftime('p%Y_%m');
    }

    return $name_template;
}

sub date_next_part {
    my $period = shift;
    my $curr_part = shift;

    my $period_date;

    my $curr_date = DateTime->now;
    $curr_date->set_time_zone( $curr_tz );

    if ( $period eq 'day' ) {
        my $curr_date = $curr_date->truncate( to => 'day' );
        $curr_date->add(days => 2 + $curr_part);
        $period_date = $curr_date->strftime('%Y-%m-%d');
    }
    elsif ($period eq 'week') {
        my $curr_date = $curr_date->truncate( to => 'week' );
        $curr_date->add(days => 7 * $curr_part + 1);
        $period_date = $curr_date->strftime('%Y-%m-%d');
    }
    elsif ($period eq 'month') {
        my $curr_date = $curr_date->truncate( to => 'month' );
        $curr_date->add(months => 2 + $curr_part);

        $period_date = $curr_date->strftime('%Y-%m-%d');
    }

    return $period_date;
}

sub delete_old_data {
    $dbh->do("DELETE FROM sessions WHERE lastaccess < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)");
    $dbh->do("TRUNCATE housekeeper");
    $dbh->do("DELETE FROM auditlog_details WHERE NOT EXISTS (SELECT NULL FROM auditlog WHERE auditlog.auditid = auditlog_details.auditid)");
}

创建定时任务

chmod +x part.pl

[root@zabbix-pri ~]# crontab -l
0 3 * * * /root/part.pl

查看执行后的结果

mysql> show create table history;
+---------+-------------------------------------------------+
| Table   | Create Table                                                                                                                                                     
+---------+-------------------------------------------------+
| history | 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
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p2019_06_02 VALUES LESS THAN (1559491200) ENGINE = InnoDB,
 PARTITION p2019_06_03 VALUES LESS THAN (1559577600) ENGINE = InnoDB,
 PARTITION p2019_06_04 VALUES LESS THAN (1559664000) ENGINE = InnoDB,
 PARTITION p2019_06_05 VALUES LESS THAN (1559750400) ENGINE = InnoDB,
 PARTITION p2019_06_06 VALUES LESS THAN (1559836800) ENGINE = InnoDB,
 PARTITION p2019_06_07 VALUES LESS THAN (1559923200) ENGINE = InnoDB,
 PARTITION p2019_06_08 VALUES LESS THAN (1560009600) ENGINE = InnoDB,
 PARTITION p2019_06_09 VALUES LESS THAN (1560096000) ENGINE = InnoDB,
 PARTITION p2019_06_10 VALUES LESS THAN (1560182400) ENGINE = InnoDB,
 PARTITION p2019_06_11 VALUES LESS THAN (1560268800) ENGINE = InnoDB) */ |

proxy mysql优化

对于proxy端的mysql来说,主要是proxy_history表很大,所以做个定时任务清理,同样换成分区表

drop table proxy_history;

 CREATE TABLE `proxy_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL DEFAULT '0',
  `source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `severity` int(11) NOT NULL DEFAULT '0',
  `value` longtext COLLATE utf8_bin NOT NULL,
  `logeventid` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  `state` int(11) NOT NULL DEFAULT '0',
  `lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
  `mtime` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,clock)
) ENGINE=InnoDB AUTO_INCREMENT=242428784 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY RANGE ( clock)
(PARTITION p2019_11_19 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-20 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_20 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-21 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_21 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-22 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-23 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_23 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-24 00:00:00")) ENGINE = InnoDB,
PARTITION p2019_11_24 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-25 00:00:00")) ENGINE = InnoDB)

其他的跟上面类似,也是通过part.pl脚本来进行分区的处理。

安装ansible tower

ansible tower是ansible的一个图形化界面,IT自动化的管理平台,本文整理了安装过程中的步骤以及碰到的坑。

前提条件

操作系统版本:

  • Red Hat Enterprise Linux 7.4 or later 64-bit
  • CentOS 7.4 or later 64-bit
  • Ubuntu 16.04 LTS 64-bit

数据库版本:

  • PostgreSQL version 9.6.X以上
  • Ansible version 2.2 (最低版本) 来运行 Ansible Tower versions 3.2以上

Tower安装

下载

wget https://releases.ansible.com/ansible-tower/setup-bundle/ansible-tower-setup-bundle-latest.el7.tar.gz -P /root/tower/
cd /root/tower/
tar xvf ansible-tower-setup-bundle-latest.el7.tar.gz -C ./
cd ansible-tower-setup-bundle-3.4.3-1.el7/

目录结构,跟playbook类似

[root@zabbix-stb ansible-tower-setup-bundle-3.4.3-1.el7]# ll
总用量 52
-rw-r--r--  1 root root   143 3月  27 07:55 backup.yml
drwxr-xr-x  2 root root    17 3月  27 07:55 group_vars
-rw-r--r--  1 root root  8314 3月  27 07:55 install.yml
-rw-r--r--  1 root root   494 5月  20 16:37 inventory
drwxr-xr-x  3 root root  8192 3月  27 07:55 licenses
-rw-r--r--  1 root root  2526 3月  27 07:55 README.md
-rw-r--r--  1 root root  1393 3月  27 07:55 restore.yml
drwxr-xr-x 20 root root   320 3月  27 07:55 roles
-rwxr-xr-x  1 root root 11068 3月  27 07:55 setup.sh

编辑inventory配置

[tower]
localhost ansible_connection=local

[database]

[all:vars]
admin_password='admin'

pg_host='127.0.0.1'
pg_port='5432'

pg_database='awx'
pg_username='awx'
pg_password='awx'



rabbitmq_port=5672
rabbitmq_vhost=tower

rabbitmq_username=tower
rabbitmq_password='tower'
rabbitmq_cookie=cookiemonster

# Isolated Tower nodes automatically generate an RSA key for authentication;
# To disable this behavior, set this value to false
# isolated_key_generation=true


# Needs to be true for fqdns and ip addresses
rabbitmq_use_long_name=false

运行脚本

./setup.sh

限制

echo codyguo > /var/lib/awx/i18n.db

访问地址就是ip

简介

导航栏说明

# viewes
Dashboard           仪表盘展示信息的
Jobs                跑过的任务记录
Schedules           计划任务
My View             查看用户的工作模版,和任务记录

# resources
Templates           任务模版,配置调用playbook执行时的各种参数,从此处添加计划任务
Credentials         配置连接 机器/云主机api Key/自定义的凭证类型 的账号密码等信息
Projects            这里配置项目对应的playbook,可以从Git上拉取或从本地文件夹读取playbook
Inventories         资产清单
Inventory Scripts   自定义获取资产清单的脚本

# access
Organizations       组织管理
Users               用户管理
Teams               用户组管理

# Administration
Credential Types    自定义凭证类型,添加后可在Credentials中使用
Notifications       配置任务通知,支持电子邮件,Twillio电话等
Management Jobs     计划任务管理
Instance Groups     资产组管理
Applications        自定义应用
Settings            设置

导入现有的主机

[root@ ansible-tower-setup-bundle-3.4.3-1.el7]# tower-manage inventory_import --source=/root/hosts --inventory-id=2
    2.704 INFO     Updating inventory 2: 所有主机
    2.859 INFO     Reading Ansible inventory source: /root/hosts
    4.627 INFO     Processing JSON output...
    4.632 INFO     Loaded 10 groups, 184 hosts
    6.417 INFO     Inventory import completed for  (所有主机 - 7) in 3.7s