adding users on remote mysql

[gioppoluca]

The problem is:
when installing an application in a distributed environment I need to create the application DB on a remote host and not on the same host as the application.

To make an example I'm going to install zabbix on host1 and the mysql db is on host2

I'll have to run the script to create the schema after having created the DB and user.
That operation have to be done in the host2 since it will not be possible to have root access on host1.
There is an interesting post on how to manage root password on the host2 on:
http://bitfieldconsulting.com/puppet-and-mysql-create-databases-and-users

But the post imply that you are working on the same host.

Than there is the exported resources topic:
http://docs.puppetlabs.com/guides/exported_resources.html

The idea is ... could it be possible to take the define from the bitfieldconsulting and use it as a exported resource?

define mysqldb( $user, $password ) {
    exec { "create-${name}-db":
      unless => "/usr/bin/mysql -u${user} -p${password} ${name}",
      command => "/usr/bin/mysql -uroot -p$mysql_password -e \"create database ${name}; grant all on ${name}.* to ${user}@localhost identified by '$password';\"",
      require => Service["mysqld"],
    }
  }


This way:
Create a define in the zabbix class that export the mysqldb resource
define zabbix::mysqldb ($servername = $servername, $user= $user, $password= $password,$Db = $db){
    @@mysqldb { $servername-$db: user => $user, password => $password,tag => "new_user_for_mysql" }    

}

in the zabbix class
class zabbix(...){
...
mysqldb('dbname': server => 'host2', passoword => 'pass', user => 'user')
...
}

and call from within the mysql node

class mysql::server {
  
  package { "mysql-server": ensure => installed }
  package { "mysql": ensure => installed }

  service { "mysqld":
    enable => true,
    ensure => running,
    require => Package["mysql-server"],
  }

  file { "/var/lib/mysql/my.cnf":
    owner => "mysql", group => "mysql",
    source => "puppet:///mysql/my.cnf",
    notify => Service["mysqld"],
    require => Package["mysql-server"],
  }
 
  file { "/etc/my.cnf":
    require => File["/var/lib/mysql/my.cnf"],
    ensure => "/var/lib/mysql/my.cnf",
  }

  exec { "set-mysql-password":
    unless => "mysqladmin -uroot -p$mysql_password status",
    path => ["/bin", "/usr/bin"],
    command => "mysqladmin -uroot password $mysql_password",
    require => Service["mysqld"],
  }
  Mysqldb<<| tag == 'new_user_for_mysql' |>>
 }

Could it work?
Luca

[Luca Gioppo]

OK I tryed to apply the idea and it seems to work up to the fact that the db host forget the root password
I'm using puppetlabs mysql module and doing this in node hostdb

            class { 'mysql::server':
            config_hash => {root_password => 'changeme',}
            }
            Mysql::Db <<| tag == 'new_db' |>>

on host zabbix i do
addmysqldb{'prova':servername => '', user => 'zabbix', password => 'zab01', db => 'zabbix'}

where addmysqldb is:
define addmysqldb( $servername, $user, $password, $db ) {
@@mysql::db { $db:

    user     => $user,
    password => $password,

    host     => $::hostname,
    grant    => ['all'],
    tag => 'new_db'
  }


    notice("User ${user}")
    notice("Password ${password}")
  }

Any hint on why it looses the password it states
access denied for user 'root'@'localhost' using password NO
But if I enter mysql using the "changeme" password from cli I access mysql.
How can I make @@mysql::db remember the password?
Thanks
Luca

[Luca Gioppo]

OK ...
tested and it works like a charm.
Posted the approach in my blog:
http://gioppoluca.blogspot.it/2012/09/puppet-and-exported-resources.html

Il giorno mercoledì 5 settembre 2012 15:44:45 UTC+2, Luca Gioppo ha scritto: