上次librenms升完OS後

一直出現hd容量不夠的狀況

今天才發現 /var/log/syslog 這個檔變的很大

內容都是以下的log

2024-08-03T12:14:01.608311+08:00 W-librenms-debian12 mariadbd[221]: 2024-08-03 12:14:01 283381 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

2024-08-03T12:14:01.608631+08:00 W-librenms-debian12 mariadbd[221]: 2024-08-03 12:14:01 283381 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').

2024-08-03T12:14:01.608659+08:00 W-librenms-debian12 mariadbd[221]: 2024-08-03 12:14:01 283381 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

google了一下

說是升OS時DB沒升造成

使用以下指令升級DB

mysql_upgrade --user=root --password=passwd

目前看來正常了

再觀察看看

https://community.home-assistant.io/t/mariadb-errors-after-update/424731/3

今天打開librenms的時候發現無法進入

出現錯誤訊息

查了一下log 發現  5/30 03:00左右就出問題了

重開後正常

但跑了一下valid.php出現以下錯誤

./validate.php

====================================

Component | Version

--------- | -------

LibreNMS  | 21.5.1-16-g15da7fa

DB Schema | 2020_12_14_091314_create_port_group_port_table (205)

PHP       | 7.4.19

Python    | 3.6.8

MySQL     | 5.5.68-MariaDB

RRDTool   | 1.4.8

SNMP      | NET-SNMP 5.7.2

====================================

[OK]    Composer Version: 2.0.14

[OK]    Dependencies up-to-date.

[OK]    Database connection successful

[FAIL]  MariaDB version 10.2.2 is the minimum supported version as of March, 2021. Update MariaDB to a supported version 10.5 suggested).

[FAIL]  Your database is out of date!

        [FIX]:

        ./lnms migrate

[WARN]  Global lnms shortcut not installed. lnms command must be run with full path

        [FIX]:

        sudo ln -s /opt/librenms/lnms /usr/bin/lnms

[WARN]  Bash completion not installed. lnms command tab completion unavailable.

        [FIX]:

        sudo cp /opt/librenms/misc/lnms-completion.bash /etc/bash_completion.d/

[WARN]  Log rotation not enabled, could cause disk space issues

        [FIX]:

        sudo cp /opt/librenms/misc/librenms.logrotate /etc/logrotate.d/librenms

[WARN]  Your install is over 24 hours out of date, last update: Sat, 29 May 2021 14:08:28 +0000

        [FIX]:

        Make sure your daily.sh cron is running and run ./daily.sh by hand to see if there are any errors.

[FAIL]  We have found some files that are owned by a different user than 'librenms', this will stop you updating automatically and / or rrd files being updated causing graphs to fail.

        [FIX]:

        sudo chown -R librenms:librenms /opt/librenms

        sudo setfacl -d -m g::rwx /opt/librenms/rrd /opt/librenms/logs /opt/librenms/bootstrap/cache/ /opt/librenms/storage/

        sudo chmod -R ug=rwX /opt/librenms/rrd /opt/librenms/logs /opt/librenms/bootstrap/cache/ /opt/librenms/storage/

        Files:

         /opt/librenms/config.php_20201107

         /opt/librenms/html/plugins/Weathermap/nkhc.png

         /opt/librenms/html/plugins/Weathermap/nkhc.html

除了mariadb升級外

其他先手動做完沒問題

想說     ./lnms migrate 應該會跟db有關

所以先升db 好了

先備db

    $ mysqldump -u root -p --all-database > mysql-backup.sql

備config

# cp /etc/my.cnf /etc/my.cnf.bak

加入mariadb的repo

# vi /etc/yum.repos.d/MariaDB.repo

加入以下內容:

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.5/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

停止及移除目前的 MariaDB:

    # systemctl stop mariadb

    # yum remove mariadb mariadb-server

現在開始安裝 MariaDB 10.5,

    # yum install mariadb mariadb-server

啟動 MariaDB 及設定開機自動啟動:

    # systemctl enable  mariadb

    # systemctl start  mariadb

將原來的 MariaDB 資料升級:

    # mysql_upgrade -u root -p

升級過程沒出現什麼問題

再來跑一下

        ./lnms migrate

出現以下錯誤

Migrating: 2020_12_14_091314_create_port_groups_table

In Connection.php line 678:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists (SQL: create table port_groups (id int unsi
gned not null auto_increment primary key, name varchar(255) not null, desc varchar(255) null) default character set utf8mb4 collate ‘ut
f8mb4_unicode_ci’)

In Exception.php line 18:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists

In PDOStatement.php line 112:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists

