How to Connect to a MySQL Database from the Command Line (Local and Remote)

Applies to: MySQL 8.4.11 and 8.0.46 (Ubuntu 24.04), MariaDB 11.8 (Debian 13 and Docker); tested with the mysql and mariadb command-line clients

To connect to a MySQL database from a terminal on the server, run mysql -u USER -p DATABASE and type the password when asked. From another computer, add the server with -h: mysql -h HOST -P 3306 -u USER -p DATABASE. The same commands work for MariaDB, whose client is called mariadb (on Debian 13, mysql is a link to it).

1. Connect on the server itself

mysql -u appuser -p appdb

-p with nothing after it makes the client ask for the password, so it doesn’t end up in your shell history. To log in as the database root user on Debian or Ubuntu, use sudo mysql: in our tests, root on Ubuntu 24.04 (MySQL 8.0.46) used the auth_socket plugin, and on Debian 13 (MariaDB 11.8) root logged in through the socket without a password.

2. localhost or 127.0.0.1?

They connect in different ways. The status command inside the client shows which one you got:

$ mysql -u appuser -p -h localhost appdb -e status
Current user:		appuser@localhost
Connection:		Localhost via UNIX socket
UNIX socket:		/var/run/mysqld/mysqld.sock

$ mysql -u appuser -p -h 127.0.0.1 appdb -e status
Current user:		appuser@127.0.0.1
SSL:			Cipher in use is TLS_AES_128_GCM_SHA256
Connection:		127.0.0.1 via TCP/IP
TCP port:		3306

localhost uses the socket file; 127.0.0.1 uses TCP port 3306. This matters for accounts: a user created only as 'solo'@'localhost' logged in with -h localhost but got ERROR 1045 (28000): Access denied for user 'solo'@'127.0.0.1' with -h 127.0.0.1. A socket error, on the other hand, only affects localhost.

3. Connect to a remote server

$ mysql -h db.example.com -P 3306 -u appuser -p appdb -e "SELECT CURRENT_USER(), DATABASE();"
CURRENT_USER()	DATABASE()
appuser@%	appdb

For this to work, three things must be true on the database server: MySQL listens on the network, not only on 127.0.0.1 (the bind-address setting); the user is allowed from your IP address ('appuser'@'%' or a specific address); and the firewall lets your IP reach port 3306. On cPanel hosting, allow your IP under Remote MySQL: enable remote MySQL connections in cPanel.

A safer way that needs no open port is an SSH tunnel. According to the OpenSSH manual, -L forwards a local port to an address on the far side:

ssh -L 3307:127.0.0.1:3306 user@server.example.com
# in a second terminal on your computer:
mysql -h 127.0.0.1 -P 3307 -u appuser -p appdb

4. Keep the password out of the command line

-pPassword works, but the client warns: mysql: [Warning] Using a password on the command line interface can be insecure. For scripts, put the login in ~/.my.cnf and make it readable only by you:

[client]
user=appuser
password=AppPass-456

chmod 600 ~/.my.cnf
mysql appdb

In our MariaDB test, mariadb appdb then logged in as appuser@localhost without a prompt.

Error messages and what they mean

• ERROR 1045 (28000): Access denied for user 'appuser'@'172.28.0.3' (using password: YES): wrong password, or no account for that user from that IP address. See error 1045.
• ERROR 1044 (42000): Access denied for user 'appuser'@'%' to database 'mysql': the login worked, but the user has no rights on that database. We got the same error for a database that doesn’t exist.
• ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): no server is running on this machine, or it uses another socket path. Check the service, or connect with -h 127.0.0.1 or the right host.
• ERROR 2003 (HY000): Can't connect to MySQL server on 'db.example.com:3307' (111): nothing listens on that port (connection refused). Check the port, bind-address and the firewall.
• ERROR 2005 (HY000): Unknown MySQL server host 'nohost.invalid' (-2): the host name doesn’t resolve. Check the spelling or use the IP address.
• ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain: we got this with the MariaDB 11.8 client against a MySQL 8.4 server. According to MariaDB, clients from 11.4 verify the server certificate by default, and the password-based check only works with some login plugins, not MySQL’s default caching_sha2_password. Use the MySQL client, give the server’s CA certificate with --ssl-ca, or, on a trusted network only, add --skip-ssl-verify-server-cert.

Once connected: list databases, create a user and grant permissions or connect from PHP.

Official documentation: MySQL 8.4: connecting to the server and MariaDB zero-configuration SSL.