MySQL dump - password not found / invalid #399

New Issue

sylvainmetayer · 2021-03-18T08:08:38Z

sylvainmetayer commented

2021-03-18 08:08:38 +00:00

What I'm trying to do and why

First, I just discovered Borg and Borgmatic, thanks for this, it looks really great ! I'm using Hetzner as backup storage for my raspberry, but it seems their is a bug with mysqldump step.

Steps to reproduce (if a bug)

Create a backup with the following configuration

location:
    source_directories:
        - /home/

    repositories:
        - XXX@XXX.your-storagebox.de:xxx

    # Stay in same file system (do not cross mount points).
    # Defaults to false. But when a database hook is used, the
    # setting here is ignored and one_file_system is considered
    # true.
    # one_file_system: true

    exclude_caches: true
    exclude_if_present: .nobackup

storage:
    encryption_passphrase: xxx

    # Type of compression to use when creating archives. See
    # http://borgbackup.readthedocs.io/en/stable/usage/create.html
    # for details. Defaults to "lz4".
    # compression: lz4

    ssh_command: ssh -p 23

    # archive_name_format: '{hostname}-documents-{now}'
    relocated_repo_access_is_ok: true

    # Bypass Borg error about a previously unknown unencrypted
    # repository. Defaults to false.
    # unknown_unencrypted_repo_access_is_ok: true

retention:
    keep_daily: 7
    keep_weekly: 2

consistency:
    checks:
        - repository
        - data
        - extract
    check_last: 2

hooks:
    mysql_databases:
        - name: xxx
          hostname: localhost
          username: xxx
          password: securePassword
          options: "--skip-comments --order-by-primary"
    healthchecks: https://hc-ping.com/xxx

Actual behavior (if a bug)

Include (sanitized) --verbosity 2 output if applicable.


🕙[ 08:50:48 ] ❯ sudo borgmatic --verbosity 2 --files
Ensuring legacy configuration is upgraded
/etc/borgmatic/config.yaml: No commands to run for pre-everything hook
/etc/borgmatic/config.yaml: Calling healthchecks hook function initialize_monitor
/etc/borgmatic/config.yaml: No commands to run for pre-prune hook
/etc/borgmatic/config.yaml: No commands to run for pre-backup hook
/etc/borgmatic/config.yaml: No commands to run for pre-check hook
/etc/borgmatic/config.yaml: Calling healthchecks hook function ping_monitor
/etc/borgmatic/config.yaml: Pinging Healthchecks start
/etc/borgmatic/config.yaml: Using Healthchecks ping URL https://hc-ping.com/xxx/start
xxx@xxx.your-storagebox.de:xxx: Pruning archives
borg prune --keep-daily 7 --keep-weekly 2 --prefix {hostname}- --list --debug --show-rc xxx@xxx.your-storagebox.de:xxx
using builtin fallback logging configuration
35 self tests completed in 0.53 seconds
SSH command line: ['ssh', '-p', '23', 'xxx@xxx.your-storagebox.de', 'borg', 'serve', '--umask=077', '--debug']
TAM-verified manifest
Killed stale lock xxx@242606368388023.9051-0.
Removed stale exclusive roster lock for host xxx@242606368388023 pid 9051 thread 0.
Removed stale exclusive roster lock for host xxx@242606368388023 pid 9051 thread 0.
security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx'
security: read manifest timestamp '2021-03-17T22:06:55.638931'
security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931
security: repository checks ok, allowing access
Verified integrity of /root/.cache/borg/fc6b9cba62def71de7ff5ba6f303fa044b6fa5568fecff3fd159857122a55d08/chunks
security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx'
security: read manifest timestamp '2021-03-17T22:06:55.638931'
security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931
security: repository checks ok, allowing access
Keeping archive: xxx-2021-03-17T23:06:36.001184 Wed, 2021-03-17 23:06:38 [677e033624483a0cfd93964a781873bd25d406303c6bb4be08877f4fc5b97f7b]
RemoteRepository: 218 B bytes sent, 1.61 kB bytes received, 5 messages sent
terminating with success status, rc 0
xxx@xxx.your-storagebox.de:xxx: Creating archive
xxx@xxx.your-storagebox.de:xxx: Calling mysql_databases hook function remove_database_dumps
xxx@xxx.your-storagebox.de:xxx: Removing MySQL database dumps
xxx@xxx.your-storagebox.de:xxx: Calling mysql_databases hook function dump_databases
xxx@xxx.your-storagebox.de:xxx: Dumping MySQL databases
xxx@xxx.your-storagebox.de:xxx: Dumping MySQL database xxx to /root/.borgmatic/mysql_databases/localhost/xxx
mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx > /root/.borgmatic/mysql_databases/localhost/xxx
borg create --exclude-caches --exclude-if-present .nobackup --one-file-system --read-special --list --filter AME- --debug --show-rc xxx@xxx.your-storagebox.de:xxx::{hostname}-{now:%Y-%m-%dT%H:%M:%S.%f} /home/xxx/data /root/.borgmatic
using builtin fallback logging configuration
35 self tests completed in 0.53 seconds
SSH command line: ['ssh', '-p', '23', 'xxx@xxx.your-storagebox.de', 'borg', 'serve', '--umask=077', '--debug']
TAM-verified manifest
security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx'
security: read manifest timestamp '2021-03-17T22:06:55.638931'
security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931
security: repository checks ok, allowing access
Creating archive at "xxx@xxx.your-storagebox.de:xxx::{hostname}-{now:%Y-%m-%dT%H:%M:%S.%f}"
Verified integrity of /root/.cache/borg/fc6b9cba62def71de7ff5ba6f303fa044b6fa5568fecff3fd159857122a55d08/chunks
Reading files cache ...
Verified integrity of /root/.cache/borg/fc6b9cba62def71de7ff5ba6f303fa044b6fa5568fecff3fd159857122a55d08/files
security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx'
security: read manifest timestamp '2021-03-17T22:06:55.638931'
security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931
security: repository checks ok, allowing access
Processing files ...
---[backup file]---
mysqldump: Got error: 1045: "Access denied for user 'xxx'@'localhost' (using password: YES)" when trying to connect
xxx@xxx.your-storagebox.de:xxx: Error running actions for repository
Command 'mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx > /root/.borgmatic/mysql_databases/localhost/xxx' returned non-zero exit status 2.
/etc/borgmatic/config.yaml: No commands to run for on-error hook
/etc/borgmatic/config.yaml: Calling healthchecks hook function ping_monitor
/etc/borgmatic/config.yaml: Pinging Healthchecks fail
/etc/borgmatic/config.yaml: Using Healthchecks ping URL https://hc-ping.com/xxx/fail
/etc/borgmatic/config.yaml: Calling healthchecks hook function destroy_monitor
/etc/borgmatic/config.yaml: Error running configuration file
/etc/borgmatic/config.yaml: No commands to run for post-everything hook

