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

New Issue

Auroch · 2020-06-11T12:15:24Z

Auroch commented

2020-06-11 12:15:24 +00:00

What I'm trying to do and why

Dump Mysql databases as i do for postgreSQL, using -name: all

Steps to reproduce (if a bug)

    - name: all
      hostname: localhost
      port: 3306
      username: root
      password: "<PASSWORD>"
      options: "--single-transaction --quick"

Produce this error :

#> borgmatic --verbosity 1 --files
...
ssh://user@borgServer/PATH: Dumping PostgreSQL databases
ssh://user@borgServer/PATH: Dumping PostgreSQL database all to /tmp/borgmatic/postgresql_databases/localhost/all
pg_dumpall --no-password --clean --if-exists > /tmp/borgmatic/postgresql_databases/localhost/all
ssh://user@borgServer/PATH: Calling mysql_databases hook function dump_databases
ssh://user@borgServer/PATH: Dumping MySQL databases
ssh://user@borgServer/PATH: Querying for "all" MySQL databases to dump
mysql --host localhost --port 3306 --protocol tcp --user root --skip-column-names --batch --execute show schemas
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
ssh://user@borgServer/PATH: Error running actions for repository
Command '('mysql', '--host', 'localhost', '--port', '3306', '--protocol', 'tcp', '--user', 'root', '--skip-column-names', '--batch', '--execute', 'show schemas')' returned non-zero exit status 1
...

But with this config style where i put all databases but each configured :

        - name: firstdatabase
          hostname: localhost
          port: 3306
          username: root
          password: "<PASSWORD>"
          options: "--single-transaction --quick"
        - name: seconddatabase
          hostname: localhost
          port: 3306
          username: root
          password: "<PASSWORD>"
          options: "--single-transaction --quick"
        ...

Produce no error :

ssh://user@borgServer/PATH: Pruning archives
ssh://user@borgServer/PATH: Creating archive
ssh://user@borgServer/PATH: Removing PostgreSQL database dumps
ssh://user@borgServer/PATH: Removing MySQL database dumps
ssh://user@borgServer/PATH: Dumping PostgreSQL databases
ssh://user@borgServer/PATH: Dumping MySQL databases
Creating archive at "ssh://user@borgServer/PATH::{hostname}-{now}"
A /etc/

Other notes / implementation ideas

The SQL error is the same as if I try to connect in command line without giving password. Is it possible that borgmatic not give the password with "all" ?

Environmentif

borgmatic version: [1.5.6]

borgmatic installation method: [pip3]

borg installation method: [debian stretch]

Borg version: [1.1.9]

Python version: [3.5.3]

Database version (if applicable): [Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2]

operating system and version: [Debian 9.12]

#### What I'm trying to do and why Dump Mysql databases as i do for postgreSQL, using -name: all #### Steps to reproduce (if a bug) - name: all hostname: localhost port: 3306 username: root password: "<PASSWORD>" options: "--single-transaction --quick" ##### Produce this error : ``` #> borgmatic --verbosity 1 --files ... ssh://user@borgServer/PATH: Dumping PostgreSQL databases ssh://user@borgServer/PATH: Dumping PostgreSQL database all to /tmp/borgmatic/postgresql_databases/localhost/all pg_dumpall --no-password --clean --if-exists > /tmp/borgmatic/postgresql_databases/localhost/all ssh://user@borgServer/PATH: Calling mysql_databases hook function dump_databases ssh://user@borgServer/PATH: Dumping MySQL databases ssh://user@borgServer/PATH: Querying for "all" MySQL databases to dump mysql --host localhost --port 3306 --protocol tcp --user root --skip-column-names --batch --execute show schemas ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ssh://user@borgServer/PATH: Error running actions for repository Command '('mysql', '--host', 'localhost', '--port', '3306', '--protocol', 'tcp', '--user', 'root', '--skip-column-names', '--batch', '--execute', 'show schemas')' returned non-zero exit status 1 ... ``` ##### But with this config style where i put all databases but each configured : ``` - name: firstdatabase hostname: localhost port: 3306 username: root password: "<PASSWORD>" options: "--single-transaction --quick" - name: seconddatabase hostname: localhost port: 3306 username: root password: "<PASSWORD>" options: "--single-transaction --quick" ... ``` ##### Produce no error : ``` ssh://user@borgServer/PATH: Pruning archives ssh://user@borgServer/PATH: Creating archive ssh://user@borgServer/PATH: Removing PostgreSQL database dumps ssh://user@borgServer/PATH: Removing MySQL database dumps ssh://user@borgServer/PATH: Dumping PostgreSQL databases ssh://user@borgServer/PATH: Dumping MySQL databases Creating archive at "ssh://user@borgServer/PATH::{hostname}-{now}" A /etc/ ``` #### Other notes / implementation ideas The SQL error is the same as if I try to connect in command line without giving password. Is it possible that borgmatic not give the password with "all" ? #### Environmentif **borgmatic version:** [1.5.6] **borgmatic installation method:** [pip3] **borg installation method:** [debian stretch] **Borg version:** [1.1.9] **Python version:** [3.5.3] **Database version (if applicable):** [Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2] **operating system and version:** [Debian 9.12]

witten added the

bug

label 2020-06-11 17:39:14 +00:00

witten commented

2020-06-11 17:53:29 +00:00

Thank you for filing this one! The main difference between borgmatic's MySQL hook for "all" vs. a single database is that "all" causes borgmatic to run mysql ... --execute 'show schemas' in order to list each database to dump. Based on your output, that looks like the command that's failing due to Access denied.

I've confirmed on my system that the password environment variable is properly passed through to this show schemas command. In fact, if I change my "all" configuration to use the wrong password, I get Access denied... while it works fine with the right password.