進DB先把 port_groups drop 掉

再跑一次

        ./lnms migrate

就正常了

最後再跑一次

./valid.php

沒有任何問題

後續觀察中

https://www.opencli.com/mysql/rhel-centos7-upgrade-mariadb-to-10-5 

https://community.librenms.org/t/validation-gives-failure-to-update-mariadb-will-eventually-cause-issues-lnms-migrate-gives-error/15391

最近几次cacti的更新都一直有問題
所以都要倒回之前的備份
手動修改後
再做一次
這次是要把
/usr/share/cacti/install/upgrades/1_2_8.php
內的這行刪掉
db_install_execute('ALTER TABLE host_snmp_cache MODIFY COLUMN snmp_index VARCHAR(255) NOT NULL default ""');

查了一下好像目前的mariadb的版本沒辦法醬操作 要升版

今天先把mariadb升級
先保留php-5.4.16-46.1.el7_7.x86_64
下次cacti升級再看看有什麼問題
再來處理

新增以下檔案

/etc/yum.repos.d/mariadb.repo

內容如下

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

改好後直接 yum -y update

mariadb就會直接升了

再來要 mv /etc/my.cnf /etc/my.cnf_old 不然db會起不來

接下來照文件改

Add variables in the [mysqld] section.

collation-server = utf8mb4_unicode_ci
character-set-server=utf8mb4
max_heap_table_size = 64M
tmp_table_size = 64M
join_buffer_size = 64M
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_flush_log_at_timeout = 3
innodb_buffer_pool_size = 1GB
innodb_buffer_pool_instances = 10
# Based on what type for storage you use. The below values are for SSD drives.
# Change it if Cacti reports issues during the installation
innodb_read_io_threads = 32
innodb_write_io_threads = 16
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000

db重啟就好了 

再把這個補上去

ALTER TABLE host_snmp_cache MODIFY COLUMN snmp_index VARCHAR(255) NOT NULL default ""

https://www.itzgeek.com/how-tos/linux/centos-how-tos/monitor-network-using-cacti-with-centos-7-rhel-7.html

昨天晚上server發生filesystem錯誤

重開後mariadb出現問題
到log去看時發現以下資訊

171002  8:51:09  InnoDB: cannot calculate statistics for table ABC/def
InnoDB: because the .ibd file is missing.  For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
171002  8:51:09 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table ABC/def does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

到目錄去看時
def.idb這個檔真的不見了
查了一下
這個檔不見是沒辦法由目前的資料再產生的
只能recovery

找了一下備分
倒回去
目前看來正常
再觀察看看

今天要登入awacs(網管軟体)時
出現無法連接的訊息
登入主机發現mariadb的daemon不見了
systemctl restart mariadb也起不來
看了一下 /var/log/mariadb/mariadb.log 發現以下的記錄

170824 19:02:01  InnoDB: Page checksum 2583736692 (32bit_calc: 3902863637), prior-to-4.0.14-form checksum 2992650943
InnoDB: stored checksum 218772529, prior-to-4.0.14-form stored checksum 775370784
InnoDB: Page lsn 825440558 909582385, low 4 bytes of lsn at page end 825373998
InnoDB: Page number (if stored to page already) 775303712,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 775041840
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

按照說明
在/etc/my.cnf 加上

[mysqld]
innodb_force_recovery = 1

重啟mariadb

再查一下log

170824 19:02:01  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 1553519:2, should be 1553541:2!
170824 19:02:01  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 1553519:3, should be 1553541:3!

InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
170824 19:02:01  InnoDB: Rolling back trx with id 127203F, 451 rows to undo
170824 19:02:01  InnoDB: Waiting for the background threads to start

InnoDB: Rolling back of trx id 127203F completed
170824 19:02:01  InnoDB: Rollback of non-prepared transactions completed
170824 19:02:02 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 653240202086
170824 19:02:02 InnoDB: !!! innodb_force_recovery is set to 1 !!!
170824 19:02:02 [Note] Plugin 'FEEDBACK' is disabled.
170824 19:02:02 [Note] Server socket created on IP: '0.0.0.0'.
170824 19:02:02 [Note] Event Scheduler: Loaded 0 events
170824 19:02:02 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.41-MariaDB'  socket: '/home/Alopex/mysql/mysql.sock'  port: 3306  MariaDB Server
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

看來是修好了
不過user還是不能用

InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

要再把

[mysqld]
innodb_force_recovery = 1

mark掉再重開

目前看來是正常了
再觀察看看


https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

