fradelg/docker-mysql-cron-backup

Docker image to backup all your databases periodically

docker-mysql-cron-backup's Issues

Allow for no compression

Hi!

I'm currently using this project to backup a very important database, and this has proved to be extremely useful when I had to restore data after resetting my computer. So thanks a lot for making this tool!

Now I have a little question about a feature that would help me: would it be possible to specify a setting to disable compression entirely? I always backup the entire directory to have an history of all backups ever made, which means I have to use the raw SQL files and compress them together to save space, which is not possible if they are already compressed.

What I do currently is that I periodically uncompress all the SQL files to re-compress them as a single archive. But it would be a lot more handy if I could just specify set the GZIP_LEVEL variable to 0 so it would only export the .sql file itself.

Would it be doable? Thanks in advance for your answer :)

Old github URL in docker automated build?!?

https://hub.docker.com/r/fradelg/mysql-cron-backup/ indicates that https://github.com/fradelg/docker-images is the source for the automated build.

Latest symlink

Hi,

First of all, thank you for this docker image !

I found an issue about the latest symlink, which should point to the new created archive, not the temp backup. It's here:

docker-mysql-cron-backup/backup.sh

Lines 17 to 19 in 7e99a5d

	gzip -f "$FILENAME"
	rm "$LATEST" 2> /dev/null
	ln -s "$FILENAME" "$LATEST"

Which IMO should be:
ln -s "$FILENAME".gz "$LATEST"

Cheers.

Mount path is not absolute

In the volumes section I have:

volumes:
      - /Users/<user>/Documents/Coding/phpmyadmin/backup:/backup

When I try running the demo, I get the error:

Error response from daemon: invalid volume specification: 'dc8a85f672ffa6d7e2309cc9d8b17aa7760d4b24b4494c0a4170b69b38c008c3:~/Documents/Coding/phpmyadmin/backup:rw': invalid mount config for type "volume": invalid mount path: '~/Documents/Coding/phpmyadmin/backup' mount path must be absolute

Large backup files cannot be restored

My backup file in .sql.gz format exceeds 170M and cannot be restored to the database using the restore.sh script, and it will be stuck after executing the command

backup.sh not terminating and high CPU usage

I'm using https://hub.docker.com/r/fradelg/mysql-cron-backup/~/dockerfile/ for making backups. Since some days there is seen a high cpu usage like you can see with ctop:

container environment:

           "Env": [
                "MYSQL_PASS=foobar",
                "MAX_BACKUPS=20",
                "MYSQL_USER=root",
                "MYSQL_HOST=nextcloud-db",
                "CRON_TIME=0 0 * * *",
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "MYSQL_PORT=3306"
            ],

Here some debugging output:

/ # ps aux
PID   USER     TIME   COMMAND
    1 root       0:00 crond -f
    6 root       0:09 tail -F /mysql_backup.log
    8 root       0:00 /bin/sh -c /backup.sh >> /mysql_backup.log 2>&1
    9 root     248:09 {backup.sh} /bin/bash /backup.sh
28040 root       0:00 sh
29468 root       0:00 /bin/sh -c /backup.sh >> /mysql_backup.log 2>&1
29477 root      64:20 {backup.sh} /bin/bash /backup.sh
29642 root       0:00 ps aux
29643 root       0:00 {backup.sh} /bin/bash /backup.sh
29645 root       0:00 {backup.sh} /bin/bash /backup.sh
29646 root       0:00 {backup.sh} /bin/bash /backup.sh
29647 root       0:00 {backup.sh} /bin/bash /backup.sh
29648 root       0:00 {backup.sh} /bin/bash /backup.sh
/ # head -10 /mysql_backup.log
=> Backup started at 201704070000
Dumping database: nextcloud
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
Backup ${TARGET} is deleted
/ # grep -c deleted /mysql_backup.log
17497990
/ # ls -la backup | tail -4
-rw-r--r--    1 root     root        103755 Apr  5 00:00 201704050000.nextcloud.sql.gz
-rw-r--r--    1 root     root        104040 Apr  6 00:00 201704060000.nextcloud.sql.gz
-rw-r--r--    1 root     root        104248 Apr  7 00:00 201704070000.nextcloud.sql.gz
-rw-r--r--    1 root     root        107657 Apr  8 00:00 201704080000.nextcloud.sql.gz
/ # ls -la backup | wc -l
28

