Puppet MySQL disable root user login without password

Question

I have been over several threads for issues related to puppetlabs-mysql module in last few hours to get a workaround on my issue.

MySQL installation on my agent node with this module is enabling the root user login without any password. But I see the credentials has been set for the root user, and can login using those credentials also.

What difference should I make in my manifest to disable MySQL password-less root login?

Manifest applied,

class { '::mysql::server':
  root_password    => 'rootpassword',
  override_options => { 'mysqld' => { 'max_connections' => '1024' } }
}

Answer 1

What you are referring to as passwordless login is most probably .my.cnf saving the password in an optionfile.

So by setting $mysql::server::create_root_my_cnf to false you would disable this.

class { '::mysql::server':
  root_password    => 'rootpassword',
  create_root_my_cnf => false,
  override_options => { 'mysqld' => { 'max_connections' => '1024' } }
}

Answer 2

I had the same problem and for me, creating a simple ~root/.my.cnf file fixed the problem. Add the following to the root .my.cnf file:

[client]
password="mysql root user password here"

This allows puppet to connect to mysql again. Puppet will then overwrite this file with its own generated configuration.