ELK+Zabbix+TimesacleDB+Grafana 搭建(上)

Zabbix分布式搭建

一．拓?fù)?/p>

所有服務(wù)器的硬件指標(biāo)都是一樣的

Centos?7?4核8G內(nèi)存，需要監(jiān)控18-19W的監(jiān)控項(xiàng)

組件說(shuō)明：

Web?server?是前端頁(yè)面，zabbix有兩個(gè)選擇nginx和apache，默認(rèn)是apache

Zabbix?server是主程序用來(lái)管理所有的組件和調(diào)度

Zabbix?agent?是插件，安裝在服務(wù)器上可以收集信息反饋給server或者proxy

Zabbix?proxy?分擔(dān)server壓力

二．?dāng)?shù)據(jù)庫(kù)TimesacleDB 10.217.37.146搭建

vim /etc/sysctl.conf

SELINUX=disable

firewall-cmd –state

service firewalld start ??#開(kāi)啟

service firewalld restart ??#重啟

service firewalld stop ????#關(guān)閉

sudo yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <

[timescale_timescaledb]

name=timescale_timescaledb

baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch

repo_gpgcheck=1

gpgcheck=0

enabled=1

gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey

sslverify=1

sslcacert=/etc/pki/tls/certs/ca-bundle.crt

metadata_expire=300

EOL

sudo yum install -y timescaledb-postgresql-11

/usr/pgsql-11/bin/postgresql-11-setup initdb

systemctl enable postgresql-11 && systemctl start postgresql-11

修改默認(rèn)用戶postgres的密碼

su postgres

psql

ALTER USER postgres WITH PASSWORD 'xxxx';

配置數(shù)據(jù)庫(kù)監(jiān)聽(tīng)ip和端口號(hào)

vim /var/lib/pgsql/11/data/postgresql.conf

listen_addresses = '*'

port = 5432

允許全部IP可以訪問(wèn)數(shù)據(jù)庫(kù)

vim /var/lib/pgsql/11/data/pg_hba.conf

host ???all ????????????all ????????????0.0.0.0/0 ??????????????md5

數(shù)據(jù)庫(kù)調(diào)優(yōu)，有個(gè)自帶的工具timescaledb-tune，自動(dòng)根據(jù)主機(jī)性能調(diào)整，全部yes就行

su - postgres

timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config

systemctl restart postgresql-11

創(chuàng)建數(shù)據(jù)庫(kù)zabbix和用戶zabbix，對(duì)用戶增加最高權(quán)限和遠(yuǎn)程訪問(wèn)權(quán)限

sudo -u postgres psql

create user zabbix with password 'xxxx';

create database zabbix owner zabbix;

grant all privileges on database zabbix to zabbix;

ALTER ROLE zabbix CREATEROLE SUPERUSER;

查看數(shù)據(jù)庫(kù)是否創(chuàng)建成功

playboy=>?\l

增加系統(tǒng)用戶和密碼

sudo adduser zabbix

sudo passwd zabbix

下載源代碼把zabbix官網(wǎng)提供的zabbix表進(jìn)行導(dǎo)入，（因?yàn)槲曳?wù)器下載的慢所以我下載到了我的pc上通過(guò)lrzsz?上傳到服務(wù)器的）

wget https://ayera.dl.sourceforge.net/project/zabbix/ZABBIX%20Latest%20Stable/4.4.4/zabbix-4.4.4.tar.gz

yum install lrzsz –y

tar -xzvf zabbix-4.4.4.tar.gz

cd /usr/local/zabbix-4.4.4/database/postgresql

echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix

cat schema.sql | sudo -u postgres psql zabbix

cat images.sql | sudo -u postgres psql zabbix

cat data.sql | sudo -u postgres psql zabbix

cat timescaledb.sql | sudo -u postgres psql zabbix

三．Zabbix?Web?agent server10.206.230.146搭建

vim /etc/sysctl.conf

SELINUX=disable

firewall-cmd –state

service firewalld start ??#開(kāi)啟

service firewalld restart ??#重啟

service firewalld stop ????#關(guān)閉

rpm -Uvh

https://repo.zabbix.com/zabbix/4.4/rhel/7/x86_64/zabbix-release-4.4-1.el7.noarch.rpm

yum clean all

yum -y install zabbix-server-pgsql zabbix-web-pgsql zabbix-agent

vim /etc/zabbix/zabbix_server.conf

DBHost=10.217.37.146 ??????????????#數(shù)據(jù)庫(kù)ip

