Backing up the database

[Maxburn]

I have a list of instructions I use to create a backup and suddenly today the same command completes successfully but there's nothing in the file. Any ideas? Last backup was about 780M.

Command;

mysqldump --all-databases --single-transaction --quick --lock-tables=false > ~/backups/full-backup-$(date +%F).sql -u zabbix -p passwordhere

.sql file results in text;

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

MySQL data folder seems fine;

user@server:/var/lib/mysql# du -hs /var/lib/mysql
36G /var/lib/mysql

Zabbix server is 5.0.18 and is on Ubuntu 20.04.3 LTS with MySQL 8.0.27-0ubuntu0.20.04.1

I know, not strictly a Zabbix question, I will enquire elsewhere as well. This is also the same command I used to upgrade from Zabbix 4 to 5 on a new server.

[cyber]

That "Usage" line there usually indicates, that there is something wrong with the command you entered.
Are you sure username and passwd need to be after output redirection? IIRC they should before, so mysqldump would understand them.

[Maxburn]

That's a piece of it, Yes, I wasn't following my notes correctly. I was supposed to not enter the password there, and wait for it to prompt. So now entering it correctly gets me this error;

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

So looking around seems that's new in MySQL and I need to use "--no-tablespaces" to get around it. NOW my SQL dump file contents look like this but I still don't have any data;

user@computer:~/backups$ mysqldump --all-databases --no-tablespaces --single-transaction --quick --lock-tables=false > ~/backups/full-backup-$(date +%F).sql -u zabbix -p
Enter password:
user@computer:~/backups$ cat *.sql
-- MySQL dump 10.13 Distrib 8.0.27, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.27-0ubuntu0.20.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_ RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RE CALC */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-11-29 10:10:50

Edit; and directly answering you command like this below still gets the above result. Seems the order of the CLI options doesn't matter.

mysqldump --all-databases --no-tablespaces --single-transaction --quick --lock-tables=false -u zabbix -p > ~/backups/full-backup-$(date +%F)second.sql

[Maxburn]

Something is either broken or changed with mysqldump, the all-databases function doesn't work and fails silently. BEWARE; if your backup script completes suspiciously quickly and the output is really tiny you have a problem!

Today this works; mysqldump --no-tablespaces --single-transaction --quick -p -u zabbix zabbix > ~/backups/full-backup-$(date +%F).sql