Linux中无法远程连接数据库问题的解决方法
<p><span><strong>起因</strong></span></p>
<p>
今天在ubuntu16.04环境下通过mysql workbench访问远程数据库时，发现无法连接问题，解决思路及方法记录如下，不足之处，请多指教。</p>
<p>
<span><strong>问题</strong></span></p>
<p>
通过workbench输入密码访问时报这个错：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_805435">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">Your connection attempt failed </code><code class="bash keyword">for</code> <code class="bash plain">user </code><code class="bash string">'root'</code> <code class="bash plain">from your host to server at 118.89.153.162:3306: Access denied </code><code class="bash keyword">for</code> <code class="bash plain">user </code><code class="bash string">'root'</code><code class="bash plain">@</code><code class="bash string">'118.89.153.162'</code> <code class="bash plain">(using password: YES)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>思路</strong></span></p>
<ul>
<li>
网络问题，更换网络之后重启workbench和MySQL服务进行连接，依旧是这个错。</li>
<li>
密码错误，通过ssh远程登录服务器查看远程mysql的password，发现所填密码正确。</li>
<li>
远程mysql没有访问权限，一般有两种方法：改表法和授权法 ： </li>
</ul>
<p>
<strong> 授权法：</strong></p>
<p>
第一步：尝试打开/etc/mysql/my.cnf配置文件，若文件不存在或者内容为空，则打开下面的文件路径：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_702547">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">vim </code><code class="bash plain">/etc/mysql/my</code><code class="bash plain">.cnf </code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">//</code><code class="bash plain">或：</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">vim </code><code class="bash plain">/etc/mysql/mysql</code><code class="bash plain">.conf.d</code><code class="bash plain">/mysqld</code><code class="bash plain">.cnf</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
第二步：在文件中查找下面一行，如果该行前没有加#,可在该行前添加#进行注释，保存退出。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_808661">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">bind-address = 127.0.0.1</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>改表法：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_42886">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">mysql>use mysql;</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">mysql>GRANT ALL PRIVILEGES ON *.* TO </code><code class="bash string">'root'</code><code class="bash plain">@</code><code class="bash string">'%'</code> <code class="bash plain">WITH GRANT OPTION </code><code class="bash plain">//</code><code class="bash plain">赋予任何主机访问以及修改所有数据的权限</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">//</code><code class="bash plain">例如，你想root用户使用root从任何主机连接到mysql服务器的话。</code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">//GRANT</code> <code class="bash plain">ALL PRIVILEGES ON *.* TO </code><code class="bash string">'root'</code><code class="bash plain">@</code><code class="bash string">'%'</code> <code class="bash plain">IDENTIFIED BY </code><code class="bash string">'root'</code> <code class="bash plain">WITH GRANT OPTION;</code>
</div>
<div class="line number5 index4 alt2">
<code class="bash plain">//</code><code class="bash plain">如果你想允许用户root从ip为192.168.1.124的主机连接到mysql服务器，并使用123456作为密码</code>
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">//GRANT</code> <code class="bash plain">ALL PRIVILEGES ON *.* TO </code><code class="bash string">'root'</code><code class="bash plain">@</code><code class="bash string">'192.168.1.124'</code><code class="bash plain">IDENTIFIED BY </code><code class="bash string">'123456'</code> <code class="bash plain">WITH GRANT OPTION;</code>
</div>
<div class="line number7 index6 alt2">
<code class="bash plain">mysql>flush privileges </code><code class="bash plain">//</code><code class="bash plain">权限刷新，使修改立即生效</code>
</div>
<div class="line number8 index7 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">/etc/init</code><code class="bash plain">.d</code><code class="bash plain">/mysql</code> <code class="bash plain">restart </code><code class="bash plain">//</code><code class="bash plain">或: service mysql restart 重启mysql服务</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
两种方法我尝试了一下，错误类型变成了：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_151543">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">Can't connect to MySQL server on ...</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
看到这个错误我想到了远程服务器防火墙的原因，于是我有了下面的思路：</p>
<p>
4. 远程ubuntu服务器的防火墙原因导致无法本地访问远程的3306端口</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_898688">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw disable </code><code class="bash plain">//</code><code class="bash plain">关闭防火墙，该命令需在root权限下执行，慎用</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
再次通过workbench连接远程mysql，连接成功。</p>
<p>
<span><strong>原因</strong></span></p>
<p>
我的本地无法连接远程是因为远程ubuntu服务器防火墙默认禁止外部访问3306端口。</p>
<p>
<span><strong>做法</strong></span></p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_772765">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw </code><code class="bash functions">enable</code> <code class="bash plain">//</code><code class="bash plain">开启防火墙</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw allow 3306 </code><code class="bash plain">//</code><code class="bash plain">允许外部访问3306端口</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>总结</strong></span></p>
<p>
1. 一般的无法连接远程mysql问题是由上面四个愿意导致的，这里不做赘述。</p>
<p>
2. 在遇到一个问题时首先要做的不是立刻找做法解决这个问题，而应该是思考为什么会出现这个问题，产生这个问题的原因，做到对症下药。</p>
<p>
3. 自己还有很多不足，戒骄戒躁才能进步。</p>
<p>
<span><strong>附赠</strong></span></p>
<p>
Ubuntu常用ufw防火墙命令</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_766748">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">//</code><code class="bash plain">安装方法</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">apt-get </code><code class="bash functions">install</code> <code class="bash plain">ufw</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash spaces"> </code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">//</code><code class="bash plain">启用</code>
</div>
<div class="line number5 index4 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw </code><code class="bash functions">enable</code>
</div>
<div class="line number6 index5 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw default deny </code><code class="bash plain">//</code><code class="bash plain">开启了防火墙并随系统启动同时关闭所有外部对本机的访问，本机访问外部正常。</code>
</div>
<div class="line number7 index6 alt2">
</div>
<div class="line number8 index7 alt1">
<code class="bash plain">//</code><code class="bash plain">关闭</code>
</div>
<div class="line number9 index8 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw disable</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="bash plain">//</code><code class="bash plain">查看防火墙状态</code>
</div>
<div class="line number12 index11 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw status</code>
</div>
<div class="line number13 index12 alt2">
</div>
<div class="line number14 index13 alt1">
<code class="bash plain">//</code><code class="bash plain">开启/禁用相应端口或服务举例</code>
</div>
<div class="line number15 index14 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw allow 3306 </code><code class="bash plain">//</code><code class="bash plain">允许外部访问3306端口</code>
</div>
<div class="line number16 index15 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw delete allow 3306 禁止外部访问3306端口</code>
</div>
<div class="line number17 index16 alt2">
<code class="bash spaces"> </code><code class="bash functions">sudo</code> <code class="bash plain">ufw allow from 118.89.153.162 </code><code class="bash plain">//</code><code class="bash plain">允许此IP访问所有的本机端口</code>
</div>
<div class="line number18 index17 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw deny smtp </code><code class="bash plain">//</code><code class="bash plain">禁止外部访问smtp服务</code>
</div>
<div class="line number19 index18 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw delete allow smtp </code><code class="bash plain">//</code><code class="bash plain">删除上面建立的某条规则</code>
</div>
<div class="line number20 index19 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw deny proto tcp from 10.0.0.0</code><code class="bash plain">/8</code> <code class="bash plain">to 192.168.0.1 port 22 </code><code class="bash plain">//</code><code class="bash plain">要拒绝所有的TCP流量从10.0.0.0</code><code class="bash plain">/8</code> <code class="bash plain">到192.168.0.1地址的22端口</code>
</div>
<div class="line number21 index20 alt2">
</div>
<div class="line number22 index21 alt1">
<code class="bash plain">//</code><code class="bash plain">推荐使用</code>
</div>
<div class="line number23 index22 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">apt-get </code><code class="bash functions">install</code> <code class="bash plain">ufw</code>
</div>
<div class="line number24 index23 alt1">
<code class="bash functions">sudo</code> <code class="bash plain">ufw </code><code class="bash functions">enable</code>
</div>
<div class="line number25 index24 alt2">
<code class="bash functions">sudo</code> <code class="bash plain">ufw default deny</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
参考文章：http://www.jb51.net/article/230446.html</p>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。</p>
<p>
原文链接：https://www.cnblogs.com/lishanlei/p/8933269.html</p>
頁:
[1]