【无标题】

mysql常用命令

# 创建用户，授权

CREATE USER 'jf_read'@'%' IDENTIFIED BY 'Ab@123456#*';
GRANT SELECT ON pdcc.* TO 'jf_read'@'%';

# 重置root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

# 刷线权限
FLUSH PRIVILEGES;

# 查看root用户是否允许远程连接
SELECT User, Host FROM mysql.user WHERE User = 'root';
# 允许任何主机连接
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;



# 数据库总体大小
SELECT 
    table_schema AS 'Database',
    SUM(data_length + index_length) AS 'Size (Bytes)',
    SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)'
FROM 
    information_schema.tables
GROUP BY 
    table_schema;


# 每张表的行数，以及数据与索引各自占用的空间
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;


# 当前活动连接数
SHOW STATUS LIKE 'Threads_connected';

# 最大允许的连接数
SHOW VARIABLES LIKE 'max_connections';

mysql5.7重置root密码，参考文档

https://blog.youkuaiyun.com/BHSZZY/article/details/119037853?fromshare=blogdetail&sharetype=blogdetail&sharerId=119037853&sharerefer=PC&sharesource=m0_71275219&sharefrom=from_link