用了很多遍却还是记不住的mysql日常

1、查找数据文件路径

show  global  variables  like  "%datadir%" ;

2、查询版本

use mysql;
select version() from dual;

3、允许root用户远程连接

• 不设置远程密码，使用原用户密码

  grant all privileges on *.*  to 'root'@'%' ;
  flush privileges;
  

• 设置远程密码

  create user 'root'@'%' identified by '123456';
  grant all privileges on *.* to 'root'@'%' with grant option;
  flush privileges;
  

4、关闭root用户远程连接

• 删掉mysql库 - user表中的，Host为%，User为root的用户后执行“flush privileges;”即可。

  use mysql;
  delete from user where `Host`='%' and `User`='root';
  flush privileges;
  

5、查询数据库的数据量

• 查询库的全部信息

  select * from information_schema.TABLES where TABLE_SCHEMA = 'musheng_wms';
  

  

• 查询库的表行数

  select TABLE_NAME,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA = 'musheng_wms' order by TABLE_ROWS DESC;
  

  

• 查询库的总行数

  select SUM(TABLE_ROWS) from information_schema.TABLES where TABLE_SCHEMA = 'musheng_wms' order by TABLE_ROWS DESC;
  

  

• 查询库各表占用的硬盘容量

  select TABLE_SCHEMA as '数据库',
  TABLE_NAME as '表名',
  TABLE_ROWS as '记录数',
  truncate(DATA_LENGTH/1024/1024, 2) as '数据容量(MB)',
  truncate(INDEX_LENGTH/1024/1024, 2) as '索引容量(MB)'
  from information_schema.TABLES 
  where TABLE_SCHEMA ='musheng_wms'
  order by TABLE_ROWS desc, INDEX_LENGTH desc;
  

  

• 查询库占用硬盘的总大小

  select TABLE_SCHEMA as '数据库',
  SUM(truncate(DATA_LENGTH/1024/1024, 2)) as '数据总容量(MB)',
  SUM(truncate(INDEX_LENGTH/1024/1024, 2)) as '索引总容量(MB)'
  from information_schema.TABLES 
  where TABLE_SCHEMA ='musheng_wms'
  order by TABLE_ROWS desc, INDEX_LENGTH desc;
  

  

6、导入导出*.sql

• 导入

  mysql -h [ip_address] -u [username] -p [database_name] < [*.sql_path];
  # 如
  mysql -h localhost -u root -p test < D:/test.sql;
  

• 导出

  mysqldump -h [ip_address] -u [username] -p [database_name] > [*.sql_path];
  # 如
  mysqldump -h localhost -u root -p test > D:/test.sql
  

7、日志配置查询

show variables like 'log_%';

8、查询内部进程列表

show processlist;

9、查询正在使用中的表

show open tables where In_use > 0;

10、查看当前的锁

select * from information_schema.innodb_locks;

11、查看定时任务

# 查整个mysql的定时任务
select * from information_schema.events;

12、查看触发器

# 查当前库的触发器
show triggers;
# 查整个mysql的触发器
select * from information_schema.triggers;