MySQL 连接到局域网中另一台PC上的mysql服务器

connecting to mysql server on another PC in LAN

提问by revolver

I have mySQL setup in a PC on my local network, how do I connect to it? I also have mySQL installed in this computer (which I want to use to connect to the database).

我在本地网络上的 PC 上安装了 mySQL，如何连接到它？我还在这台计算机上安装了 mySQL（我想用它来连接到数据库）。

I tried the following but it's not working

我尝试了以下但它不起作用

mysql -u user -h 192.168.1.28:3306 -p password
ERROR 2005 (HY000): Unknown MySQL server host '192.168.1.28:3306' (0)

EDIT: Thanks for your help. Anyway, I connect without 3306 and I have another problem. MACBOOK is the name of my client computer.

编辑：感谢您的帮助。无论如何，我在没有 3306 的情况下连接，我还有另一个问题。MACBOOK 是我的客户端计算机的名称。

mysql -u user -ppassword -h 192.168.1.28 
ERROR 1045 (28000): Access denied for user 'user'@'MACBOOK' (using password: YES)

Thanks.

谢谢。

回答by user3811169

That was a very useful question! Since we need to run the application with a centralized database, we should give the privileges to that computer in LAN to access the particular database hosted in LAN PC. Here is the solution for that!

这是一个非常有用的问题！由于我们需要使用集中式数据库运行应用程序，因此我们应该授予 LAN 中该计算机访问 LAN PC 中托管的特定数据库的权限。这是解决方案！

1. Go to MySQL server
2. Type the following code to grant access for other pc:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root_password';
   

3. then type:

   FLUSH PRIVILEGES;
   

1. 转到 MySQL 服务器
2. 键入以下代码以授予其他电脑的访问权限：

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root_password';
   

3. 然后输入：

   FLUSH PRIVILEGES;
   

Replace %with the IP you want to grant access for!

替换 %为您要授予访问权限的 IP！

回答by RolandoMySQLDBA

Since you have mysql on your local computer, you do not need to bother with the IP address of the machine. Just use localhost:

既然你本地电脑上有mysql，你就不用管机器的IP地址了。只需使用本地主机：

mysql -u user -p

or

或者

mysql -hlocalhost -u user -p

If you cannot login with this, you must find out what usernames (user@host) exist in the MySQL Server locallly. Here is what you do:

如果你不能用这个登录，你必须在本地找出 MySQL 服务器中存在哪些用户名（user@host）。这是你要做的：

Step 01) Startup mysql so that no passwords are require no passwords and denies TCP/IP connections

步骤 01) 启动 mysql 以便没有密码不需要密码并拒绝 TCP/IP 连接

service mysql restart --skip-grant-tables --skip-networking

Keep in mind that standard SQL for adding users, granting and revoking privs are disabled.

请记住，用于添加用户、授予和撤销权限的标准 SQL 被禁用。

Step 02) Show users and hosts

步骤 02) 显示用户和主机

select concat(''',user,'''@''',host,'''') userhost,password from mysql.user;

Step 03) Check your password to make sure it works

步骤 03) 检查您的密码以确保其有效

select user,host from mysql.user where password=password('YourMySQLPassword');

If your password produces no output for this query, you have a bad password.

如果您的密码没有为此查询产生任何输出，则您的密码不正确。

If your password produces output for this query, look at the users and hosts. If your host value is '%', your should be able to connect from anywhere. If your host is 'localhost', you should be able to connect locally.

如果您的密码为此查询生成输出，请查看用户和主机。如果您的主机值为 '%'，则您应该可以从任何地方连接。如果您的主机是“本地主机”，您应该能够在本地连接。

Make user you have 'root'@'localhost' defined.

让用户定义了 'root'@'localhost'。

Once you have done what is needed, just restart mysql normally

完成需要的操作后，正常重启mysql

service mysql restart

If you are able to connect successfully on the macbook, run this query:

如果您能够在 macbook 上成功连接，请运行以下查询：

SELECT USER(),CURRENT_USER();

USER()reports how you attempted to authenticate in MySQL

USER()报告您尝试在 MySQL 中进行身份验证的方式

CURRENT_USER()reports how you were allowed to authenticate in MySQL

CURRENT_USER()报告您如何被允许在 MySQL 中进行身份验证

Let us know what happens !!!

让我们知道发生了什么！！！

UPDATE 2012-02-13 20:47 EDT

更新 2012-02-13 20:47 EDT

Login to the remote server and repeat Step 1-3

登录远程服务器并重复步骤 1-3

See if any user allows remote access (i.e, host in mysql.user is '%'). If you do not, then add 'user'@'%' to mysql.user.

查看是否有任何用户允许远程访问（即 mysql.user 中的主机是 '%'）。如果不这样做，则将 'user'@'%' 添加到 mysql.user。

回答by ImtiazeA

Users who can Install MySQL Workbenchon MySQL ServerMachine

可以MySQL Workbench在MySQL Server机器上安装的用户

If you use or have MySQL Workbench on the MySQL Server PC you can do this with just a few clicks. Recommend only for development environment.

如果您在 MySQL 服务器 PC 上使用或拥有 MySQL Workbench，只需单击几下即可完成此操作。仅推荐用于开发环境。

1. Connect to MySQL Server

1. 连接到 MySQL 服务器

1. Find this option Users and Privilegesfrom Navigatorand click on it.

2. Users and Privileges从中找到此选项Navigator并单击它。

1. Select rootuser and change value for Limit to Hosts Matchingto %.

3. 选择root用户和变化值Limit to Hosts Matching来%。

1. The click Applyat the bottom.

4. Apply底部的点击。

This should enable root user to access MySQL Server from remote machine.

这应该允许 root 用户从远程机器访问 MySQL 服务器。

