12 posts tagged

mysql

The main idea is to have fully automated docker database backup from low end D-Link NAS DNS-320.

Solution design is following:

  1. My backup box will copy backup.sh script to the remote coreos-03 host.
  2. Then remote host copies backup.sh script into database container.
  3. 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.
  4. Rsnapshot saves folder with gzipped SQL dump and rotates old backup folders as necessary.

So, we will need only

  • ssh
  • tar
  • rsnapshot

Here is my working implementation:

script backup.sh

#!/bin/bash
## env vars are already in docker container
/usr/bin/mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE

script backup-coreos-itservice.sh

/ffp/bin/scp /ffp/home/root/backup.sh core@coreos-03:/home/core/itservice/backup.sh
/usr/sbin/ssh -C core@coreos-03 "docker cp /home/core/itservice/backup.sh itservice_db_1:/usr/local/bin/backup.sh"
/usr/sbin/ssh -C core@coreos-03 "docker exec itservice_db_1 /usr/local/bin/backup.sh" > latest.sql
/opt/bin/tar czf itservice-sql-dump.tar.gz latest.sql --remove-files

rsnapshot.conf

...
backup_script	/mnt/HD/HD_a2/ffp/home/root/backup-coreos-itservice.sh	coreos-03/itservice_db_1
...

crontab

0 */4 * * * rsnapshot hourly
30 3 * * *  rsnapshot daily
0  3 * * 1  rsnapshot weekly
30 2 1 * *  rsnapshot monthly

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.

dockermysql
  • Use custom command to launch mysql container with right collation:
command: mysqld --character-set-server=utf8 --collation-server=utf8_general_ci
  • Alter existing database:
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

use

docker-compose exec db mysql -uuser -ppass database

to exec your queries in running container.

dockermysql

You can use following line in service description in docker-compose.yml for mysql database

command: mysqld --character-set-server=utf8 --collation-server=utf8_general_ci
dockermysql

In case you have warning message about insecure user and password usage from command line you can do following:

  1. use mysql_config_editor set login-path=local host=localhost user=db_user password executed in container via exec. That will create encrypted file.
  2. Specify —login-path=local instead of -u user and -p password in your backup script.

I have my backups via rsnapshot. This is something like TimeMachine for Mac with handy directory structure and without duplication.

And my backup script looks like this:

/usr/bin/ssh -i /mnt/HD/HD_a2/ffp/home/root/id_rsa_nexus.key -C core@myipaddress "docker exec itservice_db_1 mysqldump --login-path=local itservice_production" >
 mysqlbackup.sql

That will create mysql dump on your remote backup host without passing user and password in more secure way.

docker-compose exec db mysql_config_editor set --login_path=local --host=localhost --user=itservice --password

will ask password and save credentials in container.

Resources: http://stackoverflow.com/questions/20751352/suppress-warning-messages-using-mysql-from-within-terminal-but-password-written

The main thing is to specify -i key, it allows input redirection from host to container.

docker exec -i container mysql -uuser -ppassword database < dump.sql
dockermysql

Вот простой рецепт:

- name: backup mysql from container
  gather_facts: no
  vars:
    - db_container: container_db_1
    - image: mysql:5.6
    - db:
        name: database_name
        user: root
        password: root
    - dump_file: ./dump-latest.sql
  tasks:
    - name: run backup container
      shell: "docker run --rm --link {{db_container}}:db --entrypoint \"/usr/bin/mysqldump\" {{image}} -hdb -u{{db.user}} -p{{db.password}} {{db.name}}"
      register: output

    - name: copy output
      local_action: copy content="{{ output.stdout }}" dest="{{dump_file}}"

Работает БЕЗ создания файлов на удаленном хосте, помимо, конечно, временного контейнера.

Мой предыдущий способ «просто запусти и залинкуй контейнер» разбился о проблемы с поддержкой mysql версии 5.7 и выше и то, что docker-compose версии 2 не поддерживает публикацию environment переменных слинкованного контейнера.

Поэтому пришлось разработать свой, крайне простой способ для выгрузки mysql бекапа из mysql контейнера. Поскольку у mysql свой хитры entrypoint, простая замена CMD не работала.

Вот работающий код.

docker run \
    --rm \
    --link container_db_1:db \
    --entrypoint "/usr/bin/mysqldump" mysql:5 -hdb -uroot -proot database > ./backup/mysql-latest.sql

обратите внимание, что > ./backup/mysql-latest.sql завернет вывод из контейнера в файл на хосте, а не внутри.

В качестве образа mysql:5 используйте тот образ, который уже используется в вашем проекте (mysql:5.6, например).

Чуть позднее сделаю рецепт для ansible.

Ctrl + ↓ Earlier