summary:
/etc/borgmatic/config.yaml: Error running configuration file
xxx@xxx.your-storagebox.de:xxx: Error running actions for repository
mysqldump: Got error: 1045: "Access denied for user 'xxx'@'localhost' (using password: YES)" when trying to connect
Command 'mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx > /root/.borgmatic/mysql_databases/localhost/xxx' returned non-zero exit status 2.

Need some help? https://torsion.org/borgmatic/#issues

Expected behavior (if a bug)

As mysql password is given in configuration, I expect it to backup my database.

Other notes / implementation ideas

I excluded the bad credential password option when looking how borgmatic create MySQL dumps. It seems it use the MYSQL_PWD environment variable. So I tested :

MYSQL_PWD=securePassword mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx

and this works on my shell. Am I missing an option, or is this a bug in borgmatic/borg ?

For what it worth, my password does not contains spaces/accentuations, only characters and numbers.

As a workaround, I can still use --skip-comments --order-by-primary -psecurePassword and it works. From my understanding, it seems that the MYSQL_PWD variable is not defined/invalid.

MySQL user permissions :

MariaDB [(none)]> SHOW GRANTS for 'xxx';
+----------------------------------------------------------------------------------------------------+
| Grants for xxx@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `xxx`@`%` IDENTIFIED BY PASSWORD 'XXX' |
| GRANT ALL PRIVILEGES ON `xxx`.* TO `xxx`@`%`                                                       |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Environment

borgmatic version: [version here]

Use sudo borgmatic --version or sudo pip show borgmatic | grep ^Version

🕙[ 09:04:16 ] ❯ sudo borgmatic --version
1.5.12

borgmatic installation method: [e.g., Debian package, Docker container, etc.]

installed with pip3

Borg version: [version here]

Use sudo borg --version

🕙[ 09:04:18 ] ❯ sudo borg --version
borg 1.1.15

Python version: [version here]

Use python3 --version

🕙[ 09:04:43 ] ❯ python3 --version
Python 3.7.3

Database version (if applicable): [version here]

Use psql --version or mysql --version on client and server.

