mysql如何限制管理员用户远程登陆

转载

云端筑梦师 2024-10-23 22:39:03

文章标签 mysql如何限制管理员用户远程登陆数据库缓存存储引擎表空间 文章分类 MySQL 数据库

1、MySQL并发控制机制
并发控制:每个会话会启动一个mysql线程
服务器层：用于控制锁
存储引擎层：并发访问控制基本上应该由存储引擎层完成

锁：lock
读锁：共享锁
写锁：独占锁

锁力度：
表级锁：myisam表级锁
行级锁：innodb行级锁

锁分类：
隐式锁：由存储引擎自动完成
显示锁：用户可手动施加锁，表级锁

手动加锁：在服务器级别实现
lock tables tb1 {read|write}；添加锁
UNLOCK TABLES：释放所有锁，不能单独释放。
FLUSH TABLES WITH READ LOCK;全局添加读锁。

事务：
一个执行单元：多个sql语句，要么都执行，要不都不执行。事务有2种操作，提交和回滚
事务隔离级别：
select @@global.tx_isolation; ：查看数据库隔离级别，会产生脏读
set session tx_isolation='read-uncommitted';：设置事务级别
read-uncommitted（读未提交，就可以访问）：最低隔离级别
read-commtted（提交后才可以读取）：不可重复读
repeatable-read（可重读）：幻读
serializabile（可串行化）：加锁读，必须在提交后才可以读取数据
start transaction; ：启动事务
commit：提交事务
rollback：回滚事务
select @@global.autocommit;查看是否自动提交，多次提交会降低I/O，建议关闭

事务日志;
可将随机I/O变为连续I/O，先把数据存储在连续I/O事务日志中，随后在更新到随机I/O中

注意：
   myisam存储引擎不支持事务。

2、MySQL存储引擎介绍
  show table status like 'user'\G或 show table status where name='user'\G：查看表的存储引擎属性。
存储引擎：称为表类型，他提供在表级别上，同一个数据库建议用同一个存储引擎。

  show table status like 'user'\G：表引擎字段说明
  Row_format
myisam：Dynamic：可变长度、fixed：固定长度、compressed：压缩表使用该格式该表数据都为压缩后存放。
innodb：compact：压缩表、redundent：有冗余表、
Rows：表中现有的行数
  Avg_row_length：平均每行包含的字节数
  Data_length：数据大小
Max_data_length：表的最大容量，0表示没有限制
Index_length：索引长度
Data_free：空闲的数据空间，
Auto_increment：带有auto_increment字段下个使用数值。
Create_time：表的创建时间
Update_time：表最后的更新时间
Check_time：检查表时间
Collation：默认排序规则。
Checksum：校验信息
Create_options：创建表时额外添加的选项
Comment：注释信息

mysql存储引擎特性：
innodb：设计用于处理大量短期事务，支持自动的崩溃恢复，优先考虑使用该引擎。
innodb支持Table Space: 表空间可由一个或多个物理文件组成，并可以实现自动增长。
table space支持两种风格和存储格式：
把所有的表都放在同一个表空间中。默认使用该方式。
表结构定义：tb_name.frm （数据库目录下）
数据和索引：统一的表空间文件中
每个表都使用专用的表空间。建议使用每表单独表空间机制。
默认在数据库目录下：tb_name.ibd;如果要启用需要在配置文件：
/etc/my.cnf中添加innodb_file_per_table = on
innodb基于聚簇索引建立：基于主键索引查询时性能较好，辅助索引必须包含主键索引
innodb：支持事务、行级锁、mvcc、支持热备。

innodb：存储引擎管理参数
innodb_data_file_path：表空间文件的文件名称及特性，可以设定多个文件
innodb_data_file_path = ibdata1:20G;ibdata2:10G;ibdata3:1G:autoextend；
innodb_data_home_dir：表空间的数据文件的存储位置
建议设置innodb_file_per_table = ON

InnoDB缓冲池: buffer pool：由InnoDB维护的内存空间：缓存索引及数据
innodb_buffer_pool_size：设置缓冲池大小
SHOW ENGINE INNODB STATUS;查看缓冲池中数据交互情况

