blocks|key|5433996|text|这在MySQL上是默认允许的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5433997|默认情况下禁用的是远程root访问。如果要启用该功能，请在本地运行以下SQL命令：|offset|length|style|CODE|5433998|+GRANT+ALL+PRIVILEGES+ON+*.*+TO+'root'@'%25'+IDENTIFIED+BY+'password'+WITH+GRANT+OPTION;
+FLUSH+PRIVILEGES;|code-block|syntax|javascript|5433999|然后找到以下行并在my.cnf文件中将其注释掉，该文件通常位于Unix/OSX系统上的/etc/mysql/my.cnf中。在某些情况下，文件的位置是/etc/mysql/mysql.conf.d/mysqld.cnf)。|BOLD|5434000|如果是Windows系统，你可以在MySQL安装目录中找到它，通常类似于C:\Program+Files\MySQL\MySQL+Server+5.5\，文件名为my.ini。|5434001|变更线|5434002|+bind-address+=+127.0.0.1|5434003|至|5434004|+#bind-address+=+127.0.0.1|5434005|并重新启动MySQL服务器(Unix/OSX和Windows)以使更改生效。|5434006|entityMap|0|LINK|mutability|MUTABLE|url|https://superuser.com/questions/282115/how-to-restart-mysql|1|https://stackoverflow.com/questions/12972434/restart-mysql-server-on-windows-7^0|0|B|4|0|0|9|6|17|H|K|3|0|10|14|29|6|0|0|0|0|0|E|8|0|N|7|1|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1C|8|@$D|1D|E|1E|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1F|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1G|8|@$D|1H|E|1I|F|G]|$D|1J|E|1K|F|G]|$D|1L|E|1M|F|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1N|8|@$D|1O|E|1P|F|G]|$D|1Q|E|1R|F|G]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1S|8|@]|9|@]|A|$]]|$1|T|3|U|5|J|7|1T|8|@]|9|@]|A|$K|L]]|$1|V|3|W|5|6|7|1U|8|@]|9|@]|A|$]]|$1|X|3|Y|5|J|7|1V|8|@]|9|@]|A|$K|L]]|$1|Z|3|10|5|6|7|1W|8|@]|9|@$D|1X|E|1Y|1|1Z]|$D|20|E|21|1|22]]|A|$]]|$1|11|3|-4|5|6|7|23|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]|19|$5|14|15|16|A|$17|1A]]]]

That is allowed by default on MySQL.

What is disabled by default is remote <code>root</code> access. If you want to enable that, run this SQL command locally:

<pre><code> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
</code></pre>

And then find the following line and comment it out in your <code>my.cnf</code> file, which usually lives on <code>/etc/mysql/my.cnf</code> on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

If it's a Windows system, you can find it in the MySQL installation directory, usually something like <code>C:\Program Files\MySQL\MySQL Server 5.5\</code> and the filename will be <code>my.ini</code>.

Change line

<pre><code> bind-address = 127.0.0.1
</code></pre>

to

<pre><code> #bind-address = 127.0.0.1
</code></pre>

And restart the MySQL server (<a href="https://superuser.com/questions/282115/how-to-restart-mysql">Unix/OSX</a>, and <a href="https://stackoverflow.com/questions/12972434/restart-mysql-server-on-windows-7">Windows</a>) for the changes to take effect.

blocks|key|1600571|text|完成以上所有操作后，我仍然无法以root身份远程登录，但通过远程登录端口3306确认MySQL正在接受连接。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1600572|我开始查看MySQL中的用户，并注意到multiple+root+users有不同的密码。|1600573|select+user,+host,+password+from+mysql.user;|code-block|syntax|javascript|1600574|因此，在MySQL中，我再次使用set+all+the+passwords+for+root，我终于可以以root身份远程登录。|1600575|use+mysql;
update+user+set+password=PASSWORD('NEWPASSWORD')+where+User='root';
flush+privileges;|1600576|entityMap|0|LINK|mutability|MUTABLE|url|https://serverfault.com/questions/235593/mysql-log-shows-3-root-users-2-without-passwords-why|1|http://www.cyberciti.biz/faq/mysql-change-root-password/^0|G|4|10|4|16|5|0|J|J|0|0|0|4|5|1H|4|G|U|1|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|17|8|@]|D|@$9|18|A|19|1|1A]]|E|$]]|$1|H|3|I|5|J|7|1B|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@$9|1H|A|1I|1|1J]]|E|$]]|$1|O|3|P|5|J|7|1K|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|Z]]]]

