How to login with created user from community.mysql.mysql_user

Get Help
1

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!!!

2

I can login as my testuser, but not from my other playbook.

doesnt get the password overwritten each time i execute this?:

    - name: Create MySQL user {{ sql_src_db }} with a random password and additional parameters
      community.mysql.mysql_user:
        name: "{{ sql_src_db }}"
        password: "{{ password_result.stdout }}"
        host: "%"
        priv: "{{ sql_src_db }}.*:ALL"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
3

its confusing because i added an test of the userdata in my playboook to ceck if the new credentials are alright and the playbook doesnt fail there:

---
- 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: Output the generated password
      ansible.builtin.debug:
        msg: "Generated password for MySQL user {{ sql_src_db }}: {{ password_result.stdout }}"
      when: ansible_check_mode is not defined or not ansible_check_mode

    - name: Create MySQL user {{ sql_src_db }} with a random password and additional parameters
      community.mysql.mysql_user:
        name: "{{ sql_src_db }}"
        password: "{{ password_result.stdout }}"
        host: "%"
        priv: "{{ sql_src_db }}.*:ALL"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock

    - name: Append the generated password to the text file
      ansible.builtin.lineinfile:
        path: "/tmp/{{ sql_src_db }}_usrpw.txt"
        line: "{{ password_result.stdout }}"
        create: yes
        insertafter: EOF

    - name: Test SQL connection with the created user
      ansible.builtin.shell: >
        mysql -u {{ sql_src_db }} -p{{ password_result.stdout }} -e "SHOW DATABASES;" 
        --socket=/var/run/mysqld/mysqld.sock
      register: sql_test_result
      ignore_errors: yes

    - name: Output the SQL connection test result
      ansible.builtin.debug:
        msg: >
          "SQL connection successful with user {{ sql_src_db }}."
      when: sql_test_result.rc == 0

    - name: Output SQL connection test failure
      ansible.builtin.debug:
        msg: >
          "SQL connection failed with user {{ sql_src_db }}. Error: {{ sql_test_result.stderr }}"
      when: sql_test_result.rc != 0
4

What I can see here:

root@testsrv-02:~# sudo mysql -u bob -p
Enter password:
ERROR 1045 (28000): Access denied for user 'bob'@'localhost' (using password: YES)

is that you have tried to test user bob instead of testbob. So I wonder if this is just a simple mixup. I see no apparent error in you playbook.

5

yeah thats wy i changed my statement that i can just not login with the user/pw created in the playbook above
Somehow when using vars in the sql usercreation the test of the playbook can use the credential, but when i use the password outputted from the debug password output(wich matches the one in the .txt files) i cannot login with them in the sql server

6

it seems i did misspelled the user with bob and sakilla/sakila