握瑾怀瑜 发布的文章

mysql 学习记录07-性能调优与诊断

测试方法

关键性指标

1. IOPS(Input/Output operations Per Second,每秒处理的I/O请求次数)

传统的机械磁盘的I/O能力(不管是吞吐量还是IOPS)进展缓慢,严重滞后于系统中的其他组件,因此磁盘I/O能力往往都称为整套系统中的瓶颈,这也是目前的现状,而判断磁盘的I/O能力的指标之一,就是IOPS。

需要说明一点,通常提到的磁盘读写能力,比如形容他每秒读300M,写200M,这个说的数据吞吐量(I/O)能力的另一个关键指标,但是IOPS指的可不是读写的数据吞吐量,IOPS指的是每秒能够处理的I/O请求次数。

什么是一次I/O请求呢?举个例子来说,读写100MB的文件,就是一次I/O请求,写入1B的数据,这也是一次I/O请求,反应快的朋友看到这肯定明白了,IOPS指标越高,那么单位时间能够响应的请求自然就越多。从理论上来讲,只要系统实际的请求数低于IOPS的能力,就相当于每一个请求都能即时得到响应,那么I/O就不会是瓶颈了。

不过,往深处想一想,旋即又产生了疑问,这个IOPS只是请求次数,可请求与请求不一样,就像前面说的,同样一个请求,读取100MB的文件所需要耗费的时间,肯定比写入1B的时间要长上不少,这中间除了寻道时间耗时,差异除妖体现在数据传输的时间上,而数据传输时间实际上就是最开始提到的吞吐量了。

如果想I/O系统的响应足够快,那么IOPS越高越好,或者换种说法,IOPS指标比较高的话,就更适合要快速响应的系统,尤其是对于短连接,小事务,轻量数据作为操作特点的OLTP系统,当然吞吐量也很重要,但是吞吐量对于磁盘来说基本是个确定的值,没什么讨论空间。

我们怎么衡量服务器的IOPS呢,在Linux下和Windows下都有很多性能测试工具,可以获得IOPS指标,比如Linux平台上较为流行的IOmeter/FIO,Windows平台下最常用的磁盘性能测试工具HD tune,输出的测试结果中均包含IOPS,不过对于传输的机械式磁盘,这个指标其实不必专门测试。

一方面是因为磁盘读写性能测试环境太多,只通过一个难有代表性,如果要搞多重全面测试的话,偏离了本章的注意;再其次,传统磁盘完成一个I/O请求所花费的时间受到3个方面因素的影响

  • 寻道时间(Tseek) : 将磁头移动到数据所在的磁道上所需要的时间,通常都在3~15ms。
  • 旋转延迟时间(Trotation) : 将盘头旋转,使所请求的数据所在扇区移动至磁头下方所需要的时间,这个时间跟磁盘的转速密切相关,转速越快,延迟越短,一般15000转的磁盘平均旋转延迟时间为2ms。
  • 数据传输时间(Transfer) : 完成传输所请求的数据所需要的时间

考虑到数据传输时间跟要传输的数据量关系密切,而传输数据量又与吞吐量密切相关,这个变数太多,不过为了方便推算,我们假定要传输的数据很小,或者吞吐量极高,数据能在瞬间完成。也就是说,在计算时我们先忽略数据传输的时间,那么根据现有信息就可以计算出磁盘理论上的最大IOPS,计算公式为 IOPS = 1000ms/(寻道时间 + 旋转延迟时间)。

基于这一公式计算的话,单块SAS 15K转的硬盘,其中最大的IOPS=1000/(3+2),约为200个每秒。这是理论上的最大值,实际表现一定超不过这个值,如果是万转的磁盘或更低速的磁盘,那么这个指标还会更低。而且在实际场景中,数据顺序读个随机读时,寻道或旋转延迟肯定不同,因此单位时间内的请求响应能力肯定也都不一样,当然,相比前面的公式计算出的值,只会更低,不会更高。

要提高IOPS,目前来看就是拼硬件,传统方案是使用多块磁盘通过RAID调带后,使I/O读写能力获得提升。比如我们希望IOPS达到5000,那么理论上就需要5000/200=25块磁盘,组成RAID0来实现。

考虑到上面提供的IOPS指标只是理论值,实际表现往往低于这个值,因此应该留足富余,这里提到的数据都只是理论参考值,现实中需要根据实际情况操作。另外,不同RAID类型,计算公式也需要有所调整。

举例来说,RAID5上每个写的I/O操作,分别需要读写数据和校验位,计算后再写入数据和校验位,也就是说,对于RAID5,每个写I/O操作实际将产生4次I/O,若视同RAID5条带后的存储系统,写入时IOPS能达到5000/s,那就至少需要4 * 5000 / 200 = 100块磁盘

当然这也只是理论值,实际上极少会存在纯写入而不读取的系统,更多都是读写平均,或多读少写。假如仍然是套RAID5条带的存储系统,但平均下来系统有1/3时间在做写入操作,2/3在做读取操作,那么实际需要的磁盘数就可能变为(2/35000+41/3*5000)/200。

必须再次强调,这只是理论,实际情况需要考虑到各种细节,除了完全依赖于硬件的阵列算法,缓存命中率等,还有实际应用时数据访问特点都有可能对性能造成波动,总之一句话,不管是吞吐量还是IOPS,组RAID时磁盘数一定要留足富裕。

还有一个关键的现实因素,前面动辄就需要几十块盘起,在现实场景中极有可能不具备可操作性,因为通常MySQL数据库的数据文件都保存在本地磁盘,在显示场景中既有可能不具备可操作性,因为通常MySQL数据库的数据文件都保存在本地磁盘,而不会像Oracle数据库使用专有的独立存储,普通的2U x86服务器,磁盘挂满也就能插几十块,所以很多情况下不是买不起次破案,而是买来也挂不上去。就像前面计算单块盘的IOPS,普通x86服务器,整体磁盘的IOPS最大值也能计算出来。

基于现实背景考虑,I/O的处理能力尽管非常重要,但也只能是整体架构设计中的环节之一,单机性能再强也无法满足所有场景需要,因此集群方案在架构设计时就得考虑在内。

固态硬盘越发火热,由于SSD磁盘通过电子信息来工作,天生无机械构件,没有马达和磁片,即使是运行状态也完全静音,关键是避免了传统机械式磁盘在寻道的盘片旋转上的时间开销,这使得寻址时间超短,IOPS可以做到很高(相比传统磁盘而言),不同厂商的不同产品差距巨大。单块SSD磁盘IOPS达到几千只是刚起步,几万甚至几十万都有可能,像Fusion-IO这种甚至能突破百万IOPS。

固态硬盘SSD相比传统硬盘,主要是在IOPS方面有巨大优势,I/O延迟较低,不过在吞吐量指标上,相比传统磁盘的优势就不那么明显了(还是有优势)。由于自身实现原理,存在数据擦除操作,尽管可以通过一定的几千/算法尽量避免,但还是有可能在用来一阵时间之后响应变慢的情况。

同时SSD的IOPS数值受较多因素的影响,比如数据读写的特征(和传统磁盘一样),使用时间,系统配置,甚至驱动程序都有关系。此外,在使用寿命方面,相比传统磁盘也有一定差距,至于容量/性价比更是搞的离谱。

像我们之前的制定的5000 IOPS目标,如果对容量没有要求,那直接上一块SSD可能就满足了。听起来SSD相当不错,那是否往后就用SSD了?这个在可能的前提下,能选择SSD磁盘绝对应该选用SSD,只不过就目前来看,还不可能所有场景均使用SSD磁盘。制约固态磁盘普及的因素主要有两个,一方面是容量(已有较大改善),另一方面是价格(也在稳步下降)。不过,只要银子足够充足,多块SSD磁盘再做条带,性能嗷嗷叫,I/O不为瓶颈都不是梦。这种思路简单粗暴,但着实有效,只是这种确实耗费银两,多数企业不肯接受这个成本投入,再加上规模也没到这个级别,在有别的选择缓解瓶颈的前提下,还是通过别的方案来处理性能问题吧,这也是性能调优依然被视作较有技术含量的因素之一。

2. QPS(Query Per Second,每秒请求(查询)次数)

要注意这个参数,尽管所有数据库都有这个指标,单对于MySQL数据库来说,这个指标尤其重要,因为MySQL数据库中,尽管能拿来衡量性能的指标众多,可整租称得上实用又好用的,就是QPS了。顾名思义,就是每秒查询次数,还有比这更指标更直观反映系统(查询)性能的吗,这就像用IOPS衡量磁盘每秒钟能接受多少次请求,明确且直观。

这个指标的获取也比较简单,MySQL数据库原生就提供有QPS指标值,DBA可以在mysql命令行模式下执行status命令,返回的最后一行输出信息中就包含QPS指标,此外,通过mysqladmin命令附加status参数,也能查询到QPS指标。

不过MySQL原生提供的QPS指标,是该MySQL实例生命周期内的全局指标,这个平均值具备移动参考意思,可是我们都知道系统有忙有闲,闲时就不用说了,QPS完全可以为0,那么系统繁忙时变现如何,峰值承载的QPS能达到多少,这就得通过其他方式获取了。

MySQL提供了一系列的状态变量,其中有一项就是用来记录当前的请求次数,即Questions状态变量的值。尽管这也是MySQL实例生命周期内的全局指标,不过我们只要每隔一秒查询下这个变量值,并将相邻的两值相减,得到的就是精确的每一秒的实际请求数了,如果当前MySQL数据库恰处于,那么我们获取的值就可以视为该MySQL实例的QPS峰值响应能力。

MySQL数据库原生提供的QPS指标,就是通过Questions状态变量除以Uptime状态变量的值所得到的结果,即QPS=Questions/Uptime。我们自己计算QPS时,工时大致也是如此,只是把Uptime换成我们自己定义的时间单位就好了。

3. TPS(Transation Per Second,每秒事务数)

按说TPS指标应该更加重要,可是考虑到MySQL数据库这种开创式的插件式存储引擎的设计,必然存在着并非所有存储引擎都支持事务,这也导致TPS在MySQL数据库体系内,使用范围并不是100%,不过他依然很重要,因为最为流行的存储引擎之一InnoDB是支持事务的,因此这个参数还是需要我们重点关注的。

TPS参数MySQL原生没有提供,如果需要的话我们得自己算,计算方法去QPS同理,任然是基于MySQL数据库提供的一系列变量,计算公式为 :

TPS = (Com_commit + Com_rollback) / Seconds

这个公式里面又出现了两个状态变量,分别代表提交次数和回滚次数,Seconds就是我们定义的时间间隔,如果把这个换成Uptime,那么该公式就能计算该MySQL实例在本次生命周期中的瓶颈TPS。

获取关键性指标

前面提到的3个指标,IOPS先跳过,这个纯硬件指标,尽管对DB性能影响很大,不过硬件因素通常不是由DBA所能左右的,所以我们这里重点关注OPS和TPS,在系统资源层面,我们重点关注CPU占用情况。也就是说,我们会重点统计CPU和QPS,如果能统计到TPS(对象使用了支持事务的存储引擎),那就顺道将TPS指标也手机以下

1. 手动获取关键性指标

mysql> show global status like "%Questions%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 138   |
+---------------+-------+

值是取到了,但是现在面临两个问题
1、QPS统计的是每秒产生的请求数,怎么让其按秒输出Questions状态变量的值呢?卡着秒取值吗...
2、当前的数据没有访问量,怎么让数据库繁忙起来?

2. 自动获取性能指标

先解决第一个问题,自动统计请求

# 每隔一秒查询一次,并返回差值
[[email protected] mysql3307]# mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep Questions
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Questions                                     | 140                                              |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |

通过灵活的使用mysqladmin命令,咱们还可以用来获取MySQL实例每秒查询,更新,删除数据等指标,如每秒SELECT,UPDATE数量

mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep -E "Com_select|Com_update"

再解决第二问题,使用mysqlslap,一款mysql自带的专用清理压测工具

在执行mysqlslap命令期间,mysqladmin命令仍需在运行,否则就看不到它的输出了。

mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --query="select * from mysql.user;" --number-of-queries=100000 -c 30 -i 10 --create-schema=jssdb

参数解释

  • --number-of-queries : 指定测试要执行的查询语句。
  • -c, -concurrency : 指定测试执行请求的并行度
  • -i,-iterations : 指定测试运行的次数
  • --create-schema : 指定此次测试在哪个schema下执行

上面的命令翻译过来就是 : 在jssdb数据库下,模拟30个用户连接,共执行100万次请求。

mysqlslap命令功还支持其他的参数

  • -a,--auto-generate-sql : 自动生成测试所需的SQL语句。
  • -x,--number-char-cols : 指定生成的表对象中VARCHAR类型列的数量,默认只有一个
  • -y,--number-int-cols : 指定生成的表对象中INT类型列的数量,默认只有一个
  • --auto-generate-sql-add-autoincrement : 自动生成测试用的表对象时,在表中增加AUTO_INCREMENT列
  • --auto-generate-sql-execute-number : 指定此次测试要执行的查询次数
  • --auto-generate-sql-guid-primary : 自动生成基于GUID为主键的表,本参数与--auto-generate-sql-add-autoincrement参数互斥
  • --auto-generate-sql-load-type : 指定测试类型,可选值如下

    • read : 读
    • write : 写
    • key : 通过主键读
    • update : 更新操作
    • mixed : 既有读也有写,默认就是这个
  • --auto-generate-sql-secondary-indexes : 指定自动生成的表中辅助索引(或者说是非主键索引)的数量
  • --auto-generate-sql-unique-query-number : 指定生成的查询语句个数。举例来说生成100个查询语句,如果设置此次测试共执行100次,那就代表每次测试执行的都是不同的SQL语句,如果设置此次测试共执行1万次,那就代表每个查询语句会被执行100次(前面的相当于同一条语句被执行了100万次)。默认值为10。
  • --auto-generate-sql-unique-write-number : 指定生成的插入语句个数,与上同理,只是这个参数专用于指定参入语句,默认值是10,通常与- --auto-generate-sql-write-number联用
  • --auto-generate-sql-write-number : 指定每个线程执行时插入的记录数,默认是100个。

总结

# 获取OPS信息
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --number-of-queries=100000 -c 30 -i 10 --create-schema=my_test -a 
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep Questions
# 获取TPS信息
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --number-of-queries=100000 -c 30 -i 10 --create-schema=my_test -a 
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep -E "Com_select|Com_update"

3. 自定义脚本获取性能指标

这里就不延伸了...

TPCC测试

专业名词

  • TPC : 全称[T]ransaction [Processing] Performance [C]ouncil,是一家非盈利组织,该组织制定各种商业应用的基准测试规范,任意厂商或个人,都可以按照其规范来开发自己的应用程序
  • TPC-C : 由TPC退出的一套基准测试程序,主要用于联机事务类应用的测试,最后那个字母C仅是序号,在他之后还有TPC-D,TPC-R,TPC-W,不过也废弃了。TPC-C这个关键不是完全因为他没有被废弃,这只是一部分原因,最主要的原因是TPCC是套基准,不管是有了新硬件,还是软件出了新版本,为显示出新产品的优势,总得拿出些数值做对比,TPCC就是数值之一,各大厂商都着力在吹。TPC-C针对联机事务类应用和决策支持类应用(或称数据仓库)另有一套标准,就是TPC-H。
  • TPCC-MYSQL : 由Percona基于TPCC规范开发的一套mysql基准测试程序,也就是本书的主角,这里先不多作介绍,因为本节剩下的篇幅全是介绍他。

