mysql - Ansible playbook 在 Ubuntu 上安装和配置 MySQL

Question

我正在尝试合理安全地设置 MySQL，
在 Ubuntu 22.04 上，使用 Ansible。这是我的剧本(来自 Lorin Hochstein 的帖子)见 Ansible idempotent MySQL installation Playbook这是我的剧本(转换为 apt 和 Ubuntu)

- hosts: carme.hcs
  become: yes
  gather_facts: false
  vars:
    new_mysql_root_password: <redacted>
    mysqlsoftware:
      - python3-pymysql
      - mysql-client
      - mysql-server

  tasks:
    - name: Install MySQL
      action: apt install {{ item }}
      with_items: "{{ mysqlsoftware }}"

    - name: Start the MySQL service
      action: service name=mysql 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:
        check_implicit_admin: true
        login_user: root
        name: root
        priv: '*.*:ALL,GRANT'
        host: "{{ item }}"
        password: "{{ new_mysql_root_password }}"
      with_items:
        - 127.0.0.1
        - ::1
        - localhost

    - name: copy .my.cnf file with root password credentials
      template: src=./shared/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`

步骤 1 和 2 工作得很好。
第 3 步总是失败 TASK [update mysql root password for all root accounts] ********************************************************************************************************* failed: [carme.hcs] (item=127.0.0.1) => {"ansible_loop_var": "item", "changed": false, "item": "127.0.0.1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"} failed: [carme.hcs] (item=::1) => {"ansible_loop_var": "item", "changed": false, "item": "::1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"} failed: [carme.hcs] (item=localhost) => {"ansible_loop_var": "item", "changed": false, "item": "localhost", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"} 我检查了carme.hcs，/root/.my.cnf 不存在。
我可以使用“sudo mysql”登录 mysql，但不能使用
“mysql -u root”或“mysql -u root -p”。这不是
我希望更改的全新 MySQL 安装的默认设置？
我也跑了' 从 user = "root"的用户中选择用户、主机、插件、身份验证字符串； ' 在 carme 上，结果令人难以置信。
简而言之，对于 root@localhost，plugin = "mysql_native_password"和 authentication_string 是空白的。
我不明白出了什么问题。请给点启示!

Answer 1

剧本有很多问题，我很尴尬。

我误解了安装 pip 以及应该使用 apt 安装什么以及使用 pip 安装什么。

apt 会挂起，并且需要一个 -y 参数。

它离开/root/.my.cnf 所以 root 仍然可以在没有密码的情况下登录。

修复后，当它运行两次时，第二次将失败，因为它无法在没有密码的情况下登录。

mysql.user 表中不存在 127.0.0.1 和::1，因此不需要删除它们的 root 访问权限。

好的，让我们发布更新的剧本。

- hosts: carme.hcs
  become: yes
  gather_facts: false
  vars:
    new_mysql_root_password: redacted
    mysqlsoftware:
      - mysql-server
      - mysql-client
  tasks:
    - name: install python, pip etc
      shell: apt-get -y install "{{ item }}"
      with_items:
        - pip 
        - python3-dev 
        - default-libmysqlclient-dev 
        - build-essential

    - name: Install MySQL server
      shell: apt-get -y install mysql-server

    - name: Install MySQL client
      shell: apt-get -y install mysql-client

    - name: pip install mysqlclient
      shell: pip install mysqlclient

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

    - name: copy .my.cnf file with root password credentials
      template: src=/home/ian/Ansible/playbooks/shared/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

    - name: update mysql root password for all root accounts
      mysql_user:
        name: root
        host: localhost
        password: "{{ new_mysql_root_password }}"

    - 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

    - name: Remove   /root/.my.cnf
      ansible.builtin.file:
        path: /root/.my.cnf
        state: absent