DBRep-MySQL Backup funktioniert nicht

[Homalix99]

Hallo,

ich betreibe fhem und DB (mariaDB) in unterschiedlichen Docker-Containern und wollte das DB Backup gemäß des Wiki-Beitrags
(https://wiki.fhem.de/wiki/DbRep_-_Reporting_und_Management_von_DbLog-Datenbankinhalten#Backup.2FRestore_einer_MySQL.2FMariaDB_Datenbank_im_laufenden_Betrieb) automatisieren.
Dabei soll das Backup als "serverSite" Backup laufen.
Das Backup wird aber mit Fehler abgebrochen(Can't create/write to file '/fhem-docker/mariadb/DB_dumps/fhem_history_2022_11_29_19_13.csv' (Errcode: 2 "No such file or directory") at ./FHEM/93_DbRep.pm line 10796.):
Log:

Code Auswählen Erweitern


2022.11.29 19:13:32.012 3: DbRep DB_dump - ################################################################
2022.11.29 19:13:32.013 3: DbRep DB_dump - ###             New database serverSide dump                 ###
2022.11.29 19:13:32.013 3: DbRep DB_dump - ################################################################
2022.11.29 19:13:32.013 3: DbRep DB_dump - execute command before dump: 'set logDB reopen 3600'
2022.11.29 19:13:32.015 2: DbLog logDB: Connection closed until 20:13:32 (3600 seconds).
2022.11.29 19:13:32.244 4: DbRep DB_dump - Database connect - user: fhemuser, UTF-8 option set: yes
2022.11.29 19:13:32.249 3: DbRep DB_dump - Searching for tables inside database fhem....
2022.11.29 19:13:32.250 4: DbRep DB_dump - SQL execute: SHOW TABLE STATUS FROM `fhem`
2022.11.29 19:13:32.252 5: DbRep DB_dump - ......... Table definition found: .........
2022.11.29 19:13:32.253 5: DbRep DB_dump - Avg_row_length: 420
2022.11.29 19:13:32.253 5: DbRep DB_dump - Collation: utf8mb4_general_ci
2022.11.29 19:13:32.253 5: DbRep DB_dump - Comment:
2022.11.29 19:13:32.253 5: DbRep DB_dump - Create_options:
2022.11.29 19:13:32.253 5: DbRep DB_dump - Create_time: 2022-11-28 20:28:40
2022.11.29 19:13:32.253 5: DbRep DB_dump - Data_free: 0
2022.11.29 19:13:32.253 5: DbRep DB_dump - Data_length: 16384
2022.11.29 19:13:32.253 5: DbRep DB_dump - Engine: InnoDB
2022.11.29 19:13:32.253 5: DbRep DB_dump - Index_length: 0
2022.11.29 19:13:32.253 5: DbRep DB_dump - Max_data_length: 0
2022.11.29 19:13:32.253 5: DbRep DB_dump - Max_index_length: 0
2022.11.29 19:13:32.253 5: DbRep DB_dump - Name: current
2022.11.29 19:13:32.253 5: DbRep DB_dump - Row_format: Dynamic
2022.11.29 19:13:32.253 5: DbRep DB_dump - Temporary: N
2022.11.29 19:13:32.253 5: DbRep DB_dump - Update_time: 2022-11-29 19:13:28
2022.11.29 19:13:32.253 5: DbRep DB_dump - Version: 10
2022.11.29 19:13:32.254 5: DbRep DB_dump - ......... Table definition END ............
2022.11.29 19:13:32.254 5: DbRep DB_dump - ......... Table definition found: .........
2022.11.29 19:13:32.254 5: DbRep DB_dump - Avg_row_length: 98
2022.11.29 19:13:32.254 5: DbRep DB_dump - Collation: utf8mb4_general_ci
2022.11.29 19:13:32.254 5: DbRep DB_dump - Comment:
2022.11.29 19:13:32.254 5: DbRep DB_dump - Create_options:
2022.11.29 19:13:32.254 5: DbRep DB_dump - Create_time: 2022-11-28 20:28:42
2022.11.29 19:13:32.254 5: DbRep DB_dump - Data_free: 4194304
2022.11.29 19:13:32.254 5: DbRep DB_dump - Data_length: 18366464
2022.11.29 19:13:32.254 5: DbRep DB_dump - Engine: InnoDB
2022.11.29 19:13:32.254 5: DbRep DB_dump - Index_length: 10027008
2022.11.29 19:13:32.254 5: DbRep DB_dump - Max_data_length: 0
2022.11.29 19:13:32.254 5: DbRep DB_dump - Max_index_length: 0
2022.11.29 19:13:32.254 5: DbRep DB_dump - Name: history
2022.11.29 19:13:32.255 5: DbRep DB_dump - Row_format: Dynamic
2022.11.29 19:13:32.255 5: DbRep DB_dump - Temporary: N
2022.11.29 19:13:32.255 5: DbRep DB_dump - Update_time: 2022-11-29 19:13:28
2022.11.29 19:13:32.255 5: DbRep DB_dump - Version: 10
2022.11.29 19:13:32.255 5: DbRep DB_dump - ......... Table definition END ............
2022.11.29 19:13:32.255 4: DbRep DB_dump - SQL execute: SELECT table_name, round (data_length / 1024 / 1024, 2) "data size in MB", round (index_length / 1024 / 1024, 2) "index size in MB", round (data_free / 1024 / 1024, 2) "free space in MB" FROM information_schema.TABLES where table_schema = 'fhem';
2022.11.29 19:13:32.257 4: DbRep DB_dump - SQL execute: SELECT round ((COUNT(*) * 300 * 1024)/1048576 + 150, 2) "overhead in MB" FROM information_schema.TABLES where table_schema = 'fhem';
2022.11.29 19:13:32.259 3: DbRep DB_dump - Estimate of fhem before optimize (MB): Data size: 17.54, Index size: 9.56, Space free: 4, Overhead: 150.59
2022.11.29 19:13:32.259 3: DbRep DB_dump - Optimizing tables
2022.11.29 19:13:32.259 3: DbRep DB_dump - Optimizing table `current` (INNODB). It may take a while ...
2022.11.29 19:13:32.259 4: DbRep DB_dump - SQL execute: OPTIMIZE TABLE `current`
2022.11.29 19:13:32.288 3: DbRep DB_dump - Table 1 `current` optimized successfully.
2022.11.29 19:13:32.288 3: DbRep DB_dump - Optimizing table `history` (INNODB). It may take a while ...
2022.11.29 19:13:32.288 4: DbRep DB_dump - SQL execute: OPTIMIZE TABLE `history`
2022.11.29 19:13:34.593 3: DbRep DB_dump - Table 2 `history` optimized successfully.
2022.11.29 19:13:34.593 3: DbRep DB_dump - 2 tables have been optimized.
2022.11.29 19:13:34.594 4: DbRep DB_dump - SQL execute: SELECT table_name, round (data_length / 1024 / 1024, 2) "data size in MB", round (index_length / 1024 / 1024, 2) "index size in MB", round (data_free / 1024 / 1024, 2) "free space in MB" FROM information_schema.TABLES where table_schema = 'fhem';
2022.11.29 19:13:34.596 4: DbRep DB_dump - SQL execute: SELECT round ((COUNT(*) * 300 * 1024)/1048576 + 150, 2) "overhead in MB" FROM information_schema.TABLES where table_schema = 'fhem';
2022.11.29 19:13:34.598 3: DbRep DB_dump - Estimate of fhem after optimize (MB): Data size: 18.57, Index size: 8.55, Space free: 5, Overhead: 150.59
2022.11.29 19:13:34.598 3: DbRep DB_dump - Starting dump of database 'fhem', table 'history'
2022.11.29 19:13:34.598 5: DbRep DB_dump - Use Outfile: /fhem-docker/mariadb/DB_dumps/fhem_history_2022_11_29_19_13.csv
2022.11.29 19:13:34.598 4: DbRep DB_dump - SQL execute: SELECT * FROM history INTO OUTFILE '/fhem-docker/mariadb/DB_dumps/fhem_history_2022_11_29_19_13.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
';
2022.11.29 19:13:34.600 2: DbRep DB_dump - ERROR - DBD::mysql::st execute failed: Can't create/write to file '/fhem-docker/mariadb/DB_dumps/fhem_history_2022_11_29_19_13.csv' (Errcode: 2 "No such file or directory") at ./FHEM/93_DbRep.pm line 10796.

2022.11.29 19:13:34.608 4: DbRep DB_dump - execute command after dump: 'set logDB reopen'
2022.11.29 19:13:34.610 3: DbLog logDB: Reopen requested.
2022.11.29 19:13:34.610 3: DbLog logDB - Creating Push-Handle to database mysql:database=fhem;host=192.168.3.20;port=3306 with user fhemuser
2022.11.29 19:13:34.614 3: DbLog logDB - Push-Handle to db mysql:database=fhem;host=192.168.3.20;port=3306 created
2022.11.29 19:13:34.615 3: DbLog logDB - UTF8 support enabled
2022.11.29 19:13:34.616 2: DbRep DB_dump - command message after dump: "Reopen executed."


List des DB_dump Objekts:

Code Auswählen Erweitern


Internals:
   DATABASE   fhem
   DEF        logDB
   FUUID      6384c184-f33f-6b52-8abf-b92b46000736f142
   FVERSION   93_DbRep.pm:v8.50.3-s26429/2022-09-19
   LASTCMD    sqlCmd show grants;
   MODEL      Client
   NAME       DB_dump
   NOTIFYDEV  global,DB_dump
   NR         2017
   NTFY_ORDER 50-DB_dump
   ROLE       Client
   STATE      WARNING - sqlCmd finished, but message after command appeared
   TYPE       DbRep
   UTF8       1
   eventCount 36
   HELPER:
     DBLOGDEVICE logDB
     GRANTS     USAGE,ALL PRIVILEGES
     IDRETRIES  2
     MINTS      2022-11-21 23:02:05
     PACKAGE    main
     VERSION    8.50.3
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      512
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   OLDREADINGS:
   READINGS:
     2022-11-29 19:33:31   SqlResultRow_1  GRANTS FOR ROOT@%
     2022-11-29 19:33:31   SqlResultRow_2  GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*462366917EEDD1970A48E87D8EF59EB67D2CA26F' WITH GRANT OPTION
     2022-11-29 19:33:31   after_sqlCmd_message Reopen executed.
     2022-11-29 19:33:31   background_processing_time 0.0084
     2022-11-29 19:33:31   sqlCmd          show grants;
     2022-11-29 19:33:31   sqlResultNumRows 1
     2022-11-29 19:33:31   sql_processing_time 0.0014
     2022-11-29 19:33:31   state           WARNING - sqlCmd finished, but message after command appeared
Attributes:
   DbLogExclude .*
   devStateIcon initialized:control_3dot_hor_s
connected:10px-kreis-gelb
.*disconnect:10px-kreis-grau
.*done:10px-kreis-gruen
.*error:10px-kreis-rot
   dumpDirLocal /fhem-docker/mariadb/DB_dumps
   dumpDirRemote /fhem-docker/mariadb/DB_dumps
   executeAfterProc set logDB reopen
   executeBeforeProc set logDB reopen 3600
   optimizeTablesBeforeDump 1
   room       Datenbank
   showproctime 1
   useAdminCredentials 1
   verbose    5


Docker-compose Def:

Code Auswählen Erweitern


mysql:
        image: mariadb
        restart: unless-stopped
        container_name: mysql
        environment:
            PUID: 1000
            PGID: 1000
            MYSQL_ROOT_PASSWORD: '*********
            MYSQL_DATABASE: fhem
            MYSQL_USER: fhemuser
            MYSQL_PASSWORD: *******
            TZ: Europe/Berlin

        volumes:
            - ./mariadb/fhem-init.sql:/docker-entrypoint-initdb.d/fhem-init.sql
            - ./mariadb/data:/var/lib/mysql
            - ./mariadb/log:/var/log
            - /fhem-docker/mariaDB/DB_dumps:/var/backups
            - ./mariadb/mycustom.cnf:/etc/mysql/conf.d/custom.cnf

        ports:
            - 3306:3306
            - 33060:33060
        networks:
            - fhem-network


Ein set DB_dump sqlCmd show grants;
zeigt nichts an, ausser "WARNING - sqlCmd finished, but message after command appeared",.
aber in der DB selbst:

Code Auswählen Erweitern


MariaDB [mysql]> use fhem;
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 [fhem]> SHOW GRANTS FOR 'fhemuser';
+--------------------------------------------------------------------------------------------------------+
| Grants for fhemuser@%                                                                                  |
+--------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO `fhemuser`@`%` IDENTIFIED BY PASSWORD '*A9DD226C578D0B4039CBAABD517786FA01EB49DD' |
| GRANT ALL PRIVILEGES ON `fhem`.* TO `fhemuser`@`%`                                                     |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)


Ich steh da gerade "auf dem Schlauch", vielleicht hat jemand eine Idee, viel schein es nicht zu sein.

Gruß

Alex

[DS_Starter]

Moin Alex,

ich habe eine Vermutung.
Das Zielverzeichnis wird mit

dumpDirRemote    : das Erstellungsverzeichnis des Dumpfile auf dem entfernten Server

festgelegt ujnd ist bei dir:

dumpDirRemote    /fhem-docker/mariadb/DB_dumps


Das angegebene Verzeichnis muss lokal vom MariaDB-Server erreichbar sein.
Wenn ich die Docker-Angaben richtig deute müsste das Attribut auf "/var/backups" gestellt werden da dieses Verzeichnis das lokale Volume ist auf das /fhem-docker/mariadb/DB_dumps vom Docker-Host gemappt wurde.

Grüße,
Heiko

[Homalix99]

Hallo Heiko,

super, es funktioniert. Vielen Dank.
Das mit dem Dump-Verzeichnis aus remote Sicht hatte ich nicht bedacht, eigentlich klar, da die DB ja nur ihre Verzeichnisse innerhalb ihres Containers sehen kann.

VG

Alex