fradelg / docker-mysql-cron-backup Goto Github PK
View Code? Open in Web Editor NEWDocker image to backup all your databases periodically
License: Apache License 2.0
Docker image to backup all your databases periodically
License: Apache License 2.0
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.
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
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
Is it possible to backup to a remote service?
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!
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
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
Thank you for this very helpful dB tool.
Is there any plans for adding Web UI to list and restore backups?
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
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
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.
Is possible, send the backup to S3
Thanks
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.
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
Which IMO should be:
ln -s "$FILENAME".gz "$LATEST"
Cheers.
https://hub.docker.com/r/fradelg/mysql-cron-backup/ indicates that https://github.com/fradelg/docker-images is the source for the automated build.
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!
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.
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?
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.
I can't use host IP to connect or remote IP to backup
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?
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
latest mariadb uses environment variables like MARIADB_USER MARIADB_PASSWORD MARIADB_DATABASE instead of the MYSQL prefix.
Docker compose doesn't override multiple env files when using the form MYSQL_USER: ${MARIADB_USER}
So I have to update the same value twice when sharing the env_file with the mariadb container:
MARIADB_USER: user
MYSQL_USER: user
Supporting Docker secrets to this docker image would be great :D
Thanks
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
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.
Hi, could there be multiarch support for arm cpus?
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.
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.
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
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 :)
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.
Hi,
have a quick question, can this container be run as an user different from root?
Thanks!
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.
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?
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?
Could be done with mysqldump --ignore-table=DATABASE.table1 --ignore-table=DATABASE.table2
and then some ENV var in the compose file.
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
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.
docker-mysql-cron-backup/backup.sh
Line 24 in 42312b9
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.