首页 > 代码库 > mysql(MySQL客户端连接工具)
mysql(MySQL客户端连接工具)
在MySQL提供的工具中,DBA使用最频繁的莫过于mysql。这里的mysql不是指MySQL服务,也不是mysql数据库,而是连接数据库的客户端工具。类似于Oracle的sqlplus。
语法: mysql [options][database]
options
是mysql的可用选项,一次可以写一个或者多个,甚至可以不写。database
表示连接的数据库,一次只能写一个或者不写,如果不写,在登录数据库后还需要使用use dbname
选择数据库。
mysql的选项通常有两种表达方式:
-
+选项单词的缩写+选项值**;--
+选项的完整单词+=
+选项的实际值;
例如:mysql -uroot -ppassword mysql --user=root --password=password
1.连接选项
-u,--user=name 指定连接的用户名
-p,--password=password 指定连接密码
-h,--host=name 指定服务器IP或域名
-P,--port=3308 指定连接端口
在默认情况下,如果这些选项都不写,mysql将会使用‘用户‘@‘localhost‘和空密码连接本机的3306端口。空用户会在mysql安装完毕后自动生成,这也就是仅使用mysql命令就能连到数据库的原因。
如果客户端和服务器在用一台机器上,通常不需要指定-h选项,否则需要指定mysql服务所在的IP或主机名。如果不指定端口,默认连接到3306端口。示例如下:
# 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) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>
2.指定客户端字符集
--default-character-set=charset-name
为服务器的字符集选项。该选项可以配置在my.cnf的[mysqld]
组中,同样也可以作为客户端字符集选项,也可以配置在[mysql]
组中。在使用mysql命令登录数据库时,使用--default-character-set
选项可以指定客户端的字符集。例如,如果未使用--default-character-set
选项登录数据库时:
# 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) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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/ |+--------------------------+-----------------------------------------+
当使用--default-character-set
选项登录数据库时:
# 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) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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.执行选项
-e,--execute=name
执行sql语句并退出
此选项可以直接在客户端执行SQL语句,而不用连接到MySQL数据库后再执行,对于一些脚本的执行,使用此法较为便利。可以使用此种方法连续执行多条SQL语句,语句之间用分号(;)分隔例如:
# 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.格式化选项
-E,--vertical
将输出按字段顺序垂直显示 -s,--silent
去掉SQL输出结果中的线条框
# 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客户端连接工具)