如何使用Ansible登录MySQL/MariaDB

Question

我正在使用Ansible将给定的商店软件安装指南与Ansible项目进行复制。因此，我需要创建一个数据库并编辑权限。

我将我的凭据添加到本地my.cnf文件，并通过将其复制到服务器

- name: Create Database
become: yes
community.mysql.mysql_db:
name: shopware
state: present
config_file: /etc/mysql/my.cnf

[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[client]
user="root"
password=""

下一步

- name: Create Database
become: yes
community.mysql.mysql_db:
name: shopware
state: present
config_file: /etc/mysql/my.cnf
- name: Create user with password, all database privileges and 'WITH GRANT OPTION' shopware database
become: yes
community.mysql.mysql_user:
state: present
name: "{{shopware_db_username}}"
password: "{{shopware_db_pass}}"
priv:
'shopware.*': 'ALL,GRANT'

正在抛出错误：

TASK [Create Database] ******************************************************************************************************************************************************************************************************
fatal: [swserver]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /etc/mysql/my.cnf has the credentials. Exception message: (1698, "Access denied for user 'root'@'localhost'")"}

附加信息

• 使用空密码通过mysql -u root -p登录不起作用

服务器

• 操作系统：Ubuntu 20.04.3 LTS x86_64
• 内核：5.4.0-94-generic

主机

• 操作系统：Windows 10 x86_64上的Ubuntu 20.04.3 LTS
• 答案2.9.6
• 内核：5.10.16.3-microsoft-standard-WSL2

来源：https://git.rosibert.de/rpolito/ansible-shopware

Answer 1

由于我过去也有类似的问题，我喜欢在这里分享我的解决方法。

MySQL/MariaDB服务器安装后

- name: Install from EPEL-7 and/or IUS-7 repositories
yum:
name: ['mariadb103-server', 'mariadb103', 'python2-PyMySQL']
state: latest

并将配置复制到/etc/my.cnf.d/my.cnf

[server]
[mysqld]
datadir=/var/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

可以启动服务

- name: Make sure service is started and enabled
systemd:
name: mariadb
state: started
enabled: yes

并通过套接字连接到以执行下一步

# mysql_secure_installation

- name: Delete all anonymous SQL user accounts
mysql_user:
user: "" 
host_all: yes
state: absent
- name: Remove the SQL test database
mysql_db:
db: "test"
state: absent
- name: Change root user password on first run
mysql_user: 
login_user: root
login_password: ''
name: root
password: "{{ SQL_ROOT_PASSWORD }}"
priv: "*.*:ALL,GRANT"
host: "{{ item }}"
with_items:
- "{{ ansible_hostname }}"
- "127.0.0.1"
- "::1"
- "localhost"

之后，像这样的任务

- name: Make sure user exists
shell:
cmd: "mysql -uroot -p{{ SQL_ROOT_PASSWORD }} -e"CREATE USER IF NOT EXISTS '{{ SQL_USER }}'@'{{ SQL_IP }}' IDENTIFIED BY '{{ SQL_USER_PASSWORD }}' REQUIRE SSL;" -N -B"
register: result

是可能的。