4.5.1.1 mysql Options

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Table 4.10 mysql Options

FormatDescriptionIntroducedDeprecated
--auto-rehash Enable automatic rehashing    
--auto-vertical-output Enable automatic vertical result set display    
--batch Do not use history file    
--binary-as-hex Display binary values in hexadecimal notation 5.7.19  
--binary-mode Disable \r\n - to - \n translation and treatment of \0 as end-of-query    
--bind-address Use specified network interface to connect to MySQL Server    
--character-sets-dir Directory where character sets are installed    
--column-names Write column names in results    
--column-type-info Display result set metadata    
--comments Whether to retain or strip comments in statements sent to the server    
--compress Compress all information sent between client and server    
--connect-expired-password Indicate to server that client can handle expired-password sandbox mode. 5.7.2  
--connect_timeout Number of seconds before connection timeout    
--database The database to use    
--debug Write debugging log; supported only if MySQL was built with debugging support    
--debug-check Print debugging information when program exits    
--debug-info Print debugging information, memory, and CPU statistics when program exits    
--default-auth Authentication plugin to use    
--default-character-set Specify default character set    
--defaults-extra-file Read named option file in addition to usual option files    
--defaults-file Read only named option file    
--defaults-group-suffix Option group suffix value    
--delimiter Set the statement delimiter    
--enable-cleartext-plugin Enable cleartext authentication plugin    
--execute Execute the statement and quit    
--force Continue even if an SQL error occurs    
--get-server-public-key Request RSA public key from server 5.7.23  
--help Display help message and exit    
--histignore Patterns specifying which statements to ignore for logging    
--host Connect to MySQL server on given host    
--html Produce HTML output    
--ignore-spaces Ignore spaces after function names    
--init-command SQL statement to execute after connecting    
--line-numbers Write line numbers for errors    
--local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE    
--login-path Read login path options from .mylogin.cnf    
--max_allowed_packet Maximum packet length to send to or receive from server    
--max_join_size The automatic limit for rows in a join when using --safe-updates    
--named-commands Enable named mysql commands    
--net_buffer_length Buffer size for TCP/IP and socket communication    
--no-auto-rehash Disable automatic rehashing    
--no-beep Do not beep when errors occur    
--no-defaults Read no option files    
--one-database Ignore statements except those for the default database named on the command line    
--pager Use the given command for paging query output    
--password Password to use when connecting to server    
--pipe On Windows, connect to server using named pipe    
--plugin-dir Directory where plugins are installed    
--port TCP/IP port number for connection    
--print-defaults Print default options    
--prompt Set the prompt to the specified format    
--protocol Connection protocol to use    
--quick Do not cache each query result    
--raw Write column values without escape conversion    
--reconnect If the connection to the server is lost, automatically try to reconnect    
--i-am-a-dummy, --safe-updates Allow only UPDATE and DELETE statements that specify key values    
--secure-auth Do not send passwords to server in old (pre-4.1) format   5.7.5
--select_limit The automatic limit for SELECT statements when using --safe-updates    
--server-public-key-path Path name to file containing RSA public key    
--shared-memory-base-name The name of shared memory to use for shared-memory connections    
--show-warnings Show warnings after each statement if there are any    
--sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C)    
--silent Silent mode    
--skip-auto-rehash Disable automatic rehashing    
--skip-column-names Do not write column names in results    
--skip-line-numbers Skip line numbers for errors    
--skip-named-commands Disable named mysql commands    
--skip-pager Disable paging    
--skip-reconnect Disable reconnecting    
--socket For connections to localhost, the Unix socket file or Windows named pipe to use    
--ssl Enable encrypted connection    
--ssl-ca File that contains list of trusted SSL Certificate Authorities    
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files    
--ssl-cert File that contains X.509 certificate    
--ssl-cipher List of permitted ciphers for connection encryption    
--ssl-crl File that contains certificate revocation lists    
--ssl-crlpath Directory that contains certificate revocation list files    
--ssl-key File that contains X.509 key    
--ssl-mode Security state of connection to server 5.7.11  
--ssl-verify-server-cert Verify host name against server certificate Common Name identity    
--syslog Log interactive statements to syslog 5.7.1  
--table Display output in tabular format    
--tee Append a copy of output to named file    
--tls-version Protocols permitted for encrypted connections 5.7.10  
--unbuffered Flush the buffer after each query    
--user MySQL user name to use when connecting to server    
--verbose Verbose mode    
--version Display version information and exit    
--vertical Print query output rows vertically (one line per column value)    
--wait If the connection cannot be established, wait and retry instead of aborting    
--xml Produce XML output    
 

  • If the command line is mysql --force --one-database db1, mysql handles the input as follows:

    • The DELETE statement is executed because the default database is db1, even though the statement names a table in a different database.

    • The DROP TABLE and CREATE TABLE statements are not executed because the default database is not db1, even though the statements name a table in db1.

    • The INSERT and CREATE TABLE statements are executed because the default database is db1, even though the CREATE TABLE statement names a table in a different database.

  • --pager[=command]

    Use the given command for paging query output. If the command is omitted, the default pager is the value of your PAGER environment variable. Valid pagers are less, more, cat [> filename], and so forth. This option works only on Unix and only in interactive mode. To disable paging, use --skip-pager. Section 4.5.1.2, “mysql Commands”, discusses output paging further.

  • --password[=password], -p[password]

    The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one.

    Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.

  • --pipe, -W

    On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections.

  • --plugin-dir=dir_name

    The directory in which to look for plugins. Specify this option if the --default-auth option is used to specify an authentication plugin but mysql does not find it. See Section 6.3.9, “Pluggable Authentication”.

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

  • --print-defaults

    Print the program name and all options that it gets from option files.

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --prompt=format_str

    Set the prompt to the specified format. The default is mysql>. The special sequences that the prompt can contain are described in Section 4.5.1.2, “mysql Commands”.

  • --protocol={TCP|SOCKET|PIPE|MEMORY}

    The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, “Connecting to the MySQL Server”.

  • --quick, -q

    Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file.

  • --raw, -r

    For tabular output, the “boxing” around columns enables one column value to be distinguished from another. For nontabular output (such as is produced in batch mode or when the --batch or --silent option is given), special characters are escaped in the output so they can be identified easily. Newline, tab, NUL, and backslash are written as \n, \t, \0, and \\. The --raw option disables this character escaping.

    The following example demonstrates tabular versus nontabular output and the use of raw mode to disable escaping:

    % mysql
