CODEWITHSUNDEEP
mysql
Hank Lapidez
Hank Lapidez

Reputation: 2025

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

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?

Upvotes: 4

Views: 1573

Answers (4)

Illya Moskvin
Illya Moskvin

Reputation: 342

After MySQL 5.7+, you can use mysqlpump. In many cases, it's a drop-in replacement for mysqldump. It allows you to specify --include-databases and --exclude-databases. You can add these options to your .cnf file under the [mysqlpump] section:

[mysqlpump]
include-databases = "mydb1,mydb2"

See mysqlpump object selection docs for more info.

Upvotes: 1

d g
d g

Reputation: 1604

Can you source it? Something like:

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

Upvotes: 1

Tarun Lalwani
Tarun Lalwani

Reputation: 146510

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

Upvotes: 4

huwence
huwence

Reputation: 326

Try this:

mysqldump --user='{{USER}}' --password='{{PASSWORD}}' -h {{HOST}} {{DATABASE}} {{TABLE_NAME}} > database.sql

Upvotes: -1

Related Questions