search

holland-backup / holland

Holland Backup Manager
http://hollandbackup.org
Other
154 stars 47 forks source link

--ssl-verify-server-cert #376

Open 74io opened 2 months ago

74io commented 2 months ago

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 commented 2 months ago

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: https://github.com/holland-backup/holland/blob/ab9fd2cc1eabdf26833eec5ebe4dd9beff2ef42b/plugins/holland.backup.mysqldump/holland/backup/mysqldump/plugin.py#L129

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) https://github.com/holland-backup/holland/blob/ab9fd2cc1eabdf26833eec5ebe4dd9beff2ef42b/plugins/holland.backup.mysqldump/holland/backup/mysqldump/plugin.py#L167

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?