您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
马哥linux运维学习笔记-mysql系列之四mysql客户端工具的使用
发布时间：2019-03-13 20:18:36编辑：雪饮阅读（）
mysql -D
-D可指定连接时默认自动use到某个库
[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@localhost mysql]# mysql -D mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql>
脚本模式
一个sql文件如：
[root@localhost ~]# cat test.sql
create database testdb;
create table testdb.tb1(id int,name char(20));
该文件中存放着sql语句，可以通过脚本模式执行，脚本模式有两种，一种是不进入mysql控制台，一种是进入mysql控制台。
不进入mysql控制台导入sql
[root@localhost ~]# mysql < test.sql
进入mysql控制台导入sql
mysql> drop database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> \. /root/test.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
定义mysql语句结束符
默认情况下mysql语句结束符是分号
mysql> show databases
->
->
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
7 rows in set (0.01 sec)
通过delimiter可以定义mysql语句结束符
mysql> delimiter //
mysql> show databases;
-> //
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
也可以用\d来定义，\d是delimiter的简写
mysql> \d ;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
另外关于语句的结束符仅仅是服务端语句需要结束符，若是客户端语句则无需结束符，如
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1
Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.28-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 4 hours 41 min 25 sec
Threads: 1 Questions: 36 Slow queries: 0 Opens: 38 Flush tables: 1 Open tab les: 27 Queries per second avg: 0.002
--------------
；
再另外，当我们之前定义了一个语句结束符，但是由于某些原因导致我们忘记了之前定义的结束符，此时使用\g无论如何也能将语句执行，如
mysql> select user from user\g
+------+
| user |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)
语句终止
当你在mysql控制台中写了一个sql，而该sql还没有结束，即还没有写结束符的时候，此时使用\c并回车即可终止该语句的执行，如：
mysql> select user,host from us\c
数据库重连
在某些时候我们需要用到数据库重连，此时我们不需要退出当前控制台，而是直接使用命令\r即可，如
mysql> \r
Connection id: 7
Current database: *** NONE ***
mysql输出html格式数据
mysql默认输出的数据样式是文本字符拼接出来的表格，其实mysql的输出格式是可以定义的，比如这里让其输出html的表格代码形式
[root@localhost ~]# mysql --html
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Database changed
mysql> select user from user;
<TABLE BORDER=1><TR><TH>user</TH></TR><TR><TD>root</TD></TR><TR><TD>root</TD></TR><TR><TD>root</TD></TR></TABLE>3 rows in set (0.00 sec)
mysql获取命令帮助
当某个命令的用法忘记的时候可以使用mysql的help命令查看其用法，如
mysql> help alter
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
GRANT
SPATIAL
mysqladmin
mysqladmin是一个mysql工具，有许多功能。
创建数据库
[root@localhost ~]# mysqladmin create hellodb
检查一个mysql数据库是否在线
检查本机
[root@localhost ~]# mysqladmin ping
mysqld is alive
检查远程主机
[root@localhost ~]# mysqladmin -u root -p -h 192.168.1.11 ping
Enter password:
mysqladmin: connect to server at '192.168.1.11' failed
error: 'Can't connect to MySQL server on '192.168.1.11' (113)'
Check that mysqld is running on 192.168.1.11 and that the port is 3306.
You can check this by doing 'telnet 192.168.1.11 3306'
查看mysql服务器中当前的线程列表
[root@localhost ~]# mysqladmin processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 11 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
查看mysql服务器状态
[root@localhost ~]# mysqladmin status
Uptime: 18229 Threads: 1 Questions: 84 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 41 Queries per second avg: 0.004
；
通过sleep可以设置每间隔多久打印出最新状态
[root@localhost ~]# mysqladmin status --sleep 2
Uptime: 18300 Threads: 1 Questions: 85 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 41 Queries per second avg: 0.004
Uptime: 18302 Threads: 1 Questions: 85 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 41 Queries per second avg: 0.004
Uptime: 18304 Threads: 1 Questions: 85 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 41 Queries per second avg: 0.004
通过count可以定义最多打印多少次最新状态
[root@localhost ~]# mysqladmin status --sleep 2 --count 2
Uptime: 18409 Threads: 1 Questions: 86 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 41 Queries per second avg: 0.004
Uptime: 18411 Threads: 1 Questions: 86 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 41 Queries per second avg: 0.004
查看状态变量
[root@localhost ~]# mysqladmin extended-status
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 1 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 3244 |
| Bytes_sent | 24634 |
| Com_admin_commands | 1 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
查看mysql服务器变量
[root@localhost ~]# mysqladmin variables
+---------------------------------------------------+--------------------------------------------------------------------------------------------------------- ---------------+
| Variable_name | Value |
+---------------------------------------------------+--------------------------------------------------------------------------------------------------------- ---------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| binlog_stmt_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1
关闭mysql服务器
[root@localhost ~]# mysqladmin shutdown
查看mysql服务器版本、状态等信息
[root@localhost ~]# mysqladmin version
mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.5.28-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.200
关键字词：linux,mysql,客户端