Pass additional options to MySQL database list command #306

New Issue

NoxInmortus · 2020-04-30T09:33:59Z

NoxInmortus commented

2020-04-30 09:33:59 +00:00

Context

Hello, I'm trying to setup mysql dumps using a restricted backups user.
I manage to connect from cli equivalent :

root@lemp01:[~]: mysql -u backups -pxxxx --skip-column-names --batch --execute 'show schemas' && echo $?
forum_gaming
imperium_airsoft
information_schema
mysql
performance_schema
roundcube
teampass_gaming
0

But still the dump fails :

Apr 29 23:00:04 lemp01 mysqld[7502]: 2020-04-29 23:00:04 343508 [Warning] Access denied for user 'backups'@'localhost' (using password: YES)

[2020-04-29 23:00:04,076] CRITICAL: Command '('mysql', '--user', 'backups', '--skip-column-names', '--batch', '--execute', 'show schemas')' returned non-zero exit status 1.

Something I do not understand either, I tried to remove /root/.my.cnf which contains :

[mysql]
user=root
password=xxxxx
default-character-set=utf8

[xtrabackup]
open-files-limit=3000

and then the dump would work. But why ? I dont understand.

How it should work

The mysqldump from borgmatic scheduled cron should work as expected, with /root/.my.cnf existing or not.

Environment

borgmatic version: 1.5.2

borgmatic installation method: PIP3

Borg version: 1.1.9-2

Python version: 3.7.3

Database version (if applicable): 10.3.22-MariaDB

operating system and version: Debian 10 x86

Borgmatic conf:

location:
    source_directories:
        - /var/www
    one_file_system: false
    files_cache: ctime,size,inode
    repositories:
        - /var/backups/borg
    exclude_from:
        - /etc/borgmatic/excludes
    exclude_caches: true
    exclude_if_present: .nobackup
    borgmatic_source_directory: /tmp/borgmatic
storage:
    encryption_passphrase: xxx
    compression: lz4
    remote_rate_limit: 5000
    umask: 0077
    lock_wait: 5
    archive_name_format: '{hostname}-{now}'
    relocated_repo_access_is_ok: true
retention:
    keep_within: 2d
    keep_daily: 7
    keep_weekly: 4
    keep_monthly: 1
    keep_yearly: 1
    prefix: '{hostname}-'
consistency:
    checks:
        - repository
        - archives
        - extract
        - data
    check_last: 1
    prefix: '{hostname}-'

hooks:
    mysql_databases:
        - name: all
          options: --single-transaction --quick
          password: xxx
          username: backups
    umask: 0077

The user backups can connect from localhost or 127.0.0.1 only, and have following privileges: *.*:LOCK TABLES,SELECT,SHOW VIEW

Cron command:

0 23 * * * root PATH=$PATH:/usr/local/bin nice -n 19 ionice -c 3 borgmatic --log-file /var/log/borg.log --log-file-verbosity 2 -c /etc/borgmatic/config.yaml

#### Context Hello, I'm trying to setup mysql dumps using a restricted backups user. I manage to connect from cli equivalent : ``` root@lemp01:[~]: mysql -u backups -pxxxx --skip-column-names --batch --execute 'show schemas' && echo $? forum_gaming imperium_airsoft information_schema mysql performance_schema roundcube teampass_gaming 0 ``` But still the dump fails : ``` Apr 29 23:00:04 lemp01 mysqld[7502]: 2020-04-29 23:00:04 343508 [Warning] Access denied for user 'backups'@'localhost' (using password: YES) [2020-04-29 23:00:04,076] CRITICAL: Command '('mysql', '--user', 'backups', '--skip-column-names', '--batch', '--execute', 'show schemas')' returned non-zero exit status 1. ``` Something I do not understand either, I tried to remove /root/.my.cnf which contains : ``` [mysql] user=root password=xxxxx default-character-set=utf8 [xtrabackup] open-files-limit=3000 ``` and then the dump would work. But why ? I dont understand. #### How it should work The mysqldump from borgmatic scheduled cron should work as expected, with /root/.my.cnf existing or not. #### Environment **borgmatic version:** 1.5.2 **borgmatic installation method:** PIP3 **Borg version:** 1.1.9-2 **Python version:** 3.7.3 **Database version (if applicable):** 10.3.22-MariaDB **operating system and version:** Debian 10 x86 Borgmatic conf: ``` location: source_directories: - /var/www one_file_system: false files_cache: ctime,size,inode repositories: - /var/backups/borg exclude_from: - /etc/borgmatic/excludes exclude_caches: true exclude_if_present: .nobackup borgmatic_source_directory: /tmp/borgmatic storage: encryption_passphrase: xxx compression: lz4 remote_rate_limit: 5000 umask: 0077 lock_wait: 5 archive_name_format: '{hostname}-{now}' relocated_repo_access_is_ok: true retention: keep_within: 2d keep_daily: 7 keep_weekly: 4 keep_monthly: 1 keep_yearly: 1 prefix: '{hostname}-' consistency: checks: - repository - archives - extract - data check_last: 1 prefix: '{hostname}-' hooks: mysql_databases: - name: all options: --single-transaction --quick password: xxx username: backups umask: 0077 ``` The user backups can connect from `localhost` or `127.0.0.1` only, and have following privileges: `*.*:LOCK TABLES,SELECT,SHOW VIEW` Cron command: ``` 0 23 * * * root PATH=$PATH:/usr/local/bin nice -n 19 ionice -c 3 borgmatic --log-file /var/log/borg.log --log-file-verbosity 2 -c /etc/borgmatic/config.yaml ```