mysql> SELECT CHAR(92);
+----------+
| CHAR(92) |
+----------+
| \        |
+----------+

% mysql -s
mysql> SELECT CHAR(92);
CHAR(92)
\\

% mysql -s -r
mysql> SELECT CHAR(92);
CHAR(92)
\

  • --reconnect

    If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost. To suppress reconnection behavior, use --skip-reconnect.

  • --safe-updates, --i-am-a-dummy, -U

    If this option is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. In addition, restrictions are placed on SELECT statements that produce (or are estimated to produce) very large result sets. If you have set this option in an option file, you can use --skip-safe-updates on the command line to override it. For more information about this option, see Section 4.5.1.6.4, “Using Safe-Updates Mode (--safe-updates)”.

  • --secure-auth

    Do not send passwords to the server in old (pre-4.1) format. This prevents connections except for servers that use the newer password format.

    As of MySQL 5.7.5, this option is deprecated and will be removed in a future MySQL release. It is always enabled and attempting to disable it (--skip-secure-auth, --secure-auth=0) produces an error. Before MySQL 5.7.5, this option is enabled by default but can be disabled.

    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them was removed in MySQL 5.7.5. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

  • --server-public-key-path=file_name

    The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. The file must be in PEM format. This option applies to clients that authenticate with the sha256_password authentication plugin. This option is ignored for accounts that do not authenticate with one of those plugins. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

    This option is available only if MySQL was built using OpenSSL.

    For information about the sha256_password and caching_sha2_password plugins, see Section 6.5.1.4, “SHA-256 Pluggable Authentication”, and Section 6.5.1.5, “Caching SHA-2 Pluggable Authentication”.

  • --shared-memory-base-name=name

    On Windows, the shared-memory name to use, for connections made using shared memory to a local server. The default value is MYSQL. The shared-memory name is case-sensitive.

    The server must be started with the --shared-memory option to enable shared-memory connections.

  • --show-warnings

    Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

  • --sigint-ignore

    Ignore SIGINT signals (typically the result of typing Control+C).

  • --silent, -s

    Silent mode. Produce less output. This option can be given multiple times to produce less and less output.

    This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

  • --skip-column-names, -N

    Do not write column names in results.

  • --skip-line-numbers, -L

    Do not write line numbers for errors. Useful when you want to compare result files that include error messages.

  • --socket=path, -S path

    For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

  • --ssl*

    Options that begin with --ssl specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See Section 6.4.2, “Command Options for Encrypted Connections”.

  • --syslog, -j

    This option causes mysql to send interactive statements to the system logging facility. On Unix, this is syslog; on Windows, it is the Windows Event Log. The destination where logged messages appear is system dependent. On Linux, the destination is often the /var/log/messages file.

    Here is a sample of output generated on Linux by using --syslog. This output is formatted for readability; each logged message actually takes a single line.

    Mar  7 12:39:25 myhost MysqlClient[20824]:
  SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
  DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
