CertGrid CertGrid
Troubleshooting·MySQL

MySQL Local and Remote Connections

A connection from another host fails twice, with two different errors and two different causes: 2003 because the server is not listening, then 1130 because the account does not exist for that host. Fixing the first does nothing for the second.

Foundations Guide 4 of 45 Intermediate

Written against the versions above. `bind-address` lives in `/etc/mysql/mysql.conf.d/mysqld.cnf` on Debian and Ubuntu. On RPM builds the same setting is in `/etc/my.cnf`.

db-a01 runs the server. db-util01 is the remote client, and the only way to prove a remote connection is to make one from another host.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB
db-util01192.168.0.84Ubuntu 26.04 LTSClient, Backup Target and Monitoring2 Core4 GB50 GB

Before you start

  1. Read the setting that decides everything

    Two views of one value: what the running server has, and what the file says.

    bind_address is 127.0.0.1. Ubuntu sets it explicitly on line 31 of mysqld.cnf, and the running server agrees. This is not a firewall rule and no firewall change will alter it - the server simply never binds to an address another machine could reach.

    bash Example session 
    sudo mysql --table -e "SHOW VARIABLES LIKE 'bind_address'"+---------------+-----------+| Variable_name | Value     |+---------------+-----------+| bind_address  | 127.0.0.1 |+---------------+-----------+grep -n "bind-address" /etc/mysql/mysql.conf.d/mysqld.cnf31:bind-address		= 127.0.0.132:mysqlx-bind-address	= 127.0.0.1

    Expected resultbind_address = 127.0.0.1, and line 31 of the config setting it.

    Success conditionYou know why a remote client will fail before you try one.

  2. Try root over TCP, and watch socket authentication refuse

    -h 127.0.0.1 looks like the same machine, and in network terms it is. To MySQL it is a different thing entirely: naming a host makes the client use TCP, where naming nothing (or localhost) uses the unix socket.

    ERROR 1698, not 1045. The account was found and the connection reached the server; the authentication method failed. auth_socket reads the operating system user from the socket itself, and a TCP connection carries no such thing.

    This is the practical limit of socket authentication: it is unspoofable, and it only works from the local machine.

    bash Example session 
    sudo mysql -h 127.0.0.1 -e "SELECT 1" ; echo "exit=$?"ERROR 1698 (28000): Access denied for user 'root'@'localhost'exit=1

    Expected resultERROR 1698 (28000): Access denied for user 'root'@'localhost' and exit=1.

    Success conditionYou have seen that root cannot authenticate over TCP, even locally.

  3. Confirm a password account works over both

    The same two transports as appuser, which uses caching_sha2_password rather than auth_socket.

    Both return exit=0. A password travels fine over either transport, which is exactly why the account you use from another machine has to be a password account.

    The literal strings in the results are labels this guide chose, not a report from the server - the proof of which transport was used is the Connection: line in STATUS, and the 1698 above.

    bash Example session 
    mysql -u appuser -p'S7rong-Pass!2026' -h localhost -e "SELECT 'socket path' AS via" ; echo "exit=$?"viasocket pathexit=0mysql -u appuser -p'S7rong-Pass!2026' -h 127.0.0.1 -e "SELECT 'tcp path' AS via" ; echo "exit=$?"viatcp pathexit=0

    Expected resultBoth succeed with exit=0.

    Success conditionYou know a password account is not restricted to one transport.

  4. Try it for real from another machine

    db-util01 gets the client package - it needs no server - and then attempts the connection that a real application would make.

    ERROR 2003 ... (111). The number in brackets is the operating system error: 111 is ECONNREFUSED. Nothing was listening. This is a network-layer refusal and the server never saw the attempt - there is no MySQL account involved, no password checked and nothing in the server log.

    The distinction matters because the fix is a server setting, not a grant.

    bash Example session 
    sudo apt-get install -y mysql-clientThe following packages were automatically installed and are no longer required:  linux-headers-7.0.0-14 linux-headers-7.0.0-14-generic  linux-image-unsigned-7.0.0-14-generic  linux-main-modules-zfs-7.0.0-14-generic linux-modules-7.0.0-14-generic  linux-tools-7.0.0-14 linux-tools-7.0.0-14-genericUse 'sudo apt autoremove' to remove them.mysql --versionmysql  Ver 8.4.10-0ubuntu0.26.04.1 for Linux on x86_64 ((Ubuntu))mysql -u appuser -p'S7rong-Pass!2026' -h 192.168.0.81 -e "SELECT 1" ; echo "exit=$?"ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.81:3306' (111)exit=1

    Expected resultThe client installs, then ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.81:3306' (111) and exit=1.

    Success conditionYou have reproduced the first failure and know it is a listening problem.

  5. Make the server listen on the network

    One setting, then a restart - bind-address is read at startup and cannot be changed on a running server.

    Check the ss output carefully. Port 3306 has moved to 0.0.0.0, but 33060 is still on 127.0.0.1: mysqlx-bind-address on line 32 is a separate setting and was not touched. Two ports, two settings.

    0.0.0.0 means every interface. On a lab network that is fine. On anything reachable from outside, bind to the specific address you mean and put a firewall in front of it.

    bash Example session 
    grep -n "bind-address" /etc/mysql/mysql.conf.d/mysqld.cnf31:bind-address		= 127.0.0.132:mysqlx-bind-address	= 127.0.0.1sudo systemctl restart mysqlsudo ss -lntp | grep mysqldLISTEN 0      151          0.0.0.0:3306       0.0.0.0:*    users:(("mysqld",pid=8429,fd=23))LISTEN 0      70         127.0.0.1:33060      0.0.0.0:*    users:(("mysqld",pid=8429,fd=21))

    Expected resultLine 31 now 0.0.0.0, line 32 unchanged, and 3306 listening on 0.0.0.0.

    Success conditionThe server is reachable from the network - at the socket level, at least.

  6. Try again, and fail differently

    The same command from db-util01 that failed a moment ago.

    ERROR 1130, not 2003. This is progress, and reading the change is the skill: the server answered. It accepted the TCP connection, looked for an account matching this client, found none, and said so by name - Host '192.168.0.84' is not allowed.

    The network problem is solved. What remains is an account problem, and no amount of further bind-address work will touch it. A reader who only ever learned "open bind-address" stops here and starts editing firewall rules.

    bash Example session 
    mysql -u appuser -p'S7rong-Pass!2026' -h 192.168.0.81 -e "SELECT 1" ; echo "exit=$?"ERROR 1130 (HY000): Host '192.168.0.84' is not allowed to connect to this MySQL serverexit=1

    Expected resultERROR 1130 (HY000): Host '192.168.0.84' is not allowed to connect to this MySQL server and exit=1.

    Success conditionYou can tell a listening problem from an account problem by the error alone.

  7. Create the account for that host, and connect

    In MySQL the host is half the account name. appuser@localhost already exists and is a different account from the one this client needs.

    After creating appuser@192.168.0.84 the list shows two appuser rows. They have separate passwords and separate grants; changing one does not touch the other.

    The connection then succeeds, and the result is worth reading closely. CURRENT_USER() is appuser@192.168.0.84 - the account the server matched - and @@hostname is db-a01, confirming the query really did execute on the other machine.

    bash Example session 
    sudo mysql -e "CREATE USER 'appuser'@'192.168.0.84' IDENTIFIED BY 'S7rong-Pass!2026'"sudo mysql --table -e "SELECT user, host FROM mysql.user WHERE user='appuser'"+---------+--------------+| user    | host         |+---------+--------------+| appuser | 192.168.0.84 || appuser | localhost    |+---------+--------------+mysql -u appuser -p'S7rong-Pass!2026' -h 192.168.0.81 -e "SELECT CURRENT_USER(), USER(), @@hostname" ; echo "exit=$?"CURRENT_USER()	USER()	@@hostnameappuser@192.168.0.84	appuser@192.168.0.84	db-a01exit=0

    Expected resultTwo appuser rows, then appuser@192.168.0.84 connected and @@hostname reporting db-a01.

    Success conditionA remote client is connected, and you know which of the two failures each fix addressed.

Troubleshooting

Official sources

← All MySQL guides