Automysqlbackup "access denied" issue

Help
1

Hi,

I’m stuck with an issue and need some help.

I’ve an “Access denied” error with automysqlbackup

Dec 13 10:45:01 hydrogen automysqlbackup[166555]: # Parsing databases ... Note: Parsed config file /nix/store/0ihhpkncd5g4nzjzrf4bpjsx74zrpa7i-automysqlbackup.conf.
Dec 13 10:45:01 hydrogen automysqlbackup[166555]: Note: /etc/automysqlbackup/automysqlbackup.conf was not found - no global config file.
Dec 13 10:45:01 hydrogen automysqlbackup[166555]: Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.
Dec 13 10:45:01 hydrogen automysqlbackup[166522]: ###### WARNING ######
Dec 13 10:45:01 hydrogen automysqlbackup[166522]: Errors reported during AutoMySQLBackup execution.. Backup failed
Dec 13 10:45:01 hydrogen automysqlbackup[166522]: Error log below..
Dec 13 10:45:01 hydrogen automysqlbackup[166556]: ERROR 1698 (28000): Access denied for user 'automysqlbackup'@'localhost'

config file content in the nix store is

cat /nix/store/0ihhpkncd5g4nzjzrf4bpjsx74zrpa7i-automysqlbackup.conf
#version=3.0.7
# DONT'T REMOVE THE PREVIOUS VERSION LINE!
#
CONFIG_backup_dir='/var/backup/mysql'
CONFIG_db_exclude=( 'information_schema' 'performance_schema' )
CONFIG_db_names=( 'merlin' )
CONFIG_mailcontent='stdout'
CONFIG_mysql_dump_host='localhost'
CONFIG_mysql_dump_single_transaction='yes'
CONFIG_mysql_dump_socket='/run/mysqld/mysqld.sock'
CONFIG_mysql_dump_username='automysqlbackup'

merlin database exists

MariaDB [(none)]> use merlin;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [merlin]>

user automysqlbackup is configured with following privileges

MariaDB [(none)]> show grants for 'automysqlbackup'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for automysqlbackup@localhost                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO `automysqlbackup`@`localhost` IDENTIFIED VIA unix_socket |
| GRANT EXECUTE ON FUNCTION `sys`.`extract_schema_from_file_name` TO `automysqlbackup`@`localhost`                        |
| GRANT EXECUTE ON FUNCTION `sys`.`extract_table_from_file_name` TO `automysqlbackup`@`localhost`                         |
| GRANT EXECUTE ON FUNCTION `sys`.`format_path` TO `automysqlbackup`@`localhost`                                          |
| GRANT EXECUTE ON FUNCTION `sys`.`format_bytes` TO `automysqlbackup`@`localhost`                                         |
| GRANT EXECUTE ON FUNCTION `sys`.`ps_thread_account` TO `automysqlbackup`@`localhost`                                    |
| GRANT EXECUTE ON FUNCTION `sys`.`format_statement` TO `automysqlbackup`@`localhost`                                     |
| GRANT EXECUTE ON FUNCTION `sys`.`format_time` TO `automysqlbackup`@`localhost`                                          |
+-------------------------------------------------------------------------------------------------------------------------+

and socket file exists too

ll /run/mysqld/
total 0
srwxrwxrwx 1 mysql mysql 0 Dec 13 09:16 mysqld.sock

I even updated privileges for automysqlbackup to ALL PRIVILEGES ON *.* but the issue remains.

What I’m missing here ?

2

I have the exact same problem.

I can access the mysql server with this sudo -u automysqlbackup mysql
So access is working?
But why not in automysqlbackup?

3

I didn’t solve this issue.

I do my backups with restic and I do a mysqldump juste before.
I came up with the following configuration

    services = {
      mysql = {
        enable = true;
        package = pkgs.mariadb;
        settings = {
          "mysqld" = {
            "bind-address" = "127.0.0.1";
            "port" = 3306;
          };
        };
        ensureUsers = [{
          name = "restic";
          ensurePermissions = {
            "*.*" = "SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT";
            # https://forums.mysql.com/read.php?10,668311,668315#msg-668315
            "function sys.extract_table_from_file_name" = "execute";
            "function sys.format_path" = "execute";
            "function sys.format_statement" = "execute";
            "function sys.extract_schema_from_file_name" = "execute";
            "function sys.ps_thread_account" = "execute";
            "function sys.format_time" = "execute";
            "function sys.format_bytes" = "execute";
          };
        }];
      };
      restic = {
        backups = {
          mysql = {
            user = "restic";
            repository = "rclone:ovh:restic.${hostName}.${bucketHash}/mysql";
            initialize = true;
            passwordFile = config.age.secrets."restic.pwd".path;
            rcloneConfigFile = config.age.secrets."rclone.conf".path;
            paths = [ "${cfg.location}" ];
            backupPrepareCommand = ''
              for DB in $(${mariadb}/bin/mysql -e 'SHOW DATABASES WHERE `Database` NOT IN ("information_schema","sys","performance_schema","mysql")' -s --skip-column-names); do
                  dest="${cfg.location}/$DB-$(date +%Y-%m-%d-%H-%M-%S).gz"
                  if ${mariadb}/bin/mysqldump --single-transaction --routines --triggers $DB | ${gzip}/bin/gzip -c > $dest.tmp; then
                      mv $dest.tmp $dest
                  else
                      echo "Failed to back up to $dest"
                      rm -f $dest.tmp
                      failed="$failed $DB"
                  fi
              done
            '';

            backupCleanupCommand = ''
              find ${cfg.location} -type f -name '*.gz' -mmin +1440 -delete;
            '';
            pruneOpts = [ "--keep-last 7" ];
            timerConfig = {
              OnCalendar = "*-*-* *:00:00";
              Persistent = true;
              RandomizedDelaySec = "10min";
            };
          };
        };
      };
    };
4

Have you tried the mysqlBackup service? It seems to work for me without any additional configuration:

# Mysql/maridb backup
  services.mysqlBackup = {
    enable = true;
    location = "/var/backups";
    databases = [
      "nextcloud"
    ];
    singleTransaction = true;
    user = "root";
  };