第一章 MySQL日志系统
第一节 MySQL日志系统
1. mysql 日志分类
l 服务器日志.
n 记录进程启动运行过程中的特殊事件， 帮助分析MySQL服务遇到的问题
n 根据需求抓取特定的SQL语句，追踪性能可能存在的问题的业务SQL
l 事务日志
n 记录应用程序对数据的所有更改
n 可用于数据恢复
n 可用于实例间数据同步
服务器日志：服务器错误日志、慢查询日志、综合查询日志
事务日志：存储引擎事务日志、二进制日志
2. 服务错误日志：
l 记录实例启动运行过程中重要消息
l 配置参数
l log error = /data/mysql data/node-1/mysqld.log
l 内容并非全是错误消息
l 如果mysqld进程无法正常启动首先查看错误日志
3. 慢查询日志
l 记录执行时间超过一定阈值的SQL语句
l 配置参数
slow query log = 1
slow query log file = /data/mysqldata/node-1/ mysql-slow.log
long query _time= 5
l 用于分析系统中可能存在性能问题的SQL
4. 综合查询日志
l 如果开启将会记录系统中所有SQL语句
l 配置参数
general log= 1
general log flile = /data/mysql data/node-1/mysql-gen.log
l 偶尔用于帮助分析系统问题,对性能有影响
5. 存储弓|擎事务日志
l 部分存储弓|擎拥有重做日志(redo log)
l 如InnoDB,TokuDB等WAL(Write Ahead Log)机制存储弓|擎
l 日志随着事务commit优先持久化,确保异常恢复不丢数据
l 日志顺序写性能较好
6. InnoDB事务日志重用机制
InnoDB事务日志采用两组文件交替重用
7. 二进制日志binlog
l binlog (binary log)
l 记录数据|起数据变化的SQL语句或数据逻辑变化的内容
l MySQL服务层记录,无关存储弓|擎
l binlog的主要作用:
n 基于备份恢复数据
n 数据库主从同步
n 挖掘分析SQL语句
开启binlog
l 主要参数：
log bin = c:/tmp/mylog/mysql-bin
sqllog bin= 1
syncgbinlog= 1
l 查看binlog文件：
Show binary logs;
binlog管理
l 主要参数
max binlog size = 100MB
expire logs days= 7
l binlog始终生成新文件,不会重用
l 手工清理binlog
purge binary logs to ‘mysql-bin.000009’;
purge binary logs before ‘2015-06-01 22:46:26’;
查看binglog内容
l 日志(Log)
show binlog events in ‘mysql-bin.000011;
show binlog events in ‘mysql-bin.000011’ from 60 limit 3;
l mysqlbinlog工具
mysqlbinlog c:/tmp/mylog/ mysql-bin.000001
—start-datetime| -ostop-datetime
—start-position —stop-position
binlog格式
l 主要参数
binlog format = {RQW |STATEMENT| MIXED}
l 查看row模式的binlog内容
mysqlbinlog —base64-output=decode-rows -V c:/tmp/mylog/mysql-bin.000001
第二节 慢日志分析
1. 通过MySQL Processlist定位慢查询
l show processlist
l show full processlist打印全部sql语句
2. MySQL Slowlog使用与介绍
l Slowlog 设置:
set global slow_query_log=ON/OFF;打开、关闭slowlog
set global long_query_time=1/5/0.2;设置慢查询阈值
Set global log_output= “FILE”/“TABLE”;设置慢查询存储位置
show variables like’slow_query_log_file’;查看sloglog文件位置
l 慢查询日志
Less mysql-slow.log
3. 使用pt-query-digest分析慢查询
l 使用tcpdump抓取mysql协议包
第二章 MySQL备份与恢复
第一节 MySQL数据备份
1. 备份用途
l 数据灾备
n 应对硬件故障数据丢失
n 应对人为或程序bug导致数据误删除
l 制作镜像库以供服务
n 需要将数据迁移、统计分析等用处
n 需要为线上数据建立一个镜像
l 数据
数据文件或文本格式数据
l 操作日志（binlog）
数据库变更日志
2. 数据库备份类型：
l 冷备份
关闭数据库服务,完整拷贝数据文件
l 热备份
在不影响数据库读写服务的情况下备份数据库
l 物理备份
以数据页的形式拷贝数据文件
l 逻辑备份
导出为裸数据或者SQL(insert)语句—简单，但是速度很慢
l 本地备份
在数据库服务器本地进行备份
l 远程备份
远程连接数据库进行备份
l 全量备份
备份完整的数据库
l 增量备份
只备份上一次备份以来发生修改的数据
3. 备份周期
l 考虑因素:
l 数据库大小(决定备份时间)
l 恢复速度要求(快速or慢速)
l 备份方式(全量or增量)
4. 常用工具
l mysqldump -逻辑备份,热备 ——逻辑备份，单线程
l xtrabackup -物理备份,热备
l Lvm/zfs snapshot -物理备份
l mydumper -逻辑备份, 热备 ——多线程比mysqldump快一些
l cp-物理备份,冷备
mysqldump：MySQL自带的一个备份工具
常用工具及用法- xtrabackup
l 特点:
l 开源,在线备份InnoDB表
l 支持限速备份,避免对业务造成影响
l 支持流备
l 支持增量备份
l 支持备份文件压缩与加密
l 支持并行备份与恢复,速度快
xtrabackup备份原理
l 基于InnoDB的crash-recovery功能（事务崩溃恢复的功能）
l 备份期间允许用户读写,写请求产生redo日志，。
l 从磁盘上拷贝数据文件
l 从InnoDB redo log file实时拷贝走备份期间产生的所有redo日志.
l 恢复的时候数据文件+ redo日志= -致性数据.
实用脚本innobackupex
l 开源Per|脚本,封装调用xtrabackup及一 系列相关工具与OS操作,最终完成备份过程
l 支持备份InnoDB和其它引|擎的表
l 备份一致性保证
5. 如何定制备份策略
需要考虑的因素
| 数据库是不是都是innodb引擎表 | 备份方式，热备or冷备 |
|---|---|
| 数据量大小. | 逻辑备br物理备，全量or增量 |
| 数据库本地空间是否充足 | 备份到本地or远程 |
| 需要多快恢复 | 备份频率小时or天 |
<br /> <br /> <br /> <br />第二节 MySQL数据恢复<br />1. 备份需求分析<br />2. 制定备份策略<br />3. 备份脚本编写<br />4. 数据恢复原则<br /> <br />**第三章 MySQL线上运维**<br />第一节 MySQL线上部署<br />1. 理解线上部署考虑的因素<br />考虑因素:<br />l 版本选择，5.1、5.5 还是5.6 ?<br />l 分支选择，官方社区版? percona server? Mariadb?<br />l 安装方式，包安装?二进制包安装?源码安装?<br />l 路径配置,参数配置(尽量模板化、标准化)<br />l 一个实例多个库or多个实例单个库?<br />2. 学会编译安装以及二进制安装MySQL<br />l 二进制安装MySQL<br />l 下载软件安装包<br />l 解压放到指定目录 (比如/usr/local )<br />l 将MySQL目录放到PATH中<br />l 初始化实例,编辑配置文件并启动<br />l 账户安全设置<br />下载MySQL源码安装包<br />l 安装必要包(make cmake bison-devel ncurses devel build essential)<br />l Cmake配置mysq|编译选项,可以定制需要安装的功能,编译选项参考: .<br />l [http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html](http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html)<br />make && make install<br />l 初始化实例,编辑配置文件并启动<br />l 账户安全设置<br />学会升级MySQL<br />学会多实例部署MySQL数据库<br />学会合理部署MySQL线上库<br />l 根据需求选择合适的版本以及分支,建议使用或升级到较高版本5.5或5.6<br />l 如果需要定制MySQL功能的话,可以考虑编译安装,否则的话建议使用<br />二进制包安装,比较省事<br />l 根据机器配置选择部署多个MySQL实例还是单个实例,机器配置非常好<br />的话,建议部署多实例<br /> <br />第二节 MySQL主从复制<br /><br />MySQL主从复制用途：<br />实时灾备，用于故障切换<br />读写分离，提供查询服务<br />备份，避免影响业务<br />1. 学会部署MySQL主从复制<br />l 主从部署必要条件:<br />主库开启binlog日志(设置log-bin参数)<br />主从server-id不同<br />从库服务器能连通主库<br />MySQL主从复制部署<br />l 主从部署步骤:<br />备份还原(mysqldump或xtrabackup)<br />授权(grant replication slave on *.*)<br />配置复制,并启动(change master to)<br />查看主从复制信息( show slave status\G )<br />2. 了解复制基本原理<br /><br />3. 学会配置半同步复制、并行复制<br />l 存在的问题:<br />主库宕机后，数据可能丢失<br />从库只有一个sql thread,主库写压力大，复制很可能延时<br />l 解决方法:<br />半同步复制<br />并行复制<br />4. 学会配置部分数据复制、联级复制<br />MySQL semi-sync (半同步复制)<br />异步复杂逻辑<br /><br />半同步复制逻辑<br /><br />半同步复制:<br />l 5.5集成到MySQL，以插件的形式存在，需要单独安装<br />l 确保事务提交后binlog至少传输到- - 个从库<br />l 不保证从库应用完这个事务的binlog<br />l 性能有一-定的降低，响应时间会更长.<br />l 网络异常或从库宕机，卡住主库,直到超时或从库恢复<br /> <br /> <br /> <br /> <br />配置MySQL半同步复制<br /><br />配置MySQL并行复制<br />并行复制: .<br />l 社区版5.6中新增<br />l 并行是指从库多线程apply binlog<br />l 库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)<br />l 设置<br />set global slave_ parallel_ workers=10; 设置sq|线程数为10<br />5. 复制监控<br />查询从库状态: Show slave status\G<br />l 复制出错处理<br />常见:<br />1062 (主键冲突) 1032 (记录不存在<br />解决:手动处理<br />或:<br />跳过复制出错:set global sql_ slave_ skip_ counter=1<br />6. 总结<br />l MySQL主从复制是MySQL高可用性、高性能(负载均衡)的基础<br />l 简单、灵活，部署方式多样，可以根据不同业务场景部署不同复制结构<br />l MySQL主从复制目前也存在一些问题，可以根据需要部署复制增强功能来解决问题<br />l 复制过程中应该时刻监控复制状态，复制出错或延时可能给系统造成影响<br />l MySQL复制是MySQL数据库工程师必知必会的一-项基本技能<br /> <br /> <br /> <br /> <br /> <br /> <br />
第三节 MySQL日常运维
1. MySQL DBA基本的日常运维工作
l 日常
导数据、数据修改、表结构变更
加权限、问题处理
l 其它.
数据库选型部署、设计、监控、备份、优化等
2.日常工作的注意事项
l 数据最终形式(csv、sql文本还是直接导入某库中)
l 导数据方法(mysqldump、 select into outfile)
l 导数据注意事项:
导出为csv格式需要file权限，并且只能数据库本地导
避 免锁库锁表(mysqldump使用- -single- transaction选项不锁表)
避免对业务造成影响，尽量在镜像库做
3.学会更安全的处理日常工作
数据修改及注意事项
l 修改前切记做好备份
l 开事务做，修改完检查好了再提交
l 避免一次修改大量数据，可以分批修改
l 避免业务高峰期做
表结构变更注意事项
l 在低峰期做
l 表结构变更是否会有锁? (5. 6包含online ddl功能)
l 使用pt-onl ine-schema-change完成表结构变更
n 可以避 免主从延时
n 可以避免负载过高，可以限速
加权限及注意实现
l 只给符合需求的最低权限
l 避免授权时修改密码
l 避免给应用账号super权限
问题处理(数据库慢?)
l 数据库慢在哪?
l Show processlist查看mysql连接信息
l 查看系统状态(iostat, top, vmstat)
4. 小结.
l 日常工作比较简单，但是任何一个操作都可能影响线上服务
l 结合不同环境，不同要求选择最合适的方法处理
l 日常工作应该求稳不求快，保障线上稳定是DBA的最大责任
第四节 MySQL参数调优
为什么要调整参数
l 不同服务器之间的配置、 性能不- -样
l 不同业务场景对数据的需求不一-样
l MySQL的默认参数只是个参考值,并不适合所有的应用场景。
1. MySQL数据库的参数调优手段
优化之前我们需要知道什么
l 服务器相关的配置
l 业务相关的情况
l MySQL相关的配置
服务器上需要关注哪些
l 硬件情况
l 操作系统版本
l CPU、网卡节电模式
l 服务器numa设置
l RAID卡缓存
磁盘调度策略-Write Back——性能好
l 数据写入cache既返回,数据异步的从cache刷入存储介质
磁盘调度策略-Write Through——安全性高
l 数据同时写入cache和存储介质才返回写入成功。
RAID
l RAID Redundant Array of Independent Disks
生产环境里一般不太会用裸设备,通常会使用RAID卡对一块盘或多块盘做RAID
RAID卡会预留一块内存,来保证数据高效的存储与读取
常见的RAID类型有:RAID1、RAID0、 RAID10和RAID5
常见的RAD：
RAID如何保证数据安全
l BBU (Backup Battery Unit)
BBU保证在WB策略下,即使服务器发生掉电或宕机,也能够
将缓存中的数据写入到磁盘,从而保证数据的安全
2. MySQL有哪些注意事项
MySQL的部署安装
部署MySQL的要求：
l 推荐的MySQL版本: >= MySQL5.5
l 推荐的MySQL存储引擎: InnoDB
MySQL的监控
系统调优的依据:监控
l 实时监控MySQL的Slow Log
l 实时监控数据库服务器的负载情况
l 实时监控MySQL内部状态值
通常关注哪些MySQL Status
l Com Select/Update/Delete/Insert
l Bytes received/Bytes sent
l Buffer Pool Hit Rate
l Threads. connected/Threads created/Threads _running
3. MySQL参数调优
读优化
l 合理利用索弓引 |对MySQL查询性能至关重要
l 适当的调整MySQL参数也能提升查询性能
innodb buffer pool_size(缓冲池大小)
l InnoDB存储弓|擎自己维护一块内存区域完成新老数据的替换
l 内存越大越能缓存更多的数据:
innodb thread. concurrency(保护并发)
l InnoDB内部并发控制参数,设置为0代表不做控制
l 如果并发请求较多,参数设置较小，后进来的请求将会排队
写优化
l 表结构设计.上使用自增字段作为表的主键
l 只对合适的字段加索引,索引太多影响写入性能
l 监控服务器磁盘IO情况,如果写延迟较大则需要扩容
l 选择正确的MySQL版本,合理设置参数
哪些参数有助于提高写入性能
l innodb flush. log. at trx. commit && sync. Binlog
l innodb log file size
l innodb io capacity ——控制innidbIO性能的参数
l innodb insert buffer MySQL独有
n innodb flush. log. at trx. Commit ——控制innoDB事务日志的刷新
N = 0 (高效，但不安全)
N = 1（低效，非常安全）
N = 2 （高效，但不安全）
n sync. Binlog ——控制二进制日志的刷新
控制每次写入Binlog ,是否都需要进行一 次持久化
如何保证事务安全
l innodb flush log at _trx commit和sync. binlog都设为1
l 事务要和Binlog保证-致性
串行有哪些问题
l SAS盘- -般每秒只能有150- 200个Fsync.
l 换算到数据库每秒只能执行50-60个事务
社区和官方的改进
l MariaDB提出改进,即使这两个参数都是1也能做到合并效果，性能得到了大幅高。
l 官方吸收了MariaDB的思想,并在此基础上进行了改进,性能再次得到了提高。
Tips:
n 官方在MySQL5.6版本之后才做了这个优化。
n Percona和MariaDB版本在MySQL5.5已经包含了这个优化
InnoDB Redo log
l Write ahead Log
Redo log用在数据库崩溃后的故障恢复
Redo log有哪些问题
l 如果写入频繁导致Redo log里对应的最老的数据脏页还没有刷新到磁盘,此时数据库将卡住,强制刷新脏页到磁盘
l MySQL默认配置两个文件才10M ,非常容易写满,生产环境中应适当调整大小
innodb_ io. capacity
l InnoDB每次刷多少个脏页,决定InnoDB存储弓|擎的吞吐能力。
l 在SSD等高性能存储介质下,应该提高该参数以提高数据库的性能。
Insert Buffer
l 顺序读写VS随机读写
l 随机请求性能远小于顺序请求
l MySQL从5.1版本开始支持InsertBuffer
l MySQL5.5版本之后同时支持Update和Delete的Merge
l Insert Buffer只对二 级索引且非唯- -索引有效
4. 总结
l 服务器配置要合理(内核版本、磁盘调度策略、RAID卡缓存)
l 完善的监控系统,提前发现问题!
l 数据库版本要跟上,不要太新,也不要太老
l 数据库性能优化:
查询优化: 索引优化为主,参数优化为辅
写入优化：业务优化为主，参数优化为辐
若有收获，就点个赞吧
0 人点赞
