mysql怎么用show视图_【MySQL】MySQL Show命令演示

1、Show Binary logs 列出服务器上的Binary log文件；

mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000009 | 201 |

| mysql-bin.000010 | 201 |

| mysql-bin.000011 | 201 |

| mysql-bin.000012 | 154 |

+------------------+-----------+

4 rows in set (0.00 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000009 | 201 |

| mysql-bin.000010 | 201 |

| mysql-bin.000011 | 201 |

| mysql-bin.000012 | 154 |

+------------------+-----------+

4 rows in set (0.00 sec)注：show binary logs和show master logs等价;

2、Show Binlog Events 显示Binary log中的Events内容；

mysql> show binlog events in 'mysql-bin.000010';

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql-bin.000010 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |

| mysql-bin.000010 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000010 | 154 | Rotate | 1 | 201 | mysql-bin.000011;pos=4 |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

3 rows in set (0.00 sec)3、Show Character Set 显示字符集；

mysql> show character set like '%utf8%';

+---------+---------------+--------------------+--------+

| Charset | Description | Default collation | Maxlen |

+---------+---------------+--------------------+--------+

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

+---------+---------------+--------------------+--------+

2 rows in set (0.00 sec)4、Show Collation 显示排序规则；

mysql> show collation where charset='utf8';

+--------------------------+---------+-----+---------+----------+---------+

| Collation | Charset | Id | Default | Compiled | Sortlen |

+--------------------------+---------+-----+---------+----------+---------+

| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |

| utf8_bin | utf8 | 83 | | Yes | 1 |

| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |

| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |

| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |

| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |

| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |

| utf8_polish_ci | utf8 | 197 | | Yes | 8 |

| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |

| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |

| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |

| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |

| utf8_czech_ci | utf8 | 202 | | Yes | 8 |

| utf8_danish_ci | utf8 | 203 | | Yes | 8 |

| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |

| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |

| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |

| utf8_roman_ci | utf8 | 207 | | Yes | 8 |

| utf8_persian_ci | utf8 | 208 | | Yes | 8 |

| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |

| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |

| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |

| utf8_german2_ci | utf8 | 212 | | Yes | 8 |

| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |

| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |

| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |

| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |

+--------------------------+---------+-----+---------+----------+---------+

27 rows in set (0.00 sec)5、Show Columns 显示列信息；

mysql> show columns from test.t1;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(100) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> show full columns from test.t1;

+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |

+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| id | int(11) | NULL | YES | | NULL | | select,insert,update,references | |

| name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |

+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

2 rows in set (0.00 sec)6、Show Create Database 显示数据库创建语句；

mysql> show create database test;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)7、Show Create Event 显示Job创建语句；

8、Show Create Function 显示函数创建语句；

9、Show Create Procedure 显示过程创建语句；

10、Show Create Table 显示表创建语句；

11、Show Create Trigger 显示触发器创建语句；

12、Show Create User 显示用户创建语句；

13、Show Create View 显示视图创建语句；

14、Show Databases | Schemas 显示数据库列表；

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| binlog |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

6 rows in set (0.00 sec)15、Show Engine engine_name Status 显示存储引擎的操作信息；

mysql> show engine innodb status\G;

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

2018-01-29 21:30:42 0x7f2198102700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 11 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 6848 srv_idle

srv_master_thread log flush and writes: 6855

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 8

OS WAIT ARRAY INFO: signal count 8

RW-shared spins 0, rounds 17, OS waits 8

RW-excl spins 0, rounds 0, OS waits 0

RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 17.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

------------

TRANSACTIONS

------------

Trx id counter 44815

Purge done for trx's n:o < 44814 undo n:o < 0 state: running but idle

History list length 2

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421257677638368, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421257677637456, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

--------

FILE I/O

--------

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (write thread)

I/O thread 7 state: waiting for completed aio requests (write thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (write thread)

Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,

ibuf aio reads:, log i/o's:, sync i/o's:

Pending flushes (fsync) log: 0; buffer pool: 0

266 OS file reads, 118 OS file writes, 44 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

merged operations:

insert 0, delete mark 0, delete 0

discarded operations:

insert 0, delete mark 0, delete 0

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

Hash table size 34673, node heap has 0 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 2602648

Log flushed up to 2602648

Pages flushed up to 2602648

Last checkpoint at 2602639

0 pending log flushes, 0 pending chkp writes

31 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 137428992

Dictionary memory allocated 125332

Buffer pool size 8191

Free buffers 7919

Database pages 272

Old database pages 0

Modified db pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 233, created 39, written 74

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 272, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Process ID=5777, Main thread ID=139782327891712, state: sleeping

Number of rows inserted 51, updated 0, deleted 0, read 58

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.00 sec)

ERROR:

No query specified16、Show Engines 显示服务器存储引擎的状态信息；

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)17、Show Errors 显示错误信息；

mysql> drop table m1;

ERROR 1051 (42S02): Unknown table 'test.m1'