🕙[ 09:04:55 ] ❯ mysql --version
mysql  Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

operating system and version: [OS here]

Raspberry pi OS on a raspberry pi 4

🕙[ 09:05:22 ] ❯ uname -a
Linux raspberrypi 5.10.17-v7l+ #1403 SMP Mon Feb 22 11:33:35 GMT 2021 armv7l GNU/Linux

#### What I'm trying to do and why First, I just discovered Borg and Borgmatic, thanks for this, it looks really great ! I'm using Hetzner as backup storage for my raspberry, but it seems their is a bug with mysqldump step. #### Steps to reproduce (if a bug) Create a backup with the following configuration ``` location: source_directories: - /home/ repositories: - XXX@XXX.your-storagebox.de:xxx # Stay in same file system (do not cross mount points). # Defaults to false. But when a database hook is used, the # setting here is ignored and one_file_system is considered # true. # one_file_system: true exclude_caches: true exclude_if_present: .nobackup storage: encryption_passphrase: xxx # Type of compression to use when creating archives. See # http://borgbackup.readthedocs.io/en/stable/usage/create.html # for details. Defaults to "lz4". # compression: lz4 ssh_command: ssh -p 23 # archive_name_format: '{hostname}-documents-{now}' relocated_repo_access_is_ok: true # Bypass Borg error about a previously unknown unencrypted # repository. Defaults to false. # unknown_unencrypted_repo_access_is_ok: true retention: keep_daily: 7 keep_weekly: 2 consistency: checks: - repository - data - extract check_last: 2 hooks: mysql_databases: - name: xxx hostname: localhost username: xxx password: securePassword options: "--skip-comments --order-by-primary" healthchecks: https://hc-ping.com/xxx ``` #### Actual behavior (if a bug) Include (sanitized) `--verbosity 2` output if applicable. ``` 🕙[ 08:50:48 ] ❯ sudo borgmatic --verbosity 2 --files Ensuring legacy configuration is upgraded /etc/borgmatic/config.yaml: No commands to run for pre-everything hook /etc/borgmatic/config.yaml: Calling healthchecks hook function initialize_monitor /etc/borgmatic/config.yaml: No commands to run for pre-prune hook /etc/borgmatic/config.yaml: No commands to run for pre-backup hook /etc/borgmatic/config.yaml: No commands to run for pre-check hook /etc/borgmatic/config.yaml: Calling healthchecks hook function ping_monitor /etc/borgmatic/config.yaml: Pinging Healthchecks start /etc/borgmatic/config.yaml: Using Healthchecks ping URL https://hc-ping.com/xxx/start xxx@xxx.your-storagebox.de:xxx: Pruning archives borg prune --keep-daily 7 --keep-weekly 2 --prefix {hostname}- --list --debug --show-rc xxx@xxx.your-storagebox.de:xxx using builtin fallback logging configuration 35 self tests completed in 0.53 seconds SSH command line: ['ssh', '-p', '23', 'xxx@xxx.your-storagebox.de', 'borg', 'serve', '--umask=077', '--debug'] TAM-verified manifest Killed stale lock xxx@242606368388023.9051-0. Removed stale exclusive roster lock for host xxx@242606368388023 pid 9051 thread 0. Removed stale exclusive roster lock for host xxx@242606368388023 pid 9051 thread 0. security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx' security: read manifest timestamp '2021-03-17T22:06:55.638931' security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931 security: repository checks ok, allowing access Verified integrity of /root/.cache/borg/fc6b9cba62def71de7ff5ba6f303fa044b6fa5568fecff3fd159857122a55d08/chunks security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx' security: read manifest timestamp '2021-03-17T22:06:55.638931' security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931 security: repository checks ok, allowing access Keeping archive: xxx-2021-03-17T23:06:36.001184 Wed, 2021-03-17 23:06:38 [677e033624483a0cfd93964a781873bd25d406303c6bb4be08877f4fc5b97f7b] RemoteRepository: 218 B bytes sent, 1.61 kB bytes received, 5 messages sent terminating with success status, rc 0 xxx@xxx.your-storagebox.de:xxx: Creating archive xxx@xxx.your-storagebox.de:xxx: Calling mysql_databases hook function remove_database_dumps xxx@xxx.your-storagebox.de:xxx: Removing MySQL database dumps xxx@xxx.your-storagebox.de:xxx: Calling mysql_databases hook function dump_databases xxx@xxx.your-storagebox.de:xxx: Dumping MySQL databases xxx@xxx.your-storagebox.de:xxx: Dumping MySQL database xxx to /root/.borgmatic/mysql_databases/localhost/xxx mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx > /root/.borgmatic/mysql_databases/localhost/xxx borg create --exclude-caches --exclude-if-present .nobackup --one-file-system --read-special --list --filter AME- --debug --show-rc xxx@xxx.your-storagebox.de:xxx::{hostname}-{now:%Y-%m-%dT%H:%M:%S.%f} /home/xxx/data /root/.borgmatic using builtin fallback logging configuration 35 self tests completed in 0.53 seconds SSH command line: ['ssh', '-p', '23', 'xxx@xxx.your-storagebox.de', 'borg', 'serve', '--umask=077', '--debug'] TAM-verified manifest security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx' security: read manifest timestamp '2021-03-17T22:06:55.638931' security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931 security: repository checks ok, allowing access Creating archive at "xxx@xxx.your-storagebox.de:xxx::{hostname}-{now:%Y-%m-%dT%H:%M:%S.%f}" Verified integrity of /root/.cache/borg/fc6b9cba62def71de7ff5ba6f303fa044b6fa5568fecff3fd159857122a55d08/chunks Reading files cache ... Verified integrity of /root/.cache/borg/fc6b9cba62def71de7ff5ba6f303fa044b6fa5568fecff3fd159857122a55d08/files security: read previous location 'ssh://xxx@xxx.your-storagebox.de/./xxx' security: read manifest timestamp '2021-03-17T22:06:55.638931' security: determined newest manifest timestamp as 2021-03-17T22:06:55.638931 security: repository checks ok, allowing access Processing files ... ---[backup file]--- mysqldump: Got error: 1045: "Access denied for user 'xxx'@'localhost' (using password: YES)" when trying to connect xxx@xxx.your-storagebox.de:xxx: Error running actions for repository Command 'mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx > /root/.borgmatic/mysql_databases/localhost/xxx' returned non-zero exit status 2. /etc/borgmatic/config.yaml: No commands to run for on-error hook /etc/borgmatic/config.yaml: Calling healthchecks hook function ping_monitor /etc/borgmatic/config.yaml: Pinging Healthchecks fail /etc/borgmatic/config.yaml: Using Healthchecks ping URL https://hc-ping.com/xxx/fail /etc/borgmatic/config.yaml: Calling healthchecks hook function destroy_monitor /etc/borgmatic/config.yaml: Error running configuration file /etc/borgmatic/config.yaml: No commands to run for post-everything hook summary: /etc/borgmatic/config.yaml: Error running configuration file xxx@xxx.your-storagebox.de:xxx: Error running actions for repository mysqldump: Got error: 1045: "Access denied for user 'xxx'@'localhost' (using password: YES)" when trying to connect Command 'mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx > /root/.borgmatic/mysql_databases/localhost/xxx' returned non-zero exit status 2. Need some help? https://torsion.org/borgmatic/#issues ``` #### Expected behavior (if a bug) As mysql password is given in configuration, I expect it to backup my database. #### Other notes / implementation ideas I excluded the bad credential password option when looking how borgmatic create MySQL dumps. It seems it use the `MYSQL_PWD` environment variable. So I tested : ``` MYSQL_PWD=securePassword mysqldump --add-drop-database --host localhost --protocol tcp --user xxx --skip-comments --order-by-primary --databases xxx ``` and this works on my shell. Am I missing an option, or is this a bug in borgmatic/borg ? For what it worth, my password does *not* contains spaces/accentuations, only characters and numbers. As a workaround, I can still use `--skip-comments --order-by-primary -psecurePassword` and it works. From my understanding, it seems that the MYSQL_PWD variable is not defined/invalid. MySQL user permissions : ``` MariaDB [(none)]> SHOW GRANTS for 'xxx'; +----------------------------------------------------------------------------------------------------+ | Grants for xxx@% | +----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `xxx`@`%` IDENTIFIED BY PASSWORD 'XXX' | | GRANT ALL PRIVILEGES ON `xxx`.* TO `xxx`@`%` | +----------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) ``` #### Environment **borgmatic version:** [version here] Use `sudo borgmatic --version` or `sudo pip show borgmatic | grep ^Version` ``` 🕙[ 09:04:16 ] ❯ sudo borgmatic --version 1.5.12 ``` **borgmatic installation method:** [e.g., Debian package, Docker container, etc.] installed with pip3 **Borg version:** [version here] Use `sudo borg --version` ``` 🕙[ 09:04:18 ] ❯ sudo borg --version borg 1.1.15 ``` **Python version:** [version here] Use `python3 --version` ``` 🕙[ 09:04:43 ] ❯ python3 --version Python 3.7.3 ``` **Database version (if applicable):** [version here] Use `psql --version` or `mysql --version` on client and server. ``` 🕙[ 09:04:55 ] ❯ mysql --version mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2 ``` **operating system and version:** [OS here] Raspberry pi OS on a raspberry pi 4 ``` 🕙[ 09:05:22 ] ❯ uname -a Linux raspberrypi 5.10.17-v7l+ #1403 SMP Mon Feb 22 11:33:35 GMT 2021 armv7l GNU/Linux ```

