SQL命令（语句）录----残篇

//bin-log日志放在哪里？（data目录可以自定义的）

windos服务器，一般直接放在mysql/data目录下；

linux服务器，有的是放在mysql/data目录下的，有的却是在/var/lib/mysql；

//是否启用了二进制日志

	show variables like "log_bin";

//查看当前的日志

	show master status;

//查看所有日志

	show binary logs;

开启bin-log二进制日志：

my.ini 配置文件 查到或添加：
	log-bin=mysql-bin

 

my.cnf配置文件添加：
	server-id = 1
	log-bin=mysql-bin


注意：配置文件修改保存后，一定要重启mysql

//刷新一份最新的日志

flush logs;

//清空所有日志

reset master;

查看bin-log二进制日志文件具体内容：(mysqlbinlog)

二进制日志用记事本、编辑器、vi或vim等等打开都是乱码的，必须用mysqlbinlog才能正确打开。 

	windos下直接“开始-运行-cmd”，执行：
	F:\MySql\mysql-5.6.49-winx64\bin\mysqlbinlog E:\MySqlData\data\mysql-bin.000004;

	 

	linux下直接执行：
	cd /var/lib/mysql/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001;

二进制日志转SQL：(mysqlbinlog --base64-output=decode-rows)

F:\MySql\mysql-5.6.49-winx64\bin\mysqlbinlog --base64-output=decode-rows "E:\MySqlData\data\mysql-bin.000001" >C:\Users\Administrator\Desktop\aac.sql

--------------------------------end++++++++++++++++++

首先切换到"information_schema"数据库（也可以直接找到）

use information_schema;

然后↓
#查询所有数据库的总大小（只包含"“数据”"所占空间不包含其他（如：索引））

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

#查询指定数据库大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='test';

#查看指定数据库的某个表的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';

#查询整个库的表数【估算】----有一定误差，目前来看大约±1

select count(table_name),table_schema from information_schema.tables group by table_schema;

查看连接上服务器上数据库的用户

#1）从数据库中看

root@localhost:(none) 11:00:19>show processlist;

#2）从系统中查看

[root@pmos ~]#  mysqladmin -uroot -proot -hlocalhost processlist

查询TOP20大表

select table_schema,table_name,(data_length + index_length) / 1024 / 1024 /1024 as total_GB from information_schema.tables order by total_GB desc limit 20;

查询所有数据库的总大小，方法如下：

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
+-----------+
| data      |
+-----------+
| 3052.76MB |
+-----------+
1 row in set (0.02 sec)

查看所有数据库各容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) 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
order by data_length desc, index_length desc;

查看指定数据库容量大小

select
table_schema as '数据库',
sum(table_rows) 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='mysql';　

查看指定数据库各表容量大小

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='mysql'
order by data_length desc, index_length desc;