在 ubuntu 上使用 ansible 安装 MySQL

Install MySQL with ansible on ubuntu

提问by Ajouve

I have a problem installing MySQL with ansible on a vagrant ubuntu,

我在 vagrant ubuntu 上使用 ansible 安装 MySQL 时遇到问题，

This is my MySQL part

这是我的 MySQL 部分

---
- name: Install MySQL
  apt:
    name: "{{ item }}"
  with_items:
    - python-mysqldb
    - mysql-server

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

- name: Start the MySQL service
  service: 
    name: mysql 
    state: started
    enabled: true

  # '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 

And I have this error

我有这个错误

failed: [default] => (item=vagrant-ubuntu-trusty-64) => {"failed": true, "item": "vagrant-ubuntu-trusty-64"}
msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials
failed: [default] => (item=127.0.0.1) => {"failed": true, "item": "127.0.0.1"}
msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials
failed: [default] => (item=::1) => {"failed": true, "item": "::1"}
msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials
failed: [default] => (item=localhost) => {"failed": true, "item": "localhost"}
msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

my .my.cnf is

我的 .my.cnf 是

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

and when copied on the server

当复制到服务器上时

[client]
user=root
password=root

I don't understand why, ~/.my.cnf is created

我不明白为什么，创建了 ~/.my.cnf

Project Github

项目Github

Thanks

谢谢

回答by tedder42

When mysql-serveris installed headlessly, there's no password. Therefore to make .my.cnfwork, it should have a blank password line. Here's what I tested with for a .my.cnf:

当mysql-server被headlessly安装，没有密码。因此，为了.my.cnf工作，它应该有一个空白的密码行。这是我对 a 进行的测试.my.cnf：

[client]
user=root
password=

It's also slightly strange to put .my.cnfin your vagrantuser directory as owned by root and only readable as root.

将.my.cnf您的vagrant用户目录作为 root 拥有并且只能作为 root 读取也有点奇怪。

After ensuring the password was blank in .my.cnf, I was able to properly set the password for root in those four contexts. Note that it fails to run after that, since .my.cnfwould need to be updated, so it fails the idempotency test.

在确保密码为空后.my.cnf，我能够在这四个上下文中正确设置 root 的密码。请注意，此后它无法运行，因为.my.cnf需要更新，因此它无法通过幂等性测试。

There's a note on the ansible mysql_user module page that suggests writing the password and thenwriting the .my.cnffile. If you do that, you need a whereclause to the mysql_useraction (probably with a file stat before that).

ansible mysql_user 模块页面上有一条注释，建议先写入密码，然后再写入.my.cnf文件。如果你这样做，你需要一个动作的where子句mysql_user（可能在此之前有一个文件统计信息）。

Even more elegant is to use check_implicit_adminalong with login_userand login_password. That's beautifully idempotent.

更优雅的是check_implicit_admin与login_user和一起使用login_password。这真是幂等的。

As a third way, perhaps check_implicit_adminmakes it even easier.

作为第三种方式，也许check_implicit_admin会使它更容易。

Here's my successful playbook showing the above, tested with a few fresh servers. Kinda proud of this. Note .my.cnfis unnecessary for all of this.

这是我成功的剧本，展示了上述内容，并在几台新服务器上进行了测试。有点为此感到自豪。.my.cnf所有这些都不需要注意。

---
- hosts: mysql
  vars:
    mysql_root_password: fart
  tasks:
  - name: Install MySQL
    apt: name={{ item }} update_cache=yes cache_valid_time=3600 state=present
    sudo: yes
    with_items:
    - python-mysqldb
    - mysql-server
  #- name: copy cnf
  #  copy: src=.my.cnf dest=~/.my.cnf owner=ubuntu mode=0644
  #  sudo: yes
  - name: Start the MySQL service
    sudo: yes
    service: 
      name: mysql 
      state: started
      enabled: true
  - name: update mysql root password for all root accounts
    sudo: yes
    mysql_user: 
      name: root 
      host: "{{ item }}" 
      password: "{{ mysql_root_password }}"
      login_user: root
      login_password: "{{ mysql_root_password }}"
      check_implicit_admin: yes
      priv: "*.*:ALL,GRANT"
    with_items:
      - "{{ ansible_hostname }}"
      - 127.0.0.1
      - ::1
      - localhost 

(edit- removed my.cnf)

（编辑 - 删除了 my.cnf）

回答by Arbab Nazar

Here is my complete working MySQL role, that might help you.

这是我完整的 MySQL 角色，可能对您有所帮助。

vars/main.yml:

变量/main.yml：

mysql_root_pass: mypassword #MySQL Root Password

asks/main.yml:

询问/main.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: 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

templates/root.cnf.j2

模板/root.cnf.j2

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

handlers/main.yml

处理程序/main.yml

---
 - name: Restart MySQL
   service: name=mysql state=restarted

site.yml

站点.yml

---
- hosts: all
  become: yes
  gather_facts: yes
  roles:
    - mysql

If you need any help, please check this github link. Thanks

如果您需要任何帮助，请查看此 github链接。谢谢