Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

"msg": "unable to find /root/.my.cnf. Exception message: (2003, \"Can't connect to MySQL server on 'localhost' ([Errno 2] No such file or directory)\")" #26

Open
charnet1019 opened this issue Jul 5, 2022 · 3 comments
Open

"msg": "unable to find /root/.my.cnf. Exception message: (2003, \"Can't connect to MySQL server on 'localhost' ([Errno 2] No such file or directory)\")" #26

charnet1019 opened this issue Jul 5, 2022 · 3 comments
Assignees
@tgadiev

Comments

@charnet1019
Copy link

charnet1019 commented Jul 5, 2022 •
edited
Loading

OS: CentOS 7.9
Ansible: 2.12.6

An error is reported when customizing the data directory.
playbook:

---
  
- hosts: mysql
  gather_facts: yes

  vars:
    mysql_root_password: p8TEM-veDRE<8c8h8serd
    mysql_databases:
      - name: demo
        encoding: utf8mb4
        collation: utf8mb4_general_ci
    mysql_users:
      - name: testuser
        host: "%"
        password: Sime32-SRpR-Paccw0rd
        priv: "demo.*:ALL"
    mysql_port: 3306
    mysql_bind_address: '0.0.0.0'
    mysql_daemon: mysqld
    mysql_version: 5.7
    mysql_datadir: "/data/mysql/data"
    mysql_socket: "/data/mysql/data/mysql.sock"

  roles:
    - mysql
  tags:
    - mysql

Add create directory task in tasks/configure.yml

- name: Create directory if necessary
  file:
    path: "{{ item }}"
    state: directory
    owner: "{{ mysql_users_groups }}"
    group: "{{ mysql_users_groups }}"
  loop:
    - "{{ mysql_datadir }}"
  when:
    - mysql_datadir and mysql_datadir != None

error message:

