Shulou(Shulou.com)06/01 Report--

View help commands

Mysql > help;-Total help

Mysql > help show;-- View show's help commands

Mysql > help create;-- View help commands for create

Mysql > help select;-- View help commands for select

Mysql > help flush;-- View help commands for flush

Mysql > help reset;-- View help commands for reset

Query the basic information of an instance

Status

View the creation script for the database db1

Mysql > show create database db1

View the creation script for table table1

Mysql > show create table table1\ G

Query which fields of the table1 table have an index. A value of Key means that the field has an index.

Desc table1

Query the index of the table1 table, and you can also see the cardinality information

Show index from table1

View the execution plan of the select statement

Explain extended select * from T1

Desc extended select * from T1

View a parameter

Show global variables like'% XX%'

Show global variables where variable_name in ('XX')

Check whether the database is read-only

Show variables like 'read_only'

View a status

Show status like'% YY%'

View the number of clients currently connected

Show status like 'Threads_connected'

Check the number of connections to the server

Show status like 'Connections'

Check the maximum number of connections ever made

Show status like 'Max_used_connections'

View mysql threads

Show full processlist

Check how many databases there are.

Show databases

Check how many tables are under the current database

Show tables

To view various engine information, Support is listed as DEFAULT and represents as the default storage engine of the current instance.

Show engines

View the storage engine settings of the current instance

Show variables like'% engi%'

View LSN (the latest number of Log sequence number's current redo log)

Show engine innodb status

View the current database

Select database ()

View the current database server version

Select version ()

View current user

Select user ()

Query specific SQL of unsubmitted session

Show engine innodb status; looks at lock struct information, for example, the following thread is 8.

1 lock struct (s), heap size 1136, 0 row lock (s), undo log entries 1

MySQL thread id 8, OS thread handle 1358473536, query id 1271 localhost root cleaning up

Select sql_text from performance_schema.events_statements_current where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=8)

Or

Select b.conn_id,b.thd_id,a.last_statement from sys.session a,sys.processlist b where a.thd_id=b.thd_id and a.conn_id=b.conn_id and b.conn_id=8

Query lock source thread

Select a. From information_schema.INNODB_TRX a where trx_id in (select blocking_trx_id from information_schema.INNODB_LOCK_WAITS)

Query locked thread

Select a. From information_schema.INNODB_TRX a where trx_id in (select requesting_trx_id from information_schema.INNODB_LOCK_WAITS)

Query who blocked the XX thread select trx_mysql_thread_id blocking_thread,trx_started,trx_query from information_schema.INNODB_TRX where trx_id in

(select blocking_trx_id from information_schema.INNODB_LOCK_WAITS where requesting_trx_id in

(select trx_id from information_schema.INNODB_TRX where trx_mysql_thread_id='XX')

)

After version 5.7.9, it is recommended to use sys.schema_table_lock_waits and sys.innodb_lock_waits to check congestion, but you need to enable the parameter performance_schema=ON.

Sys.schema_table_lock_waits

Select * from sys.schema_table_lock_waits where object_name= `'test'.'t' `\ G

Select blocking_pid from sys.schema_table_lock_waits where object_name= `'test'.'t' `\ G

Https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html

Sys.innodb_lock_waits

Select * from sys.innodb_lock_waits where locked_table= `'test'.'t' `\ G

Select blocking_pid from sys.innodb_lock_waits where locked_table= `'test'.'t' `\ G

Https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html

Query the permissions of the user1 user

Show grants for user1

View all binary logs

Show binary logs

Show master logs

View current binary log file status

Show master status

Refresh the binary log

Flush binary logs

Delete all logs before a binary log

Purge binary logs to 'mysql-bin.000003'

Delete all binary log

Mysql > reset master

Query how many slow query records there are

Mysql > show global status like'% Slow_queries%'

Execute a 10-second query

Mysql > select sleep (10)

Find transactions with a duration of more than 60s

Select * from information_schema.innodb_trx where TIME_TO_SEC (timediff (now (), trx_started)) > 60

Query the size of data and indexes in all databases

Select round (sum (data_length+index_length) / 1024ax 1024) as total_mb,round (sum (data_length) / 1024ax 1024) as data_mb,round (sum (index_length) / 1024ax 1024) as index_mb from information_schema.tables

Query the engine, capacity, and total number of tables for each database

Select table_schema,engine

Round (sum (data_length+index_length) / 1024ax 1024) as total_mb

Round (sum (data_length) / 1024ax 1024) as data_mb

Round (sum (index_length) / 1024ax 1024) as index_mb

Count (*) as tables

From information_schema.tables

Where table_schema not in ('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA') group by table_schema,engine order by 3 desc

Query the SQL of CPU consumption

Mysql > show full processlist

Find the largest Time, and its corresponding ID column is the thread ID that consumes the most cpu, and the corresponding Info column is the specific SQL.

Or

Check the slow query log, find the row with the highest Query_ time value, and record its thread ID number and specific SQL

Check what slave are available on master

Mysql > select * from information_schema.processlist as p where p.command = 'Binlog Dump'

Or

Mysql > show slave hosts; (this method needs to specify the-- report-host=HOSTNAME option when starting from the service, where HOSTNAME is any name.)

Kill thread SQL, the following two must be used at the same time, in which kill thread_id=kill connection thread_id

Mysql > kill query thread_id

Mysql > kill thread_id

View of all database events

Select db,name,last_executed,status from mysql.event

Event viewing for a single database

Show events from dbname\ G

Disable an event in a database

Alter event dbname.eventname disable

Re-collect the statistics of the table

Analyze table tablename

Rebuild the table

Alter table tablename engine=innodb

Modify the storage engine of the table to innodb

Alter table tablename engine=innodb

Optimization table

Optimize table tablename=analyze table tablename + alter table tablename engine=innodb

Modify the definer of proc stored procedures, such as changing 'dev_user@%'' to 'prod_user@%''

Select db,name,type,definer from mysql.proc\ G

Update mysql.proc set definer='prod_user@%' where definer='dev_user@%'

Modify the definer of the event event, such as changing 'dev_user@%'' to 'prod_user@%''

Select db,name,definer from mysql.event\ G

Update mysql.event set definer='prod_user@%' where definer='dev_user@%'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.