As I defined to wipe everything older then 20 days I guess the problem is the part removing old dumps?

Thanks, Jan.

Runs privileged when it shouldn't

This runs privileged as root user in the container, which poses a security risk for Docker daemon users since root in the container maps to root on the host. Everything this does can and should be done as an unprivileged user.

I addressed this in my fork which also removes the golang wrapper bloat and replaces cron with anacron for persistent backup intervals.

[Request] Optional external backup

Is it possible to add a backup system for external vendors?

You could use http://duplicity.nongnu.org/ or https://github.com/rclone/rclone, not if there are other better tools.

restore with command wont work with Hyphen in database name

Hello,

I tried to do a backup with the command docker container exec <your_mysql_backup_container_name> /restore.sh /backup/<your_sql_backup_gz_file> but I get the message that the database is unkown. This is probably because the database contained a - in the name. Databases that do not make use of a - work fine. Do you maybe have a solution for this or should I recreate all my databases without a hyphen in the name?

ERROR: for project-mysql Cannot create container for service mysql: mkdir /home/chiqui3d/project/data/backup/latest.project_docker.sql.gz: file exists

Hello,

Do you know why I'm getting this error when I try to make a docker-compose up -d --build

ERROR: for project-mysql Cannot create container for service mysql: mkdir /home/chiqui3d/project/data/backup/latest.project_docker.sql.gz: file exists

This is the configuration of docker-compose.yml

volumes:
  dbdata:
  appdata:

services:
  backup:
    container_name: ${PROJECT_NAME}-backup
    image: fradelg/mysql-cron-backup
    depends_on:
      - mysql
    volumes:
      - ./data/backup:/backup
    environment:
      - MYSQL_DB=${MYSQL_DATABASE}
      - MYSQL_HOST=mysql
      - MYSQL_USER=root
      - MYSQL_PASS=${MYSQL_ROOT_PASSWORD}
      - MAX_BACKUPS=15
      - INIT_BACKUP=1
      # Every day at 03:00
      - CRON_TIME=0 3 * * *
    restart: unless-stopped
    networks:
      - db
  mysql:
    build:
      context: ./
      dockerfile: ./docker/mysql/Dockerfile
      args:
        - MYSQL_VERSION=${MYSQL_VERSION}
        - DOCKER_ENV=${DOCKER_ENV}
    restart: always
    container_name: ${PROJECT_NAME}-mysql
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    volumes:
      - dbdata:/var/lib/mysql
      - ./data/backup/latest.${MYSQL_DATABASE}.sql.gz:/docker-entrypoint-initdb.d/database.sql.gz
    networks:
      - db

MYSQL_PORT is required while using MYSQL_HOST

When someone only defines the MYSQL_HOST container, it doesn't start. You need to define MYSQL_PORT, even if the port is the default port for MySQL.

If mysqldump fails, all previous dumps are deleted

Hi there,
I like the idea behind this project, yet, I found there is a very severe bug in backup.sh. You probably need to move DB_COUNTER=$(( DB_COUNTER + 1 )) on line 23 out of this if/then-block and into the enclosing one instead. Why? You set DB_COUNTER=0 on line 9; if mysqldump fails, it will remain zero, DB_COUNTER is never increased to at least 1. On line 32, this will cause havoc, since MAX_FILES=$(( MAX_BACKUPS * DB_COUNTER )), so MAX_FILES becomes zero. Subsequently, all dumps are deleted, just when you need them. Just learned this the hard way.

Problem with dial

Error:

Waiting for: tcp://mysql:3306
Problem with dial: dial tcp: lookup mysql on 127.0.0.11:53: server misbehaving. Sleeping 1s

I'm getting the above error, Does anyone know what can be the issue!!

I have tried running the command and docker-compose. Getting same error.

Feature request: E-Mail notification

It would be cool if you could still be notified by email whether a backup was successful or not.
The whole thing with an external SMTP host, which you can enter.
Could that possibly be implemented?

