MySQL client

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
## 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