一些有用的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\G

登录到某一个库

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