myisam：不支持事务、行级锁和热备，支持全文索引，支持表压缩存放、空间函数。崩溃后无法安全恢复。
myisam存储格式：每张表有3个文件位于数据库目录下
tb_name.frm: 表格式、
tb_name.MYD: 数据
tb_name.MYI: 索引

其他存储引擎：
menory：基于内存实现，数据存储在内存中，无法持久存储数据。

aria：myisam增强引擎兼容myisam，以前使用myisam引擎现在可以使用aria

常用命令：
   show table status like 'user'\G或 show table status where name='user'\G：查看表的存储引擎属性。
show engines;显示数据库所有引擎
show global variables like '%storage%';：查看数据库使用默认引擎，如果需要修改默认引擎修改配置文件或变量值即可。
set default_storage_engine='innodb';：修改引擎变量值

3、MySQL用户和权限管理
用户@主机：表示此用户帐号可从@HOST范围内的某主机对此MySQL建立连接；
@HOST: 表示客户端
  %：表示任意字符，例如：172.16.0.0/16, 172.16.%.%
_：表示单个任意字符

mysql的权限类别：
库级别和表级别通用（alter、alter routine、create、create routine、create view、drop、index）
表级别（select、insert into、update、delete）
字段级别：明确指明对某字查询
管理类（create temporary tables、create user、file、super、show databases、reload、shutdown、lock tables）
程序类
all：表示赋予所有权限。
grant：授权。
revoke：删除权限。

常用命令：
  create user 'xj'@'192.168.%.%' identified by '123456';添加用户
drop user ‘xj’@‘192.168.%.%’：删除用户
  show grants for 'xj'@'192.168.%.%';：查看该用户拥有的权限
  grant select on phpdb.* to 'xj'@'192.168.%.%';：对来自192.168网段的xj用户授予查询phpdb下所有表的权限。
revoke all on phpdb.* from 'xj'@'192.168.%.%';删除授予权限
  grant select on phpdb.* to 'xj'@'192.168.%.%' with grant option;：允许把自己权限授予他人。
rename user 'xj' to 'xxx';把用户xj修改为xxx。

4、MySQL查询缓存管理及数据类型选择
缓存：hit（命中），miss（未命中）:衡量缓存的有效性：命中率, hit/(hit+miss)
query_cache_type
on、off、demand（手动指明该语句是否缓存sql_cache | sql_no_cache）

query cache:
key:查询语句的hash码
value：查询语句的执行结果

查询语句中不会缓存的场景：
查询语句中有不确定数据时不会缓存。
查询中包含用户自定义的函数、存储函数、用户变量、临时表、包含权限的语句不会缓存。

需要消耗大量资源的查询，缓存比较有效

缓存优化的思路：
批量写入比单次写入对缓存的影响要小得多；
缓存空间不宜过大，大量缓存的同时失效会导致MySQL假死；
必要时，使用SQL_CACHE或SQL_NO_CACHE手动控制缓存；
对写密集型的应用场景，禁用缓存反而能提高性能；

碎片整理：flush query cache;
清空缓存：reset query cache;

常用命令：
show global variables like 'query_cache%';：显示缓存相关变量
  query_cache_limit：单条语句查询结果大于该值不与缓存，可修改变量值。
  query_cache_min_res_unit：查询缓存时内存中分配最小单位。
  query_cache_size：缓存最大空间，该数组必须为1024倍数，0为没有开启缓存。
query_cache_type：是否开启手动缓存功能
  query_cache_wlock_invalidate：
show global status like 'Qcache%';：为mysql运行过程中的统计数据不能修改。
  Qcache_free_blocks ：查询缓存中空闲块
  Qcache_free_memory ：所有未分配的空间
  Qcache_hits：查询命中次数
  Qcache_inserts：向缓存中插入的次数
  Qcache_lowmem_prunes ：缓存满后清除缓存中数据次数
  Qcache_not_cached：没能被缓存的次数
  Qcache_queries_in_cache：还留在缓存中的数据，0表示都流失了。
  Qcache_total_blocks：共分配缓存中的块
   show global status like 'Com_select';：现实共执行select的次数