"高性能MySQL"的作者之一Vadim Tkachenko,在该书的第二章提到过一款名为dbt2的TPC-C测试工具,书中也提供了使用该工具的例子,可能是作者自己都觉得该工具不大好使,因此后台作者就开发了我们将要提到的主角 : tpcc-mysql

tpcc-mysql

安装

代码可以从github下载

cd tpcc-mysql-master/src
make

如果没有报错的话,就是安装好了,安装好了之后,tpcc-mysql-master下会生成两个可执行文件

  • tpcc_load : 用于初始化数据
  • tpcc_start : 用于执行基准测试

使用

1. 创建测试数据库
mysqladmin -h 127.0.0.1 -P 3306 -u root -pZj123$%^ create tpcc 
2. 导入数据
mysql -h 127.0.0.1 -P 3306 -u root -pZj123$%^ tpcc < create_table.sql
3. 倒入索引
mysql -h 127.0.0.1 -P 3306 -u root -pZj123$%^ tpcc < add_fkey_idx.sql
4. 初始化数据

语法 : pcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -l part -m min_wh -n max_wh * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS

tpcc_load -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10
5. 测试

语法 : tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file
与连接相关的参数都比较好懂,这里介绍下从-r开始的的参数

  • -r warmup_time : 指定预热时间,以秒为单位,默认是10秒,主要母的是为了将数据加载到内存
  • -l running_time : 指定测试执行的时间,以秒为单位,默认是20秒
  • -i report_interval : 指定生成报告的间隔时间
  • -f report_file : 将测试中各项操作的记录输出到指定文件内保存
  • -t trx_file : 输出更详细的操作信息到指定文件内保存

命令如下

# 开始压测指定仓库10个,并发10个,预热10s,持续300s,每隔10s生成报告,并生成日志文件tpcc_mysql.log:
./tpcc_start -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10 -c 10 -r 100 -l 300 -f tpcc_mysql.log -t tpcc_mysql.rtx

返回结果

***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '127.0.0.1'
option P with value '3306'
option u with value 'root'
option p with value 'Zj123$%^'
option d with value 'tpcc'
option w with value '10'
option c with value '10'
option r with value '100'
option l with value '300'
option f with value 'tpcc_mysql.log'
option t with value 'tpcc_mysql.rtx'
<Parameters>
     [server]: 127.0.0.1
     [port]: 3306
     [DBname]: tpcc
       [user]: root
       [pass]: Zj123$%^
  [warehouse]: 10
 [connection]: 10
     [rampup]: 100 (sec.) --预热时长
    [measure]: 300 (sec.) --测试时长

RAMP-UP TIME.(100 sec.) --预热结束

MEASURING START. --测试开始

  10, trx: 28, 95%: 3776.729, 99%: 5182.355, max_rt: 7222.578, 28|4839.867, 2|2649.252, 2|8888.263, 4|28777.471
  20, trx: 21, 95%: 4728.754, 99%: 5173.055, max_rt: 5173.142, 19|2625.231, 3|768.530, 1|5638.004, 1|13718.509
...

STOPPING THREADS.......... --结束压测

<Raw Results> --第一次统计结果
  [0] sc:0 lt:1557  rt:0  fl:0 avg_rt: 1063.3 (5) --new-order,新订单业务成功次数(success),延迟次数(late),重试次数(retry),失败次数(failure),平均重试次数
  [1] sc:36 lt:1521  rt:0  fl:0 avg_rt: 433.4 (5) --payment,支付业务统计
  [2] sc:11 lt:145  rt:0  fl:0 avg_rt: 532.8 (5) --order-status,订单状态业务统计
  [3] sc:0 lt:153  rt:0  fl:0 avg_rt: 3633.0 (80) --delivery 发货业务统计
  [4] sc:0 lt:158  rt:0  fl:0 avg_rt: 6656.3 (20) --stock-level库存业务统计
 in 300 sec.

<Raw Results2(sum ver.)>    --第二次测试结果
  [0] sc:0  lt:1557  rt:0  fl:0 
  [1] sc:36  lt:1521  rt:0  fl:0 
  [2] sc:11  lt:145  rt:0  fl:0 
  [3] sc:0  lt:153  rt:0  fl:0 
  [4] sc:0  lt:158  rt:0  fl:0 

<Constraint Check> (all must be [OK])  --所有的都必须OK才行
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK] --支付成功次数,结果大于43%,为OK,结果小于43%,为NG;
   Order-Status: 4.36% (>= 4.0%) [OK] --订单状态 
       Delivery: 4.27% (>= 4.0%) [OK] --发货状态
    Stock-Level: 4.41% (>= 4.0%) [OK] --库存状态
 [response time (at least 90% passed)] --响应耗时指标必须超过90%通过才行
      New-Order: 0.00%  [NG] * --表示not good
        Payment: 2.31%  [NG] *
   Order-Status: 7.05%  [NG] *
       Delivery: 0.00%  [NG] *
    Stock-Level: 0.00%  [NG] *

<TpmC>
                 311.400 TpmC --TpmC结果值(每分钟事务数,该值是第一次统计结果中的新订单事务数除以总耗时分钟数,例如本例中是:1557/5=311.4),TpmC/60=tps

解释

10, trx: 28, 95%: 3776.729, 99%: 5182.355, max_rt: 7222.578, 28|4839.867, 2|2649.252, 2|8888.263, 4|28777.471
20, trx: 21, 95%: 4728.754, 99%: 5173.055, max_rt: 5173.142, 19|2625.231, 3|768.530, 1|5638.004, 1|13718.509

第一项为我们定义的任务执行时间,他是按照每10秒为一个区间进行输出。后面分别为在10s内新订单事物数,每个给定区间的新订单事务的95%响应时间,每个给定区间的新订单事务的99%响应时间,新订单事物最大响应的时间,其他事物数和最大响应时间。

所以上面10的一列为
164 10秒内执行了28次事务
95% 事务平均响应时间为3776.729秒
99% 事务平均响应时间为5182.355秒
max_rt 事务最长响应时间为7222.578秒

TPCC没有收集系统性能指标,需要通过其他途径获取期间的系统性能指标,重点关注CPU和系统平均负载。

数据库参数配置优化

连接相关参数

1. max_connections : 指定MySQL服务端最大并发连接数,范围为1~10万,默认值为151。

这个参数非常重要,因为它决定了同时最多能有多少个会话链接到MySQL服务;其次这个参数很有意思,改参数的默认值随着版本的不同一致在变来变去,单总体值的范围都比较保守。我们可以忽略默认值,直接手动指定参数值。设定该参数时,根据数据库服务器的配置和性能,一般设置在500~2000之间都没有太大的问题。

2. max_connect_error : 指定允许连接不成功的最大尝试次数,值的范围从1~2^64(32位的平台最大为2^32),在5.6.6版本默认值是100,在之前的版本中,默认值为10。

mysql 学习记录06-MySQL命令行工具

MySQL的命令行工具不少,从大体上可以分为服务端和客户端两类。服务端工具包括mysql_install_db,mysqld_safe,mysqld以及mysqld_mutli,数量不多,但是非常重要。客户端工具有五花八门了。

mysql_install_db--MySQL建库工具

这个命令用来初始化与MySQL数据库系统表相关的物理文件。所创建的文件如果没有特别别指定,都会保存在data目录下,所以对改命令而言,指定data路径的--datadir就是最重要的参数之一。

需要注意这个命令不是在bin目录下,而是在script目录下

# datadir用于指定生成的文件的存放路径
# basedir用于指定mysql的目录,有可能会用mysql相关的命令
mysql_install_db --datadir=/data/mysqldata/3306/data --basedir=/usr/local/mysql

mysqld_safe--MySQL启动工具

mysqld_safe用于启动MySQL服务,在启动前会做安全检查,如果发现目前已经有MySQL服务在运行会抛出错误信息。
可以使用--defaults-file指定配置文件

mysqld_safe --defaults-file=/etc/my.cnf &

注意在命令行最后加了&符号,改符号的功能是将执行的命令行进程放到后台执行。

mysqld--MySQL主进程

真正控制MySQL服务的进程就是mysqld了
可以使用mysqld --verbose --help查看配置
如果想要知道怎么用mysqld启动数据库,可以执行ps -ef| grep mysql

[[email protected] plugin]# ps -ef| grep mysql
root       4257      1  0 Nov18 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql      4523   4257  0 Nov18 pts/0    00:00:26 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

每个MySQL服务对应一个端口,每个端口对应一个mysqld进程

作为MySQL数据库中最重要的关键进程之一,除了拥有众多系统参数(MySQL服务的启动时参数),还拥有相当多的系统变量(MySQL服务的运行时参数),以及一系列的系统变量(记录MySQL服务的运行时状态)。

简单来说系统参数就是命令行选项,在执行命令时指定,用以控制时间不同功能或设定。

系统变量可以使用show [global] variables;查看,绝大多数的系统参数都有对应的同名系统变量,只不过系统参数是在服务启动时指定,系统变量则可以在服务运行时修改。

注意系统变量有作用域的概念,分为全局(GLOBAL)和会话(SESSION)两类。可以用set global variables_name = 1;

需要注意不管是全局还是局部变量,在服务重启后都会失效,所以记得要把修改的配置加到配置文件中。

状态变量就是记录MySQL服务运行时的系统状态。状态变量也有作用域,分为全局(GLOBAL)和会话(SESSION)两类,前者是记录整个MySQL服务的状态,而后者只代表当前会话的状态。可以使用show [global] status;查看。

mysqld_multi--MySQL多实例管理工具

这个命令可以同时管理多个mysqld实例,改命令的格式如下

mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]
or
mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

mysqld_multi会扫描my.cnf文件中的区块名(即组名),即[mysqldN]。我们知道默认配置文件中mysqld区块的配置是没有后面的N的,但是如果希望通过mysqd_multi来管理MySQL,就需要配置个N了。这个N既起到唯一的作用,又能标识mysqld进程。具体怎么配置可以使用mysqld_multi --example查看

配置步骤

1. 初始化数据

注意执行之后会随机生成一个[email protected]的密码,记得保存下来

# 5.7后用mysql_install_db看不到随机生成的密码
mysqld --initialize --user=mysql --datadir=/var/lib/mysql3307

2. 修改配置文件my.cnf

[mysqld1]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
federated
# 设置server id
server_id = 888888
# # 设置binlog存放路径
log-bin = mysql-bin
#log-bin-index = master-bin.index

[mysqld2]
mysqld     = /usr/sbin/mysqld
#ledir      = /path/to/mysqld-binary/
mysqladmin = /usr/bin//mysqladmin
socket=/var/lib/mysql/mysql3307.sock
port       = 3307
datadir=/var/lib/mysql3307
symbolic-links=0
log-error=/var/log/mysqld3307.log
pid-file=/var/run/mysqld/mysqld.3307pid
character-set-server=utf8
collation-server=utf8_general_ci
user=mysql
federated

4. 服务管理相关命令

# 开启mysql2服务
mysqld_multi start 2
# 开启mysql6,mysql7,mysql8服务
mysqld_multi start 6-8
# 查看服务是否启动
mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is running
# 关闭服务,注意关闭命令必须输入用户和密码才能生效
mysqld_multi stop 2 -user=root -password=Zj123$%^

mysql--专业命令行工具

相关参数

1. --auto-rehash

自动补全功能,类似linux命令行下的tab键自动补全命令。注意,注意启用此参数后,登录mysql的时间可能会有一定的延长,因为需要加载相关的字典信息,才能实现自动补全

2. --default-character-set

用于指定连接会话的字符集,相当于在连接到MySQL后,执行SET NAMES设置当前会话字符集

3. -e,--execute

mysql命令支持两种操作方式:交互模式和非交互模式。常规应用都是进入到交互模式下,而后执行MySQL的各种DML/DDL/DCL语句,或其他语句。有时我们不需要进入命令行模式,而是在执行mysql命令时直接执行要执行的语句,这种就可以用-e实现,如下

[[email protected] mysql3307]# mysql -h 127.0.0.1 -P 3307 -u root -p123456 -e "show global variables like '%master%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| master_info_repository | FILE  |
| master_verify_checksum | OFF   |
| sync_master_info       | 10000 |
+------------------------+-------+
4. -f,--force

通过mysql命令批量执行sql语句(或者执行一个包括SQL语句的文件,总之是在非交互模式)时,如果要执行的某条sql有错误,那么默认情况下该条语句后面的语句就都不会执行。这个参数的作用在于,即使前面有sql语句由错误,也不会影响后面语句的执行。

5. --show-warnings

执行完语句之后,马上显示警告信息,相当于执行完SQL语句之后再自动执行SHOW WARNING语句

相关技巧

mysql是个命令行工具,进入到交互模式的命令行后,它不仅具有执行SQL语句(各类DML/DDL/DCL)的本领,自己提供了若干命令,这些命令主旨是帮助DBA更好的执行SQL语句。
可以使用help命令查看相关的命令,说几个比较常用的

1. pager

可以视作mysql命令行模式中的管道符,如下

# 设置,pager后面接的是系统命令
pager more
# 查询看看效果吧
select * from member limit 100
# 取消
nopager
2. prompt

可以用于指定mysql命令行的提示符,可以显示当前的数据库,用户等信息,具体的可以百度,可以配置直接叫到配置文件中

3. rehash

自动补全

4. Tee

指定内容输出到外部文件,如下

# 开始
tee /tmp/mysql_tee.txt
# 执行sql
select * from mysql.user;
#结束
notee

mysqladmin--管理工具

通过该工具可以完成检查服务器配置,当前状态,创建/删除数据库等操作。
命令格式如下

mysqladmin [OPTIONS] command command ...

参数

参数很多,记录几个比较特别的参数

-i,--sleep

间隔指定时间后,再次重复调用本mysqladmin命令

-r,--relative

当与-i参数联合使用并且指定了extends-status命令时,显示本次与上次之间个状态值的差异

命令

create [dbname]

创建数据库

drop [dbname]

删除数据库

extended-status

查看服务器状态信息,跟show global variables一样

flush-hosts

刷新缓存信息

flush-logs

刷新日志

flush-status

充值状态变量

flush-tables

刷新所有表

flush-thread

刷新线程缓存

flush-privileges

重新加载授权表,与reload完全相同

reload

与上面的命令相同

refesh

刷新所有表,并切换日志文件

password [new-password]

修改指定用户的密码,只是按照旧的格式修改

ping

通过ping的方式,检查当前MySQL服务是否仍能正常提供服务

debug

输出当前MySQL服务的调试信息到error_log文件中,某些情况下性能分析或故障排查非常实用

kill id,id,...

杀掉连接至MySQL服务的进程,与KILL id功能相同

processlist

查看当前MySQL服务所有的链接线程信息,功能完全等同于SHOW PROCESSLIST语句

shutdown

关闭服务

status

查看当前MySQL的状态,功能与mysql命令行模式下的status类型,这个命令相当于只显示mysql命令行模式下status命令的最后一行信息,能够获取较为关键的几项指标。
status命令返回的信息包含

Uptime : MySQL服务的启动时间
Thread : 当前链接的会话数
Questions : 自MySQL服务为启动后,执行的查询语句数量
Slow Queries : 慢查询语句的数量
Opens : 当前处于打开状态的表对象的数量
Flush tables : 执行过flush,refresh和reload命令的数量
Open tables : 当前会话打开的表对象的数量
Queries per second avg : 查询的执行频率
start-slave

启动slave服务

stop-slave

停止slave服务

variable

显示系统变量,与show global variables相同

version

查看版本信息,同时还包含status命令的信息

其他常用命令

myisamchk

用于MyISAM表对象的检查和修复

myisam_ftdump

输出MyISAM全文索引

innochecksum

计算InnoDB数据文的checksum

mysqldumpslow

分析慢查询日志

mysqlbug

向MySQL官方输出bug

其他工具

就不说了...
phpMyAdmin,MySQL Workbench,Navcat之类。。。

mysql 学习记录05-复制特性

概述

MySQL的复制特性很灵活,可以实现整个服务(all database)级别,单个数据库级别,数据库中某些表对象的复制,即能从A复制到B(主从单项复制),B再复制到C,也可以实现A直接复制到B和C(单主多从复制),甚至A的数据库复制给B,B的数据也复制会A(双主复制)。

一般来说MySQL的复制特性一版用于以下场景

  • 提高性能。通过一主多从(甚至多主多从)的部署方案,将涉及数据写的操作放在Master端操作,而将数据读的操作分散到众多的slave端。这样一方面能够降低Master负载,提高数据写入请求的响应效率;另一方面众多的Slave节点同时提供读操作,有一个负载均衡的效果
  • 数据安全。由于数据被复制到Slave节点,即使Master节点宕机,Slave节点还保存着一份数据,这相当于实现了数据的冗余;在日常工作中可以将备份任务放在Slave端执行,以避免执行备份操作时对Master产生影响
  • 数据分析。将数据分析和挖掘等较占资源的操作放在Slave节点执行,这样就可以降低对Master节点可能造成的性能影响
  • 数据分布。基于MySQL复制特性的实现原理,Master和Slave并不需要实时链接,因此可以将Slave和Master放在不同的物理位置,而基本不用担心网络中断等因素对同步造成影响

MySQ复制的过程默认是异步的,也就是说Master端的修改不会马上同步到Slave端,另一方面,Slave不需要时刻都链接Master,只有需要同步数据时,才连接到Master。只要Master节点依然保留了自上次同步后所生成的二进制日志,就可以继续同步。

对于某些场景,这种方式就不一定适用了。如果说对于读写并发和实时性要求都很高的场景,用户可能希望插入的数据库,能够立刻就被查询出来,如果查询是从Slave端获取数据,那么异步的数据同步方案就满足不了需求。针对这种情况,MySQL复制在处理数据时,有两种不同的模式:

  • 基于语句复制([S]tatement [B]ased [R]eplication) : 基于实际执行的SQL语句的模式方案,简称SBR
  • 基于记录复制([R]wo [B]ased [R]eplication) : 基于修改的列的复制模式,简称RBR
  • 基于上述两种方式的混合的复制模式(Mix Based Replication, MBR)

数据复制模式跟日志文件记录格式强相关

创建复制环境

1. 禁止主库写入

两种方式,如下

  1. 可以关闭MySQL服务
/etc/init.d/mysqld stop
  1. 锁定表
flush tables with read lock;

2. 复制数据文件

随便找方式复制即可

3. 设置Master Server ID并启动Master(如果已经设置过可以跳过这一步)

  1. 修改Master配置文件
[mysqld]
# 设置server id
server_id = 888888
# 设置binlog存放路径
log-bin = mysql-bin

4. 获得Master当前的binlog文件和Position(用户后面设置Slave)

# 查看Master信息用于之后配置Slave
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5. 恢复Master的正常服务

如果Master服务停止了,要启动Master

/etc/init.d/mysqld start

如果Master是锁定的,需要释放锁

unlock tables;

6. 创建复制专用用户

在MySQL中,Slave如果想要获取二进制日志,需要主动链接Master节点,创建一个用户专门用于Slave从Master同步数据,更安全和方便管理。

# 创建一个用户运行从192.168.23网段链接服务器,拥有读取二进制日志的权限(如果已经设置过可以跳过这一步)
grant replication slave on *.* to [email protected]'192.168.23.%' identified by '[email protected]#';

7. 配置Slave

  1. 修改Slave配置文件
[mysqld]
# 设置server id
server_id = 666666
# 设置binlog存放路径,Slave可以不配置这一项
# log-bin = mysql-bin
  1. 删除auto.cnf文件
    这个文件里保存了server-uuid的参数,他拥有唯一标识MySQL服务,如果Slave端是直接复制的Master端的文件,需要把这个文件删除掉,避免和Master相同,MySQL在启动时会自动生成一个
/etc/init.d/mysqld start

8. 启动Slave

/etc/init.d/mysqld start

9. 在Slave上配置Master的连接信息

# 配置连接信息,在第四步中用`show master status`
change master to master_host = '192.168.23.8', master_port = 3306, master_user = 'repl', master_password = '[email protected]#', master_log_file = 'mysql-bin.000002', master_log_pos = 154;
# 启动slave
start slave

10. 测试复制是否配置成功

  1. 做变更数据的操作看数据是否能同步
  2. 如果没有同步,用show slave status查看错误信息

复制环境常用管理命令

1. 检查Slave节点的状态

show slave status

参数

下面罗列一些比较重要的参数

  • Slave_IO_State : 显示Slave的当前状态
  • Slave_IO_Running : 显示I/O线程是否在运行,正常情况下应该在运行,除非DBA手动将其停止,或者出现错误
  • Slave_IO_Running : 显示SQL线程是否在运行,正常情况下应该在运行,除非DBA手动将其停止,或者出现错误
  • Last_IO_Erroe/Last_SQL_Error : 正常情况下应该是控制,如果遇到了错误,那么这里会输出错误信息,根据错误信息可以找到相应的错误
  • Seconds_Behind_Master : 显示房钱Slave节点与Matser节点的同步延迟。注意,这个参数有一定的迷惑性,表现在两方面。第一,看起来像是时间,而且确实有时间因素,但其实不是时间。其实是Slave节点接收到的Master的日志文件和已经应用的日志文件之间的差距。比如I/O线程接收到的日志文件写入位置为34560,而SQL线程才应用到34000,这两个位置之间时间上的差距是一小时,那么Seconds_Behind_Master就会显示3600秒。而有可能此时Master已经写到34660的位置了,只是还没有被I/O线程读取到Slave几点。这个参数显示的值不是完全精确的主从之间的延迟时间,而只是Slave节点本地日志接收和应用上的差异。
  • Master_Log_File/Read_Master_Log_Pos : 显示当前读取的Matser节点二进制日志文件和文件位置。
  • Relay_Master_Log_File/Exec_Master_Log_Pos : 显示当前Slave节点正在应用的日志文件位置。
  • Relay_Log_File/Relay_Log_Pos : 显示当前Slave节点正在处理的中继日志文件和位置

show slave status是最为直接简单全面的查询Slave节点状态的方法,此外show processlist也能在一定程度上起到复制了解复制状态的作用

2. 启动停止slave线程

出现意外情况,可以手动控制Slave界定啊的启动和停止,主要通过stop slavestart slave实现
Slave服务由两个线程组成

  • IO_THREAD : 负责读取Master端的二进制日志,并写入到本地的中继日志(relay-log)中
  • SQL_THREAD : 负责从本地中继日志中读取时间并执行

这是两个独立的线程,他们的启动和停止,也可以分开进行,相互之间没有依赖性。
灵活的利用这个两个线程的启停,可以使复制的应用场景更加灵活。如果在备份时,为了保持备份数据的一致性,很多人往往选择停止整个Slave服务,单仔细一想,IO_THREAD并不需要停止,特可以继续从Master读取二进制日志保存在在本地的中继日志中,这样还可以起到对Master节点数据冗余保护的作用。我们只需要停止SQL_THREAD,记录下当前应用到的日志文件名和位置,就可以开始备份任务。因为此时Slave不会有数据更新,相当于处于只读状态,这样创建出来的就是一致性的备份了。当然数据再备份期间与Master是不同步的(如果这期间Master有数据写入的话)。

怎么单独启停IO_THREAD/SQL_THREAD呢?只需要在语句后面机上要操作的线程名就行了,如下

start slave sql_thread;
stop slave sql_thread

复制特性的实施原理和关键因素

复制特性的核心就是基于Master节点对数据库中各项变更的处理机制,MySQL数据库启用二进制日志后,该文件中就会记录MySQL服务自启动以来,所有对数据库结构或内容(数据)的变更事件(即增删改操作),当然select语句由于不会触发结构上变更所以select行为不会被记录。

Slave节点只要连接到Master节点,请求这些二进制日志。拿到二进制日志后,Slave节点就能解析这些二进制日志,并在本地执行,这样就相当于将修改操作在Slave节点重演。日志应用完之后,Slave就和Master一样了

复制特性就是基于二进制日志

复制格式

二进制日志在记录事件时,支持多种格式,由binlog_format参数控制

  • 基于语句记录(Statement-Based Logging, SBL),对应的参数值为statement
  • 基于行格式记录(Row-Based Logging,RBL),对应的参数为row
  • 混合模式记录(Mixed-Based Logging,MBL),对应的参数为mixed

复制特性中,也有一个被称为复制格式的(Replication Formats)的概念,复制格式和二进制日志格式其实是同一个东西,不过是有两个名字,也就是不同场景下的称谓是不同的。

考虑到复制格式和二进制日志格式之间的关系,其格式的种类和逻辑上,目前也分为3中

  • 基于语句复制(Statement-Based Replication,SBR)。在MySQL中的复制功能,最初就是靠传播SQL语句到目标端执行的,这就是所谓的基于语句复制,也就是说二进制日志中保存的就是SQL语句。
  • 基于行复制(Row-Based Replication,RBR)。后来(5.1.4),MySQL又引入了全新的基于行粒度的日志格式,这种模式下,二进制日志文件写入事件时,记录的是变更的记录行的信息
  • 混合记录模式(Mixed-Based Replication)。再后来(5.1.8),MySQL又进了一步,在记录事件到二进制日志时,MySQL服务根据需要,动态修改日志格式,就是所谓的混合模式。注意,混合记录是中模式,而不是一种格式。在这种模式下,默认还是选择基于行的格式记录。具体选择的是什么格式,要看当前执行的语句,以及操作对象所使用的存储引擎而定。

在服务运行期间可以对服务进行修改,但是注意最好不要随便改,因为有可能会导致复制环境报错

SBR与RBR的特点及优势

SBR的优点
  • 技术成熟,自3.23版本即开始提供对这种记录格式的支持
  • 生成日志少,特备是对于大量更新及删除的操作
  • 由于能够记录下数据库做过的所有变更操作,日志可用于行为审计
SBR的缺点
  • 存在安全隐患。这个安全并不是说会被攻击,而是说数据安全。Master节点中产生的修改操作(INSERT,DELETE,UPDATE,REPLACE),并不是都能通过基于语句方式完整的复制到Slave节点,对于不去确定的行为在基于语句复制时,很难确保Slave节点会执行并获得正确的数据,这点从逻辑上证明了主从出现不一致的合理性。比如说Master节点和Slave节点分别执行FOUND_ROWS(),SYSDATE(),UUID()这类函数,可能返回不同的结果;如果使用了这些函数,那么语句执行时会抛出下列警告信息(客户端使用show warnings查看)
Statement is not safe to log in statement format
  • 执行INSERT ... SELECT语句时需要更多的行锁(相对RBR而言)
  • UPDATE要扫表(无索引可用的情况下)时需要持有更多的行锁(相对RBR而言)
  • 对于InnoDB引擎,INSERT语句使用AUTO_INCREMENT会阻塞其他INSERT语句
  • 对于复杂的语句,Slave节点执行时语句必须先被评估,而对于基于row格式复制,则Slave节点只需要修改具体的记录即可(不必执行跟Master端相同的SQL语句,这既是有点也是缺点)
  • 如果语句在Slave节点执行时操作失败,基于statement格式复制会增加主从不一致的概率
  • 单条语句中执行的函数中调用NOW()返回日志相同,但是存储过程就不一定了
  • 对象定义必须拥有唯一键,主要是为了避免冲突
RBR的优点
  • 所有修改都能被安全的复制到Slave节点
  • 5.1.14版本开始,mysql数据库不再被复制,RDB方式的复制不能支持mysql库中的表对象。对于像GRANT/REVOKE这类操作,以及trigger/stored procedures/views等对象的维护操作,会被使用SBR方式复制到Slave
  • 对于CREATE TABLE ... SELECT这类DDL+DML的操作,CREATE创建对象部分使用SBR模式复制,其他部分则使用RBR模式复制
  • 与RDBMS实现的技术类似,其他数据库软件管理和维护方面的经验也可以继承使用
  • Master端执行修改操作时,仅需持有极少的锁,因此可以获得更高的并发性能
  • Slave节点执行INSERT/UPDATE/DELETE时也仅需持有少量锁
RBR的缺点
  • RBR可能会生成更多的日志。比如DML语句,基于statement格式记录日志的话,只需记录所执行的SQL语句,相比之下,基于row格式记录日志,会记录所有变化了的行到二进制日志文件,如果语句触发的记录变更特别多,那么生成的二进制日志自然非常多,即使执行的操作随后被回滚。这也意味着创建备份和回复需要更多时间,以及二进制日志会被更长时间加锁以写数据,也可能带来额外的并发性能上的文件。
  • UDFS生成BLOB值需要花费比基于statement格式日志更长的时间,这是因为BLOB值是被记录的,而不是语句生成的
  • 不能通过分析日志来获取曾经执行过的语句。不过还是可以通过mysqlbinlog命令看有哪些数据被修改了
  • 对于非事务引擎,比如MyISAM表对象,Slave节点应用INSERT操作时,使用RBR模式要比使用SBR模式持有更强的锁定,也就是说使用RBR模式在Slave上并没有并行插入的概念

中继日志文件和状态文件

IO_THREAD将接受到二进制日志文件保存在中继日志文件中。中继日志用来保存其他及诶点的事件(也是二进制格式),可以使用如下参数配置

relay-log = relay-bin
relay-log-index = relay-bin-index

Slave节点会在满足下列条件时,触发生成新的中继文件,并更新相关的索引文件

  • 启动Slaves节点I/O线程时
  • 执行日志刷新命令,如FLUSH LOGS或mysqladmin flush-logs等
  • 中继文件达到指定的最大值有如下两种情况

    1. 如果max_relay_log_size参数值大于0,则日志文件超过该值后即会重建。
    2. 如果max_relay_log_size参数值为0,则通过max_binlog_size确定单个Relay日志的最大值

中继日志文件的维护可以交由SQL_THREAD来处理,他会自动删除无用的中继日志文件

