Can't set up MySQL on Ubuntu

Get Help
,
1

Hi All,
I am trying to configure a fresh MySQL installation: Server version: 8.0.40-0ubuntu0.20.04.1 (Ubuntu)

The script sets up variables, and calls a role that starts with the following

  • name: Start MySQL service
    service:
    name: mysql
    state: started
    enabled: yes
- name: Update MySQL root password
  mysql_user:
    name: root
    password: "{{ mysql_root_password }}"

- name: Copy .my.cnf file with root password credentials
  template:
    src: files/my.cnf.j2
    dest: /root/.my.cnf
    owner: root
    mode: 0600

The second step erros with
TASK [configMysql : Update MySQL root password] ******************************************************************************************************
fatal: [ganymede.hcs]: FAILED! => {“changed”: false, “msg”: “unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, "Access denied for user ‘root’@‘localhost’")”}

If I swap steps 2 and 3 about I get the same error on the same step (now 3).

Some things I have found after I went on the target machine with ssh

Last login: Wed Dec 4 11:42:26 2024 from 192.168.1.151
ian@ganymede:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’
ian@ganymede:~$ sudo su
[sudo] password for ian:
root@ganymede:/home/ian# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.40-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

In short, if I am root, I can log in without a password.
If I am not root, I cannot log in as root. (with or without password)

I want to get into a state where I can import the databases, and set up the users and have root@localhost able to log in to mysql with a password.

Where am I going wrong?

Regards

Ian

1 Like
2

These days MariaDB, and I assume MySQL, default to using socket authentication for root rather than a password, what does this return:

sudo mysqlshow --print-defaults | grep socket
1 Like
3

That command does not show anything, so I tried it as root, and again, no response.

Looking at /etc/mysql/mysql.conf.d/mysqld/cnf I believe it is using /var/run/mysqld/mysqld.sock.

I do need to change that to port 3306.

4

No, you only need to use TCP/IP to connect to MySQL if you need to connect to the server from another host, for connections to MySQL on the localhost it is fine to use the socket connection.

2 Likes
5

What I am trying to do is to build a “restore” script, to rebuild a server following a disaster (which has not happened yet - long may that continue :slight_smile: ).

I need to find some way to get MySQL to respond to an Ansible playbook to make the necessary changes: create users, import databases, remove test databases, and remove null usernames.

So far, I have found 5 different instructions on the internet.

None of them work.

Most fail, with “Access denied for user ‘root’@‘localhost’”, but one reported that ansible_hostname is undefined, and another failed “template error while templating string: expected token ‘end of print statement’, got ‘USER’. String: {{ALTER USER root@localhost IDENTIFIED BY mysql_root_password;}}. expected token ‘end of print statement’, got ‘USER’”

The play is

- name: Run MySQL query with elevated privileges
  community.mysql.mysql_query:
      query: "{{ALTER USER root@localhost IDENTIFIED BY mysql_root_password;}}"
      login_user: "ian"
      login_password: "{{ansible_become_pass}}"
      become: true

I cannot see what is wrong. root, localhost and mysql_root_password were originally enclosed in single quotes, but the error always the same.

Ian

6

Try using the socket to authenticate, this should be the default, try something like this?

- name: Run MySQL query with elevated privileges
  community.mysql.mysql_query:
    query: "ALTER USER root@localhost IDENTIFIED BY {{ mysql_root_password }};"
    login_user: root
    login_db: mysql
    login_unix_socket: /var/run/mysqld/mysqld.sock
  become: true

See also the documentation.

1 Like