witten commented

2020-04-30 16:19:40 +00:00

Thank you for including the detailed information in the ticket. I'm a little perplexed about the behavior you're seeing though. It sounds like borgmatic works when ~/.my.cnf is not there, and it fails due to permissions when ~/.my.cnf is present. My first reaction would be that the ~/.my.cnf is causing it to try to connect as root with invalid credentials. But that does not appear to be the case, because your logs say Access denied for user 'backups'@'localhost', not for the root database user. Are you sure that log entry corresponds to borgmatic's connection attempt? There's nothing in there about a connection from root?

The mysqldump from borgmatic scheduled cron should work as expected, with /root/.my.cnf existing or not.

I'm not sure this is possible given how borgmatic interacts with MySQL. Specifically, it invokes the mysql binary, which as far as I know, doesn't have a way to disable the ~/.my.cnf configuration file. I agree though that it's confusing to have that outside influence.

Thank you for including the detailed information in the ticket. I'm a little perplexed about the behavior you're seeing though. It sounds like borgmatic works when `~/.my.cnf` is not there, and it fails due to permissions when `~/.my.cnf` is present. My first reaction would be that the `~/.my.cnf` is causing it to try to connect as root with invalid credentials. But that does not appear to be the case, because your logs say `Access denied for user 'backups'@'localhost'`, not for the root database user. Are you sure that log entry corresponds to borgmatic's connection attempt? There's nothing in there about a connection from root? > The mysqldump from borgmatic scheduled cron should work as expected, with /root/.my.cnf existing or not. I'm not sure this is possible given how borgmatic interacts with MySQL. Specifically, it invokes the `mysql` binary, which as far as I know, doesn't have a way to disable the `~/.my.cnf` configuration file. I agree though that it's confusing to have that outside influence.

witten added the

question / support

label 2020-04-30 16:19:49 +00:00

NoxInmortus commented

2020-05-01 10:15:46 +00:00

Hello @witten, thanks for your time.

I just made two tests to make my says valid :

root@lemp01:[/etc/cron.d]: cat /etc/cron.d/borgbackup 
53 11 * * * root PATH=$PATH:/usr/local/bin nice -n 19 ionice -c 3 borgmatic --log-file /var/log/borg.log --log-file-verbosity 2 -c /etc/borgmatic/config.yaml

May 01 11:53:03 lemp01 mysqld[7502]: 2020-05-01 11:53:03 357808 [Warning] Access denied for user 'backups'@'localhost' (using password: YES)

root@lemp01:[~]: mv /root/.my.cnf /tmp

root@lemp01:[/etc/cron.d]: cat borgbackup 
57 11 * * * root PATH=$PATH:/usr/local/bin nice -n 19 ionice -c 3 borgmatic --log-file /var/log/borg.log --log-file-verbosity 2 -c /etc/borgmatic/config.yaml

root@lemp01:[/etc/cron.d]: date && ls /tmp/borgmatic/mysql_databases/localhost/
Fri 01 May 2020 11:57:06 PM CEST
all

