mysql

Connect to a database

$ mysql [database_name]

$ mysql [[-u|--user]] [user] [[-p|--password]] [database_name]

$ mysql [[-h|--host]] [database_host] [database_name]

$ mysql [[-S|--socket]] [path/to/socket.sock]

$ mysql [[-e|--execute]] "source [filename.sql]" [database_name]

$ mysql [[-u|--user]] [user] [[-p|--password]] [database_name] < [path/to/backup.sql]

$ mysql [[-u|--user]] [user] [[-p|--password]] < [path/to/backup.sql]

mysql [options] [database_name]
mysql {--help | -?}

-h host_name, --host=host_name
    Specifies the host_name where the MySQL server is running. Can be localhost or an IP address.

-P port_num, --port=port_num
    Connects to the server using the specified TCP/IP port_num. Default is 3306.

-u user_name, --user=user_name
    The MySQL user account user_name to use for authentication.

-p[password], --password[=password]
    Prompts for a password if password is not provided. If provided directly, it should be immediately after -p with no space (e.g., -pmypass).

-D db_name, --database=db_name
    Specifies the default database db_name to use after connecting.

-e statement, --execute=statement
    Executes the SQL statement and exits. Useful for non-interactive scripting.

-f, --force
    Continues processing even if an SQL error occurs. Relevant for batch operations.

-N, --skip-column-names
    Do not write column names in the output. Useful for scripting where only data is needed.

-B, --batch
    Prints results with a tab as a separator, each row on a new line. Disables history and interactive features.

-q, --quick
    Does not cache results, printing row by row. Can be useful for large result sets to save memory.

-s, --silent
    Silent mode. Produces less output, suppressing informational messages.

-t, --table
    Displays output in a formatted table. This is the default for interactive mode.

-X, --xml
    Produces output in XML format. Useful for machine parsing.

-H, --html
    Produces output in HTML format. Useful for generating web pages.

-V, --vertical
    Prints output vertically, with each column on a new line. Useful for wide tables or viewing CLOB/TEXT fields.

--help, -?
    Displays a help message and exits.

The mysql command is the primary command-line client for interacting with MySQL and MariaDB database servers. It allows users to execute SQL statements, perform administrative tasks, import/export data, and retrieve query results directly from the terminal.

This versatile tool is indispensable for database administrators (DBAs) for maintenance, monitoring, and troubleshooting, as well as for developers to test queries, manage schema, and interact with their applications' backend data.

It supports both interactive mode, where users type SQL queries line by line, and non-interactive mode, where SQL commands are provided as arguments or piped from files. The mysql client is part of the standard MySQL client package, making it widely available on systems where MySQL is installed or its client libraries are present. Its robustness and flexibility make it a cornerstone for managing MySQL databases from the command line.

Using the -ppassword option on the command line is generally insecure as the password can be visible in process lists (e.g., via ps command history). It is recommended to use the -p option without a password to be prompted, or configure a ~/.my.cnf file for secure credentials.

For very large result sets, running mysql without options like --batch or --quick can consume significant memory and potentially cause client-side performance issues or crashes.

Firewall rules or network configurations can prevent the mysql client from connecting to remote database servers.

When mysql is run without the -e or --batch options and without input redirection, it enters interactive mode. In this mode, it displays a mysql> prompt, allowing users to type SQL statements line by line, which are executed upon pressing Enter and terminating with a semicolon (;), \g, or \G.

The mysql command is extensively used in shell scripts for automation. SQL commands can be supplied via the -e option or by piping a file into standard input (e.g., mysql < script.sql). Combining it with options like --batch or --skip-column-names makes output highly suitable for parsing by other scripting tools.

Users can store connection parameters and client options in configuration files, typically ~/.my.cnf in their home directory. This allows for more secure storage of passwords and simplifies common connections, avoiding the need to type long command-line options repeatedly.

The mysql command-line client is a core component of the MySQL database system, which was originally developed by MySQL AB. It was first released in 1995. Over the years, as MySQL gained popularity as a leading open-source relational database management system, the mysql client evolved to support new features, improve performance, and enhance usability.

Following MySQL AB's acquisition by Sun Microsystems in 2008, and then Oracle Corporation in 2010, development of the mysql client continued under Oracle. Its design has largely remained consistent, serving as the canonical way for users and scripts to interact with a MySQL server, a testament to its robust and effective design from its early days.

mysqldump(1), mysqlimport(1), mysqladmin(1), mysqlshow(1), mariadb(1)