【查阅】Mysql 常规运维命令

【运维常用】

【1】查看锁

mysql> show status like ‘Table%’; 
+—————————-+——–+ 
| Variable_name | Value | 
+—————————-+——–+ 
| Table_locks_immediate | 795505 | 
| Table_locks_waited | 0 | 
| Table_open_cache_hits | 0 | 
| Table_open_cache_misses | 0 | 
| Table_open_cache_overflows | 0 | 
+—————————-+——–+ 
5 rows in set (0.00 sec)

Table_locks_immediate 指的是能够立即获得表级锁的次数 
Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数，如果数量大，说明锁等待多，有锁争用情况

查看正在被锁定的的表 
show OPEN TABLES where In_use > 0;

mysql> show OPEN TABLES where In_use > 0; 
+————–+—————+——–+————-+ 
| Database | Table | In_use | Name_locked | 
+————–+—————+——–+————-+ 
| music | class_record | 1 | 0 | 
| vipswoole | chat_message | 3 | 0 | 
| music | user_account | 1 | 0 | 
| music | sales_channel | 1 | 0 | 
| music | class_room | 5 | 0 | 
| music | user | 1 | 0 | 
| music_school | user | 1 | 0 | 
+————–+—————+——–+————-+ 
7 rows in set (0.00 sec)

mysql>

如果查看到锁争用情况严重，可以再查看当前执行的SQL ： 
mysql>show processlist

(mysqladmin -uroot -p -P 3306 processlist)

mysqladmin命令有一个debug参数，可以分析当前MySQL服务的状态信息，同时也可以用来帮助我们定位当前锁的详细情况，这里我们通过该命令分析一下当前MySQL服务的详细状态，执行mysqladmin命令如下：

[root@phpmysql02 data]# mysqladmin -ujss -p -S /data/3306/mysql.sock debug

Enter password: 
debug会将状态信息生成到mysql的错误文件，一般锁的信息都会保存在最后几行，这里我们在操作系统层error log最后几行：

[root@phpmysql02 data]# tail -10 phpmysql02.err

Thread database.table_name Locked/Waiting Lock_type 
2 hdpic.t_wiki_zutu Waiting - write Highest priority write lock 
123890 hdpic.t_wiki_zutu_category Locked - read Low priority read lock 
123890 hdpic.t_wiki_zutu_photo Locked - read Low priority read lock 
123890 hdpic.t_wiki_zutu Locked - read Low priority read lock 
124906 hdpic.t_wiki_zutu Waiting - read Low priority read lock 
从上述信息可以看出，123890持有的读锁阻塞了2的写入和124906的读操作，这个状态符合我们的推论，接下来处理就比较单纯了，如果现状不可接受，不能继续等待，将123890杀掉，释放资源即可：

mysql> kill 123890;

Query OK, 0 rows affected (0.00 sec) 
再次执行show processlist查看：

使用系统表进行锁查询：

select r.trx_isolation_level, r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread, 
r.trx_state waiting_trx_state,lr.lock_mode waiting_trx_lock_mode,lr.lock_type waiting_trx_lock_type, 
lr.lock_table waiting_trx_lock_table,lr.lock_index waiting_trx_lock_index,r.trx_query waiting_trx_query, 
b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_trx_thread,b.trx_state blocking_trx_state, 
lb.lock_mode blocking_trx_lock_mode,lb.lock_type blocking_trx_lock_type,lb.lock_table blocking_trx_lock_table, 
lb.lock_index blocking_trx_lock_index,b.trx_query blocking_query 
from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id=w.blocking_trx_id 
inner join information_schema.innodb_trx r on r.trx_id=w.requesting_trx_id 
inner join information_schema.innodb_locks lb on lb.lock_trx_id=w.blocking_trx_id 
inner join information_schema.innodb_locks lr on lr.lock_trx_id=w.requesting_trx_id G

涉及的3张表说明：

information_shcema下的三张表（通过这三张表可以更新监控当前事物并且分析存在的锁问题） 
—— innodb_trx （ 打印innodb内核中的当前活跃（ACTIVE）事务） 
—— innodb_locks （ 打印当前状态产生的innodb锁 仅在有锁等待时打印） 
—— innodb_lock_waits （打印当前状态产生的innodb锁等待 仅在有锁等待时打印）

1) innodb_trx表结构说明 （摘取最能说明问题的8个字段） 
字段名 说明 
trx_id innodb存储引擎内部唯一的事物ID 
trx_state 
当前事物状态（running和lock wait两种状态） 
trx_started 
事物的开始时间 
trx_requested_lock_id 等待事物的锁ID，如trx_state的状态为Lock wait，那么该值带表当前事物等待之前事物占用资源的ID，若trx_state不是Lock wait 则该值为NULL 
trx_wait_started 事物等待的开始时间 
trx_weight 事物的权重，在innodb存储引擎中，当发生死锁需要回滚的时，innodb存储引擎会选择该值最小的进行回滚 
trx_mysql_thread_id mysql中的线程id, 即show processlist显示的结果 
trx_query 事物运行的SQL语句 
2）innodb_locks表结构说明

