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)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)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)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)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)mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)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)mysql> show engine innodb statusG;
*************************** 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 specifiedmysql> 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)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)mysql> show events from mysql;
Empty set (0.00 sec)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)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)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)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)25、Show Slave Hosts 显示已注册到主库的从库主机信息；
26、Show Slave Status 显示从库线程的状态信息；
27、Show Table Status from | in DB 显示数据库中非临时表的信息；
mysql> mysql> show table status from testG;
*************************** 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 specifiedmysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t_emp |
+----------------+
2 rows in set (0.01 sec)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)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)