MySQL 8.0 Remote Connection: Command Line & DBeaver Setup with Firewall & User Config

1. Connection Parameters and Address Clarification

The experiment uses a Kylin Advanced Server V11 (x86_64) running MySQL Community Server 8.0.46 listening on 0.0.0.0:3307. The Windows client resides at 192.168.19.1 and connects to the server at 192.168.19.201:3307. A dedicated account 'course_connect'@'192.168.19.1' is created with a random password. The article emphasizes three distinct address concepts: 127.0.0.1 / localhost — refers to the machine executing the command (Windows), not the remote server. 0.0.0.0 — server-side bind address meaning "listen on all IPv4 interfaces"; not a client connection target. 192.168.19.201 — the actual server IP the Windows client must use.

MySQL official connection documentation [1] explains address, port, and protocol selection.

2. Server-Side Preparation: Firewall and User Account

2.1 Verify Listening and Configure firewalld

On the Kylin server (interface ens33, firewalld zone public), confirm MySQL listens on 0.0.0.0:3307. Then add a rich rule allowing only the Windows source IP 192.168.19.1/32 to access TCP port 3307, writing to both runtime and permanent configurations:

firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.19.1/32" port port="3307" protocol="tcp" accept'
firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.19.1/32" port port="3307" protocol="tcp" accept'
firewall-cmd --zone=public --list-rich-rules

Runtime configuration takes effect immediately; permanent configuration survives reloads. Both must be set. References: firewalld rich rules [2], runtime vs permanent [3].

2.2 Create Dedicated Database Account

Log into MySQL via local socket as root@localhost (administrative account) and create the remote user:

CREATE USER 'course_connect'@'192.168.19.1' IDENTIFIED BY RANDOM PASSWORD;

The server returns a generated password — save it securely (password manager). Do not hard-code passwords in command lines. The account receives only USAGE privilege (no schema access), which is sufficient for connection testing. Verify with:

SELECT User, Host FROM mysql.user WHERE User='course_connect';
SHOW GRANTS FOR 'course_connect'@'192.168.19.1';

MySQL account naming rules [4], CREATE USER syntax [5].

3. Command-Line Connection from Windows

3.1 Client Version and Port Reachability

Windows uses MySQL client 8.0.43 (client version ≠ server version). Verify client presence and TCP reachability:

mysql --version
Test-NetConnection -ComputerName 192.168.19.201 -Port 3307 -InformationLevel Quiet

True

confirms TCP connectivity, not authentication success. Test-NetConnection reference [7].

3.2 Authenticate and Verify Instance Identity

Connect explicitly via TCP, setting character set and port:

mysql --no-defaults --default-character-set=utf8mb4 --protocol=TCP -h 192.168.19.201 -P 3307 -u course_connect -p

Enter the generated password at prompt. Inside the client, run the identity query:

SELECT VERSION() AS server_version, @@hostname AS server_host,
       @@port AS server_port, CURRENT_USER() AS account;

Result confirms: server version 8.0.46, host study, port 3307, authenticated as [email protected].

3.3 Connection Status and Exit

Run the client-side status command (not SQL) to see connection details: status Output shows Connection: 192.168.19.201 via TCP/IP, TCP port: 3307, and the active TLS cipher suite. Exit with QUIT; — this closes the client connection only, not the server service.

4. GUI Connection with DBeaver Community 26.2.3

4.1 Driver Selection

Create a new connection, choose the MySQL driver for MySQL 8+ (not "MySQL Old" or "MySQL 5 Legacy"). DBeaver downloads the JDBC driver (MySQL Connector/J 8.2.0 in this run) on first use; tool version and driver version are distinct.

4.2 Connection Parameters and Test

Fill the Main tab:

Server Host: 192.168.19.201 Port: 3307 Database: (empty)

Username: course_connect Password: (saved generated password)

Save password: unchecked

Click Test Connection ; success dialog reports MySQL 8.0.46 and Connector/J 8.2.0. DBeaver MySQL connection docs [11].

4.3 Execute Same Identity Query

Open SQL editor (F3), verify the connection banner shows 192.168.19.201, then run:

SELECT VERSION() AS server_version,
       @@hostname AS server_host,
       @@port AS server_port,
       CURRENT_USER() AS account;

Result matches command-line output exactly (port may display as 3,307 due to locale formatting). DBeaver SQL Editor reference [12].

5. Comparison Exercise and Completion Criteria

Both clients produce identical verification data: version 8.0.46, host study, port 3307, account [email protected]. Key conceptual question: Why does the server bind to 0.0.0.0 while the client connects to 192.168.19.201 ? Because 0.0.0.0 tells the server to accept on all local IPv4 interfaces; the client must target the specific interface IP reachable over the network. Using 127.0.0.1 on Windows would loop back to Windows itself.

Completion standard: TCP check passes, authentication passes, both clients' SQL queries confirm the target instance. Each layer requires its own evidence.

6. Error Scenario: Wrong Port (3306 vs 3307)

Deliberately connect to port 3306 with a 3-second timeout:

mysql --no-defaults --connect-timeout=3 --protocol=TCP -h 192.168.19.201 -P 3306 -u course_connect -p

Result:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.19.201:3306' (10060)

. This is a connection-phase failure (Windows error 10060 = timeout), not an authentication error. Troubleshooting order: verify server listen address/port, firewall rules, then client-specified port. Reverting to 3307 restores success, confirming the root cause was the port typo, not server or credential changes.