Ansible 幂等 MySQL 安装 Playbook

Ansible idempotent MySQL installation Playbook

提问by ndequeker

I want to setup a MySQL server on AWS, using Ansible for the configuration management. I am using the default AMI from Amazon (ami-3275ee5b), which uses yumfor package management.

我想在 AWS 上设置一个 MySQL 服务器，使用 Ansible 进行配置管理。我正在使用 Amazon ( ami-3275ee5b)的默认 AMI ，它yum用于包管理。

When the Playbook below is executed, all goes well. But when I run it for a second time, the task Configure the root credentialsfails, because the old password of MySQL doesn't match anymore, since it has been updated the last time I ran this Playbook.

当执行下面的 Playbook 时，一切顺利。但是当我第二次运行它时，任务Configure the root credentials失败了，因为 MySQL 的旧密码不再匹配，因为它已经在我上次运行这个 Playbook 时更新了。

This makes the Playbook non-idempotent, which I don't like. I want to be able to run the Playbook as many times as I want.

这使得 Playbook 非幂等，我不喜欢。我希望能够根据需要多次运行 Playbook。

- hosts: staging_mysql
  user: ec2-user
  sudo: yes

  tasks:
    - name: Install MySQL
      action: yum name=$item
      with_items:
        - MySQL-python
        - mysql
        - mysql-server

    - name: Start the MySQL service
      action: service name=mysqld state=started

    - name: Configure the root credentials
      action: command mysqladmin -u root -p $mysql_root_password

What would be the best way to solve this, which means make the Playbook idempotent? Thanks in advance!

解决这个问题的最佳方法是什么，这意味着使 Playbook 幂等？提前致谢！

采纳答案by ndequeker

Ansible version for a secure MySQL installation.

用于安全 MySQL 安装的 Ansible 版本。

mysql_secure_installation.yml

mysql_secure_installation.yml

- hosts: staging_mysql
  user: ec2-user
  sudo: yes

  tasks:
    - name: Install MySQL
      action: yum name={{ item }}
      with_items:
        - MySQL-python
        - mysql
        - mysql-server

    - name: Start the MySQL service
      action: service name=mysqld state=started

    # 'localhost' needs to be the last item for idempotency, see
    # http://ansible.cc/docs/modules.html#mysql-user
    - name: update mysql root password for all root accounts
      mysql_user: name=root host={{ item }} password={{ mysql_root_password }}
      with_items:
        - "{{ ansible_hostname }}"
        - 127.0.0.1
        - ::1
        - localhost

    - name: copy .my.cnf file with root password credentials
      template: src=templates/root/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

    - name: delete anonymous MySQL server user for $server_hostname
      action: mysql_user user="" host="{{ server_hostname }}" state="absent"

    - name: delete anonymous MySQL server user for localhost
      action: mysql_user user="" state="absent"

    - name: remove the MySQL test database
      action: mysql_db db=test state=absent

templates/root/my.cnf.j2

模板/根/my.cnf.j2

[client]
user=root
password={{ mysql_root_password }}

References

参考

• The original answer by Lorin Hochstein
• https://github.com/gaspaio/ansible-devbox/blob/master/roles/mysql/tasks/server.yml

• Lorin Hochstein 的原始答案
• https://github.com/gaspaio/ansible-devbox/blob/master/roles/mysql/tasks/server.yml

回答by Lorin Hochstein

I posted about this on coderwall, but I'll reproduce dennisjac's improvement in the comments of my original post.

我在coderwall上发布了这个，但我会在我原来的帖子的评论中重现 dennisjac 的改进。

The trick to doing it idempotently is knowing that the mysql_user module will load a ~/.my.cnf file if it finds one.

以幂等方式执行此操作的技巧是知道 mysql_user 模块如果找到一个 ~/.my.cnf 文件将加载一个。

I first change the password, then copy a .my.cnf file with the password credentials. When you try to run it a second time, the myqsl_user ansible module will find the .my.cnf and use the new password.

我首先更改密码，然后使用密码凭据复制一个 .my.cnf 文件。当您尝试第二次运行它时，myqsl_user ansible 模块将找到 .my.cnf 并使用新密码。

