-1

I want to create a cron script to interact with mysql, for example

#!/bin/bash

mysql -uroot -p
echo root
echo "CREATE DATABASE example"

But it doesn't work, it only prompts:

Enter password:

and when I exit mysql it shows

root
"CREATE DATABASE example"

Any idea?

Anthon
  • 79,293
oootramas
  • 11

5 Answers5

7

Put something like:

[client]
user=root
password="my-very-secret-password"

In a file whose permissions ensure that nobody outside the people who are entitled to read it can read it.

And run:

#! /bin/sh -
mysql --defaults-extra-file=/path/to/that/file --batch << "EOF"
CREATE DATABASE example
EOF

See MySQL's own guideline itself for more information.

You could put the password in the script and restrict read access to the script itself, but you'd also need to make sure that the password is not passed as argument to any command as that would then make it visible to anybody in the output of ps.

You could do something like:

#! /bin/sh -
mysql --defaults-extra-file=/dev/fd/3 --batch 3<< "END_OF_AUTH" << "END_OF_SQL"
[client]
user=root
password="my-very-secret-password"
END_OF_AUTH
CREATE DATABASE example
END_OF_SQL
Stéphane Chazelas
  • 544,893
  • Great, it works perfectly. But maybe you can complete the answer with some explanation. At first I didn't understand I need to files. Thanks – oootramas Oct 05 '17 at 13:38
1

This is similar to Stéphane Chazelas' answer, but uses process substitution and a shell function instead of a heredoc to provide the user & password:

#!/bin/bash

printconf() {
cat <<-EOF
    [mysql]
    user=root
    password=supersecretpassword
EOF
}

mysql --defaults-extra-file=<(printconf) -e 'CREATE DATABASE example'

The function (printconf) just outputs a correctly formatted mysql conf file.

IMO, this is more readable than having multiple heredocs on the one line.

It still has the user & password details embedded in the script (so doesn't require an external file like ~/.my.cnf) and still avoids exposing the password in the kernel's process table (i.e. via ps, pgrep, etc).

NOTE: This requires a modern shell that supports process substitution (e.g. bash, zsh, or ksh).

The script contains a plain text password so should be adequately protected by ownership, group, permissions, and or ACLs. i.e. at the very least, it should not be world-readable.

cas
  • 78,579
0

You should be able to execute the sql statement with:

mysql -u root -p<password> -e "CREATE DATABASE example"

Note there is no space between -p and the password

Raman Sailopal
  • 1,432
0

You can use echo to achieve this, but you need to pipe the output to your command.

echo -e "root\nCREATE DATABASE example" | mysql -uroot -p

The pipe operator (|) sends the output of the first command (echo) to the next. The echo uses -e to interpret special characters, namely newlines (\n), which indicates when one response ends and is the equivalent of pressing <ENTER>.

Centimane
  • 4,490
0

Similar to Stéphane Chazelas answer, you can create a .my.cnf inside the home of the user you're using to execute the script, add your credentials and just execute your script.

~/.my.cnf

[client]
user=root
password="This15MyPa55word"

script

#!/bin/bash
mysql -e "CREATE DATABASE example"

From man mysql

--execute=statement, -e statement

      Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using
      Options on the Command Line”, for some examples. With this option, mysql does not use the history file.
tachomi
  • 7,592