查询某个数据库表容量

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;

查询所有数据库容量

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;

连接数

查询mysql最大连接数

show variables like '%max_connections%';

查询已经使用的连接数

show global status like 'Max_used_connections';

设置最大连接数

set GLOBAL max_connections=256;或者修改mysql配置文件my.cnf，在[mysqld]段中添加或修改max_connections值： max_connections=256

导出

导出表数据

mysqldump

mysqldump --skip-lock-table -hxx -Pxx -uxx -pxx db table > file

mysql -e

# -N代表不要列名

mysql -hxx -Pxx -uxx -pxx -N -e "query statement" db > file

update - select联用

# 场景1

UPDATE plugin_assemble_job_build build

SET build.exe_type =(

SELECT

conf.exe_type

FROM

plugin_assemble_job_conf conf

WHERE

build.job_conf_id = conf.id

);

# 场景2

UPDATE PLUGIN_MYSCAN_BUILD BUILD

INNER JOIN PLUGIN_MYSCAN_CONF CONF ON BUILD.JOB_CONF_ID = CONF.ID

SET BUILD.PDB_PRODUCT=CONF.PDB_PRODUCT,

BUILD.DESCRIPTION=CONF.DESCRIPTION,

BUILD.TASK_TYPE=CONF.TASK_TYPE,

BUILD.SCAN_URL=CONF.SCAN_URL,

BUILD.FTP_ADDRESS=CONF.FTP_ADDRESS,

BUILD.DEPARTMENT=CONF.DEPARTMENT,

BUILD.SECURITY_OWNER_EMAIL=CONF.SECURITY_OWNER_EMAIL,

BUILD.ONLINE_HOST_MAP=CONF.ONLINE_HOST_MAP,

BUILD.IP_HOST_MAP=CONF.IP_HOST_MAP,

BUILD.HTTP_HEADER=CONF.HTTP_HEADER,

BUILD.LOGIN_TARGET=CONF.LOGIN_TARGET,

BUILD.USER_NAME=CONF.USER_NAME,

BUILD.USER_PASSWORD=CONF.USER_PASSWORD,

BUILD.UC_APPID=CONF.UC_APPID,

BUILD.UC_CHECK_URL=CONF.UC_CHECK_URL,

BUILD.COOKIE=CONF.COOKIE,

BUILD.USER_NAME_2=CONF.USER_NAME_2,

BUILD.USER_PASSWORD_2=CONF.USER_PASSWORD_2,

BUILD.COOKIE_2=CONF.COOKIE_2,

BUILD.LOG_FILE_URL=CONF.LOG_FILE_URL,

BUILD.LOG_START_TIME =CONF.LOG_START_TIME,

BUILD.LOG_END_TIME=CONF.LOG_END_TIME;

索引

查询索引

mysql> show index from ;

mysql> show keys from ;

锁

锁表

show OPEN TABLES where In_use > 0;可以查看被锁的表。然后可以通过show full processlist来查看所有的进程。

show engine innodb status\G这个命令可以查看一些运行状体，对于锁来说，主要看TRANSACTIONS部分和LATEST DETECTED DEADLOCK部分。

information_shcema下的三张表(通过这三张表可以更新监控当前事物并且分析存在的锁问题)

innodb_trx

打印innodb内核中的当前活跃(ACTIVE)事务)

innodb_locks

打印当前状态产生的innodb锁,仅在有锁等待时打印

innodb_lock_waits

打印当前状态产生的innodb锁等待 仅在有锁等待时打印

innodb_trx表结构说明

字段名

说明

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语句

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(此选项不可信)

innodb_lock_waits表结构说明

字段名

说明

requesting_trx_id

申请锁资源的事物ID

requested_lock_id

申请的锁的ID

blocking_trx_id

阻塞其他事物的事物ID

blocking_lock_id

阻塞其他锁的锁ID

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

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

结果如下：

*************************** 1. row ***************************

trx_isolation_level: REPEATABLE READ

waiting_trx_id: 2900247

waiting_trx_thread: 1070

waiting_trx_state: LOCK WAIT

waiting_trx_lock_mode: S

waiting_trx_lock_type: RECORD

waiting_trx_lock_table: `jiang_test`.`test`

waiting_trx_lock_index: PRIMARY

waiting_trx_query: select * from test where id=3 lock in share mode

blocking_trx_id: 2900241

blocking_trx_thread: 1137

blocking_trx_state: RUNNING

blocking_trx_lock_mode: X

blocking_trx_lock_type: RECORD

blocking_trx_lock_table: `jiang_test`.`test`

blocking_trx_lock_index: PRIMARY

blocking_query: NULL

1 row in set (0.01 sec)

MySQL监控

基本环境

GoLang

prometheus

node_exporter

mysqld_exporter

安装

GoLang安装

三个服务都是基于GoLang开发，因此go环境是基础。可以在Go的官网下载到安装包。这里直接使用了1.12.1版本，linux可以直接点击这里下载。 下载完成以后，执行tar -C /usr/local -xzf go1.12.1.linux-amd64.tar.gz解压到/usr/local目录(你也可以安装到自己喜欢的地方)。解压完成以后，通过

export GOROOT=$PATH:/usr/local/go

export PATH=$PATH:$GOROOT/bin

来指定环境变量。最后通过go version来验证，或者根据测试代码来测试：

mkdir workspace

cd workspace

touch hello.go

文件中贴入以下内容：

package main

import "fmt"

func main() {

fmt.Printf("hello, world\n")

}

然后使用go run hello.go进行测试。 当然你也可以使用go build进行构建，然后执行./workspace来测试。

node_exporter安装

node_exporter用于监控服务器的CPU、内存、存储使用情况。你可以在这里查看相关介绍和代码。 依赖

go lang

glibc-static

这里使用0.17.0版本，你可以在这里直接下载。 上传解压以后，执行：

cd node_exporter-0.17.0

make

接着你可以执行./node_exporter -h查看可用的参数。也可以执行make test来测试。