mysqladmin to set initial root password fails in script but works in shell

Question

I am trying to do this as part of a startup script (that runs as root) on an EC2 instance

# mysqladmin -u root password mygreatnewpassword

To debug this I echoed the entire command to the log and it's correct. I did a 'ps ax' before the command in the script to be sure mysql was running. I tried it with double and single quotes around the password (shouldn't have mattered and didn't). No matter what I try, in the script it gets error

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

but when I copy and run the exact same line from the script in a shell it works. And I've verified that there is no root password before the command runs (in either case).

It can't be that I need to use -p, even though that's what the error seems like, because I don't need that when I run it from a shell.

I'm baffled.

And before anyone comments that this is insecure, it's not. The password is stored on a private EBS volume that's mounted as part of the startup and pulled out of a file on that volume and put into the startup shell's environment and then used in the script (and not echoed into syslog). Then the EBS volume is unmounted. Once that startup script ends that password is nowhere except inside mysql.

Update: I just tried this instead (found in the 5.1 MYSQL manual in 2.12.2

mysql -u root <<EOF
UPDATE mysql.user SET Password = PASSWORD("$MYSQL_PWD") WHERE User = 'root';
FLUSH PRIVILEGES;
EOF

Didn't work either, similar error and running

mysql -u root

from a shell gets me connected just fine (and I can enter the UPDATE and FLUSH cmds) w/o error.

I'm starting to think that mysql and mysqladmin are doing something funky like reading from a tty that is causing them to fail when run from within shell scripts. That will make it difficult to have my startup script initialize things.

Sorry if I wasn't clear, there is no root password, this is just after the script installs mysql. I am trying to set the initial root password with mysqladmin. The format of the command above is correct for setting the root password when none exists, and, that command works when typed into a shell but not when run from the startup script. — JK Scheinberg, Apr 08 '12 at 00:19

Richard Harrison · Answer 1 · 2012-04-08T13:29:59.540

0

Referring to mysql_secure_installation (/usr/bin on my install) it sets the password in both the DB and a temporary mysql.cnf file: the following are extracts from the file to give you and idea of what it's doing.

#!/bin/ksh
config=".my.cnf.$$"
command=".mysql.$$"
do_query() {
    echo $1 >$command
    mysql --defaults-file=$config <$command
    return $?
}

make_config() {
    echo "# mysql_secure_installation config file" >$config
    echo "[mysql]" >>$config
    echo "user=root" >>$config
    echo "password=$rootpass" >>$config
}

rootpass="mynewpassword"
do_query "UPDATE mysql.user SET Password=PASSWORD('$rootpass') WHERE User='root';"
do_query "FLUSH PRIVILEGES;"
rm -f $config $command

edited Apr 08 '12 at 13:29

answered Apr 07 '12 at 23:58

Richard Harrison

19,247
4
40
67

I was asking how to set the initial root password after a fresh install (when there is no root password) from my install script. What you posted just does a ping command authenticating with root/secretpassword. – JK Scheinberg Apr 08 '12 at 00:14
sorry I didn't understand that from the original question; I've added an extract taken from the mysql supplied files that should help to fix this. – Richard Harrison Apr 08 '12 at 13:30
thanks for the help, still didn't work. I even simplified it. It runs from command line but still fails in a script. I'm giving up and assuming I can't do it ( http://cl.ly/2j352L2P041M0f3H470W ) – JK Scheinberg Apr 09 '12 at 00:10
it must be scriptable because mysql_secure_installation does this. To me the error message you're seeing looks like you the mysql command cannot connect because the password isn't being passed in. When you run this script has the password already been set? – Richard Harrison Apr 10 '12 at 08:16
no, right after the script fails i can still type just 'mysql' and get connected and a prompt. – JK Scheinberg Apr 10 '12 at 11:50
your script file isn't setting the 'password=' line. if you add this does it work? – Richard Harrison Apr 10 '12 at 15:18
nope. I tried that. since there is no password it also fails. – JK Scheinberg Apr 10 '12 at 18:26

score 0 · Answer 2 · answered Apr 09 '12 at 00:11

0

It doesn't look like this is possible. mysql and mysqladmin are not fully scriptable.

answered Apr 09 '12 at 00:11

JK Scheinberg

11
1
5

score 0 · Answer 3 · answered Oct 24 '14 at 02:32

Ok. I was just setting up mysql on my mac and following the instructions on setting the mysql root password for the first time using the the mysqladmin command like the owner of this question did. Basically the situation is exactly as described here.

So actually mysql accepts both the new password after running the command and no password when trying to log in as root. This happens because there are multiple users named root.

+------+-------------------+
| User | Host              |
+------+-------------------+
| root | 127.0.0.1         |
| root | ::1               |
|      | localhost         |
| root | localhost         |
|      | macbook-pro.local |
| root | macbook-pro.local |
+------+-------------------+

Therefore I feel the need to necro this question..

mysqladmin to set initial root password fails in script but works in shell

3 Answers3