After doing all of above I still couldn't login as <code>root</code> remotely, but Telnetting to port <code>3306</code> confirmed that <code>MySQL</code> was accepting connections.

I started looking at the users in MySQL and noticed there were <a href="https://serverfault.com/questions/235593/mysql-log-shows-3-root-users-2-without-passwords-why">multiple root users</a> with different passwords. 

<pre><code>select user, host, password from mysql.user;
</code></pre>

So in <code>MySQL</code> I <a href="http://www.cyberciti.biz/faq/mysql-change-root-password/" rel="noreferrer">set all the passwords for root</a> again and I could finally log in remotely as <code>root</code>.

<pre><code>use mysql;
update user set password=PASSWORD('NEWPASSWORD') where User='root';
flush privileges;
</code></pre>

blocks|key|563966|text|根据我的经验，您可以在/etc/mysql/mysql.conf.d/mysqld.cnf路径下找到配置文件。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|563967|(我挣扎了一段时间才找到这条路)|563968|entityMap^0|B|Y|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Just a note from my experience, you can find configuration file under this path <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code>.

(I struggled for some time to find this path)

blocks|key|1600741|text|请按照以下步骤为MySQL用户设置通配符远程访问。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600742|1600743|+(1)打开命令。|blockquote|1600744|1600745|(2)导航到路径C:\Program+Files\MySQL\MySQL+Server+5.X\bin，运行以下命令。|1600746|mysql+-u根-p|offset|length|style|BOLD|1600747|(3)输入root密码。|1600748|(4)执行以下命令提供权限。|1600749|将*.*上的所有权限授予'USERNAME'@'IP‘，由’PASSWORD‘标识；|1600750|用户名:您希望连接到MySQL服务器的用户名。IP:您希望允许访问MySQL服务器的公用IP地址。|1600751|PASSWORD:使用的用户名密码。|1600752|IP可替换为%25，以允许用户从任何IP地址连接。|1600753|(5)按照下面的命令和exit来刷新占用率。|1600754|刷新权限；|1600755|退出；或\q|1600756|1600757|​|1600758|📷|atomic|1600759|1600760|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/0d5eded17e91838fdae135bf0fe039b2.png|imageAlt^0|0|0|0|0|0|0|B|0|0|0|0|16|0|0|0|0|0|0|5|0|0|3|4|2|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1O|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1R|8|@$K|1S|L|1T|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1U|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1V|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1W|8|@$K|1X|L|1Y|M|N]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|20|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|21|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|22|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|23|8|@$K|24|L|25|M|N]]|9|@]|A|$]]|$1|14|3|15|5|6|7|26|8|@$K|27|L|28|M|N]|$K|29|L|2A|M|N]]|9|@]|A|$]]|$1|16|3|-4|5|6|7|2B|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|2C|8|@]|9|@]|A|$]]|$1|19|3|1A|5|1B|7|2D|8|@]|9|@$K|2E|L|2F|1|2G]]|A|$]]|$1|1C|3|18|5|6|7|2H|8|@]|9|@]|A|$]]|$1|1D|3|-4|5|6|7|2I|8|@]|9|@]|A|$]]]|1E|$1F|$5|1G|1H|1I|A|$1J|1K|1L|-4]]]]

Please follow the below mentioned steps inorder to set the wildcard remote access for MySQL User.

<blockquote>
 (1) Open cmd.
 
 (2) navigate to path C:\Program Files\MySQL\MySQL Server 5.X\bin and
 run this command.
 
 mysql -u root -p
 
 (3) Enter the root password.
 
 (4) Execute the following command to provide the permission.
 
 GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY
 'PASSWORD';
 
 USERNAME: Username you wish to connect to MySQL server. 
 
 IP: Public IP address from where you wish to allow access to MySQL
 server.
 
 PASSWORD: Password of the username used. 
 
 IP can be replaced with % to allow user to connect from any IP
 address.
 
 (5) Flush the previleges by following command and exit.
 
 FLUSH PRIVILEGES; 
 
 exit;
 or \q
</blockquote>

<a href="https://i.stack.imgur.com/8bD4d.png" rel="noreferrer"><img src="https://i.stack.imgur.com/8bD4d.png" alt="enter image description here"></a>

