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