字段名 说明 
lock_id 锁的ID 
lock_trx_id 事物的ID 
lock_mode 锁的模式（S锁与X锁两种模式） 
lock_type 锁的类型 表锁还是行锁（RECORD） 
lock_table 要加锁的表 
lock_index 锁住的索引 
lock_space 锁住对象的space id 
lock_page 事物锁定页的数量，若是表锁则该值为NULL 
lock_rec 事物锁定行的数量，若是表锁则该值为NULL 
lock_data 事物锁定记录主键值，若是表锁则该值为NULL（此选项不可信） 
3）innodb_lock_waits表结构说明 
字段名 说明 
requesting_trx_id申请锁资源的事物ID 
requested_lock_id申请的锁的ID 
blocking_trx_id阻塞其他事物的事物ID 
blocking_lock_id阻塞其他锁的锁ID

可以根据这三张表进行联合查询，得到更直观更清晰的结果，可以参考如下SQL（可根据自己的分析习惯适进行调整）

【2】查看库大小

SELECT TABLE_SCHEMA,CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free_size,
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables 
GROUP BY TABLE_SCHEMA 
ORDER BY SUM(data_length) DESC

【3】如何释放表空间/收缩表空间/统计信息

详细参考：MySQL check table/optimize table/analyze table/REPAIR TABLE

optimize table tab_name;(alter table one type=innodb;) -- 收缩表空间
ANALYZE TABLE tab_name; -- 更新统计信息

【4】查看实例信息

登录mysql后，输入 s

【5】索引与表信息

/*查看是否包含非innodb的表*/
select * from information_schema.tables  where table_schema not in ('mysql','sys','information_schema','performance_schema','mosquitto') and ENGINE <> 'INNODB';


#查看库的总量
select count(1) from information_schema.SCHEMATA where schema_name not in ('mysql','information_schema','performance_schema','sys');
#查看表的总量
select count(1) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys');
#查看每个库下多少个表：
select TABLE_SCHEMA,count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') group by TABLE_SCHEMA;
#查看100W+的表的个数：
select count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and TABLE_ROWS >=1000000;
#查看10G+表的个数：
select count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and DATA_FREE+DATA_LENGTH+INDEX_LENGTH>=10737418240;
#查看非innodb表个数：
select count(*) from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys') and engine !='InnoDB';
表的碎片率排行top10
select table_schema,table_name,table_rows,data_length,index_length,data_free ,
data_free/(data_length+index_length+data_free) as free_rate
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
order by free_rate desc
limit 10
表的碎片大小排行top10
select table_schema,table_name,table_rows,data_length,index_length,data_free
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
order by data_free desc
limit 10
表的行数top10，
select table_schema,table_name,table_rows,data_length,index_length,data_free
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
order by table_rows desc
limit 10
表的体积top10
select table_schema,table_name,table_rows,data_length,index_length,data_free
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
order by data_length+index_length+data_free desc
limit 10
表的auto_increment top 10
select table_schema,table_name,table_rows,AUTO_INCREMENT
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
order by AUTO_INCREMENT  desc
limit 10
未使用的索引情况：总数，按表统计top10：
select count(1) from sys.schema_unused_indexes;
select object_schema,object_name,count(1) from sys.schema_unused_indexes group by object_schema,object_name order by count(1) desc limit 10;
查看指定库下指定表or所有表
select * from  information_schema.tables  where table_schema='mysql'
and  table_name='t1';
查看指定库下指定的表的列信息（能粗略查看索引信息）
select column_name,column_type,is_nullable,column_key from information_schema.columns where table_schema='svrabp' and  table_name='rabp_tra_bank_account';
准确查看索引信息：
select  index_name ,non_unique,concat('(',group_concat(column_name order by seq_in_index),')') as index_column
from information_schema.statistics
where  table_schema='svrabp' and  table_name='rabp_tra_bank_accountt3'
group by index_name,non_unique;
查看指定库下指定表未使用的索引：
select * from sys.schema_unused_indexes
where object_schema='cfbtrust' and object_name='bank_sales_order_detail';
查询实例中有哪些用户：
select user,host  from mysql.user where 1=1;
查询库下表的详细信息
select TABLE_NAME,table_comment,AUTO_INCREMENT,table_collation from information_schema.tables where table_schema='car';
查询表的列信息（类似于DESC 表）
SELECT TABLE_NAME,
       COLUMN_NAME,
        column_type,
        COLUMN_COMMENT,
        COLUMN_DEFAULT,
        IS_NULLABLE,
        COLUMN_KEY,
        CHARACTER_SET_NAME,
        COLLATION_NAME,
        extra
FROM information_schema.COLUMNS WHERE table_schema='car_ota'
ORDER BY TABLE_NAME,ORDINAL_POSITION