TASK [mysql : Check mysql version] *********************************************************************************************************************************************************
task path: /home/ansible/roles/mysql/tasks/secure-installation.yml:6
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'echo ~root && sleep 0'"'"''
<192.168.60.8> (0, b'/root\n', b'')
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342 `" && echo ansible-tmp-1657003633.2455368-23813-217389934954342="` echo /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342 `" ) && sleep 0'"'"''
<192.168.60.8> (0, b'ansible-tmp-1657003633.2455368-23813-217389934954342=/root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342\n', b'')
Using module file /usr/local/lib/python3.9/site-packages/ansible_collections/community/mysql/plugins/modules/mysql_variables.py
<192.168.60.8> PUT /root/.ansible/tmp/ansible-local-2350319dwt_nc/tmp0_8k8gu6 TO /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342/AnsiballZ_mysql_variables.py
<192.168.60.8> SSH: EXEC sshpass -d10 sftp -o BatchMode=no -b - -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' '[192.168.60.8]'
<192.168.60.8> (0, b'sftp> put /root/.ansible/tmp/ansible-local-2350319dwt_nc/tmp0_8k8gu6 /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342/AnsiballZ_mysql_variables.py\n', b'')
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'chmod u+x /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342/ /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342/AnsiballZ_mysql_variables.py && sleep 0'"'"''
<192.168.60.8> (0, b'', b'')
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' -tt 192.168.60.8 '/bin/sh -c '"'"'/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342/AnsiballZ_mysql_variables.py && sleep 0'"'"''
<192.168.60.8> (1, b'\r\n{"failed": true, "msg": "unable to find /root/.my.cnf. Exception message: (1045, \\"Access denied for user \'root\'@\'localhost\' (using password: YES)\\")", "exception": "  File \\"/tmp/ansible_mysql_variables_payload_s6hl_9yp/ansible_mysql_variables_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_variables.py\\", line 208, in main\\n  File \\"/tmp/ansible_mysql_variables_payload_s6hl_9yp/ansible_mysql_variables_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py\\", line 106, in mysql_connect\\n    db_connection = mysql_driver.connect(autocommit=autocommit, **config)\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/connections.py\\", line 353, in __init__\\n    self.connect()\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/connections.py\\", line 633, in connect\\n    self._request_authentication()\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/connections.py\\", line 907, in _request_authentication\\n    auth_packet = self._read_packet()\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/connections.py\\", line 725, in _read_packet\\n    packet.raise_for_error()\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/protocol.py\\", line 221, in raise_for_error\\n    err.raise_mysql_exception(self._data)\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/err.py\\", line 143, in raise_mysql_exception\\n    raise errorclass(errno, errval)\\n", "invocation": {"module_args": {"variable": "version", "login_user": "root", "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "login_host": "localhost", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "mode": "global", "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "value": null}}}\r\n', b'Shared connection to 192.168.60.8 closed.\r\n')
<192.168.60.8> Failed to connect to the host via ssh: Shared connection to 192.168.60.8 closed.
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'rm -f -r /root/.ansible/tmp/ansible-tmp-1657003633.2455368-23813-217389934954342/ > /dev/null 2>&1 && sleep 0'"'"''
<192.168.60.8> (0, b'', b'')
The full traceback is:
  File "/tmp/ansible_mysql_variables_payload_s6hl_9yp/ansible_mysql_variables_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_variables.py", line 208, in main
  File "/tmp/ansible_mysql_variables_payload_s6hl_9yp/ansible_mysql_variables_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 106, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 907, in _request_authentication
    auth_packet = self._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.6/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
fatal: [192.168.60.8]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "check_hostname": null,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "login_host": "localhost",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "mode": "global",
            "value": null,
            "variable": "version"
        }
    },
    "msg": "unable to find /root/.my.cnf. Exception message: (1045, \"Access denied for user 'root'@'localhost' (using password: YES)\")"
}





TASK [mysql : Reset root credentials] ******************************************************************************************************************************************************
task path: /home/ansible/roles/mysql/tasks/secure-installation.yml:53
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'echo ~root && sleep 0'"'"''
<192.168.60.8> (0, b'/root\n', b'')
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138 `" && echo ansible-tmp-1657003644.1607108-23996-145481247012138="` echo /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138 `" ) && sleep 0'"'"''
<192.168.60.8> (0, b'ansible-tmp-1657003644.1607108-23996-145481247012138=/root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138\n', b'')
Using module file /usr/local/lib/python3.9/site-packages/ansible_collections/community/mysql/plugins/modules/mysql_db.py
<192.168.60.8> PUT /root/.ansible/tmp/ansible-local-2350319dwt_nc/tmpb6m2_rop TO /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138/AnsiballZ_mysql_db.py
<192.168.60.8> SSH: EXEC sshpass -d10 sftp -o BatchMode=no -b - -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' '[192.168.60.8]'
<192.168.60.8> (0, b'sftp> put /root/.ansible/tmp/ansible-local-2350319dwt_nc/tmpb6m2_rop /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138/AnsiballZ_mysql_db.py\n', b'')
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'chmod u+x /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138/ /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138/AnsiballZ_mysql_db.py && sleep 0'"'"''
<192.168.60.8> (0, b'', b'')
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' -tt 192.168.60.8 '/bin/sh -c '"'"'/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138/AnsiballZ_mysql_db.py && sleep 0'"'"''
<192.168.60.8> (1, b'\r\n{"failed": true, "msg": "unable to find /root/.my.cnf. Exception message: (2003, \\"Can\'t connect to MySQL server on \'localhost\' ([Errno 2] No such file or directory)\\")", "exception": "  File \\"/tmp/ansible_mysql_db_payload_rbuyi31j/ansible_mysql_db_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_db.py\\", line 641, in main\\n  File \\"/tmp/ansible_mysql_db_payload_rbuyi31j/ansible_mysql_db_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py\\", line 106, in mysql_connect\\n    db_connection = mysql_driver.connect(autocommit=autocommit, **config)\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/connections.py\\", line 353, in __init__\\n    self.connect()\\n  File \\"/usr/local/lib/python3.6/site-packages/pymysql/connections.py\\", line 664, in connect\\n    raise exc\\n", "invocation": {"module_args": {"name": ["mysql"], "state": "import", "target": "/tmp/reset.sql", "login_unix_socket": "/data/mysql/data/mysql.sock", "login_host": "localhost", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "encoding": "", "collation": "", "single_transaction": false, "quick": true, "ignore_tables": [], "hex_blob": false, "force": false, "master_data": 0, "skip_lock_tables": false, "use_shell": false, "unsafe_login_password": false, "restrict_config_file": false, "check_implicit_admin": false, "config_overrides_defaults": false, "login_user": null, "login_password": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "dump_extra_args": null}}}\r\n', b'Shared connection to 192.168.60.8 closed.\r\n')
<192.168.60.8> Failed to connect to the host via ssh: Shared connection to 192.168.60.8 closed.
<192.168.60.8> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.60.8> SSH: EXEC sshpass -d10 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/root/.ansible/cp/9a07ef05c1"' 192.168.60.8 '/bin/sh -c '"'"'rm -f -r /root/.ansible/tmp/ansible-tmp-1657003644.1607108-23996-145481247012138/ > /dev/null 2>&1 && sleep 0'"'"''
<192.168.60.8> (0, b'', b'')
The full traceback is:
  File "/tmp/ansible_mysql_db_payload_rbuyi31j/ansible_mysql_db_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_db.py", line 641, in main
  File "/tmp/ansible_mysql_db_payload_rbuyi31j/ansible_mysql_db_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 106, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