cacti從過年後就一直升級
今天又升到1.1.6了
本來是跑在ubuntu 14.04上
因為之前升過一次有問題
所以放著一直沒去動
不過最近查了一下 centos 的 epel有放進去了
用yum應該很方便 而且維護也比較簡單
轉過去沒啥大問題
mariadb的參數要做些調整 還要設定時區
在install的Pre-installation Checks頁面中有提到
再來就是碰到二個問題
一個就是本來有寫一些用來撈資料的script 放在/root
然後再ln到cacti的目錄
但這個版本使用上會有問題
所以只好把這些script直接copy到/usr/share/cacti/scripts裡
copy完後記得要rebuild poller cache才會生效

另一個問題比較奇怪
本來有利用snmp從ruckus的controller上撈線上人數
但轉到新版後圖出不來了
但aruba沒問題
可是用snmpwalk去撈又能拿到資料
看了設定也沒錯
最後

在config把snmp改成v1

就............好...............了

先醬
跑看看
目前polling一次時間大約15秒內

在python中撈取mysql的資料
在cursor.execute後可以用cursor.fetchone()及cursor.fetchall()

如果回傳的只有一筆資料 可以使用cursor.fetchone() 可以少一點code
語法

cursor.execute(select count(*) from table)
result = cursor.fetchone()
print result[0]

如果回傳的資料有很多 則使用cursor.fetchall()

語法
cursor.execute(select * from table)
result = cursor.fetchall()
if result:
for record in result:
print record[0]

今天又有一個需求
因為資料在計算的過程中會有時間差
所以有可能會產生二筆相同的資料
如下圖

可是因為某些原因
不希望讓使用者看到
原本想用DISTINCT來解決
但DISTINCT只能用在一個欄位
花時間找了一下資料
發現直接用group by就可以解決了

select user,ip from table group by ip;

http://tc.wangchao.net.cn/bbs/detail_1846934.html

最近的工作就是一直用python來連mariadb

今天有個需求
要把從db撈出來的資料寫到檔案
找到這個方便的方法

cursor.execute("test from  test_table")
result = cursor.fetchall()
if result:
f = open("/tmp/data",'w')
        for record in result:
                print record[0],          #在畫面上印出資料
print>>f, record[0]    #把資料寫進檔案

今天使用python要把資料塞進mysql時一直出問題
明明就有執行
也沒錯誤訊訊息
但資料就是沒進去
本來以為是跳脫字元的問題
試了也沒有
後來找到一個方便的語法
也不用再考慮單引號 雙引號要使用跳脫字元的情況
語法如下
最後的 db.commit() 一定要下
就是因為這個沒下
才試了一下午
最後還是問了高手才知道的

sql="insert into table values(%s,%s,%s,)"
cursor.execute(sql,(now,i,88888))
db.commit()

now是現在時間
i是變數
88888是數字

對應到table
datetime
varchar(50)
bigint(20)

今天本來是要升級OCS
但升到2.2後畫面竟然只有几個數字
而且升級的過程一直不順
想說重裝好了
因為原來是裝在centos 5上
而centos 5 的EOL是2017年3 月
所以試看看裝在centos 7 會不會比較沒問題
查了一下
現在在centos 7 上裝很簡單了
稍微記錄一下
最小安裝後改一下hosts.allow hosts.deny 及selinux
關掉filewalld
systemctl stop firewalld
systemctl disable firewalld

再裝一下net-tools及 epel-release
epel-release是用來加入額外repo
安裝mariadb
yum install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb

修改mariadb的root密碼及一些安全設定
mysql_secure_installation

再來安裝ocs
yum install -y ocsinventory-server ocsinventory-reports

因為加入了epel-release 所以系統會把所有相依性的套件一次裝好

啟動httpd服務
systemctl start httpd
sytemctl enable httpd

接下來就可以進入web介面 依畫面指示進行接下來的安裝動作了
http://ocs-server-ip/ocsreports/

預設登入帳號密碼是 admin/admin 記得要改

修改server ip 使用文字化圖形介面指令
nmtui

目前的版本是2.1.2
再等一陣子看看 應該會直接升到 2.2 到時直接使用yum update就好了

今天接到一個工作
要把snort裡的資料匯出成文字檔給外面的單位
因為BASE沒辦法一次全部匯出
所以要自己寫sql了

select event.cid,signature,sig_name,inet_ntoa(iphdr.ip_src),inet_ntoa(iphdr.ip_dst),timestamp from iphdr,event,signature where event.signature=signature.sig_id and event.cid=iphdr.cid into outfile '/tmp/sqloutput.txt';


http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html
http://sgros.blogspot.tw/2012/07/querying-snort-sql-database.html
http://note.tc.edu.tw/670.html