{"id":1914,"date":"2024-08-22T11:56:18","date_gmt":"2024-08-22T03:56:18","guid":{"rendered":"https:\/\/www.ruianding.com\/blog\/?p=1914"},"modified":"2024-08-30T11:48:31","modified_gmt":"2024-08-30T03:48:31","slug":"troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections","status":"publish","type":"post","link":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/","title":{"rendered":"Troubleshooting MySQL &#8211; The Impact of skip-grant-tables on TCP Connections"},"content":{"rendered":"\n<p>I recently encountered a interesting issue while working with MySQL on a CentOS server. The problem revolved around the skip-grant-tables option, which I enabled to reset the root password. Unexpectedly, enabling this option caused MySQL to stop listening on the default TCP port (3306), which led to an inability to connect to the server remotely. Here\u2019s a detailed breakdown of the problem, the investigation process, and the solution.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-text-color has-cyan-bluish-gray-color has-alpha-channel-opacity has-cyan-bluish-gray-background-color has-background\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Problem Description<\/strong><\/h2>\n\n\n\n<p>After enabling <code>skip-grant-tables<\/code> in the MySQL configuration to bypass the password authentication for user management tasks, I noticed that MySQL was no longer accepting connections on the default TCP port (3306). This behavior effectively limited access to the MySQL server to only those using the <strong>Unix socket<\/strong> on the local machine.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-text-color has-cyan-bluish-gray-color has-alpha-channel-opacity has-cyan-bluish-gray-background-color has-background\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Investigation<\/strong><\/h2>\n\n\n\n<p>Initially, I attempted to verify whether MySQL was listening on port 3306 using the following command:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">sudo netstat -tulnp | grep mysql<\/pre>\n\n\n\n<p>However, this command returned no output, indicating that MySQL was not listening on any TCP ports. To further investigate, I checked the Unix socket that MySQL might be using instead:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">mysqladmin -u root -p variables | grep socket<\/pre>\n\n\n\n<p>This command confirmed that MySQL was indeed using a Unix socket instead of TCP. The use of <code>skip-grant-tables<\/code> had caused MySQL to stop listening on the TCP port and rely solely on the Unix socket for connections.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-text-color has-cyan-bluish-gray-color has-alpha-channel-opacity has-cyan-bluish-gray-background-color has-background\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Root Cause<\/strong><\/h2>\n\n\n\n<p>The root cause is that enabling skip-grant-tables stops MySQL from listening on TCP ports by default. This behavior is likely a security measure, as skipping grant tables disables user authentication, which could expose the server to unauthorized access if it continues to listen on the network.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently encountered a interesting issue while working with MySQL on a CentOS server. The problem revolved around the skip-grant-tables option, which I enabled to reset the root password. Unexpectedly, enabling this option caused MySQL to stop listening on the default TCP port (3306), which led to an inability to connect to the server remotely. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[51],"tags":[],"class_list":["post-1914","post","type-post","status-publish","format-standard","hentry","category-mysql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.0 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Troubleshooting MySQL - The Impact of skip-grant-tables on TCP Connections - \u6781\u7b80IT\uff5cSimpleIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Troubleshooting MySQL - The Impact of skip-grant-tables on TCP Connections - \u6781\u7b80IT\uff5cSimpleIT\" \/>\n<meta property=\"og:description\" content=\"I recently encountered a interesting issue while working with MySQL on a CentOS server. The problem revolved around the skip-grant-tables option, which I enabled to reset the root password. Unexpectedly, enabling this option caused MySQL to stop listening on the default TCP port (3306), which led to an inability to connect to the server remotely. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\" \/>\n<meta property=\"og:site_name\" content=\"\u6781\u7b80IT\uff5cSimpleIT\" \/>\n<meta property=\"article:published_time\" content=\"2024-08-22T03:56:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-08-30T03:48:31+00:00\" \/>\n<meta name=\"author\" content=\"Ruian Ding\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ruian Ding\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\"},\"author\":{\"name\":\"Ruian Ding\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\"},\"headline\":\"Troubleshooting MySQL &#8211; The Impact of skip-grant-tables on TCP Connections\",\"datePublished\":\"2024-08-22T03:56:18+00:00\",\"dateModified\":\"2024-08-30T03:48:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\"},\"wordCount\":259,\"publisher\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\"},\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\",\"url\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\",\"name\":\"Troubleshooting MySQL - The Impact of skip-grant-tables on TCP Connections - \u6781\u7b80IT\uff5cSimpleIT\",\"isPartOf\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#website\"},\"datePublished\":\"2024-08-22T03:56:18+00:00\",\"dateModified\":\"2024-08-30T03:48:31+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.ruianding.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Troubleshooting MySQL &#8211; The Impact of skip-grant-tables on TCP Connections\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/#website\",\"url\":\"https:\/\/www.ruianding.com\/blog\/\",\"name\":\"Ruian's Tech Troubleshooting Toolbox\",\"description\":\"Debug the World.\",\"publisher\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\"},\"alternateName\":\"\u4e01\u777f\u5b89\u7684\u6280\u672f\u5206\u4eab\u535a\u5ba2\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.ruianding.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\",\"name\":\"Ruian Ding\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png\",\"contentUrl\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png\",\"width\":284,\"height\":284,\"caption\":\"Ruian Ding\"},\"logo\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/\"},\"description\":\"I am currently a Support Specialist at NIO, focusing on cloud-related issues for NIO Power. Previously, at Microsoft Entra ID, I specialized in identity and access management (IAM), including device registration, Windows Hello for Business (WHfB), multi-factor authentication (MFA), and single sign-on (SSO). In addition to my core expertise, I have a strong foundation in Active Directory, Servers, Cloud Computing, Network Administration, and Front-end Web Development. This diverse technical skill set enables me to effectively handle a wide range of challenges in a fast-paced IT environment.\",\"sameAs\":[\"https:\/\/www.ruianding.com\"],\"url\":\"https:\/\/www.ruianding.com\/blog\/author\/ruiand\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Troubleshooting MySQL - The Impact of skip-grant-tables on TCP Connections - \u6781\u7b80IT\uff5cSimpleIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/","og_locale":"en_US","og_type":"article","og_title":"Troubleshooting MySQL - The Impact of skip-grant-tables on TCP Connections - \u6781\u7b80IT\uff5cSimpleIT","og_description":"I recently encountered a interesting issue while working with MySQL on a CentOS server. The problem revolved around the skip-grant-tables option, which I enabled to reset the root password. Unexpectedly, enabling this option caused MySQL to stop listening on the default TCP port (3306), which led to an inability to connect to the server remotely. [&hellip;]","og_url":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/","og_site_name":"\u6781\u7b80IT\uff5cSimpleIT","article_published_time":"2024-08-22T03:56:18+00:00","article_modified_time":"2024-08-30T03:48:31+00:00","author":"Ruian Ding","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Ruian Ding","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/#article","isPartOf":{"@id":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/"},"author":{"name":"Ruian Ding","@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b"},"headline":"Troubleshooting MySQL &#8211; The Impact of skip-grant-tables on TCP Connections","datePublished":"2024-08-22T03:56:18+00:00","dateModified":"2024-08-30T03:48:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/"},"wordCount":259,"publisher":{"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b"},"articleSection":["MySQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/","url":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/","name":"Troubleshooting MySQL - The Impact of skip-grant-tables on TCP Connections - \u6781\u7b80IT\uff5cSimpleIT","isPartOf":{"@id":"https:\/\/www.ruianding.com\/blog\/#website"},"datePublished":"2024-08-22T03:56:18+00:00","dateModified":"2024-08-30T03:48:31+00:00","breadcrumb":{"@id":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.ruianding.com\/blog\/troubleshooting-mysql-the-impact-of-skip-grant-tables-on-tcp-connections\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.ruianding.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Troubleshooting MySQL &#8211; The Impact of skip-grant-tables on TCP Connections"}]},{"@type":"WebSite","@id":"https:\/\/www.ruianding.com\/blog\/#website","url":"https:\/\/www.ruianding.com\/blog\/","name":"Ruian's Tech Troubleshooting Toolbox","description":"Debug the World.","publisher":{"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b"},"alternateName":"\u4e01\u777f\u5b89\u7684\u6280\u672f\u5206\u4eab\u535a\u5ba2","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.ruianding.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b","name":"Ruian Ding","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png","contentUrl":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png","width":284,"height":284,"caption":"Ruian Ding"},"logo":{"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/"},"description":"I am currently a Support Specialist at NIO, focusing on cloud-related issues for NIO Power. Previously, at Microsoft Entra ID, I specialized in identity and access management (IAM), including device registration, Windows Hello for Business (WHfB), multi-factor authentication (MFA), and single sign-on (SSO). In addition to my core expertise, I have a strong foundation in Active Directory, Servers, Cloud Computing, Network Administration, and Front-end Web Development. This diverse technical skill set enables me to effectively handle a wide range of challenges in a fast-paced IT environment.","sameAs":["https:\/\/www.ruianding.com"],"url":"https:\/\/www.ruianding.com\/blog\/author\/ruiand\/"}]}},"_links":{"self":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts\/1914","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/comments?post=1914"}],"version-history":[{"count":7,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts\/1914\/revisions"}],"predecessor-version":[{"id":1922,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts\/1914\/revisions\/1922"}],"wp:attachment":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/media?parent=1914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/categories?post=1914"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/tags?post=1914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}