查看数据库大小操作

#查看所有数据库大小
SELECT table_schema AS 'Database',
    SUM(data_length + index_length) / 1024 / 1024 AS 'Total_size_MB'
FROM information_schema.tables
GROUP BY table_schema;
#查看所有数据库表空间大小
SELECT table_schema AS 'Database',
       table_name AS 'Table',
       round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.tables
ORDER BY data_length + index_length DESC;
#查看某个库所有表空间大小
SELECT table_name AS 'Table',
       round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.tables
WHERE table_schema = '填数据库名'
ORDER BY data_length + index_length DESC;
#查看数据库表空间碎片大小
SELECT
    table_schema AS `Database`,
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`,
    round((data_free / 1024 / 1024), 2) AS `Free Space (MB)`
FROM
    information_schema.TABLES
WHERE
    table_schema='填数据库名';
#这条语句将重组表并释放未使用的空间，有助于提高表的性能并减少碎片
OPTIMIZE TABLE your_table_name;

开放增删改查权限，不开放表结构修改权限

有许多生产环境是不需要修改表结构的，也是为了防止SQL注入。

创建用户
mysql> grant all on *.* to 'ie'@'%' identified by 'test1'

设置权限
1.首先我们先回收所有权限。
revoke  all   on  *.*  from   ie'@'%' ;

2.设置权限
grant select, insert, update, delete on  *.*  from   ie'@'%' ;

3.刷新
flush privileges;

4.修改密码
-- 方法1：推荐官方写法
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
-- 方法2：使用SET语句
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPassword123!');

MySQL 数据库日志的切割

#!/bin/bash
time=`date -d"yesterday" +%Y-%m-%d`
basedir=/data/mysql/data/logs
lognameA=mysql_general.log
lognameB=slow_query.log
cp $basedir/$lognameA $basedir/${time}-$lognameA
cp $basedir/$lognameB $basedir/${time}-$lognameB
sleep 1
echo "" > $lognameA
echo "" > $lognameB
find $basedir -mtime +7 -name "*-$lognameA" -exec rm -r {} \;
find $basedir -mtime +7 -name "*-$lognameB" -exec rm -r {} \;

MySQL锁表查询

#查看锁表
show open tables  where In_use > 0;

​
#查询表级锁争用情况 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺： mysql> show status like 'table%'; 如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

#获取InnoDB行锁争用情况    
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

解锁

第一种
show processlist;
找到锁进程，kill id ;

​
第二种 mysql>UNLOCK TABLES;

MySQL安全插件：Connection-Control Plugins 的利与弊

查看mysql 正在使用的插件

• https://blog.51cto.com/u_13941177/2178880

• https://blog.csdn.net/leonpenn/article/details/104790469/

  mysql> select PLUGIN_NAME, PLUGIN_STATUS from INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like 'connection%';
  +------------------------------------------+---------------+
  | PLUGIN_NAME                              | PLUGIN_STATUS |
  +------------------------------------------+---------------+
  | CONNECTION_CONTROL                       | DELETED       |
  | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
  +------------------------------------------+---------------+
  

禁止root 用户远程登录

use mysql;
delete from user where user="root" and host="%";

Mysql8.0 中创建用户并授权

mysql5.7 中可以创建用户并授权，而在mysql8.0中需要先创建用户再授权

mysql> create user 'srebro'@'%' identified by 'srebro';
Query OK, 0 rows affected (0.02 sec)

​
mysql> grant all on . to 'srebro'@'%'; Query OK, 0 rows affected (0.00 sec)

Mysql 中表重命名操作

#先查看表是否有事务在运行，只有没有在使用才能重命名
show OPEN TABLES where In_use > 0;


#重命名一张表
CREATE TABLE new_table SELECT * FROM old_table;