MySQL remote connections: host, port, TLS and clients

To connect to a remote MySQL server you need five things: the host name or IP address, the port, a user name, its password, and the name of a database. With those, mysql -h host -P port -u user -p database gets you a prompt, and the same five values make the connection string for your application. Most failures come from one of three places: the network (the port is wrong or not reachable), the account (the user exists for a different host pattern than the one you are connecting from), or the authentication plugin (an old client that does not speak caching_sha2_password, which MySQL 8.4 uses by default and which wants an encrypted connection the first time).

This guide goes through each piece in the order you meet them: the details you need, the command-line client, encryption and --ssl-mode, the authentication plugin, connection strings for the common runtimes, graphical tools, and the error messages people actually hit. Everything here applies to MySQL 8.4 LTS, with notes where 8.0 behaves differently.

The five details, and where they come from#

On your own machine MySQL listens on 3306 and the account you use is often root@localhost. On a hosted database none of that is a safe assumption. The host is whatever address the provider gives you, the port may be anything, and the account you should use day to day is an application user with rights on one database rather than root.

On RE:NODE, a MySQL server is created with a root password, an application database and an application user, all generated, and it is reached on the plan's host and port shown in the panel. Those two values are the ones to copy: do not guess 3306. There is no proxy slot on database plans, so the server is reached directly on that address and port rather than through a domain and certificate managed by the panel.

One rule saves a lot of confusion later: localhost in MySQL is special. When a client is given localhost, it connects through a Unix socket file, not TCP, and ignores the port entirely. If you are on the same machine and want TCP, use 127.0.0.1. From another machine, localhost simply means your own computer.

Connecting with the mysql command-line client#

The mysql client ships with every MySQL server package and with the client-only packages (mysql-client on Debian and Ubuntu, mysql on Homebrew, the MySQL Installer or the ZIP archive on Windows). Use a client from the 8.x series or later. Clients older than 8.0 cannot authenticate against a default 8.4 account, which is covered below.

$ mysql -h db.example.net -P 30412 -u app -p appdbEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Server version: 8.4.6 MySQL Community Server - GPLmysql>

The -p with nothing after it makes the client ask for the password, which keeps it out of your shell history and out of the process list. -pSecret (no space) works but leaves the password in ~/.bash_history. -p Secret (with a space) does not do what it looks like: Secret is taken as the database name and you are still prompted.

Once you are in, check where you landed:

SELECT CURRENT_USER(), USER(), DATABASE(), @@version, @@port;

USER() is the name and host you claimed; CURRENT_USER() is the account MySQL actually matched, which is the one whose privileges apply. If those two differ in an unexpected way - you connected as app but matched ''@'%', an anonymous account - you have found the reason your grants do not seem to work. \s (or status) prints the connection summary, including whether the connection is encrypted:

SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384Connection:             db.example.net via TCP/IPServer version:         8.4.6 MySQL Community Server - GPLProtocol version:       10

Keeping credentials in an option file

Typing the host and port every time gets old. The client reads option files, and a [client] group in ~/.my.cnf (or %APPDATA%\MySQL\.mylogin.cnf via the tool below) supplies defaults:

[client]host=db.example.netport=30412user=appssl-mode=REQUIRED

Leave the password out of the plain file and let the client prompt, or use mysql_config_editor, which writes an obfuscated .mylogin.cnf:

$ mysql_config_editor set --login-path=prod --host=db.example.net \    --port=30412 --user=app --password$ mysql --login-path=prod appdb

The obfuscation stops a casual glance, not a determined reader. Set chmod 600 on either file. The client refuses to read a world-writable option file, and says so.

Encryption: ssl-mode and what each value means#

A MySQL connection is either encrypted with TLS or it is not, and the client decides how hard to insist through --ssl-mode. The default is PREFERRED: encrypt if the server offers it, quietly fall back to plain text if it does not.

MySQL 8.x generates a self-signed certificate and key in the data directory on first start unless the operator turned that off, which is why most servers offer TLS out of the box. A self-signed certificate encrypts the connection but proves nothing about who is on the other end, so VERIFY_CA and VERIFY_IDENTITY will fail against it unless you are given the CA file and pass it with --ssl-ca=ca.pem.

For a database reached over the public internet, set REQUIRED at minimum. It costs nothing measurable on modern hardware and it closes the case where a misconfigured server silently downgrades you to plain text. Then confirm with \s, or from SQL:

SHOW SESSION STATUS LIKE 'Ssl_version';SHOW SESSION STATUS LIKE 'Ssl_cipher';

An empty Ssl_cipher means the session is not encrypted. MySQL 8.4 accepts only TLS 1.2 and 1.3 by default, and it removed the old server-side --ssl switch and the have_ssl variable; if a guide tells you to check have_ssl, it was written for 5.7 or 8.0.