Mar  7 12:39:28 myhost MysqlClient[20824]:
  SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
  DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'

  • For more information, see Section 4.5.1.3, “mysql Logging”.

    The --syslog option was added in MySQL 5.7.1.

  • --table, -t

    Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

  • --tee=file_name

    Append a copy of output to the given file. This option works only in interactive mode. Section 4.5.1.2, “mysql Commands”, discusses tee files further.

  • --tls-version=protocol_list

    The protocols permitted by the client for encrypted connections. The value is a comma-separated list containing one or more protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.

    This option was added in MySQL 5.7.10.

  • --unbuffered, -n

    Flush the buffer after each query.

  • --user=user_name, -u user_name

    The MySQL user name to use when connecting to the server.

  • --verbose, -v

    Verbose mode. Produce more output about what the program does. This option can be given multiple times to produce more and more output. (For example, -v -v -v produces table output format even in batch mode.)

  • --version, -V

    Display version information and exit.

  • --vertical, -E

    Print query output rows vertically (one line per column value). Without this option, you can specify vertical output for individual statements by terminating them with \G.

  • --wait, -w

    If the connection cannot be established, wait and retry instead of aborting.

  • --xml, -X

    Produce XML output.

    <field name="column_name">NULL</field>

The output when --xml is used with mysql matches that of mysqldump --xml. See Section 4.5.4, “mysqldump — A Database Backup Program”, for details.

The XML output also uses an XML namespace, as shown here:

shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
<?xml version="1.0"?>

<resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">version</field>
<field name="Value">5.0.40-debug</field>
</row>

<row>
<field name="Variable_name">version_comment</field>
<field name="Value">Source distribution</field>
</row>

<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">i686</field>
</row>

<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">suse-linux-gnu</field>
</row>
</resultset>

  • (See Bug #25946.)

You can also set the following variables by using --var_name=value.

  • connect_timeout

    The number of seconds before connection timeout. (Default value is 0.)

  • max_allowed_packet

    The maximum size of the buffer for client/server communication. The default is 16MB, the maximum is 1GB.

  • max_join_size

    The automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.)

  • net_buffer_length

    The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

  • select_limit

    The automatic limit for SELECT statements when using --safe-updates. (Default value is 1,000.)

原文链接：https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html

posted @ 2018-11-23 16:54  xiaoblue  阅读(181)  评论(0)    收藏  举报