Skip navigation links

Additional languages
4.2.3.2 Using Option Files »
Section Navigation      [Toggle]

4.2.3.1. Using Options on the Command Line

[+/-]

4.2.3.1.1. Program Option Modifiers

Program options specified on the command line follow these rules:

  • Options are given after the command name.

  • An option argument begins with one dash or two dashes, depending on whether it is a short form or long form of the option name. Many options have both short and long forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display its help message.

  • Option names are case sensitive. -v and -V are both legal and have different meanings. (They are the corresponding short forms of the --verbose and --version options.)

  • Some options take a value following the option name. For example, -h localhost or --host=localhost indicate the MySQL server host to a client program. The option value tells the program the name of the host where the MySQL server is running.

  • For a long option that takes a value, separate the option name and the value by an “=” sign. For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent. An exception to this rule is the option for specifying your MySQL password. This option can be given in long form as --password=pass_val or as --password. In the latter case (with no password value given), the program prompts you for the password. The password option also may be given in short form as -ppass_val or as -p. However, for the short form, if the password value is given, it must follow the option letter with no intervening space. The reason for this is that if a space follows the option letter, the program has no way to tell whether a following argument is supposed to be the password value or some other kind of argument. Consequently, the following two commands have two completely different meanings: 

    shell> mysql -ptest
shell> mysql -p test

    The first command instructs mysql to use a password value of test, but specifies no default database. The second instructs mysql to prompt for the password value and to use test as the default database.

  • Within option names, dash (“-”) and underscore (“_”) may be used interchangeably. For example, --skip-grant-tables and --skip_grant_tables are equivalent. (However, the leading dashes cannot be given as underscores.)

Another option that may occasionally be useful with mysql is the --execute or -e option, which can be used to pass SQL statements to the server. When this option is used, mysql executes the statements and exits. The statements must be enclosed by quotation marks. For example, you can use the following command to obtain a list of user accounts: 

shell> mysql -u root -p --execute="SELECT User, Host FROM user" mysql
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>

Note that the long form (--execute) is followed by an equals sign (=).

If you wish to use quoted values within a statement, you will either need to escape the inner quotes, or use a different type of quotes within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotes, you can double quotes around the statement, and single quotes for any quoted values within the statement.

In the preceding example, the name of the mysql database was passed as a separate argument. However, the same statement could have been executed using this command, which specifies no default database: 

mysql> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"

Multiple SQL statements may be passed on the command line, separated by semicolons: 

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.5-alpha-log |
+-----------------+
+---------------------+
| NOW()               |
+---------------------+
| 2006-01-05 21:19:04 |
+---------------------+

The --execute or -e option may also be used to pass commands in an analogous fashion to the ndb_mgm management client for MySQL Cluster. See Section 16.2.6, “Safe Shutdown and Restart”, for an example.

4.2.3.1.1. Program Option Modifiers

Some options control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms: 

--disable-column-names
--skip-column-names
--column-names=0

The --disable and --skip prefixes and the =0 suffix all have the same effect: They turn the option off.

The “enabled” form of the option may be specified in any of these ways: 

--column-names
--enable-column-names
--column-names=1

If an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning: 

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'

The --loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file, An option that may not be recognized by all versions of a program can be given using the --loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

mysqld enables a limit to be placed on how large client programs can set dynamic system variables. To do this, use a --maximum prefix with the variable name. For example, --maximum-query_cache_size=4M prevents any client from making the query cache size larger than 4MB.