回答by Rohan Prabhu

Follow a simple checklist:

遵循一个简单的清单：

1. Try pinging the machine ping 192.168.1.2
2. Ensure MySQL is running on the specified port 3306i.e. it has not been modified.
3. Ensure that the other PC is not blocking inbound connections on that port. If it is, add a firewall exception to allow connections on port 3306and allow inbound connections in general.
4. It would be nice if you could post the exact error as it is displayed when you attempt to make that connection.

1. 尝试ping机器 ping 192.168.1.2
2. 确保 MySQL 在指定的端口上运行，3306即它没有被修改。
3. 确保另一台 PC 没有阻止该端口上的入站连接。如果是，请添加防火墙例外以允许端口上的3306连接并通常允许入站连接。
4. 如果您可以发布在您尝试建立该连接时显示的确切错误，那就太好了。

回答by zdrsoft

mysql -u user -h 192.168.1.2 -p

This should be enough for connection to MySQL server. Please, check the firewall of 192.168.1.2if remote connection to MySQL server is enabled.

这应该足以连接到 MySQL 服务器。请检查防火墙192.168.1.2是否启用了远程连接到 MySQL 服务器。

Regards

问候

回答by sarathkm

Connecting to any mysql database should be like this:

连接到任何 mysql 数据库应该是这样的：

$mysql -h hostname -Pportnumber -u username -p(then enter)

$mysql -h hostname -Pportnumber -u username -p（然后输入）

Then it will ask for password. Note: Port number should be closer to -P or it will show error. Make sure you know what is your mysql port. Default is 3306 and is optional to specify the port in this case. If its anything else you need to mention port number with -P or else it will show error.

然后它会要求输入密码。注意：端口号应该更接近 -P 否则会显示错误。确保您知道您的 mysql 端口是什么。默认值为 3306，在这种情况下可以选择指定端口。如果还有其他任何内容，您需要使用 -P 提及端口号，否则会显示错误。

For example: $mysql -h 10.20.40.5 -P3306 -u root -p(then enter)

例如：（ $mysql -h 10.20.40.5 -P3306 -u root -p然后输入）

Password:My_Db_Password

Gubrish about product you using.

Gubrish关于您使用的产品。

mysql>_

Note: If you are trying to connect a db at different location make sure you can ping to that server/computer.

注意：如果您尝试在不同位置连接数据库，请确保您可以 ping 到该服务器/计算机。

$ping 10.20.40.5

It should return TTL with time you got back PONG. If it says destination unreachable then you cannot connect to remote mysql no matter what.

它应该随着您返回 PONG 的时间返回 TTL。如果它说目标无法访问，那么无论如何您都无法连接到远程 mysql。

In such case contact your Network Administrator or Check your cable connection to your computer till the end of your target computer. Or check if you got LAN/WAN/MAN or internet/intranet/extranet working.

在这种情况下，请联系您的网络管理员或检查您的计算机的电缆连接，直到您的目标计算机结束。或者检查您是否有 LAN/WAN/MAN 或 Internet/Intranet/Extranet 工作。

回答by huelbois

You don't have to specify ':3306' after the IP, it's the default port for MySQL.

您不必在 IP 后指定 ':3306'，它是 MySQL 的默认端口。

And if your MySQL server runs with another port than 3306, then you have to add '-P [port]' instead of adding it to the IP address.

如果您的 MySQL 服务器使用 3306 以外的其他端口运行，那么您必须添加“-P [端口]”而不是将其添加到 IP 地址。

The MySQL client won't recognize the syntax "host:port", you HAVE to use -P [port] instead.

MySQL 客户端不会识别语法“host:port”，您必须使用 -P [port] 代替。

And btw, if you use '-p password', it won't work and will ask you the password again. You have to stick the password to the -p : -ppassword. (still, it's a very bad habit, because anyone that could do a PS on your server could see the plain password...)

顺便说一句，如果您使用“-p 密码”，它将不起作用，并且会再次询问您密码。您必须将密码粘贴到 -p 中：-ppassword。（仍然，这是一个非常糟糕的习惯，因为任何可以在您的服务器上执行 PS 的人都可以看到明文密码......）

回答by Odobenus Rosmarus

actually you shouldn't specify port in the host name. Mysql has special option for port (if port differs from default)

实际上你不应该在主机名中指定端口。Mysql 有特殊的端口选项（如果端口与默认值不同）

kind of

的种类

mysql --host=192.168.1.2 --port=3306

回答by Devart

You should use this:

你应该使用这个：

>mysql -u user -h 192.168.1.2 -P 3306 -ppassword

or this:

或这个：

>mysql -u user -h 192.168.1.2 -ppassword

...because 3306 is a default port number.

...因为 3306 是默认端口号。

mysql Options

mysql 选项

回答by comeondrupal

In Ubuntu Follow these steps:

在 Ubuntu 中执行以下步骤：

1. Set bind-address at /etc/mysql/mysql.conf.d

1. 将绑定地址设置为 /etc/mysql/mysql.conf.d

Change bind-address = 127.0.0.1to bind-address = 192.24.805.50 # your IP

更改bind-address = 127.0.0.1为bind-address = 192.24.805.50 # your IP

1. Grant permission for the remote machine

   mysql>GRANT ALL PRIVILEGES ON .TO 'root'@'[remoteip]' IDENTIFIED BY 'anypassword' WITH GRANT OPTION;

2. Then try connect from remote machine

   mysql -u root -h 192.24.805.50 -p

2. 授予远程机器的权限

   mysql>GRANT ALL PRIVILEGES ON 。TO 'root'@'[remoteip]' IDENTIFIED BY 'anypassword' WITH GRANT OPTION;

3. 然后尝试从远程机器连接

   mysql -u 根 -h 192.24.805.50 -p