1、查看实例上所有数据库的大小
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size, concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;
2、查看具体数据库中表的大小
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = '库名' group by TABLE_NAME order by data_length desc;
3、查看表的最后mysql修改时间（可以通过查看数据库中表的mysql修改时间，来确定mysql数据库是否已经长期不再使用。）
select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='数据库名';
4、与存储过程相关查询：
#查询存储过程
SELECT name FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';
#查看存储过程详细信息
SHOW CREATE PROCEDURE 数据库.存储过程名;
5、查看所有的存储过程：
show procedure status;
6、导出MySQL的存储过程
mysqldump -uroot -p -hlocalhost -P3306 -n -d -t -R DBName > procedurename.sql;
7、查看指定表的索引
show indexes from 表名;
8、生成指定的语句（例 清空指定库下所有表）
SELECT CONCAT('TRUNCATE TABLE ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='库名';
9、mysql 备份单个数据库
mysqldump -h 127.0.0.1 -P 3306 -ubackup -p -R --single-transaction --master-data=2 --databases xxdb | gzip > /data/backup/bak-3306/xxdb_`date +%F`.sql.gz --single-transaction: 基于此选项能实现热备InnoDB表；因此，不需要同时使用--lock-all-tables； --master-data=2 记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的 --databases xxdb 指定备份的数据库 gunzip xxx.gz gziip xxx
10、导入csv文件
load data infile '/tmp/book_activity_code_data20171017.csv' into table `bbq_activity`.`book_activity_code` fields terminated by ';' optionally enclosed by '"' escaped by '"' lines terminated by ' ';
11、linux查看消耗内存较大的前十个进程信息：
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head
12、linux查看消耗cpu较大的前十个进程信息：
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +3|head
13、查看最近几秒内的qps
select variable_name,sum(per_sec) as qps from (select straight_join lower(gs0.variable_name) as variable_name, (gs1.variable_value - gs0.variable_value)/5 as per_sec from ( select variable_name ,variable_value from information_schema.global_status where variable_name in ('com_select','com_update','com_insert','com_replace','com_delete') union all select '',sleep(5) from dual ) as gs0 join information_schema.global_status gs1 using (variable_name)) t group by variable_name with rollup;
14、查看linux 逻辑cpu核数
cat /proc/cpuinfo | grep 'model name' | wc -l
15、常用查看命令：
(1)QPS(每秒Query量) QPS = Questions(or Queries) / seconds mysql > show global status like 'Question%'; (2)TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds mysql > show global status like 'Com_commit'; mysql > show global status like 'Com_rollback'; (3)key Buffer 命中率 mysql>show global status like 'key%'; key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% (4)InnoDB Buffer命中率 mysql> show status like 'innodb_buffer_pool_read%'; innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% (5)Query Cache命中率 mysql> show status like 'Qcache%'; Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; (6)Table Cache状态量 mysql> show global status like 'open%'; 比较 open_tables 与 opend_tables 值 (7)Thread Cache 命中率 mysql> show global status like 'Thread%'; mysql> show global status like 'Connections'; Thread_cache_hits = (1 - Threads_created / connections ) * 100% (8)锁定状态 mysql> show global status like '%lock%'; Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重 Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的
16：查看统计mysql的连接数
select DB,count(1) from information_schema.PROCESSLIST group by DB;
17：kill 指定的查询线程
--mysql -S /tmp/mysql-3306.sock -ss -e "show processlist" | awk '{print "kill "$1";"}' |grep xxxx | mysql
获取要kill的线程ID：
/data/app/mysql/bin/mysql -u root -p -S /data/mysql_data/node-xxx/mysqld.sock -e "show processlist" |grep 'xxxx' | awk '{print "kill "$1";"}'
18：mysql中锁的查看
mysql查看锁表情况 mysql> show status like 'Table%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Table_locks_immediate | 105 | | Table_locks_waited | 3 | +----------------------------+----------+ Table_locks_immediate 指的是能够立即获得表级锁的次数 Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数 查看正在被锁定的的表 show OPEN TABLES where In_use > 0;
19：清空指定库下表
select CONCAT('TRUNCATE TABLE ',table_name,';') from information_schema.tables where TABLE_SCHEMA = 'db1'
20：修改存储过程的属性
--设置为DEFINER，则创建存储过程的用户需要存在、并且有调用存储过程权限、有访问存储过程里面对象的权限，每次调用都会对definer=root@localhost审核，看其是否存在并由相应的权限
update mysql.proc set security_type='DEFINER' where db='xxdb' and name='proc_xxx';
--设置为INVOKER，则每次调用不会去审核definer对应的账户是否存在，只需要调用存储过程的用户有执行存储过程权限，访问存储过程里面包含对象的权限即可
update mysql.proc set security_type='INVOKER' where db='xxdb' and name='proc_xxx';
与存储过程本身有关的权限有三类，分别是CREATE ROUTINE, ALTER ROUTINE, EXECUTE。一般来说如果用户需要有创建、删除存储过程权限，需要赋予CREATE ROUTINE即可；如果有修改存储过程权限，需要赋予ALTER ROUTINE即可；如果需要有调用存储过程权限，需要赋予EXECUTE权限即可。 但MYSQL本身对存储过程定义的语法结构有些限制，也会对用户调用权限做严格的筛选，
主要与存储过程定义参数：Definer 和 Security_type有关，前者是创建存储过程的用户，
一般是表现形式为root@localhost等；而Security_type主要分为DEFINER | INVOKER，主要用以审核调用存储过程的安全审核，
如果设置为DEFINER，则创建存储过程的用户需要存在、并且有调用存储过程权限、有访问存储过程里面对象的权限，每次调用都会对definer=root@localhost审核，看其是否存在并由相应的权限，
如果设置为INVOKER，则每次调用不会去审核definer对应的账户是否存在，只需要调用存储过程的用户有执行存储过程权限，访问存储过程里面包含对象的权限即可。
创建存储过程语法以及注意事项：
语法
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER //
CREATE PROCEDURE myproc(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM students;
END
//
DELIMITER ;
分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
- IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT:该值可在存储过程内部被改变，并可返回
- INOUT:调用时指定，并且可被改变和返回
过程体
过程体的开始与结束使用BEGIN与END进行标识。
IN参数例子
DELIMITER //
CREATE PROCEDURE in_param(IN p_in int)
BEGIN
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//
DELIMITER ;#调用
SET @p_in=1;
CALL in_param(@p_in);SELECT @p_in;
21、mysql锁的处理
解除正在死锁的状态有两种方法： 第一种： 1.查询是否锁表 show OPEN TABLES where In_use > 0; 2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程） show processlist 3.杀死进程id（就是上面命令的id列） kill id 第二种： 1.查看下在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 2.杀死进程id（就是上面命令的trx_mysql_thread_id列） kill 线程ID 例子： 查出死锁进程：SHOW PROCESSLIST 杀掉进程 KILL 420821; 其它关于查看死锁的命令： 1：查看当前的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 2：查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 3：查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
22/查看mysql的QPS 、RPS的例子
QPS:每秒的查询数 TPS:每秒的事物量 QPS的计算方法 Questions = SHOW GLOBAL STATUS LIKE 'Questions'; Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'; QPS=Questions/Uptime 例如: mysql> show global status like 'questions'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | Questions | 449106827 | +---------------+-----------+ 1 row in set (0.00 sec) mysql> show global status like 'uptime'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 238722 | +---------------+--------+ 1 row in set (0.00 sec) mysql> select 449106827/238722 as 'QPS VALUE' from dual; +-----------+ | QPS VALUE | +-----------+ | 1881.2963 | +-----------+ 1 row in set (0.00 sec) ___________________________________________________________ TPS的计算方法 Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit'; Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback'; Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'; TPS=(Com_commit + Com_rollback)/Uptime 例如: mysql> SHOW GLOBAL STATUS LIKE 'Com_commit'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Com_commit | 71050554 | +---------------+----------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Com_rollback | 537161 | +---------------+--------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Uptime'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 238827 | +---------------+--------+ 1 row in set (0.00 sec) mysql> select (71050554+537161)/238827 as 'TPS VALUE' from dual; +-----------+ | TPS VALUE | +-----------+ | 299.7472 | +-----------+ 1 row in set (0.00 sec)
23、查看mysql的status信息
(1)QPS(每秒Query量) QPS = Questions(or Queries) / seconds mysql > show global status like 'Question%';
QPS的计算方法
Questions = SHOW GLOBAL STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'; (2)TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds mysql > show global status like 'Com_commit'; mysql > show global status like 'Com_rollback'; (3)key Buffer 命中率 mysql>show global status like 'key%'; key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% (4)InnoDB Buffer命中率 mysql> show status like 'innodb_buffer_pool_read%'; innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% (5)Query Cache命中率 mysql> show status like 'Qcache%'; Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; (6)Table Cache状态量 mysql> show global status like 'open%'; 比较 open_tables 与 opend_tables 值 (7)Thread Cache 命中率 mysql> show global status like 'Thread%'; mysql> show global status like 'Connections'; Thread_cache_hits = (1 - Threads_created / connections ) * 100% (8)锁定状态 mysql> show global status like '%lock%'; Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重 Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的 (9)复制延时量 mysql > show slave status 查看延时时间 (10) Tmp Table 状况(临时表状况) mysql > show status like 'Create_tmp%'; Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大， 可能是排序句子过多或者是连接句子不够优化 (11) Binlog Cache 使用状况 mysql > show status like 'Binlog_cache%'; 如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小 (12) Innodb_log_waits 量 mysql > show status like 'innodb_log_waits'; Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待
24、mysqldump备份使用了-A -B参数，如何实现恢复单表？
先用sed或awk将全库中的需要的表结构过滤出来
sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `SC`/!d;q' /opt/bak_2017-12-07.sql
再用grep将全库中相应的表内容过滤出来
grep 'INSERT INTO `SC`' /opt/bak_2017-12-07.sql
将1和2中过滤出来的SQL语句导入数据库即可
25、show full processlist 的结果的分类展示
show full processlist 结果分组排序展示 select ID,USER,HOST,DB,COMMAND,STATE,TIME,left(INFO,60) from information_schema.processlist where COMMAND !='Sleep' and USER !='root' order by DB,STATE; select COMMAND,count(1) from information_schema.processlist where USER !='root' group by COMMAND; select DB,count(1) from information_schema.processlist where COMMAND !='Sleep' and USER !='root' group by DB; select left(INFO,6) ,count(1) from information_schema.processlist where COMMAND !='Sleep' and USER !='root' group by left(INFO,6);
26:查看mysql的常用引擎的库表分布情况
select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema') and (engine='MyISAM' or engine='InnoDB') order by engine,table_schema;
select engine,count(1) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema') group by engine;