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:
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:
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:
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:
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
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
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