I created a user:
community.mysql.mysql_user:
name: testbob
password: testbobspassword
host: "%"
priv: '*.*:ALL,GRANT'
state: present
login_unix_socket: /var/run/mysqld/mysqld.sock
But when i try to login it failes:
root@testsrv-02:~# sudo mysql -u bob -p
Enter password:
ERROR 1045 (28000): Access denied for user 'bob'@'localhost' (using password: YES)
In the sql log the user is there:
2024-09-19T12:01:17.955304Z 69 Connect root@localhost on using Socket
2024-09-19T12:01:17.958421Z 69 Query SELECT VERSION()
2024-09-19T12:01:17.971061Z 69 Query SELECT @@GLOBAL.sql_mode
2024-09-19T12:01:17.972091Z 69 Query SELECT count(*) FROM mysql.user WHERE user = 'testbob' AND host = '%'
2024-09-19T12:01:17.972842Z 69 Query SELECT VERSION() AS version
2024-09-19T12:01:17.973368Z 69 Query SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1('testbobspassword')))))
2024-09-19T12:01:17.974411Z 69 Query CREATE USER 'testbob'@'%' IDENTIFIED WITH mysql_native_password AS '*8883E66DB88B90BEE52B188C64946CA656AFA046'
2024-09-19T12:01:17.987176Z 69 Query GRANT ALL ON *.* TO 'testbob'@'%' WITH GRANT OPTION
Im askin because for an worklflow with db migration i have to set a new user with permissions for the migrated DB and in the src host change the password of the same user so nobody can login in the old DB accidently just for admin uses.
But i noted i could not login with the credentials so i created an test playbook, but even without vars i cannot login.
Testplay:
---
- name: Create MySQL user and grant privileges on a specific database
hosts:
- "{{ sql_dest_srv }}"
# - "{{ sql_src_srv }}"
become: true
vars:
ansible_python_interpreter: /usr/bin/python3
tasks:
- name: Ensure pip and venv are installed
ansible.builtin.apt:
name:
- python3-pip
- python3-venv
- python3-dev
- build-essential
- libssl-dev
- libffi-dev
- libmysqlclient-dev
- pkg-config
- python3-pymysql
state: present
- name: Create a virtual environment
ansible.builtin.command:
cmd: python3 -m venv /opt/myenv
args:
creates: /opt/myenv
- name: Install required Python packages in virtual environment
ansible.builtin.pip:
executable: /opt/myenv/bin/pip
name:
- pymysql
- name: Generate a random password
ansible.builtin.command: "openssl rand -base64 12"
register: password_result
no_log: true
- name: Create MySQL user {{ sql_src_db }} with a random password and additional parameters
community.mysql.mysql_user:
name: testbob
password: testbobspassword
host: "%"
priv: '*.*:ALL,GRANT'
state: present
login_unix_socket: /var/run/mysqld/mysqld.sock
Thanks again!!!