DBName=zabbix ???#數(shù)據(jù)庫(kù)名稱

DBUser=zabbix ???#數(shù)據(jù)庫(kù)用戶名

DBPassword=xxxx ???#數(shù)據(jù)庫(kù)密碼

DBPort=5432? ???#數(shù)據(jù)庫(kù)端口號(hào)

預(yù)分配子進(jìn)程數(shù)量。數(shù)量越多服務(wù)端吞吐能力越強(qiáng)，系統(tǒng)資源消耗越大，zabbix server的進(jìn)程數(shù)server端拉取agent端的items

StartPollers=500

系統(tǒng)初始化時(shí)，預(yù)分配的使用ipmi協(xié)議獲取主機(jī)硬件狀態(tài)的進(jìn)程數(shù)量。

StartIPMIPollers=10

預(yù)處理程序啟動(dòng)數(shù)目

StartPreprocessors=3

對(duì)不可達(dá)主機(jī)拉取數(shù)據(jù)

StartPollersUnreachable=20

陷阱機(jī)制，即對(duì)于中途加入的主機(jī)主動(dòng)發(fā)送的數(shù)據(jù)進(jìn)行保存

StartTrappers=15

使用ICMP協(xié)議的ping對(duì)網(wǎng)絡(luò)中主機(jī)進(jìn)行在線狀態(tài)檢測(cè)

StartPingers=20

對(duì)網(wǎng)絡(luò)中的主機(jī)主動(dòng)掃描發(fā)現(xiàn)，對(duì)中途加入的主機(jī)進(jìn)行上線。注意：主動(dòng)發(fā)現(xiàn)很消耗帶寬

系統(tǒng)初始化時(shí)，預(yù)分配的自動(dòng)發(fā)現(xiàn)主機(jī)的線程數(shù)量。若單臺(tái)代理所管理機(jī)器超過(guò)500臺(tái)，可以考慮加大此數(shù)值（僅適用于AGENT場(chǎng)景）

StartDiscoverers=50

timers進(jìn)程的初始數(shù)量，它是一個(gè)用于處理時(shí)間相關(guān)的告警進(jìn)程

StartTimers=10

檢查escalations的進(jìn)程，這張表好像是告警事件表

StartEscalators=10

所有媒介都是并行處理的，當(dāng)前最大會(huì)話每個(gè)媒介自己定義，但是zabbix服務(wù)的告警處理總數(shù)被參數(shù)（zabbix_sever.conf）StartAlerters所限制。同一觸發(fā)器生成的告警會(huì)序列化的處理

StartAlerters=3

分配多少共享內(nèi)存用于存儲(chǔ)配置信息，HOST,ITEM,TRIGGER數(shù)據(jù)，視監(jiān)控主機(jī)數(shù)量和監(jiān)控項(xiàng)調(diào)整

CacheSize=4G

Zabbix更新緩存數(shù)據(jù)的頻率，單位為秒

CacheUpdateFrequency=180

數(shù)據(jù)庫(kù)同步實(shí)例數(shù)

將采集數(shù)據(jù)從CACHE同步到數(shù)據(jù)庫(kù)線程數(shù)量，視數(shù)據(jù)庫(kù)服務(wù)器I/O繁忙情況，和數(shù)據(jù)庫(kù)寫(xiě)能力調(diào)整。數(shù)值越大，寫(xiě)能力越強(qiáng)。對(duì)數(shù)據(jù)庫(kù)服務(wù)器I/O壓力越大

StartDBSyncers=10

設(shè)置劃分多少共享內(nèi)存用于存儲(chǔ)采集的歷史數(shù)據(jù)，此數(shù)值越大，數(shù)據(jù)庫(kù)讀壓力越小

HistoryCacheSize=512M

歷史數(shù)據(jù)索引緩存

HistoryIndexCacheSize=128M

用于設(shè)置劃分多少共享內(nèi)存用于存儲(chǔ)計(jì)算出來(lái)的趨勢(shì)數(shù)據(jù)，此參數(shù)值從一定程度上可影響數(shù)據(jù)庫(kù)讀壓力

TrendCacheSize=128M

歷史數(shù)據(jù)緩存大小，0為不緩存，

ValueCacheSize=128M

與AGNET、SNMP設(shè)備和其它外部設(shè)備通信超時(shí)設(shè)置，單位為秒；若采集數(shù)據(jù)不完整或網(wǎng)絡(luò)繁忙，或從管理頁(yè)面發(fā)現(xiàn)客戶端狀態(tài)變化頻繁，可以考慮加大此數(shù)值。

