Hi,
I try to set the steps of mysql_secure_installation for MariaDB 10.11 on Debian 12 with the community.mysql collection. So the first thing I’ve try’d is to change the password of the root user for login via tcp (127.0.0.1 and ::1).
The task is executed successfully. But the password, that was set, isn’t mine. I can’t login after that via 127.0.0.1.
When I set the password via mysql cli I can login.
My Tasks
- name: "Zufallspasswort generieren"
ansible.builtin.set_fact:
mdb_random_password: >-
"{{ lookup(
'community.general.random_string',
length=20,
min_lower=3,
min_upper=3,
min_numeric=3,
override_special='-_.#+*!'
)
}}"
- name: "Zufallspasswort für root setzen, wenn kein Passwort angegeben wurde."
when: not mdb_root_password
ansible.builtin.set_fact:
mdb_root_password: "{{ mdb_random_password }}"
- name: "Passwort für root"
ansible.builtin.debug:
msg: "root: >>{{ mdb_root_password }}<< random: >>{{ mdb_random_password }}<<"
- name: "Passwort für root abspeichern"
ansible.builtin.template:
src: dotmy.cnf.j2
dest: /root/.my.cnf
mode: "0600"
owner: root
group: root
- name: "Absicherung MariaDB"
community.mysql.mysql_user:
name: "root"
host: "{{ item }}"
password: "{{ mdb_root_password }} "
login_unix_socket: "{{ mdb_unix_socket }}"
login_user: "root"
register: root_user
loop:
- "127.0.0.1"
- "::1"
# - "localhost"
- name: "SQL select users"
community.mysql.mysql_query:
login_db: "mysql"
query: "SELECT User, Host, Password FROM user;"
login_unix_socket: "{{ mdb_unix_socket }}"
login_user: "root"
register: mysql_users
- name: "Debug mysql_users"
ansible.builtin.debug:
var: mysql_users
- name: "SQL hash password"
community.mysql.mysql_query:
login_db: "mysql"
query: "SELECT SHA1(UNHEX(SHA1('{{ mdb_root_password }}')));"
login_unix_socket: "{{ mdb_unix_socket }}"
login_user: "root"
register: mysql_hash_pwd
- name: "Debug mysql_users"
ansible.builtin.debug:
var: mysql_hash_pwd
The ansible output for that is
...
TASK [common/apps/mariadb : Absicherung MariaDB] ***************************************************************************************************************************************************************************************************************************************
included: /home/ukosack/projects/edvnet-uk/roles/common/apps/mariadb/tasks/mysql_secure_installation.yml for s005
TASK [common/apps/mariadb : Zufallspasswort generieren] ********************************************************************************************************************************************************************************************************************************
[WARNING]: Collection community.general does not support Ansible version 2.14.18
ok: [s005]
TASK [common/apps/mariadb : Zufallspasswort für root setzen, wenn kein Passwort angegeben wurde.] **************************************************************************************************************************************************************************************
ok: [s005]
TASK [common/apps/mariadb : Passwort für root] *****************************************************************************************************************************************************************************************************************************************
ok: [s005] =>
msg: 'root: >>"2g0aV*lCyJxjM4B0m50N"<< random: >>"2g0aV*lCyJxjM4B0m50N"<<'
TASK [common/apps/mariadb : Passwort für root abspeichern] *****************************************************************************************************************************************************************************************************************************
--- before: /root/.my.cnf
+++ after: /home/ukosack/.ansible/tmp/ansible-local-19589hrgdf8qv/tmp0nx2k8q4/dotmy.cnf.j2
@@ -1,4 +1,4 @@
[client]
user=root
-password="mB3E84gkLDiVGLo1CpTw"
+password="2g0aV*lCyJxjM4B0m50N"
socket=/run/mysqld/mysqld.sock
changed: [s005]
TASK [common/apps/mariadb : Absicherung MariaDB] ***************************************************************************************************************************************************************************************************************************************
changed: [s005] => (item=127.0.0.1)
changed: [s005] => (item=::1)
TASK [common/apps/mariadb : SQL select users] ******************************************************************************************************************************************************************************************************************************************
ok: [s005]
TASK [common/apps/mariadb : Debug mysql_users] *****************************************************************************************************************************************************************************************************************************************
ok: [s005] =>
mysql_users:
changed: false
executed_queries:
- SELECT User, Host, Password FROM user;
failed: false
query_result:
- - Host: localhost
Password: ''
User: mariadb.sys
- Host: localhost
Password: invalid
User: root
- Host: localhost
Password: invalid
User: mysql
- Host: 127.0.0.1
Password: '*25DE158934BF27ADF8B94B56C0E24DE6C9DF5F56'
User: root
- Host: ::1
Password: '*25DE158934BF27ADF8B94B56C0E24DE6C9DF5F56'
User: root
rowcount:
- 5
TASK [common/apps/mariadb : SQL hash password] *****************************************************************************************************************************************************************************************************************************************
ok: [s005]
TASK [common/apps/mariadb : Debug mysql_hash_pwd] *****************************************************************************************************************************************************************************************************************************************
ok: [s005] =>
mysql_hash_pwd:
changed: false
executed_queries:
- SELECT SHA1(UNHEX(SHA1('"2g0aV*lCyJxjM4B0m50N"')));
failed: false
query_result:
- - SHA1(UNHEX(SHA1('"2g0aV*lCyJxjM4B0m50N"'))): 21d949cee7b37882fd7c8168e30e1ff08a112aca
rowcount:
- 1
TASK [common/apps/mariadb : Anonymus User löschen] *************************************************************************************************************************************************************************************************************************************
ok: [s005] => (item=localhost)
ok: [s005] => (item=s005)
TASK [common/apps/mariadb : Anmeldung root nur von localhost] **************************************************************************************************************************************************************************************************************************
ok: [s005]
TASK [common/apps/mariadb : MySQL test Datenbank löschen] ******************************************************************************************************************************************************************************************************************************
ok: [s005]
TASK [common/apps/mariadb : MariaDB-Server löschen (deleted)] **************************************************************************************************************************************************************************************************************************
skipping: [s005]
TASK [common/apps/mariadb : Installation MariaDB] **************************************************************************************************************************************************************************************************************************************
skipping: [s005]
TASK [common/apps/mariadb : Absicherung MariaDB] ***************************************************************************************************************************************************************************************************************************************
skipping: [s005]
PLAY RECAP *****************************************************************************************************************************************************************************************************************************************************************************
s005 : ok=24 changed=2 unreachable=0 failed=0 skipped=19 rescued=0 ignored=0
The password strings are random generated and will be changed on next run.
In this example I’ve tried to set the password 2g0aV*lCyJxjM4B0m50N. The ansible module creates a user password with the hash *25DE158934BF27ADF8B94B56C0E24DE6C9DF5F56.
When I set the password via mysql cli the hash differs (and I can login).
MariaDB [(none)]> set password for 'root'@'127.0.0.1' = PASSWORD('2g0aV*lCyJxjM4B0m50N');
Query OK, 0 rows affected (0,006 sec)
MariaDB [(none)]> SELECT User, Host, Password FROM mysql.user where Host = '127.0.0.1';
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | 127.0.0.1 | *499D7B84C18626812FE4DBFD77046655ACBCDF8C |
+------+-----------+-------------------------------------------+
1 row in set (0,001 sec)
My Ansible controller is a Debian 12 WSL-Linux-VM
username@N001:~/projects/uk$ ansible-config dump --only-changed
CONFIG_FILE() = /home/username/.ansible.cfg
DEFAULT_LOAD_CALLBACK_PLUGINS(/home/username/.ansible.cfg) = True
DEFAULT_LOG_PATH(/home/username/.ansible.cfg) = /home/username/ansible/logs/ansible.log
DEFAULT_PRIVATE_KEY_FILE(/home/username/.ansible.cfg) = /home/username/.ssh/id_ed25519
Where I’m wrong? What I have to do, that mysql_user set the password I’ve configured?
Thanks
Ulf
