本文详细介绍了MySQL数据库的基础管理，包括版本发展、标准化安装、客户端工具使用、系统压测、用户权限管理等内容。同时，重点讲解了MySQL的目录结构、对象分类、数据类型选择、复制原理与搭建，以及日志管理、审计日志等功能。此外，提供了关于数据类型、存储引擎、复制原理的深入理解，以及如何合理配置日志文件参数，进行日志维护与审计日志的使用方法。
经历了前的课程，基本算是把MySQL DBA基础过完：
1. MySQL几个版本的发展情况
2. MySQL标准化安装，MySQL多实例安装
3. MySQL Client类工具使用
4. MySQL sysbench压测，给MySQL搞点压力来模拟工作的实测环境
5. MySQL用户管理及权限公配，密码相安全问题。 GUI工具推荐。
本周计划
周一
1. 简单介绍一下MySQL内的目录结构（给裁剪MySQL目录一个参考）
2. 数据库里对象分类识别，全面认识一下今后要打交道的对象
3. 听故事学习MySQL数据类型，为Schema设计做准备（可能一次讲不完）
周三
1. MySQL存储引擎的特点，针对业务选择合理的存储引擎
2. 介绍MySQL复制原理及应用场景
本周内容以： 数据类型选择和复制原理及搭建为重点。
replication-manager
mysql目录结构：
ibdata文件，内部数据字，undo文件
- 插入撤销日志
- 更新撤销日志
ib_logfile文件，
- 事物记录
- 重做日志，redo文件
- 。。
frm：元数据
ibd表空间
日志文件
- 说明
- 可能会占用大量的磁盘空间
- 部分日志可以存在表里
- 分类
- binlog，也称变更日志，工作在server层，作用复制、恢复
- errorlog
- slowlog
- relaylog
- generallog
- auditlog
- ——————————–
- redolog ，引擎层
long_query_time=10 -> 1
看看形成的binlog数量，优化后，set global long_query_time=0.5;
1s = 1000ms
0.2 =200 毫秒
日志文件参数
配置日志过期时间、max值
expire-logs-days=3-7
max_binlog_size
二进制日志：
5.7默认配置binlog_format=row
gtid_mode=on
binlog 记录的格式的对比：
基于row格式的典型的bug：
案例1：
update tb set col1='10' where id=10; //案例1案例2：
update tb set col1='abc' where col3='10' limit 1; //案例2，在主从库上执行的结构是不一样的
案例3：
sysdate() //一些系统函数在不从库上也有可能不一致
uuid()查询二进制binlog文件：
//列出当前日志文件及大小
show binary logs;
//显示mysql当前日志及状态【super，replication，client权限】
show master status;
//mysql的binlog是以事件event为单位存储到日志中的，一个insert,update由多个event组成。（positon：位置、字节偏移量）
show binlog events in 'mysql-bin.000010';
查看binlog
mysqlbinlog -v -v --base64-output=decode-rows mysql-bin.000010 >1.sql维护：
删除
- 基于事件删除：
- set global expire_logs_days=7;
- purge binary logs before now()-interval 3 days;
- 根据文件名删除：
- purge binary logs to 'mysql-bin.000010';
5.7binlog是以”天”为单位保存的，v8.0 -> s
percona server 可以保存binlog个数
审计日志：
说明：
是官方收费组建
- 需要购买企业版
- 基于策略的日志记录
- 通过audit_log_policy选项设置
- 提供日志记录选项ALL、NONE、LOGINS或QUERIES，默认ALL
在日志文件中生成一个服务器活动审计记录
- 内容取决于策略，可能包括：
- 在系统上发生的错误的记录
- 客户机连接和断开连接的事件
- 客户机在连接期间执行的操作
- 客户机访问的db、tb
每个审计记录的timestamp采用UTC格式，NAME属性代表事件类型。例如”connect“表示登陆事件,”QUIT”表示客户机断开连接
操作：
需要安装audit_log插件，请使用INSTALL PLUGIN语法，如：
INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
或者可以在server启动时设置 Plugin-load选项：【mysqld】
总结：
认识information_schema库
学习利用I_S的字典信息生成语句
I_S时内存库，只读
存储server的统计信息（状态便令，设置，连接）
该库不持久化，”虚拟db“
可以通过select访问
I_S重要对象
select table_name from information_schema.tables wehre table_schema='information_schema' order by table_name;常见用法：
select table_name,engine from information_schema.tables where table_schema=’db_01’;
select character_set_name,collation_name from I_S.collations where is_default=’Yes’;
select table_schama,count(*) from I_S.tables group by table_schame;
只要能写入binlog的，都会被复制，如果没被复制，可能时从库开了过滤规则。
IS作用：
比如统计所有库的大小：
#以 “G” 为单位
select sum(data_length+index_length)/1024/1024/1024 as G from tables where table_schema='db_01'; 统计所有数据大小、索引大小
select sum(data_length)/1024/1024 as dataMB,sum(index_length)/1024/1024 as indexMB from tables where table_schema=db_01= group by table_name;
统计每张表的大小：
I_S 是字典类的基表。tables、innodb_trx、processlist用的比较多。
P_S：性能类的基表，非人类能阅读的
sys库 是人类能阅读的
常见用法 -> 生成语句
可以结合into outfile使用
【mysqld】
secure_file=priv=/tmp/
核心语句：
##show
#一般用法
show databases;
show tables;
show processlist;
show create table <tb_name>;
show index from <tb_name>;
show open tables;
show table status;
# show 还支持和like & where 使用
show databases like 'db_%';
show columns from db_01 where 'Default' is null;
show character set;
show collation;
MySQL数据类型选择
整型：tinyint / smallint / mediumint / int / bigint
浮点型：float / double
日期型：datetime / timestamp
字符型：char / varchar / text / blob & utf8 /utf8mb4 …
字段选择的规范
整型：
范围、长度：
原则：够用的前提下，越小越好
int：有符号 pow(2,31)-1
无符号 pow(2,32)-1
int(1) 是修饰符：
create table t1(id int(1));
insert into t1(id) values(pow(2,31)-1);
select * from t1; //插入成功！
int 和 mediumint 如果join一下，如果由索引的话，是用不到索引的。
自增序列，建议无符号的，unsigned。
如果由大量删除、写入，建议bigint。
类型转移：
int（11） ,11是修饰符，不是长度限制
oracle，number(11)，11是长度限制
int（8） ，zerofill，zerofill也是修饰符，左侧补0，int（8），存储1024，显示00001024
自行测试：version 5.7是否存在数据溢出的问题。
mysql整型注意事项：
- 数字类型后面的数字 是一个显示宽度，宽度结合zerofill才能使用。
- 表数据 <2千万建议只考虑 tinyint,int,bigint
- 同一个字段在其他表中要注意字段类型必须一致
浮点型
FLOAT
中了20万，1.3万人来分，最后除不尽，怎么办？
decimal，非精度 -> 精度，四舍五入
世界杯： 订单合买、订单分钱，浮点数除不尽
尽量不用浮点数，存成以分为单位即可。
- 互联网的世界不需要浮点数
float（p）
- 0 <= p <=24时，占用4字节存储空间
- 25 <= p <=53 时，占用8字节存储空间
float，没有长度定义
- 占用4字节存储空间
float是单精度
- 用于存储一般精度金额
- 建议把金额存储int型，更精确
- float列类不指定精度时不能等值查询
- num FLOAT,where num=0.12，查到结果为空，需要用范围查询
- num FLOAT（9，7），where num=0.12，可查到
DECIMAL
精确的十进制浮点型，高精度计算
M最大65（默认10），D最大30（默认0）
小数点后面的位数超限后，自动四舍五入（SQL_MODE=”时）
非常占用空间，c1 decimal(11) = c1(int)，都是占用11个字节
日期型
| DataType | Storage Required Before MySQL | Storage Required as of MySQL 5.6.4 |
|---|---|---|
| YEAR | 1 byte | 1byte |
| DATE | 3 bytes | 3bytes |
| TIME | 3 bytes | 3 bytes + fractional seconds storage |
| DATETIME | 8 bytes | 5 bytes + fractional seconds storage |
| TIMESTAMP | 4 bytes | 4 bytes + fractional seconds storage |
| Fractional Seconds | Storage Required |
|---|---|
| 0 | 0 byte |
| 1，2 | 1 bytes |
| 3，4 | 2 bytes |
| 5，6 | 3 bytes |
TIME（0），TIME（2），TIME（4），TIME（6），分别需要3，4，5，6字节
优先用TIMESTAMP，其次时DATETIME
- timestamp、datetime从mysql5.6.5开始均支持自动更新为current_timestamp
- 日期转换
- cast（datetime_col as date）
- select now()+0;
- 日期运算
- select ‘2018-12-31 23:59:59’ + interval 1 second;
- date_add(date,interval expr unit) / date_sub(date,interval expr unit)
select count(*) from sys_config;
案例：数据就一行，出现在慢查询slow.log里，原因把大字段设置成null了。
字符型
char和varchar的对比：来自官网dev.mysql.com
案例：70G表 -> 7G
调整了text字段，
可以查看排序
show global variables like '%sort%';
排序时按照字段实际长度，还是按照字段定义长度分配呢？
max_length_for_sort_data | 1024
max_sort_length | 1024
- varchar是变长类型，需要额外1-2字节
- varchar实际存储长度超过255字节时，会被当做TEXT处理
- 所有varchar列的总长度时65535字节（实际上是65533）
- 实际存储字节数：字符数 * 字符集字节数（utf8是3字节）
- 字符集是utf8mb4时，实际可存储字符数时FLOOR（65533/4）=16384
开发规范：
https://github.com/zhishutech/mysql-sql-standard/blob/master/schema/field_rule.md
看信用卡、身份证怎么存的。
要是text需要拆分一下
先写binlog，再做事务提交
先引擎层，->再server
扫一扫
1049
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
