简介
- 本篇主要对MySQL面试时可能会问到的技术点做一个总结,经验有限,主要是面向校招
- 推荐大佬的文章,可以详细学习一下:篇一,篇二
- 导学:
- 这里不涉及基本语法、基础操作(SQLBoy)可以看我的MySQL基础篇
- 后面我们会着重回顾一下分库分表
版本问题
- Q:你在学习工作中使用哪个版本的数据库?为什么选择这个版本?
- Q:是否升级过?为何升级?如何升级?
- Q:最新的MySQL版本是什么?你关注哪个特性?
- 注:这个版本指的是不同企业维护的数据库系统,不是MySQL从5.7到8.0
常见发行版
- Oracle收购MySQL之后,作为官方一直在维护
- 社区版:国内最常用的数据库
- 企业版:收费
- Percona MySQL
- 二次开发,性能较官方版本更好一些,但更新慢一步
- MariaDB
- 不是完全兼容MySQL的
- 从服务器特性看:
- 当然,也有一些免费的监控工具,例如mytop等
- 从高可用性看:
- MGR是基于Paxos算法实现的MySQL Server插件,可以实现组复制,保证数据一致性;是一种高可用解决方案
- MySQL Router是一个轻量级的中间件
- 安全性方面:
- 官方MySQL8.0升级了对密码的管理,使用了生命周期机制和sha2插件提高安全性
- 从开发管理来看:
- 现在,我们可以结合上面的特性,看看自己使用的MySQL都有哪些优势,回答第一个Q
升级
- 升级前的考虑
- 给业务带来的益处和影响
- 制定升级方案及失败的回滚方案
- 业务和运维上的痛点
- 一般和新特性相关,这个…校招生暂时无以奉告
- 对业务程序的支持和性能的影响
- 升级方案
- 评估受影响的系统有哪些(按组升级)
- 和业务人员沟通,是否需要修改程序,确定升级时机
- 使用真实数据多次测试演练
- 升级后的数据库环境检查
- 回滚方案
- 一般是主从复制的滚动升级方案,主库回滚
- 回滚后也要检查
- 基本步骤:
8.0
- 即使没有使用也要关注新版本的特性,万一能很好解决你当前的问题呢?
- MySQL从5.7直接到8.0
- 服务器新特性主要如下:
- 资源组:可以修改线程的优先级以及所能使用的硬件资源,可以指定不同的线程使用特定的资源
- 资源组的定义:
- 资源组的使用:即资源组占领1号CPU,给84号线程使用了
- 资源组:可以修改线程的优先级以及所能使用的硬件资源,可以指定不同的线程使用特定的资源
- 安全新特性如下:
- 密码记录:可以定时让业务人员修改密码,防止密码使用太久增加破解风险
- InnoDB引擎新特性:
用户管理
- Q:如何在给定场景下为某用户授权?
- Q:如何保证数据库账号的安全
- Q:如何从一个实例迁移数据库账号到另一个实例(数据库)
授权
- 规范定义MySQL账号
-- 可以使用 \h create user 查看帮助 create user roy@localhost:test_authority
- 常用用户权限
- DBA权限及库表结构相关:
- 数据操作相关:一般情况下用户只有DML权限
- 可以使用命令
show privileges;
查看权限列表及其说明
- DBA权限及库表结构相关:
- 给定场景下授权原则
- 遵循最小权限原则
- 使用
grant
命令授权
-- 给用户授予操作某个数据库表的权限 grant select,insert,update,delete on db.tb to user@ip;
- 使用
revoke
收回权限
-- 收回delete权限 revoke delete on db.tb from user@ip
账号安全
- 保证数据库账号安全
- 规范的用户管理流程(别瞎授权)
- 密码策略
- 创建用户的时候即可定义密码过期
-- 指定密码可使用一天 create user roy @'localhost' identified by '123456' password history 1; -- 设置过期 alter user roy@'localhost' password expire;
- 可在内置mysql库的user表中看到:
Password_reuse_history: 1
- 此时登录看不到任何数据库,因为还未授权
- 设置roy过期后重新登录(密码过期后可以使用过期密码登录,但是提示必须修改),此时便不能再执行任何操作命令
-- 由于密码策略,必须使用与上一次不同的密码 alter user user() identified by '654321';
账号迁移
- 迁移数据库账号,即数据库用户(开发和维护人员)的信息和权限
- 要判断两个实例的数据库版本是否一致
- 版本一致,即兼容,直接都copy过去
- 不一致,则在那边的数据库按这边的权限授权即可
- 导出创建用户及授权语句的方法
pt-show-grants u=dba_test, p=123456, h=localhost;
- 导出语句如下:
- 在新实例运行即可
服务器配置
- Q:分析一个group by语句的异常原因?
- Q:如何比较系统运行的配置和配置文件中的设置是否一致?
- Q:MySQL中的关键性能参数?
执行模式
- 给出如下数据表:
- 统计:每种产品在每个仓库中的总数量
- 当然,这个DML语句有误,但在MySQL中并不会报错;我们暂且叫异常
- 这关系到
SQL_MODE
,即SQL语句执行的模式,可配置
set global sql_mode = 'xxx'; -- 作用域还可以是session、persist
- 为了解决上面的问题,可以做如下设置:
- 还有一些常见设置
-- 例如 create table t(id real); -- float
- MySQL从5.6开始将SQL_MODE设置成
TRADITIONAL
,要注意
- MySQL从5.6开始将SQL_MODE设置成
- 统计:每种产品在每个仓库中的总数量
配置一致性
- 由于SQL_MODE可以动态设置,因此会出现系统运行配置和配置文件不一致的情况
- 使用
pt-config-diff
工具对比 - 先说明一下变量设置时的
persist
参数session
和global
就不多说了,注意他们都会因重新连接而丢失设置- 但
persist
不会,直接将设置持久化在文件
- 这是在8.0提出来的,如果不是此版本,需要global之后手动修改
my.cnf
文件
- 对比配置的命令:
pt-config-diff u=root,p=123456,h=localhost /etc/my.cnf
,指定对比的配置文件即可
关键性能参数
- 服务器配置参数
- 针对线程的设置意味着有多少线程,配置的资源就会扩大多少倍,所以别太大!
max_connections
的控制,会占用内存- 对于master服务器,
sync_binlog
最好设置为1 sort_buffer_size
只会在查询涉及到排序时分配,而且会立即分配所有空间
- 存储引擎参数
- 服务器配置这部分主要了解执行模式SQL_MODE和关键参数即可
- 任何问题都可从效率、空间开始入手分析