在MySQL中为本地服务器使用别名 [英] Using an alias for the localhost server in MySQL

问题描述

当我在办公室时，MySQL在称为"mysqldev"的特定服务器上运行.在我的/etc/hosts文件中，我设置了"mysqldev"以匹配该服务器的IP.到目前为止，一切都很好.但是，当我使用笔记本电脑不在办公室时，我想使用本地的MySQL数据库，因为我在那里克隆了特定的数据库，并且没有Internet连接到办公室的数据库.我不想更改脚本，而是要更改笔记本电脑配置.因此，在我的笔记本电脑上，我已将其设置为/etc/hosts:

When I am at the office, MySQL runs on a specific server which is called "mysqldev". In my /etc/hosts file I have set "mysqldev" to match the IP of this server. So far, so good. However, when I am out of the office using my laptop, I want to use my local MySQL database, as I clone specific databases there and have no internet to connect to the office database. I do not want to change my scripts, but rather my laptop configuration. So on my laptop I have set in /etc/hosts:

127.0.0.1 localhost mysqldev

但是，由于某些原因，这无法按预期进行.如果我使用本地主机上的服务器登录到MySQL，一切都很好.但是，当我尝试登录mysqldev服务器(与localhost相同的IP)时，出现错误消息:

However, for some reason this does not work as expected. If I log in to MySQL using the server at localhost, all good. But when I try to log in on the mysqldev server (same IP as localhost), I get the error:

Warning: mysqli::mysqli() [mysqli.mysqli]: [2002] Connection refused (trying to connect via tcp://mysqldev:3306)

(这是使用PHP，但是使用命令行却是相同的错误)

(this is using PHP, but using the command line it is the same error)

在系统上设置的用户具有在此服务器上登录的特定权限:

The users that are set up on the system have specific permission to log in on this server:

CREATE USER 'test'@'mysqldev' IDENTIFIED BY '123';

GRANT ALL PRIVILEGES ON testdb.* TO 'test'@'mysqldev';

我还关闭了防火墙进行测试，这没有什么不同.会是什么?

I have also turned off the firewall to test, and it does not make a difference. What could it be?

推荐答案

MySQL客户端库尝试猜测如何连接到数据库.当您将localhost作为主机名时，它假定您正在使用本地套接字(在计算机上搜索名为mysql.sock的文件，可能在/var/lib/mysql或/usr/local/mysql/下.

The MySQL client library tries to guess how to connect to your database. When you give localhost as the hostname, it assumes, that you're using a local socket (search your computer for a file named mysql.sock, probably under /var/lib/mysql or /usr/local/mysql/.

但是，当您使用其他任何 时(例如IP地址，其他主机名，或者您使用的是mysqldev)，它将尝试通过tcp连接到主机.从错误消息中，您的本地mysql服务器好像没有在tcp端口3306上侦听(mysql默认)

However, when you use anything else, like an ip address, a different hostname, or, in your case, mysqldev, it tries to connect to the host via tcp. From the error message, it seems as if your local mysql server is not listening on the tcp port 3306 (the mysql default)

检查您的my.cnf(可能是/etc/my.cnf或/etc/mysql/my.cnf)是否允许使用tcp.如果您找到类似

Check, if your my.cnf (probably /etc/my.cnf or /etc/mysql/my.cnf) allows the use of tcp. If you find a line like

skip-networking

评论一下:

#skip-networking

并重新启动服务器.然后重试连接.

and restart the server. Then try again to connect.

要找出您的套接字在哪里，请通过localhost连接并发出以下命令:

To find out, where your socket is, connect through localhost and issue the following command:

mysql> show global variables like '%socket%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

这篇关于在MySQL中为本地服务器使用别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！