Ansible - mysql_user module

If you are not familiar with modules, check out Ansible - Getting Started with Modules.

The mysql_user module requires the Python and PyMySQL packages to be installed on the managed nodes. The dnf module (Red Hat 8 distributions) can be used to install Python and PyMySQL.

- dnf:
  name: [ 'python', 'python3-PyMySQL' ]
  state: latest


In this example, The mysql_db module is used to create 'john.doe'@'localhost' account with ALL grants.

- name: user root will create john.doe user account
    login_user: root
    login_password: 'itsasecret'
    login_port: 3306
    login_host: localhost
    login_unix_socket: /var/lib/mysql/mysql.sock
    name: john.doe
    host: localhost
    password: "{{ '%s' | format(password) | password_hash('sha512') }}"
    state: present
    priv: '*.*:ALL,GRANT'


Notice that "root" is the login_user. This means that root will need to be a mysql user with the grant priviledge on localhost. This can be confirmed using the select mysql users command.

~]# mysql -e "select User,Password,Host,Grant_priv from mysql.user where User = 'root' \G"
*************************** 1. row ***************************
      User: root
  Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF
      Host: localhost
Grant_priv: Y
*************************** 2. row ***************************
      User: root
  Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF
Grant_priv: Y
*************************** 3. row ***************************
      User: root
  Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF
      Host: ::1
Grant_priv: Y


The select password command can be used to verify the users password matches the hashed password.

~]# mysql -e "select password('itsasecret')"
| password('itsasecret')                    |
| *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF |


Add a Comment

We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.

Please enter 369b9 in the box below so that we can be sure you are a human.


Web design by yours truely - me, myself, and I   |   |