MySQL client

Other topics

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:

ParameterDescription
-D --database=namename of the database
--delimiter=strset the statement delimiter. The default one is ';'
-e --execute='command'execute command
-h --host=namehostname to connect to
-p --password=namepassword Note: there is no space between -p and the password
-p (without password)the password will be prompted for
-P --port=#port number
-s --silentsilent mode, produce less output. Use \t as column separator
-sslike -s, but omit column names
-S --socket=pathspecify the socket (Unix) or named pipe (Windows) to use when connecting to a local instance
--skip-column-namesomit column names
-u --user=nameusername
-U --safe-updates --i-am-a-dummylogin with the variable sql_safe_updates=ON. This will allow only DELETE and UPDATE that explicitly use keys
-V --versionprint the version and exit

Contributors

Topic Id: 5619

Example Ids: 19943,19944

This site is not affiliated with any of the contributors.