除了中继日志文件外,复制环境中Slave节点还会创建两个复制环境的状态文件,即master.info和relay-log.info,这两个文件都默认保存在mysql的data目录下,也可以通过--master-info-file和--relay-log-info-file参数来自定义文件的名称和路径

  • master.info : 用于保存复制环境中连接Master节点的配置信息,比如用户名,端口,IP,密码等,5.6之后也可以选择将这些信息保存到mysql.slave_master_info表对象
  • relay-log-info : 保存处理进度及中继日志文件的位置,与前面的配置类型5.6之前保存在data路径下的relay-log-info文件中,5.6之后可以将这些信息保存到mysql.slave_relay_log_info表对象中

注意不要手动去修改这两个文件,不然可能会出现无法预知的错误。

复制过滤规则

注意,就算设置了过滤规则,Slave也会接受所有的二进制日志,等到应用时再过滤。

MySQL提供了一系列以--replicate-*开头的参数,用于复制环境中Slave节点定义过滤规则

replicate-rewrite-db=from_name->to_name用于将A库的对象保存到B库中

1. 库级过滤规则

--replicate-do-db--replicate-ignore-db用于控制是否应用指定数据库的变更事件,注意这两个参数执行,并不是过滤指定数据的操作,而是过滤当前默认数据(USE)所做的操作,如下
Slave设置了--replicate-ignore-db = a
在master上执行

use a;
drop table table1;
drop table b.table2;

上面的操作,Salve设置了不引用数据库a的事件,SQL操作中因为默认数据库是a,所以对table2的操作会被过滤掉,因为默认数据库为a(使用use a;设置了默认数据库)

--replicate-do-db某些情况也可以用于忽略其他数据库。例如,当指定了--replicate-do-db=sales,那么所有不在默认数据(use sales)下发生的变更,都不会记录日志

2. 表级复制选项

跟传统粒度越细,优化级别越高的规则不同,MySQL复制特性中的过滤规则,是先检查数据库级别的设置,当数据库级参数无有效匹配时,Slave节点才检查并评估表级过滤参数。

首先,作为一个准备工序,Slave节点要检查是否是基于语句复制(SBR),如果是的话,并且语句是在存储过程中触发,那么Slaves节点执行语句并退出,如果基于RBR的话,Slave节点并不知道Master端执行的语句做了什么,因此这种情况下什么都不会应用。

基于SBR场景,复制事件以语句形式体现(所有的修改时间都可以定位为SQL语句),而基于RBR,所有的事件均以具体修改的表的列的数据体现(即使一条简单的update tbl set col = 1也可能触发大量的row修改事件)

如果没有指定任何表级过滤规则,那么检查到这一步时,Slave节点就直接执行所有修改事件了,这是默认场景时的表现。这是默认场景时的表现。如果指定了--replicate-do-table--replicate-wild-do-table参数,则只执行参数中指定对象的修改事件;如果指定了--replicate-ignore-table--replicate-wild-ignore-table参数,则除了参数中指定对象的修改事件不执行外,其他变更事件均要在本地执行。

对于--replicate-*这类参数,如果需要指定多个对象。

不同场景下复制过滤规则的应用

条件结果
没有任何--replicate-*参数Slave执行所有接收到的时间
指定了--replicate-*-db参数,未指定表级参数只执行(或忽略)指定数据库的事件
指定了--replicate-*-table参数,未指定库级参数只执行(或忽略)指定表对象的事件
既有库级参数,也有表级参数Slave节点首先执行(或忽略)指定数据的事件,而后再处理表级过滤过滤选项。需要注意日志记录格式对复制的影响

使用XtraBackup

。。。

半同步复制环境

在半同步机制下,Master节点只要确认有至少一个Slave节点接收到了事务,即可向发起请求的客户端返回操作成功信息,Master节点甚至不需要等待Slave节点也成功执行完这个事务,只要至少有一个Slave节点接收到这个事务,并且将之成功写入到本地中继日志文件,就算成功。应该是因为相比同步机制,工作只完成了一半左右,所以就叫半同步。

相比异步复制,半同步数据在数据完整性方面有显著提升。每个成功提交的事务,都代表这份数据至少存在两个节点上。

1. 配置半同步复制环境

找到插件目录

1. 找到插件目录
mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
2. 查看路径
[[email protected] plugin]# ls | grep sem
semisync_master.so
semisync_slave.so
3. 安装插件
# 在master安装
install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
# 在slave安装
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
4. 插件设置

设置master

set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_master_timeout = 3000;

设置slave

set global rpl_semi_sync_slave_enabled = 1;
配置项说明
  • rpl_semi_sync_master_enabled : 用于控制是否在Master节点启用半同步复制,默认值为1即启用状态
  • rpl_semi_sync_master_timeout : 用于指定Master节点等待Slave响应的时间,单位是毫秒,默认是10000即10秒钟,这里设置为3秒。若超出指定时间Slave节点仍无响应,那么当前复制环境就临时被转换为异步复制。
  • rpl_semi_sync_slave_enabled : 跟第一个参数看起来很像,唯一的区别,他是用来控制Slave节点是否启用半同步复制
  • 前面配置的3个变量尽管可以动态修改,但强烈建议将所有配置的变量,都保存在初始化参数文件中,否则需要在每次启动MySQL服务时再手动进行配置
  • rpl_semi_sync_*开头的变量还有几个,这里只配置了最重要的3项,更多参数可以参考官方文档中的内容。
5. 重启Slave节点的IO_THREAD线程

这一步时为了让Slave节点重新连接Master节点,注册成半同步Slave身份,如果不重启IO_THREAD,那么Slave就会一直保持异步复制模式。

stop SLAVE IO_THREAD;
start SLAVE IO_THREAD;

监控半同步环境

可以使用show status查看状态变量,安装半同步插件后,MySQL中就会多出若干个以Rpl_semi_sync_*开头的状态变量,其中在Slave节点,与半同步复制相关的状态变量只有一项:Rpl_semi_sync_slave_status : 标识当前Slave是否启用了半同步模式。
Master节点中与半同步复制相关的状态变量要多一些,其中最值得关注的有下列几项:

  • Rol_semi_sync_master_clients : 显示当前处理半同步模式的Slave节点数量
  • Rol_semi_sync_master_status : 标识当前Master节点是否启用了半同步模式
  • Rol_semi_sync_master_no_tx : 当前未成功发送到Slave的事务数量
  • Rol_semi_sync_master_yes_tx : 当前成功发送到Slave的事务数量

语句如下

show variables like 'rpl_semi_sync%'

复制环境中的故障切换

...

延迟复制

MySQL数据库的复制环境,可以通过配置,实现Slave节点的延迟复制,就是说,将Slave节点与Master节点保持指定时间的间隔。这个实现起来比较简单,只需要指定Slave节点中的MASTER_DELAY,通过CHANGE MASTER TO语句即可进行设定,语法如下:

CHANGE MASTER TO MASTER_DELAY = n;

单位为秒

设置完之后,执行START SLAVE就可以使之生效,而无需重启MySQL。这样设置之后,Slave节点接收到Master的节点生成的日志之后,不会马上应用,而是等待,知道时间符合设定的延迟条件后才开始使用。

延迟复制一版用于以下场景

  • 若Master节点出现误改,误删等操作,造成数据都是的情况,由于Slaves节点有延迟因素的存在,那么DBA可以通过Slave节点仍然保存的数据,快速地将数据恢复回去。不过延迟时间不会太长如果发现误删操作,而且Slave节点恰好尚未应用这些事件,那就必须争分夺秒恢复才行
  • 测试复制环境出现延迟时,对系统可能造成的影响。
  • 无需通过恢复M就可以查看之前版本的数据库,某些场景下,这也能简化DBA的工作

可以用SHOW SLAVE STATUS查看Slaves节点复制信息时,有3个列值与此有关

  • SQL_Delay : 显示当前设定的延迟时间,以秒为单位。
  • SQL_Remaining_Delay : 当Slave_SQL_Running_State列的状态是"Waiting until MASTER_DELAY seconds after master executed event"时,本列显示的值就是距离延迟阈值的时间,换个说法就是还有多长时间才能开始应用,否则的话本列值应该是NULL
  • SQL_SQL_Running_State : 显示当前SQL_THREAD的状态

当SQL_THREAD处于延迟等待阶段,SHOW PROCESSLIST显示该进程的状态时,将会显示为"Waiting until MASTER_DELAY seconds after master exexuted event"。这些信息都说明,当前环境配置了延迟复制

mysql 学习记录04-数据库文件结构

初始化选项文件

默认文件位置

