--ssl-verify-server-cert

[74io]

Good evening

Firstly a huge thanks for this amazing product. I have been using it in production for over 10 years.

I have just installed version 1.2.10 on Ubuntu 22.04 and am trying to use mysqldump. The raw command:

mysqldump —host myhost.com --port 3306 --user abcd1234 -p --ssl-verify-server-cert --lock-tables --databases my_db > /home/ubuntu/my-db.sql

functions as expected so I know I can connect and use mysqldump.

However, sudo holland bk produces the error:
MySQL Error (1045, "Access denied for user 'abcd1234'@'10.186.18.12' (using password: YES)")

All credentials in /etc/holland/backupsets/default.conf are correct. I have
additional-options = "--ssl-verify-server-cert" in my default.conf file but I don't believe it is being used. I have tried to set level = debug in my holland.conf but no details of the commands being executed are output. Even sudo holland -d bk does not provide any output on the command being executed by mysqldump.

Is there possibly an issue with additional-options in 1.2.10? What is the best way to troubleshoot this?

Thanks in advance.

[74io]

The issue here is the conflict between ssl_verify_cert=TRUE which is required by pymysql to create an SSL connection and mysqldump which errors if ssl_verify_cert=TRUE is set.

In plugin.py here:

holland/plugins/holland.backup.mysqldump/holland/backup/mysqldump/plugin.py

Line 129 in ab9fd2c

self.client.connect()

Holland tries to establish a normal mysql client connection in order to fetch schema metadata etc. It uses the pymysql lib which for SSL connections needs ssl_verify_cert=TRUE. This setting must be set in the .my.cnf file. Adding it to e.g. /etc/holland/backupsets/default.conf does not work and results in an error.

Now if you add ssl_verify_cert=TRUE to your .my.cnf file if will be read by mysqldump which results in the error:
mysqldump: [ERROR] unknown variable 'ssl_verify_cert=True'."

So the only option in my case was to remove the ssl_verify_cert from the self.mysql_config["client"] array before it ran mysqldump. self.mysql_config["client"].pop("ssl_verify_cert", None) was added below: (hack I know)

holland/plugins/holland.backup.mysqldump/holland/backup/mysqldump/plugin.py

Line 167 in ab9fd2c

"""Run a MySQL backup"""

def backup(self):
        """Run a MySQL backup"""
        self.mysql_config["client"].pop("ssl_verify_cert", None)
        if self.schema.timestamp is None:
            self._fast_refresh_schema()

The script then executed fine. In my case I was using the maridb-dump client which accepts the --ssl-verify-server-cert option. I added this to /etc/holland/backupsets/default.conf additional_options = "--ssl-verify-server-cert" and it worked fine. If using mysqldump you may have to add your flavour of --ssl-mode, --ssl-ca etc. However, with some DB cloud providers no longer providing physical certificates (e.g. SkySQL) --ssl-verify-server-cert is the only way to go.

I am not sure if this can be resolved in Holland moving forward?