mysql查询下个月过生日的说说_mysql 常用查询

1、查看实例上所有数据库的大小

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),'MB') asdata_size,

concat(truncate(sum(index_length)/1024/1024,2),'MB') asindex_sizefrominformation_schema.tablesgroup byTABLE_SCHEMAorder by data_length desc;

2、查看具体数据库中表的大小

select TABLE_NAME, concat(truncate(data_length/1024/1024,2),'MB') asdata_size,

concat(truncate(index_length/1024/1024,2),'MB') asindex_sizefrom information_schema.tables where TABLE_SCHEMA = '库名'

group byTABLE_NAMEorder 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 terminatedby ';' optionally enclosed by '"' escaped by '"'lines terminatedby '\r\n';

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)keyBuffer 命中率

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)TableCache状态量

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 指的是不能立即获取表级锁而需要等待的次数

查看正在被锁定的的表

showOPEN 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.procset 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.查询是否锁表

showOPEN TABLES where In_use > 0;2.查询进程(如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程)

show processlist3.杀死进程id(就是上面命令的id列)killid

第二种：1.查看下在锁的事务SELECT * FROMINFORMATION_SCHEMA.INNODB_TRX;2.杀死进程id(就是上面命令的trx_mysql_thread_id列)kill线程ID

例子：

查出死锁进程：SHOW PROCESSLIST

杀掉进程KILL 420821;

其它关于查看死锁的命令：1：查看当前的事务SELECT * FROMINFORMATION_SCHEMA.INNODB_TRX;2：查看当前锁定的事务SELECT * FROMINFORMATION_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.00sec)

mysql> show global status like 'uptime';+---------------+--------+

| Variable_name | Value |

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

| Uptime | 238722 |

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

1 row in set (0.00sec)

mysql> select 449106827/238722 as 'QPS VALUE' fromdual;+-----------+

| QPS VALUE |

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

| 1881.2963 |

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

1 row in set (0.00sec)

___________________________________________________________

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.00sec)

mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback';+---------------+--------+

| Variable_name | Value |

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

| Com_rollback | 537161 |

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

1 row in set (0.00sec)

mysql> SHOW GLOBAL STATUS LIKE 'Uptime';+---------------+--------+

| Variable_name | Value |

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

| Uptime | 238827 |

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

1 row in set (0.00sec)

mysql> select (71050554+537161)/238827 as 'TPS VALUE' fromdual;+-----------+

| 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)keyBuffer 命中率

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)TableCache状态量

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的话，表明 innodblogbuffer 因为空间不足而等待

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);