blocks|key|1600968|text|远程登录的所有过程。远程登录已被default.You关闭，需要为所有ip..to手动打开所有访问所有ip|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600969|GRANT+ALL+PRIVILEGES+ON+*.*+TO+'root'@'%25'+IDENTIFIED+BY+'password';|code-block|syntax|javascript|1600970|特定Ip|1600971|GRANT+ALL+PRIVILEGES+ON+*.*+TO+'root'@'your_desire_ip'+IDENTIFIED+BY+'password';|1600972|然后|1600973|flush+privileges;|1600974|您可以检查您的用户主机和密码|1600975|SELECT+host,user,authentication_string+FROM+mysql.user;|1600976|现在你的职责是改变这一点|1600977|bind-address+=+127.0.0.1|1600978|你可以在上面找到这个|1600979|sudo+nano+/etc/mysql/mysql.conf.d/mysqld.cnf|1600980|如果你在上面找不到这个，那就试试这个|1600981|sudo+nano+/etc/mysql/my.cnf|1600982|在此发表评论|1600983|#bind-address+=+127.0.0.1|1600984|然后重启Mysql|1600985|sudo+service+mysql+restart|1600986|现在享受远程登录|1600987|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1I|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1K|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1L|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1N|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1O|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1P|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1R|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1S|8|@]|9|@]|A|$]]|$1|12|3|13|5|D|7|1T|8|@]|9|@]|A|$E|F]]|$1|14|3|15|5|6|7|1U|8|@]|9|@]|A|$]]|$1|16|3|17|5|D|7|1V|8|@]|9|@]|A|$E|F]]|$1|18|3|19|5|6|7|1W|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|D|7|1X|8|@]|9|@]|A|$E|F]]|$1|1C|3|1D|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|1E|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]]|1F|$]]

All process for remote login. Remote login is off by default.You need to open it manually for all ip..to give access all ip 

<pre><code>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
</code></pre>

Specific Ip

<pre><code>GRANT ALL PRIVILEGES ON *.* TO 'root'@'your_desire_ip' IDENTIFIED BY 'password';
</code></pre>

then 

<pre><code>flush privileges;
</code></pre>

You can check your User Host &amp; Password

<pre><code>SELECT host,user,authentication_string FROM mysql.user;
</code></pre>

Now your duty is to change this 

<pre><code>bind-address = 127.0.0.1
</code></pre>

You can find this on 

<pre><code>sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

</code></pre>

if you not find this on there then try this

<pre><code>sudo nano /etc/mysql/my.cnf
</code></pre>

comment in this 

<pre><code>#bind-address = 127.0.0.1
</code></pre>

Then restart Mysql

<pre><code>sudo service mysql restart
</code></pre>

Now enjoy remote login

blocks|key|5433970|text|如果您的MySQL服务器进程仅侦听127.0.0.1或::1，则无法远程连接。如果您在/etc/my.cnf中设置了bind-address，这可能是问题的根源。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5433971|您还必须为非localhost用户添加权限。|5433972|entityMap^0|17|B|1M|C|0|6|9|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|R|8|@]|D|@]|E|$]]]|I|$]]

If your MySQL server process is listening on 127.0.0.1 or ::1 only then you will not be able to connect remotely. If you have a <code>bind-address</code> setting in <code>/etc/my.cnf</code> this might be the source of the problem.

You will also have to add privileges for a non-<code>localhost</code> user as well.

blocks|key|564054|text|如果您是从brew安装的MySQL，那么默认情况下，它实际上只侦听本地接口。要解决这个问题，您需要编辑/usr/local/etc/my.cnf并将bind-address从127.0.0.1更改为*。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|564055|然后运行brew+services+restart+mysql。|564056|entityMap^0|5|4|1F|L|22|C|2F|9|2R|1|0|4|R|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]|$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|I|$]]

If you installed MySQL from <code>brew</code> it really does only listen on the local interface by default. To fix that you need to edit <code>/usr/local/etc/my.cnf</code> and change the <code>bind-address</code> from <code>127.0.0.1</code> to <code>*</code>.

Then run <code>brew services restart mysql</code>.