[2020-05-01 11:57:03,427] DEBUG: mysql --user backups --skip-column-names --batch --execute show schemas
[2020-05-01 11:57:03,464] DEBUG: /etc/borgmatic/config.yaml: Dumping MySQL database all to /tmp/borgmatic/mysql_databases/localhost/all
[2020-05-01 11:57:03,466] DEBUG: mysqldump --add-drop-database --user backups --single-transaction --quick --databases forum_gaming imperium_airsoft roundcube teampass_gaming > /tmp/borgmatic/mysql_databases/localhost/all
[...]
[2020-05-01 11:58:57,857] INFO: summary:
[2020-05-01 11:58:57,856] INFO: /etc/borgmatic/config.yaml: Successfully ran configuration file

I then tried to uncomment the password entry in .my.cnf and it indeed succeed. I now remember having the almost same issue with my old backup system using Backupmanager. But it was then using mysqldump utility and a .backup-manager_my.cnf file containing :

[mysqldump]
password=xxx

And it was using in its command line --defaults-extra-file=filename
And now i'm wondering if I could not try the same thing for borgmatic with one of thoses :

       •   --defaults-extra-file=filename

           Set filename as the file to read default options from after the global defaults files has been read.  Must be given as first option.

       •   --defaults-file=filename

           Set filename as the file to read default options from, override global defaults files. Must be given as first option.

but i'm not sure it would succeed as it is using the mysql utility, same utility described in my .my.cnf

Hello @witten, thanks for your time. I just made two tests to make my says valid : ``` root@lemp01:[/etc/cron.d]: cat /etc/cron.d/borgbackup 53 11 * * * root PATH=$PATH:/usr/local/bin nice -n 19 ionice -c 3 borgmatic --log-file /var/log/borg.log --log-file-verbosity 2 -c /etc/borgmatic/config.yaml May 01 11:53:03 lemp01 mysqld[7502]: 2020-05-01 11:53:03 357808 [Warning] Access denied for user 'backups'@'localhost' (using password: YES) ``` ``` root@lemp01:[~]: mv /root/.my.cnf /tmp root@lemp01:[/etc/cron.d]: cat borgbackup 57 11 * * * root PATH=$PATH:/usr/local/bin nice -n 19 ionice -c 3 borgmatic --log-file /var/log/borg.log --log-file-verbosity 2 -c /etc/borgmatic/config.yaml root@lemp01:[/etc/cron.d]: date && ls /tmp/borgmatic/mysql_databases/localhost/ Fri 01 May 2020 11:57:06 PM CEST all [2020-05-01 11:57:03,427] DEBUG: mysql --user backups --skip-column-names --batch --execute show schemas [2020-05-01 11:57:03,464] DEBUG: /etc/borgmatic/config.yaml: Dumping MySQL database all to /tmp/borgmatic/mysql_databases/localhost/all [2020-05-01 11:57:03,466] DEBUG: mysqldump --add-drop-database --user backups --single-transaction --quick --databases forum_gaming imperium_airsoft roundcube teampass_gaming > /tmp/borgmatic/mysql_databases/localhost/all [...] [2020-05-01 11:58:57,857] INFO: summary: [2020-05-01 11:58:57,856] INFO: /etc/borgmatic/config.yaml: Successfully ran configuration file ``` I then tried to uncomment the password entry in `.my.cnf` and it indeed succeed. I now remember having the almost same issue with my old backup system using Backupmanager. But it was then using `mysqldump` utility and a `.backup-manager_my.cnf` file containing : ``` [mysqldump] password=xxx ``` And it was using in its command line ```--defaults-extra-file=filename``` And now i'm wondering if I could not try the same thing for borgmatic with one of thoses : ``` • --defaults-extra-file=filename Set filename as the file to read default options from after the global defaults files has been read. Must be given as first option. • --defaults-file=filename Set filename as the file to read default options from, override global defaults files. Must be given as first option. ``` but i'm not sure it would succeed as it is using the mysql utility, same utility described in my `.my.cnf`

NoxInmortus commented

2020-05-01 10:22:14 +00:00

After 5 min of thoughts it will not be possible as the options of mysql_databases in borgmatic is used only for the mysql dump command line, and

Command '('mysql', '--user', 'backups', '--skip-column-names', '--batch', '--execute', 'show schemas')' returned non-zero exit status 1.

is not using the options of mysql_databases contained in borgmatic config.yaml

