{
    "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/mysqldump/feed/json/ -- and add it your reader.",
    "home_page_url": "https://matoken.org/blog/tag/mysqldump/",
    "feed_url": "https://matoken.org/blog/tag/mysqldump/feed/json/",
    "language": "ja",
    "title": "mysqldump – 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": "<div id=\"__asciidoctor-preview-1__\" class=\"paragraph\">\n<p>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<br />\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</p>\n</div>\n<div id=\"__asciidoctor-preview-2__\" class=\"listingblock\">\n<div class=\"content\">\n<pre>$ 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</pre>\n</div>\n</div>\n<div id=\"__asciidoctor-preview-3__\" class=\"paragraph\">\n<p>pv -L \u3067\u5e2f\u57df\u7d5e\u3063\u3066\u307f\u307e\u3057\u305f\uff0e<br />\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<br />\n\u3057\u3070\u3089\u304f\u3053\u308c\u3067\u8a66\u3057\u3066\u307f\u307e\u3059\uff0e</p>\n</div>\n<div id=\"__asciidoctor-preview-4__\" class=\"listingblock\">\n<div class=\"title\">pv \u3067\u5e2f\u57df\u5236\u9650</div>\n<div class=\"content\">\n<pre>$ 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&gt;/dev/null | nice -n 19 ionice -c 3 xz -9 &gt; /dev/null\"\r\n\r\nreal 127m16.113s\r\nuser 34m30.508s\r\nsys 0m23.428s</pre>\n</div>\n</div>\n<div id=\"__asciidoctor-preview-5__\" class=\"listingblock\">\n<div class=\"title\">cron\u3067\u306e\u5b9f\u884c</div>\n<div class=\"content\">\n<pre>$ sudo -u backup crontab -l | grep mysqldump\r\n14 3 * * *     umask 0266 &amp;&amp; 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&gt;/dev/null | nice -n 19 ionice -c 3 /usr/bin/xz -9 &gt; /mnt/backup/micro/`date +\\%F_\\%T_$$`.sql.xz</pre>\n</div>\n</div>\n<div id=\"__asciidoctor-preview-6__\" class=\"listingblock\">\n<div class=\"title\">\u74b0\u5883</div>\n<div class=\"content\">\n<pre>$ 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</pre>\n</div>\n</div>\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&gt;/dev/null | nice -n 19 ionice -c 3 xz -9 &gt; /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 &amp;&amp; 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&gt;/dev/null | nice -n 19 ionice -c 3 /usr/bin/xz -9 &gt; /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)"
            ]
        }
    ]
}