So assuming you've got the right password there, then my next guess is that it's a MySQL user-level permissions issue.

Are you able to connect to the database and check out the contents of the mysql.user table?

Example:

MYSQL_PWD=yourpass mysql --host localhost --port 3306 --protocol tcp --user root
...
> select User, Select_priv, Execute_priv from mysql.user;

On my machine, I get results something like this:

+------+-------------+--------------+
| User | Select_priv | Execute_priv |
+------+-------------+--------------+
| root | Y           | Y            |
| root | Y           | Y            |
+------+-------------+--------------+
2 rows in set (0.002 sec)

Not sure why root is there twice. But in any case, the user clearly has execute privilige, needed for show schemas to work. So does your root user have that permission enabled?

Thank you for filing this one! The main difference between borgmatic's MySQL hook for "all" vs. a single database is that "all" causes borgmatic to run `mysql ... --execute 'show schemas'` in order to list each database to dump. Based on your output, that looks like the command that's failing due to `Access denied`. I've confirmed on my system that the password environment variable is properly passed through to this `show schemas` command. In fact, if I change my "all" configuration to use the wrong password, I get `Access denied`... while it works fine with the right password. So assuming you've got the right password there, then my next guess is that it's a MySQL user-level permissions issue. Are you able to connect to the database and check out the contents of the [`mysql.user` table](https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql)? Example: ```bash MYSQL_PWD=yourpass mysql --host localhost --port 3306 --protocol tcp --user root ... > select User, Select_priv, Execute_priv from mysql.user; ``` On my machine, I get results something like this: ```text +------+-------------+--------------+ | User | Select_priv | Execute_priv | +------+-------------+--------------+ | root | Y | Y | | root | Y | Y | +------+-------------+--------------+ 2 rows in set (0.002 sec) ``` Not sure why `root` is there twice. But in any case, the user clearly has execute privilige, needed for `show schemas` to work. So does your `root` user have that permission enabled?

Auroch commented

2020-06-11 20:58:53 +00:00

Thank you very much for your quick response and the time spent testing and thinking.
It's a backup for a family system, so it's not very important or urgent, but I try to contribute by bringing up the info :-)

SO Yes for me the permissions are there :

MariaDB [(none)]> select User, Host, Select_priv, Execute_priv from mysql.user;
+-------------------+-----------+-------------+--------------+
| User              | Host      | Select_priv | Execute_priv |
+-------------------+-----------+-------------+--------------+
| root              | localhost | Y           | Y            |
| xxxxxxxxxxxx      | localhost | N           | N            |
| yyyyyyyyyyyyyyyyy | localhost | N           | N            |
| zzzzzzzzz         | localhost | N           | N            |
| aaaaaaaaaaaaa     | localhost | N           | N            |
| bbbb              | localhost | N           | N            |
| cccccc            | localhost | N           | N            |
| dddddddddddd      | localhost | N           | N            |
+-------------------+-----------+-------------+--------------+
8 rows in set (0.00 sec)

(Perhaps your root is there with two different hosts ?)

and if i try

mysql -u root -p --execute 'show schemas'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| xxxxxxxxxxxx       |
| yyyyyyyyyyyyyyyyy  |
| zzzzzzzzz          |
| aaaaaaaaaaaaa      |
| bbbb               |
| cccccc             |
| dddddddddddd       |
+--------------------+

The result is ok

witten commented

2020-06-11 21:12:05 +00:00

That's super odd! Have you tried the exact command that borgmatic is invoking to see whether that works?

MYSQL_PWD=yourpass mysql --host localhost --port 3306 --protocol tcp --user root --skip-column-names
    --batch --execute 'show schemas'

(Substituting yourpass of course.)

That's super odd! Have you tried the exact command that borgmatic is invoking to see whether that works? ```bash MYSQL_PWD=yourpass mysql --host localhost --port 3306 --protocol tcp --user root --skip-column-names --batch --execute 'show schemas' ``` (Substituting `yourpass` of course.)

Auroch commented

2020-06-12 05:39:04 +00:00

Indeed, after having searched quite a bit before posting my problem here, I only acted like a sheep following your request and without thinking :-)

So I executed the full command and there, ... it doesn't work.
(THe same one with -p, ask password and work)

After some research on the Internet, I came across a Virtualmin thread : https://www.virtualmin.com/node/56959

I usually pass the parameters in /etc/mysql/mariadb.conf.d/... but, and I don't know why, the /root/.my.cnf is completed with a previous password (probably set by the mysql_secure_installation script for mysql ?).
This attribute has precedence over command line environnement variable like MYSQL_PWD, so the password was systematicaly false.

And thus, it's not a bug, it's just a feature and a PEBCAK. I'm Sorry.
Thank you for your time.

Indeed, after having searched quite a bit before posting my problem here, I only acted like a sheep following your request and without thinking :-) So I executed the full command and there, ... it doesn't work. (THe same one with -p, ask password and work) After some research on the Internet, I came across a Virtualmin thread : https://www.virtualmin.com/node/56959 I usually pass the parameters in /etc/mysql/mariadb.conf.d/... but, and I don't know why, the /root/.my.cnf is completed with a previous password (probably set by the mysql_secure_installation script for mysql ?). This attribute has precedence over command line environnement variable like MYSQL_PWD, so the password was systematicaly false. And thus, it's not a bug, it's just a feature and a PEBCAK. I'm Sorry. Thank you for your time.

witten commented

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

No worries! I'm glad you figured it out.. You may be interested in this related ticket, which includes discussions of work-arounds for that same issue: #306

witten closed this issue

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

witten added

question / support

and removed

bug

labels 2020-06-12 06:14:46 +00:00

Sign in to join this conversation.