授予从任何 IP 地址远程访问 MySQL 数据库的权限

grant remote access of MySQL database from any IP address

提问by adit

I am aware of this command:

我知道这个命令：

GRANT ALL PRIVILEGES
ON database.*
TO 'user'@'yourremotehost'
IDENTIFIED BY 'newpassword';

But then it only allows me to grant a particular IP address to access this remote MySQL database. What if I want it so that any remote host can access this MySQL database? How do I do that? Basically I am making this database public so everyone can access it.

但是它只允许我授予一个特定的 IP 地址来访问这个远程 MySQL 数据库。如果我想要它以便任何远程主机都可以访问这个 MySQL 数据库怎么办？我怎么做？基本上我将这个数据库公开，以便每个人都可以访问它。

回答by Ariel

TO 'user'@'%'

% is a wildcard - you can also do '%.domain.com'or '%.123.123.123'and things like that if you need.

% 是通配符 -如果需要，您也可以执行'%.domain.com'或执行'%.123.123.123'类似操作。

回答by Siddharth Kumar

Enable Remote Access (Grant) Home / Tutorials / Mysql / Enable Remote Access (Grant) If you try to connect to your mysql server from remote machine, and run into error like below, this article is for you.

启用远程访问 (Grant) Home / 教程 / Mysql / 启用远程访问 (Grant) 如果您尝试从远程计算机连接到您的 mysql 服务器，并遇到如下错误，这篇文章是为您准备的。

ERROR 1130 (HY000): Host ‘1.2.3.4' is not allowed to connect to this MySQL server

ERROR 1130 (HY000): Host '1.2.3.4' is not allowed to connect to this MySQL server

Change mysql config

更改 mysql 配置

Start with editing mysql config file

从编辑mysql配置文件开始

vim /etc/mysql/my.cnf

Comment out following lines.

注释掉以下几行。

#bind-address           = 127.0.0.1
#skip-networking

If you do not find skip-networking line, add it and comment out it.

如果没有找到 skip-networking 行，请将其添加并注释掉。

Restart mysql server.

重启mysql服务器。

~ /etc/init.d/mysql restart

Change GRANT privilege

更改 GRANT 权限

You may be surprised to see even after above change you are not getting remote access or getting access but not able to all databases.

您可能会惊讶地发现，即使在上述更改之后，您也无法获得远程访问权限或获得访问权限但无法访问所有数据库。

By default, mysql username and password you are using is allowed to access mysql-server locally. So need to update privilege.

默认情况下，允许您使用的 mysql 用户名和密码在本地访问 mysql-server。所以需要更新权限。

Run a command like below to access from all machines. (Replace USERNAMEand PASSWORDby your credentials.)

运行如下命令以从所有机器访问。（用您的凭据替换USERNAME和PASSWORD。）

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Run a command like below to give access from specific IP. (Replace USERNAMEand PASSWORDby your credentials.)

运行如下命令以提供来自特定 IP 的访问权限。（用您的凭据替换USERNAME和PASSWORD。）

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

You can replace 1.2.3.4 with your IP. You can run above command many times to GRANT access from multiple IPs.

您可以用您的 IP 替换 1.2.3.4。您可以多次运行以上命令以从多个 IP 授予访问权限。

You can also specify a separate USERNAME& PASSWORDfor remote access.

您还可以为远程访问指定单独的USERNAME& PASSWORD。

You can check final outcome by:

您可以通过以下方式检查最终结果：

SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";

Finally, you may also need to run:

最后，您可能还需要运行：

mysql> FLUSH PRIVILEGES;

Test Connection

测试连接

From terminal/command-line:

从终端/命令行：

mysql -h HOST -u USERNAME -pPASSWORD

If you get a mysql shell, don't forget to run show databases; to check if you have right privileges from remote machines.

如果你有一个 mysql shell，不要忘记运行 show databases；检查您是否拥有远程机器的正确权限。

Bonus-Tip: Revoke Access

额外提示：撤销访问权限

If you accidentally grant access to a user, then better have revoking option handy.

如果您不小心授予用户访问权限，那么最好手边有撤销选项。

Following will revoke all options for USERNAME from all machines:

以下将撤销所有机器上 USERNAME 的所有选项：

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';
Following will revoke all options for USERNAME from particular IP:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';
Its better to check information_schema.user_privileges table after running REVOKE command.

If you see USAGE privilege after running REVOKE command, its fine. It is as good as no privilege at all. I am not sure if it can be revoked.

如果您在运行 REVOKE 命令后看到 USAGE 权限，那很好。这与根本没有特权一样好。我不确定它是否可以撤销。

回答by Waqas

Assuming that the above step is completed and MySql port 3306 is free to be accessed remotely; Don't forget to bind the public ip address in the mysql config file.

假设上述步骤完成，并且MySql 3306端口可以远程自由访问；不要忘记在mysql配置文件中绑定公网ip地址。

For example on my ubuntu server:

例如在我的 ubuntu 服务器上：

#nano /etc/mysql/my.cnf

In the file, search for the [mysqld]section block and add the new bind address, in this example it is 192.168.0.116. It would look something like this

在文件中，搜索[mysqld]部分块并添加新的绑定地址，在本例中为 192.168.0.116。它看起来像这样

......    
.....    
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.

bind-address        = 127.0.0.1    
bind-address        = 192.168.0.116

.....    
......

