mysql 运维用到的命令
show variables like 'socket%';
通过socket 进行连接
select user,host,authentication_string from user;
\s
开多个实例配置:
慢查询存入到数据表:
开启存储引擎:
禁用:
导入数据:
int
字符串:
获取随机数:
select floor(1+rand()*99);
重复某个字符多少次
select repeat('a',floor(1+rand()*127));
只修改后加入的,现有的不会修改:
修改表的字符集:
查找不是InnoDB的表:
select table_schema,table_name,`engine`, sys.format_bytes(data_length) as data_size from TABLES where `engine` <> 'InnoDB' and table_schema not in ('mysql','performance_schema','information_schema');f分区表
分区表:
计算表中每行数据的平均大小:
统计表中的列编码格式utf8
select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) as Name, character_set_name, GROUP_CONCAT(COLUMN_NAME SEPARATOR ' : ') as COLUMN_LIST from information_schema.COLUMNS where data_type in ('varchar','longtext','text','mediumtext','char') and character_set_name <> 'utf8mb4' and table_schema not in('mysql','`performance_schema`','information_schema','sys') group by NAME,character_set_name;
比较不错的 sql:
自动添加行号: