ER図の自動生成

ER図などDBの定義を自動で生成できる、SchemaSpy。Docker Hub でホスティングされていて、非常に便利。

$ docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:latest -t pgsql -host [DBHOST] -db [DBNAME] -u [DBUSER] -p [DBPASS]

rsync を使ったサーバー間のデータコピー

rsync -e "ssh -p 2288" -rva /home/user/work user@111.22.3.0:/home/user/work

ポートを指定する場合は、-e “ssh -p 2288”で指定する。
オプション-nで、dry-run実行となる。

rsync -e "ssh -p 2288" -rvna /home/user/work user@111.22.3.0:/home/user/work

失敗する場合は、パーミッションの問題が多そうです。

PostgreSQL で 横断的にカラムを検索する

ドキュメントが揃っている場合は、ドキュメントをみるのが良いですが、そうではない場合、
PostgreSQLで、外部キーとして設定されてそうなカラムをざっくりみたいなという場合のクエリです。

staff_id という名前のカラムが存在するテーブルの一覧

select t.table_schema,
       t.table_name,
       c.column_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'staff_id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

Let’s encrypt 更新失敗 対応

sclでインストールした、python2.7 で、証明書の自動更新が失敗していた為、
手動で実行してみると下記のエラーとなる。

OSError: Command /opt/eff.org/certbot/venv/bin/python2.7 -c "import sys, pip; sys...d\"] + sys.argv[1:]))" setuptools pip wheel failed with error code 1

調べてみると、pipのバージョンが古い可能性が有るということなので、下記を実行して更新ができるようになりました。

pip2.7 install --upgrade setuptools
pip2.7 install --upgrade virtualenv
pip2.7 install --upgrade pip

Let’s encrypt 証明書失効処理

使用していないドメインを、certbot-auto で更新しない形にしたかったので、失効処理を調べて実行した際のメモ

./certbot-auto revoke --cert-path=/etc/letsencrypt/archive/www.example.com/cert1.pem

cert1.pem は、最新の証明書を指定。
実行すると、下記、証明書の格納ディレクトリ、更新ファイルが削除されていました。

/etc/letsencrypt/archive/www.example.com/
/etc/letsencrypt/live/www.example.com/
/etc/letsencrypt/renewal/www.example.com.conf

CentOS7.2 fail2ban で DDos を防ぐ

毎回設定を忘れるのでメモ

filter

$ vi /etc/fail2ban/filter.d/apache-ddos.conf
[Definition]
failregex = <HOST>.*"(HEAD|GET|POST).*
ignoreregex = \.(?i)(jpe?g|gif|png|bmp|pdf|js|css|woff|eot|ttf|ico|txt|xml|swf|xlsx?|docx?|pptx?)
              \/wp-admin.*

banする条件

$ vi /etc/fail2ban/jail.d/local.conf

[apache-ddos]
enabled = true
port = http,https
# フィルタ名
filter = apache-ddos
# 監視するログ名
logpath = /var/log/httpd/ssl/www*_access_log

# 許容する接続回数
# 5回以上不正に接続するとBANされる
maxretry = 5
# 不正アクセスカウント時間
# 80秒(80)
findtime = 360
# BANした後、再び接続できるようになるまでの時間(秒)
# 86400秒 = 1日
bantime = 86400
backend = polling

自分のIPは除外

$ vi /etc/fail2ban/jail.conf

[DEFAULT]

#
# MISCELLANEOUS OPTIONS
#

# "ignoreip" can be an IP address, a CIDR mask or a DNS host. Fail2ban will not
# ban a host which matches an address in this list. Several addresses can be
# defined using space (and/or comma) separator.
ignoreip = 127.0.0.1/8 xxx.xxx.xxx.xxx/32

設定の有効化・チェック

$ systemctl reload fail2ban
$ fail2ban-regex /var/log/httpd/ssl/www.example.com_access_log /etc/fail2ban/filter.d/apache-ddos.conf ← フィルターのチェック
$ fail2ban-client status apache-ddos ← banされたIPアドレスの確認
$ less /var/log/fail2ban.log ← fail2banのログの確認

Docker on Vagrant(CentOS7)でDockerから外部に出ていけない現象

結論

