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]
options
is the available option for MySQL, one or more can be written at a time, even without writing. database
represents 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 dbname
Select the database.
mysql are usually expressed in two ways:
-
-
+ option word abbreviation + OPTION value * *;
-
--
+ 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-name
The 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-set
option 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=name
Execute 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)