Make backup on container shutdown

How is possible to automatically make a backup when I'm shutdown the container?

I use automatic backup with cron and automatic restore on startup. If I restart the container, it restores the last backup and I lose all modifications made after that.

Could you help me?
Thanks

Feature Request: Support for Postgres backup

I really like this project, as it is simple and it works well!
As I'm searching for a postgres/postgis backup mechanism like this, does it exists an equivalent solution for that? Or do you think it could be done in the future?

PS: As this project follows a very simple architecture, I don't know if it could be right to add postgres/postgis support directly into this repository. However, if it not exists an equivalent project, it could be else a good idea to develop a parallel project for supporting each different database could be intresting. This Issue could be seen not only as a directly feature request to this project, but as a starting point for others too, so I think here should be the best place to ask it!

Thank you very much
Giuseppe

"No database selected"

Hi,

Since the commit 7e99a5d I'm getting this error from backup.sh:

mysqldump: Got error: 1046: "No database selected" when selecting the database

If I get into the container and remove the double quotes added on 7e99a5d, it works as before. Could it be because in my case $MYSQLDUMP_OPTS is empty?

No .sql.gz file created

Hi,
I've added the new container for a mysql backup and have a probelm with the output. Reading the tool description I can see that I should get an output file name in format like 201708060500.${DATABASE_NAME}.sql.gz. Unfortunately instead I got another folder with a db name and inside .ibd files for each table and cannot find any .gz file.
Is that a correct behavior or am I missing something?

Below is my environment configuration for the backup container:

environment:
MYSQL_USER: "${DB_USER}"
MYSQL_PASS: "${DB_PASS}"
MYSQL_DB: "${DB_NAME}"
CRON_TIME: 0 0 * * *
MYSQL_HOST: "${DB_HOST}"
MYSQL_PORT: "${DB_PORT}"
TIMEOUT: 10s
INIT_BACKUP: 1
MAX_BACKUPS: 3
GZIP_LEVEL: 9

Move mysql_backup.log to var/log

Hello!,

I would like to mount the mysql_backup.log file on the host, that way I can manage it with a log provider like ELK, I don't know if I'm wrong but as the file is in the root directory I have no choice than to mount the full system.

Is it possible to move it to the /var/log directory?

I don't know if there is a better way to manage the logs in production.

Backing up to a remote service?

Is it possible to backup to a remote service?

Request: Backup for multiple MySQL containers

Hi,

Now I need to spin a new "docker-mysql-cron-backup" container for each MySQL docker container.
I would be awesome to somehow have 1 backup-container that can access all my MySQL containers across different compose stacks to make a backup.

Is something like that feasible?

Request: Feature to ignore certain tables

Could be done with mysqldump --ignore-table=DATABASE.table1 --ignore-table=DATABASE.table2 and then some ENV var in the compose file.

Change the value of max_binlog_cache_size

Thanks for providing this useful container.

I am doing a research aiming at finding issues in configuration files. I have a question about one MySQL config: max_binlog_cache_size. It seems the official document says "The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB."

However, the default value is 18446744073709551615, which is much larger than the recommended value.

Shall we change the value to 4GB?
Thanks!

xrealloc: cannot allocate

First time that i am using the restore option, but there is something worng
bash-5.1# /restore.sh /backup/202111200200.homeassistant.sql.gz /restore.sh: xrealloc: cannot allocate 2147478528 bytes

MAX_BACKUPS and multiple databases issue

Hello,

First of all, thanks for your work!

I just tried your image with the environment variable MAX_BACKUPS set to 1. However, I have two databases, and at the end of the backup, the first dump of the first database is deleted.
Is it possible to update your script to keep one backup by database and not one backup?

Thanks.

Support for docker secrets e.g. MYSQL_PASS_FILE

Supporting Docker secrets to this docker image would be great :D

Thanks

"Halt" Database on mysqldump

Hi,

i think you should put the parameter --single-transaction into your mysqldump statement. This is according to this answer: https://dba.stackexchange.com/a/30847

The Problem, which is solved by the mentioned parameter is the following: In big databases with a lot of reads and writes all the time the data in the mysqldump would differ timewise between the tables.

