question about MYSQLDUMP_

[michabbb]

hi avi,

i tried your latest changes and also wanted to test the MYSQLDUMP vars like this:

-e MYSQLDUMP_opt -e MYSQLDUMP_skip-lock-tables -e MYSQLDUMP_R -e MYSQLDUMP_triggers

i don´t know if the ouput of "ps ax" misses that or if i do something wrong here, because i don´t see these options used in the mysqldump call:

root@195:~# ps ax|grep mysqldump
19346 ?        S      0:19 mysqldump -h mysql -P 3306 -uroot --databases xxxxxxxxx

am i doing something wrong ? 🤔

[michabbb]

@deitch i noticed that -e RUN_ONCE is not working but -e RUN_ONCE=true is. so do i have to use MYSQLDUMP_skip-lock-tables=true instead ? 🤔 i will test this 😏

[michabbb]

mysqldump: option '--opt' cannot take an argument okay, so MYSQLDUMP_opt=true does not work 😏

[michabbb]

@deitch btw.... a notice: if you convert these variables to --something you have no chance to use parameters with only one dash, like -R ℹ️

[deitch]

Correct, it only takes vars that have a value defined, see here :

# capture our var settings
DUMPVARS=""
for i in $(env | awk -F_ '/^MYSQLDUMP_/ {print $2}'); do
  DUMPVARS="${DUMPVARS} --${i}"
done

We would need a way to determine that the var is set as an env var, but has no value. Suggestions?

[deitch]

I stand corrected. I looked at that code again, and it should do the right thing (more or less).

Can you run the container with -e DB_DUMP_DEBUG=debug to see what it is calling?

[michabbb]

@deitch so here´s my call of the container:

docker run --rm --link xxxx:mysql -e RUN_ONCE=true -e DB_DUMP_BY_SCHEMA=true -e COMPRESSION=bzip2 -e DB_DUMP_TARGET=/backup/mysql -e DB_SERVER=mysql -e DB_USER=root -e MYSQLDUMP_opt -e MYSQLDUMP_skip-lock-tables -e MYSQLDUMP_triggers -e DB_DUMP_DEBUG=debug -v /backup:/backup deitch/mysql-backup

and here´s the output of DEBUG:

+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases information_schema
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases mysql
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases performance_schema
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases xxxxxxxxx

Correct, it only takes vars that have a value defined

okay, then it´s clear.... 🤔 (thinking)

[michabbb]

We would need a way to determine that the var is set as an env var, but has no value. Suggestions?

@deitch for busy databases it´s import to make dumps with "skip-lock-tables"..... so my first suggestion would be, to use _ for vars with a value and __ for vars without. means, i would have to use it that way:

-e MYSQLDUMP__skip-lock-tables -e MYSQLDUMP_something-elese=1000

are better way would be, if i could write all mysql-dump-parameters in one single var, so you don´t have to mess with parsing all that stuff. for example:

-e MYSQLDUMP_OPTIONS="--skip-lock-tables --opt --blabla=1000 -x=50"

so you don´t have to parse, just add the stuff to the dump command. my 2nd recommendation solves two problem:

• you can use params with and without values
• you can use params with one dash or more (-R vs --opt)

[deitch]

Can you put the whole debug output there, just filter out any sensitive data?

[michabbb]