The server can also insist. REQUIRE SSL on an account (ALTER USER 'app'@'%' REQUIRE SSL;) refuses that user any unencrypted connection, and require_secure_transport=ON refuses everyone. Both are worth knowing if you hold the root account.

caching_sha2_password and the first-connection problem#

Since MySQL 8.0 the default authentication plugin is caching_sha2_password. In MySQL 8.4 the older mysql_native_password plugin is not just out of fashion - it is disabled by default and has to be switched on with mysql_native_password=ON in the server configuration before any account using it can log in. In MySQL 9.0 it is removed altogether. Plan for caching_sha2_password everywhere.

The plugin has one quirk that produces most of the confusing errors. The first time a user authenticates after a server restart (or after the password changes), the server has no cached hash for that user, and it needs the password sent securely to compute one. "Securely" means either:

1. The connection is encrypted with TLS, in which case the password goes inside the tunnel, or
2. The client fetches the server's RSA public key and encrypts the password with it.

If neither is true, the login fails with:

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reportederror: Authentication requires secure connection.

The cure is to use TLS, which you should be doing anyway. If you cannot, the client can request the key with --get-server-public-key (command line), allowPublicKeyRetrieval=true (JDBC), or the equivalent option in your driver. Requesting the key over an unencrypted connection is open to a man-in-the-middle swapping it, which is the reason it is not on by default.

The other error belongs to old clients and old drivers:

Authentication plugin 'caching_sha2_password' cannot be loaded

That is a client from the 5.7 era, an old PHP mysqlnd, or a library like the original Node mysql package (not mysql2), which never learned the plugin. Upgrade the client or driver. Recreating the user with mysql_native_password works only if the server has that plugin enabled, and on 8.4 it is off unless someone turned it on - fixing the client is the better trade. MySQL 8.4 LTS: what changed has the full list of authentication changes.

Connection strings for the common runtimes#

Every driver wants the same five values, arranged differently. Keep them in environment variables and build the string at runtime; environment variables and secrets covers how.

DB_HOST=db.example.netDB_PORT=30412DB_USER=appDB_PASSWORD=change-meDB_NAME=appdbDATABASE_URL=mysql://app:change-me@db.example.net:30412/appdb

A URL breaks if the password contains @, :, / or #, because those are URL syntax. Either percent-encode them (@ becomes %40) or pass the parts separately, which most drivers allow.

