MySQL :: MySQL 8.4 Reference Manual :: 6.5.1.1 mysql Client Options (original) (raw)

6.5.1.1 mysql Client 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 6.2.2.2, “Using Option Files”.

To write a binary string expression so that it displays as a character string regardless of whether--binary-as-hex is enabled, use these techniques: 

mysql> SELECT CHAR(0x41 USING utf8mb4);  
+--------------------------+  
| CHAR(0x41 USING utf8mb4) |  
+--------------------------+  
| A                        |  
+--------------------------+  
mysql> SELECT CONVERT(UNHEX('41') USING utf8mb4);  
+------------------------------------+  
| CONVERT(UNHEX('41') USING utf8mb4) |  
+------------------------------------+  
| A                                  |  
+------------------------------------+

When mysql operates in interactive mode, this option is enabled by default. In addition, output from the status (or \s) command includes this line when the option is enabled implicitly or explicitly: 

Binary data as: Hexadecimal

To disable hexadecimal notation, use--skip-binary-as-hex

Name                     TTL   Class   Priority Weight Port Target  
_mysql._tcp.example.com. 86400 IN SRV  0        5      3306 host1.example.com  
_mysql._tcp.example.com. 86400 IN SRV  0        10     3306 host2.example.com  
_mysql._tcp.example.com. 86400 IN SRV  10       5      3306 host3.example.com  
_mysql._tcp.example.com. 86400 IN SRV  20       5      3306 host4.example.com

To use that DNS SRV record, invoke mysql like this: 

mysql --dns-srv-name=_mysql._tcp.example.com

mysql then attempts a connection to each server in the group until a successful connection is established. A failure to connect occurs only if a connection cannot be established to any of the servers. The priority and weight values in the DNS SRV record determine the order in which servers should be tried.
When invoked with--dns-srv-name,mysql attempts to establish TCP connections only.
The --dns-srv-name option takes precedence over the--host option if both are given. --dns-srv-name causes connection establishment to use themysql_real_connect_dns_srv() C API function rather thanmysql_real_connect(). However, if the connect command is subsequently used at runtime and specifies a host name argument, that host name takes precedence over any--dns-srv-name option given atmysql startup to specify a DNS SRV record.

mysql --local-infile=0 --load-data-local-dir=/my/local/data

When both --local-infile and--load-data-local-dir are given, the order in which they are given does not matter.
Successful use of LOCAL load operations within mysql also requires that the server permits local loading; seeSection 8.1.6, “Security Considerations for LOAD DATA LOCAL”

DELETE FROM db2.t2;  
USE db2;  
DROP TABLE db1.t1;  
CREATE TABLE db1.t1 (i INT);  
USE db1;  
INSERT INTO t1 (i) VALUES(1);  
CREATE TABLE db2.t1 (j INT);

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

$> mysql -t test -e "SELECT * FROM t1"  
+-------+  
| c1    |  
+-------+  
|   100 |  
|  1000 |  
| 10000 |  
|    10 |  
+-------+  
$> mysql --quick -t test -e "SELECT * FROM t1"  
+----------------------+  
| c1                   |  
+----------------------+  
|                  100 |  
|                 1000 |  
|                10000 |  
|                   10 |  
+----------------------+  
% mysql  
mysql> SELECT CHAR(92);  
+----------+  
| CHAR(92) |  
+----------+  
| \        |  
+----------+  
% mysql -s  
mysql> SELECT CHAR(92);  
CHAR(92)  
\\  
% mysql -s -r  
mysql> SELECT CHAR(92);  
CHAR(92)  
\  
mysql --user=user_name --register-factor=3

An account that requires registration for second and third authentication factors invokes the mysql client as follows: 

mysql --user=user_name --register-factor=2,3

If registration is successful, a connection is established. If there is an authentication factor with a pending registration, a connection is placed into pending registration mode when attempting to connect to the server. In this case, disconnect and reconnect with the correct--register-factor value to complete the registration.
Registration is a two-step process comprising_initiate registration_ and_finish registration_ steps. The initiate registration step executes this statement: 

ALTER USER user factor INITIATE REGISTRATION

The statement returns a result set containing a 32 byte challenge, the user name, and the relying party ID (seeauthentication_webauthn_rp_id).
The finish registration step executes this statement: 

ALTER USER user factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string'

The statement completes the registration and sends the following information to the server as part of the_authstring_: authenticator data, an optional attestation certificate in X.509 format, and a signature.
The initiate and registration steps must be performed in a single connection, as the challenge received by the client during the initiate step is saved to the client connection handler. Registration would fail if the registration step was performed by a different connection. The--register-factor option executes both the initiate and registration steps, which avoids the failure scenario described above and prevents having to execute the ALTER USER initiate and registration statements manually.
The --register-factor option is only available for the mysql and MySQL Shell clients. Other MySQL client programs do not support it.
For related information, seeUsing WebAuthn Authentication.

$> echo "SELECT * FROM t1" | mysql -t test  
+-------+  
| c1    |  
+-------+  
| a,c,d |  
| c     |  
+-------+  
$> echo "SELECT * FROM t1" | ./mysql -uroot -Nt test  
+-------+  
| a,c,d |  
|     c |  
+-------+  
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 6.5.1.3, “mysql Client Logging”.

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

The output when --xml is used with mysql matches that ofmysqldump --xml. SeeSection 6.5.4, “mysqldump — A Database Backup Program”, for details.
The XML output also uses an XML namespace, as shown here: 

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