MariaDB Max connect

參考
https://pjstrnad.com/mariadb-raise-number-of-connections/

The issue is that you can’t have more max_connections than open_files_limit.

Indication is at the log

Changed limits: max_open_files: 1024 max_connections: 214 table_cache: 400

So you got to /etc/my.cnf and under [mysqld] add

open_files_limit=12000
max_connections=10000

But it’s not enough. Because of the systemd there are some limits in starting the mysql server – it’s already started with mysql user.

So you need to go to /etc/security/limits.conf and add

mysql soft nofile 4096
mysql hard nofile 10240

Then settings for systemd

mkdir -p /etc/systemd/system/mariadb.service.d
vi /etc/systemd/system/mariadb.service.d/limits.conf

And then enter this to to the file:

[Service]
LimitNOFILE=infinity

that worked to me. After restart of the mysql by

systemctl daemon-reload

systemctl restart mariadb

I got the connections fixed:

mysql
MariaDB [(none)]> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 10000             |
+-------------------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+

That’s all.

mysql binlog too large

檢查硬碟空間
df -h

檢查資料夾大小
du -sh /home/james/*

MySQL log 太大
設定
/etc/my.cnf

只保留七天的 Log
[mysqld]
expire-logs-days=7

重啟 mysql
systemctl restart mariadb

進入 mysql
mysql -u root -p

刷新 log
FLUSH LOGS;

檢查目前 log 在哪個位置
SHOW MASTER STATUS;

清除指定時間 log
PURGE BINARY LOGS BEFORE NOW();

或清指定的 log 檔
PURGE BINARY LOGS TO 'mysql-bin.000047';

MariaDB max_connections, table_open_cache stuck

copy my-hugh.cnf to /etc/my.cnf.d/

editing
/etc/sysctl.conf
fs.file-max = 2459688

editing
/etc/security/limits.conf
mysql           soft    nofile         4096
mysql           hard    nofile         4096

editing
/etc/my.cnf.d/my-hugh.cnf
max_connections=1024
max_user_connections=1024
table_open_cache = 4096
table_definition_cache = 4096
open_files_limit=4096

ref:
https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/

http://blog.endpoint.com/2013/12/increasing-mysql-55-maxconnections-on.html

GeoIP Update

系統
CentOS

https://github.com/maxmind/geoipupdate

先到官方 git clone
$ git clone https://github.com/maxmind/geoipupdate
$ cd geoipupdate
$ ./bootstrap
使用 bootstrap 遇到問題要裝以下幾項
yum install autoconf automake libtool

執行並安裝
$ ./configure
$ make
$ sudo make install


可能會遇到錯誤, curl, zlib 安裝
yum install curl-devel zlib-devel


設定下載參數
http://dev.maxmind.com/geoip/geoipupdate/

照上方步驟安裝完 geoipupdate 後會自動寫一份 config 到
/usr/local/etc/GeoIP.conf
修改這份 config 並 mark 原本設定
UserId XXX
LicenseKey XXX
ProductIds GeoIP2-City GeoIP2-Country


然後手動新增資料夾
/usr/local/share/GeoIP

第一次執行 geoipupdate
檔案會寫入
/usr/local/share/GeoIP

設定排程 /etc/crontab
52 11 * * 6 /usr/local/bin/geoipupdate

Clickjacking 防範

NGINX 設定

add_header X-Frame-Options "SAMEORIGIN";

參考文章
https://developer.mozilla.org/zh-TW/docs/Web/HTTP/X-Frame-Options
https://geekflare.com/add-x-frame-options-nginx/
http://devco.re/blog/2014/04/08/security-issues-of-http-headers-2-content-security-policy/

SELinux 影響 ssh with key login

如果沒有關閉 SELinux 
ssh 使用 rsa 登入時會遇到錯誤, 
上傳 key 後時要記得更新資料夾權限

restorecon -Rv /folder

就可以正常使用了