How can I specify the database I want to dump in defaults-file

Question

I want do configure a mysqldump command entirely through the defauts-file, but it seems not to be possibly to specify the database in the file.

I tried

databases=mydb
databases mydb

databases="mydb"
databases "mydb"

I always get the message

mysqldump: ignoring option '--databases' due to invalid value 'mydb'

On the commandline I only get this message, when I use the following command:

 mysqldump: --databases=mydb

Only this works

 mysqldump .... --databases mydb

works.

Must the databasename passed on the commandline?

Answer 1

If you run --help you get

$ mysqldump --help

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

...
databases                         FALSE
...

So if you see the --databases is a boolean flag, so in the config if you specify just databases then it would mean the flag is TRUE. Now the database name is always a positional argument and needs to be given at the command itself.

We do some workaround to overcome that by using a bash script

#!/bin/bash
database=$(mysqldump --print-defaults | tr ' ' '\n' | awk -F'=' '/databases/ {print $2}')
mysqldump $@ $database

What this does is that it prints the default config, which doesn't get validate so you can provide invalid options also. And then extract the database using the same from the config and then pass the command from terminal. Now if I do

$  ./mysqldump.sh
Warning: mysqldump: ignoring option '--databases' due to invalid value 'testing'
-- MySQL dump 10.13  Distrib 5.6.40, for Linux (x86_64)
--
-- Host: localhost    Database: testing
-- ------------------------------------------------------
-- Server version   5.6.40

/*!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 */;

It works. We can us a different name instead of databases as well. But the workaround I should doesn't anyway support multiple databases, but it os not trivial to update it to let it do so

Answer 2

Can you source it? Something like:

source <PATH_TO_DEFAULTS_FILE> && mysqldump -u $user -p $pass $databases ...