0

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' } }
}
Vineeth Vijayan
  • 15
  • 5
  • 1
    its probably setting the password for `'root'@'localhost'` and not `'root'@'%'` – ptierno Feb 15 '17 at 14:29
  • Thanks @ptierno . Any idea on the manifest part for the same? – Vineeth Vijayan Feb 15 '17 at 14:35
  • Just use the mysql_user resource: (sorry for the broken formatting) `mysql_user { 'root@%': ensure => present, password_hash => mysql_password('rootpassword'), }` – Ben Feb 15 '17 at 14:42

2 Answers2

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.

Lars Olafsen
  • 21
  • 2
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' } }
}
Ben
  • 341
  • 4
  • 16
  • Yes, that is correct. That manifest will create .my.cnf under my root(shell) user. So that means, it prompt for mysql root password from other shell users. I have tried your example. It works, thank you. But the thing with this is, it will throw an error on further user,db creations. Guess there is more threads on it, `Error: Failed to apply catalog: Execution of '/usr/bin/mysql -NBe SELECT CONCAT(User, '@',Host) AS User FROM mysql.user' returned 1: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` – Vineeth Vijayan Feb 16 '17 at 06:10
  • My manifest, `class { '::mysql::server': root_password => 'rootpassword', create_root_my_cnf => false, override_options => { 'mysqld' => { 'max_connections' => '1024' } } } mysql::db { 'mydb': user => 'user', password => 'pass', host => 'localhost', grant => ['SELECT', 'UPDATE'], } ` – Vineeth Vijayan Feb 16 '17 at 06:11
  • Also, I also tried your first manifest example, `mysql_user { 'root@%': ensure => present, password_hash => mysql_password('rootpassword'), }`. It does work in a way, doesn't create the file I think, creates the mysql root user without any password. Means I can't login with just `mysql` command, but accepts an empty password with `mysql -u root -p`. With this, it doesn't throw error while creating any further user,db combinations. – Vineeth Vijayan Feb 16 '17 at 06:43