一些有用的 MySQL 命令
查整个库的状态:
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 = 'databasename';
查单表:
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_NAME = 'tablename';
查看某一张表的创建语句
show create table monitor_data_value_4_26
登录到某一个库
mysql -h 111.111.xx.96 -u xx_monitor -pxx_monitor monitor_data_0
看全局参数设定(如下是带log的变量)
show global variables like '%log%'
产看master的binlog状态,slave的话类似
show master status
查看所有可用权限
show privileges
查看某个用户的权限
show grants for 'test'@'%'
创建用户和密码
CREATE USER 'test'@'%' IDENTIFIED BY 'test'
修改用户密码
--MySQL 8.0
set password for 'user'@'%' = '123456'
授权(test.* 表示test这个库所有表,‘test’@’%'表示允许让test这个用户从任意host连接)
GRANT Insert,Update,Select,Delete ON test.* TO 'test'@'%'
把所有权限都授权出去
GRANT ALL ON *.* TO 'test'@'%'
如果创建的用户需要有GRANT权限,那么在授权语句的最后加上 with grant option
设置binlog格式
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'MIXED';
修改表并建立前导索引
alter table monitor_data_key modify key4 varchar(150),add index idx_mdk_key4(key4(50));
修改表名
alter table '老表名' rename '新表名'
查看当前表某个字段最长值所在的行
select * from monitor_data_key order by length(key4) desc limit 1;
查看和设置隔离级别
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';
查看用户中所有用户(super权限)
select * from mysql.user;
查看一个库里面表的信息
show table status;
查看character_set和collation
show VARIABLES LIKE 'character_set_%' ;
show VARIABLES LIKE 'collation_%';
同一个库多表数据sum
select sum(t.x) from (
select count(*) as x from abc_0000.def
union all
select count(*) as x from abc_0001.def
union all
select count(*) as x from abc_0002.def
union all
select count(*) as x from abc_0003.def
union all
select count(*) as x from abc_0004.def
union all
select count(*) as x from abc_0005.def
union all
select count(*) as x from abc_0006.def
union all
select count(*) as x from abc_0007.def
union all
select count(*) as x from abc_0008.def
union all
select count(*) as x from abc_0009.def
union all
select count(*) as x from abc_0010.def
union all
select count(*) as x from abc_0011.def
union all
select count(*) as x from abc_0012.def
union all
select count(*) as x from abc_0013.def
union all
select count(*) as x from abc_0014.def
union all
select count(*) as x from abc_0015.def
) t