djdembeck commented

2022-03-10 20:50:35 +00:00

I have the exact issue as well with this mysql hook.

witten commented

2022-03-10 22:09:07 +00:00

@djdembeck, what version of borgmatic are you using? There was a MySQL-specific fix (#470) in borgmatic 1.5.22 that may be relevant here.

witten added the

bug

label 2022-03-10 22:09:19 +00:00

fschrempf referenced this issue

2022-03-12 13:04:28 +00:00

Database backups fail and cause stalling in borg #509

witten added the

waiting for response

label 2022-03-14 20:52:05 +00:00

witten commented

2022-04-28 23:37:26 +00:00

I'm closing this due to inactivity. However, please feel free to re-open it or file a new ticket if you have more information. Thanks!

witten closed this issue

2022-04-28 23:37:26 +00:00

witten removed the

waiting for response

label 2022-04-28 23:37:36 +00:00

schwarmco commented

2023-02-07 13:00:04 +00:00

i stumbled across this issue as i experienced the exact same problem. i finally figured the reason for this to happen:

When you run sudo borgmatic and have a /root/.my.cnf with credentials in place, the password defined in /etc/borgmatic/config.yaml gets overwritten - but not the user.

For example having a /root/.my.cnf with

[client]
user=root
password=root

and a borgmatic-conf with:

[...]
hooks:
    mysql_databases:
        - name: all
          username: borg
          password: borg

will result in a mysql --user borg but with password root

$ borg --version
borg 1.2.0

$ borgmatic --version
1.7.6

$ mysql --version
mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

i stumbled across this issue as i experienced the exact same problem. i finally figured the reason for this to happen: When you run `sudo borgmatic` and have a `/root/.my.cnf` with credentials in place, the password defined in /etc/borgmatic/config.yaml gets overwritten - but not the user. For example having a /root/.my.cnf with ``` [client] user=root password=root ``` and a borgmatic-conf with: ``` [...] hooks: mysql_databases: - name: all username: borg password: borg ``` will result in a `mysql --user borg` but with password `root` ```console $ borg --version borg 1.2.0 $ borgmatic --version 1.7.6 $ mysql --version mysql Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu)) ```

witten commented

2023-02-08 17:10:14 +00:00

Ah, excellent sleuthing! I'll reopen this to see if there's anything that can be done about it on the borgmatic side of things.

Edit: This may be relevant for mysql password setting / defaults file overriding.. https://unix.stackexchange.com/questions/205180/how-to-pass-password-to-mysql-command-line

Ah, excellent sleuthing! I'll reopen this to see if there's anything that can be done about it on the borgmatic side of things. Edit: This may be relevant for `mysql` password setting / defaults file overriding.. https://unix.stackexchange.com/questions/205180/how-to-pass-password-to-mysql-command-line

witten reopened this issue

2023-02-08 17:10:14 +00:00

witten added the

good first issue

label 2023-03-27 19:01:19 +00:00

witten referenced this issue from a commit

2023-06-15 21:56:10 +00:00

Add a documentation troubleshooting note for MySQL/MariaDB authentication errors (#399).

witten commented

2023-06-15 21:58:40 +00:00

I ended up just documenting around this for now. While I did consider passing --no-defaults to MySQL/MariaDB to make it ignore files like ~/.my.cnf, in my experience it often breaks things for users relying on that kind of configuration. If anything, the trend in borgmatic is adding more ways to pass in credentials rather than taking them away.

I ended up just documenting around this for now. While I did consider passing `--no-defaults` to MySQL/MariaDB to make it ignore files like `~/.my.cnf`, in my experience it often breaks things for users relying on that kind of configuration. If anything, the trend in borgmatic is adding _more_ ways to pass in credentials rather than taking them away.

witten closed this issue

2023-06-15 21:58:40 +00:00

Sign in to join this conversation.