Windows

  1. WINDIRmy.ini : WINDIR指的是Windows的目录,一版是C:WINDOWS,可以通过echo $WINDIR$查看该变量的实际值
  2. 系统盘的根目录保存的文件,即`C:my.ini
  3. INSTALLDIRmy.ini : INSTALLDIR指的是MySQL安装目录,一版是在C:Program FilesFilesMySQLMySQL 5.6 Server目录

UNIX/Linux平台

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. SYSCONFDIR/my.cnf : 通过CMake源码编译时指定的SYSCONFDIR参数指定的路径
  4. $MYSQL_HOME/my.cnf : 到MYSQL_HOME环境变量所在的路径
  5. ~/my.cnf : ~代表当前用户的家目录

启动时指定配置文件路径

  • default-file : 从本参数指定的文件中读取选项
  • default-extra-file : 在加载其他方式指定的选项后,再读取本参数指定的选项

配置文件示例

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 还有很多就不一一写了
[mysqldump]
quick
[mysqld_safe]
log-error=/data/3306/oldboy_3306.err

配置文件分为几个部分

  • # : 注释符
  • [group_name] : 这个区块用于定义对应命令行的选项,以上面的选项文件为例,有[client],[mysqld],[mysqldump],[mysqlhotcopy]等,名称看起来眼熟,其实就是mysql命令行工具名称。要知道MySQL中的命令行工具是比较多的,可是因为功能的不同,因此每个命令所支持的参数也会有差异,那么单个my.cnf选项文件中怎么去指定呢?就是通过[group_name]这个区块,其实相当于指定了一个作用域。当然,也可以用defaults-file参数来给每一个命令建立对应的参数

[mysqld],[mysql]等都可以找到对应的命令,但[client]对应哪个命令呢?所有的客户端命令都属于[client]区块,作用域范围,包括mysqldump/mysqlhotcopy/mysqladmin等,哪个不属于客户端命令呢?只有一个:mysqld。由于[client]块能作用于所有命令行程序,因此注意了,该区块中指定的选项要确保能被所有调用的命令行支持,否则可能会导致命令行执行报错

  • Option name : 指定具体的选项,MySQL支持的选项很多,这里就不一一列举了
  • Option value : 指定选项值。很多选项值都有范围和默认值,如果指定了选项而未指定值,那么该选项就继承默认值,当然这种情况下指不指定选项都没区别。另外就是MySQL中的选项非常灵活,对于布尔型选项,指定值可以为1或0,也可以为ON或OFF
    MySQL的参数除了在启动时进行指定外,其中还有相当一部分可以在MySQL服务运行过程中实时进行修改,运行过程中有两个作用域,全局(global)和当前会话(session),但是,不管是哪个作用域,如果没有同步修改my.cnf的话,MySQL服务一旦重启,运行过程中所做的修改就会自动撤销,因此一般设置的全局有效的选项,还需要同时修改my.cnf文件。

错误日志文件

错误日志是在启用mysqld时,通过log-error选项(或配置log-error系统参数)指定错误日志的路径及文件名,如果没有指定文件名,则默认文件名为[host_name].err,保存在mysql的%datadir%文件夹下。

错误日志中信息分为三类 : [Note],[Warning],[Error]。

  • [Note] : 正常的MySQL服务启动或关闭信息
  • [Warning] : 警告,记录一些可能影响正常功能但是有没有影响正常功能的信息,可以使用--log-warnings参数来控制是否启用(1为默认值,代表启用,0为禁用)
  • [Error] : 错误信息

某些信息也会被计入系统时间日志,其实对于Linux/UNIX系统也是一样的,错误日志也可以被写入到系统日志syslog中,在执行mysqld_safe命令启动MySQL服务时,可以附加--syslog参数,使MySQL的日志信息也会被输出到系统中。对于记录到系统日志中的消息,来自mysqld_safe和mysqld的消息会分别打上"mysqld_safe"或"mysql"的标签。用户也可以通过--syslod-tag=[tag]参数指定标签名称,修改后实际记录的标签心事会变成"mysql_safe-[tag]"和"mysql-[tag]"

查询日志文件

日志文件分类

  1. 慢查询日志(Slow Query Log),记录执行慢的查询
  2. 通用查询日志(General Query Log),所有的执行的查询语句都睡记录下来

MySQL的查询日志不仅能记录到文件,还能自动保存到MySQL数据库中的表对象里。

慢查询日志

会记录所有查询语句执行时间超过系统变量long_query_time(默认值为10秒)指定的参数值,并且访问的记录数超过系统变量min_examined_row_limit(默认为0条)的数量的语句。注意SQL语句执行时间不包括初始化表锁的开销。
SQL语句执行完毕后并且完成对其锁定资源的释放后,mysqld进程会将符合条件的SQL语句写入慢查询日志,因此慢查询日志中语句记录和顺序有可能跟执行顺序不同(因为每条语句的执行时间不同)
默认情况下慢查询日志功能是被禁用的,启用和禁用慢查询日志文件都是通过MySQL的系统参数控制,主要有以下两个

  • slow_query_log : 指定是否输出慢查询日志,指定为1标识输出,指定为0则标识不输出
  • slow_query_log_file : 指定日志文件存储路径和文件名,如果没有指定的话,则默认文件名为[host_name]-slow.log,保存在MySQL数据库data目录下

在5.1.6之前的版本,是通过--log-slow-queries控制慢查询日志文件输出路径

上面两个参数可以在MySQL运行时实时修改

set global slow_query_log = 'OFF';
set global slow_query_log_file = 'ON';

其他相关参数

  • long_query_time : 指定慢查询执行时间的阈值,以秒为单位,但最小可以指定到微秒,默认为10秒
  • long_short_format : 用来控制输出到慢查询日志文件的信息,指定改选项后,会减少向慢查询日志中输出信息
  • log_slow_admin_statements : 用来控制是否将一些执行时间较长的管理类型语句,如optimize table,analyze table,alter table语句输出到慢查询日志中
  • log_queries_not_using_indexes : 用来控制是否将未使用索引的语句输出到慢查询日志中
  • log_throttle_queries_not_using_indexes : 一般会与log_queries_not_using_indexes参数组合使用,他的功能是控制每分钟输出到慢查询日志的未使用索引的记录条数,默认值是0,这个0不是说不输出,而是不限制
  • log_slow_slave_statements : MySQL复制环境专用的参数,用来控制是否将复制的慢查询日志输出到慢查询日志

因为慢查询相关的参数有好几个,所以MySQL在处理慢查询日志输出时会有逻辑

  1. 执行的必须是查询语句,而非管理性语句(除非启用了log_slow_admin_statements)
  2. 查询语句执行的时间达到或超过了long_query_time参数指定的值,或者是符合log_queries_not_using_indexes条件
  3. 查询的记录量达到了min_examined_row_limit参数指定的值
  4. 查询语句不违反log_throttle_queries_not_using_indexes参数设置

需要注意的是,慢查询日志中有可能记录到用户权限或密码的相关的语句,比如更改mysql.user表中的数据时,如果执行速度很慢,该语句也会被记录到慢查询日志中,因此慢查询日志文件的保存也要注意安全。只不过一般来说这种授权相关的语句只有极低的概率才会出现在慢查询日志中(除非将long_query_time参数指定的极小)。
查看一个比较大的慢查询日志极为不便,可以考虑使用MySQL自带的mysqldumpslow命令,或者其他第三方工具,对慢查询日志进行抽象分析,便于阅读。

普通查询日志

普通查询日志(General Query Log)名不副实,这个日志不仅记录查询语句,而是能够记录mysqld进程所做的几乎所有操作,不仅仅是客户端发出的SQL语句会被记录到普通查询日志中,对于数据库或对象的管理操作也会记录下来,甚至客户端连接或断开连接,服务器都会向改文件中写入相应的信息。
所有启用普通查询日志最大的功能点是什么?审计!通过浏览这个日志文件中的信息,可以了解到客户端都做了些什么,这点对于DBA很有帮助,可以借此查询用户的操作是否有问题
默认普通查询为不启用,因为他的记录信息太过详尽,安全性是一方面,效率方面的影响也是值得评估的因素。不过,启用或禁用普通查询日志,也是通过MySQL的系统参数控制,如下

  • general_log : 可选的参数有0和1(或者ON或OFF),0为禁用,1为启用
  • general_log_file : 默认情况下,普通查询日志是保存在MySQL数据库的data目录下,如果没有明确指定文件名,则默认文件名为[host_name].log,通过本参数可以明确定义普通查询日志文件的存储路径和文件名

除了在启动MySQL服务时指定外,这两个参数还都可以在MySQL运行时动态修改

# 全局设置
set global general_log = 'ON';
set global general_log_file = 'ON';
# 当前会话设置
set general_log = 'ON';
set general_log_file = 'ON';

普通查询日志会记录客户端发出的语句(包含更新用户密码的语句),在5.6之前,密码会被明文保存在日志中,如下

grant select on my_test.* to zj_test identified by '123456';

5.6只有MySQL在保存这种语句时,会自动重写密码,如下

grant select on my_test.* to zj_test identified by '*7749C99E3D82E54D2A70FACF94E78E832F861A5F';

如果情况特殊,就是需要记录用户的明文密码,可以用--log-raw来配置,注意这个选项只能在MySQL启动前设置

配置查询日志

MySQL查询日志不仅可以被保存在文件里,同时也能够以表的形式保存在数据库mysql中的同名表中,但是要注意,保存在表中比直接保存在文件中会耗费更多的系统资源,所以还是建议将日志直接放到文件中。
放到表中在后面查询相关记录的话会很方便,两个存放方式各有利弊。
以下是设置查询日志的相关配置

1. 在MySQL服务启动时配置

指定--log-output选项,用来控制查询日志的输出方式,注意是输出方式,不是输出路径,选项如下

  • TABLE : 输出信息到数据库中得标,对应general_log和slow_log两个表
  • FILE : 输出信息到日志文件,默认为FILE
  • NONE : 不输出查询日志

在设置的时候可以相互之间以英文逗号分隔,标识两种方式都输出,如下

# 启用普通查询日志并记录到文件和表中
--log-output=TABLE,FILE --general_log
# 启用查询日志和慢查询日志并记录到文件中
--log-output=TABLE --general_log --slow_query_log
# 启用慢查询日志并记录到文件中
--log-output=FILE --slow_query_log
# 启用慢查询日志并记录到文件中并指定文件路径
--log-output=TABLE --slow_query_log --slow_query_log_file=/data/mysql/logs/slow.log

注意了当--log-output选项为NONE时,general_log和slow_log两个不管设置什么,都不会记录日志了
在5.1.29之前,没有--general_lof_file和--slow_query_log_file这两个参数,控制文件名及输出路径是通过--log和--log-slow_queries两个参数

  • --log : 指定普通查询日志的输出路径,并启用日志输出功能,默认文件名为[host_name].log
  • --log-slow_queries : 指定慢查询输出路径,并启用日志输出功能,默认文件名为[host_name]-slow.log
2. MySQL服务运行时修改

与查询相关的选项可以在MySQL服务运行时修改,因为这些选项都有对应的同名系统变量。

  • log_output
  • general_log & slow_query_log
  • general_log_file & slow_query_log_file

这几个参数都支持全局动态修改,修改即时生效,参数的功能与前面命令行中同名参数完全相同

  • sql_log_off : 可选参数值为1或0(ON/OFF)。用来指定是否启用/禁用当前会话执行的语句记录到通用查询日志,默认值为0即禁用。该参数是个会话级参数,用户必须要有super权限才能设置该选项。
3. 查询日志表的特点

slow_log和general_log两个表对象和普通的表对象有些不同

  1. 日志表能够支持CREATE TABLE,ALTER TABLE,DROP TABLE,TRUNCATE TABLE
  2. 默认情况下,日志表使用CSV存储引擎(可以通过SHOW CREATE TABLE slow_log/general_log),因此可以直接复制这个文件到其他位置,或者轻松导入其他数据库。从5.1.12开始,日志表也可以修改成MyISAM引擎。如果要执行ALTYER,DROP等操作,需要首先禁用日志功能,而后修改对象,最后再重新启用日志功能。例如,将普通查询日志表general_log变更存储引擎为MyISAM
set @old_log_state = @@global.general_log;
set global general_log = 'OFF';
alter table mysql.general_log engine = MyISAM;
set global general_log = @old_log_state;
  1. 日志表能支持RENAME,TRUNCATE,CHECK操作
  2. 日志表不支持LOCK TABLES,并且也不允许用户在其上进行INSERT,UPDATE,DELETE操作,该表的增删改查都是由MySQL内部操作的。
  3. FLUSH TABLES WITH READ LOCK以及设置全局系统变量read_only,均对日志表无效,在此期间MySQL仍能向其中写入数据。
  4. 日志表的写操作不会记入二进制日志,同样,如果有复制环境的话,日志表的内容不会被复制到其他slave节点。
  5. 刷新日志表或日志文件,可以使用FLUSH TABLES或FLUSH LOGS。注意在5.1.12~5.1.20版本时,FLUSH TABLES语句忽略日志表,而FLUSH LOGS则会刷新日志表及其文件
  6. 不允许在日志表创建分区
  7. MySQL5.6版本之前,mysqldump命令行工具在处理数据时,会自动忽略general_log和slow_query_log两张表,不过从5.6.6开始,mysqldump命令能够自动查询日志表,不过导出的数据中只有结构,不包含数据

二进制文件

重中之重

术语

二进制日志(Binary Log) : 记录数据库中的修改事件
二进制日志文件(Binary Log File) : 保存数据库中修改事件的文件

作用

二进制日志不同于上面的其他几种日志,在某些场景下必须要有,尤其是对于Replication特性。
二进制日志中记录对数据库的修改时间。听起来好像和普通查询日志比较像,但是其实差别很大。普通查询日志是文本格式文件,里面可以理解为用户实际执行的操作,而二进制日志文件,是无法直接查看的,里面记录了数据库实际的操作,比如建表操作数据修改等,受众不一样。另外不是说一定有数据被修改才会被记入二进制日志,某些操作,比如像DELETE语句,即使未匹配任何数据,也有可能被记录(视日志格式而定),同时二进制日志还包含事件执行话费的时间。
通过二进制日志可以实现两个重要的功能 :

  • 用于复制。将MySQL Master端的二进制日志发送到Slave端,Slave端即可根据二进制日志中的内容,在本地重做,以达到主从同步的目的。
  • 用于恢复。二进制日志可用于数据恢复,当使用备份恢复了数据库后,通过应用二进制文件,能够实现将数据库恢复到故障发生前的状态。

与其他日志有所不同,二进制日志不会记录SELECT/SHOW这类不产生修改数据的语句。
二进制日志默认不开启,需要在启动MySQL服务时附件--log-bin[=base_name]选项,该选项就是用来控制MySQL服务端要将数据库的修改操作写入二进制文件,而--log-bin选项值就是用于指定二进制文件的存储路径和文件名,如果不指定选项值,那么二进制日志文件的默认文件名就是[hostname]-bin.[num],文件会保存在MySQL数据库的data路径下。
[num]代表序号,初始是000001,如[hostname]-bin.000001。每次启动MySQL服务或刷新日志时,都会创建新的日志文件

单个文件不可能无限增长,他的最大空间时由系统变量max_binlog_size进行控制,当日志文件大小达到max_binlog_size指定的大小时,就会创建新的二进制文件。不过,即使有了max_binlog_size参数在控制,生成的日志文件仍有可能超出max_binlog_size参数指定的值。比如当二进制文件快要写满时,执行一个超大的事务,由于事务特性决定相关事件必须连续,这种情况下,该事件必须写到同一日志文件,这就有可能造成日志文件超出指定的最大值的现象

为了可以追踪二进制文件的状态,MySQL会创建一个与二进制日志文件同名(但扩展名为.index)的二进制日志索引文件,用户可以通过--log-bin-index[=file_name]参数指定该文件的名称和存储路径,该文件中包含所有可供使用的二进制文件。这个文件是一个文本文件,可以通过任意文本编辑工具打开查看,但注意不要再MySQL服务运行过程中手动修改该文件。
如需要取消记录二进制日志,只需要注释掉log-bin参数(如果还指定了log-slave-updates,binlog_format参数,也要一起注释掉,否则可能会在错误日志中记录一条警告),然后重启MySQL即可。至于已经生成的二进制文件,如果确定不想要了,可以直接在操作系统层删除(复制环境不建议如此)。MySQL当然也提供了命令,RESET MASTER语句将清空所有二进制文件,而PURGE BINARY LOGS语句可以用来删除指定的某个或某些日志文件。删除前记得备份。

对于拥有super权限的数据库用户,可以在执行操作前,首先执行set sql_log_bin=0,禁用其执行的语句生成的二进制日志。
MySQL也提供了--binlog-do-db--binglog-ignore-db两个选项,可以指定某数据库的修改行为,记录(或不记录)二进制日志。
--binlog-do-db--binglog-ignore-db两个选项一次只能指定一个值,如果有多个库需要设置,可以附加多个这个参数
information_schema这类没有物理实体的库,不会记录二进制日志
如果需要查看二进制日志可以通过mysqlbinlog

二进制之间也可能有区别,因为记录时间的格式可能不同,从5.1版本开始,记录的事件格式有3种:基于行格式记录(row-based logging),基于语句记录(statement-based logging)和混合模式记录(mixed-based logging)

MySQL中既有支持事务的存储引擎和不支持的,因此在操作基于不同存储引擎的对象时,二进制日志的处理也会有不同。
对于非事务表,语句执行后就会立刻写入二进制文件中,对于事务表,则要等到当前没有任何锁定或未提交信息才会写入到二进制日志,以此来确保日志被记录的始终是其执行的顺序。

对于暂未提交的事务,事务中的更新操作(如UPDATE,DELETE,INSERT支持事务的表对象)会被缓存起来,知道收到COMMIT语句,而后,mysqld进程就会将整个事务在COMMIT执行前全部写到二进制日志。

当线程开始处理事务时,他会按照binlog_cache_size系统变量指定的值分配内存空间,缓存SQL语句,如果语句需要的空间比分配的缓存区要大,那么线程会打开一个临时文件保存这个事务,知道事务结束时再自动删除临时文件。

binlog_cache_use状态变量显示了使用binlog_cache_size系统变量的事务数(含临时文件),binlog_cache_disk_use状态变量则显示了使用临时文件的事务数,这两个参数组合起来可用于binlog_cache_size系统变量设置的调整和优化,以尽可能避免使用磁盘临时文件

max_binlog_cache_size系统变量(默认为4GB,也是最大值)用来限制事务能够使用最大缓存区,如果某个事务超出了限制,则只需将出错,事务会回滚,该变量最小为4096。

系统变量 : 指MySQL数据库中的系统配置,可以用show [global] variables;
环境变量 : 指MySQL服务运行过程中的一些状态信息,可以用show [global] status;

如果二进制以基于行格式记录,并发插入(如CREATE SELECT/INSERT SELECT)会改为普通插入,以确保操作可被重现,如果是基于语句格式记录,那么二进制中记录的就是原始语句。

默认情况下,二进制日志不是实时同步到磁盘的,因此如果操作系统崩溃或者机器故障,是存在丢失数据的可能的。要防止这种情况出现,需要考虑的因素比较多,仅从MySQL的二进制日志同步来说,可以设置二进制日志同步到磁盘的频率,MySQL提供了专用的系统变量sync_binlog。

sync_binlog值设为1(秒)安全级别最高,同时也是最慢的设置,不过即使设置为1,同样有可能丢失数据,只是最坏的情况下,仅丢失最后执行的那条语句或事务。举例来说,使用InnoDB引擎的表通过事务像表中写数据,操作依旧写到二进制日志,但还没来得及提交语句写入日志,这时系统崩溃,那么当数据库服务重新启动时,InnoDB引擎会将未提交的事务回滚,那么这种情况下,必然造成数据丢失。

要解决这个文件,MySQL提供了深度的安全性,MySQL应被配置为以事务为单位同二进制日志和InnoDB日志到磁盘到磁盘。InnoDB日志默认既是同步状态,sync_binlog=1可以同步二进制日志。这样当MySQL服务从崩溃中恢复时,为事务执行回滚后,MySQL服务终端二进制日志中InnoDB事务的回滚,以这种方式确保二进制日志能够考虑InnoDB表中的实际数据,同样,Slave端也会保持同步状态(因为没有收到回滚语句)。

当MySQL服务执行崩溃恢复时发现,二进制日志比期望中要少,比如InnoDB事务缺少commit(当sync_binlog=1时不可能出现这种情况),服务器端就会抛出错误信息 : The binary log file_name is shorter than its expected size, 这种情况下,说明二进制日志文件有误,复制环境有必要重建

中继日志及复制状态文件

在复制过程中,Slave节点会创建若干文件,有些用于保存从Master节点接收到的二进制日志,有些用于记录当前复制环境的状态,还有用户记录日志事件处理进度等相关信息,从文件类型来看分为下面3类

  • 中继日志(relay log)文件 : 用于保存读取到的Matser二进制日志,由Slave节点的I/O进程负责数据的维护,这个文件也可以通过mysqlbinlog命令解析和读取其中记录的内容。
  • Master信息日志文件(master.info) : 顾名思义,当然就是保存复制环境中连接Master节点的配置信息,比如说Slave节点连接Master使用的用户名,密码,IP,端口等均在其中。随着版本的增长,这个文件中保存的内容也越来越丰富。在5.6之前,这个信息日志总是保存在master.info文件中,默认在MySQL的data路径下,而进入5.6版本后,DBA也可以选择将这些信息保存在mysql.slave_master_info表对象中
  • 中继日志信息日志文件(relay-log.info) : 保存处理进度及中继日志文件的位置的位置;与前面的日志信息相似,在MySQL5.6版本之前,也都是保存在文本格式的文件中,位于data路径下的relay-log.info文件中,不过从5.6开始,也可以将这个信息保存在mysql.slave_relay_log_info表对象中。

注意,为了保证宕机后表对象数据的安全性和一致性,后面提到的两个表对象最好使用支持事务的存储引擎,比如InnoDB引擎。之前默认的都是MyISAM存储引擎,不过从5.6.6开始,这两个表对象(如果设置为使用表来保存)默认就会是InnoDB存储引擎。另外,不管是作为文本格式文件,还是表对象,Master信息日志文件和中继信息日志文件都不要手动去编辑和修改,否则极有可能导致出现不可预料的错误。

前面的两个信息日志文件,功能纯粹,内容简单,而中继日志文件,这类文件只存在于MySQL复制(Replication)环境的Slave节点。这个日志文件跟MySQL二进制文件非常类似,并且两者记录的方式是相同的(所有才均可被mysqlbinlog命令读取),只不过,其中记录的时间内容主题可能有差异。二进制日志是记录Master端的修改行为,而中继日志则是记录接收记录自Master端的二进制日志。简单来说,二进制文件是为Master服务的,中继日志文件是为Slave服务的。

默认情况下,中继日志文件会以[host_name]-replay-bin.nnnnnn的命名格式保存在MySQL数据库的data目录下,起始文件序号一版都是000001。当然这类文件的命名规格也可以自定义,对应的初始化选项是--relay-log

默认情况下,中继日志文件的文件名包含有主机名信息,那么,如果复制环境的Slave运行一段时间后,主机名发生了变更,会发生什么情况呢?毫无疑问,复制将会中断,Slave端的应用会报错。对于已经通过--relay-log选项重新定义了命名规则的系统当然不会出现这种情况,不过即使出现了这种情况也没关系,因为文件毕竟都在,只是他自己找不到了。最简单的办法是把主机名改回去,然后重启Slave服务。如果此时不能改名的话,那么通过中专的途径建立文件软链接等方式,使其能够正确的读取到文件即可。

表对象文件

数据文件可能是数量最多,也最常见到的文件。数据文件的类型是基于存储引擎的,由于MySQL这种插件式存储引擎的设计,在实际环境中,可能会看到各种各样的数据文件。下面罗列一些比较常见的文件类型

  • frm : 表对象的结构定义文件,甭管什么存储引擎的表对象,一定拥有这个文件
  • ibd : InnoDB引擎专用的数据文件(含索引)
  • MYD : MyISAM引擎专用的数据文件
  • MYI : MyISAM引擎专用的索引文件
  • CSV : CSV引擎专用数据文件
  • ARZ : ARCHIVE引擎专用的数据文件

其他文件

进程id文件

如果是使用mysqld_safe启动MySQL数据库,当MySQL服务启动后,可以发现在数据库根目录下发现一个mysql.pid文件。查看该文件内容,可以看到其中保存着遗传数据,貌似这串数字对应的是MySQL服务的进程号。

这个文件用于保存当前MySQL实例的进程号

这个文件保存进程号的主要母的就是方式MySQL实例被多次启动。注意,仅限mysqld_safe命令启动MySQL服务的情况,因为这个文件是由mysqld_safe命令创建和维护的。当使用mysqld_safe命令启动MySQL服务,他会执行一系列检查,其中就包括到MySQL数据库根目录下查看是否存在mysql.pid文件,如果已经发现当前已经存在这个文件,就会抛出一个错误信息,并终止MySQL服务的启动 :

A mysqld process already exists

如果是用mysqld命令启动的,那有没有pid都没有影响,因为他并不检测当前是否要已经有mysqld进程在运行,这就可能导致一个MySQL数据库被多次启动,这也是问什么推荐用mysqld_safe命令启动MySQL数据库,而不是直接用mysqld命令启动

mysql.pid对于mysqld_safe命令的副作用也是有的。比如就是mysqld_safe命令检测MySQL服务是否允许,只是通过mysql.pid文件是否存在来判断,而不会去检测具体的进程是否存在。因此如果再启动数据库时,明明没有任何mysqld进程在运行,但mysqld_safe命令总是返回 "A mysqld process already exists"这样的提示语,没准就是因为数据库根目录下存在着mysql.pid文件。遇到这种情况,首先删除这个文件,而后再尝试重新执行mysqld_safe命令。

套接字文件

在Linux/UNIX环境下,可以使用UNIX域套接字。UNIX域套接字不是网络协议,只有当MySQL客户端和MySQL服务在同一台机器上此案使用。

套接字默认文件名为mysql.sock,默认保存在/tmp目录下,当然用户也可以通过--socket选项指定该文件的具体路径

show variables like "%socket%";

自动配置文件

从5.6开始,每个MySQL实例会拥有一个唯一的UUID,MySQL就是通过这个UUID来避免Slave应用错误的数据。这个文件保存在根目录下的auto.cnf中,这个文件由MySQL自动生成,不要尝试修改。

auto.cnf文件中的内容,从格式来看较为类型my.cnf初始化选项文件,也是拥有区块,在区块中有具体的参数值。从前只有[auto]区块,[auto]区块中只有一行记录,就是server_uuid,其值正式当前服务器的UUID。

mysql 细节记录03-存储引擎

概述

MySQL的存储引擎采用了插件式的设计方案

查看可用搜索引擎

show engines;

常用搜索引擎

MEMORY存储引擎

特性

  1. 数据存储在内存中
  2. 在磁盘上只有一个.frm文件,用来存储表结构的定义
  3. 数据在MySQL服务关闭或重启后,数据会丢失
  4. 删除数据时的数据不会被释放内存,没有使用的内存会被新的记录使用,只有当整个表被删除或重建时才会回收相对内存,可以使用DROP,CREATE,ALTER回收内存
  5. 可以使用的最大的内存空间由max_heap_table_size决定,默认为16MB,如果要创建内存上限不一样的表,可以在创建表之前设置max_heap_table_size(设置会话级的配置,不要设置全局的配置)再创建表,注意重启之后又会重新继承全局的max_heap_table_size设置

指定搜索引擎

create table m_test_min (`id` int) engine MEMORY;

CSV存储引擎

特性

  1. 包含一个结构定义文件(.frm),一个的数据文件(.csv),一个源信息文件(.csm),用来保存表的状态及表中的数据量
  2. csv可以直接编辑,如果表结构损坏,可以使用CHECK TABLE或REPAIR TABLE命令进行检查和修复

ARCHIVE存储引擎

特性

  1. 可以对存储的数据进行压缩
  2. 使用zlib算法压缩数据
  3. 可以用OPTIMIZE TABLE分析表使其打包成更小的格式
  4. 仅支持insert和select,不支持delete,repalce,update语句
  5. 支持order by和blob列等常规操作
  6. 支持行级锁,不支持索引
  7. 插入效率高,保存数据占用的空间小
  8. 包含一个结构文件(.frm),一个数据文件(.arz),执行优化操作时会出现一个临时文件(.arn)

BLACKHOLE存储引擎

特性

  1. 不保存数据
  2. 在磁盘上只有一个.frm文件,用来存储表结构的定义

MERGE引擎

特性

也被称为MGR_MyISAM存储引擎,实际上是将一组MyISAM表聚合在一起
被聚合在一起的表要求相同的列和索引信息(列的定义,列的顺序,索引都必须完全相同)

  1. 可以将多种存储引擎为MyISAM的表结合到一起(效果相当于UNION语句)
  2. 包含一个结构文件(.frm),一个描述数据来源的文件(.mgr)
  3. 不存储实际的数据
  4. 默认不能往MERGE引擎的表插入数据。需要通过(INSERT_METHOD)指定插入记录的表才能插入数据

实例

# 创建待创建的表1
create table merge_test_1 (`id` int) engine myisam;
# 创建待创建的表2
create table merge_test_2 (`id` int) engine myisam;
# 创建待存储引擎为merge表
create table merge_test (`id` int) engine merge union (merge_test_1,merge_test_2);
# 创建待存储引擎为merge表并指定插入表
create table merge_test (`id` int) engine=merge union=(merge_test_1,merge_test_2) insert_method=first;
# 修改插入数据的表
alter table merge_test insert_method last;

FEDERATED引擎

特性

  1. 可以用于映射其他实例的表
  2. 默认不支持,需要在配置文件中开启

    1. 在编译是指定DWITH_FEDERATED_STORAGE_ENGINE(可以用show engines;查看是否编译这个存储引擎)
    2. 在配置文件的[mysqld]中添加federated
    3. 重启mysql
  3. 包含一个结构文件(.frm),文件中还包含了远端链接信息的描述
  4. 处理sql的过程和其他方式不一样

    1. 存储引擎检查FEDERATED表列以及关联的目标端的SQL语句
    2. 使用MySQL客户端API发送语句到远端
    3. 远端处理完语句,本地接收该语句生成的结果集
    4. 如果返回结果是一个结果集,每一列都会转换为FEDERATED引擎期望的内部引擎,返回正确的结果给客户端
  5. 本地并不会保存数据,每次请求都需要从远端拉数据
  6. 不支持索引
  7. 无法感知远端表的结构变化
  8. 批量插入相对于其他搜索引擎稍差,因为实际上还是会以逐条插入的防止执行
  9. 支持select,insert,update,delete,不知道alter table以及其他DDL语句(除drop table外)

注意点

  1. 配置了Replication的复制环境,需要注意从库是否有链接到远端数据库的权限
  2. 当使用CONNECTION语句时,密码中不能包含有@字符,这个字符在连接时是关键字

配置

1. 直接指定connection

create table federated_target (
`id` int
) engine=federated connection="mysql://root:Zj123$%^@127.0.0.1/my_test/federated_source";
# connection格式
# scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

1. 使用 CREATE SERVER 创建 FEDERATED

直接指定connection有几个问题

  1. 一旦链接信息发生变化,需要逐个更新表的结构
  2. 密码中不能含有@符号
    使用 CREATE SERVER 可以避免这两个问题
# 创建server
create server federated_server
foreign data wrapper mysql
options (user 'root', password 'Zj123$%^', host '127.0.0.1', port 3306, database 'my_test');

# 创建表
create table federated_target_2 (
`id` int
) engine=federated connection="federated_server/federated_source";

MyISAM存储引擎

特性

  1. 一个结构定义文件(.frm),一个数据文件(.MYD),一个索引文件(.MYI)
  2. 默认存储在DB所在目录下,可自定义实际存储路径(如下),注意,就算定义了路径,DB目录下始终会有MYD和MYI文件(软链接)

    • DATA DIRECTORY [=] 'absolute path to directory'
    • INDEX DIRECTORY [=] 'absolute path to directory'
  3. 单表最多支持2^64条记录
  4. 单表最多创建64个索引
  5. 复合索引最多包含16个列
  6. 索引最大长度为1000B
  7. 单表字符列最大长度为65532B(不止是MyISAM存储引擎,这个限制作用于整个MySQL数据库)
  8. 锁粒度太粗(表级锁),在读写并重的场景时,整体响应速度不理想
  9. 查询快,写入快

MyISAM引擎存储格式

主要分为三种

  1. 定长(FIXED,也称静态)
  2. 动态(DYNAMIC)
  3. 压缩(COMPRESSED)

前两种不需要单独指定,会在创建对象时根据列的类型自动适配(表中没有BLOB或TEXT类型的列)

静态格式表

指表中不包含变长类型的列(varchar/varbinary/blob/text),表中的列均是固定的字节数。在MyISAM支持的三种格式中是最简单也是最安全的格式,也是最宽的查找数据的方式

创建静态表
# 创建表
create table myisam_test (
`id` int
) engine=myisam;
# 查看表信息,Row_format显示的就是表的类型
show table status like "myisam_test";
指定表格式为静态

只要表中没有BLOB,TEXT类型的列,就可以使用 row_format 强制指定表的格式为静态,这种情况下表,varchar变长类型会以定长处理,会增加表所占用的空间

# 创建表
create table myisam_test_2 (
`id` int,
`name` varchar(100)
) engine=myisam row_format=fixed;
# 查看表信息,Row_format显示的就是表的类型
show table status like "myisam_test";
特点
  • 对于char,varchar类型会自动填充空格以达到指定的列长度,对于binary,varbinary二进制类型的列会附件0x00以达到指定长度
  • 较快,易于存储
  • 易于崩溃后重建,因为记录保存的位置是固定的
  • 一版不需要重建,除非删除了大量记录,而后需要释放相应的磁盘空间
  • 通常会比动态格式占据更多的磁盘空间

动态表

表中包含有变长字符类型的列(如varchar,varbinary,blob,text),或者在创建表时指定了row_format = dynamic
相比静态表,动态表的处理比较复杂,每行都需要有个行头来记录该行的长度,由于长度不定,在更新的操作后,可能会产生存储上的碎片(可以使用OPTIMIZE TABLEmyisamchk -r消除碎片)。所在在性能上回比静态表低,但是存储空间的消耗也会比静态表小.

特点
  • 字符串长度小于4的列外,其他字符列的长度都是动态的
  • 每行记录的行头会有一个bitmap,标识该行哪些列包含空字串(对于字符类型的列)或0(对于数值类型的列),注意不包含NULL指的列。如果字符类型的列截取掉空格后长度为0,或者数值类型的列的值为0,那么这类列的劣质只需要在bitmao中标注即可,并不需要向磁盘中列实际对应的位置写任何值,如果是非空的字符列的话,按照实际字符长度去保存
  • 相对于静态表,相同列长定义的情况下,会节省一定空间
  • 每行仅需要存储字符实际需要的空间,如果之后记录变大,则该条记录可能会分片保存,这也是碎片产生的主因。比如修改一条记录附加更多信息,则该条记录的长度必然会扩展,当原始的空间无法存储新增的数据时,只能将新增的数据保存在另外的位置,这即产生了碎片。更新一行记录产生碎片的同时会生成一个链接,一个新链接至少20个字符,以便能满足扩展要求。myisamchk -ei可以用来查询表的统计信息,myisamchk -ed命令可以查询表对象的链接数,同时也可以通过定期执行OPTIMIZE TABLE或者是myisamchk -r命令消除碎片和链接
  • 相比静态类型表,遇到崩溃时恢复操作会更加复杂,这也是因为记录可能存在碎片,其中某些链接(碎片)有可能会丢失,一旦出现这种情况,那么基本这行记录的数据就丢失了

动态类型表的行长度计算公式

3 + (number of columns + 7) / 8 + (number of char columns) + (packed size of numeric columns) + (length of strings) + (number of NULL columns + 7)

也可用通过show table status like查看表对象的状态,其中的 Avg_row_length 列的值是大概估算二栋记录平均长度,不够精确,但是比较简单,可供参考.

压缩格式表

特点
  • 只能用myisamchk命令创建,解压也是用myisamchk
  • 只读
  • 不能修改或者添加记录
  • 基于静态或动态格式表
  • 占用空间更小

InnoDB存储引擎

终于到InnoDB了...

特性

  • 设计遵循ACID模型,支持实物,拥有从服务崩溃中恢复的能力,能最大限度的保护用户数据。

ACID
即事务的4个特性:原子性(Atomiocity),一致性(Consistency),隔离性(Isolation)和持久性(Durability),这4个特性合称ACID模型

  • 支持行级锁(Row-level Locking),并且引入类似Oracle数据库的一致性读特性,以提升多用户并发是的读写性能
  • InnoDB引擎表组织数据时按照主键(Primary Keys)聚簇,通过主键查找数据时极为优异
  • 在维护数据完整性方面,InnoDB支持外键(foreign key)约束
  • 对于服务器软,硬件问题导致的宕机,不管当时数据库在做什么,都不必担心,也不必进行任何特殊操作,MySQL服务在启动时能够自动进行故障恢复(原理通用Oracle数据库中的示例恢复),而且在MySQL5.6版本中,这个过程据说比之前的版本还要快
  • InnoDB拥有自己的缓存池(对应innodb_buffer_pool_size系统变量,类似Oracle数据库中的SGA_TARGET),常用数据(含索引)都在缓存中
  • 对于insert,update,delete操作,会被一种称为change buffering的机制自动优化。InnoDB不仅仅提供了一致性读,而且还能缓存变更的数据,以减少次磁盘I/O

InnoDB注意点

  • 所有表都要创建主键,最好选择常作为查询条件的列,如果没有合适的列,那么就创建到auto_incremnet的列上。
  • 如果数据是通过多表关联获取,那么使用join,为提高join性能,最好在join列上穿件索引,并且join条件的这些列最好使用相同的数据类型和定义
  • 综合考虑磁盘的I/O能力,必要时可以禁用auto commit自动提交功能
  • 相互关联的DML操作放到一个事务中处理
  • 停止视同LOCK TABLE语句,InnoDB能够处理会话并发读写同一个表对象,如果是希望执行排他的记录更新,那可以尝试使用`SELECT ... FOR
    UPDATE`语句
  • 启动innodb_file_per_table选项,使表中的数据和索引保存在单独的文件中,而不是保存到系统表空间
  • 评估数据和读写行为是否适用新的压缩(compression)特性,如果可以,建议在执行CREATE TABLE时指定row_format=compressed选项,以提高性能
  • 启动MySQL服务时附加--sql_mode=NO_ENGINE_SUBSTITUTION,以防止表被创建成其他存储引擎
  • 在新版本中,删除或创建索引性能有所提升,对系统的冲击也有所降低
  • 清空(truncate)表,现在非常快,并且释放的空间能够被操作系统重用
  • 使用DYNAMIC格式保存大数据类型(如BLOB,TEXT)将更有效率
  • INFORMATION_SCHEMA库中提供了若干新的表对象,可用于监控存储引擎的工作和负载情况
  • 新增了PERFORMANCE_SCHEMA库,可以用来查看存储引擎的性能统计数据

InnoDB引擎配置

5.6之前InnoDB引擎的数据都会放在系统表空间对应的数据文件,所以需要做如下设置

1. 指定InnoDB引擎数据文件保存路径

通过innodb_data_file_path来设置,如下

innodb_data_file_path=datafile_spec1[;datafile_spec2]...
# datafile_spec1语法如下
file_name:file_size[autoextend[:max:max_file_size]]
  • file_name : 文件大小
  • file_size : 指定文件大小
  • autoextend : 指定是否使用扩展
  • :max:max_file_size : 指定该数据文件最大可占用空间,可选参数

例子如下

innodb_data_file_path=ibdata01.df:2048M:autoextend:max:100G

需要注意,虽说InnoDB不会限制数据文件的大小,但操作系统自身可能会对单个文件的大小做限制,由于达到了操作系统的最大值导致数据文件扩展报错,可能会导致MySQL数据库崩溃
如果有多个文件可以用;分隔,如下

innodb_data_file_path=ibdata01.df:2048M:autoextend:max:100G;ibdata02.df:2048M:autoextend:max:100G

默认InnoDB系统表空间对应的数据会放在innodb_data_home_dir目录下

2. 每个表的数据分开保存

5.6之前InnoDB引擎的数据都会放在系统表空间对应的数据文件,这样做有几个缺点

  1. DBA管理成本增加
  2. 系统空间的数据文件扩展后无法回收,即使表被删除或TRUNCATE,甚至该表空间已经没有数据了,已分配的空间也只能被InnoDB存储引擎使用,而不能被其他文件使用。如果需要回收这部分存储空间需要如下处理

    1. 导出数据
    2. 删除数据文件并重新配置innodb_data_file_path
    3. 重新启动MySQL服务
    4. 导入数据

为了避免以上的情况,可以将InnoDB定义为多重表空间(multiple tablespaces),这样设置后每个表都会拥有一个独享的.idb文件,这个文件就是独立的表空间,相当于MyISAM中的.MYI.MYD
相比系统表空间,多重表空间有如下有点

  • 各表对象的数据独立存储至不同的文件,可以更灵活的分散I/O,执行备份及恢复操作
  • 能够支持compressed row format压缩存储数据
  • 当执行TRUNCATE/DROP删除表对象时,空间可以即时释放回操作系统层。
  • 空间自动扩展,无需额外配置

是否启用多重表空间由innodb_file_per_table来控制,如下

set global innodb_file_per_table=1;

对于数据原本是保存在系统空间的表,需要做如下操作

# 开启表空间
set global innodb_file_per_table=1;
# 修改表结构(alte table会触发表对象的重建,重建后的数据就会保存在独立的表空间)
alter table table_name engine=innodb;
注意

无论是否设置了innodb_file_per_table,系统表空间都是必须要有的,InnoDB自身需要使用系统表空间,存储内部数据字段及UNDO日志

3. 配置InnoDB日志文件

除了表空间,InnoDB还有自己专用的日志文件,即REDOLOG日志文件
默认InnoDB会创建两组大小均为5MB的日志文件,分别命名为ib_logfile0和ib_logfile1,日志保存在datadir变了指定的路径下

也可以使用如下的配置修改

  • innodb_log_group_home_dir : 指定InnoDB的REDOLOG日志文件板寸路径,默认是在datadir变了指定的目录下
  • innodb_log_file_size : 用于指定日志文件的大小,默认是5MB,每个日志文件最大不能超过512GB。本参数会影响检查点(checkpoint)的执行频率,已经故障的恢复时间,因此本参数值的设定也有些讲究。一般来说,日志文件设置得越大,检查点执行的频率就越低,从缓存池刷新数据到磁盘的次数就相对较少,因此能减少I/O操作,但是如果再这个期间出现故障,那么冲洗启动MySQL时,灾难恢复的时间也会越长。总和来看 : 要适量...
  • innodb_log_files_in_group : 用于指定日志文件组的数量,默认(最少)是2个,最多不超过100个,也要适量...

注意这三个参数不支持动态修改(可以改,但改的步骤有讲究,操作不好的话MySQL就启动不了了)

如果需要动态调整需要依赖innodb_fast_shutdown参数,这参数默认为1,这个参数总共可以配置三个值,如下

  • 0 : 等到会话关闭,所有事务结束,缓存区中的数据刷新到磁盘
  • 1 : 关闭会话终止连接,将已提交的数据刷新至数据文件,未提交的数据则进行回滚,这种方式也被称为快速关闭
  • 2 : 忽略当前的所有操作,直接关闭,下次启动时InnoDB需要执行故障恢复,重新读取日志文件中的数据,回滚未提交的事务等。
修改日志相关的配置
  1. 修改innodb_fast_shutdown为1
set global innodb_fast_shutdown = 1;
  1. 停止MySQL服务
  2. 修改配置文件的日志相关的配置
  3. 重新启动MySQL

4. 设置独立的UNDO表空间

什么是UNDO日志

对于实务操作来说,有提交(Commit)就必然会有回滚(Rollback),提交比较好理解,就是确定保存写入的数据,那么回滚就麻烦一些,因为他代表两步操作:首先撤销刚刚做的修改,而后将数据恢复至修改前的状态。那么,数据一经写入,怎么回复到修改前的状态呢?最简单的方式,但是就是在修改前先将旧数据保存下载,保存下来的这部分数据用专业术语形容,就是UNDO日志,存储在系统分配好的回滚段中。

在MySQL中回滚段默认是保存在系统表空间中,不过从MySQL5.6版本开始,InnoDB引擎中的UNDO日志也可以单独设置表空间,将InnoDB的UNDO日志从系统空间中移出,转移至一个独立的表空间中保存,于是就有了UNDO表空间,可以将UNDO表空间放置于SSD存储设置上已获得更好的性能

配置独立的UNDO表空间

想要使用独立的UNDO表空间,需要用到下面的配置项

  • innodb_undo_directory : 用户指定保存UNDO日志的物理文件的位置
  • innodb_undo_tablespaces : 用户指定UNDO表空间的数量,每个表空间都是独立的.idb文件,因此可以理解为UNDO数据文件的数量
  • innodb_undo_logs : 用于指定UNDO表空间中回滚段的数量(老版本中名称为innodb_rollback_segments)
注意点
  1. UNDO表空间配置只能建库前
  2. UNDO表空间一旦创建就不能删除
  3. 一旦使用了UNDO表空间,则无法降级到5.6之前的版本

使用事务

MySQL默认开启了自动提交,也就是每次执行的语句都会自动提交

1. 关闭自动提交

# 修改当前会话的配置
set autocommit = 0;
# 修改全局配置
set global init_connect = 'set autocommit = 0';
# 在配置文件中修改
[mysqld]
set global init_connect = 'set autocommit = 0';

2. 显式的声明事务

# 开始事务
start transaction;
# 执行sql
insert into `user` (`name`) value ('jin');
# 提交(commit)或者回滚(rollback)
commit;

逻辑存储结构

为了避免所有表和索引都被保存在系统表空间,导致严重的I/O争用,建议用innodb_file_per_table选项,这样创建的每个表都会独立存储(创建与表同名的.ibd文件)。这种方式创建的表会减少磁盘的碎片,并且当表被truncate时,也能将占用的空间释放回操作系统层,而不是像系统空间那样一旦占用就无法释放

InnoDB中的逻辑存储结构,从小到大分成了4种粒度

  • 页(pages,也叫块)。页是InnoDB中的最小管理单位,同一个MySQL数据库,不管他分成多少个表空间,所有表空间都拥有相同的页大小。默认情况下,Page size的大小为16KB,不过可以在创建MySQL实例(初始化数据库)时通过innodb_page_size变了进行配置,可选值有4KB,8KB,16KB三种
  • 扩展(Extents,也叫区)。每个扩展固定1MB大小,由64个16KB的页组成(大小为8KB时则由128个页组成,页大小为4KB时由256个页组成)
  • 段(Segments),段本身有很多种,比如像数据段,索引段,还有前面提到过的回滚段,不过对于InnoDB来说,这里说的段实际上指的是独立表空间对应的数据文件
  • 表空间(Tablespaces)。InnoDB逻辑存储单元中的最高粒度

Segment由无数个Extent组成,那么当表空间的空闲空间即将用尽,需要扩展时,对于需要扩展的表空间,InnoDB第一次时分配32个Pages,之后,每次扩展会分配一个完整的Extent给Segment,最大能够同时向Segment中增加4个Extent,以保证数据的连续性。
InnoDB比较特殊,他不是传统的堆组织表,而是个索引组织表,因此对于InnoDB来说数据就是索引,索引正式数据。InnoDB表的索引就需要两个段,其中一个用于B树的非页节点,另一个则用于B树的页节点。如果能保持叶节点存储在磁盘上的连续性,则能获得更好的IO性能,因为这些节点包含的正式表中实际的数据。

当通过show table status查看当前可用的空闲空间时,InnoDB会显示表空间中空闲的Extent。InnoDB会保留一些Extent,用于内部操作,这些Extent不会被包含在空闲空间中。

当从表中删除数据时,InnoDB会访问关联的B树索引。释放的空闲空间能否被其他用户使用,要看表空间中的Page或Extent是被什么操作释放的。删除一个或者表中所有记录能够释放给其他用户。不过要记住,删除的列只会被purge线程物理删除,而不是delete操作本身,purge会自动运行,当不再需要相关的Page构造回滚段或一致性读时就会将这部分被标记删除的数据物理移除,这种设定与InnoDB的多版本特性有关。

执行show engine innodb status可以查看InnoDB的基础信息,返回信息较长,不过这也说明其信息量很大,有效利用该语句输出的信息,能够加深我们队InnoDB引擎状态的了解

用户插入的记录与Page如果关联?

一条记录的长度(变长列除外,含varbinary,varchar,blob,text等类型),一般都会小于一个数据库页的一半,就是说,单条记录的最大长度不超过800B
如果一条记录的长度小于Page的一般,那么将会被完整的存储在一个Page内。当他增长到Page的一般时,变长列将选择在另外的Page保存,知道满足记录长度不超过Page一般的条件。对于选择Page外存储的列,InnoDB会在行头部前768个字段保存相关信息。每一个列都拥有自己的溢出页列表。这个768B前缀是一个20B长的值,保存着列实际的长度,以及指向其溢出的列表
表对象中的数据经过一段时间的增删改操作,极可能出现存储上的碎片,也就是说,数据再磁盘上的物理顺序并不相邻,各记录之间存在着或大或小的空闲空间。
对象存在着碎片的一个表象,就是他占用的空间比其应该占用的要多,可以简单估算下。假设一个表的平均记录长度是1KB,当前共有两万条记录,那么20MB应该就能存储该表的所有数据了,考虑到InnoDB数据和索引都是以B树的方式保存,其因子数从50%~100%,如果当前竟然占用了200MB空间,那显然就不正常了,就一定有碎片产生。
去除碎片可以通过重建表的方式,对于InnoDB来说,任意alter table操作都可能会导致表的重建,从而消除碎片。另外也可通过mysqldump先备份再导入的方式去除碎片。
清楚碎片不仅仅为了提高了资源利用率,更重要的是碎片过多可能会对性能造成不利影响,所以对于频繁增删改查的表空间,应该定时清理。

多版本机制

InnoDB能保存一定数量的记录修改的历史版本,以支持实物的特性,比如一致性和回滚。这部分信息保存在系统表空间中被称为回滚段(rollback segment)的数据结构中,与Oracle数据库的实现机制类似。InnoDB使用回滚段中的信息来执行实物的回滚,同时还可以用这些信息构造一致性读(Consisten Read)所需要的数据。
InnoDB引擎内部在保存数据到数据库时,每一行都有3个自动生成的内部列

  • DB_TRX_IDG : 占用6B,用来标记事务的标识符,记录下最后一个事务的操作类型(更新或修改),注意,删除操作会被当成update处理,只是在其中设置特殊的位置指出实例操作为delete。
  • DB_ROLL_PRT : 占用7B,被称为回滚标记,用来指定UNDO日志记录写到回滚段中的日志。如果记录被更新,那么UNDO日志会包含用于构造记录更新前状态的所有必要信息
  • DB_ROW_ID : 占用6B,这个从本质上来说,就是记录行的行ID,如果是由InnoDB自动生成的聚簇索引(隠式主键),那么改索引中就会包含记录行的行ID值,否则,DB_ROW_ID不会出现在任何索引中。

回滚段中的UNDO日志分为insert UNDO日志和update UNDO日志两种:

  • inserT UNDO : 仅在事务回滚时需要,事务提交后即可被废弃
  • UPDATE UNDO : 用于构造一致性读,这部分数据只有当没有任何事务需要用到想过信息构造记录行的之前版本,已提供一致性读的快照才会被遗弃。

考虑到InnoDB的回滚段,一致性读等特性,建议事务尽早提交,不要长期持有,这使得InnoDB无法丢弃update UNDO日志的数据,可能会造成回滚段过大,占满整个系统表空间,从而拖垮整个InnoDB引擎的运行。
回滚段中的UNDO日志记录大小,一版情况下都小于实际插入或修改的记录大小,用户可用通过这个特点大致计算回滚段所需要的空间。在多版本模式下,记录被DELETE语句删除时不会立刻在物理上彻底删除。只有当InnoDB废弃了update UNDO日志后,才会从物理上一处关联的列和索引记录,这种一处操作被称为purge,他的执行速度很快。
如果以相同的频率插入和删除表中的列,purge线程可能会存在延迟的情况,这样表就会不断增大,因为那些被(标识)删除的列实际上仍然存在,此时磁盘操作可能会变慢,对于这种情况,考虑减少新增记录,并且为purge线程分配更多的资源。控制purge线程延迟可以通过系统变量innodb_max_purge_lag进行设置

联机修改表结构

对于MySQL数据库来说,即使是InnoDB引擎,在执行DDL时也令人颇感头疼。
在5.6之前,对InnoDB银引擎表执行DDL(加列,删列,建索引,修改列定义等均包含在内),MySQL的处理流程大致如下

  1. 创建一个结构与源表对象完全相同的临时表(隠式操作,该对象对用户不可见),并将该表的结构修改为期望的结构
  2. 锁定源表,只许查询,不许修改。
  3. 将源表数据复制到新创建的临时表,类似insert into new_table select * from old_table;
  4. 将表重命名,新创建的临时表名称修改为正式表名
  5. 接触锁定,删除源表

可以看出每次DDL就相当于重建对象,如果是小表的话还好,但是表比较大的话,第3步会持续很长时间,期间不能做其他操作

对大表执行DDL的几种思路

  1. 尽量不要搞太大的表,可以使用分表实现(这种方法会造成之后会维护多张表对象,维护成本增高)
  2. 通过MySQL复制特性。先在slave端对大表做变更,而后执行主从切换,这种方式步骤的繁琐,容易造成复制环境中主从不同步
  3. 在表对象设计时,预留出若干字段(无法预知后面有哪些可能会用到的字段)
  4. 借用开源方案(Online Schema Change)

在5.6版本之后,MySQL官方终于对在线DDL修改InnoDB引擎表提供了(有限)支持。
为什么是有限支持呢?,因为当前还没有做到,对所有的DDL操作都支持练级执行(严格来说,即使是RDBMS市场中的领头羊Oracle也做不到),某些情况下还是需要复制整个表,或者DDL操作时,不允许DML同事修改表中记录等,同时某些场景下还有限制条件。官方提供了一份汇总表,罗列了不同操作对应的场景。

  • 就地进行(In-Place) : 用于标识改操作是否允许附加ALGORITHM=INPLACE子句,如果允许的话则表示修改操作可以直接在该表对象上执行(也就是In-Place),最好是Yes
  • 复制表(Copies Tables) : 用于标识该操作是否需要复制整个表,最好是No,多数情况跟In-Place的值相反
  • 允许同时执行DML(Allows Concurrent DML) : 用于标识该操作是否允许联机进行,最好是Yes,可以通过指定LOCK=NONE来允许DDL操作进行过程中仍然接受DML,而且某些情况下MySQL也会自动允许某些粒度的DML/DDL同时执行,如果同时DML语句被允许,那么同时执行查询必然也是可以的
  • 允许同事执行查询(Allows Concurrent Queries) : 用于标识当DDL执行时,是否允许同事查询对象中的数据,最好是YES。征程情况下,所有连接DDL都是允许同同时今夕查询的
DDL操作是否就地操作是否复制表是否允许同时更新/删除是否允许同事查询备注
创建索引或增加索引Yes*No*YesYes对于全文索引有限制,具体见下一行。注意操作时需要创建的索引在同一个语句的前面子句中注明要被删除,那么本项操作就不是完全的进行,而是仍然需要复制表
增加全文索引YesNo*NoNo除非用户提供FTS_DOC_ID列,否则创建第一个全文索引时仍然需要复制全表
删除索引YesNoYesYes 
修改列的默认值YesNoYesYes本操作仅需要修改表对象定义文件.frm
修改列的自增值YesNoYesYes本操作仅需要修改内存中的相关值
增加一个外键约束Yes*No*YesYes为避免复制表,建议在创建外键约束时先禁用外键检查
删除一个外键约束YesNoYesYes 
重命名列Yes*No*Yes*Yes若希望修改时不影响DML语句的并行执行,那只能改列名,不能改数据类型
增加列YesYesYes*Yes当增加的是个自增列时,是不允许同时执行DML的。即使附加了ALGORITHM=INPLACE子句,但数据仍然需要重组,因此这类操作依旧代价高昂
删除列YesYesYesYes与上同理
修改列的顺序YesYesYesYes与上同理
修改row_format属性YesYesYesYes与上同理
修改key_block_size属性YesYesYesYes与上同理
标记为NULLYesYesYesYes与上同理
标记为NOT NULLYes*YesYesYes操作成本高昂,原理上相同。另外需要注意,若SQL_MODE中的值包括script_ALL_TABLES,那么当修改的列中包含为NULL的列时,操作会失败
修改列的数据类型NoYesNoYes 
增加主键Yes*YesYesYes由于数据需要重新组织,因此成本高昂
修改另一列为主键YesYesYesYes与上同理
删除主键NoYesNoYes 
转换字符集NoYesNoYes若字符的编码发生变化,那么整个表被重建就不可避免,代价高昂
指定字符集NoYesNoYes与上同理
通过FORCE选项强制重建NoYesNoYes表现类似指定ALGORITHM=COPY语句,或者设置old_alter_table=1

建议把关注点放在"是否就地操作"和"是否复制表"两列上,这两列值的是与否,直接影响联机DDL的操作性能。一版来说,就地操作,而且不用复制表的操作性能是最好的;某些情况下尽管是就地操作,但仍然需要复制数据,不过即使是这种情况也会比表对象重建要高效一些。
这个表格还是比较清晰的,对于单项操作来说如果认真看还是能看得懂的,不过现实情况有时候要更复杂,比如我们有可能在增加列的同事删除索引,或者修改列定义的同时增加索引之类,就是说执行的DDL语句中符合了多项修改。在练级DDL前,不管复合多少项修改,其实对于MySQL的处理逻辑来说没有影响,反正表对象要重建,就按照新的定义创建对象并复制数据就好了。如今引入链接DDL,情况就会复杂一些,表7-3中的内容能够提供一些参考。如果不确定所做的操作,究竟会产生什么样的影响,那么可以换种思路,考虑将一条DDL拆分成多条DDL分别执行。

联机DDL相关语句的语法

对于InnoDB引擎表来说,执行ALTER TABLE时,并不需要特别指定是否使用联机DDL,MySQL会自动进行选择,是否进行就地(In-Place)执行,是否允许并行DML等。不过用户也可以通过ALTER TABLE语句中的LOCK和ALGORITHM两个子句,来明确控制联机DDL时的操作行为。其中,LOCK子句对于表并行度控制的微调比较有效,而ALGORITHM子句则对于操作时的性能和操作策略有较大影响。

LOCK语句用于控制表变更期间读写并发粒度,总共有4个选项

  • DEFAULT : 默认处理策略,等同于不指定LOCK子句
  • NONE : 不使用锁定策略,这种情况下其他会话既能读也能写
  • SHARED : 采取共享锁定策略,这种情况下其他会话可读但不可写
  • EXCLUSIVE : 采取排他锁定,这种情况下其他会话既不能读也不能写

ALGORITHM子句有3个选项值

  • DEFAULT : 由MySQL按照默认方式处理,相当于不指定ALGORITHM子句,如果指定了ALGORITHM子句为DEFAULT,则跟不指定没有区别,一是因为该子句默认值就是DEFAULT,另外即使指定了也还是默认处理逻辑
  • INPALCE : 如果支持直接对当前表对象做修改,则直接就地修改,对于联机DDL语句,最好是能够INPLACE,但是前提是操作确实支持INPLACE,如果对于不支持INPLACE的操作或引擎指定ALGORITHM值为INPLACE,则语句执行就会报错
  • COPY : 不管是否支持就地修改,都采取将表对象中数据新复制一份的方式修改,这是在联机DDL被引入前的操作方式。毫无疑问,这种方式成本高,代价发,执行时间长,应尽可能避免此类情况发生,不过某些情况下,可能必须采取COPY方式,比如说,重定义主键的情况等

如果希望并发粒度最高,那么就要指定LOCK=NONE(可读可写),若希望操作的成本最低,则最好指定ALGORITHM=INPLACE(可读可写),若希望操作的成本低,则最好指定ALGORITHM=INPLACE(直接对对象进行操作,涉及读写的数据量最小)。不过不是说指定了值,实际执行就一定是做对应的操作,需要考虑实际的情况,比如说即使明确的指定了LOCK=NONE,但是实际执行的是创建或删除主键的操作,那怎么可能不加锁呢

InnoDB表对偶像的限制条件
  1. 最大和最小
  • 单表最多不超过1020列,在5.6.9版本之前最多不超过100列,最多能创建64个辅助索引
  • 默认情况下,单列索引(含前缀索引)的键长度不超过767B。注意这个还跟字符集有关系。举例来说,在TEXT/VARCHAR类型列上创建前缀索引,假设当前是utf8字符集,因为每个字符占用3个字节,那么对于长度超过255个字符的列就可能会遭遇这种限制。不过,当启用了innodb_large_prefix配置选项时,最大长度就能提高到3072B。如果创建的前缀索引长度超出了最大值会怎么样呢?也得看情况,对于非唯一索引,会自动缩减到最大长度,而对于唯一索引则会抛出错误。
  • 尽管InnoDB引擎支持列长度超过65535B,不过创建表时,仍然不能创建包含VARBINARY/VARCHAR列的符合长度超过65535B,例如,下面创建时会报错
  • 一些老的文件系统,最大支持的文件大小不能超过2GB,对于这种系统上运行的InnoDB,创建数据文件时需要注意了,可以通过创建多个数据文件的方式来突破这一限制,如果使用独立表空间,那么需要控制表对象中的记录量了
  • InnoDB日志文件最大不超过512GB
  • InnoDB系统表空间最少需要10MB空间,最大则能够支持到64TB(for billion database pages),这同时也表示单个表最大不能超过64TB
  • 默认的数据库中数据页的大小为16K,可以在创建MySQL实例时通过innodb_page_size选项指定为16K/8K/4K三种。目前暂不支持增加数据页的大小,因为当前InnoDB没有处理16K大小page size的函数,如果指定了超过这个值的page size,启动InnoDB时有可能会报错。另外,在同一个MySQL实例中的数据文件和日志文件的数据页大小必须相同。
  1. 制约因素
  • ANALYZE TABLE语句输出索引信息(显示在SHOW INDEX输出中的Cardinality),通过随机访问每个索引树,并更新相应的索引统计信息。由于这只是个预估值,因此重复执行ANALYZE TABLE语句可能生成不同的数值,这种差异使ANALYZE TABLE语句在InnoDB引擎表上执行更快(相比其他引擎如MyISAM),但不能做到100%准确,因为并非所有的列都被统计。如果由于ANALYZE TABLE生成的统计信息不正确,导致执行计划并不理想(这种情况完全有可能出现),那么对于用户来说,恐怕就得通过FORCE INDEX强制指定索引。
  • SHOW TABLE STATUS语句不能列出InnoDB引擎表的实际统计数据(除了物理大小),记录行数,平均纪录长度等信息都仅是预估值
  • InnoDB引擎并不会讲表的记录量保存在内部的某处(对于事务引擎这也确实比较难实现),因此执行SELECT COUNT(*) FROM tbl时,InnoDB必须检索全表(也可能是该表主键),这可能造成效率和性能上的问题,如果改表不是经常查询,那么应用查询缓存技术会比较有效,如果是经常查询总记录数,MySQL建议对于InnoDB引擎表的这类需求,考虑通过创建中间表专门记录表记录行数的方式来处理。当然如果只是想要得到一个大概的数据量,那么SHOW TABLE STATUS中显示的信息就可以了
  • 在windows系统上,InnoDB使用小写名称保存数据库和表名。因此对于Windows/UNIX平台迁移的数据库,建议创建对象时都是用小写规则
  • 对于auto_increment列,建议创建单列索引,如果是复合索引,那么最好定义为第一列
  • 当初始化表上之前指定了auto_increment列,InnoDB会加载一个独占锁在auto_increment列的索引的最大值上。在访问自增长计数时,InnoDB使用特殊的表锁AUTO-INC,该锁只作用于当前的SQL语句,而非整个事务,其他会话或事务仍在AUTO-INC表锁持有时执行插入
  • 当重启MySQL服务时,InnoDB可能会重用之前的auto_increment列生成但未保存的值(即由之前的事务生成的值,不过rollback了)
  • 当auto_increment整数列超出范围时,insert操作会返回复制键错误的消息,这是MySQL层的行为。不过一般不用在意这个问题,MySQL支持多种整型,其中BIGINT类型有64位长度,支持的范围从--9223372036854775808到9223372036854775807,计算每秒插入一百万条记录,BIGINT至少也能坚持100年
  • DELETE FROM tbl并非重建表,而是逐条删除记录,因此,清空表中记录还是首推TRUNCATE TABLE tbl
  • 目前级联的外键行为并不会触发triggers,如果应用有触发器,那么需要考虑关联数据的更新
  • 在创建表时,注意列名不能与InnoDB内部列相同(如DB_ROW_ID,DB_TRX_ID,DB_ROLL_PTR,DB_MIX_ID),否则MySQL会抛出1005错误或error-1错误。
  1. 锁和事务
  • 当系统变量innodb_table_lock=1时(默认就是1),LOCK TABLES会在表上持有两个锁,一个是MySQL层持有的锁,另外InnoDB层也需要持有一个锁。在MySQL 4.1.2版本之前并不会持有InnoDB层锁。如果要恢复旧版本时的特性,可以考虑将innodb_table_locks参数值设置为0。不过注意如果没有持有InnoDB层的锁,LOCK TABLES语句由可能会在仍有其他事务锁定表中记录时依然返回锁定成功的信息。在5.6版本中,设置innodb_table_locks=0对于LOCK TABLES ... WRITE语句无效,不过对于隐式的LOCK TABLES ... WRITE (比如triggers) LOCK TABLES ... READ语句仍然有效
  • InnoDB引擎在事务中持有的锁会在事务提交或回滚时释放,因此当autocommit=1时执行LOCK TABLES并没有意义,因为持有的锁会马上释放
  • 事务过程中不能显示的去锁定其他表,因为LOCK TABLES会隐式执行COMMIT和UNLOCK TABLES。
  • 在5.5之前的版本中,InnoDB引擎并发数据修改事务的总数量,不能超过1023,从5.5开始并发事务数可以达到128*1023个