MariaDB Tutorial / Troubleshoot Local and Remote MariaDB Connections

Narrow a MariaDB connection problem by checking server reachability, address and protocol, account matching, authentication and database permissions in order. Remote access additionally needs an appropriate listener, network policy, account host and verified encryption; first establish a known local TCP connection.

Separate the client, server and network path

An installed client is not proof of a running server. An ODBC driver alone may provide neither a server nor a command-line client. Check the actual package, service name and executable paths using the Windows installation guide or Ubuntu installation guide. Check installation/service state through Install MariaDB on Windows and Start the Server or Install MariaDB on Ubuntu and Start the Server, then separately verify allowed operations using Create Users and Grant Only Required Privileges.

Check the MariaDB documentation for the syntax and conditions used here: mariadb command line client, mariadb remote connection guide.

Fix the local connection path explicitly

This command assumes the client and server are on the same PC and the server listens on port 3306. Replace that port if the actual configuration differs; supply the password only at its prompt.

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 shopcourse

SELECT VERSION(),@@hostname,@@port,DATABASE();
SELECT USER(),CURRENT_USER();
SHOW GRANTS;

Confirm the version, host, port, selected database and CURRENT_USER(). Depending on the platform and options, localhost can imply a socket, named pipe or TCP. This exercise explicitly chooses TCP and 127.0.0.1 to remove that ambiguity.

Test real database access after authenticating

Check that products_44_lab is unused, then create the full independent fixture with the appropriate learning account:

USE shopcourse;
CREATE TABLE products_44_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p44_price CHECK (price >= 0),
  CONSTRAINT ck_p44_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_44_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

SELECT COUNT(*) FROM products_44_lab;

The count is six. SELECT 1 succeeds without proving access to every intended database or table, so test actual required operations. Record useful error codes and sanitized messages rather than expose credentials in logs or screenshots.

Limit remote listening and network access to the required path

Confirm the actual private server address, port, allowed client origin and TLS policy with the administrator. An Ubuntu package may use /etc/mysql/mariadb.conf.d/50-server.cnf, but configuration locations vary. Back up the file and establish that the running server reads it before changing its server section.

[mariadbd]
bind-address = 10.0.0.10
port = 3306

10.0.0.10 is a private-address example, not an address guaranteed to exist on your machine. Use a real server interface, permit only required client origins through the network policy and account definition, and plan the disruption and reversal of any restart. Do not infer that the account host is automatically correct merely because the listener accepts packets.

Verify the remote certificate and account match

The next command assumes a real resolvable server and a trusted CA file. Replace db.example.internal and the path, and ensure the server name matches its certificate. A successful local test does not establish remote network or TLS correctness.

mariadb --protocol=tcp --host=db.example.internal --port=3306 --user=app_user --password --ssl --ssl-verify-server-cert --ssl-ca=C:/mariadb-lab/ca.pem

An approved SSH tunnel or VPN can provide a different path, but inspect the origin visible to the server and the resulting account match. Broad listening, wildcard origins or disabled certificate checks should not become habitual connection-error fixes.

Record the failure stage and reversible changes

For an unreachable server, inspect DNS, client address, listener, port and firewall. Once reachable, investigate account Host matching, authentication plugin and credentials. Unknown database and table-permission errors are later stages. Record configuration changes, original values and restarts and retain the path back to the prior policy. Remove only the disposable local table after reviewing its contents.

DROP TABLE shopcourse.products_44_lab;