{
    "version": "https:\/\/jsonfeed.org\/version\/1.1",
    "title": "Discover Docker, K8s and Hashicorp Nomad with Maksym Prokopov: posts tagged mysql",
    "_rss_description": "The blog about containerisation, virtual machines and useful shell snippets and findings",
    "_rss_language": "en",
    "_itunes_email": "",
    "_itunes_categories_xml": "",
    "_itunes_image": "",
    "_itunes_explicit": "",
    "home_page_url": "https:\/\/blog.it-premium.com.ua\/tags\/mysql\/",
    "feed_url": "https:\/\/blog.it-premium.com.ua\/tags\/mysql\/json\/",
    "icon": false,
    "authors": [
        {
            "name": "Maksym Prokopov",
            "url": "https:\/\/blog.it-premium.com.ua\/",
            "avatar": false
        }
    ],
    "items": [
        {
            "id": "182",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/fully-automated-mysql-database-dump-backup-from-remote-host\/",
            "title": "Fully automated docker mysql database backup from remote low-end NAS",
            "content_html": "<p>The main idea is to have fully automated docker database backup from low end D-Link NAS DNS-320.<\/p>\n<p>Solution design is following:<\/p>\n<ol start=\"1\">\n<li>My backup box will copy backup.sh script to the remote coreos-03 host.<\/li>\n<li>Then remote host copies backup.sh script into database container.<\/li>\n<li>Backup box executes docker command “docker exec itservice_db_1 backup.sh” on coreos-03 host, which, in turn, executes mysqlbackup. SQL dump is captured directly from command output and then gzipped.<\/li>\n<li>Rsnapshot saves folder with gzipped SQL dump and rotates old backup folders as necessary.<\/li>\n<\/ol>\n<p>So, we will need only<\/p>\n<ul>\n<li>ssh<\/li>\n<li>tar<\/li>\n<li>rsnapshot<\/li>\n<\/ul>\n<p>Here is my working implementation:<\/p>\n<p>script backup.sh<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">#!\/bin\/bash\n## env vars are already in docker container\n\/usr\/bin\/mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE<\/code><\/pre><p>script backup-coreos-itservice.sh<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">\/ffp\/bin\/scp \/ffp\/home\/root\/backup.sh core@coreos-03:\/home\/core\/itservice\/backup.sh\n\/usr\/sbin\/ssh -C core@coreos-03 &quot;docker cp \/home\/core\/itservice\/backup.sh itservice_db_1:\/usr\/local\/bin\/backup.sh&quot;\n\/usr\/sbin\/ssh -C core@coreos-03 &quot;docker exec itservice_db_1 \/usr\/local\/bin\/backup.sh&quot; &gt; latest.sql\n\/opt\/bin\/tar czf itservice-sql-dump.tar.gz latest.sql --remove-files<\/code><\/pre><p>rsnapshot.conf<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">...\nbackup_script\t\/mnt\/HD\/HD_a2\/ffp\/home\/root\/backup-coreos-itservice.sh\tcoreos-03\/itservice_db_1\n...<\/code><\/pre><p>crontab<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">0 *\/4 * * * rsnapshot hourly\n30 3 * * *  rsnapshot daily\n0  3 * * 1  rsnapshot weekly\n30 2 1 * *  rsnapshot monthly<\/code><\/pre><p>Keep in mind, that you will need to generate ssh keys for your backup box and add it to authorized_keys on coreos-03 host, but this is out of scope this article.<\/p>\n",
            "date_published": "2017-08-17T10:27:09+01:00",
            "date_modified": "2017-08-18T08:28:01+01:00",
            "tags": [
                "backup",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Thu, 17 Aug 2017 10:27:09 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "182",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "174",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/docker-mysql-container-field-doesnt-have-a-default-value\/",
            "title": "Docker MySQL container “Field doesn’t have a default value”",
            "content_html": "<p>issue caused by long lasting MySQL bug <a href=\"http:\/\/stackoverflow.com\/questions\/15438840\/mysql-error-1364-field-doesnt-have-a-default-values\">http:\/\/stackoverflow.com\/questions\/15438840\/mysql-error-1364-field-doesnt-have-a-default-values<\/a><\/p>\n<p>Short path add this command to mysql container<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">command: mysqld --character-set-server=utf8 --collation-server=utf8_general_ci --sql_mode=NO_ENGINE_SUBSTITUTION<\/code><\/pre><p>That will fix issue.<\/p>\n",
            "date_published": "2017-01-23T10:11:48+01:00",
            "date_modified": "2017-01-23T10:11:46+01:00",
            "tags": [
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Mon, 23 Jan 2017 10:11:48 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "174",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "169",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/simple-docker-mysql-backup-restore\/",
            "title": "Simple docker mysql backup\/restore",
            "content_html": "<p><a href=\"https:\/\/gist.github.com\/spalladino\/6d981f7b33f6e0afe6bb\">https:\/\/gist.github.com\/spalladino\/6d981f7b33f6e0afe6bb<\/a><\/p>\n<h2>Backup<\/h2>\n<p>docker exec CONTAINER \/usr\/bin\/mysqldump -u root --password=root DATABASE > backup.sql<\/p>\n<h2>Restore<\/h2>\n<p>cat backup.sql | docker exec -i CONTAINER \/usr\/bin\/mysql -u root --password=root DATABASE<\/p>\n",
            "date_published": "2017-01-13T09:35:03+01:00",
            "date_modified": "2017-01-13T09:35:00+01:00",
            "tags": [
                "backup",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Fri, 13 Jan 2017 09:35:03 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "169",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [],
                "og_images": []
            }
        },
        {
            "id": "163",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/use-mysql-in-docker-with-utf-8-encoding-and-utf8-general-ci\/",
            "title": "Use MySQL in docker with utf-8 encoding and utf8_general_ci",
            "content_html": "<ul>\n<li>Use custom command to launch mysql container with right collation:<\/li>\n<\/ul>\n<pre class=\"e2-text-code\"><code class=\"\">command: mysqld --character-set-server=utf8 --collation-server=utf8_general_ci<\/code><\/pre><ul>\n<li>Alter existing database:<\/li>\n<\/ul>\n<pre class=\"e2-text-code\"><code class=\"\">ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;\nALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;<\/code><\/pre><p>use<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">docker-compose exec db mysql -uuser -ppass database<\/code><\/pre><p>to exec your queries in running container.<\/p>\n",
            "date_published": "2016-11-08T10:51:28+01:00",
            "date_modified": "2016-11-08T10:52:06+01:00",
            "tags": [
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Tue, 08 Nov 2016 10:51:28 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "163",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "162",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/how-to-launch-mysql-in-docker-with-collation-and-charset\/",
            "title": "How to launch MySQL in docker with collation and charset",
            "content_html": "<p>You can use following line in service description in docker-compose.yml for mysql database<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">command: mysqld --character-set-server=utf8 --collation-server=utf8_general_ci<\/code><\/pre>",
            "date_published": "2016-11-04T15:06:39+01:00",
            "date_modified": "2016-11-04T15:06:36+01:00",
            "tags": [
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Fri, 04 Nov 2016 15:06:39 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "162",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "159",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/more-secure-mysqldump-for-docker-container\/",
            "title": "More secure mysqldump for docker container",
            "content_html": "<p>In case you have warning message about insecure user and password usage from command line you can do following:<\/p>\n<ol start=\"1\">\n<li>use mysql_config_editor set <s>login-path=local <\/s>host=localhost <s>user=db_user <\/s>password executed in container via exec. That will create encrypted file.<\/li>\n<li>Specify --login-path=local instead of -u user and -p password in your backup script.<\/li>\n<\/ol>\n<p>I have my backups via rsnapshot. This is something like TimeMachine for Mac with handy directory structure and without duplication.<\/p>\n<p>And my backup script looks like this:<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">\/usr\/bin\/ssh -i \/mnt\/HD\/HD_a2\/ffp\/home\/root\/id_rsa_nexus.key -C core@myipaddress &quot;docker exec itservice_db_1 mysqldump --login-path=local itservice_production&quot; &gt;\n mysqlbackup.sql<\/code><\/pre><p>That will create mysql dump on your remote backup host without passing user and password in more secure way.<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">docker-compose exec db mysql_config_editor set --login_path=local --host=localhost --user=itservice --password<\/code><\/pre><p>will ask password and save credentials in container.<\/p>\n<p>Resources: <a href=\"http:\/\/stackoverflow.com\/questions\/20751352\/suppress-warning-messages-using-mysql-from-within-terminal-but-password-written\">http:\/\/stackoverflow.com\/questions\/20751352\/suppress-warning-messages-using-mysql-from-within-terminal-but-password-written<\/a><\/p>\n",
            "date_published": "2016-10-18T10:58:29+01:00",
            "date_modified": "2016-12-02T10:17:15+01:00",
            "tags": [
                "backup",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Tue, 18 Oct 2016 10:58:29 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "159",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "158",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/how-to-load-mysql-dump-from-local-host\/",
            "title": "How to load mysql dump from local host",
            "content_html": "<p>The main thing is to specify -i key, it allows input redirection from host to container.<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">docker exec -i container mysql -uuser -ppassword database &lt; dump.sql<\/code><\/pre>",
            "date_published": "2016-10-18T10:27:23+01:00",
            "date_modified": "2016-10-18T10:27:21+01:00",
            "tags": [
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Tue, 18 Oct 2016 10:27:23 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "158",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "136",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/snyatie-bekapa-iz-mysql-konteynera-pri-pomoschi-ansible\/",
            "title": "Снятие бекапа из mysql контейнера при помощи Ansible",
            "content_html": "<p>Вот простой рецепт:<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">- name: backup mysql from container\n  gather_facts: no\n  vars:\n    - db_container: container_db_1\n    - image: mysql:5.6\n    - db:\n        name: database_name\n        user: root\n        password: root\n    - dump_file: .\/dump-latest.sql\n  tasks:\n    - name: run backup container\n      shell: &quot;docker run --rm --link {{db_container}}:db --entrypoint \\&quot;\/usr\/bin\/mysqldump\\&quot; {{image}} -hdb -u{{db.user}} -p{{db.password}} {{db.name}}&quot;\n      register: output\n\n    - name: copy output\n      local_action: copy content=&quot;{{ output.stdout }}&quot; dest=&quot;{{dump_file}}&quot;<\/code><\/pre><p>Работает БЕЗ создания файлов на удаленном хосте, помимо, конечно, временного контейнера.<\/p>\n<script type=\"text\/javascript\" src=\"https:\/\/asciinema.org\/a\/680nbb59m9ko2ptrdkvf79123.js\" id=\"asciicast-680nbb59m9ko2ptrdkvf79123\" async><\/script>\n",
            "date_published": "2016-07-21T12:19:21+01:00",
            "date_modified": "2016-07-21T13:14:38+01:00",
            "tags": [
                "ansible",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Thu, 21 Jul 2016 12:19:21 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "136",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "135",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/esche-odin-bystry-skript-dlya-bekapa-mysql-iz-docker\/",
            "title": "Еще один быстрый скрипт для бекапа mysql из docker",
            "content_html": "<p>Мой предыдущий <a href=\"http:\/\/blog.it-premium.com.ua\/all\/prostoy-sposob-sdelat-bekap-mysql-v-docker-konteynere\/\">способ “просто запусти и залинкуй контейнер”<\/a> разбился о проблемы с поддержкой mysql версии 5.7 и выше и то, что docker-compose версии 2 не поддерживает публикацию environment переменных слинкованного контейнера.<\/p>\n<p>Поэтому пришлось разработать свой, крайне простой способ для выгрузки mysql бекапа из mysql контейнера. Поскольку у mysql свой хитры entrypoint, простая замена CMD не работала.<\/p>\n<p>Вот работающий код.<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">docker run \\\n    --rm \\\n    --link container_db_1:db \\\n    --entrypoint &quot;\/usr\/bin\/mysqldump&quot; mysql:5 -hdb -uroot -proot database &gt; .\/backup\/mysql-latest.sql<\/code><\/pre><p>обратите внимание, что > .\/backup\/mysql-latest.sql завернет вывод из контейнера в файл на хосте, а не внутри.<\/p>\n<p>В качестве образа mysql:5 используйте тот образ, который уже используется в вашем проекте (mysql:5.6, например).<\/p>\n<p>Чуть позднее сделаю рецепт для ansible.<\/p>\n",
            "date_published": "2016-07-21T11:59:16+01:00",
            "date_modified": "2016-07-21T12:01:46+01:00",
            "tags": [
                "backup",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Thu, 21 Jul 2016 11:59:16 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "135",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "126",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/istoriya-pro-bag-v-bekape-confirm-mysql-backup\/",
            "title": "История про баг в бекапе confirm\/mysql-backup",
            "content_html": "<p>Прекрасная идея и достойное воплощение <a href=\"https:\/\/blog.confirm.ch\/backup-mysql-mariadb-docker-container\/\">MySQL бекапов прямо из контейнера<\/a>.<\/p>\n<p>Разбилась об изменение в дефолтном MySQL 5.7 TIMESTAMP<br \/>\n<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/timestamp-initialization.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/timestamp-initialization.html<\/a><\/p>\n<p>О чем и <a href=\"https:\/\/github.com\/maxbube\/mydumper\/issues\/6\">отрепортил автору mydumper на github<\/a><\/p>\n",
            "date_published": "2016-07-18T15:55:01+01:00",
            "date_modified": "2016-07-18T15:54:59+01:00",
            "tags": [
                "backup",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Mon, 18 Jul 2016 15:55:01 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "126",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [],
                "og_images": []
            }
        },
        {
            "id": "124",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/prostoy-sposob-sdelat-bekap-mysql-v-docker-konteynere\/",
            "title": "Простой способ сделать бекап MySQL в Docker контейнере",
            "content_html": "<p>существует такой специальный контейнер confirm\/mysql-backup, который создан, что бы делать бекапы<\/p>\n<p>для этого запустите контейнер с маппингом my_local_backup_dir:\/backups и будет создан свежий бекап и положен в вашу my_local_backup_dir.<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">docker run --name demo_backup --link demo_db -v \/var\/demo_backup:\/backup -d confirm\/mysql-backup<\/code><\/pre><p>Для восстановления добавьте ключ окружения MODE=RESTORE<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">docker run --name demo_backup --link demo_db -v \/var\/demo_backup:\/backup -d confirm\/mysql-backup -e MODE=RESTORE<\/code><\/pre><p>Подробнее здесь <a href=\"https:\/\/blog.confirm.ch\/backup-mysql-mariadb-docker-container\/\">https:\/\/blog.confirm.ch\/backup-mysql-mariadb-docker-container\/<\/a><\/p>\n<p>Рецепт для Ansible<\/p>\n<pre class=\"e2-text-code\"><code class=\"\">- name: backup mysql container\n  hosts: coreos\n  tasks:\n    - name: backup mysql\n      docker_container:\n        name: mysql-backup\n        image: confirm\/mysql-backup\n        links:\n          - mprokopovmichellekievua_db_1\n        volumes:\n          - \/home\/core\/sql:\/backup<\/code><\/pre><p>после выполнения контейнера будет создан бекап следующего вида:<br \/>\nкаталог export-20160714-124628<br \/>\nв котором будут отдельно файлы схемы и данных по таблицам:<\/p>\n<p>metadata\t\t\t\t\t michelle_wordpress.wp_postmeta-schema.sql.gz\t\t michelle_wordpress.wp_term_taxonomy-schema.sql.gz  michelle_wordpress.wp_usermeta.sql.gz<br \/>\nmichelle_wordpress.wp_commentmeta-schema.sql.gz  michelle_wordpress.wp_postmeta.sql.gz\t\t\t michelle_wordpress.wp_term_taxonomy.sql.gz\t    michelle_wordpress.wp_users-schema.sql.gz<br \/>\nmichelle_wordpress.wp_comments-schema.sql.gz\t michelle_wordpress.wp_posts-schema.sql.gz\t\t michelle_wordpress.wp_termmeta-schema.sql.gz\t    michelle_wordpress.wp_users.sql.gz<br \/>\nmichelle_wordpress.wp_links-schema.sql.gz\t michelle_wordpress.wp_posts.sql.gz\t\t\t michelle_wordpress.wp_terms-schema.sql.gz<br \/>\nmichelle_wordpress.wp_options-schema.sql.gz\t michelle_wordpress.wp_term_relationships-schema.sql.gz  michelle_wordpress.wp_terms.sql.gz<br \/>\nmichelle_wordpress.wp_options.sql.gz\t\t michelle_wordpress.wp_term_relationships.sql.gz\t michelle_wordpress.wp_usermeta-schema.sql.gz<\/p>\n",
            "date_published": "2016-07-14T13:38:55+01:00",
            "date_modified": "2016-07-14T14:42:56+01:00",
            "tags": [
                "backup",
                "docker",
                "mysql"
            ],
            "_date_published_rfc2822": "Thu, 14 Jul 2016 13:38:55 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "124",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "highlight\/highlight.js",
                    "highlight\/highlight.css"
                ],
                "og_images": []
            }
        },
        {
            "id": "66",
            "url": "https:\/\/blog.it-premium.com.ua\/all\/ustanovka-centos-mysql-varnish-nginx-php-fpm\/",
            "title": "Установка CentOS + MySQL + Varnish + Nginx + PHP-FPM",
            "content_html": "<p><a href=\"https:\/\/stavrovski.net\/blog\/install-and-configure-nginx-mysql-php-fpm-in-centos-6#set-up-mysql\">https:\/\/stavrovski.net\/blog\/install-and-configure-nginx-mysql-php-fpm-in-centos-6#set-up-mysql<\/a><br \/>\n<a href=\"http:\/\/dev.mysql.com\/doc\/mysql-yum-repo-quick-guide\/en\/\">http:\/\/dev.mysql.com\/doc\/mysql-yum-repo-quick-guide\/en\/<\/a><br \/>\n<a href=\"http:\/\/www.sitepoint.com\/set-automatic-virtual-hosts-nginx-apache\/\">http:\/\/www.sitepoint.com\/set-automatic-virtual-hosts-nginx-apache\/<\/a><\/p>\n<p>Деплой по хуку в гитлабе<br \/>\n<a href=\"https:\/\/github.com\/mauricerenck\/code-samples\/blob\/master\/gitlab-deploy\/deploy.php\">https:\/\/github.com\/mauricerenck\/code-samples\/blob\/master\/gitlab-deploy\/deploy.php<\/a><br \/>\n<a href=\"http:\/\/habrahabr.ru\/post\/246699\/\">http:\/\/habrahabr.ru\/post\/246699\/<\/a><br \/>\n<a href=\"http:\/\/habrahabr.ru\/post\/269163\/\">http:\/\/habrahabr.ru\/post\/269163\/<\/a><\/p>\n",
            "date_published": "2015-11-05T14:18:11+01:00",
            "date_modified": "2015-11-05T14:18:09+01:00",
            "tags": [
                "centos",
                "mysql",
                "nginx",
                "varnish"
            ],
            "_date_published_rfc2822": "Thu, 05 Nov 2015 14:18:11 +0100",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "66",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [],
                "og_images": []
            }
        }
    ],
    "_e2_version": 4134,
    "_e2_ua_string": "Aegea 11.3 (v4134)"
}