{ "version": "https://jsonfeed.org/version/1.1", "user_comment": "This feed allows you to read the posts from this site in any feed reader that supports the JSON Feed format. To add this feed to your reader, copy the following URL -- https://matoken.org/blog/tag/mysql/feed/json/ -- and add it your reader.", "home_page_url": "https://matoken.org/blog/tag/mysql/", "feed_url": "https://matoken.org/blog/tag/mysql/feed/json/", "language": "ja", "title": "mysql – matoken's blog", "description": "Is there no plan B?", "icon": "https://matoken.org/blog/wp-content/uploads/2025/03/cropped-1865f695c4eecc844385acef2f078255036adccd42c254580ea3844543ab56d9.jpeg", "items": [ { "id": "http://matoken.org/blog/?p=2176", "url": "https://matoken.org/blog/2018/10/11/mysqldump-limit-by-pv/", "title": "mysqldump \u3092 pv \u3067\u5236\u9650", "content_html": "
\n

mysqldump \u52d5\u3044\u3066\u308b\u6642\u9593\u306b\u540c\u30b5\u30fc\u30d0\u3067 GNU social \u3068\u304b Nextcloud \u3068\u304b\u306e mysql \u3092\u5229\u7528\u3057\u3066\u3044\u308b\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u304c\u91cd\u304f\u3066\u4f7f\u3044\u7269\u306b\u306a\u3089\u306a\u3044\u3067\u3059\uff0enice + ionice \u306f\u6307\u5b9a\u3057\u3066\u3044\u307e\u3059\u304c\u52b9\u3044\u3066\u306a\u3044\u611f\u3058\uff0e
\ndump \u3057\u305f sql \u3092\u5727\u7e2e\u3057\u3066\u3044\u308b xz \u30b3\u30de\u30f3\u30c9\u304c cpu \u30921 core \u4f7f\u3044\u6f70\u3057\u3066\u3044\u308b\u3088\u3046\u3067\u3059\uff0e\u3053\u306e\u30de\u30b7\u30f3\u306f2 core \u3042\u308b\u306e\u3067\u3059\u304c\uff0c\u3082\u30461\u3064\u306e core \u3082\u305d\u306e\u4ed6\u306e\u51e6\u7406\u3067\u307b\u307c\u4f7f\u3044\u5207\u3063\u3066\u5f85\u3061\u304c\u51fa\u3066\u3044\u308b\u3088\u3046\u306a\u611f\u3058\uff0e

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

pv -L \u3067\u5e2f\u57df\u7d5e\u3063\u3066\u307f\u307e\u3057\u305f\uff0e
\ncpu \u306e\u69d8\u5b50\u3092\u898b\u306a\u304c\u3089\u3060\u3093\u3060\u3093\u7d5e\u3063\u3066\u3044\u3063\u3066 128k \u8fc4\u7d5e\u3063\u3066\u3084\u3063\u3068 xz \u306e cpu 25\u301c40% \u4f4d\u306b\u306a\u308a\u307e\u3057\u305f\uff0e\u3053\u306e\u72b6\u614b\u3060\u3068\u666e\u901a\u306b\u4f7f\u3048\u308b\u611f\u3058\u3067\u3059\uff0e
\n\u3057\u3070\u3089\u304f\u3053\u308c\u3067\u8a66\u3057\u3066\u307f\u307e\u3059\uff0e

\n
\n
\n
pv \u3067\u5e2f\u57df\u5236\u9650
\n
\n
$ time sh -c \"nice -n 19 ionice -c 3 mysqldump --defaults-file=/backup/micro/.my-backup.cnf --single-transaction --quick\r\n--all-databases --events | pv -L 128k 2>/dev/null | nice -n 19 ionice -c 3 xz -9 > /dev/null\"\r\n\r\nreal 127m16.113s\r\nuser 34m30.508s\r\nsys 0m23.428s
\n
\n
\n
\n
cron\u3067\u306e\u5b9f\u884c
\n
\n
$ sudo -u backup crontab -l | grep mysqldump\r\n14 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
\n
\n
\n
\n
\u74b0\u5883
\n
\n
$ dpkg-query -W mysql-client-5.7 pv xz-utils\r\nmysql-client-5.7        5.7.23-0ubuntu0.16.04.1\r\npv      1.6.0-1\r\nxz-utils        5.1.1alpha+20120614-2ubuntu2\r\n$ lsb_release -d\r\nDescription:    Ubuntu 16.04.5 LTS\r\n$ uname -m\r\nx86_64\r\n$ grep -m1 model\\ name /proc/cpuinfo\r\nmodel name      : AMD Athlon(tm) II Neo N36L Dual-Core Processor
\n
\n
\n", "content_text": "mysqldump \u52d5\u3044\u3066\u308b\u6642\u9593\u306b\u540c\u30b5\u30fc\u30d0\u3067 GNU social \u3068\u304b Nextcloud \u3068\u304b\u306e mysql \u3092\u5229\u7528\u3057\u3066\u3044\u308b\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u304c\u91cd\u304f\u3066\u4f7f\u3044\u7269\u306b\u306a\u3089\u306a\u3044\u3067\u3059\uff0enice + ionice \u306f\u6307\u5b9a\u3057\u3066\u3044\u307e\u3059\u304c\u52b9\u3044\u3066\u306a\u3044\u611f\u3058\uff0e\ndump \u3057\u305f sql \u3092\u5727\u7e2e\u3057\u3066\u3044\u308b xz \u30b3\u30de\u30f3\u30c9\u304c cpu \u30921 core \u4f7f\u3044\u6f70\u3057\u3066\u3044\u308b\u3088\u3046\u3067\u3059\uff0e\u3053\u306e\u30de\u30b7\u30f3\u306f2 core \u3042\u308b\u306e\u3067\u3059\u304c\uff0c\u3082\u30461\u3064\u306e core \u3082\u305d\u306e\u4ed6\u306e\u51e6\u7406\u3067\u307b\u307c\u4f7f\u3044\u5207\u3063\u3066\u5f85\u3061\u304c\u51fa\u3066\u3044\u308b\u3088\u3046\u306a\u611f\u3058\uff0e\n\n\n\n$ vmstat 1 10\r\nprocs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----\r\n r b swpd free buff cache si so bi bo in cs us sy id wa st\r\n 2 0 328212 264788 212260 5628192 1 2 35 160 2 15 35 9 44 12 0\r\n 1 1 328212 259136 212264 5628192 0 0 0 191 1497 3132 63 6 19 12 0\r\n 1 1 328212 252492 212264 5628192 0 0 0 88 959 1716 81 6 7 6 0\r\n 2 1 328212 248264 212264 5628196 0 0 0 114 973 1887 85 3 7 5 0\r\n 2 0 328212 242572 212264 5628196 0 0 0 85 972 1967 74 5 15 5 0\r\n 4 0 328212 241664 212100 5624380 0 0 0 97 1106 4226 91 7 2 2 0\r\n 2 1 328212 234268 212100 5624384 0 0 0 140 1013 1815 70 5 19 7 0\r\n 3 0 328212 235760 211944 5620076 0 0 0 2461 1289 4063 94 4 1 0 0\r\n 1 0 328212 229656 211944 5620080 0 0 0 110 953 1685 61 6 18 15 0\r\n 1 0 328212 224616 211952 5620072 0 0 0 151 983 1683 84 2 8 6 0\n\n\n\npv -L \u3067\u5e2f\u57df\u7d5e\u3063\u3066\u307f\u307e\u3057\u305f\uff0e\ncpu \u306e\u69d8\u5b50\u3092\u898b\u306a\u304c\u3089\u3060\u3093\u3060\u3093\u7d5e\u3063\u3066\u3044\u3063\u3066 128k \u8fc4\u7d5e\u3063\u3066\u3084\u3063\u3068 xz \u306e cpu 25\u301c40% \u4f4d\u306b\u306a\u308a\u307e\u3057\u305f\uff0e\u3053\u306e\u72b6\u614b\u3060\u3068\u666e\u901a\u306b\u4f7f\u3048\u308b\u611f\u3058\u3067\u3059\uff0e\n\u3057\u3070\u3089\u304f\u3053\u308c\u3067\u8a66\u3057\u3066\u307f\u307e\u3059\uff0e\n\n\npv \u3067\u5e2f\u57df\u5236\u9650\n\n$ time sh -c \"nice -n 19 ionice -c 3 mysqldump --defaults-file=/backup/micro/.my-backup.cnf --single-transaction --quick\r\n--all-databases --events | pv -L 128k 2>/dev/null | nice -n 19 ionice -c 3 xz -9 > /dev/null\"\r\n\r\nreal 127m16.113s\r\nuser 34m30.508s\r\nsys 0m23.428s\n\n\n\ncron\u3067\u306e\u5b9f\u884c\n\n$ sudo -u backup crontab -l | grep mysqldump\r\n14 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\n\n\n\n\u74b0\u5883\n\n$ dpkg-query -W mysql-client-5.7 pv xz-utils\r\nmysql-client-5.7 5.7.23-0ubuntu0.16.04.1\r\npv 1.6.0-1\r\nxz-utils 5.1.1alpha+20120614-2ubuntu2\r\n$ lsb_release -d\r\nDescription: Ubuntu 16.04.5 LTS\r\n$ uname -m\r\nx86_64\r\n$ grep -m1 model\\ name /proc/cpuinfo\r\nmodel name : AMD Athlon(tm) II Neo N36L Dual-Core Processor", "date_published": "2018-10-11T06:40:22+09:00", "date_modified": "2018-10-11T06:40:22+09:00", "authors": [ { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" } ], "author": { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" }, "tags": [ "mysql", "mysqldump", "pv", "xz", "Linux", "Ubuntu", "xenial(16.04)" ] }, { "id": "http://matoken.org/blog/?p=1836", "url": "https://matoken.org/blog/2018/02/18/i-changed-mysqls-datadir-and-it-got-scolded-by-apparmor-and-it-not-start/", "title": "mysql\u306edatadir\u3092\u5909\u66f4\u3057\u305f\u3089apparmor\u306b\u6012\u3089\u308c\u3066\u8d77\u52d5\u3057\u306a\u304f\u306a\u3063\u305f", "content_html": "

\u30c7\u30a3\u30b9\u30af\u306e\u90fd\u5408\u3067mysql\u306e\u30c7\u30fc\u30bf\u306e\u7f6e\u304d\u5834\u6240\u3092\u5909\u66f4\u3057\u307e\u3057\u305f\uff0e
\nmysqld\u3092\u505c\u6b62\u3057\u3066\uff0c\u30c7\u30fc\u30bf\u3092\u79fb\u52d5\u3057\u3066\uff0c\u30b7\u30f3\u30dc\u30ea\u30c3\u30af\u30ea\u30f3\u30af\u3082\u4e00\u5fdc\u8cbc\u3063\u3066\u304a\u304f\uff0e
\n/etc/mysql/mysql.conf.d/mysqld.cnf\u3067datadir\u3092\u5909\u66f4\uff0e

\n
\n
[mysqld]\r\ndatadir         = /export/data/var/lib/mysql\r\n
\n
\n

\u3053\u306e\u72b6\u614b\u3067mysql\u3092\u8d77\u52d5\u3059\u308b\u3068\u3053\u3093\u306a\u611f\u3058\u306e\u30a8\u30e9\u30fc\u3067\u8d77\u52d5\u3057\u306a\u304f\u306a\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\uff0e

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

\u8a72\u5f53\u30d5\u30a1\u30a4\u30eb\u306f\u4e00\u898b\u554f\u984c\u7121\u3055\u305d\u3046\u306b\u898b\u3048\u307e\u3059\uff0e

\n
\n
$ sudo ls -la /export/data/var/lib/mysql/ibdata1\r\n-rw-rw---- 1 mysql mysql 102760448  2\u6708 18 05:15 /export/data/var/lib/mysql/ibdata1\r\n$ sudo -u mysql dd if=/export/data/var/lib/mysql/ibdata1 bs=10 count=1|od -xc\r\n1+0 \u30ec\u30b3\u30fc\u30c9\u5165\u529b\r\n1+0 \u30ec\u30b3\u30fc\u30c9\u51fa\u529b\r\n10 bytes copied, 9.8366e-05 s, 102 kB/s\r\n0000000    2214    405b    0000    0000    0000\r\n        024   "   [   @  \\0  \\0  \\0  \\0  \\0  \\0\r\n0000012\r\n
\n
\n

\u4f55\u3067\u3060?\u3068\u601d\u3063\u305f\u3089kernel log\u306b\u3053\u3093\u306a\u30ed\u30b0\u304c\uff0eapparmor\u3067\u5f15\u3063\u304b\u304b\u3063\u3066\u3044\u308b\u3088\u3046\u3067\u3059\uff0e

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

/etc/apparmor.d/usr.sbin.mysqld\u3067\u30d1\u30b9\u3092\u5909\u66f4\u3057\u307e\u3059\uff0e

\n
\n
diff --git a/apparmor.d/usr.sbin.mysqld b/apparmor.d/usr.sbin.mysqld\r\nindex 2619e7d..adb8259 100644\r\n--- a/apparmor.d/usr.sbin.mysqld   \r\n+++ b/apparmor.d/usr.sbin.mysqld\r\n@@ -46,16 +46,16 @@\r\n   /usr/share/mysql/** r,  \r\n\r\n # Allow data dir access   \r\n-  /var/lib/mysql/ r,\r\n-  /var/lib/mysql/** rwk,  \r\n+  /export/data/var/lib/mysql/ r,  \r\n+  /export/data/var/lib/mysql/** rwk,\r\n\r\n # Allow data files dir access\r\n-  /var/lib/mysql-files/ r,\r\n-  /var/lib/mysql-files/** rwk,\r\n+  /export/data/var/lib/mysql-files/ r,\r\n+  /export/data/var/lib/mysql-files/** rwk,\r\n\r\n # Allow keyring dir access\r\n-  /var/lib/mysql-keyring/ r,\r\n-  /var/lib/mysql-keyring/** rwk, \r\n+  /export/data/var/lib/mysql-keyring/ r,\r\n+  /export/data/var/lib/mysql-keyring/** rwk,\r\n\r\n # Allow log file access \r\n   /var/log/mysql.err rw,\r\n
\n
\n

\u3053\u306e\u72b6\u614b\u3067apparmor\u3092\u518d\u8d77\u52d5\u3057\u3066\u8a2d\u5b9a\u3092\u53cd\u6620\u3057\u3066\u304b\u3089mysql\u3092\u8d77\u52d5\u3067OK\u3067\u3057\u305f\uff0e

\n
\n
$ sudo service apparmor restart\r\n$ sudo service mysql start\r\n
\n
\n

\u3053\u306e\u5f8ciostat -x\u3092\u773a\u3081\u3066\u5927\u4e08\u592b\u305d\u3046\u304b\u306a\u30fc\u3063\u3066\u601d\u3063\u305f\u306e\u3067\u3059\u304c\u30c7\u30a3\u30b9\u30af\u30a2\u30af\u30bb\u30b9\u97f3\u304c\u5927\u304d\u304f\u306a\u3063\u305f\u306e\u3067\u307e\u305f\u5225\u306e\u5834\u6240\u306b\u79fb\u52d5\u3059\u308b\u304b\u3082\u2026\u2026\uff0e

\n

\u74b0\u5883

\n
\n
$ dpkg-query -W mysql-server\r\nmysql-server    5.7.21-0ubuntu0.16.04.1\r\n$ lsb_release -a\r\nDistributor ID: Ubuntu\r\nDescription:    Ubuntu 16.04.3 LTS\r\nRelease:        16.04\r\nCodename:       xenial\r\n$ uname -m\r\nx86_64\r\n
\n
\n

\n", "content_text": "\u30c7\u30a3\u30b9\u30af\u306e\u90fd\u5408\u3067mysql\u306e\u30c7\u30fc\u30bf\u306e\u7f6e\u304d\u5834\u6240\u3092\u5909\u66f4\u3057\u307e\u3057\u305f\uff0e\nmysqld\u3092\u505c\u6b62\u3057\u3066\uff0c\u30c7\u30fc\u30bf\u3092\u79fb\u52d5\u3057\u3066\uff0c\u30b7\u30f3\u30dc\u30ea\u30c3\u30af\u30ea\u30f3\u30af\u3082\u4e00\u5fdc\u8cbc\u3063\u3066\u304a\u304f\uff0e\n/etc/mysql/mysql.conf.d/mysqld.cnf\u3067datadir\u3092\u5909\u66f4\uff0e\n\n[mysqld]\r\ndatadir = /export/data/var/lib/mysql\r\n\n\n\u3053\u306e\u72b6\u614b\u3067mysql\u3092\u8d77\u52d5\u3059\u308b\u3068\u3053\u3093\u306a\u611f\u3058\u306e\u30a8\u30e9\u30fc\u3067\u8d77\u52d5\u3057\u306a\u304f\u306a\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\uff0e\n\n2018-02-17T16:12:54.184655Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable\r\n2018-02-17T16:12:54.184718Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable\r\n2018-02-17T16:12:54.184734Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error\r\n2018-02-17T16:12:54.785643Z 0 [ERROR] Plugin 'InnoDB' init function returned error.\r\n2018-02-17T16:12:54.786151Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.\r\n2018-02-17T16:12:54.786272Z 0 [ERROR] Failed to initialize builtin plugins.\r\n2018-02-17T16:12:54.786415Z 0 [ERROR] Aborting\r\n\n\n\u8a72\u5f53\u30d5\u30a1\u30a4\u30eb\u306f\u4e00\u898b\u554f\u984c\u7121\u3055\u305d\u3046\u306b\u898b\u3048\u307e\u3059\uff0e\n\n$ sudo ls -la /export/data/var/lib/mysql/ibdata1\r\n-rw-rw---- 1 mysql mysql 102760448 2\u6708 18 05:15 /export/data/var/lib/mysql/ibdata1\r\n$ sudo -u mysql dd if=/export/data/var/lib/mysql/ibdata1 bs=10 count=1|od -xc\r\n1+0 \u30ec\u30b3\u30fc\u30c9\u5165\u529b\r\n1+0 \u30ec\u30b3\u30fc\u30c9\u51fa\u529b\r\n10 bytes copied, 9.8366e-05 s, 102 kB/s\r\n0000000 2214 405b 0000 0000 0000\r\n 024 " [ @ \\0 \\0 \\0 \\0 \\0 \\0\r\n0000012\r\n\n\n\u4f55\u3067\u3060?\u3068\u601d\u3063\u305f\u3089kernel log\u306b\u3053\u3093\u306a\u30ed\u30b0\u304c\uff0eapparmor\u3067\u5f15\u3063\u304b\u304b\u3063\u3066\u3044\u308b\u3088\u3046\u3067\u3059\uff0e\n\nFeb 18 00:35:26 micro kernel: [ 3569.631324] audit: type=1400 audit(1518881726.300:24): apparmor="DENIED" operation="open" prof\r\nile="/usr/sbin/mysqld" name="/proc/18795/status" pid=18795 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=114 ouid=114\r\n\n\n/etc/apparmor.d/usr.sbin.mysqld\u3067\u30d1\u30b9\u3092\u5909\u66f4\u3057\u307e\u3059\uff0e\n\ndiff --git a/apparmor.d/usr.sbin.mysqld b/apparmor.d/usr.sbin.mysqld\r\nindex 2619e7d..adb8259 100644\r\n--- a/apparmor.d/usr.sbin.mysqld \r\n+++ b/apparmor.d/usr.sbin.mysqld\r\n@@ -46,16 +46,16 @@\r\n /usr/share/mysql/** r, \r\n\r\n # Allow data dir access \r\n- /var/lib/mysql/ r,\r\n- /var/lib/mysql/** rwk, \r\n+ /export/data/var/lib/mysql/ r, \r\n+ /export/data/var/lib/mysql/** rwk,\r\n\r\n # Allow data files dir access\r\n- /var/lib/mysql-files/ r,\r\n- /var/lib/mysql-files/** rwk,\r\n+ /export/data/var/lib/mysql-files/ r,\r\n+ /export/data/var/lib/mysql-files/** rwk,\r\n\r\n # Allow keyring dir access\r\n- /var/lib/mysql-keyring/ r,\r\n- /var/lib/mysql-keyring/** rwk, \r\n+ /export/data/var/lib/mysql-keyring/ r,\r\n+ /export/data/var/lib/mysql-keyring/** rwk,\r\n\r\n # Allow log file access \r\n /var/log/mysql.err rw,\r\n\n\n\u3053\u306e\u72b6\u614b\u3067apparmor\u3092\u518d\u8d77\u52d5\u3057\u3066\u8a2d\u5b9a\u3092\u53cd\u6620\u3057\u3066\u304b\u3089mysql\u3092\u8d77\u52d5\u3067OK\u3067\u3057\u305f\uff0e\n\n$ sudo service apparmor restart\r\n$ sudo service mysql start\r\n\n\n\u3053\u306e\u5f8ciostat -x\u3092\u773a\u3081\u3066\u5927\u4e08\u592b\u305d\u3046\u304b\u306a\u30fc\u3063\u3066\u601d\u3063\u305f\u306e\u3067\u3059\u304c\u30c7\u30a3\u30b9\u30af\u30a2\u30af\u30bb\u30b9\u97f3\u304c\u5927\u304d\u304f\u306a\u3063\u305f\u306e\u3067\u307e\u305f\u5225\u306e\u5834\u6240\u306b\u79fb\u52d5\u3059\u308b\u304b\u3082\u2026\u2026\uff0e\n\u74b0\u5883\n\n$ dpkg-query -W mysql-server\r\nmysql-server 5.7.21-0ubuntu0.16.04.1\r\n$ lsb_release -a\r\nDistributor ID: Ubuntu\r\nDescription: Ubuntu 16.04.3 LTS\r\nRelease: 16.04\r\nCodename: xenial\r\n$ uname -m\r\nx86_64", "date_published": "2018-02-18T18:52:17+09:00", "date_modified": "2018-02-18T18:52:17+09:00", "authors": [ { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" } ], "author": { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" }, "tags": [ "apparmor", "mysql", "Linux", "Ubuntu", "xenial(16.04)" ] }, { "id": "http://matoken.org/blog/?p=810", "url": "https://matoken.org/blog/2015/06/11/wordpress-spam-ip-deny-list/", "title": "WordPress \u3078\u306espam \u6295\u7a3f\u3092\u3057\u305f\u3053\u3068\u306e\u3042\u308bIP \u3092\u62d2\u5426\u3059\u308b\u3088\u3046\u306b\u3057\u305f", "content_html": "
\n

\u6700\u8fd1WordPress \u3078\u306e\u30b3\u30e1\u30f3\u30c8\u3068\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u30b9\u30d1\u30e0\u304c\u9177\u304f\u306a\u3063\u3066\u304d\u307e\u3057\u305f\u3002URL \u304c\u542b\u307e\u308c\u3066\u3044\u308b\u7269\u306f\u627f\u8a8d\u304c\u5fc5\u8981\u306a\u3088\u3046\u306b\u3057\u3066\u3044\u308b\u306e\u3067\u3059\u304c\u9762\u5012\u3067\u3059\u3002\u3053\u306e\u3068\u304d\u306b\u30b9\u30d1\u30e0\u306f\u30b9\u30d1\u30e0\u3060\u3068\u624b\u52d5\u3067\u632f\u308a\u5206\u3051\u3092\u3057\u3066\u3044\u308b\u306e\u3067\u632f\u308a\u5206\u3051\u305f\u3082\u306e\u304b\u3089IP \u3092\u629c\u304d\u51fa\u3057\u3066\u30a2\u30af\u30bb\u30b9\u5236\u9650\u3092\u639b\u3051\u308b\u3068\u307e\u3057\u306b\u306a\u3089\u306a\u3044\u304b\u3068\u8a2d\u5b9a\u3057\u3066\u307f\u307e\u3057\u305f\u3002

\n

\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304b\u3089\u8a72\u5f53IP \u3092\u629c\u304d\u51fa\u3059

\n

MySQL \u304b\u3089\u30b9\u30d1\u30e0\u3092\u6307\u5b9a\u3057\u305fIP \u306e\u4e00\u89a7\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u3057\u3066\u53d6\u5f97\u51fa\u6765\u305d\u3046\u3067\u3059\u3002

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

\u81ea\u52d5\u5316\u3057\u305f\u3044\u306e\u3067\u3001MySQL \u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u70ba\u306eMySQL \u306e\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\u3068\u3001IP \u3092\u629c\u304d\u51fa\u3059sql\u30d5\u30a1\u30a4\u30eb\u3092\u7528\u610f\u3057\u307e\u3059\u3002

\n\n

\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u3057\u3066IP \u306e\u4e00\u89a7\u304c\u53d6\u5f97\u51fa\u6765\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002

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

apache httpd \u306e .htaccess \u3067\u6307\u5b9aIP\u30a2\u30c9\u30ec\u30b9\u304b\u3089\u306e WordPress \u306e\u30b3\u30e1\u30f3\u30c8\u6295\u7a3f\u3068\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u3092\u5236\u9650

\n

Debian \u306e\u30d1\u30c3\u30b1\u30fc\u30b8\u3067 WordPress \u3092\u5c0e\u5165\u3057\u3066\u3044\u308b\u306e\u3067\u5c0e\u5165\u30d1\u30b9\u306f /usr/share/wordpress/ \u3067\u3059\u3002
\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\u306f /etc/wordpress \u4ee5\u4e0b\u3067\u3059\u3002
/usr/share/wordpress/.htaccess \u306f /etc/wordpress/htaccess \u306e\u30b7\u30f3\u30dc\u30ea\u30c3\u30af\u30ea\u30f3\u30af\u306b\u306a\u3063\u3066\u3044\u307e\u3059\u3002

\n

\u30a2\u30af\u30bb\u30b9\u5236\u9650\u3092\u3059\u308b\u306b\u306f .htaccess \u306b deny from ip address \u306a\u611f\u3058\u3067\u884c\u3051\u307e\u3059\u3002
\u3055\u3063\u304d\u306eIP\u306e\u4e00\u89a7\u53d6\u5f97\u6642\u306b\u4e26\u3079\u66ff\u3048\u3066\u540c\u4e00IP\u3092\u307e\u3068\u3081\u3066\u982d\u306bdeny from \u3092\u4ed8\u3051\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002

\n
$ /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 /\"\r\n
\n

\u3042\u307e\u308a\u7121\u3044\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u6b63\u3057\u3044IP \u30a2\u30c9\u30ec\u30b9\u3092\u767b\u9332\u3057\u3066\u3057\u307e\u3063\u3066\u3082\u672c\u6587\u304c\u8aad\u3081\u308b\u3088\u3046\u306b\u30b3\u30e1\u30f3\u30c8\u3084\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u3060\u3051\u5236\u9650\u3057\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002\u30b3\u30e1\u30f3\u30c8\u3001\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u306f\u6b21\u306ewp-comments-post.php / wp-trackback.php \u3092\u5229\u7528\u3059\u308b\u3088\u3046\u306a\u306e\u3067\u3053\u306e\u30d5\u30a1\u30a4\u30eb\u3078\u306e\u30a2\u30af\u30bb\u30b9\u3092\u5236\u9650\u3057\u307e\u3059\u3002
.htaccess \u306e Files \u30c7\u30a3\u30ec\u30af\u30c6\u30a3\u30d6\u3092\u5229\u7528\u3057\u3066\u4ee5\u4e0b\u3088\u3046\u306a\u611f\u3058\u3067\u3044\u3051\u305d\u3046\u3067\u3059\u3002

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

\u52d5\u4f5c\u78ba\u8a8d\u306f\u81ea\u5206\u306eIP\u30a2\u30c9\u30ec\u30b9\u3092\u4e00\u6642\u7684\u306b\u767b\u9332\u3057\u3066\u884c\u3044\u307e\u3057\u305f\u3002blog\u672c\u6587\u306b\u306f\u30a2\u30af\u30bb\u30b9\u51fa\u6765\u3066\u30b3\u30e1\u30f3\u30c8URL \u306f\u62d2\u5426\u3055\u308c\u3066\u3044\u307e\u3059\u3002

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

cron \u3092\u4f7f\u3063\u3066\u81ea\u52d5\u7684\u306b\u66f4\u65b0\u3059\u308b\u3088\u3046\u306b\u3059\u308b

\n

IP list \u306f\u5916\u90e8\u30d5\u30a1\u30a4\u30eb\u306b\u3057\u3066\u8aad\u307f\u8fbc\u3080\u3088\u3046\u306b\u3059\u308b\u3068\u4fbf\u5229\u305d\u3046\u3067\u3059\u304c\u3001Include \u30c7\u30a3\u30ec\u30af\u30c6\u30a3\u30d6\u306f .htaccess \u304b\u3089\u306f\u5229\u7528\u51fa\u6765\u7121\u3044\u3088\u3046\u306a\u306e\u3067\u5206\u5272\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u3063\u3066\u9023\u7d50\u3059\u308b\u3053\u3068\u306b\u3057\u307e\u3059\u3002

\n\n

htaccess_base / htaccess_spamhead / htaccess_spamtail \u3092\u7528\u610f\u3057\u307e\u3059\u3002

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

htaccess_spamiplist / htaccess \u306fcron \u30671\u6642\u9593\u6bce\u306b\u4f5c\u6210\u3059\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002

\n
$ sudo -u www-data crontab -e\r\n$ sudo -u www-data crontab -l\r\n14 * * * *      /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\r\n
\n

\u3053\u308c\u30671\u6642\u9593\u6bce\u306bWordPress \u4e0a\u3067spam \u3068\u5224\u5b9a\u3057\u305f\u30b3\u30e1\u30f3\u30c8/\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u306e\u9001\u4fe1\u5143IP \u304b\u3089\u30b3\u30e1\u30f3\u30c8/\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u3092\u62d2\u5426\u3059\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002
\u3046\u307e\u304f\u3044\u304f\u3068\u3044\u3044\u306e\u3067\u3059\u304c\u2026\u2026\u3002

\n

\u8ffd\u8a18)
\u8a2d\u5b9a\u3057\u3066\u6570\u65e5\u7d4c\u3061\u307e\u3057\u305f\u304c\uff0cspam\u6fc0\u6e1b\u3057\u307e\u3057\u305f!

\n
\n", "content_text": "\u6700\u8fd1WordPress \u3078\u306e\u30b3\u30e1\u30f3\u30c8\u3068\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u30b9\u30d1\u30e0\u304c\u9177\u304f\u306a\u3063\u3066\u304d\u307e\u3057\u305f\u3002URL \u304c\u542b\u307e\u308c\u3066\u3044\u308b\u7269\u306f\u627f\u8a8d\u304c\u5fc5\u8981\u306a\u3088\u3046\u306b\u3057\u3066\u3044\u308b\u306e\u3067\u3059\u304c\u9762\u5012\u3067\u3059\u3002\u3053\u306e\u3068\u304d\u306b\u30b9\u30d1\u30e0\u306f\u30b9\u30d1\u30e0\u3060\u3068\u624b\u52d5\u3067\u632f\u308a\u5206\u3051\u3092\u3057\u3066\u3044\u308b\u306e\u3067\u632f\u308a\u5206\u3051\u305f\u3082\u306e\u304b\u3089IP \u3092\u629c\u304d\u51fa\u3057\u3066\u30a2\u30af\u30bb\u30b9\u5236\u9650\u3092\u639b\u3051\u308b\u3068\u307e\u3057\u306b\u306a\u3089\u306a\u3044\u304b\u3068\u8a2d\u5b9a\u3057\u3066\u307f\u307e\u3057\u305f\u3002\n\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304b\u3089\u8a72\u5f53IP \u3092\u629c\u304d\u51fa\u3059\nMySQL \u304b\u3089\u30b9\u30d1\u30e0\u3092\u6307\u5b9a\u3057\u305fIP \u306e\u4e00\u89a7\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u3057\u3066\u53d6\u5f97\u51fa\u6765\u305d\u3046\u3067\u3059\u3002\n$ cat /etc/wordpress/spamcommentip.sql\r\nSELECT comment_author_IP FROM wordpress.wp_comments WHERE comment_approved='spam'\r\n$ /usr/bin/mysql -umy -p < /etc/wordpress/spamcommentip.sql | /usr/bin/sort -n | /usr/bin/uniq -c| sort -n|cut -c-7|uniq -c\r\n 81 1\r\n 31 2\r\n 26 3\r\n 12 4\r\n 8 5\r\n 9 6\r\n 4 7\r\n 9 8\r\n 1 9\r\n 1 10\r\n 3 11\r\n 2 12\r\n 3 13\r\n 1 14\r\n 1 15\r\n 1 18\r\n 4 20\r\n 1 22\r\n 1 23\r\n 1 24\r\n 1 26\r\n 1 43\r\n 1 82\r\n 1 119\r\n 1 146\r\n 1 394\r\n\n\u81ea\u52d5\u5316\u3057\u305f\u3044\u306e\u3067\u3001MySQL \u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u70ba\u306eMySQL \u306e\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\u3068\u3001IP \u3092\u629c\u304d\u51fa\u3059sql\u30d5\u30a1\u30a4\u30eb\u3092\u7528\u610f\u3057\u307e\u3059\u3002\n\n\nMySQL \u30a2\u30af\u30bb\u30b9\u306e\u70ba\u306e\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\n$ cd /etc/wordpress\r\n$ umask 077\r\n$ sudo touch .my.conf\r\n$ sudo chown www-data.root .my.conf\r\n$ vi .my.conf\r\ncat .my.conf \r\nuser=wp\r\npassword=XXXXXXXXXXXXXX\r\ndatabase=wordpress\r\n\n\n\nIP \u3092\u629c\u304d\u51fa\u3059\u70ba\u306esql\u30d5\u30a1\u30a4\u30eb\n$ sudo vi /etc/wordpress/spamcommentip.sql\r\n$ cat $ cat /etc/wordpress/spamcommentip.sql\r\nSELECT comment_author_IP FROM wordpress.wp_comments WHERE comment_approved='spam'\r\n\n\n\n\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u3057\u3066IP \u306e\u4e00\u89a7\u304c\u53d6\u5f97\u51fa\u6765\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002\n$ /usr/bin/mysql --defaults-file=/etc/wordpress/.my.conf < /etc/wordpress/spamcommentip.sql\r\n\napache httpd \u306e .htaccess \u3067\u6307\u5b9aIP\u30a2\u30c9\u30ec\u30b9\u304b\u3089\u306e WordPress \u306e\u30b3\u30e1\u30f3\u30c8\u6295\u7a3f\u3068\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u3092\u5236\u9650\nDebian \u306e\u30d1\u30c3\u30b1\u30fc\u30b8\u3067 WordPress \u3092\u5c0e\u5165\u3057\u3066\u3044\u308b\u306e\u3067\u5c0e\u5165\u30d1\u30b9\u306f /usr/share/wordpress/ \u3067\u3059\u3002\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\u306f /etc/wordpress \u4ee5\u4e0b\u3067\u3059\u3002/usr/share/wordpress/.htaccess \u306f /etc/wordpress/htaccess \u306e\u30b7\u30f3\u30dc\u30ea\u30c3\u30af\u30ea\u30f3\u30af\u306b\u306a\u3063\u3066\u3044\u307e\u3059\u3002\n\u30a2\u30af\u30bb\u30b9\u5236\u9650\u3092\u3059\u308b\u306b\u306f .htaccess \u306b deny from ip address \u306a\u611f\u3058\u3067\u884c\u3051\u307e\u3059\u3002\u3055\u3063\u304d\u306eIP\u306e\u4e00\u89a7\u53d6\u5f97\u6642\u306b\u4e26\u3079\u66ff\u3048\u3066\u540c\u4e00IP\u3092\u307e\u3068\u3081\u3066\u982d\u306bdeny from \u3092\u4ed8\u3051\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002\n$ /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 /\"\r\n\n\u3042\u307e\u308a\u7121\u3044\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u6b63\u3057\u3044IP \u30a2\u30c9\u30ec\u30b9\u3092\u767b\u9332\u3057\u3066\u3057\u307e\u3063\u3066\u3082\u672c\u6587\u304c\u8aad\u3081\u308b\u3088\u3046\u306b\u30b3\u30e1\u30f3\u30c8\u3084\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u3060\u3051\u5236\u9650\u3057\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002\u30b3\u30e1\u30f3\u30c8\u3001\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u306f\u6b21\u306ewp-comments-post.php / wp-trackback.php \u3092\u5229\u7528\u3059\u308b\u3088\u3046\u306a\u306e\u3067\u3053\u306e\u30d5\u30a1\u30a4\u30eb\u3078\u306e\u30a2\u30af\u30bb\u30b9\u3092\u5236\u9650\u3057\u307e\u3059\u3002.htaccess \u306e Files \u30c7\u30a3\u30ec\u30af\u30c6\u30a3\u30d6\u3092\u5229\u7528\u3057\u3066\u4ee5\u4e0b\u3088\u3046\u306a\u611f\u3058\u3067\u3044\u3051\u305d\u3046\u3067\u3059\u3002\n<Files ~ wp-comments-post.php|wp-trackback.php>\r\n deny from ip address\r\n</Files>\r\n\n\u52d5\u4f5c\u78ba\u8a8d\u306f\u81ea\u5206\u306eIP\u30a2\u30c9\u30ec\u30b9\u3092\u4e00\u6642\u7684\u306b\u767b\u9332\u3057\u3066\u884c\u3044\u307e\u3057\u305f\u3002blog\u672c\u6587\u306b\u306f\u30a2\u30af\u30bb\u30b9\u51fa\u6765\u3066\u30b3\u30e1\u30f3\u30c8URL \u306f\u62d2\u5426\u3055\u308c\u3066\u3044\u307e\u3059\u3002\n% w3m -dump_head https://matoken.org/blog/blog/2015/06/09/facebook-pgp/|head -1\r\nHTTP/1.0 200 OK\r\n% w3m -dump_head https://matoken.org/blog/wp-comments-post.php|head -1\r\nHTTP/1.1 403 Forbidden\r\n\ncron \u3092\u4f7f\u3063\u3066\u81ea\u52d5\u7684\u306b\u66f4\u65b0\u3059\u308b\u3088\u3046\u306b\u3059\u308b\nIP list \u306f\u5916\u90e8\u30d5\u30a1\u30a4\u30eb\u306b\u3057\u3066\u8aad\u307f\u8fbc\u3080\u3088\u3046\u306b\u3059\u308b\u3068\u4fbf\u5229\u305d\u3046\u3067\u3059\u304c\u3001Include \u30c7\u30a3\u30ec\u30af\u30c6\u30a3\u30d6\u306f .htaccess \u304b\u3089\u306f\u5229\u7528\u51fa\u6765\u7121\u3044\u3088\u3046\u306a\u306e\u3067\u5206\u5272\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u3063\u3066\u9023\u7d50\u3059\u308b\u3053\u3068\u306b\u3057\u307e\u3059\u3002\n\n/etc/wordpress/htaccess_base\n\n\u5143\u306ehtaccess\n\n\n/etc/wordpress/htaccess_spamhead\n\nFiles \u30c7\u30a3\u30ec\u30af\u30c6\u30a3\u30d6\u306e\u5148\u982d\n\n\n/etc/wordpress/htaccess_spamiplist\n\n\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304b\u3089\u629c\u304d\u51fa\u3057\u3066\u4f5c\u6210\u3057\u305f\u62d2\u5426IP\u30ea\u30b9\u30c8\n\n\n/etc/wordpress/htaccess_spamtail\n\nFiles \u30c7\u30a3\u30ec\u30af\u30c6\u30a3\u30d6\u306e\u672b\u5c3e\n\n\n/etc/wordpress/htaccess\n\n\u6700\u7d42\u7684\u306b\u7d50\u5408\u3055\u308c\u3066.htaccess \u306e\u30ea\u30f3\u30af\u5143\u306b\u306a\u308b\u30d5\u30a1\u30a4\u30eb\n\n\n\nhtaccess_base / htaccess_spamhead / htaccess_spamtail \u3092\u7528\u610f\u3057\u307e\u3059\u3002\n$ sudo cp -p /etc/wordpress/htaccess /etc/wordpress/htaccess_base\r\n$ sudo sh -c \"echo '<Files ~ wp-trackback.php|wp-comments-post.php>' > /etc/wordpress/htaccess_spamhead\"\r\n$ sudo sh -c \"echo '</Files>' > /etc/wordpress/htaccess_spamtail\"\r\n\nhtaccess_spamiplist / htaccess \u306fcron \u30671\u6642\u9593\u6bce\u306b\u4f5c\u6210\u3059\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002\n$ sudo -u www-data crontab -e\r\n$ sudo -u www-data crontab -l\r\n14 * * * * /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\r\n\n\u3053\u308c\u30671\u6642\u9593\u6bce\u306bWordPress \u4e0a\u3067spam \u3068\u5224\u5b9a\u3057\u305f\u30b3\u30e1\u30f3\u30c8/\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u306e\u9001\u4fe1\u5143IP \u304b\u3089\u30b3\u30e1\u30f3\u30c8/\u30c8\u30e9\u30c3\u30af\u30d0\u30c3\u30af\u3092\u62d2\u5426\u3059\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u3046\u307e\u304f\u3044\u304f\u3068\u3044\u3044\u306e\u3067\u3059\u304c\u2026\u2026\u3002\n\u8ffd\u8a18)\u8a2d\u5b9a\u3057\u3066\u6570\u65e5\u7d4c\u3061\u307e\u3057\u305f\u304c\uff0cspam\u6fc0\u6e1b\u3057\u307e\u3057\u305f!", "date_published": "2015-06-11T09:01:25+09:00", "date_modified": "2015-06-15T11:23:42+09:00", "authors": [ { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" } ], "author": { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" }, "tags": [ "Debian", "Linux", "mysql", "WordPress" ] }, { "id": "http://matoken.org/blog/?p=622", "url": "https://matoken.org/blog/2014/09/12/mysqldump_warning_fix/", "title": "mysqldump \u306e\u8b66\u544a\u3092\u4fee\u6b63(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.)", "content_html": "

mysql \u306e\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u6642\u306b\u8b66\u544a\u304c\u51fa\u3066\u3044\u308b\u306e\u306b\u6c17\u3065\u304d\u307e\u3057\u305f\uff0e

\n

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.

\n

\u30e1\u30c3\u30bb\u30fc\u30b8\u3067\u691c\u7d22\u3059\u308b\u3068\u3061\u3087\u3063\u3068\u524d\u306e\u30ea\u30ea\u30fc\u30b9\u30ce\u30fc\u30c8\u306b\u305d\u308c\u3089\u3057\u3044\u3082\u306e\u304c\uff0e

\n
\n

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)

\n
\n

\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u7701\u7565\u3057\u305f\u308a\u3059\u308b\u3068\u8b66\u544a\u3092\u51fa\u3059\u3088\u3046\u306b\u306a\u3063\u305f\u3088\uff0e5.7\u304b\u3089\u4f7f\u3048\u306a\u304f\u306a\u308b\u3088\uff0e\u3068\u3044\u3046\u3053\u3068\u306e\u3088\u3046\u3067\u3059\uff0e

\n

\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306fssh \u7d4c\u7531\u306e\u30ea\u30e2\u30fc\u30c8\u3067\u53d6\u5f97\u3057\u3066\u3044\u3066\uff0c ~/.ssh/authorized_keys \u306b\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u611f\u3058\u3067\u66f8\u3044\u3066\u3044\u307e\u3057\u305f\uff0e

\n

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...

\n

–event \u90e8\u5206\u306f\u672c\u6765\u306f –events \u306e\u69d8\u306a\u306e\u3067\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u4fee\u6b63\u3057\u3066\u89e3\u6c7a\u3057\u307e\u3057\u305f\uff0e

\n

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...

\n

\uff03\u3061\u306a\u307f\u306b\u53d7\u3051\u5074\u306f\u3053\u3093\u306a\u611f\u3058 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`

\n
\n", "content_text": "mysql \u306e\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u6642\u306b\u8b66\u544a\u304c\u51fa\u3066\u3044\u308b\u306e\u306b\u6c17\u3065\u304d\u307e\u3057\u305f\uff0e\nWarning: Using unique option prefix event instead of events is deprecated and will be removed in a future release. Please use the full name instead.\n\u30e1\u30c3\u30bb\u30fc\u30b8\u3067\u691c\u7d22\u3059\u308b\u3068\u3061\u3087\u3063\u3068\u524d\u306e\u30ea\u30ea\u30fc\u30b9\u30ce\u30fc\u30c8\u306b\u305d\u308c\u3089\u3057\u3044\u3082\u306e\u304c\uff0e\n\nPreviously, 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)\n\n\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u7701\u7565\u3057\u305f\u308a\u3059\u308b\u3068\u8b66\u544a\u3092\u51fa\u3059\u3088\u3046\u306b\u306a\u3063\u305f\u3088\uff0e5.7\u304b\u3089\u4f7f\u3048\u306a\u304f\u306a\u308b\u3088\uff0e\u3068\u3044\u3046\u3053\u3068\u306e\u3088\u3046\u3067\u3059\uff0e\n\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306fssh \u7d4c\u7531\u306e\u30ea\u30e2\u30fc\u30c8\u3067\u53d6\u5f97\u3057\u3066\u3044\u3066\uff0c ~/.ssh/authorized_keys \u306b\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u611f\u3058\u3067\u66f8\u3044\u3066\u3044\u307e\u3057\u305f\uff0e\ncommand=\"/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...\n–event \u90e8\u5206\u306f\u672c\u6765\u306f –events \u306e\u69d8\u306a\u306e\u3067\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u4fee\u6b63\u3057\u3066\u89e3\u6c7a\u3057\u307e\u3057\u305f\uff0e\ncommand=\"/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...\n\uff03\u3061\u306a\u307f\u306b\u53d7\u3051\u5074\u306f\u3053\u3093\u306a\u611f\u3058 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`", "date_published": "2014-09-12T13:22:55+09:00", "date_modified": "2014-09-12T20:20:40+09:00", "authors": [ { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" } ], "author": { "name": "matoken", "url": "https://matoken.org/blog/author/matoken/", "avatar": "https://secure.gravatar.com/avatar/38f5f3b575c5eb45cda6aa659bca119ac7a5e16b46565e869d0030e3bd66981d?s=512&d=mm&r=g" }, "tags": [ "Debian", "Linux", "mysql", "Wheezy" ] } ] }