blocks|key|563909|text|仅供参考我为这个问题苦恼了好几个小时..最后，我打电话给我的主机提供商，发现在我的例子中，使用的是云服务器，在1和1的控制面板中，他们有一个辅助防火墙，您必须克隆并添加端口3306。一旦添加了，我就直接进入了..|type|unstyled|depth|inlineStyleRanges|entityRanges|data|563910|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Just F.Y.I
I pulled my hair out with this problem for hours.. finally I call my hosting provider and found that in my case using a cloud server that in the control panel for 1and1 they have a secondary firewall that you have to clone and add port 3306. Once added I got straight in..

blocks|key|5434033|text|对于需要它的人，检查防火墙端口3306是否也已打开，如果您的防火墙服务正在运行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5434034|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For whom it needs it, check firewall port 3306 is open too, if your firewall service is running.

blocks|key|5434158|text|在从头开始设置MySQL时，此博客How+to+setup+a+MySQL+server+on+Local+Area+Network将非常有用|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5434159|entityMap|0|LINK|mutability|MUTABLE|url|https://medium.com/@kasunsiyambalapitiya/how-to-setup-a-mysql-server-on-local-area-network-lan-c3c5012c7d6b^0|7|5|H|1D|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

This blog <a href="https://medium.com/@kasunsiyambalapitiya/how-to-setup-a-mysql-server-on-local-area-network-lan-c3c5012c7d6b" rel="nofollow noreferrer">How to setup a MySQL server on Local Area Network</a> will be useful in setting up a <code>MySQL</code> from scratch

blocks|key|4949892|text|当我在使用MySQL服务器作为数据库的Java项目服务器上工作时，我不得不面对这个挑战。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|4949893|我是这么做的，|4949894|首先，确认您的MySQL服务器配置允许远程连接。使用首选文本编辑器打开MySQL服务器配置文件：|4949895|sudo+nano+/etc/mysql/mysql.conf.d/mysqld.cnf|code-block|syntax|javascript|4949896|向下滚动到bind-address行，确保注释掉了，或替换为0.0.0.0+(以允许所有远程连接)，或替换为您希望从其进行远程连接的Ip-Addresses。|CODE|4949897|进行必要的更改后，保存并退出配置文件。通过重新启动MySQL服务，将所做的更改应用于MySQL配置文件：|4949898|sudo+systemctl+restart+mysql|4949899|接下来，登录到安装它的服务器上的MySQL服务器控制台：|4949900|mysql+-u+root+-p|4949901|输入您的mysql用户密码|4949902|检查您想要的用户已经有权访问的主机。在我的例子中，用户是root|4949903|SELECT+host+FROM+mysql.user+WHERE+user+=+"root";|4949904|这给了我以下输出：|4949905|%2B-----------%2B
%7C+host++++++%7C
%2B-----------%2B
%7C+localhost+%7C
%2B-----------%2B|4949906|接下来，我运行以下命令，授予root用户对名为my_database的数据库的远程访问权限|4949907|USE+my_database;
GRANT+ALL+PRIVILEGES+ON+*.*+TO+'root'@'%25'+IDENTIFIED+BY+'my-password';|4949908|注意：：%25允许用户从网络上的所有主机进行远程访问。您可以使用命令-+GRANT+ALL+PRIVILEGES+ON+*.*+TO+'root'@'Ip-Address'+IDENTIFIED+BY+'my-password';指定要授予用户访问权限的各个主机的Ip地址|4949909|之后，我检查了用户现在可以访问的主机。在我的例子中，用户是root|4949910|SELECT+host+FROM+mysql.user+WHERE+user+=+"root";|4949911|4949912|%2B-----------%2B
%7C+host++++++%7C
%2B-----------%2B
%7C+%25+++++++++%7C
%7C+localhost+%7C
%2B-----------%2B|4949913|最后，您可以尝试使用以下命令从另一台服务器连接到MySQL服务器：|4949914|mysql+-u+username+-h+mysql-server-ip-address+-p|4949915|其中，u代表用户代码，h代表mysql-server-ip-address，p代表密码。所以在我的例子中是：|4949916|mysql+-u+root+-h+34.69.261.158+-p|4949917|4949918|根据您的MySQL服务器版本，您应该会得到以下输出：|4949919|Welcome+to+the+MySQL+monitor.++Commands+end+with+;+or+\g.
Your+MySQL+connection+id+is+4
Server+version:+5.7.31+MySQL+Community+Server+(GPL)

Copyright+(c)+2000,+2020,+Oracle+and/or+its+affiliates.+All+rights+reserved.

Oracle+is+a+registered+trademark+of+Oracle+Corporation+and/or+its
affiliates.+Other+names+may+be+trademarks+of+their+respective
owners.

