Question 204427 in Mysql Question posted in
The official MySQL documentation can be found here.

Resetting the root password for mysql 8 in ansible on centos stream 9

Hi I’m kind of lost here with the following issue.

All I’m trying to do is setup and install mysql 8 on centos stream 9. This should be fairly easy (I’ve done this before on earlier versions of both). But for some reason I cannot get the root password to reset in order to continue on to securing mysql.

I’ve tried the following

1) - name: Set new root pw with temp PASSWORD
 ansible.builtin.shell: mysql -u root -p --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ centos_mysql_root_password }}';"
 
2) - name: Grab the temp root pw from /var/log/mysqld
  ansible.builtin.shell: >- grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}' | tail -n 1
  register: mysql_root_temp_pw
  no_log: true

- name: Update root user password
  community.mysql.mysql_user:
    login_password: "{{ mysql_root_temp_pw.stdout }}"
    name: root
    password: "{{ centos_mysql_root_password }}"
    state: present

3) - name: Ensure root User Can Only Login from Localhost
  community.mysql.mysql_user:
    login_password: "{{ centos_mysql_root_password }}"
    check_implicit_admin: yes
    name: root
    host: "{{ item }}"
    password: "{{ centos_mysql_root_password }}"
    state: present
  with_items:
    - localhost
    - 127.0.0.1
    - ::1
  
- name: Add .my.cnf to Root Home Directory
  ansible.builtin.template:
    src: my.cnf.j2
    dest: /root/.my.cnf

If I run the first command it hangs as it does not quote the ALTER USER command correctly (even if I turn it into a variable and use {{ var | quote }} )
Every other command I run I usually get an error about not being able to connect as root to localhost although I have no idea how thats possible since its a fresh install and I have not run mysql_secure_installation yet.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I would consider this closed, the issue was the following.

    In the original question I was installing the following rpm to install mysql.

    https://repo.mysql.com/mysql80-community-release-el9-1.noarch.rpm

    Then installing mysql-community-server. While using that I kept seeing the above issue.

    I deleted that version of mysql and removed the rpm, I then installed the default version of mysql that comes with centos-stream9, the packages I installed are below.

    1. mysql.x86_64
    2. mysql-common.x86_64
    3. mysql-errmsg.x86_64
    4. mysql-selinux.noarch
    5. mysql-server.x86_64

    With these installed the following ansible task works.

    - name: Set mysql root password
      ansible.builtin.shell: mysql -NBe "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{{ centos_mysql_root_password }}';"

    I'm not sure what is wrong with the mysql-community-server version. But for now the solution at least for me is to install default mysql 8 packages on centos-stream9.


  2. 0

    I’ve build this once for CentOS 8, and I think the logic hasn’t changed.

    Only do this when installed mysql for the first time:

        - name: root | stop mysql
      systemd:
        name: mysql
        state: stopped
   
    - name: root | place temporary cnf file
      template:
        src: temp_cnf.j2
        dest: /etc/my.cnf
        mode: 0644

    - name: root | start and enable mysql
      systemd:
        name: mysql
        state: started
        enabled: true

    - name: root | get temp root password
      shell: >-
        grep 'temporary password' /var/log/mysqld.log |
        awk '{print $NF}' | tail -n 1
      register: temp_root_pw

    - name: root | set root password
      shell: >-
        mysqladmin -u root
        --password="{{ temp_root_pw.stdout }}"
        password "my_password_here"

    Here are the contents of temp_cnf.j2:

    [client]
socket=/var/run/mysqld/mysqld.sock

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

    Please take a good look at the file itself, as it does more than only configuring the root pw.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search