# MySQL client

# Base login

To access MySQL from the command line:

mysql --user=username --password=pwd --host=hostname test_db

This can be shortened to:

mysql -u username -p password -h hostname test_db

By omitting the password value MySQL will ask for any required password as the first input. If you specify password the client will give you an 'insecure' warning:

mysql -u=username -p -h=hostname test_db

For local connections --socket can be used to point to the socket file:

mysql --user=username --password=pwd --host=localhost --socket=/path/to/mysqld.sock test_db

Omitting the socket parameter will cause the client to attempt to attach to a server on the local machine. The server must be running to connect to it.

# Execute commands

This set of example show how to execute commands stored in strings or script files, without the need of the interactive prompt. This is especially useful to when a shell script needs to interact with a database.

# Execute command from a string

$ mysql -uroot -proot test -e'select * from people'

| id | name  | gender |
|  1 | Kathy | f      |
|  2 | John  | m      |

To format the output as a tab-separated grid, use the --silent parameter:

$ mysql -uroot -proot test -s -e'select * from people'

id      name    gender
1       Kathy   f
2       John    m

To omit the headers:

$ mysql -uroot -proot test -ss -e'select * from people'

1       Kathy   f
2       John    m

# Execute from script file:

$ mysql -uroot -proot test < my_script.sql

$ mysql -uroot -proot test -e'source my_script.sql'

# Write the output on a file

$ mysql -uroot -proot test < my_script.sql > out.txt

$ mysql -uroot -proot test -s -e'select * from people' > out.txt

# Syntax

  • mysql [OPTIONS] [database_name]

# Parameters

Parameter Description
-D --database=name name of the database
--delimiter=str set the statement delimiter. The default one is ';'
-e --execute='command' execute command
-h --host=name hostname to connect to
-p --password=name password Note: there is no space between -p and the password
-p (without password) the password will be prompted for
-P --port=# port number
-s --silent silent mode, produce less output. Use \t as column separator
-ss like -s, but omit column names
-S --socket=path specify the socket (Unix) or named pipe (Windows) to use when connecting to a local instance
--skip-column-names omit column names
-u --user=name username
-U --safe-updates --i-am-a-dummy login with the variable sql_safe_updates=ON. This will allow only DELETE and UPDATE that explicitly use keys
-V --version print the version and exit