- hosts: staging_mysql
  user: ec2-user
  sudo: yes

  tasks:
    - name: Install MySQL
      action: yum name={{ item }}
      with_items:
        - MySQL-python
        - mysql
        - mysql-server

    - name: Start the MySQL service
      action: service name=mysqld state=started

    # 'localhost' needs to be the last item for idempotency, see
    # http://ansible.cc/docs/modules.html#mysql-user
    - name: update mysql root password for all root accounts
      mysql_user: name=root host={{ item }} password={{ mysql_root_password }} priv=*.*:ALL,GRANT
      with_items:
        - "{{ ansible_hostname }}"
        - 127.0.0.1
        - ::1
        - localhost

    - name: copy .my.cnf file with root password credentials
      template: src=templates/root/.my.cnf dest=/root/.my.cnf owner=root mode=0600

The .my.cnf template looks like this:

.my.cnf 模板如下所示：

[client]
user=root
password={{ mysql_root_password }}

Edit: Added privileges as recommended by Dhananjay Nene in the comments, and changed variable interpolation to use braces instead of dollar sign.

编辑：添加了 Dhananjay Nene 在评论中推荐的权限，并将变量插值更改为使用大括号而不是美元符号。

回答by Dhananjay Nene

This is an alternative solution to the one proposed by @LorinHochStein

这是@LorinHochStein 提出的解决方案的替代解决方案

One of my constraints was to ensure that no passwords are stored in plain text files anywhere on the server. Thus .my.cnf was not a practical proposition

我的限制之一是确保没有密码存储在服务器上的任何地方的纯文本文件中。因此 .my.cnf 不是一个实用的提议

Solution :

解决方案 ：

- name: update mysql root password for all root accounts from local servers
  mysql_user: login_user=root 
              login_password={{ current_password }} 
              name=root 
              host=$item 
              password={{ new_password }} 
              priv=*.*:ALL,GRANT
  with_items:
      - $ansible_hostname
      - 127.0.0.1
      - ::1
      - localhost

And in the vars file

在 vars 文件中

current_password: foobar
new_password: "{{ current_password }}"

When not changing the mysql password run ansible playbook on command line as usual.

当不更改 mysql 密码时，像往常一样在命令行上运行 ansible playbook。

When changing the mysql password, add the following to the command line. Specifying it on the commandline allows the parameter set on the command line to take precedence over the one defaulted to in the vars file.

更改mysql密码时，在命令行中添加以下内容。在命令行上指定它允许在命令行上设置的参数优先于 vars 文件中默认设置的参数。

$ ansible-playbook ........ --extra-vars "new_password=buzzz"

After running the command change the vars file as follows

运行命令后更改vars文件如下

current_password=buzzz
new_password={{ current_password }}

回答by mahemoff

Adding to the previous answers, I didn't want a manual step before running the command, ie I want to spin up a new server and just run the playbook without having to manually change the root password first time. I don't believe {{ mysql_password }} will work the first time, when root password is null, because mysql_password still has to be defined somewhere (unless you want to override it with -e).

添加到前面的答案中，我不想在运行命令之前手动执行步骤，即我想启动一个新服务器并只运行 playbook，而不必第一次手动更改 root 密码。我不相信 {{ mysql_password }} 会第一次工作，当 root 密码为空时，因为 mysql_password 仍然必须在某处定义（除非你想用 -e 覆盖它）。

So I added a rule to do that, which is ignored if it fails. This is in addition to, and appears before, any of the other commands here.

所以我添加了一个规则来做到这一点，如果失败，则忽略该规则。这是此处任何其他命令的补充，并且出现在此之前。

- name: Change root user password on first run
  mysql_user: login_user=root
              login_password=''
              name=root
              password={{ mysql_root_password }}
              priv=*.*:ALL,GRANT
              host={{ item }}
      with_items:
        - $ansible_hostname
        - 127.0.0.1
        - ::1
        - localhost
      ignore_errors: true

回答by anneb

For ansible 1.3+ :

对于 ansible 1.3+ ：

- name: ensure mysql local root password is zwx123
  mysql_user: check_implicit_admin=True login_user=root login_password="zwx123" name=root password="zwx123" state=present

回答by oblalex

Well, this came a bit complicated. I've spent a whole day on this and came up with the solution listed below. The key point is how Ansible installs MySQL server. From the docs of mysql_usermodule (last note on page):

嗯，这有点复杂。我花了一整天的时间来解决这个问题，并提出了下面列出的解决方案。关键是 Ansible 如何安装 MySQL 服务器。来自mysql_user模块的文档（页面上的最后一个注释）：

