{"id":401,"date":"2021-07-23T12:29:10","date_gmt":"2021-07-23T12:29:10","guid":{"rendered":"https:\/\/ssdsunucum.com\/blog\/troubleshoot-mysql-profiles\/"},"modified":"2021-07-23T12:29:10","modified_gmt":"2021-07-23T12:29:10","slug":"troubleshoot-mysql-profiles","status":"publish","type":"post","link":"https:\/\/ssdsunucum.com\/blog\/troubleshoot-mysql-profiles\/","title":{"rendered":"Troubleshoot MySQL\u00ae Profiles"},"content":{"rendered":"<\/p>\n
\n
<\/div>\n
\n

Overview<\/h2>\n

This document describes additional steps to follow if you experience problems when you attempt to connect to your remote MySQL\u00ae server.<\/p>\n

\n
Note:<\/div>\n
\n

If you see a Can\u2019t connect to local MySQL server through socket \u2018\/var\/lib\/mysql\/mysql.sock\u2019 (2) 0<\/em> error, verify whether the mysqld<\/code> daemon is functional.<\/p>\n

    \n
  • If the daemon is functional, verify the Unix socket. To do this, run the netstat -ax | grep mysql<\/code> command.<\/li>\n
  • If you have one socket and an application points to the other socket, make a symbolic link to point it to the correct socket.<\/li>\n<\/ul><\/div>\n<\/div>\n

    Error messages<\/h2>\n

    The following describes some errors that you may encounter, their possible causes, and the solutions we recommend:<\/p>\n

    Failed to connect to remote server<\/h3>\n

    DNS error: the server cannot resolve the hostname<\/h4>\n

    Verify your DNS configuration settings.<\/p>\n

    Invalid IP address error: the IP address does not exist<\/h4>\n
      \n
    1. Confirm the local server\u2019s IP address network configuration. For more information, read our Installation Guide – Troubleshoot Your Installation.<\/li>\n
    2. Confirm the remote MySQL server\u2019s IP address.<\/li>\n
    3. Ensure that the remote MySQL server is responsive.<\/li>\n
    4. Verify the local and remote server firewall configuration settings.<\/li>\n<\/ol>\n

      Incorrect SSH port<\/h4>\n
        \n
      1. Confirm the SSH port on the remote MySQL server:\n
        \n
        \n\n\n
        \n
        1\n<\/span>2\n<\/span>3\n<\/span><\/code><\/pre>\n<\/td>\n
        		\n
        # ss -pln | grep ssh<\/span>\nLISTEN     0<\/span>      128<\/span>                      :::22                      :::*      users:((<\/span>\"sshd\"<\/span>,1163,4))<\/span>\nLISTEN     0<\/span>      128<\/span>                       *:22                       *:*      users:((<\/span>\"sshd\"<\/span>,1163,3))<\/span><\/code><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<\/div>\n<\/li>\n
      2. Enter the correct SSH port for the remote MySQL server. In the example, the correct port is 22.<\/li>\n<\/ol>\n
        Invalid SSH credentials<\/h4>\n
        Verify you have the correct username and password to access the remote MySQL server.<\/p>\n
        Unable to connect to remote MySQL host IP, connection failed with error<\/h3>\n
        Host \u2018hostname\u2019 is not allowed to connect to this MySQL server<\/h4>\n
        Insufficient MySQL permissions<\/h5>\n
        Grant the user superuser access.\n<\/p>\n
        \n
        \n\n\n
        \n
        1\n<\/span>2\n<\/span>3\n<\/span>4\n<\/span>5\n<\/span>6\n<\/span>7\n<\/span><\/code><\/pre>\n<\/td>\n
        		\n
        # mysql mysql<\/span>\n-snip-\nmysql> CREATE USER 'user'<\/span>@'hostname'<\/span> IDENTIFIED BY 'password'<\/span>;\nQuery OK, 0<\/span> rows affected (<\/span>0<\/span>.00 sec)<\/span>\n\nmysql> GRANT ALL PRIVILEGES ON *.* TO 'user'<\/span>@'hostname'<\/span> WITH GRANT OPTION;\nQuery OK, 0<\/span> rows affected (<\/span>0<\/span>.00 sec)<\/span><\/code><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<\/div>\n
        Lost connection to MySQL server at \u2018handshake: waiting for initial communication packet\u2019, system error: 110<\/h4>\n
        Firewall error. The server cannot access the specified port on the remote MySQL server.<\/h5>\n
        Verify your firewall configuration settings. For more information, read our How to Configure Your Firewall for cPanel & WHM Services documentation.<\/p>\n
        \n
        Note:<\/div>\n
        \n        The specific solution will depend on the configuration settings of the local server, remote server, and firewall utility.\n    <\/div>\n<\/div>\n
        Cannot connect to MySQL server on \u2018IP\u2019 (107)<\/h4>\n
        MySQL is down on the remote MySQL server.<\/h5>\n
        Restart MySQL on the remote MySQL server.<\/p>\n
        Incorrect MySQL port.<\/h5>\n
          \n
        1. Confirm the MySQL port on the remote MySQL server:\n
          \n
          \n\n\n
          \n
          1\n<\/span>2\n<\/span><\/code><\/pre>\n<\/td>\n
          		\n
          # ss -pln | grep mysql<\/span>\nLISTEN     0<\/span>      50<\/span>                        *:3306                     *:*      users:((<\/span>\"mysqld\"<\/span>,3308,12))<\/span><\/code><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<\/div>\n<\/li>\n
        2. Enter the correct MySQL port for the MySQL server. In the example, the correct port is 3306.<\/li>\n<\/ol>\n
          Reverse DNS entry warning<\/h2>\n
          Before you set up a remote MySQL server, ensure that the remote server can resolve your local server\u2019s hostname to its IP address. To confirm this, log in to the remote server via SSH and run the following command, where 1.2.3.4<\/code> represents the IP address:\n<\/p>\n
          \n
          host 1<\/span>.2.3.4<\/code><\/pre>\n<\/div>\n
          If you do not<\/strong> have a reverse DNS entry configured for your hostname, add an entry for your hostname to the \/etc\/hosts<\/code> file on the remote server.<\/p>\n
          For more information, read our How to Configure Reverse DNS for BIND in WHM documentation.<\/p>\n
          Remote MySQL root<\/code> password<\/h2>\n
          You can change the MySQL root<\/code> password in WHM\u2019s MySQL Root Password<\/em> interface (WHM<\/em> >> Home<\/em> >> SQL Services<\/em> >> MySQL Root Password<\/em>). If you use a remote MySQL server, you must<\/strong> choose whether you will change the local or the remote server\u2019s root<\/code> password. By default, the system selects the remote MySQL server.<\/p>\n
          \n
          Warning:<\/div>\n
          \n
          If you install MySQL 8.0 on a remote server from the community repository, the server will enable its own levels of password validation by default. These may conflict with cPanel & WHM\u2019s password validation.<\/p>\n
            \n
          • If you use the Automatically create a MySQL superuser via SSH.<\/em> option to set up the remote MySQL profile, the system will automatically make an adjustment for these different validation schemes.<\/li>\n
          • If you use the Manually enter an existing MySQL superuser\u2019s credentials<\/em> option to set up the remote MySQL profile, you will need to perform the following changes through the MySQL command line:\n
              \n
            1. Open the \/etc\/my.cnf<\/code> file with your preferred text editor.<\/li>\n
            2. Add the following lines to the file:\n
              \n
              validate-password.policy=<\/span>0<\/span>\nvalidate-password.length=<\/span>1<\/span>\nvalidate_password.special_char_count=<\/span>0<\/span>\nvalidate_password.mixed_case_count=<\/span>0<\/span>\nvalidate_password.number_count=<\/span>0<\/span>\n<\/code><\/pre>\n<\/div>\n<\/li>\n<\/ol>\n<\/li>\n<\/ul><\/div>\n<\/div>\n
              MySQL 8.0 running on a cPanel server<\/h3>\n
              When the remote MySQL server is also a cPanel & WHM server, you must<\/strong> perform additional steps:<\/p>\n
                \n
              • If you change the MySQL root<\/code> password on the hosting server, you must<\/strong> update the remote MySQL server\u2019s \/root\/.my.cnf<\/code> configuration file.<\/li>\n
              • If you change the MySQL root<\/code> password on the remote MySQL server, you must<\/strong> update the MySQL profile on the hosting server, and then reactivate the profile.<\/li>\n<\/ul>\n
                MySQL 8.0 running on any other server<\/h3>\n
                When the remote MySQL server is running on any other server, you must<\/strong> turn off password caching. Perform the following steps to avoid an authentication error:<\/p>\n
                  \n
                1. Open the \/etc\/my.cnf<\/code> with your preferred text editor.<\/li>\n
                2. Change the default-authentication-plugin<\/code> settings value to mysql_native_password<\/code>. This will resemble the following example:\n
                  \n
                  default-authentication-plugin=<\/span>mysql_native_password<\/code><\/pre>\n<\/div>\n<\/li>\n
                3. Log in to MySQL at the command line. Then, run the following command:\n
                  \n
                  mysql> ALTER USER 'root'<\/span>@'cpanel.server.host'<\/span> IDENTIFIED WITH mysql_native_password BY '12345luggage'<\/span>;<\/code><\/pre>\n<\/div>\n
                  \n
                  Important:<\/div>\n
                  \n
                  Use a secure password. A secure password is not<\/strong> a dictionary word, and it contains uppercase and lowercase letters, numbers, and symbols.<\/p>\n<\/p><\/div>\n<\/div>\n<\/li>\n<\/ol><\/div>\n","protected":false},"excerpt":{"rendered":"
                  Overview This document describes additional steps to follow if you experience problems when you attempt to connect to your remote MySQL\u00ae server. Note: If you see a Can\u2019t connect to local MySQL server through socket \u2018\/var\/lib\/mysql\/mysql.sock\u2019 (2) 0 error, verify whether the mysqld daemon is functional. If the daemon is functional, verify the Unix socket. …<\/p>\n","protected":false},"author":1,"featured_media":402,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/posts\/401"}],"collection":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/comments?post=401"}],"version-history":[{"count":0,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/posts\/401\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/media\/402"}],"wp:attachment":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/media?parent=401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/categories?post=401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/tags?post=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}