fatal: [192.168.60.8]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "collation": "",
            "config_file": "/root/.my.cnf",
            "config_overrides_defaults": false,
            "connect_timeout": 30,
            "dump_extra_args": null,
            "encoding": "",
            "force": false,
            "hex_blob": false,
            "ignore_tables": [],
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": "/data/mysql/data/mysql.sock",
            "login_user": null,
            "master_data": 0,
            "name": [
                "mysql"
            ],
            "quick": true,
            "restrict_config_file": false,
            "single_transaction": false,
            "skip_lock_tables": false,
            "state": "import",
            "target": "/tmp/reset.sql",
            "unsafe_login_password": false,
            "use_shell": false
        }
    },
    "msg": "unable to find /root/.my.cnf. Exception message: (2003, \"Can't connect to MySQL server on 'localhost' ([Errno 2] No such file or directory)\")"
}

RUNNING HANDLER [mysql : restart mysql] ****************************************************************************************************************************************************
task path: /home/ansible/roles/mysql/handlers/main.yml:2

PLAY RECAP *********************************************************************************************************************************************************************************
192.168.60.8               : ok=32   changed=7    unreachable=0    failed=1    skipped=5    rescued=1    ignored=0
@charnet1019
Copy link
Author

charnet1019 commented Jul 6, 2022 •
edited
Loading

Resolved.

Step 1
modify templates/init.cnf.j2 to the following content:

[mysqld]
{% if mysql_version is version('5.7.2', '>=') and mysql_daemon in ['mysqld'] %}
default_authentication_plugin = mysql_native_password
{% endif %}
skip-show-database
skip-grant-tables
datadir = {{ mysql_datadir }}
socket = {{ mysql_socket }}

Step 2
modify templates/user-my.cnf.j2 to the following content:

# {{ ansible_managed }}
  
[client]
user="{{ mysql_user_name }}"
password="{{ mysql_user_password }}"

[mysqld]
datadir = {{ mysql_datadir }}
socket = {{ mysql_socket }}

Step 3
Add the following at the beginning of tasks/configure.yml

- name: Create diretory if necessary
  file:
    path: "{{ item }}"
    state: directory
    owner: "{{ mysql_users_groups }}"
    group: "{{ mysql_users_groups }}"
  loop:
    - "{{ mysql_datadir }}"
  when:
    - mysql_datadir and mysql_datadir != None

Step 4
Add mysql_users_groups attr in defaults/main.yml

mysql_users_groups: mysql

@tgadiev tgadiev self-assigned this Jul 6, 2022
@tgadiev
Copy link
Contributor

tgadiev commented Jul 6, 2022

Great contribution @charnet1019 ! Could you create PR for that fix?

@charnet1019
Copy link
Author

Great contribution @charnet1019 ! Could you create PR for that fix?

Yes, I will verify it again, and submit a PR if there is no problem

@charnet1019 charnet1019 mentioned this issue Jul 6, 2022
Open
8 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tgadiev tgadiev

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@charnet1019 @tgadiev