Greetings
hasechris

Add option to RUN_ONCE in order to use this image for Kubernetes Cronjobs

Hi,

Would it be possible to add a RUN_ONCE option to the image which means that it'll do a backup on startup (like INIT_BACKUP) but after that will exit/stop the container?

I'd like to continue using the image but with a Kubernetes Cronjob. So, the Container orchestrator takes care of scheduling the container.

S3

Is possible, send the backup to S3

Thanks

using secrets

Hi there,
I like your docker image because it's very easy to use and phpmyadmin supports the .sql.gz by default.

Does the image support secret to set the db password?

Remove double quotes from $MYSQLDUMP_OPTS

docker-mysql-cron-backup/backup.sh

Line 24 in 42312b9

if mysqldump --single-transaction "$MYSQLDUMP_OPTS" -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$db" > "$FILENAME"

In my case, I need to specify multiple options, but because of the double quotes it fails

example:
MYSQLDUMP_OPTS=--quick --lock-tables=false --compact --opt

CPU spikes every minute

What is the reason for this? The db backup image is spiking every minute.

db_backup:
image: fradelg/mysql-cron-backup
environment:
- MYSQL_HOST=
- MYSQL_USER=
- MYSQL_PASS=
- MYSQL_DATABASE=
- CRON_TIME=0 3,15 * * *
- MAX_BACKUPS=30
- INIT_BACKUP=0
- GZIP_LEVEL=9
- MYSQLDUMP_OPTS=--no-tablespaces
volumes:
- db_backup_data:/backup
depends_on:
- db

the image is set to make a backup twice a day. For a simple cronjob that runs twice a day, the cpu spikes are quite a lot.

Restore one DB from list

Hi,

In current configuration for auto restore during intial docker startup, restore script will restore all databases configured in environment variable MYSQL_DATABASE which is defaulted to --all-databases
If we set INIT_RESTORE_LATEST, during startup it will check MYSQL_DATABASE and restore all databases set on it.
The problem is if we want to restore one database only, if change MYSQL_DATABASE, this will impact the backup.

I suggest to add another environment variable for restore databases for example MYSQL_DATABASE_RESTORE, so backup will not be impacted and user will have control to restore any database just by changing the value of MYSQL_DATABASE_RESTORE and set the flag INIT_RESTORE_LATEST.

dial tcp: lookup mariadb_galera on 127.0.0.11:53: no such host

I can't use host IP to connect or remote IP to backup

ERROR 1046 (3D000) at line 22: No database selected

When running
docker container exec nextcloud_db_backup_1 /restore.sh /backup/latest.nextcloud.sql.gz

I get that error.

=> Restore database from /backup/latest.nextcloud.sql.gz
ERROR 1046 (3D000) at line 22: No database selected
=> Restore failed

Part of my compose file:

  db_backup:
    image: fradelg/mysql-cron-backup
    restart: unless-stopped
    depends_on:
      - db
    env_file:
      - ./db.env
      - ./global.env
    environment:
      - MYSQL_DB=nextcloud
      - MYSQL_HOST=db
      - MYSQL_PORT=3306
      - MYSQL_USER=nextcloud
      - MAX_BACKUPS=15
      - INIT_BACKUP=1
      # Every day at 03:00
      - CRON_TIME=0 3 * * *
      # Make it small
      - GZIP_LEVEL=9
    volumes:
      - db_backup:/backup

REQUEST: Multiarch support

Hi, could there be multiarch support for arm cpus?

Web UI

Thank you for this very helpful dB tool.

Is there any plans for adding Web UI to list and restore backups?

Problems enviroment CRON_TIME

Logs:
2021/03/18 14:28:06 Waiting for: tcp://mysql:3306
2021/03/18 14:28:06 Connected to tcp://mysql:3306
/run.sh: line 5: [: : integer expression expected
=> Running cron task manager

Arbitrary user for container

Hi,

have a quick question, can this container be run as an user different from root?

Thanks!

Restore path

Hi,

would like to ask how the restore process works. The databases will be restored in the original paths, picked up from the databases docker compose files?

Thanks!