Timeout=60

定義主機(jī)不可達(dá)的超時(shí)時(shí)間

UnreachablePeriod=180

在主機(jī)不可用期間，定期檢查主機(jī)的時(shí)間

UnavailableDelay=60

用于服務(wù)端數(shù)據(jù)庫(kù)慢查詢功能，單位是毫秒。若有服務(wù)端數(shù)據(jù)庫(kù)監(jiān)控慢查詢的需求，可以視具體情況調(diào)整此數(shù)

LogSlowQueries=3000

啟用多少子進(jìn)程與代理端通信，若代理端較多可考慮加大此數(shù)值

StartProxyPollers=1

Zabbix服務(wù)端將配置文件數(shù)據(jù)同步到代理端的頻率，僅適用于代理端為被動(dòng)模式情況

ProxyConfigFrequency=300

Zabbix服務(wù)端請(qǐng)求代理端采集的數(shù)據(jù)的頻率，僅適用代理端為被動(dòng)模式情況

ProxyDataFrequency=1

systemctl start zabbix-server && systemctl enable zabbix-server

systemctl status zabbix-server

vim /etc/httpd/conf.d/zabbix.conf

php_value date.timezone Asia/shanghai

php_value memory_limit 2048M

vim /etc/php.ini

memory_limit = 2048M

啟動(dòng)Zabbix server和agent進(jìn)程，并為它們?cè)O(shè)置開(kāi)機(jī)自啟：

systemctl restart zabbix-server zabbix-agent httpd

systemctl enable zabbix-server zabbix-agent httpd

四.?zabbix proxy 10.217.37.188搭建

vim /etc/sysctl.conf

SELINUX=disable

firewall-cmd –state

service firewalld start ??#開(kāi)啟

service firewalld restart ??#重啟

service firewalld stop ????#關(guān)閉

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql-community-server

systemctl start mysqld.service ????????#數(shù)據(jù)庫(kù)必須初始化，初始化后才有數(shù)據(jù)庫(kù)文件生成

systemctl status mysqld.service ??????#?Active: active (running)表示正常

systemctl restart mysqld.service

systemctl enable mysqld.service

mysql有默認(rèn)密碼，到mysql.log下查找

grep "password" /var/log/mysqld.log

用root賬號(hào)進(jìn)入 數(shù)據(jù)默認(rèn)密碼

mysql –u root -p

修改root的密碼，xxxx就是密碼

ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxx';

創(chuàng)建數(shù)據(jù)庫(kù)名稱zabbix和zabbix_proxy?，zabbix和proxy數(shù)據(jù)庫(kù)編碼設(shè)置為utf8

create database zabbix character set utf8 collate utf8_bin;

create database zabbix_proxy character set utf8 collate utf8_bin;

注意：刪除數(shù)據(jù)庫(kù)命令

drop database zabbix；

查看創(chuàng)建好的數(shù)據(jù)庫(kù)

show?databases;

創(chuàng)建用戶zabbix和密碼開(kāi)并啟遠(yuǎn)程權(quán)限，@表示哪些ip可以訪問(wèn)數(shù)據(jù)庫(kù)，這里寫(xiě)的是zabbix server的ip

grant all privileges on zabbix_proxy.* to zabbix_proxy@10.206.230.146 identified by 'xxxx';

grant all privileges on zabbix_proxy.* to zabbix_proxy@127.0.0.1 identified by 'xxxx';

grant all privileges on zabbix_proxy.* to zabbix_proxy@10.217.37.188 identified by 'xxxx';

grant all privileges on zabbix_proxy.* to root@localhost identified by 'xxxx';

systemctl restart mysqld

安裝下載zabbix-proxy，把數(shù)據(jù)庫(kù)表schema.sql.gz導(dǎo)入進(jìn)zabbix-prxoy數(shù)據(jù)庫(kù)

rpm –Uvh?https://repo.zabbix.com/zabbix/4.4/rhel/7/x86_64/zabbix-release-4.4-1.el7.noarch.rpm

yum clean all

yum -y install zabbix-proxy-mysql

systemctl restart zabbix-proxy

systemctl enable zabbix-proxy

find?–name?schema.sql.gz

cd?./usr/share/doc/zabbix-proxy-mysql-4.4.4

zcat schema.sql.gz | mysql -h 127.0.0.1 -u zabbix_proxy -p zabbix_proxy

數(shù)據(jù)庫(kù)某些表象存儲(chǔ)較大，后期無(wú)法進(jìn)行快速查詢，將它分塊存儲(chǔ)，

