zabbix之mysql数据库

## 一. mysql 数据库连接数设置（重启服务后失效，需要重新设置）：

1.查看mysql最大连接数

 show variables like '%max_connections%’;

2. 查看MySQL应用连接数

 show status like 'max_used_connections'; 

3. 设置mysql 最大连接数

     set GLOBAL max_connections=5000;

### 二. 查看mysql数据库以及表空间大小

1. 查询单个库中所有表磁盘占用大小

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
         concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
         concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
         concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
  from information_schema.tables
where TABLE_SCHEMA = 'zabbix'; # zabbix 具体库名

2. 查询所有数据库占用磁盘空间大小

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_size desc;

3. 查看索引空间

SELECT CONCAT( ROUND( SUM( index_length ) / ( 1024 * 1024 * 1024 ), 6 ), ' GB' ) AS 'Total Index Size' 
FROM information_schema.TABLES 
WHERE table_schema LIKE 'zabbix';

4. 查看某库某表的空间大小

 select concat(round(sum(DATA_LENGTH/1024/1024),2),"MB") as data from information_schema.TABLES where table_schema='zabbix' and table_name="history";