import mysql from "mysql2/promise";const pool = mysql.createPool({  host: process.env.DB_HOST,  port: Number(process.env.DB_PORT),  user: process.env.DB_USER,  password: process.env.DB_PASSWORD,  database: process.env.DB_NAME,  connectionLimit: 10,  ssl: { rejectUnauthorized: false }, // TLS on, self-signed certificate accepted});

from sqlalchemy import create_engineimport osurl = (f"mysql+pymysql://{os.environ['DB_USER']}:{os.environ['DB_PASSWORD']}"       f"@{os.environ['DB_HOST']}:{os.environ['DB_PORT']}/{os.environ['DB_NAME']}"       "?charset=utf8mb4")engine = create_engine(url, pool_size=5, pool_recycle=1800,                       # TLS on; no CA given, so a self-signed cert is accepted                       connect_args={"ssl": {"check_hostname": False}})

$dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',    getenv('DB_HOST'), getenv('DB_PORT'), getenv('DB_NAME'));$pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,]);

jdbc:mysql://db.example.net:30412/appdb?sslMode=REQUIRED&characterEncoding=UTF-8

Two settings belong in every string. The character set should be utf8mb4, or your app will one day store an emoji as four question marks - see utf8mb4 and collations. And something should recycle idle connections before the server's wait_timeout (28800 seconds, eight hours, by default) closes them, or the first request after a quiet night fails with "MySQL server has gone away". pool_recycle in SQLAlchemy and the pool's idle timeout in other drivers handle this. How big each pool should be is a topic of its own: MySQL connection limits and pooling.

For PHP specifically, PHP PDO and MySQL goes further into prepared statements and error modes, and the C# side - MySqlConnector versus Oracle's Connector/NET - is in .NET with PostgreSQL, MySQL or SQL Server.

GUI tools: Workbench, DBeaver, HeidiSQL and friends#

Any of the common graphical clients will do, and they all ask for the same fields. What differs is where they hide the TLS and public-key options.

• MySQL Workbench (Oracle, free): Connection Method Standard (TCP/IP), host, port, user. On the SSL tab, set Use SSL to Require. Workbench is the closest match to the server's own feature set, including the visual EXPLAIN.
• DBeaver (free community edition): new MySQL connection, fill Server Host and Port. On the Driver properties tab set allowPublicKeyRetrieval to true if you are not using TLS and hit the public-key error; on the SSL tab tick Use SSL and, for a self-signed certificate, untick certificate verification.
• HeidiSQL (Windows, free): Network type MariaDB or MySQL (TCP/IP), then the SSL tab. Light and fast, and fine for browsing and quick edits.
• TablePlus, DataGrip, Beekeeper Studio: same fields, TLS under an SSL or Advanced tab.

The pitfall shared by all of them is the bundled driver version. A tool that ships an old connector may fail with the plugin error above even though the command-line client works. Updating the tool, or the driver inside it, fixes it. MySQL GUI clients compared goes through each one in more detail.

When the connection fails: the errors, decoded#

ERROR 2003 (HY000): Can't connect to MySQL server on 'db.example.net:3306' (110)

A network failure before MySQL was even reached. Error 110 is a timeout (something is dropping packets: a firewall, the wrong address), 111 is "connection refused" (the address is right, but nothing listens on that port). Check the port first - the 3306 in the message is the giveaway that the client used the default because you forgot -P. Then check that your own network allows outbound connections on that port; some office and campus networks block everything except web traffic. nc -vz db.example.net 30412 or Test-NetConnection db.example.net -Port 30412 on Windows tells you whether the port answers at all.

ERROR 1045 (28000): Access denied for user 'app'@'198.51.100.7' (using password: YES)

MySQL was reached and rejected the login. Either the password is wrong, or there is no account app whose host pattern matches your address 198.51.100.7. The message shows the host MySQL saw, which is the one that has to match. using password: NO means the client sent no password at all - usually -p missing, or an empty environment variable. MySQL users and privileges explains how host patterns are matched.

ERROR 1044 (42000): Access denied for user 'app'@'%' to database 'other'

You logged in fine but asked for a database the account has no rights on. Connect to the right database or ask for a grant.

ERROR 1129 (HY000): Host '198.51.100.7' is blocked because of many connection errors

Your address made more failed handshakes than max_connect_errors allows (100 by default). Someone with admin rights clears it with TRUNCATE TABLE performance_schema.host_cache;. In MySQL 8.4 FLUSH HOSTS is gone; that TRUNCATE (or mysqladmin flush-hosts) is the replacement. Then find the thing that was failing - usually a health check opening a TCP connection and closing it without logging in.

ERROR 1040 (HY000): Too many connections

The server reached max_connections (151 by default). Something is leaking connections or a pool is sized larger than the server can carry. Raising the limit is rarely the fix; connection pools and limits explains why.

ERROR 2013 (HY000): Lost connection to MySQL server during queryERROR 2006 (HY000): MySQL server has gone away

The connection died. The usual causes: the server closed an idle connection after wait_timeout, a single statement exceeded max_allowed_packet (64 MB by default in 8.x), the server restarted, or a network device in between killed a long-idle TCP session. Recycle pooled connections more often than the timeout and reconnect on this error.

Keeping a remote database safe#

A database reachable from the internet is a target the moment it exists. Scanners try root with common passwords on every address and port they find, all day. The defences are not complicated:

• Use the application user for the application, not root. Keep root for administration and give it a long generated password.
• Require TLS for anything that crosses the internet.
• Give each application its own user with rights on its own database only, so a leak of one credential does not expose the rest.
• Rotate a password the moment it might have leaked (a committed .env, a screenshot in a support thread) - MySQL 8 supports dual passwords with RETAIN CURRENT PASSWORD, so you can rotate without downtime.
• Watch for failed logins. performance_schema.host_cache counts errors per address.

Database security checklist covers the rest, including what to do if you suspect a credential has been used by someone else.

FAQ#

What port does MySQL use?

3306 by default, and 33060 for the X Protocol used by MySQL Shell's document store. A hosted server can listen on any port, so use the one your provider gives you rather than assuming the default. The client falls back to 3306 silently if you forget -P.

Why does localhost work on the server but not from my laptop?

Because localhost means "this machine" and, in MySQL, also means "use the Unix socket". From your laptop, localhost is your laptop. Use the server's host name or address, and make sure an account exists for a host pattern that matches where you connect from.

Do I need TLS if my password is strong?

Yes. Without TLS, every query and every result crosses the network in plain text, and caching_sha2_password has to fall back to RSA key exchange for the first login after a restart. Set --ssl-mode=REQUIRED or the equivalent in your driver.

How do I fix "Authentication plugin caching_sha2_password cannot be loaded"?

Update the client or driver to one that supports MySQL 8 authentication: an 8.x or later mysql client, mysql2 rather than mysql in Node, a current PHP, a current Connector/J. Downgrading the account to mysql_native_password only works if the 8.4 server has that plugin enabled, and it is off by default.

Can I connect without specifying a database?

Yes. Leave it off the command line and run USE appdb; after logging in, or prefix table names with the database. Applications should name the database in the connection so every query runs where you expect.