Day01-MySQL之基础入门
1. MySQL数据库简介
1.1 什么是数据库 ?什么是OLTP?
数据库管理系统(Database Management System)
本质:管理数据的大型软件
功能:
- 解决不同应用的数据存储问题
- 易于使用和维护
- 保证数据安全与完整
- 多个Server端连接支持
- 实现自定义schema与约束
- 提供数据定义DDL,数据操作等语言DML,实现增删改查
什么是OLTP?
1.定义
OLTP(on-line transaction processing)翻译为联机事务处理,保证业务快速响应与支持,数据库与业务直接高性能低延时并发交互操作(一秒钟几千个用户分别存钱,取钱)。这个直接存储与业务直接相关的数据库,叫做OLTP型数据库,服务在线业务。
2.数据本质
为业务数据服务:产品使用在前端交互产生两种数据:
- 行为数据:行为数据指点击,滑动等操作,也称为埋点数据.
- 业务数据:业务数据是在行为数据之后产生的与产品结果挂钩的数据,比如最终下单,存1W块到账号等数据.
3.功能核心
-
实时性强,稳定性强:数据库与业务直接高性能低延时并发交互操作
-
保证交易的完整性
-
主要是对数据的增删改
什么是OLAP:
1.定义
OLAP是做分析处理,当数据积累到一定的程度,需要对历史做一个总结分析时,就需要把过去一段时间内产生的数据拿出来进行统计分析,从中获取我们想要的信息,为公司做决策提供支持。
从对数据库操作来看,OLAP是对数据的查询.
2.本质
为业务数据服务:产品使用在前端交互产生两种数据:
- 行为数据:行为数据指点击,滑动等操作,也称为埋点数据.
- 业务数据:业务数据是在行为数据之后产生的与产品结果挂钩的数据,比如最终下单,存1W块到账号等数据.
3.功能核心
- 大数据量级的分析,低并发,高延时
- 需要将不同业务数据清洗后再数据仓库统一分析
- 主要是对数据的查询
- OLAP依赖于OLTP,让OLTP产生的数据发现价值的过程
OLTP与OLAP区别?
OLTP System Online Transaction Processing | OLAP System Online Analytical Processing | |
---|---|---|
业务目的 | 处理业务,如订单、合同等 | 业务支持决策 |
面向对象 | 业务处理人员 | 分析决策人员 |
主要工作负载 | 增、删、改 | 查询 |
主要衡量指标 | 事务吞吐量 | 查询响应速度(QPS) |
数据库设计 | 3NF或BCNF | 星型/雪花模型 |
1.2.为什么学习MySQL?MySQL的产品迭代
-
为什么学习MySQL
- 国内市场占有率最高80%,去IOE趋势明显
- OLTP场景是公司发展的根基,所有业务为此场景服务
- 一二线的大厂DBA1+1配置模式
-
MySQL的产品迭代
1.3 一二线大厂主流版本选择
企业生产环境一般要过20个小版本后才会进行评测阶段
1.MySQL:版本选择
-
5.7版本
- 5.7.20之后的版本
-
8.0版本
- 8.0.20开始评测
2.安装方式:
一线大厂:源码功能修改与编译(百度)
二线(58,陌陌):tar.gz方式,方便做打包的生产标准MySQL包管理
2. MySQL 服务管理-systemd 和Surpviser
mysql服务器demon进程管理工具。
3. MySQL的权限管理
1)mysql部署依赖的系统用户权限
root:system mysql命令
mysql:mysql系统用户:/data/ /log
数据安全
2)mysql访问用户,create user。
数据库权限非常重要:一般业务需求:select(主,从),delete、update、insert(主库)足以。
千万,千万不要给业务root用户,DBA可不可以用root???不要。
root用户管理:权限只限于127.0.0.1/localhost。登录mysql主机才能操作。
dbadmin:all,ddl+dml+select
replica 用户:replica slave,replica client,切记权限扩大化
权限IP限制?dbadmin@·%·?DB服务器开放了外网,撞库风险。权限管理平台,可以定到10.10.10.10,10.10.10.%,10.10.%。千万不要%?
企业办公网访问?建议禁止,提供sql查询平台解决,跳转堡垒机进行访问。
4. MySQL的数据类型:够用即可
数值类型:tinyint (-128~127),int(±21亿),bigint()
字符串类型:char, varchar(200) varchar(50)
text 64K,行存:name ,age ,department,comment text
select name,age where
怎么办?
拆表:基本信息表:id,user-no,name,age
user-extend id,user-no,comment
定律:能用varchar(10000),也不要用text。
时间:date、datetime,timestamp
timestamp玩法:
1)存的值固定,时区不同,显示不同
2)长度4字节
3)update-time timestamp not null default current-timestamp on update current-timestamp;
业务:1、update name update time;有了上面方式,只更新要更新的内容,update-time自动更新。
datetime 1970之前,2038年之后,就用它。8字节够用。
注意点:千万不用用varchar存储时间varchar(255),时间范围查询,很慢!!!!
枚举:建议tinyint替换枚举,提前想好所有的枚举值,否则增加枚举值就是全表的数据拷贝ddl变更。
blob:二进制,有压缩。存储图片。
浮点数:flolt:非精度类型(四舍五入),decimal(20,10)精确类型
5. MySQL日志介绍
innodb日志:
- redo(事务流程、MVCC)
- undo(功能、不同版本undo特性)
server日志:
- binlog(复制,格式)
- errorlog(排查问题必备,有时需要ELK)
- general log(审计日志,跟slow log结合优化优先级,问题排查)
- slow log(30% sql优化,重要数据来源 pt-query-digest,sql advise,web页面+慢SQL报表)。
6. mysql 5.7新特性
链接: https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
-
数据库安全
引入ALTER USER命令,可用来修改用户密码,密码的过期策略,及锁定用户等
表空间加密
mysql.user表中存储密码的字段从password修改为authentication_string -
复制
MGR、多源复制、增强半同步(AFTER_SYNC)、基于WRITESET的并行复制、在线开启GTID复制、在线设置复制过滤规则 -
innodb
UNDO表空间的自动回收,InnoDB原生支持分区表,在此之前,是通过ha_partition接口来实现的,引入了innodb_deadlock_detect选项,在高并发场景下,可使用该选项来关闭死锁检测 -
系统表
优化了Performance Schema,其内存使用减少,集成了SYS数据库,简化了MySQL的管理及异常问题的定位 -
操作性
可设置SELECT操作的超时时长(max_execution_time)
在线修改Buffer pool的大小
引入了查询改写插件(Query Rewrite Plugin),可在服务端对查询进行改写
mysql_install_db被mysqld --initialize代替,用来进行实例的初始化
可通过SHUTDOWN命令关闭MySQL实例
看阿里的数据库内核月报:
链接: https://www.bookstack.cn/read/aliyun-rds-core/fa26cd1629beddd8.md
链接: https://www.bookstack.cn/read/aliyun-rds-core/eb37d1fcc2b4ce76.md
链接: https://www.bookstack.cn/read/aliyun-rds-core/a3a0f1d936417434.md
链接: https://www.bookstack.cn/read/aliyun-rds-core/615266555fb07b04.md
7. mysql 8.0新特性
链接: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
- 新增降序索引
order by A asc,B asc;
order by A desc,B desc;
order by A asc,B desc;
- 隐式索引
10个索引,索引优化,合并,干掉。drop index 秒执行。那后果??业务force index,月度跑,年度跑SQL,周跑,1天generl log。
ALTER TABLE t ALTER INDEX i INVISIBLE;
- 全局参数持久化
修改配置动态生效,100--->500,没有vim my.cnf;过了n久,主库切了或者抖动。连接数又恢复以前的
SET PERSIST max_connections = 500;
auto-cnf
-
字符集默认:Latin1->utf8mb4
-
系统表引擎:MyISAM->INnodb
-
DDL原子化,在MySQL 8.0之前,DDL操作是非原子型操作,在执行过程中遇到实例故障重启,可能导致DDL没有完成也没有回滚。
-
安全:身份认证方式由以前的mysql_native_password改为caching_sha2_password(就算同样的密码,加密后显示也不一样)鉴权认证driver层面也需要升级,测兼容性
-
binlog保留时间:由expire_logs_days->binlog_expire_logs_seconds
-
关闭查询缓存。
-
MGR优化
-
JSON、GIS加强
8. 数据库起不来的问题或常见数据库起不来异常处理
1.权限问题
# mysql安装时用的root用户,启动时用的mysql用户,目录或者文件的操作权限不够导致的启动问题。
# 相关提示或者日志报错:
# 日志中提示:Permission denied
# 解决方案:
# 赋权,需要提前建立mysql实例的启动用户,这里用mysql系统用户来举例
chown -R mysql:mysql $basedir/mysql_$port
chown -R mysql:mysql $datadir/mysql_data_$port
chown -R mysql:mysql $logdir/mysql_logerr_$port
chown -R mysql:mysql $slowdir/mysql_slow_$port
chown -R mysql:mysql $tmpdir/mysql_tmp_$port
chown -R mysql:mysql $binlogdir/mysql_logbin_$port
chown -R mysql:mysql $socket-dir/mysql_$port.sock
chown -R mysql:mysql $pid-dir/mysql_$port.pid
2.端口已经存在
# 安装mysql前,一定要查看操作系统环境,启动时查看端口是否已经启动,单机多实例或者忘记修改端口导致。
# 相关日志:
# [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
# [ERROR] Do you already have another mysqld server running on port: 3306 ?
# [ERROR] Aborting
# 解决问题:
# 查看当前mysql实例启动情况:ps -ef|grep mysql
netstat -tlpn|grep mysql
# 下线或者修改my.cnf为其他端口
3.之前机器上已经安装mysql并下线,数据目录忘记删除
# 解决方法:
# 如果确实是已经下线的mysql实例,为了避免误删除,可以mv到一个trash目录,另外就是直接删除;对于服务器复用的情况,建议重装OS。
4.参数配置问题
# 1.安装的高版本,配置文件还是基于低版本修改,对于已经删除或者废弃的参数不调整的话,也会导致启动失败
# 2.配置文件参数配置过大:比如配置了超过实际内存的innodb-buffer-pool-size。
# 报错:[ERROR] InnoDB: Cannot allocate memory for the buffer pool
5.硬盘问题
# 1.磁盘坏道,SSD读写寿命已到,你会发现都cd不到数据目录。
# 解决方案:
innodb_force_recovery 0~6
# 及时切换主或者从库,加强硬件使用监控。
# 2.磁盘满了,也是导致启动不起来的的原因。
6.误删数据
# 比如不小心删除了共享表空间ibdata或者binlog文件等。
# 解决方案:
# 一定要有主从+备份等可用性架构配置,就算误删除了系统文件,也是有备份可恢复,或者从库可以切。
# 如果真的只有一个单实例,对于ibdata被删的情况,可以通过拷贝所有表的idb的方式恢复。
7.Linux有个强制访问控制选项(Seliunx)导致mysql无法启动。
# 一般mysql都会修改默认的各种目录配置,修改配置跟Selinux冲突导致的启动问题
# 解决问题:
# sestatus查看SElinux的状态,setenforce 0关闭即可。
8.基于二进制安装,系统上缺少mysql的依赖文件而无法启动
yum -y install libaio
yum -y install numactl
9.mysql空实例,因为没有初始化而无法启动。
# 日志提示:can't open the mysql.plugin table ,please run mysql_upgrade to create it
# 解决方案:
# ./mysql_install_db —user=mysql —datadir=$datadir/mysql_data_$port —basedir=$basedir/mysql_$port
9. 了解MySQL对cup、内存、io子系统资源利用特点
10. 企业级生产案例:MySQL企业级部署安装-大厂标准集群化部署姿势
根据MySQL的特点 ,指定通用的部署规范
- 部署规范:目录、数据盘、log,
- 用自己的部署脚本给大家介绍部署姿势。wget or 目录 or 虚拟机镜像
- ansible or saltstack这个是高阶。
11. MySQL上云:MySQL in docker/MySQL in K8S
1、docker安装mysql
# 1.docker 安装
yum search docker
yum install -y docker
# 2.查看可用mysql版本:
docker search mysql
# 3.拉取官方的最新版本的镜像
docker pull mysql:latest
# 4.查看本地镜像
docker images
# 5.运行mysql容器
docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
# 6.查看安装的mysql
docker ps
# 7.登录mysql
2、mysql on k8s
- 安装k8s 大工程
- 编辑yaml来部署集群 PV,local starge,共享存储