+ file_env DB_SERVER
+ local var=DB_SERVER
+ local fileVar=DB_SERVER_FILE
+ local def=
+ '[' mysql ']'
+ '[' '' ']'
+ local val=
+ '[' mysql ']'
+ val=mysql
+ export DB_SERVER=mysql
+ DB_SERVER=mysql
+ unset DB_SERVER_FILE
+ file_env DB_PORT
+ local var=DB_PORT
+ local fileVar=DB_PORT_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_PORT=
+ DB_PORT=
+ unset DB_PORT_FILE
+ file_env DB_USER
+ local var=DB_USER
+ local fileVar=DB_USER_FILE
+ local def=
+ '[' root ']'
+ '[' '' ']'
+ local val=
+ '[' root ']'
+ val=root
+ export DB_USER=root
+ DB_USER=root
+ unset DB_USER_FILE
+ file_env DB_PASS
+ local var=DB_PASS
+ local fileVar=DB_PASS_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_PASS=
+ DB_PASS=
+ unset DB_PASS_FILE
+ file_env DB_NAMES
+ local var=DB_NAMES
+ local fileVar=DB_NAMES_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_NAMES=
+ DB_NAMES=
+ unset DB_NAMES_FILE
+ file_env DB_DUMP_FREQ 1440
+ local var=DB_DUMP_FREQ
+ local fileVar=DB_DUMP_FREQ_FILE
+ local def=1440
+ '[' '' ']'
+ local val=1440
+ '[' '' ']'
+ '[' '' ']'
+ export DB_DUMP_FREQ=1440
+ DB_DUMP_FREQ=1440
+ unset DB_DUMP_FREQ_FILE
+ file_env DB_DUMP_BEGIN +0
+ local var=DB_DUMP_BEGIN
+ local fileVar=DB_DUMP_BEGIN_FILE
+ local def=+0
+ '[' '' ']'
+ local val=+0
+ '[' '' ']'
+ '[' '' ']'
+ export DB_DUMP_BEGIN=+0
+ DB_DUMP_BEGIN=+0
+ unset DB_DUMP_BEGIN_FILE
+ file_env DB_DUMP_DEBUG
+ local var=DB_DUMP_DEBUG
+ local fileVar=DB_DUMP_DEBUG_FILE
+ local def=
+ '[' debug ']'
+ '[' '' ']'
+ local val=
+ '[' debug ']'
+ val=debug
+ export DB_DUMP_DEBUG=debug
+ DB_DUMP_DEBUG=debug
+ unset DB_DUMP_DEBUG_FILE
+ file_env DB_DUMP_TARGET /backup
+ local var=DB_DUMP_TARGET
+ local fileVar=DB_DUMP_TARGET_FILE
+ local def=/backup
+ '[' /backup/mysql ']'
+ '[' '' ']'
+ local val=/backup
+ '[' /backup/mysql ']'
+ val=/backup/mysql
+ export DB_DUMP_TARGET=/backup/mysql
+ DB_DUMP_TARGET=/backup/mysql
+ unset DB_DUMP_TARGET_FILE
+ file_env DB_DUMP_BY_SCHEMA
+ local var=DB_DUMP_BY_SCHEMA
+ local fileVar=DB_DUMP_BY_SCHEMA_FILE
+ local def=
+ '[' true ']'
+ '[' '' ']'
+ local val=
+ '[' true ']'
+ val=true
+ export DB_DUMP_BY_SCHEMA=true
+ DB_DUMP_BY_SCHEMA=true
+ unset DB_DUMP_BY_SCHEMA_FILE
+ file_env DB_DUMP_KEEP_PERMISSIONS true
+ local var=DB_DUMP_KEEP_PERMISSIONS
+ local fileVar=DB_DUMP_KEEP_PERMISSIONS_FILE
+ local def=true
+ '[' '' ']'
+ local val=true
+ '[' '' ']'
+ '[' '' ']'
+ export DB_DUMP_KEEP_PERMISSIONS=true
+ DB_DUMP_KEEP_PERMISSIONS=true
+ unset DB_DUMP_KEEP_PERMISSIONS_FILE
+ file_env DB_RESTORE_TARGET
+ local var=DB_RESTORE_TARGET
+ local fileVar=DB_RESTORE_TARGET_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_RESTORE_TARGET=
+ DB_RESTORE_TARGET=
+ unset DB_RESTORE_TARGET_FILE
+ file_env AWS_ENDPOINT_URL
+ local var=AWS_ENDPOINT_URL
+ local fileVar=AWS_ENDPOINT_URL_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_ENDPOINT_URL=
+ AWS_ENDPOINT_URL=
+ unset AWS_ENDPOINT_URL_FILE
+ file_env AWS_ENDPOINT_OPT
+ local var=AWS_ENDPOINT_OPT
+ local fileVar=AWS_ENDPOINT_OPT_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_ENDPOINT_OPT=
+ AWS_ENDPOINT_OPT=
+ unset AWS_ENDPOINT_OPT_FILE
+ file_env AWS_ACCESS_KEY_ID
+ local var=AWS_ACCESS_KEY_ID
+ local fileVar=AWS_ACCESS_KEY_ID_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_ACCESS_KEY_ID=
+ AWS_ACCESS_KEY_ID=
+ unset AWS_ACCESS_KEY_ID_FILE
+ file_env AWS_SECRET_ACCESS_KEY
+ local var=AWS_SECRET_ACCESS_KEY
+ local fileVar=AWS_SECRET_ACCESS_KEY_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_SECRET_ACCESS_KEY=
+ AWS_SECRET_ACCESS_KEY=
+ unset AWS_SECRET_ACCESS_KEY_FILE
+ file_env AWS_DEFAULT_REGION
+ local var=AWS_DEFAULT_REGION
+ local fileVar=AWS_DEFAULT_REGION_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_DEFAULT_REGION=
+ AWS_DEFAULT_REGION=
+ unset AWS_DEFAULT_REGION_FILE
+ file_env SMB_USER
+ local var=SMB_USER
+ local fileVar=SMB_USER_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export SMB_USER=
+ SMB_USER=
+ unset SMB_USER_FILE
+ file_env SMB_PASS
+ local var=SMB_PASS
+ local fileVar=SMB_PASS_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export SMB_PASS=
+ SMB_PASS=
+ unset SMB_PASS_FILE
+ file_env COMPRESSION gzip
+ local var=COMPRESSION
+ local fileVar=COMPRESSION_FILE
+ local def=gzip
+ '[' bzip2 ']'
+ '[' '' ']'
+ local val=gzip
+ '[' bzip2 ']'
+ val=bzip2
+ export COMPRESSION=bzip2
+ COMPRESSION=bzip2
+ unset COMPRESSION_FILE
+ [[ -n debug ]]
+ set -x
+ '[' -n root ']'
+ DBUSER=-uroot
+ '[' -n '' ']'
+ DBPASS=
+ DUMPVARS=
++ awk -F_ '/^MYSQLDUMP_/ {print $2}'
++ env
+ '[' -z mysql ']'
+ '[' -z '' ']'
+ echo 'DB_PORT not provided, defaulting to 3306'
+ DB_PORT=3306
+ COMPRESS=
+ UNCOMPRESS=
+ case $COMPRESSION in
+ COMPRESS=bzip2
+ UNCOMPRESS='bzip2 -d'
+ EXTENSION=tbz2
+ TMPDIR=/tmp/backups
+ TMPRESTORE=/tmp/restorefile
+ declare -A uri
+ [[ -n '' ]]
DB_PORT not provided, defaulting to 3306
++ date
+ echo Starting at Mon Jan 14 13:24:59 UTC 2019
Starting at Mon Jan 14 13:24:59 UTC 2019
++ date +%s
+ current_time=1547472299
+ freq_time=86400
++ date +%Y%m%d
+ today=20190114
+ [[ +0 =~ ^\+(.*)$ ]]
+ waittime=0
+ target_time=1547472299
+ '[' -z true ']'
+ true
+ mkdir -p /tmp/backups
+ do_dump
++ date -u +%Y%m%d%H%M%S
+ now=20190114132459
+ SOURCE=db_backup_20190114132459.tbz2
+ TARGET=db_backup_20190114132459.tbz2
+ '[' -d /scripts.d/pre-backup/ ']'
+ workdir=/tmp/backup.1
+ rm -rf /tmp/backup.1
+ mkdir -p /tmp/backup.1
+ '[' -n true -a true = true ']'
+ [[ -z '' ]]
++ mysql -h mysql -P 3306 -uroot -N -e 'show databases'
+ DB_NAMES='information_schema
mysql
performance_schema
xxxxxx
xxxxxxxxxxx
xxxxxxxxx'
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases information_schema
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases mysql
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases performance_schema
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases xxxxxxxx
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases xxxxxxxx
+ for onedb in $DB_NAMES
+ mysqldump -h mysql -P 3306 -uroot --databases xxxxxxxxx
+ tar -C /tmp/backup.1 -cvf - .
+ bzip2
./
./performance_schema_20190114133900.sql
./xxxxxxxxxx_20190114133900.sql
./xxxxxxxxx_20190114133900.sql
./xxxxxxxx_20190114133900.sql
./information_schema_20190114133900.sql
./mysql_20190114133900.sql
+ rm -rf /tmp/backup.1
+ '[' -d /scripts.d/post-backup/ ']'
+ '[' -f /scripts.d/source.sh ']'
+ '[' -f /scripts.d/target.sh ']'
+ for target in ${DB_DUMP_TARGET}
+ backup_target /backup/mysql
+ local target=/backup/mysql
+ uri_parser /backup/mysql
+ uri=()
+ full=/backup/mysql
+ full=/backup/mysql
+ full=/backup/mysql
+ [[ / == \/ ]]
+ full=file://localhost/backup/mysql
+ [[ file://l == \f\i\l\e\:\/\/\/ ]]
+ pattern='^(([a-z0-9]{2,5})://)?((([^:\/]+)(:([^@\/]*))?@)?([^:\/?]+)(:([0-9]+))?)(\/[^?]*)?(\?[^#]*)?(#.*)?$'
+ [[ file://localhost/backup/mysql =~ ^(([a-z0-9]{2,5})://)?((([^:\/]+)(:([^@\/]*))?@)?([^:\/?]+)(:([0-9]+))?)(\/[^?]*)?(\?[^#]*)?(#.*)?$ ]]
+ full=file://localhost/backup/mysql
+ uri[uri]=file://localhost/backup/mysql
+ uri[schema]=file
+ uri[address]=localhost
+ uri[user]=
+ uri[password]=
+ uri[host]=localhost
+ uri[port]=
+ uri[path]=/backup/mysql
+ uri[query]=
+ uri[fragment]=
+ [[ file == \s\m\b ]]
+ [[ -n '' ]]
+ return 0
+ case "${uri[schema]}" in
+ mkdir -p /backup/mysql
+ cpOpts=-a
+ '[' -n true -a true = false ']'
+ cp -a /tmp/backups/db_backup_20190114133900.tbz2 /backup/mysql/db_backup_20190114133900.tbz2
+ /bin/rm /tmp/backups/db_backup_20190114133900.tbz2
+ '[' -z true ']'
+ exit 0

[deitch]

There are a few things going on here, but the biggest one is how docker passes env vars.

When you do docker run -e ABC=def -e FOO ... a few things happen in the container:

• env var ABC is set to def (as expected)
• env var FOO is set to whatever it is in the calling process (the one that called docker run).
  • if FOO was set to anything (even "", as long as it was set), then it will be set to the same thing in the container.
  • if FOO was not set to something in the calling process, then it is unset in the container. Not set to "", but unset entirely.

There is a potential bug in the container entrypoint itself that it might not properly set it if it is set to "", but that is secondary.

In essence, running docker run -e MYSQLDUMP_someoption ... will not tell the container anything. Inside, MYSQLDUMP_someoption will be unset.

So, for this to work, you have to call it with: docker run -e MYSQLDUMP_someoption= .... You can set it to MYSQLDUMP_someoption="" or even just MYSQLDUMP_someoption=, but there has to be an = sign there.

Try that, run it in debug, let's see what happens.

[michabbb]

with -e MYSQLDUMP_opt="" i get

+ mysqldump -h mysql -P 3306 -uroot --databases mysql --skip-lock-tables= --triggers= --opt=
Warning: mysqldump: ignoring option '--triggers' due to invalid value ''
mysqldump: option '--opt' cannot take an argument

with -e MYSQLDUMP_opt= i get

+ mysqldump -h mysql -P 3306 -uroot --databases mysql --skip-lock-tables= --triggers= --opt=
Warning: mysqldump: ignoring option '--triggers' due to invalid value ''
mysqldump: option '--opt' cannot take an argument

i highly recommend to avoid all these problems and replace your "multiple mysqldump envs logic" with one single ENV that gets all params the user wants to use for his dump call.

[deitch]

Yeah, I like that better. Should just have MYSQLDUMP_OPTS="--opt abc --optb=def" etc.

Open a PR?

[michabbb]

@deitch i guess you are a much better bash programmer, than i am, to be honest 😏

[deitch]

much better bash programmer

Shhh... don't tell anyone. And I have an MBA too, and consult on product strategy. :-)

Check out https://github.com/deitch/mysql-backup/pull/79

[michabbb]

@deitch thanks a lot for your efforts in this project! i can confirm: works! 👍

[michabbb]

@deitch so atomic is your fulltime job, your own business ?

[deitch]

Yes. I have several consulting engagements. Most are around operational transformations (lots of cloud), some around product strategy in that space, some around developing communities and markets. I did a lot of arm work in 2018, a lot of Kubernetes work.

[michabbb]

@deitch do you also do regular server-admin jobs, for example: hey, i need to make my servers bulletproof, install tripwire and all that stuff ? 😏

[deitch]

Depends on the context. Might be easier to have this conversation directly? Email avi [at] atomicinc [dot] com ?