Zabbix大表有：history，history_log，history_str，history_text，history_uint，trends，trends_uint

官網(wǎng)：

https://www.zabbix.org/wiki/Docs/howto/mysql_partitioning

數(shù)據(jù)庫(kù)分區(qū)有幾種方式，本文使用的是range方式，

數(shù)據(jù)庫(kù)分區(qū)管理有兩種方式：mysql管理，外部腳本，官網(wǎng)推薦使用外部腳本+事件管理

root查看mysql當(dāng)前版本是否支持分區(qū)，顯示YES or ACTIVE就是支持，新版本都支持

SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition';

root進(jìn)入zabbix庫(kù)查看事件計(jì)劃程序是否啟用

SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

SET GLOBAL event_scheduler = ON; ???#開(kāi)啟

vim /etc/my.cnf

event_scheduler=ON

查詢?cè)摂?shù)據(jù)庫(kù)表的分區(qū)字段的最小時(shí)鐘，以history_uint表示例，如下

select from_unixtime(min(clock)) from history_uint

創(chuàng)建分區(qū)表的腳本

vim?partition.sql

DELIMITER $$

CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)

BEGIN

/*

SCHEMANAME = The DB schema in which to make changes

TABLENAME = The table with partitions to potentially delete

PARTITIONNAME = The name of the partition to create

*/

/*

Verify that the partition does not already exist

*/

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

/*

Print a message indicating that a partition was created.

Create the SQL to create the partition.

Execute the SQL from #2.

*/

SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;

SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );

PREPARE STMT FROM @sql;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END IF;

END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)

BEGIN

/*

SCHEMANAME = The DB schema in which to make changes

TABLENAME = The table with partitions to potentially delete

DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)

*/

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. ?All partitions are prefixed with

a "p", so use SUBSTRING TO get rid of that character.

*/

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;

/*

Create the basics for when we need to drop the partition. ?Also, create

@drop_partitions to hold a comma-delimited list of all partitions that

should be deleted.

*/

SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");

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 = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));

END LOOP;

IF @drop_partitions != "" THEN

/*

Build the SQL to drop all the necessary partitions.

Run the SQL to drop the partitions.

Print out the table partitions that were deleted.

*/

SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");

PREPARE STMT FROM @full_sql;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;

ELSE

/*

No partitions are being deleted, so print out "N/A" (Not applicable) to indicate

that no changes were made.

*/

SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;

END IF;

END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)

BEGIN

DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);

DECLARE PARTITION_NAME VARCHAR(16);

DECLARE OLD_PARTITION_NAME VARCHAR(16);

DECLARE LESS_THAN_TIMESTAMP INT;

DECLARE CUR_TIME INT;

CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);

SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));

SET @__interval = 1;

create_loop: 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, 'p%Y%m%d%H00');

IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN

CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);

END IF;

SET @__interval=@__interval+1;

SET OLD_PARTITION_NAME = PARTITION_NAME;

END LOOP;

SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');

CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);

END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))

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 "p201403270600" when all other partitions will be like "p201403280000").

*/

SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));

SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');

-- Create the partitioning query

SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)");

SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");

-- Run the partitioning query

PREPARE STMT FROM @__PARTITION_SQL;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END IF;

END$$

DELIMITER ;

7是數(shù)據(jù)保存天數(shù)，1小時(shí)表示每隔多久生成一個(gè)分區(qū)，48表示本次一共生成多少個(gè)分區(qū)

DELIMITER $$