Type+'help;'+or+'\h'+for+help.+Type+'\c'+to+clear+the+current+input+statement.

mysql>+|4949920|资源：How+to+Allow+Remote+Connections+to+MySQL|4949921|就这样。|4949922|我希望这对有帮助|4949923|entityMap|0|LINK|mutability|MUTABLE|url|https://phoenixnap.com/kb/mysql-remote-connection^0|5|5|J|P|0|0|7|0|0|1C|0|0|0|27|U|7|0|0|1G|0|0|0|S|0|0|0|D|0|0|W|S|4|0|0|0|9|0|0|0|19|E|4|N|B|0|0|0|3Q|4|1|Y|27|0|0|X|T|4|0|0|0|9|0|0|0|X|0|0|0|1I|0|0|0|D|0|0|Q|0|0|0|17|3|14|0|0|0|4|0|0|8|0^^$0|@$1|2|3|4|5|6|7|2D|8|@$9|2E|A|2F|B|C]|$9|2G|A|2H|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|2I|8|@$9|2J|A|2K|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|2L|8|@$9|2M|A|2N|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|2O|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|2P|8|@$9|2Q|A|2R|B|C]|$9|2S|A|2T|B|Q]]|D|@]|E|$]]|$1|R|3|S|5|6|7|2U|8|@$9|2V|A|2W|B|C]]|D|@]|E|$]]|$1|T|3|U|5|L|7|2X|8|@]|D|@]|E|$M|N]]|$1|V|3|W|5|6|7|2Y|8|@$9|2Z|A|30|B|C]]|D|@]|E|$]]|$1|X|3|Y|5|L|7|31|8|@]|D|@]|E|$M|N]]|$1|Z|3|10|5|6|7|32|8|@$9|33|A|34|B|C]]|D|@]|E|$]]|$1|11|3|12|5|6|7|35|8|@$9|36|A|37|B|C]|$9|38|A|39|B|Q]]|D|@]|E|$]]|$1|13|3|14|5|L|7|3A|8|@]|D|@]|E|$M|N]]|$1|15|3|16|5|6|7|3B|8|@$9|3C|A|3D|B|C]]|D|@]|E|$]]|$1|17|3|18|5|L|7|3E|8|@]|D|@]|E|$M|N]]|$1|19|3|1A|5|6|7|3F|8|@$9|3G|A|3H|B|C]|$9|3I|A|3J|B|Q]|$9|3K|A|3L|B|Q]]|D|@]|E|$]]|$1|1B|3|1C|5|L|7|3M|8|@]|D|@]|E|$M|N]]|$1|1D|3|1E|5|6|7|3N|8|@$9|3O|A|3P|B|C]|$9|3Q|A|3R|B|Q]|$9|3S|A|3T|B|Q]]|D|@]|E|$]]|$1|1F|3|1G|5|6|7|3U|8|@$9|3V|A|3W|B|C]|$9|3X|A|3Y|B|Q]]|D|@]|E|$]]|$1|1H|3|1I|5|L|7|3Z|8|@]|D|@]|E|$M|N]]|$1|1J|3|16|5|6|7|40|8|@$9|41|A|42|B|C]]|D|@]|E|$]]|$1|1K|3|1L|5|L|7|43|8|@]|D|@]|E|$M|N]]|$1|1M|3|1N|5|6|7|44|8|@$9|45|A|46|B|C]]|D|@]|E|$]]|$1|1O|3|1P|5|L|7|47|8|@]|D|@]|E|$M|N]]|$1|1Q|3|1R|5|6|7|48|8|@$9|49|A|4A|B|C]]|D|@]|E|$]]|$1|1S|3|1T|5|L|7|4B|8|@]|D|@]|E|$M|N]]|$1|1U|3|10|5|6|7|4C|8|@$9|4D|A|4E|B|C]]|D|@]|E|$]]|$1|1V|3|1W|5|6|7|4F|8|@$9|4G|A|4H|B|C]]|D|@]|E|$]]|$1|1X|3|1Y|5|L|7|4I|8|@]|D|@]|E|$M|N]]|$1|1Z|3|20|5|6|7|4J|8|@$9|4K|A|4L|B|C]]|D|@$9|4M|A|4N|1|4O]]|E|$]]|$1|21|3|22|5|6|7|4P|8|@$9|4Q|A|4R|B|C]]|D|@]|E|$]]|$1|23|3|24|5|6|7|4S|8|@$9|4T|A|4U|B|C]]|D|@]|E|$]]|$1|25|3|-4|5|6|7|4V|8|@]|D|@]|E|$]]]|26|$27|$5|28|29|2A|E|$2B|2C]]]]

