Using an alias for the localhost server in MySQL

问题:

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

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)

(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?

回答1:

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/.

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)

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

comment it out:

#skip-networking

and restart the server. Then try again to connect.

---

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 |
+---------------+-----------------------------+

回答2:

If you are using MAMP Pro and are running into issues, you can simple uncheck the box labeled "Allow local access only" within the MySQL Server settings.