MySQL (MySQL Client connection tool)

Among the tools provided by MySQL, the DBA uses MySQL most frequently. MySQL here does not refer to the MySQL service, nor the MySQL database, but the client tool that connects the database. Similar to Oracle's Sqlplus.

Syntax: MySQL [options][database]

optionsis the available option for MySQL, one or more can be written at a time, even without writing. databaserepresents a connected database, can write only one or not at a time, if not write, after logging in to the database also need to useuse dbnameSelect the database.

mysql are usually expressed in two ways:

1. - + option word abbreviation + OPTION value * *;
2. -- + options for the full word += the actual value of the + option;

Example: Mysql-uroot-ppassword mysql--user=root--password=password

1. Connection options

-U,--user=name Specify the user name of the connection
-P,--Password=password Specify connection password
-H,--host=name Specify the server IP or domain name
-P,--port=3308 Specify the connection port

By default, if none of these options are written, MySQL will use ' user ' @ ' localhost ' and a blank password to connect to port 3306 on this machine. An empty user is automatically generated after MySQL is installed, which is why you can connect to the database using only the MySQL command.

If the client and server are on a single machine, you typically do not need to specify the-H option, otherwise you will need to specify the IP or host name of the MySQL service. If you do not specify a port, connect to port 3306 by default. Examples are as follows:　

# mysql-h 10.10.200.202-uroot-penter password:welcome to the MySQL monitor.  

2. Specify the client character set

--default-character-set=charset-nameThe character set option for the server. This option can be configured on the MY.CNF[mysqld]Group, it can also be used as a client character set option, or you can configure the[mysql]Group.Use the --default-character-set option to specify the client's character set when logging in to the database using the MySQL command。 For example, if you do not use--default-character-setoption when logging in to the database:

# mysql-h 10.10.200.202-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 75520Server version:5.6.28 Source distributioncopyright (c), +, Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show variables like ' chara% '; +--------------------------+------- ----------------------------------+| variable_name | Value |+--------------------------+-----------------------------------------+| character_set_client | Latin1 | | character_set_connection | Latin1 | | Character_set_database | UTF8 | | Character_set_filesystem | binary | |Character_set_results | Latin1 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/local/mysql-5.6.28/share/charsets/|+--------------------------+-----------------------------------------+

when using --default-character-set option when you log on to the database:

# mysql-h 10.10.200.202-uroot-p--default-character-set=utf8enter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 75542Server version:5.6.28 Source distributioncopyright (c), +, Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show variables like ' chara% '; +--------------------------+------- ----------------------------------+| variable_name | Value |+--------------------------+-----------------------------------------+| character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | UTF8 | | Character_set_filesystem |        Binary                          || Character_set_results | UTF8 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/local/mysql-5.6.28/share/charsets/|+--------------------------+-----------------------------------------+ 8 rows in Set (0.01 sec)

3. Execution Options

-e,--execute=nameExecute SQL statement and exit
This option allows you to execute SQL statements directly from the client without having to connect to the MySQL database, which is convenient for some script execution. You can use this method to execute multiple SQL statements consecutively, separating the statements with semicolons (;), for example:

# mysql-uroot-p MYSQL-E "Select Host,user from User" Enter password: +--------------------+--------+| Host               | user   |+--------------------+--------+| 10.10.200.201      | root   | | 10.10.200.201      | zabbix | | 127.0.0.1          | root |   |: 1                | root   | | localhost          |        | | localhost          | root | |   localhost          | zabbix | | tcxx-ops-mysql-202 |        | | tcxx-ops-mysql-202 | Root   |+--------------------+--------+

4. formatting options

-E,--Vertical   Displays output vertically   in field order,
-s,--Silent   Remove the line box from the SQL output results

# mysql-uroot-p MYSQL-E "Select Host,user from User"-eenter password: *************************** 1. Row ***************************host:127.0.0.1user:root*************************** 2. Row ***************************host::: 1user:root*************************** 3. Row ***************************host:localhostuser: *************************** 4. Row ***************************host:localhostuser:root*************************** 5. Row ***************************host:test-serveruser: *************************** 6. Row ***************************host:test-serveruser:root

 

MySQL (MySQL Client connection tool)