net.ipv4.conf.all.forwarding = 0になっているので、1にする。

$ sysctl net.ipv4.conf.all.forwarding
net.ipv4.conf.all.forwarding = 0

$ sudo sysctl net.ipv4.conf.all.forwarding=1
net.ipv4.conf.all.forwarding = 1
$ sysctl net.ipv4.conf.all.forwarding
net.ipv4.conf.all.forwarding = 1

事の発端

HostからVagrant経由で、Dockerにアクセスしてもなぜか繋がらなくなった。

docker-composeでビルドし直してみる。

$ docker-compose build --no-cache
....
 IPv4 forwarding is disabled. Networking will not work

apt-getでパッケージupdateしようとした所、外部に出ていけなくなっていた。

Vagrant上の、CentOSの、net.ipv4.conf.all.forwardingが 0 になっていて、外部に出れなくなっていた。

原因

ネットワークの再起動で、

sysctl -w net.ipv4.ip_forward=0 > /dev/null 2>&1

となっている。

対策

/etc/sysctl.conf

net.ipv4.ip_forward=1

ブログサーバー移行(ServersMan→ConoHa)

このブログを運用しているサーバーを、ServersMan→ConoHaへ移行しました。

長らく更新しておらず、後ろめたい感じだったのでこれから更新再開していきたいと思います。

MySQL で容量を確認するSQL

データベース毎の容量

SELECT
    table_schema                                         AS "データベース名"
  , FLOOR(SUM(data_length + index_length) / 1024 / 1024) AS "合計容量(MB)"
FROM information_schema.tables
GROUP BY
    table_schema
ORDER BY
    SUM(data_length + index_length) DESC
;

+-----------------------+------------------+
| データベース名        | 合計容量(MB)     |
+-----------------------+------------------+
| xxxxxxxx_xxxxxxxx     |             1995 |
| information_schema    |                0 |
+-----------------------+------------------+

テーブル毎の容量

use DATABASE_NAMEで確認したいデータベースに切り替えた後に実行。

SELECT
    table_name                                        AS "テーブル名"
  , FLOOR((data_length + index_length) / 1024 / 1024) AS "合計容量(MB)"
  , FLOOR((data_length) / 1024 / 1024)                AS "データ容量(MB)"
  , FLOOR((index_length) / 1024 / 1024)               AS "インデックス容量(MB)"
  , table_rows                                        AS "行数"
  , avg_row_length                                    AS "平均行容量"
  , engine                                            AS "ストレージエンジン"
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY
    (data_length + index_length) DESC
;

+-------------------+------------------+---------------------+------------------------------+----------+-----------------+-----------------------------+
| テーブル名        | 合計容量(MB)     | データ容量(MB)      | インデックス容量(MB)         | 行数     | 平均行容量      | ストレージエンジン          |
+-------------------+------------------+---------------------+------------------------------+----------+-----------------+-----------------------------+
| aaa               |             1781 |                1132 |                          649 | 10340935 |             114 | InnoDB                      |
| bbb               |               99 |                  42 |                           56 |   842684 |              52 | InnoDB                      |
| ccc               |               53 |                  50 |                            3 |    97275 |             545 | InnoDB                      |
| ddd               |               48 |                  34 |                           13 |   614400 |              58 | InnoDB                      |
| eee               |               12 |                   5 |                            6 |    84417 |              68 | InnoDB                      |
| fff               |                1 |                   0 |                            0 |     7083 |              53 | InnoDB                      |
| ggg               |                0 |                   0 |                            0 |     2598 |              63 | InnoDB                      |
| hhh               |                0 |                   0 |                            0 |     1543 |              63 | InnoDB                      |
| iii               |                0 |                   0 |                            0 |      233 |              70 | InnoDB                      |
| jjj               |                0 |                   0 |                            0 |      233 |              70 | InnoDB                      |
| kkk               |                0 |                   0 |                            0 |       69 |             237 | InnoDB                      |
| lll               |                0 |                   0 |                            0 |      265 |              61 | InnoDB                      |
| mmm               |                0 |                   0 |                            0 |        1 |           16384 | InnoDB                      |
+-------------------+------------------+---------------------+------------------------------+----------+-----------------+-----------------------------+