CREATE ?PROCEDURE`partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))BEGIN

CALL partition_maintenance(SCHEMA_NAME, 'history', 7, 1, 48);

CALL partition_maintenance(SCHEMA_NAME, 'history_log', 7, 1, 48);

CALL partition_maintenance(SCHEMA_NAME, 'history_str', 7, 1,4?8);

CALL partition_maintenance(SCHEMA_NAME, 'history_text', 7, 1,48);

CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 7, 1, 48);

CALL partition_maintenance(SCHEMA_NAME, 'trends', 7, 1, 48);

CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 7, 1, 48);END$$

DELIMITER ;

對(duì)數(shù)據(jù)庫(kù)zabbix導(dǎo)入腳本

mysql -uroot?-p zabbix_proxy < partition.sql

添加linux腳本的定時(shí)任務(wù)，每天夜里1點(diǎn)01分開(kāi)始執(zhí)行

cd?./usr/share/doc/zabbix-server-mysql-4.4.3目錄下有crontab.txt定時(shí)任務(wù)

根據(jù)實(shí)際情況填寫(xiě)

Vim?crontab.txt

#zabbix partition_maintenance

01?01?* * * mysql -uroot -p'xxxx' zabbix ?-e "CALL partition_maintenance_all('zabbix');"

新創(chuàng)建的定時(shí)任務(wù)生效，查看定時(shí)任務(wù)，保證服務(wù)器重啟后任務(wù)可以自動(dòng)啟動(dòng)

crontab crontab.txt

crontab -l

systemctl start crond.service

systemctl enable crond.service

service?crond?status

進(jìn)入zabbix數(shù)據(jù)庫(kù)查看是否自動(dòng)添加了分區(qū)表，下面以history_uint為例

show create table history_uint;

查zabbix數(shù)據(jù)庫(kù)各個(gè)表占用情況

select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';

停止mysql

service?mysqld?stop

進(jìn)入mysql配置表進(jìn)行配置修改

vim /etc/my.cnf

[mysqld]下增加如下參數(shù)：

innodb?引擎使用獨(dú)立表項(xiàng)

innodb_file_per_table=1

zabbix在使用數(shù)據(jù)庫(kù)的過(guò)程中，特別是刪除歷史數(shù)據(jù)的過(guò)程中，會(huì)涉及到大數(shù)據(jù)操作，如果邏輯日志文件太小，會(huì)造成執(zhí)行不成功，日志回滾的問(wèn)題

數(shù)值參考

show engine innodb status\G;

（Log sequence number減去Last checkpoint at?）/1024/1024

距離最好不要超過(guò)innodb_log_file_size的0.75.

innodb_log_file_size=20M

InnoDB需要innodb buffer pool中處理緩存，其中包含數(shù)據(jù)緩存?索引緩存?緩沖數(shù)據(jù)?內(nèi)部結(jié)構(gòu)

show global status like 'Innodb_buffer_pool_pages_data';

show global status like 'Innodb_buffer_pool_pages_total';

show global status like 'Innodb_page_size';

計(jì)算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

當(dāng)結(jié)果 > 95% 則增加 innodb_buffer_pool_size， 建議使用物理內(nèi)存的 75%

當(dāng)結(jié)果 < 95% 則減少 innodb_buffer_pool_size，

Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

innodb_buffer_pool_size=6G

關(guān)閉符號(hào)鏈接

symbolic-links=0

慢查詢?nèi)罩敬鎯?chǔ)

slow_query_log=/var/log/mysql.slow.log

back_log值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中。也就是說(shuō)，如果MySql的連接數(shù)達(dá)到max_connections時(shí)，新來(lái)的請(qǐng)求將會(huì)被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過(guò)back_log，將不被授予連接資源。

back_log=500

數(shù)據(jù)庫(kù)連接超時(shí)時(shí)間

connect_timeout=5

多個(gè)表關(guān)聯(lián)的時(shí)候 減少參與被驅(qū)動(dòng)表的join操作

join_buffer_size=2M

它決定索引處理的速度，尤其是索引讀的速度。

key_reads /key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好

show global status like 'key_read%';

key_buffer_size=16M

開(kāi)啟慢查詢，時(shí)間超過(guò)1秒就算慢

long_query_time=1

”MySQL: ERROR 1040: Too many connections”的異常情況，造成這種情況的一種原因是訪問(wèn)量過(guò)高，MySQL服務(wù)器抗不住，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另一種原因就是MySQL配置文件中max_connections值過(guò)小

show variables like '%max_connections%';

show global status like 'Max_used_connections';

Max_used_connections / max_connections * 100%>10%

max_connections=1000

接受的數(shù)據(jù)包大小，默認(rèn)40M

max_allowed_packet=40M

安全參數(shù)防止暴力破解密碼

max_connect_errors=10

這個(gè)參數(shù)的意思就是是否開(kāi)啟合并insert，默認(rèn)是開(kāi)啟的,用白話講就是用mysqldump導(dǎo)出生成的insert數(shù)據(jù)合并成一條,如果不開(kāi)啟就是一條數(shù)據(jù)一個(gè)insert.，最大值是16M

net_buffer_length=16M

配置MySql日志何時(shí)寫(xiě)入硬盤(pán)的參數(shù)，

當(dāng)設(shè)置為0，該模式速度最快，但不太安全，mysqld進(jìn)程的崩潰會(huì)導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失。

當(dāng)設(shè)置為1，該模式是最安全的，但也是最慢的一種方式。在mysqld 服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log 只有可能丟失最多一個(gè)語(yǔ)句或者一個(gè)事務(wù)。。

當(dāng)設(shè)置為2，該模式速度較快，也比0安全，只有在操作系統(tǒng)崩潰或者系統(tǒng)斷電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。

查找資料時(shí)候看到其他文章說(shuō)innodb_flush_log_at_trx_commit和sync_binlog 兩個(gè)參數(shù)是控制MySQL 磁盤(pán)寫(xiě)入策略以及數(shù)據(jù)安全性的關(guān)鍵參數(shù)，當(dāng)兩個(gè)參數(shù)都設(shè)置為1的時(shí)候?qū)懭胄阅茏畈睿扑]做法是innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

推薦參數(shù)

max_join_size=4294967295

參數(shù)來(lái)控制寫(xiě)的優(yōu)先級(jí),當(dāng)一個(gè)表的讀鎖達(dá)到這個(gè)數(shù)的時(shí)候,MySQL會(huì)暫時(shí)的,降低寫(xiě)的優(yōu)先級(jí).MySQL盡量少用一些看似精妙但復(fù)雜的語(yǔ)句,因?yàn)槟銖?fù)雜可能導(dǎo)致給表加的讀鎖時(shí)間就長(zhǎng),會(huì)導(dǎo)致寫(xiě)的操作無(wú)法進(jìn)行.?數(shù)值為1就是讓讀和寫(xiě)交替進(jìn)行

show status like "innodb_row_lock%";

max_write_lock_count=1

是MySQL讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)，MySQL會(huì)為它分配一段內(nèi)存緩沖區(qū)。

read_buffer_size=1M

這個(gè)變量定義了用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小，但是對(duì)于已經(jīng)存在的內(nèi)存表就沒(méi)有什么用了，除非這個(gè)表被重新創(chuàng)建(create table)或者修改(alter table)或者truncate table。服務(wù)重啟也會(huì)設(shè)置已經(jīng)存在的內(nèi)存表為全局max_heap_table_size的值。

這個(gè)變量和tmp_table_size一起限制了內(nèi)部?jī)?nèi)存表的大小。?暫不關(guān)注

max_heap_table_size=256M

tmp_table_size=1048576

max_tmp_tables=32

在排序BLOB或TEXT值時(shí)使用的字節(jié)數(shù)(每個(gè)值僅頭max_sort_length個(gè)字節(jié)被使用；其余的被忽略)。

max_sort_length=1024

使用CREATE TABLE或CREATE DATABASE語(yǔ)句指定的大小寫(xiě)字母在硬盤(pán)上保存表名和數(shù)據(jù)庫(kù)名。名稱比較對(duì)大小寫(xiě)敏感，默認(rèn)0

lower_case_table_names=0

insert delayed這個(gè)特性，異步插入到數(shù)據(jù)庫(kù)，MySQL的這個(gè)特性，是MySQL對(duì)標(biāo)準(zhǔn)SQL的一個(gè)擴(kuò)展，從MySQL 3.22.15 引入，5.6已經(jīng)不推薦使用，5.7已經(jīng)不支持了，雖然能識(shí)別，但是已經(jīng)被忽略掉

delayed_insert_timeout=300

delayed_insert_limit =100

delayed_queue_size=5000

max_delayed_threads=20

為非0值時(shí)，MySQL服務(wù)器會(huì)將所有打開(kāi)的表每隔flush_time指定的時(shí)長(zhǎng)進(jìn)行關(guān)閉，默認(rèn)是0

flush_time=0

interactive_timeout針對(duì)交互式連接，wait_timeout針對(duì)非交互式連接

通過(guò)mysql客戶端連接數(shù)據(jù)庫(kù)是交互式連接，通過(guò)jdbc連接數(shù)據(jù)庫(kù)是非交互式連接。

默認(rèn)都是28800

wait_timeout=28800

interactive_timeout=28800

每個(gè)連接線程被創(chuàng)建時(shí)，MySQL給它分配的內(nèi)存大小。當(dāng)MySQL創(chuàng)建一個(gè)新的連接線程時(shí)，需要給它分配一定大小的內(nèi)存堆棧空間，以便存放客戶端的請(qǐng)求的Query及自身的各種狀態(tài)和處理信息推薦使用默認(rèn)值

show variables like 'thread%';

thread_stack=256K

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

systemctl restart mysqld

yum -y install zabbix-agent

systemctl restart zabbix-agent.service

systemctl enable zabbix-agent.service

Zabbix 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。