切换到信息库:

use information_schema;

查询所有数据库的总大小

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

统计一下所有库数据量

SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES;

统计每个库大小

SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES group by table_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='h0118';

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

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='h0118';

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

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

一、下载地址:http://dev.mysql.com/downloads/mysql/
1.进入官网下载,显示的应该是最新版本,选择第二个(mysql5.7.20-winx64.zip)
2.下载完成后,直接解压到自定义目录,解压目录就是安装目录

二、配置环境变量
1.新增环境变量,例:
变量名:MYSQL_HOME
变量值:D:\mysql\mysql5.7.20-winx64

2.修改环境变量PATH
在PATH后面加入%MYSQL_HOME%\bin,注:加入新的变量值需要用;隔开

三、添加my.ini配置文件
1.下载的压缩文件中没有my.ini配置文件和data文件夹,需要手动在根目录(D:\mysql\mysql5.7.20-winx64\)下新建文本my.ini,如果 data 不能自动生成就放 bin 目录下,my.ini,内容如下:

[mysqld]
bind-address = 0.0.0.0
port = 8020
explicit_defaults_for_timestamp = true
character-set-client-handshake = FALSE
character-set-server = UTF8MB4

tmpdir="d:/tools/mysql-8.0.22-winx64/tmp"
basedir="d:/tools/mysql-8.0.22-winx64"
tmpdir="d:/tools/mysql-8.0.22-winx64/data"
max_connections=200
default-authentication-plugin=mysql_native_password
#skip_grant_tables

[mysql]
default-character-set = UTF8MB4

[mysql.server]
default-character-set = UTF8MB4

[client]
default-character-set = UTF8MB4

[WinMySQLadmin]  
Server = "d:/tools/mysql-8.0.22-winx64/bin/mysqld.exe"

四、以管理员身份运行命令行cmd,进入bin目录(一定要进入bin目录)

mysqld --initialize --user=mysql --console (生成临时密码,要记住)
mysqld --install
net start mysql
mysql -P8020 -uroot -p    (输入刚才的临时密码)
SET PASSWORD FOR 'root'@'localhost' = 'password';
use mysql;
update user set host='%' where user='root';
flush privileges;
grant system_user on *.* to 'root';  //root还缺一些权限,不能操作其它用户,需要给予所有权限

五、成功
其他:移除服务命令,在bin目录下(mysqld --remove)

备份 MySql 数据库:

mysqldump -uroot -pPassword mydb | gzip > /home/backup/mydb_$(date +%Y%m%d_%H%M%S).sql.gz
mysqldump -P8010 -uroot -pPassword mydb > D:\data\bak\mydb.sql

备份SP:

mysqldump -uroot -pPassword -n -t -d -R mydb > /home/backup/mydb_sp.sql
mysqldump -P8010 -uroot -pPassword -n -t -d -R mydb > D:\data\bak\mydb_sp.sql

删除10天前的备份:

find /home/backup -mtime +10 -name "*.*" -exec rm -rf {} \;

windows版本下:

D:\tools\mysql-8.0.17-winx64\bin>mysqldump -uroot -pPassword crms > D:\crms_db.sql
D:\tools\mysql-8.0.17-winx64\bin>mysqldump -uroot -pPassword -n -t -d -R crms > D:\crms_sp.sql

创建数据库:

create database allchat;

还原数据库:

source D:\sql.sql

增加用户、修改密码登录、远程访问及权限

CREATE USER 'operator'@'%' IDENTIFIED BY 'Password';
ALTER USER 'operator'@'%' IDENTIFIED WITH mysql_native_password BY 'Password';
grant all privileges on ball.* to 'operator'@'%';
#UPDATE mysql.user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Execute_priv='Y',Alter_priv='Y',create_routine_priv='Y',alter_routine_priv='Y',Grant_priv='Y',Super_priv='Y' where user='operator';
flush privileges;

删除用户:

drop user 'allchat_db'@'localhost';

修改密码:

ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY'Password';

Ubuntu 服务重启:

/etc/init.d/mysql start|stop|restart|reload|force-reload|status

日志读取:

mysqlbinlog --no-defaults  /var/lib/mysql/ib_logfile0 > /home/test.txt

移除黑名单(IP被阻止):

flush hosts;

mysql 查看版本号:

mysql --version
mysql> status; 

远程连接:

mysql -h192.168.10.28 -P9906 -uroot -p

Ubuntu Mysql 配置文件位置:

cd /etc/mysql/mysql.conf.d
vi mysqld.cnf

迁移安装位置:
修改系统虚拟变量 - 卸载服务[mysqld --remove] - 重启 - 目录复制到新位置 - 修改my.ini - 安装服务[mysqld --install] - 启动

忘记重置密码:

mysqld –shared-memory –skip-grant-tables
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password';

这个存储过程,可以在当前数据库中按关键字查找所有存储过程,非常实用,DBA必备工具:

CREATE PROCEDURE [dbo].[SP] 
     @keywords nvarchar(100)= 'p_Comm_Area_GetList'
AS 
BEGIN 
    SET NOCOUNT ON; 
    SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%' + @keywords+ '%' 
    AND ROUTINE_TYPE='PROCEDURE' 
END

用法:

exec sp 'keywords'

查看磁盘占用情况:

df -h

查找大文件(. 为路径,根据需要修改,全盘搜索就直接 / ):

find . -type f -size +800M

查找哪个目录占用大空间:
先查找根目录:

du -ah --max-depth=1 /

看哪个目录占用大,然后进去再查看下一层,比如 var 目录大,则:

cd /var
#不指定目录默认为当前目录
du -ah --max-depth=1

一层一层下去就可以找到目标目录。

首先,在 vmware 中扩展硬盘空间,这个很简单,不用多说。做好后,到系统中:

fdisk /dev/sda

按 n 创建新分区,全部按默认的即可,新系统一般有 /dev/sda1 - sda3,默认创建为 /dev/sda4
然后创建物理卷:

pvcreate  /dev/sda4

可以用 pvscan 查看情况,sda3 在卷组 “ubuntu-vg” 里,而 sda4 不在内。然后用vgextend,把新物理卷加到卷组:

vgextend ubuntu-vg /dev/sda4

查看卷组的命令是:vgdisplay
接下来是要扩容逻辑卷,使用 lvextend 命令,使用前,请先用 df -h 得到物理磁盘路径,默认为:/dev/mapper/ubuntu--vg-ubuntu--lv

lvextend -l +100%FREE /dev/mapper/ubuntu--vg-ubuntu--lv

最后,运行 resize2fs,让逻辑卷的扩容生效:

resize2fs /dev/mapper/ubuntu--vg-ubuntu--lv

搞定!

另外,在 vmware 中扩容后,开始使用 fdisk 命令,会有红色错误提示:

GPT PMBR size mismatch (41943039 != 125829119) will be corrected by write.

这个很简单,直接运行 fdisk /dev/sda 后,按 w 保存修改就会自动修复了。