文章目录
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;