I had to this challenge when working on a Java Project with MySQL server as the database.
Here's how I did it:
First, confirm that your MySQL server configuration to allow for remote connections. Use your preferred text editor to open the MySQL server configuration file:
<pre><code>sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
</code></pre>
Scroll down to the bind-address line and ensure that is either commented out or replaced with <code>0.0.0.0</code> (to allow all remote connections) or replaced with Ip-Addresses that you want remote connections from.
Once you make the necessary changes, save and exit the configuration file. Apply the changes made to the MySQL config file by restarting the MySQL service:
<pre><code>sudo systemctl restart mysql
</code></pre>
Next, log into the MySQL server console on the server it was installed:
<pre><code>mysql -u root -p
</code></pre>
Enter your mysql user password
Check the hosts that the user you want has access to already. In my case the user is <code>root</code>:
<pre><code>SELECT host FROM mysql.user WHERE user = &quot;root&quot;;
</code></pre>
This gave me this output:
<pre><code>+-----------+
| host |
+-----------+
| localhost |
+-----------+
</code></pre>
Next, I ran the command below to grant the <code>root</code> user remote access to the database named <code>my_database</code>:
<pre><code>USE my_database;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-password';
</code></pre>
Note: <code>%</code> grants a user remote access from all hosts on a network. You can specify the Ip-Address of the individual hosts that you want to grant the user access from using the command - <code>GRANT ALL PRIVILEGES ON *.* TO 'root'@'Ip-Address' IDENTIFIED BY 'my-password';</code>
Afterwhich I checked the hosts that the user now has access to. In my case the user is <code>root</code>:
<pre><code>SELECT host FROM mysql.user WHERE user = &quot;root&quot;;
</code></pre>
This gave me this output:
<pre><code>+-----------+
| host |
+-----------+
| % |
| localhost |
+-----------+
</code></pre>
Finally, you can try connecting to the MySQL server from another server using the command:
<pre><code>mysql -u username -h mysql-server-ip-address -p
</code></pre>
Where u represents user, h represents mysql-server-ip-address and p represents password. So in my case it was:
<pre><code>mysql -u root -h 34.69.261.158 -p
</code></pre>
Enter your mysql user password
You should get this output depending on your MySQL server version:
<pre><code>Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql&gt; 
</code></pre>
Resources: <a href="https://phoenixnap.com/kb/mysql-remote-connection" rel="nofollow noreferrer">How to Allow Remote Connections to MySQL</a>
That's all.
I hope this helps

blocks|key|1600637|text|对于OS用户来说，要知道绑定地址参数通常是在启动列表中设置的，而不是在my.ini文件中设置的。因此，在我的例子中，我从/Library/LaunchDaemons/homebrew.mxcl.mariadb.plist中删除了<string>--bind-address=127.0.0.1</string>。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1600638|entityMap^0|1O|1E|36|15|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

And for OS X people out there be aware that the bind-address parameter is typically set in the launchd plist and not in the my.ini file. So in my case, I removed <code>&lt;string&gt;--bind-address=127.0.0.1&lt;/string&gt;</code> from <code>/Library/LaunchDaemons/homebrew.mxcl.mariadb.plist</code>.

blocks|key|4949674|text|如果mysqld将bind+address设置为环回/本地地址(例如127.0.0.1)，则无法从远程主机访问服务器，因为无法从任何远程主机访问环回接口。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4949675|将此选项设置为0.0.0.0+(IPv4%2B6的::)以接受来自任何主机的连接，或者，如果您只想在一个接口上允许连接，则接受到另一个外部可访问地址的连接。|4949676|Source|4949677|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address|1^0|2|6|Y|9|9|C|0|0|7|7|N|2|0|0|6|1|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|15|8|@]|D|@$9|16|A|17|1|18]]|E|$]]|$1|J|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|Q]]]]

If <code>mysqld</code> has a <a href="https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address" rel="nofollow noreferrer">bind address</a> set to a loopback/local address (e.g. <code>127.0.0.1</code>), the server will not be reachable from remote hosts, because a loopback interface cannot be reached from any remote host.

