# Can't set up MySQL on Ubuntu **URL:** \ **Category:** Get Help\ **Tags:** mysql, ubuntu\ **Created:** [December 4, 2024, 12:20pm UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881 "2024-12-04T12:20:10Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![HobsonI](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/hobsoni/32/4960_2.png) [@HobsonI](https://forum.ansible.com/u/HobsonI)\ **Post date:** [December 4, 2024, 12:20pm UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881/1 "2024-12-04T12:20:10Z")
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 ---
**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\ **Post date:** [December 4, 2024, 12:24pm UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881/2 "2024-12-04T12:24:20Z")
These days MariaDB, and I assume MySQL, default to using socket authentication for `root` rather than a password, what does this return: ```bash sudo mysqlshow --print-defaults | grep socket ``` ---
**Author:** ![HobsonI](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/hobsoni/32/4960_2.png) [@HobsonI](https://forum.ansible.com/u/HobsonI)\ **Post date:** [December 4, 2024, 2:11pm UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881/3 "2024-12-04T14:11:49Z")
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. ---
**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\ **Post date:** [December 5, 2024, 3:13pm UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881/4 "2024-12-05T15:13:49Z")
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. ---
**Author:** ![HobsonI](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/hobsoni/32/4960_2.png) [@HobsonI](https://forum.ansible.com/u/HobsonI)\ **Post date:** [December 6, 2024, 9:25am UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881/5 "2024-12-06T09:25:16Z")
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 🙂 ). 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 ```auto - 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 ---
**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\ **Post date:** [December 6, 2024, 11:27am UTC](https://forum.ansible.com/t/cant-set-up-mysql-on-ubuntu/38881/6 "2024-12-06T11:27:56Z")
Try using the socket to authenticate, this should be the default, try something like this? ```yaml - 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](https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_query_module.html).