タグ: mysql

mysqldump を pv で制限

matoken 1件のコメント

mysqldump 動いてる時間に同サーバで GNU social とか Nextcloud とかの mysql を利用しているアプリケーションが重くて使い物にならないです．nice + ionice は指定していますが効いてない感じ．
dump した sql を圧縮している xz コマンドが cpu を1 core 使い潰しているようです．このマシンは2 core あるのですが，もう1つの core もその他の処理でほぼ使い切って待ちが出ているような感じ．

$ vmstat 1 10
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0 328212 264788 212260 5628192    1    2    35   160    2   15 35  9 44 12  0
 1  1 328212 259136 212264 5628192    0    0     0   191 1497 3132 63  6 19 12  0
 1  1 328212 252492 212264 5628192    0    0     0    88  959 1716 81  6  7  6  0
 2  1 328212 248264 212264 5628196    0    0     0   114  973 1887 85  3  7  5  0
 2  0 328212 242572 212264 5628196    0    0     0    85  972 1967 74  5 15  5  0
 4  0 328212 241664 212100 5624380    0    0     0    97 1106 4226 91  7  2  2  0
 2  1 328212 234268 212100 5624384    0    0     0   140 1013 1815 70  5 19  7  0
 3  0 328212 235760 211944 5620076    0    0     0  2461 1289 4063 94  4  1  0  0
 1  0 328212 229656 211944 5620080    0    0     0   110  953 1685 61  6 18 15  0
 1  0 328212 224616 211952 5620072    0    0     0   151  983 1683 84  2  8  6  0

pv -L で帯域絞ってみました．
cpu の様子を見ながらだんだん絞っていって 128k 迄絞ってやっと xz の cpu 25〜40% 位になりました．この状態だと普通に使える感じです．
しばらくこれで試してみます．

pv で帯域制限
$ time sh -c "nice -n 19 ionice -c 3 mysqldump --defaults-file=/backup/micro/.my-backup.cnf --single-transaction --quick
--all-databases --events | pv -L 128k 2>/dev/null | nice -n 19 ionice -c 3 xz -9 > /dev/null"

real 127m16.113s
user 34m30.508s
sys 0m23.428s
cronでの実行
$ sudo -u backup crontab -l | grep mysqldump
14 3 * * *     umask 0266 && nice -n 19 ionice -c 3 /usr/bin/mysqldump --defaults-file=/mnt/backup/micro/.my-backup.cnf --single-transaction --quick --all-databases --events | pv -L 128k 2>/dev/null | nice -n 19 ionice -c 3 /usr/bin/xz -9 > /mnt/backup/micro/`date +\%F_\%T_$$`.sql.xz
環境
$ dpkg-query -W mysql-client-5.7 pv xz-utils
mysql-client-5.7        5.7.23-0ubuntu0.16.04.1
pv      1.6.0-1
xz-utils        5.1.1alpha+20120614-2ubuntu2
$ lsb_release -d
Description:    Ubuntu 16.04.5 LTS
$ uname -m
x86_64
$ grep -m1 model\ name /proc/cpuinfo
model name      : AMD Athlon(tm) II Neo N36L Dual-Core Processor

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

matoken コメントをする

ディスクの都合で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   "   [   @  
$ 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

  
$ 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

  
$ 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

  
$ 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

  
$ 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

  
$ 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


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 を拒否するようにした

matoken コメントをする

最近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 のシンボリックリンクになっています。

アクセス制限をするには .htaccess に deny 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 https://matoken.org/blog/blog/2015/06/09/facebook-pgp/|head -1
HTTP/1.0 200 OK
% w3m -dump_head https://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.)

matoken コメントをする

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`