Set this option to <code>0.0.0.0</code> (<code>::</code> for IPv4+6) to accept connections from any host, or to another externally-reachable address if you want to only allow connections on one interface.

<a href="https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address" rel="nofollow noreferrer">Source</a>

blocks|key|1600921|text|启用远程root访问可能很危险。如果您要设置具有更多限制性权限的用户帐户，则会更好。以下三个步骤应该可以做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600922|1600923|确保至少在my.ini或my.cnf文件中注释掉以bind-address+...开头的行。如果它不存在，继续前进。您可以在Windows.|ordered-list-item|offset|length|style|CODE|1600924|Afterwards，上的C:\ProgramData\MySQL\MySQL+Server+8.0中找到此文件。请检查要与其建立连接的用户帐户是否在Limit+to+Hosts+Matching字段中没有localhost。虽然不推荐这样做，但您可以将%25放在该字段中用于测试目的。为此，您可以使用MySQL工作台打开与服务器的本地连接，然后从菜单栏转到Server>Users和权限，并找到要连接的用户帐户。|1600925|1600926|"Limit+to+Hosts+Matching“字段禁止您进行非本地连接。即，它将所接受的连接限制为IP地址的模式。理想情况下，您应该从静态IP地址或子网访问MySQL服务器，以便您可以尽可能地加以限制。|1600927|显然，您的防火墙应该允许MySQL+|1600928|应用程序通过您想要的端口进行通信。您和服务器之间的物理网络设备应允许在要连接的端口上进行通信。(通常为端口3306+)|unordered-list-item|1600929|1600930|entityMap^0|0|0|P|G|0|D|11|2V|9|3J|1|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|Y|8|@$F|Z|G|10|H|I]]|9|@]|A|$]]|$1|J|3|K|5|E|7|11|8|@$F|12|G|13|H|I]|$F|14|G|15|H|I]|$F|16|G|17|H|I]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1B|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|V|$]]

Enabling remote root access can be dangerous. It would be preferable if you were to set up user accounts with more restrictive permissions. The following three steps should do it.

<ol>
<li>Ensure that the line starting with <code>bind-address ...</code> is at least commented out in your my.ini or my.cnf file. If it doesn't exist, move on.
You can find this file in <code>C:\ProgramData\MySQL\MySQL Server 8.0</code> on Windows.</li>
<li>Afterwards, check that the user account you are establishing the connection with does not have <code>localhost</code> in the Limit to Hosts Matching field. While it isn't recommended, you can instead put <code>%</code> in that field for testing purposes. You can do this by opening a local connection to the server with MySQL Workbench, then going to Server>Users and Privileges from the menu bar and finding the user account you want to connect with.</li>
</ol>

The "Limit to Hosts Matching" field is what disallows you to connect non-locally. I.e. it limits the accepted connections to a pattern of IP addresses. Ideally, you should be accessing the MySQL server from a static IP address or subnet, so that you can be as restrictive as possible.

<ol start="3">
<li>Obviously, your firewall should allow the MySQL Server application to communicate over the port you want. The physical networking equipment in between you and your server should allow communication on the port you want to connect with. (port 3306 typically)</li>
</ol>

