【持续更新】Mysql工作笔记

1、开放增删改查权限,不开放表结构修改权限

有许多生产环境是不需要修改表结构的,也是为了防止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;

2、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 {} \;

3、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;

4、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        |
+------------------------------------------+---------------+

5、禁止root 用户远程登录

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

6、备份数据正确的步骤

1、全局加锁
flush table with read lock;
    
    
2、设置库为只读
set global read_only=1;

image-20231214100316492

3、使用mysqldump 进行逻辑备份

mysqldump  -uroot -p --set-gtid-purged=OFF --single-transaction -B $dbname >  XX.sql

4、mysql 用户名,密码 安全检查

禁止直接赋予% 权限,请针对具体IP进行访问控制

mysql> select User,Host,plugin,authentication_string from mysql.user;

image-20231214100542376

5、解锁

unlock tables

6、关闭只读,允许读写

set global read_only=0;

7、Mysql 官方演示数据

  • https://dev.mysql.com/doc/index-other.html

image-20231214100839777

8、Mysql 统计大小

(1)、查看所有数据库容量大小

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;

image-20231214101007614

(2)、统计每个库表,容量大小

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;

image-20231214101129630

(3)、 查看指定数据库容量大小

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

image-20231214101210454

(4)、 查看指定指定库各表容量大小

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

image-20231214101353855

9、1227- Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

使用navicat 设计某张表的时候, you need (at least one of) the PROCESS privilege(s) for this operation

PS: 之前就只给fbyq 用户单个库的所有权限,没有给所有库的权限,根据错误提示,可以发现一些授权时全局的权限,针对的是*.*,而不能单独对某个库、表授权

grant process on *.* to fbyq@'%';
flush privileges;

需要关闭navicat 软件,从新打开 就可以设计表了

MySQL权限附录

Permissible Privileges for GRANT and REVOKE

Privilege Column Context
ALL [PRIVILEGES\] Synonym for “all privileges” Server administration
ALTER Alter_priv Tables
ALTER ROUTINE Alter_routine_priv Stored routines
CREATE Create_priv Databases, tables, or indexes
CREATE ROUTINE Create_routine_priv Stored routines
CREATE TABLESPACE Create_tablespace_priv Server administration
CREATE TEMPORARY TABLES Create_tmp_table_priv Tables
CREATE USER Create_user_priv Server administration
CREATE VIEW Create_view_priv Views
DELETE Delete_priv Tables
DROP Drop_priv Databases, tables, or views
EVENT Event_priv Databases
EXECUTE Execute_priv Stored routines
FILE File_priv File access on server host
GRANT OPTION Grant_priv Databases, tables, or stored routines
INDEX Index_priv Tables
INSERT Insert_priv Tables or columns
LOCK TABLES Lock_tables_priv Databases
PROCESS Process_priv Server administration
PROXY See proxies_priv table Server administration
REFERENCES References_priv Databases or tables
RELOAD Reload_priv Server administration
REPLICATION CLIENT Repl_client_priv Server administration
REPLICATION SLAVE Repl_slave_priv Server administration
SELECT Select_priv Tables or columns
SHOW DATABASES Show_db_priv Server administration
SHOW VIEW Show_view_priv Views
SHUTDOWN Shutdown_priv Server administration
SUPER Super_priv Server administration
TRIGGER Trigger_priv Tables
UPDATE Update_priv Tables or columns
USAGE Synonym for “no privileges” Server administration

10、优雅的停止数据库

作为一个合格的运维,严禁 systemctl stop mysqld

# 进入原5.7 mysql命令行 正确关闭数据库
mysql -uroot -p
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)


# 确保数据都刷到硬盘上,更改成0
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)



mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[root@cmdb ~]# ps -ef | grep mysql
root     30990 30934  0 16:12 pts/0    00:00:00 grep --color=auto mysql

11、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)

12、Mysql 中表重命名操作

用于一些大表归档

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


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


!!!!!!!谨慎!!!!!!!!!
#truncate 表【一文,MySQL 的 delete、truncate、drop 区别 https://zhuanlan.zhihu.com/p/270331768】
Truncate table TABLE_NAME