MySQL server installs with default login_user of ‘root' and no password. To secure this user as part of an idempotent playbook, you must create at least two tasks: the first must change the root user's password, without providing any login_user/login_password details. The second must drop a ~/.my.cnf file containing the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from the file.

That issue with blank or null password was a big surprise.

空白或空密码的问题是一个很大的惊喜。

Role:

角色：

---

- name: Install MySQL packages
  sudo: yes
  yum: name={{ item }} state=present
  with_items:
    - mysql
    - mysql-server
    - MySQL-python


- name: Start MySQL service
  sudo: yes
  service: name=mysqld state=started enabled=true


- name: Update MySQL root password for root account
  sudo: yes
  mysql_user: name=root password={{ db_root_password }} priv=*.*:ALL,GRANT


- name: Create .my.cnf file with root password credentials
  sudo: yes
  template: src=.my.cnf.j2 dest=/root/.my.cnf owner=root group=root mode=0600
  notify:
  - restart mysql


- name: Create a database
  sudo: yes
  mysql_db: name={{ db_name }}
            collation=utf8_general_ci
            encoding=utf8
            state=present


- name: Create a database user
  sudo: yes
  mysql_user: name={{ db_user }}
              password={{ db_user_password }}
              priv="{{ db_name }}.*:ALL"
              host=localhost
              state=present

Handler:

处理程序：

---

- name: restart mysql
  service: name=mysqld state=restarted

.my.cnf.j2:

.my.cnf.j2:

[client]
user=root
password={{ db_root_password }}

回答by smuniyappa

The following will Work (Insert my.cnf in between 2 mysql_user calls)

以下将起作用（在 2 个 mysql_user 调用之间插入 my.cnf）

---

---

- name: 'Install MySQL'
    yum: name={{ item }} state=present
    with_items:
    - MySQL-python
    - mysql
    - mysql-server
    notify:
     - restart-mysql
- name: 'Start Mysql Service'
  action: service name=mysqld state=started enabled=yes
- name: 'Update Mysql Root Password'
  mysql_user: name=root host=localhost password={{ mysql_root_password }} state=present
- name: 'Copy Conf file with root password credentials'
  template: src=../templates/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600
- name: 'Update Rest-Mysql Root Password'
  mysql_user: name=root host={{ item }} password={{ mysql_root_password }} state=present
    with_items:
    - "{{ ansible_hostname }}"
    - "{{ ansible_eth0.ipv4.address }}"
    - 127.0.0.1
    - ::1
- name: 'Delete anonymous MySQL server user from server'
  mysql_user: name="" host={{ ansible_hostname }} state="absent"

回答by Arbab Nazar

I know this is an old question, but I am sharing my working playbook for those, who are looking for it:

我知道这是一个老问题，但我正在为那些正在寻找它的人分享我的工作手册：

mysql.yml

mysql.yml

---
 - name: Install the MySQL packages
   apt: name={{ item }} state=installed update_cache=yes
   with_items:
     - mysql-server-5.6
     - mysql-client-5.6
     - python-mysqldb
     - libmysqlclient-dev

 - name: Copy the configuration file (my.cnf)
   template: src=my.cnf.j2 dest=/etc/mysql/my.cnf
   notify:
     - Restart MySQL

 - name: Update MySQL root password for all root accounts
   mysql_user: name=root host={{ item }} password={{ mysql_root_pass }} state=present
   with_items:
     - "{{ ansible_hostname }}"
     - 127.0.0.1
     - ::1
     - localhost

 - name: Copy the root credentials as .my.cnf file
   template: src=root.cnf.j2 dest=~/.my.cnf mode=0600

 - name: Ensure Anonymous user(s) are not in the database
   mysql_user: name='' host={{ item }} state=absent
   with_items:
     - localhost
     - "{{ ansible_hostname }}"

 - name: Remove the test database
   mysql_db: name=test state=absent
   notify:
     - Restart MySQL

vars.yml

变量名

---
 mysql_port: 3306 #Default is 3306, please change it if you are using non-standard
 mysql_bind_address: "127.0.0.1" #Change it to "0.0.0.0",if you want to listen everywhere
 mysql_root_pass: mypassword #MySQL Root Password

my.cnf.j2

我的.cnf.j2

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = {{ mysql_port }}
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = {{ mysql_bind_address }}
key_buffer              = 16M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 64M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

root.cnf.j2

根.cnf.j2

[client]
user=root
password={{ mysql_root_pass }}

回答by Arbab Nazar

It is important to start/re-start the mysql server prior to setting the root password. Also, I had tried everything posted up to this post [date] and discovered it is imperative to pass login_passwordand login_user.

在设置 root 密码之前启动/重新启动 mysql 服务器很重要。此外，我已经尝试了在这篇文章 [date] 之前发布的所有内容，并发现必须通过login_password和login_user。

(i.e.) Any Plays after setting the mysql_useruser:rootand password= {{ SOMEPASSWORD }}, you must connect using login_passwordand login_userfor any subsequent play.

(ie) Any Plays 设置mysql_useruser:rootand 后password= {{ SOMEPASSWORD }}，您必须连接使用login_passwordandlogin_user以进行任何后续播放。

Note: The with_itemsbelow is based on what Ansible &/ MariaDB default hosts created.

注意：with_items以下基于 Ansible 和/MariaDB 默认主机创建的内容。

Example for Securing a MariaDB Server:

保护 MariaDB 服务器的示例：

---
# 'secure_mariadb.yml'

- name: 'Ensure MariaDB server is started and enabled on boot'
  service: name={{ mariadb_service_name }} state=started enabled=yes

# localhost needs to be the last item for idempotency, see
# http://ansible.cc/docs/modules.html#mysql-user
- name: 'Update Mysql Root Password'
  mysql_user: name=root
              host={{ item }}
              password={{ root_db_password }}
              priv=*.*:ALL,GRANT
              state=present
  with_items:
    - 127.0.0.1
    - ::1
    - instance-1 # Created by MariaDB to prevent conflicts between port and sockets if multi-instances running on the same computer.
    - localhost

- name: 'Create MariaDB main configuration file'
  template: >
    src=my.cnf.j2
    dest=/etc/mysql/my.cnf
    owner=root
    group=root
    mode=0600

- name: 'Ensure anonymous users are not in the database'
  mysql_user: login_user=root 
              login_password={{ root_db_password }}
              name=''
              host={{ item }}
              state=absent
  with_items:
    - 127.0.0.1
    - localhost

- name: 'Remove the test database'
  mysql_db: login_user=root 
            login_password={{ root_db_password }}
            name=test
            state=absent

- name: 'Reload privilege tables'
  command: 'mysql -ne "{{ item }}"'
  with_items:
    - FLUSH PRIVILEGES
  changed_when: False

- name: 'Ensure MariaDB server is started and enabled on boot'
  service: name={{ mariadb_service_name }} state=started enabled=yes


# 'End Of File'

回答by Ben

I'm adding my own take on the various approaches (centos 7).

我正在添加我自己对各种方法的看法（centos 7）。

The variable mysql_root_password should be stored in an ansible-vault (better) or passed on the command-line (worse)

变量 mysql_root_password 应该存储在 ansible-vault（更好）或通过命令行传递（更糟）

- name: "Ensure mariadb packages are installed"
  yum: name={{ item }} state="present"
  with_items:
    - mariadb
    - mariadb-server

- name: "Ensure mariadb is running and configured to start at boot"
  service: name=mariadb state=started enabled=yes

# idempotently ensure secure mariadb installation --
# - attempts to connect as root user with no password and then set the root@ mysql password for each mysql root user mode.
# - ignore_errors is true because this task will always fail on subsequent runs (as the root user password has been changed from "")
- name: Change root user password on first run, this will only succeed (and only needs to succeed) on first playbook run
  mysql_user: login_user=root
              login_password=''
              name=root
              password={{ mysql_root_password }}
              priv=*.*:ALL,GRANT
              host={{ item }}
  with_items:
    - "{{ ansible_hostname }}"
    - 127.0.0.1
    - ::1
    - localhost
  ignore_errors: true

- name: Ensure the anonymous mysql user ""@{{ansible_hostname}} is deleted
  action: mysql_user user="" host="{{ ansible_hostname }}" state="absent" login_user=root login_password={{ mysql_root_password }}

- name: Ensure the anonymous mysql user ""@localhost is deleted
  action: mysql_user user="" state="absent" login_user=root login_password={{ sts_ad_password }}

- name: Ensure the mysql test database is deleted
  action: mysql_db db=test state=absent login_user=root login_password={{ mysql_root_password }}