blocks|key|564170|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|564171|检查远程服务器授予通配符访问端口3306的权限：|unordered-list-item|564172|564173|564174|+sudo+lsof+-i+-P+-n+%7C+grep监听|blockquote|564175|564176|564177|它不应该是这样的：|564178|564179|+mysqld+23083+mysql+21u+IPv4+145900142+0t0+TCP+127.0.0.1:3306+(监听)|offset|length|style|CODE|564180|564181|564182|在这种情况下，我们需要使用以下命令更新/etc/mysql/mysql.conf.d/mysqld.cnf或/etc/mysql/mariadb.conf.d/50-server.cnf：|564183|绑定地址=+127.0.0.1+-->+0.0.0.0|564184|然后重启mysql+"sudo+service+mysql重启“|564185|要从客户端测试mySQL连接，请执行以下操作：|564186|564187|+nc+-vz++3306|564188|564189|entityMap^0|0|0|0|0|0|0|0|0|0|1B|9|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|19|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|1A|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|1B|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|1C|7|@]|8|@]|9|$]]|$1|F|3|G|4|H|6|1D|7|@]|8|@]|9|$]]|$1|I|3|-4|4|5|6|1E|7|@]|8|@]|9|$]]|$1|J|3|-4|4|5|6|1F|7|@]|8|@]|9|$]]|$1|K|3|L|4|5|6|1G|7|@]|8|@]|9|$]]|$1|M|3|-4|4|5|6|1H|7|@]|8|@]|9|$]]|$1|N|3|O|4|H|6|1I|7|@$P|1J|Q|1K|R|S]]|8|@]|9|$]]|$1|T|3|-4|4|5|6|1L|7|@]|8|@]|9|$]]|$1|U|3|-4|4|5|6|1M|7|@]|8|@]|9|$]]|$1|V|3|W|4|5|6|1N|7|@]|8|@]|9|$]]|$1|X|3|Y|4|5|6|1O|7|@]|8|@]|9|$]]|$1|Z|3|10|4|5|6|1P|7|@]|8|@]|9|$]]|$1|11|3|12|4|5|6|1Q|7|@]|8|@]|9|$]]|$1|13|3|-4|4|5|6|1R|7|@]|8|@]|9|$]]|$1|14|3|15|4|H|6|1S|7|@]|8|@]|9|$]]|$1|16|3|-4|4|5|6|1T|7|@]|8|@]|9|$]]|$1|17|3|-4|4|5|6|1U|7|@]|8|@]|9|$]]]|18|$]]

<ul>
<li>Check remote server grant permission to Wildcard access to port 3306:</li>
</ul>
<blockquote>
sudo lsof -i -P -n | grep LISTEN
</blockquote>
It should NOT be same like this:
<blockquote>
mysqld 23083 mysql 21u IPv4 145900142 0t0 TCP
<code>127.0.0.1</code>:3306 (LISTEN)
</blockquote>
In this case, we need to update /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf with:
bind-address = 127.0.0.1 --&gt; 0.0.0.0
And then restart mysql &quot;sudo service mysql restart&quot;
In order to test mySQL connection from a client:
<blockquote>
nc -vz &lt;host_address&gt; 3306
</blockquote>

blocks|key|4949772|text|有时需要在windows上使用pc的名称|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|4949773|第一步)放入mysql的配置文件中：|4949774|4949775|+mysqld.cnf设置绑定地址=+0.0.0.0|blockquote|4949776|4949777|4949778|(允许通过tcp/ip进行接收连接)|4949779|第二步)在mysql中创建用户，表用户，使用windows属性上pc的名称，而不是ip|4949780|​|4949781|📷|atomic|4949782|4949783|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/66d51222e17059c5d029313b05e250f2.png|imageAlt^0|I|2|0|0|I|0|0|0|Q|0|0|0|0|I|0|0|T|Z|8|0|0|1|0|0|1|0|1|0|0|0|1|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]|$1|I|3|J|5|K|7|1D|8|@$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1L|8|@$9|1M|A|1N|B|C]|$9|1O|A|1P|B|C]]|D|@]|E|$]]|$1|R|3|S|5|6|7|1Q|8|@$9|1R|A|1S|B|C]]|D|@]|E|$]]|$1|T|3|U|5|V|7|1T|8|@$9|1U|A|1V|B|C]]|D|@$9|1W|A|1X|1|1Y]]|E|$]]|$1|W|3|S|5|6|7|1Z|8|@$9|20|A|21|B|C]]|D|@]|E|$]]|$1|X|3|-4|5|6|7|22|8|@]|D|@]|E|$]]]|Y|$Z|$5|10|11|12|E|$13|14|15|-4]]]]

some times need to use name of pc on windows

first step) put in config file of mysql: 

<blockquote>
 mysqld.cnf SET bind-address= 0.0.0.0
</blockquote>

(to let recibe connections over tcp/ip)

second step) make user in mysql, table users, with name of pc on windows propierties, NOT ip

<a href="https://i.stack.imgur.com/bXLab.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bXLab.png" alt="enter image description here"></a>

I have installed MySQL Community Edition 5.5 on my local machine and I want to allow remote connections so that I can connect from external source.

How can I do that?

MySQL: How to allow remote connection to mysql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我已在本地计算机上安装了MySQL社区版5.5，并且我希望允许远程连接，以便可以从外部源进行连接。我该怎么做呢？

问MySQL:如何允许远程连接mysql
EN

回答 16

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:如何允许远程连接mysqlEN

回答 16

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:如何允许远程连接mysql
EN