mysql> show errors;

+-------+------+-------------------------+

| Level | Code | Message |

+-------+------+-------------------------+

| Error | 1051 | Unknown table 'test.m1' |

+-------+------+-------------------------+

1 row in set (0.00 sec)18、Show Events 显示Job信息；

mysql> show events from mysql;

Empty set (0.00 sec)19、Show Grants 显示授权信息；

mysql> show grants for alen;

+----------------------------------------+

| Grants for alen@% |

+----------------------------------------+

| GRANT USAGE ON *.* TO 'alen'@'%' |

| GRANT SELECT ON `test`.* TO 'alen'@'%' |

+----------------------------------------+

2 rows in set (0.00 sec)20、Show Index 显示索引信息；

21、Show Master Status 显示主库的二进制日志状态信息；

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000012 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)22、Show Privileges 显示MySQL服务器支持的权限列表；

mysql> show privileges;

+-------------------------+---------------------------------------+-------------------------------------------------------+

| Privilege | Context | Comment |

+-------------------------+---------------------------------------+-------------------------------------------------------+

| Alter | Tables | To alter the table |

| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |

| Create | Databases,Tables,Indexes | To create new databases and tables |

| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |

| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |

| Create view | Tables | To create new views |

| Create user | Server Admin | To create new users |

| Delete | Tables | To delete existing rows |

| Drop | Databases,Tables | To drop databases, tables, and views |

| Event | Server Admin | To create, alter, drop and execute events |

| Execute | Functions,Procedures | To execute stored routines |

| File | File access on server | To read and write files on the server |

| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |

| Index | Tables | To create or drop indexes |

| Insert | Tables | To insert data into tables |

| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |

| Process | Server Admin | To view the plain text of currently executing queries |

| Proxy | Server Admin | To make proxy user possible |

| References | Databases,Tables | To have references on tables |

| Reload | Server Admin | To reload or refresh tables, logs and privileges |

| Replication client | Server Admin | To ask where the slave or master servers are |

| Replication slave | Server Admin | To read binary log events from the master |

| Select | Tables | To retrieve rows from table |

| Show databases | Server Admin | To see all databases with SHOW DATABASES |

| Show view | Tables | To see views with SHOW CREATE VIEW |

| Shutdown | Server Admin | To shut down the server |

| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |

| Trigger | Tables | To use triggers |

| Create tablespace | Server Admin | To create/alter/drop tablespaces |

| Update | Tables | To update existing rows |

| Usage | Server Admin | No privileges - allow connect only |

+-------------------------+---------------------------------------+-------------------------------------------------------+

31 rows in set (0.00 sec)23、Show Processlist 显示正在运行的线程信息；

mysql> show processlist;

+----+------+---------------------+------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+---------------------+------+---------+------+----------+------------------+

| 4 | root | 192.168.1.107:50638 | NULL | Sleep | 7572 | | NULL |

| 5 | root | 192.168.1.107:50639 | NULL | Sleep | 1 | | NULL |

| 6 | root | 192.168.1.107:50640 | NULL | Sleep | 256 | | NULL |

| 7 | root | 192.168.1.107:50641 | NULL | Sleep | 256 | | NULL |

| 8 | root | localhost | test | Query | 0 | starting | show processlist |

+----+------+---------------------+------+---------+------+----------+------------------+

5 rows in set (0.00 sec)24、Show Relaylog Events 显示复制从库的relaylog信息；

25、Show Slave Hosts 显示已注册到主库的从库主机信息；

26、Show Slave Status 显示从库线程的状态信息；

27、Show Table Status from | in DB 显示数据库中非临时表的信息；

mysql> mysql> show table status from test\G;

*************************** 1. row ***************************

Name: t1

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2018-01-29 19:39:59

Update_time: 2018-01-29 19:49:44

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

*************************** 2. row ***************************

Name: t_emp

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 8

Avg_row_length: 2048

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2018-01-26 23:33:38

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

2 rows in set (0.00 sec)

ERROR:

No query specified28、Show Tables 显示数据库中表的列表；

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t1 |

| t_emp |

+----------------+

2 rows in set (0.01 sec)29、Show Global | Session Variable 显示变量信息；

mysql> show variables like '%log%bin%';

+----------------------------------+---------------------------------------+

| Variable_name | Value |

+----------------------------------+---------------------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binlog/mysql-bin |

| log_bin_index | /var/lib/mysql/binlog/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_statements_unsafe_for_binlog | ON |

| sql_log_bin | ON |

+----------------------------------+---------------------------------------+

7 rows in set (0.00 sec)30、Show Warnings 显示警告信息；

mysql> select 19/0;

+------+

| 19/0 |

+------+

| NULL |

+------+

1 row in set, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+---------------+

| Level | Code | Message |

+---------+------+---------------+

| Warning | 1365 | Division by 0 |

+---------+------+---------------+

1 row in set (0.00 sec)