{"id":5414,"date":"2023-03-26T08:00:00","date_gmt":"2023-03-26T00:00:00","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5414"},"modified":"2023-03-22T20:02:22","modified_gmt":"2023-03-22T12:02:22","slug":"%e4%bb%8emysql%e5%91%bd%e4%bb%a4%e4%b8%ad%e6%8f%90%e5%8f%96%e7%9b%b8%e5%85%b3%e4%bf%a1%e6%81%af","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5414.html","title":{"rendered":"\u4ecemysql\u547d\u4ee4\u4e2d\u63d0\u53d6\u76f8\u5173\u4fe1\u606f"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u7b80\u5355\u6574\u7406\u4e00\u4e0b\u6700\u8fd1\u5728\u505a\u7684mysql\u547d\u4ee4\u4fe1\u606f\u63d0\u53d6\u7684\u5185\u5bb9\uff0c\u65b9\u4fbf\u540e\u9762\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\uff08\u7b80\u5355\u80fd\u60f3\u5230\u7684\u5c31\u662f\u5176\u4ed6\u5ba2\u6237\u7aef\u8fde\u63a5\u547d\u4ee4\u7684\u89e3\u6790\u548c\u63d0\u53d6\uff09\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<p>\u901a\u7528\u6027\u7684\u5927\u4f53\u6d41\u7a0b\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u5148\u8bfb\u5b98\u65b9\u7684manual\u6587\u6863\uff0c\u770b\u6709\u54ea\u4e9b\u5178\u578b\u9009\u9879\u548c\u5199\u6cd5\uff1b<\/li>\n\n\n\n<li>\u518d\u5bf9\u771f\u5b9e\u6570\u636e\u8fdb\u884c\u62bd\u6837\uff0c\u770b\u770b\u80fd\u6b63\u786e\u63d0\u53d6\u5230\u54ea\u4e9b\u4fe1\u606f\uff1b<\/li>\n\n\n\n<li>\u5bf9\u65e0\u6cd5\u63d0\u53d6\/\u63d0\u53d6\u9519\u8bef\u7684\u65e5\u5fd7\u8fdb\u884c\u9488\u5bf9\u6027\u5206\u6790\uff0c\u4f18\u5316\u6539\u8fdb\u63d0\u53d6\u903b\u8f91\uff1b<\/li>\n\n\n\n<li>\u505a\u4e00\u4e2a\u7ed3\u679c\u7edf\u8ba1\u5206\u6790\u7684\u529f\u80fd\uff0c\u8bc4\u4f30\u4e00\u4e0b\u63d0\u53d6\u903b\u8f91\u7684\u6709\u6548\u6027\u548c\u5c40\u9650\u6027\uff0c\u7528\u6570\u636e\u6307\u5bfc\u540e\u9762\u7684\u64cd\u4f5c\u51b3\u7b56\u3002<\/li>\n<\/ol>\n\n\n\n<h6 class=\"wp-block-heading\">\u9605\u8bfbmysql manual\u6587\u6863<\/h6>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4ece mysql \u7684\u547d\u4ee4\u4e2d\u63d0\u53d6 user\/password\/host\/port\/database \u7b49\u4fe1\u606f\u3002\n$ man mysql\n...\n\u2022   --user=user_name, -u user_name\n\u2022   --password&#91;=password], -p&#91;password]\n\u2022   --host=host_name, -h host_name\n\u2022   --port=port_num, -P port_num\n\u2022   --database=db_name, -D db_name The database to use. This is useful primarily in an option file.\n\n$ man mysqldump\n...\nNAME\n   mysqldump - a database backup program\n\nSYNOPSIS\n   mysqldump &#91;options] &#91;db_name &#91;tbl_name ...]]\n\n   \u2022   --user=user_name, -u user_name The user name of the MySQL account to use for connecting to the server.\n   \u2022   --host=host_name, -h host_name Dump data from the MySQL server on the given host. The default host is localhost.\n   \u2022   --port=port_num, -P port_num For TCP\/IP connections, the port number to use.\n...<\/code><\/pre>\n\n\n\n<h6 class=\"wp-block-heading\">\u7b80\u5355\u8bd5\u8bd5\u770b\u6548\u679c<\/h6>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql\nERROR 1045 (28000): Access denied for user '$(id -un)'@'localhost' (using password: NO)\n$\n$ mysql -uroot\nERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)\n$\n$ mysql -uroot -p\nEnter password:\nERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)\n$\n$ mysql -uroot -p\nEnter password:\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 12\nServer version: 8.0.28 Homebrew\n...\n$ mysql -uroot -p -P3306\n$ mysql -uroot -p -P 3306\n$ mysql -uroot -p --port 3306\n$ mysql -uroot -p --port    3306\n$ mysql -uroot -p --port=3306\n$ mysql -uroot -p --port=  3306\nmysql: &#91;ERROR] mysql: Empty value for 'port' specified.\n$\n$ mysql -uroot -p --port3306\nmysql: &#91;ERROR] unknown option '--port3306'.\n$<\/code><\/pre>\n\n\n\n<h6 class=\"wp-block-heading\">\u7528SQL\u8fdb\u884c\u5b57\u7b26\u4e32\u5904\u7406\u4ee5\u63d0\u53d6\u76f8\u5173\u4fe1\u606f<\/h6>\n\n\n\n<pre class=\"wp-block-code\"><code># \u56e0\u4e3abash\u65e5\u5fd7\u53ea\u662f\u8bb0\u5f55\u4e86\u547d\u4ee4\uff0c\u4f46\u5e76\u672a\u8bb0\u5f55\u547d\u4ee4\u7684\u6267\u884c\u72b6\u6001\uff08\u6210\u529f\/\u5931\u8d25\uff09\uff0c\u6240\u4ee5\u4e0b\u9762\u7684\u5355\u7eaf\u57fa\u4e8e\u6267\u884c\u547d\u4ee4\u5b57\u7b26\u4e32\u505a\u5b50\u4e32\u63d0\u53d6\u53ef\u80fd\u4f1a\u62ff\u5230\u9519\u8bef\/\u5b9e\u9645\u4e0d\u53ef\u7528\u7684\u7ed3\u679c\u3002\n# \u6bd4\u5982 m_port \u53d6\u5230\u7684\u7ed3\u679c\u4e0d\u662f\u6570\u5b57\u683c\u5f0f\u7684\u5b57\u7b26\u4e32\u3002\n,case\nwhen cmd like '%--host%' then split(split(cmd, '--host=')&#91;1], ' ')&#91;0]\nwhen cmd like '% -h%' then split(trim(split(cmd, ' -h')&#91;1]), ' ')&#91;0]\nelse 'default'\nend as m_host\n\n,case\nwhen cmd like '%--port%' then split(split(cmd, '--port=')&#91;1], ' ')&#91;0]\nwhen cmd like '% -P%' then split(trim(split(cmd, ' -P')&#91;1]), ' ')&#91;0]\nelse 'default'\nend as m_port\n\n,case\nwhen cmd like '%--user%' then split(split(cmd, '--user=')&#91;1], ' ')&#91;0]\nwhen cmd like '% -u%' then split(trim(split(cmd, ' -u')&#91;1]), ' ')&#91;0]\nelse 'default'\nend as m_user\n\n,case\nwhen cmd like '%--password%' then split(split(cmd, '--password=')&#91;1], ' ')&#91;0]\nwhen cmd like '% -p%' then split(trim(split(cmd, ' -p')&#91;1]), ' ')&#91;0]\nelse 'default'\nend as m_pass\n\n-- \u4ecemysql\u547d\u4ee4\u4e2d\u63d0\u53d6\u51fa\u7684\u4fe1\u606f\u53ef\u80fd\u6709\u4e9b\u810f\u6570\u636e\u9700\u8981\u8fc7\u6ee4\uff0c\u8fd9\u91cc\u4ee5\u63d0\u53d6\u7684\u660e\u6587\u5bc6\u7801\u4e3a\u4f8b\u8fdb\u884c\u8bf4\u660e\uff1a\n-- (m_pass not in ('','default') and m_pass not like '-%')\n\n,case\nwhen cmd like '%--database%' then split(split(cmd, '--database=')&#91;1], ' ')&#91;0]\nwhen cmd like '% -D%' then split(trim(split(cmd, ' -D')&#91;1]), ' ')&#91;0]\nelse 'xdefaultx'\nend as m_db<\/code><\/pre>\n\n\n\n<h6 class=\"wp-block-heading\">\u5bf9\u63d0\u53d6\u7ed3\u679c\u8fdb\u884c\u8bc4\u4f30\u5206\u6790<\/h6>\n\n\n\n<pre class=\"wp-block-code\"><code># \u5148\u5bf9\u63d0\u53d6\u5b57\u6bb5\u8fdb\u884c\u6807\u7b7e\u5316\u5904\u7406\uff0c\u540e\u9762\u518d\u57fa\u4e8e\u4e0d\u540c\u7ef4\u5ea6\u8fdb\u884c group by \u4ee5\u7edf\u8ba1\u4e0d\u540c\u6807\u7b7e\u5728\u6574\u4f53\u6570\u636e\u4e2d\u7684\u5360\u6bd4\u60c5\u51b5\n\n,if(m_host in ('127.0.0.1','localhost'),1,0) as m_host_local\n\n,if(m_port rlike '^(\\\\d+){1,}$',1,0) as m_port_numeric\n\n,if(m_pass not in ('','default') and m_pass not like '-%',1,0) as m_pass_exists<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p>Connecting to the MySQL Server Using Command Options<br><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/connecting.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/connecting.html<\/a><\/p>\n\n\n\n<p>Command Options for Connecting to the Server<br><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/connection-options.html\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/connection-options.html<\/a><\/p>\n\n\n\n<p>Tired of manually extracting login credentials from mysql connection strings? Look no further!<br><a href=\"https:\/\/www.npmjs.com\/package\/mysql-parse\">https:\/\/www.npmjs.com\/package\/mysql-parse<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u7b80\u5355\u6574\u7406\u4e00\u4e0b\u6700\u8fd1\u5728\u505a\u7684mysql\u547d\u4ee4\u4fe1\u606f\u63d0\u53d6\u7684\u5185\u5bb9\uff0c\u65b9\u4fbf\u540e\u9762\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\uff08\u7b80\u5355\u80fd\u60f3 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7],"tags":[83,16,17,1262],"class_list":["post-5414","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","tag-bash","tag-mysql","tag-mysqldump","tag-sql"],"views":1883,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5414","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=5414"}],"version-history":[{"count":1,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5414\/revisions"}],"predecessor-version":[{"id":5415,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5414\/revisions\/5415"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}