mysqlのdatadirを変更したらapparmorに怒られて起動しなくなった

ディスクの都合でmysqlのデータの置き場所を変更しました.
mysqldを停止して,データを移動して,シンボリックリンクも一応貼っておく.
/etc/mysql/mysql.conf.d/mysqld.cnfでdatadirを変更.

[mysqld]
datadir         = /export/data/var/lib/mysql

この状態でmysqlを起動するとこんな感じのエラーで起動しなくなってしまいました.

2018-02-17T16:12:54.184655Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-02-17T16:12:54.184718Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-02-17T16:12:54.184734Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-02-17T16:12:54.785643Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-02-17T16:12:54.786151Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-02-17T16:12:54.786272Z 0 [ERROR] Failed to initialize builtin plugins.
2018-02-17T16:12:54.786415Z 0 [ERROR] Aborting

該当ファイルは一見問題無さそうに見えます.

$ sudo ls -la /export/data/var/lib/mysql/ibdata1
-rw-rw---- 1 mysql mysql 102760448  2月 18 05:15 /export/data/var/lib/mysql/ibdata1
$ sudo -u mysql dd if=/export/data/var/lib/mysql/ibdata1 bs=10 count=1|od -xc
1+0 レコード入力
1+0 レコード出力
10 bytes copied, 9.8366e-05 s, 102 kB/s
0000000    2214    405b    0000    0000    0000
        024   "   [   @  \0  \0  \0  \0  \0  \0
0000012

何でだ?と思ったらkernel logにこんなログが.apparmorで引っかかっているようです.

Feb 18 00:35:26 micro kernel: [ 3569.631324] audit: type=1400 audit(1518881726.300:24): apparmor="DENIED" operation="open" prof
ile="/usr/sbin/mysqld" name="/proc/18795/status" pid=18795 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114

/etc/apparmor.d/usr.sbin.mysqldでパスを変更します.

