MySQL: command-line connections (TCP, socket, legacy server)
Memo of mysql client connections: root via sudo, TCP, Unix socket, MySQL Shell and the --skip-secure-auth option.
A memo of ways to connect to a MySQL server from the command line, including the common cases where the root password is unknown or the Unix socket must be used.
Local connection as root
On Linux, root can often connect through the socket with sudo, without a password:
sudo mysql -u root
sudo mysql -u root -p
TCP connection
mysql -u root -p -h 127.0.0.1
mysql -u app_user -p -h mysqlserver01
mysql -u dba -h mysqlserver01
In a script the password can be passed attached to the option (-p<PASSWORD>), but it is then visible in the process list and the shell history; prefer a bare -p (prompt) or an option file.
Run a single command without entering interactive mode:
mysql -u app_user -p -h localhost -Bse 'show databases'
MySQL Shell
\connect --mc dba@mysqlserver01
Legacy server (MySQL 5 and earlier)
A very old server uses the old password hashing format; a recent client refuses it unless --skip-secure-auth is given:
mysql -h mysqlserver01 -u legacy_user -p --skip-secure-auth
Running commands as the service account
When you need to read a file restricted to the mysql user (for example /etc/my.cnf):
sudo su -c "less /etc/my.cnf" -s /bin/sh mysql
Connecting through the Unix socket
To find the socket path from a TCP connection:
mysql -u root -p -h 127.0.0.1 -e "select @@socket"
Then connect explicitly through that socket:
mysql -u root -p -S /var/run/mysqld/mysql.sock
Notes
- Host names, accounts and passwords from the original note were removed or replaced by generic values (
<PASSWORD>,app_user,mysqlserver01). - The socket path (
/var/run/mysqld/mysql.sock) varies by distribution.