After 5 min of thoughts it will not be possible as the `options` of `mysql_databases` in borgmatic is used only for the mysql dump command line, and ``` Command '('mysql', '--user', 'backups', '--skip-column-names', '--batch', '--execute', 'show schemas')' returned non-zero exit status 1. ``` is not using the `options` of `mysql_databases` contained in borgmatic config.yaml

NoxInmortus commented

2020-05-05 10:21:13 +00:00

Hello,

I managed to bypass this issue with

borg_before_everything_commands:
- 'mv /root/.my.cnf /root/.my.cnf.bak'
borg_after_everything_commands:
- 'mv /root/.my.cnf.bak /root/.my.cnf'

but that's... pretty ugly.

Any more thoughts on that?

Hello, I managed to bypass this issue with ``` borg_before_everything_commands: - 'mv /root/.my.cnf /root/.my.cnf.bak' borg_after_everything_commands: - 'mv /root/.my.cnf.bak /root/.my.cnf' ``` but that's... pretty ugly. Any more thoughts on that?

witten commented

2020-05-26 16:44:48 +00:00

Sorry for the delay here. A couple of different ideas:

Add a separate MySQL list_options configuration option to support passing additional options to the mysql command used for listing databases. That would allow you to manually specify --defaults-file=whatever for both options and list_options.
Or, add a MySQL defaults_file option to the configuration, so you can just set a value other than .my.cnf, and borgmatic will take care of passing it to both mysql and mysqldump.

Have you confirmed that manually passing --defaults-file=... to mysql or mysqldump gets the desired behavior on your system?

Sorry for the delay here. A couple of different ideas: * Add a separate MySQL `list_options` configuration option to support passing additional options to the `mysql` command used for listing databases. That would allow you to manually specify `--defaults-file=whatever` for both `options` and `list_options`. * Or, add a MySQL `defaults_file` option to the configuration, so you can just set a value other than `.my.cnf`, and borgmatic will take care of passing it to both `mysql` and `mysqldump`. Have you confirmed that manually passing `--defaults-file=...` to `mysql` or `mysqldump` gets the desired behavior on your system?

👍 1

NoxInmortus commented

2020-05-28 16:35:49 +00:00

Hello @wittens, sorry for the delay as well. Here is the result :

root@gaming01:[/etc/mysql]: mysql --defaults-file=/etc/mysql/test.cnf --user backups --skip-column-names --batch --execute 'show schemas' 
bc_characters
bc_world
information_schema
maniacontrol
mysql
performance_schema
pterodactyl
realmd
vanilla_characters
vanilla_world

root@gaming01:[/etc/mysql]: mysql  --user backups --skip-column-names --batch --execute 'show schemas' 
ERROR 1045 (28000): Access denied for user 'backups'@'localhost' (using password: YES)

root@gaming01:[/etc/mysql]: cat test.cnf 
[mysql]
user=backups
password=xxxxxxxxxxxxxx

I think that :

Add a separate MySQL list_options configuration option to support passing additional options to the mysql command used for listing databases.

Would be indeed a great option. It would allow to list ANY additional parameters :))

Hello @wittens, sorry for the delay as well. Here is the result : ``` root@gaming01:[/etc/mysql]: mysql --defaults-file=/etc/mysql/test.cnf --user backups --skip-column-names --batch --execute 'show schemas' bc_characters bc_world information_schema maniacontrol mysql performance_schema pterodactyl realmd vanilla_characters vanilla_world root@gaming01:[/etc/mysql]: mysql --user backups --skip-column-names --batch --execute 'show schemas' ERROR 1045 (28000): Access denied for user 'backups'@'localhost' (using password: YES) root@gaming01:[/etc/mysql]: cat test.cnf [mysql] user=backups password=xxxxxxxxxxxxxx ``` I think that : ``` Add a separate MySQL list_options configuration option to support passing additional options to the mysql command used for listing databases. ``` Would be indeed a great option. It would allow to list ANY additional parameters :))

witten referenced this issue

2020-06-12 06:14:35 +00:00

MySQL backup for -name: all doesn't work but is ok for each database separately #325

witten commented

2020-06-17 21:23:15 +00:00

Sounds good. I'll rename this ticket accordingly!

witten changed title from ~~Required permissions for mysql dump ?~~ to Pass additional options to MySQL database list command

2020-06-17 21:24:32 +00:00

witten added

design finalized

and removed

question / support