diff --git a/apparmor.d/usr.sbin.mysqld b/apparmor.d/usr.sbin.mysqld
index 2619e7d..adb8259 100644
--- a/apparmor.d/usr.sbin.mysqld   
+++ b/apparmor.d/usr.sbin.mysqld
@@ -46,16 +46,16 @@
   /usr/share/mysql/** r,  

 # Allow data dir access   
-  /var/lib/mysql/ r,
-  /var/lib/mysql/** rwk,  
+  /export/data/var/lib/mysql/ r,  
+  /export/data/var/lib/mysql/** rwk,

 # Allow data files dir access
-  /var/lib/mysql-files/ r,
-  /var/lib/mysql-files/** rwk,
+  /export/data/var/lib/mysql-files/ r,
+  /export/data/var/lib/mysql-files/** rwk,

 # Allow keyring dir access
-  /var/lib/mysql-keyring/ r,
-  /var/lib/mysql-keyring/** rwk, 
+  /export/data/var/lib/mysql-keyring/ r,
+  /export/data/var/lib/mysql-keyring/** rwk,

 # Allow log file access 
   /var/log/mysql.err rw,

この状態でapparmorを再起動して設定を反映してからmysqlを起動でOKでした.

$ sudo service apparmor restart
$ sudo service mysql start

この後iostat -xを眺めて大丈夫そうかなーって思ったのですがディスクアクセス音が大きくなったのでまた別の場所に移動するかも…….

環境

$ dpkg-query -W mysql-server
mysql-server    5.7.21-0ubuntu0.16.04.1
$ lsb_release -a
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.3 LTS
Release:        16.04
Codename:       xenial
$ uname -m
x86_64

WordPress へのspam 投稿をしたことのあるIP を拒否するようにした

最近WordPress へのコメントとトラックバックスパムが酷くなってきました。URL が含まれている物は承認が必要なようにしているのですが面倒です。このときにスパムはスパムだと手動で振り分けをしているので振り分けたものからIP を抜き出してアクセス制限を掛けるとましにならないかと設定してみました。

データベースから該当IP を抜き出す

MySQL からスパムを指定したIP の一覧は以下のようにして取得出来そうです。

$ cat /etc/wordpress/spamcommentip.sql
SELECT comment_author_IP FROM wordpress.wp_comments WHERE comment_approved='spam'
$ /usr/bin/mysql -umy -p < /etc/wordpress/spamcommentip.sql | /usr/bin/sort -n | /usr/bin/uniq -c| sort -n|cut -c-7|uniq -c
     81       1
     31       2
     26       3
     12       4
      8       5
      9       6
      4       7
      9       8
      1       9
      1      10
      3      11
      2      12
      3      13
      1      14
      1      15
      1      18
      4      20
      1      22
      1      23
      1      24
      1      26
      1      43
      1      82
      1     119
      1     146
      1     394

自動化したいので、MySQL にアクセスする為のMySQL の設定ファイルと、IP を抜き出すsqlファイルを用意します。

  • MySQL アクセスの為の設定ファイル

    $ cd /etc/wordpress
    $ umask 077
    $ sudo touch .my.conf
    $ sudo chown www-data.root .my.conf
    $ vi .my.conf
    cat .my.conf 
    user=wp
    password=XXXXXXXXXXXXXX
    database=wordpress
    
  • IP を抜き出す為のsqlファイル

    $ sudo vi /etc/wordpress/spamcommentip.sql
    $ cat $ cat /etc/wordpress/spamcommentip.sql
    SELECT comment_author_IP FROM wordpress.wp_comments WHERE comment_approved='spam'
    

以下のようにしてIP の一覧が取得出来るようになりました。

$ /usr/bin/mysql --defaults-file=/etc/wordpress/.my.conf < /etc/wordpress/spamcommentip.sql

apache httpd の .htaccess で指定IPアドレスからの WordPress のコメント投稿とトラックバックを制限

Debian のパッケージで WordPress を導入しているので導入パスは /usr/share/wordpress/ です。
設定ファイルは /etc/wordpress 以下です。
/usr/share/wordpress/.htaccess/etc/wordpress/htaccess のシンボリックリンクになっています。

アクセス制限をするには .htaccessdeny from ip address な感じで行けます。
さっきのIPの一覧取得時に並べ替えて同一IPをまとめて頭にdeny from を付けるようにします。

$ /usr/bin/mysql --defaults-file=/etc/wordpress/.my.conf < /etc/wordpress/spamcommentip.sql | /usr/bin/sort -n | /usr/bin/uniq | /bin/grep -v 'comment_author_IP' | /bin/sed "s/^/  deny from /"

あまり無いと思いますが、正しいIP アドレスを登録してしまっても本文が読めるようにコメントやトラックバックだけ制限しようと思います。コメント、トラックバックは次のwp-comments-post.php / wp-trackback.php を利用するようなのでこのファイルへのアクセスを制限します。
.htaccess の Files ディレクティブを利用して以下ような感じでいけそうです。

<Files ~ wp-comments-post.php|wp-trackback.php>
  deny from ip address
</Files>

動作確認は自分のIPアドレスを一時的に登録して行いました。blog本文にはアクセス出来てコメントURL は拒否されています。

% w3m -dump_head http://matoken.org/blog/blog/2015/06/09/facebook-pgp/|head -1
HTTP/1.0 200 OK
% w3m -dump_head http://matoken.org/blog/wp-comments-post.php|head -1
HTTP/1.1 403 Forbidden

cron を使って自動的に更新するようにする

IP list は外部ファイルにして読み込むようにすると便利そうですが、Include ディレクティブは .htaccess からは利用出来無いようなので分割ファイルを作って連結することにします。

  • /etc/wordpress/htaccess_base
    • 元のhtaccess
  • /etc/wordpress/htaccess_spamhead
    • Files ディレクティブの先頭
  • /etc/wordpress/htaccess_spamiplist
    • データベースから抜き出して作成した拒否IPリスト
  • /etc/wordpress/htaccess_spamtail
    • Files ディレクティブの末尾
  • /etc/wordpress/htaccess
    • 最終的に結合されて.htaccess のリンク元になるファイル

htaccess_base / htaccess_spamhead / htaccess_spamtail を用意します。

$ sudo cp -p /etc/wordpress/htaccess /etc/wordpress/htaccess_base
$ sudo sh -c "echo '<Files ~ wp-trackback.php|wp-comments-post.php>' > /etc/wordpress/htaccess_spamhead"
$ sudo sh -c "echo '</Files>' > /etc/wordpress/htaccess_spamtail"

htaccess_spamiplist / htaccess はcron で1時間毎に作成するようにします。

$ sudo -u www-data crontab -e
$ sudo -u www-data crontab -l
14 * * * *      /usr/bin/mysql --defaults-file=/etc/wordpress/.my.conf < /etc/wordpress/spamcommentip.sql | /usr/bin/sort -n | /usr/bin/uniq | /bin/grep -v 'comment_author_IP' | /bin/sed "s/^/  deny from /" > /etc/wordpress/htaccess_spamiplist && /bin/cat /etc/wordpress/htaccess-base /etc/wordpress/htaccess_spamhead /etc/wordpress/htaccess_spamiplist /etc/wordpress/htaccess_spamtail > /etc/wordpress/htaccess

これで1時間毎にWordPress 上でspam と判定したコメント/トラックバックの送信元IP からコメント/トラックバックを拒否するようになりました。
うまくいくといいのですが……。

追記)
設定して数日経ちましたが,spam激減しました!

mysqldump の警告を修正(Warning: Using unique option prefix event instead of events is deprecated and will be removed in a future release. Please use the full name instead.)

mysql のバックアップ時に警告が出ているのに気づきました.

Warning: Using unique option prefix event instead of events is deprecated and will be removed in a future release. Please use the full name instead.

メッセージで検索するとちょっと前のリリースノートにそれらしいものが.

Previously, program options could be specified in full or as any unambiguous prefix. For example, the –compress option could be given to mysqldump as –compr, but not as –comp because the latter is ambiguous. Option prefixes now are deprecated. They can cause problems when new options are implemented for programs. A prefix that is currently unambiguous might become ambiguous in the future. If an unambiguous prefix is given, a warning now occurs to provide feedback. For example: Warning: Using unique option prefix compr instead of compress is deprecated and will be removed in a future release. Please use the full name instead. Option prefixes are no longer supported in MySQL 5.7; only full options are accepted. (Bug #16996656) MySQL :: MySQL 5.6 Release Notes :: Changes in MySQL 5.6.13 (2013-07-31)

オプションを省略したりすると警告を出すようになったよ.5.7から使えなくなるよ.ということのようです.

バックアップはssh 経由のリモートで取得していて, ~/.ssh/authorized_keys に以下のような感じで書いていました.

command="/usr/bin/mysqldump --defaults-file=/home/user/.my.cnf --opt --all-databases --event | /usr/bin/xz -9",from="nnn.nnn.nnn.nnn",no-pty,no-port-forwarding,no-X11-forwarding,no-agent-forwarding ecdsa-sha2-nistp521 AAAA...

–event 部分は本来は –events の様なので以下のように修正して解決しました.

command="/usr/bin/mysqldump --defaults-file=/home/user/.my.cnf --opt --all-databases --events | /usr/bin/xz -9",from="nnn.nnn.nnn.nnn",no-pty,no-port-forwarding,no-X11-forwarding,no-agent-forwarding ecdsa-sha2-nistp521 AAAA...

#ちなみに受け側はこんな感じ 2 13 * * * /usr/bin/ssh -q -p nnnnn -i /home/user/.ssh/id_ecdsa-mysql-backup user@server > /backup/server/db/&#96;/bin/date +\%Y\%M\%d_\%H:\%m:\%S_\%s_$$&#96;.sql.xz`