Mysql and root password

Chef Infra (archive)
1

Confused how I should be handling the root password with the mysql recipe.

After it installed, I couldn’t access the mysql instance since I didn’t
know the password.

  1. I stopped the mysql service.
  2. mysqld_safe --skip-grant-tables &
  3. mysql -u root
    changed password for root
  4. tried to stop the mysql server:

sudo /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql

How can I stop it? (I got around it by killing the process via kill -9
1234)

  1. started the service up again, my root password now works.

***But now, if I re-run my chef-solo script, I get this:

[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Running exception handlers
[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Exception handlers complete
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL: Stacktrace dumped to
/home/ubuntu/chef/chef-stacktrace.out
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL:
Chef::Exceptions::ShellCommandFailed: execute[mysql-install-privileges]
(mysql::server line 136) had an error:
Chef::Exceptions::ShellCommandFailed: Expected process to exit with [0],
but received ‘1’
---- Begin output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
STDOUT:
STDERR: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’
(using password: YES)
---- End output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
Ran /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" < /etc/mysql/grants.sql
returned 1

I guess it is generating a random password and trying to login somehow,
what should I change now?

2

chef solo has nowhere to store the password so it just randomly generates one.

You can hard coded it with an attribute, or use chef-server then the
credentials will be saved between runs.

--AJ

On 16 April 2012 14:47, S Ahmed sahmed1020@gmail.com wrote:

Confused how I should be handling the root password with the mysql recipe.

After it installed, I couldn't access the mysql instance since I didn't know
the password.

  1. I stopped the mysql service.
  2. mysqld_safe --skip-grant-tables &
  3. mysql -u root
    changed password for root
  4. tried to stop the mysql server:

sudo /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql

How can I stop it? (I got around it by killing the process via kill -9
1234)

  1. started the service up again, my root password now works.

***But now, if I re-run my chef-solo script, I get this:

[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Running exception handlers
[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Exception handlers complete
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL: Stacktrace dumped to
/home/ubuntu/chef/chef-stacktrace.out
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL:
Chef::Exceptions::ShellCommandFailed: execute[mysql-install-privileges]
(mysql::server line 136) had an error: Chef::Exceptions::ShellCommandFailed:
Expected process to exit with [0], but received '1'
---- Begin output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
STDOUT:
STDERR: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
---- End output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
Ran /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" < /etc/mysql/grants.sql
returned 1

I guess it is generating a random password and trying to login somehow, what
should I change now?

3

I am having the same problem with the mysql plugin.
Per the readme file

  • mysql['server_root_password'] - Set the server's root password
    with this, default is a randomly generated password with
    OpenSSL::Random.random_bytes.

I set default['mysql']['server_root_password']='test123' attributes
file and will not work on ubuntu. Anyway to get tat aspect to work?

On Mon, Apr 16, 2012 at 11:04 AM, AJ Christensen aj@junglist.gen.nz wrote:

chef solo has nowhere to store the password so it just randomly generates one.

You can hard coded it with an attribute, or use chef-server then the
credentials will be saved between runs.

--AJ

On 16 April 2012 14:47, S Ahmed sahmed1020@gmail.com wrote:

Confused how I should be handling the root password with the mysql recipe.

After it installed, I couldn't access the mysql instance since I didn't know
the password.

  1. I stopped the mysql service.
  2. mysqld_safe --skip-grant-tables &
  3. mysql -u root
    changed password for root
  4. tried to stop the mysql server:

sudo /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql

How can I stop it? (I got around it by killing the process via kill -9
1234)

  1. started the service up again, my root password now works.

***But now, if I re-run my chef-solo script, I get this:

[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Running exception handlers
[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Exception handlers complete
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL: Stacktrace dumped to
/home/ubuntu/chef/chef-stacktrace.out
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL:
Chef::Exceptions::ShellCommandFailed: execute[mysql-install-privileges]
(mysql::server line 136) had an error: Chef::Exceptions::ShellCommandFailed:
Expected process to exit with [0], but received '1'
---- Begin output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
STDOUT:
STDERR: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
---- End output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
Ran /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" < /etc/mysql/grants.sql
returned 1

I guess it is generating a random password and trying to login somehow, what
should I change now?

4

The answer is in the very log file you quoted:
The cookbook generated the password and then stored it in a /etc/mysql/grants.sql file. Do not change it; just read it from there and use that one.

On Apr 16, 2012, at 4:47 AM, S Ahmed sahmed1020@gmail.com wrote:

Confused how I should be handling the root password with the mysql recipe.

After it installed, I couldn't access the mysql instance since I didn't know the password.

  1. I stopped the mysql service.
  2. mysqld_safe --skip-grant-tables &
  3. mysql -u root
    changed password for root
  4. tried to stop the mysql server:

sudo /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql

How can I stop it? (I got around it by killing the process via kill -9 1234)

  1. started the service up again, my root password now works.

***But now, if I re-run my chef-solo script, I get this:

[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Running exception handlers
[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Exception handlers complete
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL: Stacktrace dumped to /home/ubuntu/chef/chef-stacktrace.out
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL: Chef::Exceptions::ShellCommandFailed: execute[mysql-install-privileges] (mysql::server line 136) had an error: Chef::Exceptions::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" < /etc/mysql/grants.sql ----
STDOUT:
STDERR: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
---- End output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" < /etc/mysql/grants.sql ----
Ran /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" < /etc/mysql/grants.sql returned 1

I guess it is generating a random password and trying to login somehow, what should I change now?

5

Andrea,

Thanks for clearing that up, I see the root password now :slight_smile:

Question is, how can I read it from that file, do you mean this could be
automated somehow?

I don't plan on remote connecting to my mysql server anyhow, so was hoping
there was a way I could hard code the password. David says he tried and it
didn't work, do you konw what the issue might be?

thanks!

On Mon, Apr 16, 2012 at 2:51 AM, Andrea Campi
andrea.campi@zephirworks.comwrote:

The answer is in the very log file you quoted:
The cookbook generated the password and then stored it in a
/etc/mysql/grants.sql file. Do not change it; just read it from there and
use that one.

On Apr 16, 2012, at 4:47 AM, S Ahmed sahmed1020@gmail.com wrote:

Confused how I should be handling the root password with the mysql
recipe.

After it installed, I couldn't access the mysql instance since I didn't
know the password.

  1. I stopped the mysql service.
  2. mysqld_safe --skip-grant-tables &
  3. mysql -u root
    changed password for root
  4. tried to stop the mysql server:

sudo /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql

How can I stop it? (I got around it by killing the process via kill -9

  1. started the service up again, my root password now works.

***But now, if I re-run my chef-solo script, I get this:

[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Running exception handlers
[Mon, 16 Apr 2012 02:43:09 +0000] ERROR: Exception handlers complete
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL: Stacktrace dumped to
/home/ubuntu/chef/chef-stacktrace.out
[Mon, 16 Apr 2012 02:43:09 +0000] FATAL:
Chef::Exceptions::ShellCommandFailed: execute[mysql-install-privileges]
(mysql::server line 136) had an error:
Chef::Exceptions::ShellCommandFailed: Expected process to exit with [0],
but received '1'
---- Begin output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
STDOUT:
STDERR: ERROR 1045 (28000): Access denied for user 'root'@'localhost'
(using password: YES)
---- End output of /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql ----
Ran /usr/bin/mysql -u root -p"QNX7GkHcx7cbU65BYxGb" <
/etc/mysql/grants.sql returned 1

I guess it is generating a random password and trying to login somehow,
what should I change now?

6

On Mon, Apr 16, 2012 at 1:42 AM, David Montgomery
davidmontgomery@gmail.com wrote:

I set default['mysql']['server_root_password']='test123' attributes
file and will not work on ubuntu. Anyway to get tat aspect to work?

That's hard to say without knowing what your environment is. Please
provide information regarding what is unique about your system. What
version of Ubuntu? What version of the MySQL cookbook? Was this a
fresh system, or did you already have the mysql-server package
installed? What version of Chef?

Bryan

7

Brian,

I just tested it and it worked for me fine (setting the root password).
I'm on ubuntu 10.10, running chef 10.8 on a fresh server with the mysql
cookbook from opscode.

On Mon, Apr 16, 2012 at 10:19 AM, Bryan McLellan btm@loftninjas.org wrote:

On Mon, Apr 16, 2012 at 1:42 AM, David Montgomery
davidmontgomery@gmail.com wrote:

I set default['mysql']['server_root_password']='test123' attributes
file and will not work on ubuntu. Anyway to get tat aspect to work?

That's hard to say without knowing what your environment is. Please
provide information regarding what is unique about your system. What
version of Ubuntu? What version of the MySQL cookbook? Was this a
fresh system, or did you already have the mysql-server package
installed? What version of Chef?

Bryan

8

BUT, I just tried adding the default in my role like:

name 'webserver'
description 'web server role for serving http requests'
run_list "recipe[nginx]" , "recipe[redis]", "recipe[mysql::server]",
"recipe[mysql::client]", "recipe[mongodb]"

default_attributes "mysql" => { "server_root_password" => "123" }

And it didn't pickup the password and I get the same error as before.

It worked when I added the default in the mysql cookbook attribute file,
i.e.:

default['mysql']['server_root_password'] = '123'

I'm probably doing somethign wrong though :slight_smile:

On Mon, Apr 16, 2012 at 10:36 AM, S Ahmed sahmed1020@gmail.com wrote:

Brian,

I just tested it and it worked for me fine (setting the root password).
I'm on ubuntu 10.10, running chef 10.8 on a fresh server with the mysql
cookbook from opscode.

On Mon, Apr 16, 2012 at 10:19 AM, Bryan McLellan btm@loftninjas.orgwrote:

On Mon, Apr 16, 2012 at 1:42 AM, David Montgomery
davidmontgomery@gmail.com wrote:

I set default['mysql']['server_root_password']='test123' attributes
file and will not work on ubuntu. Anyway to get tat aspect to work?

That's hard to say without knowing what your environment is. Please
provide information regarding what is unique about your system. What
version of Ubuntu? What version of the MySQL cookbook? Was this a
fresh system, or did you already have the mysql-server package
installed? What version of Chef?

Bryan

9

On Mon, Apr 16, 2012 at 10:36 AM, S Ahmed sahmed1020@gmail.com wrote:

I just tested it and it worked for me fine (setting the root password).
I'm on ubuntu 10.10, running chef 10.8 on a fresh server with the mysql
cookbook from opscode.

Thanks. I suspect one common issue might be that if the mysql-server
package is already installed, it won't set a password because on
Debian/Ubuntu it does so via pre-seeding the package before the
package is installed. CHEF-2570 [1] added a 'reconfig' action to the
package provider, it would be good if someone could add support to the
mysql cookbook for this and test it. I've created COOK-1198 [2] to
track that work.

Bryan

[1] http://tickets.opscode.com/browse/CHEF-2570
[2] http://tickets.opscode.com/browse/COOK-1198

10

On Mon, Apr 16, 2012 at 11:02 AM, S Ahmed sahmed1020@gmail.com wrote:

default_attributes "mysql" => { "server_root_password" => "123" }

Be aware of attribute precedence.

http://wiki.opscode.com/display/chef/Attributes

Default attributes are really for when nothing else is provided.
You're trying to set a specific value, so I recommend using a normal
attribute.

Bryan

11

I've read only about setting default_attributes or override in a role, how
do I set a 'normal' attribute?

BTW, what if I am setting multiple attributes, do I just wrap it in [ .. ]
? i.e.

default_attributes [
"mysql" => {},
"abc" => { }
]

On Mon, Apr 16, 2012 at 11:04 AM, Bryan McLellan btm@loftninjas.org wrote:

On Mon, Apr 16, 2012 at 11:02 AM, S Ahmed sahmed1020@gmail.com wrote:

default_attributes "mysql" => { "server_root_password" => "123" }

Be aware of attribute precedence.

http://wiki.opscode.com/display/chef/Attributes

Default attributes are really for when nothing else is provided.
You're trying to set a specific value, so I recommend using a normal
attribute.

Bryan

12

On Mon, Apr 16, 2012 at 11:21 AM, S Ahmed sahmed1020@gmail.com wrote:

I've read only about setting default_attributes or override in a role, how
do I set a 'normal' attribute?

In a role, you'll want to use override_attribute to set the value to
what it should be for all nodes in that role.

http://wiki.opscode.com/display/chef/Attributes#Attributes-RoleAttributes

Grab a cup of coffee and read that whole page, it should help.

Bryan

13

On Monday, April 16, 2012 at 8:21 AM, S Ahmed wrote:

I've read only about setting default_attributes or override in a role, how do I set a 'normal' attribute?

BTW, what if I am setting multiple attributes, do I just wrap it in [ .. ] ? i.e.

default_attributes [
"mysql" => {},
"abc" => { }
]

Normal attributes belong to individual nodes, and are intended to be used for settings that are unique to that node. In general, you should have very few of these, though generated passwords are one use case.

When using Chef in a client/server configuration, normal attributes are preserved, while default and override attributes are recomputed from cookbooks and roles on each run. This is why the MySQL cookbook sets the password as a normal attribute on the node.

For your use case, with chef-solo, you can probably just set the desired value as an attribute in your JSON attributes file (that is, the one you pass in via the -j command line option). If you'd rather use a role, you have to either remove the password generation code from the cookbook, or set an override attribute.

HTH,

Dan DeLeo