创建 MySQL 数据库及用户

MySQL 是一个使用广泛的数据库服务器，你肯定会在某些节点上安装配置 MySQL 服务器。 本节将向你展示如何安装配置 MySQL 服务器，以及如何为应用程序自动创建数据库和用户。

准备工作

1. 如果你还没有 MySQL 模块，先创建一个：

   # mkdir /etc/puppet/modules/mysql
   # mkdir /etc/puppet/modules/manifests
   # mkdir /etc/puppet/modules/files

2. 使用如下内容创建 /etc/puppet/modules/mysql/manifests/server.pp 文件：

   class mysql::server {
     package { "mysql-server": ensure => installed }
   
     service { "mysql":
       enable => true,
       ensure => running,
       require => Package["mysql-server"],
     }
   
     file { "/etc/mysql/my.cnf":
       owner => "mysql", group => "mysql",
       source => "puppet:///mysql/my.cnf",
       notify => Service["mysql"],
       require => Package["mysql-server"],
     }
   
     exec { "set-mysql-password":
       unless => "/usr/bin/mysqladmin -uroot -p${mysql_password}
        status",
       command => "/usr/bin/mysqladmin -uroot password ${mysql_
        password}",
       require => Service["mysql"],
     }
   }
   

3. 使用如下内容创建 /etc/puppet/modules/mysql/files/my.cnf 文件：

   [client]
   port = 3306
   socket = /var/run/mysqld/mysqld.sock
   
   [mysqld_safe]
   socket = /var/run/mysqld/mysqld.sock
   nice = 0
   
   [mysqld]
   user = mysql
   socket = /var/run/mysqld/mysqld.sock
   port = 3306
   datadir = /var/lib/mysql
   
   !includedir /etc/mysql/conf.d/

4. 添加如下代码到 /etc/puppet/manifests/site.pp 文件：

   $mysql_password = "secret"
   

5. 运行 Puppet：

   # puppet agent --test
   info: Retrieving plugin
   info: Caching catalog for cookbook.bitfieldconsulting.com
   info: Applying configuration version '1309448283'
   
   notice: /Stage[main]/Mysql::Server/Package[mysql-server]/ensure:
   ensure changed 'purged' to 'present'
   
   notice: /Stage[main]/Mysql::Server/File[/etc/mysql/my.cnf]/owner:
   owner changed 'root' to 'mysql'
   
   notice: /Stage[main]/Mysql::Server/File[/etc/mysql/my.cnf]/group:
   group changed 'root' to 'mysql'
   
   info: /Stage[main]/Mysql::Server/File[/etc/mysql/my.cnf]:
   Scheduling refresh of Service[mysql]
   
   info: /Stage[main]/Mysql::Server/File[/etc/mysql/my.cnf]:
   Scheduling refresh of Service[mysql]
   
   notice: /Stage[main]/Mysql::Server/Service[mysql]/enable: enable
   changed 'false' to 'true'
   
   notice: /Stage[main]/Mysql::Server/Service[mysql]: Triggered
   'refresh' from 2 events
   
   notice: Finished catalog run in 61.78 seconds

操作步骤

1. 添加如下代码到 /etc/puppet/modules/mysql/manifests/server.pp 文件：

   define db( $user, $password ) {
     include mysql::server
   
     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'; flush
        privileges;\"",
       require => Service["mysql"],
     }
   }
   

2. 添加如下代码到一个节点：

   mysql::server::db { "johnstest":
     user => "john",
     password => "johnstest",
   }
   

3. 运行 Puppet：

   # puppet agent --test
   info: Retrieving plugin
   info: Caching catalog for cookbook.bitfieldconsulting.com
   info: Applying configuration version '1309449259'
   
   notice: /Stage[main]//Node[cookbook]/Mysql::Server::Db[johnstest]/
   Exec[create-johnstest-db]/returns: executed successfully
   
   notice: Finished catalog run in 1.61 seconds

4. 检查数据库是否已经创建，以及用户和权限的正确性：

   # mysql -ujohn -pjohnstest johnstest
   
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 36
   Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current
   input statement.
   
   mysql>

工作原理

mysql::server 类安装 MySQL，并使用你在 site.pp 文件中设置的 root 用户口令配置 MySQL。 名为 mysql::server::db 的 define 允许我们使用一个指定的名字创建数据库， 以及一个能访问此数据库的相关的 MySQL 用户。 例如，一个典型的 web 应用程序可能需要一个以应用程序命名的数据库， 以及一个可以登录数据库的特定用户名。

更多用法

要创建多个数据库，只需添加多个 mysql::server::db 实例：

mysql::server::db { [ "test1", "test2", "test3" ]:
  user => "john",
  password => "johnstest",
}