{"id":170,"date":"2024-12-12T00:57:20","date_gmt":"2024-12-11T16:57:20","guid":{"rendered":"https:\/\/www.laixuexila.com\/?p=170"},"modified":"2024-12-12T00:57:20","modified_gmt":"2024-12-11T16:57:20","slug":"php-%e4%bb%a3%e7%a0%81%e5%9c%a8%e8%bf%9e%e6%8e%a5-mysql-%e6%97%b6%e5%87%ba%e7%8e%b0%e8%ae%bf%e9%97%ae%e8%a2%ab%e6%8b%92%e7%bb%9d%ef%bc%8c%e6%94%b9%e5%a6%82%e4%bd%95%e8%a7%a3%e5%86%b3","status":"publish","type":"post","link":"https:\/\/www.laixuexila.com\/index.php\/2024\/12\/12\/php-%e4%bb%a3%e7%a0%81%e5%9c%a8%e8%bf%9e%e6%8e%a5-mysql-%e6%97%b6%e5%87%ba%e7%8e%b0%e8%ae%bf%e9%97%ae%e8%a2%ab%e6%8b%92%e7%bb%9d%ef%bc%8c%e6%94%b9%e5%a6%82%e4%bd%95%e8%a7%a3%e5%86%b3\/","title":{"rendered":"PHP \u4ee3\u7801\u5728\u8fde\u63a5 MySQL \u65f6\u51fa\u73b0\u201c\u8bbf\u95ee\u88ab\u62d2\u7edd\u201d\uff0c\u6539\u5982\u4f55\u89e3\u51b3\uff1f"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>PHP \u4ee3\u7801\u5728\u8fde\u63a5 MySQL \u65f6\u51fa\u73b0\u201c\u8bbf\u95ee\u88ab\u62d2\u7edd\u201d\u7684\u95ee\u9898\uff0c\u901a\u5e38\u4e0e\u4ee5\u4e0b\u51e0\u4e2a\u65b9\u9762\u6709\u5173\uff1a\u7528\u6237\u6743\u9650\u914d\u7f6e\u3001\u8fde\u63a5\u53c2\u6570\u8bbe\u7f6e\u3001MySQL \u670d\u52a1\u72b6\u6001\u7b49\u3002\u4ee5\u4e0b\u662f\u8be6\u7ec6\u539f\u56e0\u5206\u6790\u548c\u4fee\u590d\u65b9\u6cd5\uff1a<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>1. \u95ee\u9898\u73b0\u8c61<\/strong><\/h2>\n\n\n\n<p>\u901a\u5e38\u4f1a\u770b\u5230\u7c7b\u4f3c\u4ee5\u4e0b\u9519\u8bef\u6d88\u606f\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Warning: mysqli_connect(): (HY000\/1045): Access denied for user 'username'@'hostname' (using password: YES\/NO)<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>username<\/code>\uff1a\u5c1d\u8bd5\u767b\u5f55\u7684 MySQL \u7528\u6237\u540d\u3002<\/li>\n\n\n\n<li><code>hostname<\/code>\uff1a\u7528\u6237\u5c1d\u8bd5\u8fde\u63a5\u7684\u4e3b\u673a\u540d\u3002<\/li>\n\n\n\n<li><code>using password: YES\/NO<\/code>\uff1a\u662f\u5426\u4f7f\u7528\u4e86\u5bc6\u7801\u3002<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>2. \u5e38\u89c1\u539f\u56e0\u53ca\u4fee\u590d\u65b9\u6cd5<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\uff081\uff09<strong>\u7528\u6237\u540d\u6216\u5bc6\u7801\u9519\u8bef<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1aPHP \u4f7f\u7528\u7684\u7528\u6237\u540d\u6216\u5bc6\u7801\u4e0d\u6b63\u786e\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u786e\u8ba4\u6570\u636e\u5e93\u7528\u6237\u662f\u5426\u5b58\u5728\uff1a<br><code>sql SELECT user, host FROM mysql.user;<\/code><\/li>\n\n\n\n<li>\u68c0\u67e5\u662f\u5426\u4e3a\u8be5\u7528\u6237\u8bbe\u7f6e\u4e86\u6b63\u786e\u7684\u5bc6\u7801\uff1a<br><code>sql ALTER USER 'username'@'hostname' IDENTIFIED BY 'correct_password';<\/code><\/li>\n\n\n\n<li>\u786e\u8ba4 PHP \u4e2d\u7684\u8fde\u63a5\u4ee3\u7801\u662f\u5426\u6b63\u786e\uff1a <code>&lt;?php $servername = \"localhost\"; $username = \"username\"; $password = \"correct_password\"; $dbname = \"database_name\"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die(\"Connection failed: \" . $conn->connect_error); } echo \"Connected successfully\"; ?><\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\uff082\uff09<strong>\u7528\u6237\u6ca1\u6709\u6743\u9650\u8fde\u63a5<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1a\u6570\u636e\u5e93\u7528\u6237\u7684\u6743\u9650\u4e0d\u5141\u8bb8\u4ece\u6307\u5b9a\u4e3b\u673a\u8fde\u63a5\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u67e5\u770b\u7528\u6237\u6743\u9650\uff1a<br><code>sql SHOW GRANTS FOR 'username'@'hostname';<\/code><\/li>\n\n\n\n<li>\u5982\u679c\u6743\u9650\u4e0d\u8db3\uff0c\u6388\u4e88\u5fc5\u8981\u6743\u9650\uff1a<br><code>sql GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname'; FLUSH PRIVILEGES;<\/code><\/li>\n\n\n\n<li>\u5982\u679c\u7528\u6237\u9700\u8981\u8fdc\u7a0b\u8bbf\u95ee\uff0c\u786e\u4fdd\u4f7f\u7528\u4e86\u6b63\u786e\u7684\u4e3b\u673a\u540d <code>%<\/code>\uff1a<br><code>sql CREATE USER 'username'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'; FLUSH PRIVILEGES;<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\uff083\uff09<strong>\u4e3b\u673a\u540d\u8bbe\u7f6e\u9519\u8bef<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1aPHP \u4e2d\u7684 <code>$servername<\/code> \u503c\u4e0e MySQL \u914d\u7f6e\u4e0d\u5339\u914d\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u786e\u8ba4 MySQL \u670d\u52a1\u8fd0\u884c\u7684\u4e3b\u673a\u540d\uff1a\n<ul class=\"wp-block-list\">\n<li>\u672c\u5730\u8fde\u63a5\uff1a\u4f7f\u7528 <code>localhost<\/code> \u6216 <code>127.0.0.1<\/code>\u3002<\/li>\n\n\n\n<li>\u8fdc\u7a0b\u8fde\u63a5\uff1a\u4f7f\u7528 MySQL \u670d\u52a1\u5668\u7684 IP \u5730\u5740\u6216\u4e3b\u673a\u540d\u3002<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u4fee\u6539 PHP \u4ee3\u7801\uff1a<br><code>php $servername = \"127.0.0.1\"; \/\/ \u672c\u5730\u8fde\u63a5<\/code><\/li>\n\n\n\n<li>\u5982\u679c\u4f7f\u7528 UNIX \u5957\u63a5\u5b57\u8fde\u63a5\uff0c\u786e\u4fdd\u6b63\u786e\u8bbe\u7f6e\u5957\u63a5\u5b57\u8def\u5f84\uff1a<br><code>php $servername = \"localhost\"; $socket = \"\/var\/run\/mysqld\/mysqld.sock\"; \/\/ \u5957\u63a5\u5b57\u8def\u5f84 $conn = new mysqli($servername, $username, $password, $dbname, null, $socket);<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\uff084\uff09<strong>MySQL \u670d\u52a1\u672a\u542f\u52a8<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1aMySQL \u670d\u52a1\u672a\u8fd0\u884c\u6216\u7f51\u7edc\u7aef\u53e3\u88ab\u963b\u6b62\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u68c0\u67e5 MySQL \u670d\u52a1\u72b6\u6001\uff1a<br><code>bash systemctl status mysql<\/code><br>\u6216<br><code>bash service mysql status<\/code><\/li>\n\n\n\n<li>\u542f\u52a8 MySQL \u670d\u52a1\uff1a<br><code>bash sudo systemctl start mysql<\/code><\/li>\n\n\n\n<li>\u786e\u8ba4 MySQL \u6b63\u5728\u76d1\u542c\u6b63\u786e\u7684\u7aef\u53e3\uff08\u9ed8\u8ba4\u662f <code>3306<\/code>\uff09\uff1a<br><code>bash netstat -tln | grep 3306<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\uff085\uff09<strong>\u9632\u706b\u5899\u963b\u6b62\u8fde\u63a5<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1a\u9632\u706b\u5899\u6216\u7f51\u7edc\u7b56\u7565\u963b\u6b62\u4e86 MySQL \u7684\u8fdc\u7a0b\u8bbf\u95ee\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u5728 MySQL \u670d\u52a1\u5668\u4e0a\uff0c\u786e\u4fdd 3306 \u7aef\u53e3\u5f00\u653e\uff1a<br><code>bash sudo ufw allow 3306<\/code><\/li>\n\n\n\n<li>\u5982\u679c\u4f7f\u7528 AWS\u3001\u963f\u91cc\u4e91\u7b49\u4e91\u670d\u52a1\uff0c\u68c0\u67e5\u5b89\u5168\u7ec4\u89c4\u5219\uff0c\u786e\u4fdd\u5141\u8bb8\u5165\u7ad9 TCP 3306\u3002<\/li>\n\n\n\n<li>\u6d4b\u8bd5\u8fde\u63a5\uff1a<br><code>bash telnet server_ip 3306<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\uff086\uff09<strong>MySQL \u914d\u7f6e\u6587\u4ef6\u95ee\u9898<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1aMySQL \u914d\u7f6e\u6587\u4ef6 <code>my.cnf<\/code> \u9650\u5236\u4e86\u7528\u6237\u8fde\u63a5\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u7f16\u8f91 MySQL \u914d\u7f6e\u6587\u4ef6\uff1a<br><code>bash sudo nano \/etc\/mysql\/my.cnf<\/code><br>\u6216<br><code>bash sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/li>\n\n\n\n<li>\u786e\u4fdd <code>[mysqld]<\/code> \u90e8\u5206\u6ca1\u6709\u4ee5\u4e0b\u9650\u5236\uff1a<br><code>ini bind-address = 127.0.0.1<\/code><br>\u5982\u679c\u9700\u8981\u8fdc\u7a0b\u8fde\u63a5\uff0c\u53ef\u4ee5\u8bbe\u7f6e\u4e3a\uff1a<br><code>ini bind-address = 0.0.0.0<\/code><\/li>\n\n\n\n<li>\u91cd\u542f MySQL \u670d\u52a1\uff1a<br><code>bash sudo systemctl restart mysql<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\uff087\uff09<strong>PHP \u6269\u5c55\u672a\u5b89\u88c5\u6216\u914d\u7f6e\u9519\u8bef<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u539f\u56e0<\/strong>\uff1aPHP \u7f3a\u5c11 MySQL \u6269\u5c55\u6216\u914d\u7f6e\u9519\u8bef\u3002<\/li>\n\n\n\n<li><strong>\u4fee\u590d<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u68c0\u67e5\u662f\u5426\u5b89\u88c5\u4e86 MySQL \u6269\u5c55\uff1a<br><code>bash php -m | grep mysqli<\/code><\/li>\n\n\n\n<li>\u5982\u679c\u672a\u5b89\u88c5\uff0c\u5b89\u88c5 <code>mysqli<\/code> \u6216 <code>pdo_mysql<\/code> \u6269\u5c55\uff1a<br><code>bash sudo apt install php-mysqli sudo systemctl restart apache2<\/code><\/li>\n\n\n\n<li>\u5728 PHP \u4e2d\u542f\u7528\u6269\u5c55\uff1a<br>\u7f16\u8f91 <code>php.ini<\/code> \u6587\u4ef6\uff0c\u786e\u4fdd\u4ee5\u4e0b\u884c\u672a\u88ab\u6ce8\u91ca\uff1a<br><code>ini extension=mysqli<\/code><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>3. \u68c0\u67e5\u5e76\u9a8c\u8bc1\u8fde\u63a5<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\uff081\uff09\u4f7f\u7528 CLI \u6d4b\u8bd5\u8fde\u63a5<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5728\u547d\u4ee4\u884c\u4e2d\u6d4b\u8bd5\u6570\u636e\u5e93\u8fde\u63a5\u662f\u5426\u6b63\u5e38\uff1a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>  mysql -u username -p -h hostname -P 3306<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\uff082\uff09\u5728 PHP \u4e2d\u6d4b\u8bd5\u8fde\u63a5<\/h3>\n\n\n\n<p>\u521b\u5efa\u4e00\u4e2a\u7b80\u5355\u7684\u6d4b\u8bd5\u6587\u4ef6 <code>test_connection.php<\/code>\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;?php\n$servername = \"localhost\";\n$username = \"username\";\n$password = \"password\";\n\n$conn = new mysqli($servername, $username, $password);\n\nif ($conn-&gt;connect_error) {\n    die(\"Connection failed: \" . $conn-&gt;connect_error);\n}\necho \"Connected successfully\";\n?&gt;<\/code><\/pre>\n\n\n\n<p>\u8fd0\u884c\u6b64\u811a\u672c\uff0c\u786e\u8ba4\u662f\u5426\u6210\u529f\u8fde\u63a5\u3002<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>4. \u603b\u7ed3<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u9010\u6b65\u6392\u67e5<\/strong>\uff1a\u4ece\u7528\u6237\u540d\u3001\u5bc6\u7801\u3001\u4e3b\u673a\u540d\u5230 MySQL \u670d\u52a1\u72b6\u6001\uff0c\u9010\u4e00\u68c0\u67e5\u3002<\/li>\n\n\n\n<li><strong>\u6ce8\u610f\u6743\u9650<\/strong>\uff1a\u786e\u4fdd\u7528\u6237\u5177\u6709\u6b63\u786e\u7684\u6743\u9650\u3002<\/li>\n\n\n\n<li><strong>\u68c0\u67e5\u7f51\u7edc<\/strong>\uff1a\u9632\u706b\u5899\u548c\u7aef\u53e3\u914d\u7f6e\u662f\u5426\u6b63\u786e\u3002<\/li>\n\n\n\n<li><strong>\u4f18\u5316\u914d\u7f6e<\/strong>\uff1a\u786e\u4fdd MySQL \u548c PHP \u7684\u914d\u7f6e\u6587\u4ef6\u5339\u914d\u3002<\/li>\n<\/ul>\n\n\n\n<p>\u4ee5\u4e0a\u4e3a\u5173\u4e8e\u89e3\u51b3PHP \u4ee3\u7801\u5728\u8fde\u63a5 MySQL \u65f6\u51fa\u73b0\u201c\u8bbf\u95ee\u88ab\u62d2\u7edd\u201d\u7684\u8be6\u7ec6\u4ecb\u7ecd\uff0c\u66f4\u591a\u4fe1\u606f\u8bf7\u5173\u6ce8\u5176\u4ed6\u76f8\u5173\u6587\u7ae0\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PHP \u4ee3\u7801\u5728\u8fde\u63a5 MySQL \u65f6\u51fa\u73b0\u201c\u8bbf\u95ee\u88ab\u62d2\u7edd\u201d\u7684\u95ee\u9898\uff0c\u901a\u5e38\u4e0e\u4ee5\u4e0b\u51e0\u4e2a\u65b9\u9762\u6709\u5173\uff1a\u7528\u6237\u6743\u9650\u914d\u7f6e\u3001\u8fde\u63a5\u53c2\u6570\u8bbe\u7f6e [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-170","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/comments?post=170"}],"version-history":[{"count":1,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/170\/revisions"}],"predecessor-version":[{"id":171,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/170\/revisions\/171"}],"wp:attachment":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/media?parent=170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/categories?post=170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/tags?post=170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}