you can remove th localhost(127.0.0.1) binding if you choose, but then you have to specifically give an IP address to access the server on the local machine.

如果您愿意，您可以删除 localhost(127.0.0.1) 绑定，但是您必须专门提供一个 IP 地址才能访问本地计算机上的服务器。

Then the last step is to restart the MySql server (on ubuntu)

然后最后一步是重启MySql服务器（在ubuntu上）

stop mysql

start mysql

or #/etc/init.d/mysql restartfor other systems

或#/etc/init.d/mysql restart其他系统

Now the MySQL database can be accessed remotely by:

现在可以通过以下方式远程访问 MySQL 数据库：

mysql -u username -h 192.168.0.116 -p

回答by harishannam

Config file changes are required to enable connections via localhost.

需要更改配置文件才能通过 localhost 启用连接。

To connect through remote IPs, Login as a "root" user and run the below queries in mysql.

要通过远程 IP 连接，请以“root”用户身份登录并在 mysql 中运行以下查询。

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

This will create a new user that is accessible on localhost as well as from remote IPs.

这将创建一个可在 localhost 和远程 IP 上访问的新用户。

Also comment the below line from your my.cnf file located in /etc/mysql/my.cnf

还要评论位于 /etc/mysql/my.cnf 中的 my.cnf 文件中的以下行

bind-address = 127.0.0.1

Restart your mysql using

使用重新启动你的mysql

sudo service mysql restart

Now you should be able to connect remotely to your mysql.

现在您应该能够远程连接到您的 mysql。

回答by Dev

For anyone who fumbled with this, here is how I got to grant the privileges, hope it helps someone

对于任何对此感到困惑的人，这就是我如何授予特权的人，希望它可以帮助某人

GRANT ALL ON yourdatabasename.* TO root@'%' IDENTIFIED BY
'yourRootPassword';

As noted %is a wildcard and this will allow any IP address to connect to your database. The assumption I make here is when you connect you'll have a user named root(which is the default though). Feed in the root password and you are good to go. Note that I have no single quotes (') around the user root.

如前所述%，这是一个通配符，这将允许任何 IP 地址连接到您的数据库。我在这里所做的假设是，当您连接时，您将拥有一个名为的用户root（尽管这是默认设置）。输入 root 密码，您就可以开始了。请注意，我'在用户 root 周围没有单引号 ( )。

回答by Tomasz Czechowski

Use this command:

使用这个命令：

GRANT ALL ON yourdatabasename.* TO root@'%' IDENTIFIED BY 'yourRootPassword';

Then:

然后：

FLUSH PRIVILEGES; 

Then comment out the below line in file "/etc/mysql/mysql.conf.d/mysqld.cnf" (is required!):

然后注释掉文件“/etc/mysql/mysql.conf.d/mysqld.cnf”中的以下行（是必需的！）：

bind-address = 127.0.0.1 

Works for me!

为我工作！

回答by Imtiaz Shakil Siddique

To be able to connect with your user from any IP address, do the following:

要能够从任何 IP 地址与您的用户连接，请执行以下操作：

Allow mysql server to accept remote connections. For this open mysqld.conf file:

允许 mysql 服务器接受远程连接。对于这个打开的 mysqld.conf 文件：

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

Search for the line starting with "bind-address" and set it's value to 0.0.0.0

搜索以“bind-address”开头的行并将其值设置为 0.0.0.0

bind-address                    = 0.0.0.0

and finally save the file.

最后保存文件。

Now restart the mysql server, either with systemdor use the older servicecommand. This depends on your operating system:

现在使用systemd或使用旧service命令重新启动 mysql 服务器。这取决于您的操作系统：

sudo systemctl restart mysqld.service

Finally, mysql server is now able to accept remote connections.

最后，mysql 服务器现在能够接受远程连接。

Now we need to create a user and grant it permission, so we can be able to login with this user remotely.

现在我们需要创建一个用户并授予它权限，这样我们就可以使用这个用户远程登录了。

Connect to MySQL database as root, or any other user with root privilege.

以 root 或任何其他具有 root 权限的用户身份连接到 MySQL 数据库。

mysql -u root -p

now create desired user in both localhost and '%' wildcard and grant permissions on all DB's as such .

现在在 localhost 和 '%' 通配符中创建所需的用户，并授予对所有数据库的权限。

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

Then,

然后，

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

And finally don't forget to flush privileges

最后不要忘记刷新权限

FLUSH PRIVILEGES;

Hope this helps ;)

希望这可以帮助 ;）

回答by akhil.o.v

Run the following:

运行以下命令：

$ mysql -u root -p      
mysql> GRANT ALL ON *.* to root@'ipaddress' IDENTIFIED BY 'mysql root password';     
mysql> FLUSH PRIVILEGES;     
mysql> exit

Then attempt a connection from the IP address you specified:

然后尝试从您指定的 IP 地址建立连接：

mysql -h address-of-remove-server -u root -p   

You should be able to connect.

您应该能够连接。

回答by u5844373

You can slove the problem of MariaDB via this command:

您可以通过以下命令解决 MariaDB 的问题：

Note:

笔记：

GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'mysql root password';

%is a wildcard. In this case, it refers to all IP addresses.

%是通配符。在这种情况下，它指的是所有 IP 地址。

回答by user3846464

GRANT ALL PRIVILEGES ON *.* TO 'user'@'ipadress'