LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon