精华内容
下载资源
问答
  • 沃趣科技 - 微信公众号

    千次阅读 2020-12-17 23:14:53
    沃趣科技产品线。

    QData

    数据库需求 - 人类需求(马斯洛需求层次理论)

    • 性能 - QData
    • 备份 - QBackup
    • 异地双活 - QData Infinite
    • 私有云 - QData Clound & QFusion

    国内5大证券公司

    • 华泰证券 - 涨乐财富通
    • 中信证券
    • 国泰君安

    Skylake可扩展CPU

    Purley平台

    Ethernet网络

    NVMe over Fabric - 存储网络协议

    QData Control Vs EM(Enterprise Manager)

    QBackup

    QBackup五大优势

    RMAN、MySQLdump

    面向业务的CDP备份

    CDM技术

    QData Infinite

    QData T5 Vs Oracle Exadata X7
    IB长距交换机

    波分复用设备

    RPO | RTO

    MXVote智能仲裁软件

    QData Cloud

    Iaas、Paas、SaaS
    Oracle DBaaS
    QData 管控平台

    SLA

    QFusion

    RDS
    CNCF
    Docker Image - MySQL集群

    • 数据库容器化
    • 容器化RDS
      • 调度策略
      • 计算存储分离架构
        • Split Brain
        • IO优化
        • 本地存储

    QOne

    True-CDP备份技术

    QOne Data Acceleration数据加速平台 - QDA
    QOne Data Protection数据安全平台 - QDP

    高性能存储互联软件QLink
    智能数据缓存软件QCache
    QDVote仲裁软件

    CPU
    内存
    存储
    网络
    电源

    操作系统

    • 运行状态
    • 性能
    • 目录空间
    • 日志文件

    数据库

    • 性能
    • 等待事件
    • SQL详情
    • ASM资源池

    主机硬件
    操作系统
    QLink软件
    RAC集群
    数据库实例

    NVMe SSD
    PCLe Flash
    SATA/SAS SSD
    HDD

    虚拟化
    分布式存储
    数据压缩
    数据缓存

    PointRecover技术

    QMatrix

    展开全文
  • 沃趣科技 金戈:基于K8S的高性能RDS实践.pdf
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...

    沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、容灾备份、业务永续等需求。沃趣科技凭借专业的团队,优质的产品,前沿的技术,贴心的服务赢得了客户的信任与尊重,也获得了市场的认同。


    在这个关于直方图的简短系列中,我们将讨论为什么需要直方图以及Oracle创建它们的方法。我们将评估创建它们的成本以及给我们带来不必要的问题,然后我们将检查它在您的执行计划中给您带来潜在的问题。这篇文章的概述将局限于12c之前的Oracle版本,其中出现了在减少开销和提高稳定性的新型直方图和收集方法。

    一个简单的例子

    不久前,我的一个客户在他们的应用程序的一个非常简单的部分中发现了一些奇怪的性能问题。他们在处理一个在线销售系统,在一天的过程中,他们需要运行表单的常规报告:“向我显示最近下的订单,但尚未发出”。这个需求变成了一个非常简单的SQL:

    select {list of columns}
    from orders
    where      status = ‘R’
    order by
    order_id
    ;

    在一天中的任何一个时刻,只有少量的订单与这个谓词匹配——一个包含数百万行的表中有一两百个订单。在status列上有一个索引,以允许有效的访问路径,由于简单的原因,新订单将位于表的一部分中,该部分是添加到表中的最新块组,并且这些块将被缓存在内存中,因此假设查询非常快的执行。问题是,在某些时间,该报告需要几十秒才能运行出结果,而不是即时出现结果的。

    当然,第一个要检查的是检查执行计划是否符合预期,即查询是否试图做一些有效的事情。当查询快速运行时,Oracle正在使用预期的索引,当查询缓慢运行时,Oracle正在执行表扫描。所以问题从“为什么查询速度慢?”到“为什么优化器有时认为表扫描是个更好的建议呢”?这两个方面来思考这个问题。看过了以上业务活动描述,以及给出了文章的标题,您可能已经有了一个非常好的想法:这个数据集非常的倾斜、当优化器“看到”倾斜时,我们得到了正确的计划,当优化器没有看到倾斜时,我们得到了错误的计划。下面的查询(针对数据模型)突出显示了问题的类型:

    select status, count(*)
    from orders
    group by status
    order by
        status
    ;
    
        S       COUNT(*)
        C       529,100
        P           300
        R           300
        S           300
        X       500,000

    如您所见,大多数数据最终都处于两种状态之一(取决于订单最终到达客户的方式),少量数据分散在其他几个值上。当您看到这样的数据并知道您需要访问“稀有值”或“热数据”值时,您的想法可能会转向两个方向之一:虚拟列(这可能意味着基于函数的索引,或虚拟列的11g实现,甚至11g“扩展统计”)或直方图。

    虚拟列

    在我看来,最好的解决方案来自虚拟列(或11g以前的基于函数的索引),因为这允许我们维护一个非常小的、精确定位的索引,尽管数据集很大。因此,我们可以创建如下索引并收集统计信息:

    create index ord_new on orders(
        case status when 'R' then 'R' else null end
    );
    
    begin
        dbms_stats.gather_table_stats(
            user,
            'orders',
            method_opt => 'for all hidden columns size 1'
        );
    end;
    /
    

    尽管我需要收集包含索引定义的隐藏列的统计信息,但是在创建索引后收集所有隐藏列的统计信息代价可能很高,这样我就可以检查user_tab_cols 列以获取最新的列名,这将类似于sys_nc00037$,并仅收集该特定列的统计信息列。(注意:技术上,“else null”是多余的,但我更喜欢显式地包含最终选项。) 当然,我可能希望对其他不常访问的值运行类似的查询,以便可以再创建两个类似于上面的索引,或者创建一个包含这三个值的索引—下面是使用11g虚拟列方法的示例:

    alter table orders
    add (
        interesting_status    generated always as (
            case status
                 when 'C' then null
                 when 'X' then null
                        else status
            end
        ) virtual
    
    )
    /
    
    begin
        dbms_stats.gather_table_stats(
            user,
            'orders',
            method_opt => 'for columns interesting_status size 1'
    
        );
    end;
    /
    
    create index ord_is on orders(interesting_status);
    

    基于虚拟列/函数的索引方法(无论您使用哪种方法)有一个限制—您必须更改应用程序代码以利用它—11g中的“适当”虚拟列使代码看起来比FBI代码更整洁,但仍必须进行更改,例如(对于我给出的FBI示例):

    select {list of columns}
    from orders
    where      case status when 'R' then 'R' else null end = ‘R’
    order by
        order_id
    ;

    直方图

    如果我们不能改变应用程序代码该怎么办?我们必须确保优化器知道这个问题,因为如果我们不知道,那么基本优化器模型将对基数(行计数)产生错误的估计,并选择错误的执行路径。在最简单的级别上,我们为优化器收集的统计信息将显示:“表中有1030000行,此列有5个不同的值,没有空值,并且这些值从‘C’均匀分布到‘X’。”。有了这些信息,优化器对谓词“status='C'”的基数估计将派生为:总行数/非重复值数=206000。当然,假设使用100%个样本(估计百分比==100)来收集统计数据;如果使用大于11G的版本,或者11G中还没有转换成“近似NDV”机制,则结果可能稍微不那么可预测。这就是直方图发挥作用的地方——它们允许我们向优化器提供有关列中值分布的更详细信息。在12c之前,它们有两种类型:频率直方图和高度平衡直方图——在我们的例子中,我们需要一个频率直方图。(注:12c有两种新的直方图类型:Top-N和hybrid)。原则上,频率直方图是一段时间内数据的精确图像,而高度平衡直方图是数据分布的近似图像,它试图捕捉频繁访问值的细节和其余部分的不均匀部分。当一列包含的不同值不超过254个(12c中为2048个)时,就可以创建一个频率直方图,而高度平衡直方图的精确度要低得多,并且不能真正捕获超过127个频繁值的信息。在本文的其余部分,我将直接使用频率直方图而不使用高度平衡直方图。

    频率直方图

    在我们的示例中,我们只有5个不同列值,而模型数据集仅包含超过1百万行。我可以让Oracle通过收集具有以下方法参数设置的表stats来收集列的直方图:“for columns status size 254”。(注意,虽然我知道只有5个值,我也可以要求最大值,Oracle会发现5是足够的)。如果我还将estimate_percent设置为100,则在该列的“user_tab_histograms ”视图中将出现以下结果:

    select
        ep_let             status,
        endpoint_number - ep_lag   ct,
        ep_val
    from
      (
    select
        to_char(endpoint_value,'FMxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')       ep_val,
        chr(
              to_number(
              substr(to_char(endpoint_value,'FMxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),1,2),
              'XX'
              )
        )     ep_let,
        endpoint_number,
        lag(endpoint_number,1,0) over(order by endpoint_number) ep_lag
    from
        user_tab_histograms
    where
        table_name = 'ORDERS'
    and      column_name = 'STATUS'
        )
    order by
        ep_let
    /
    
    S         CT EP_VAL
    - ---------- -------------------------------
    C     529100 43202020202009e7ff86dab2800000
    P        300 50202020202016f62c60904c200000
    R        300 52202020202029a671551080e00000
    S        300 53202020202032fe93cf509b400000
    X     500000 5820202020202b817684cb40800000
    

    我已经展示了一种将endpoint_value从其内部数字形式转换为等效字符形式的方法,如果你的手边有ASCII码,你会发现端点值的十六进制表示后面附加了很多空格(0x20)-有关转换的实际执行方式的详细信息,请参见:http://jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/请注意,直方图有效地存储为累积频率,我使用lag()分析函数对其进行了分解,该函数允许您看到Oracle为数据中的每个不同值保存精确的计数。当优化器根据谓词“status='R'”计算基数时,有了这些信息(并假设我的SQL确实使用了文本值),它可以检查这是直方图中的一个值,并报告它在其中记录的计数。在这种特殊情况下,频率直方图是一个巨大的帮助,所以,您可能会问,为什么我们不简单地为应用程序中的每一列(或者可能只是出现在where子句中的列)创建直方图呢?

    具有频率直方图的威胁

    直方图有四个主要的缺陷,我可以用以下要点来标记,然后我将依次检查:

    • 它们不能很好地与绑定变量混合。
    • 它们的计算成本很高。
    • 取样时它们可能非常不稳定。
    • 你必须在适当的时候收集它们。

    我的观点是,如果查询使用文本值,优化器就能够从直方图中选择正确的条目。如果您对此查询使用了绑定变量,那么优化器将在第一个解析调用上使用“bind peeking”,并仍然生成正确的基数(和执行计划);但是直到11g中的“自适应游标共享”和12c中的“自适应执行计划”出现,这一个计划(本质上)是您为查询的所有后续执行保留的计划,无论绑定变量的值如何更改。在我的例子中,bind变量本来是可以的,因为对这个表的唯一查询是“status={rare value}”的非常简单的查询,而状态“R”的计划对于“P”和“s”来说是可以的,但通常情况下你不会那么幸运。如果您已经在列上创建了直方图,那么您应该期望在应用程序中做一些事情,让优化器能够很好地处理直方图,这可能意味着在where子句中使用文本,这可能意味着要做一些更微妙的事情,比如编写应用程序代码来检查用户的请求,并从一个简短的列表中选择最合适的SQL在每种情况下运行。假设您已经找到了如何在代码中最好地使用频率直方图的方法,那么在优化器希望读取直方图时,您仍然存在无法确保直方图准确的问题。下面是我告诉它以100%的样本量收集上述数据的频率直方图时运行的SQL Oracle:

    select
        substrb(dump(val,16,0,32),1,120) ep,
        cnt
    from     (
        select
            /*+
                no_expand_table(t) index_rs(t)   no_parallel(t)
                no_parallel_index(t) dbms_stats cursor_sharing_exact
                use_weak_name_resl dynamic_sampling(0) no_monitoring
                no_substrb_pad
            */
            max("STATUS") val,
            count(*) cnt
        from
            "TEST_USER"."ORDERS" t
        where
            "STATUS" is not null
        group by
            nlssort("STATUS", 'NLS_SORT = binary')
        )
    order by
        nlssort(val,'NLS_SORT = binary')
    ;
    

    准确的查询将取决于Oracle的版本以及Oracle是否认为该列需要频率直方图或高度平衡直方图,但一般原则是,您将看到一个聚合查询,它将处理大量数据,并且将为您标识的每一列显示查询的变体作为直方图的目标。收集直方图是一项的操代价很高的操作。您可以通过采样而不是计算来降低收集直方图的成本。在执行此操作时,您将看到类似的SQL出现,尽管有一些变化,特别是Oracle经常会将原始行的样本复制到它为此目的创建的全局临时表中,然后对全局termporary表运行查询。这可能导致构建直方图所做的工作要少得多,但它带来了不同的威胁。下面是我给Oracle提供“auto_sample_size”选项来收集直方图时原始数据的直方图内容:

    S         CT EP_VAL
    - ---------- -------------------------------
    C       2868 43202020202009e7ff86dab2800000
    P          2 50202020202016f62c60904c200000
    S          1 53202020202032fe93cf509b400000
    X       2627 5820202020202b817684cb40800000
    

    如果你把这些数字加起来,你会发现Oracle从表中提取了5498行样本,所以当它估计任何给定值的行数时,它会检查直方图并乘以1030000/5498(分子是根据用户表计算的行数。行数减去用户表的行数。行数减去用户表的行数。),因此,status='S'的估计值为187,status='P'的估计值为375,这两个值都是相当合理的(尤其是与没有直方图的1030000/5相比)。但我们该怎么处理“R”状态呢?–它没有出现在样本中,所以没有出现在直方图中。在这种情况下,优化器只需将直方图中最不受欢迎的值的基数减半,因此基数将计算为94。同样,在这种情况下,这也不算太糟,也不会改变关键的执行计划,但是如果您在Oracle每天采样的行中运气不好,您可以理解,您的执行计划可能会相当随机地改变。你能在这组数据中找出主要的威胁吗?如果Oracle在对数据进行采样时没有发现任何罕见的值,最后显示一个直方图,该直方图显示数据在C和X之间以大约50/50的比例分割,每行大约50万行,会发生什么情况?对status='R'的查询将使用“最不常访问值的一半”–估计约为250000;这正是发生在我的客户身上的情况。stats(正在进行默认的10g过时统计数据的夜间收集)会收集此表上的统计数据,并忽略所有罕见的值,在接下来的24小时内(或者直到下一个stats集合),优化器将决定在一个非常大的表上使用一个tabscan,而不是使用一个非常合适的索引。未能在直方图中捕获关键信息的想法将我们引向直方图的最后一个关键问题——如果在收集统计数据时关键信息永远不存在,会怎么样。想象一下,我的订单处理系统中的罕见值只出现在早上6:00到下午6:00到晚上10:00之间。它们都已经从系统中处理出来了。当默认的stats集合在深夜运行时,表中的唯一值是“C”和“X”,但是当查询在白天运行时,我们感兴趣的唯一值正是收集统计数据时不存在的值。即使是100%的样本,如果你在错误的时间收集数据,你的系统中也可能有部分数据会误导你。您需要对系统有足够的了解,以便知道应用程序代码本身应该对统计数据的质量负责。这可能意味着您编写代码以在一天中的特定时间收集统计数据;这可能意味着您编写代码以直接操作存储的统计数据(我们将在查看高度平衡直方图时查看该策略)。

    结 论

    当数据值的分布高度倾斜时,如果要确保优化器不会因此产生非常糟糕的执行计划,您需要对此做些什么。如果您可以控制应用程序代码特性(如虚拟列或基于函数的索引),则可能有助于处理特殊值;但如果无法更改代码,则可能需要依赖直方图。不过,即使有直方图,绑定变量也很容易导致问题——即使有11g和12c中的新特性,用于自适应游标和自适应执行计划。对频率直方图(更简单的类型)的简要检查向我们展示了它们对于具有少量不同值的列的用处,特别是如果您的SQL使用文本。柱状图,即使是简单的频率柱状图,对于Oracle来说,创建柱状图的成本也很高,除非它对一小部分数据进行采样,然后,如果真正感兴趣的数据是暂时的,并且只占总数的一小部分,柱状图可能会引入不稳定性。事实上,即使您在错误的时间使用了100%的样本,由于优化器对缺失值的处理,产生的直方图仍然可能导致问题。

     

    | 译者简介

    汤健·沃趣科技数据库技术专家

    沃趣科技数据库工程师,多年Oracle数据库从业经验,深入理解Oracle数据库结构体系,现主要参与公司一体机产品安装、测试、优化,并负责电信行业数据库以及系统运维。

    展开全文
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...

    沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、容灾备份、业务永续等需求。沃趣科技凭借专业的团队,优质的产品,前沿的技术,贴心的服务赢得了客户的信任与尊重,也获得了市场的认同。


    之前文章(引用沃趣技术--《当心!使用mysqldump备份可能会让你欲哭无泪》)介绍了当时间字段为timestamp时,使用mysqldump加where条件对时间字段进行过滤导出时,时域问题对导出数据的影响。今天我们就再来讨论一下mysqldump的时域问题。

    问题介绍

    日前,在客户某系统部署了一个数据清理脚本,该脚本在对数据进行清理之前,首先会按照清理数据的条件先使用mysqldump将即将清理的数据导出,再进行清理。该脚本使用crontab定时任务在凌晨两点执行。但第二天查看脚本的执行情况时,发现数据清理工作都顺利的完成了,但mysqldump导出的SQL文件里却只导出了表结构,没有导出数据。看到这个现象甚是奇怪。

    问题分析与排查

    1. 查看导出数据的where条件为"gmt_modified < date_sub(curdate(),interval 359 day)",基于之前的理解,我们想过有可能是时域的问题,所以确认了一下gmt_modified字段的数据类型,查看确认gmt_modified的数据类型为datetime,由于datetime数据类型是与时域无关的,所以针对这一问题,排除了时域对导出数据的影响。

    2. 难道是这个脚本在当前服务器的环境问题?将导出数据的条件改为"gmt_modified < date_sub(curdate(),interval 358 day)",在这个条件下会查询出一天的数据,将脚本当中的数据删除部分注释掉,只执行数据导出的部分,发现该脚本完整的导出了数据。实在让人疑惑,为什么白天上班的时候数据能够备份出来,然而凌晨的时候数据就备不出来?

    3. 这时候,怀疑是不是当时数据库处于某种状态,阻止了mysqldump的备份。于是写了一个脚本,每隔一秒去检测当前数据库的连接状态。加入crontab,与删除数据的脚本在凌晨同时调起。数据清理脚本大约1分钟执行完成,于是设定数据库连接监控脚本执行3分钟。第二天观察监控的日志,也并未发现有任何异常的连接。

    4. 手动执行脚本能够备份成功,crontab就无法备份,难道真的有什么鬼故事?于是在凌晨2点手动执行备份脚本,发现的确无法备份。将脚本当中的mysqldump语句摘录出来,单独执行,仍然没有备份成功。看来备份失败与脚本、与环境都没有关系,就是mysqldump的问题。又回到问题的起点,难道真的是时域惹的祸?于是在mysqldump时加上--skip-tz-utc的参数。执行备份,这次备份成功了。

    --skip-tz-utc参数介绍

    为什么--skip-tz-utc参数会影响mysqldump导出的时域呢,下面先简要介绍一下--skip-tz-utc这个参数。 

    在mysql服务器上执行mysqldump --help的命令,可以看到下面一段话。

    --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
                      TIMESTAMP data when a server has data in different time
                      zones or data is being moved between servers with
                      different time zones.
                      (Defaults to on; use --skip-tz-utc to disable.)
    

    --tz-utc参数是mysqldump的默认参数,会使得mysqldump的导出文件的顶部加上一个设置时域的语句SET TIME_ZONE='+00:00',这个时域是格林威治时间,这样当导出timestamp字段时,会把在服务器设置的当前时域下显示的timestamp时间值转化为在格林威治时间下显示的时间。如下图所示,mysqldump导出的文件当中显示的时间值相对于通过数据库查询显示的时间倒退了8个小时。

    mysql> show variables like "time_zone";
     +---------------+--------+
     | Variable_name | Value |
     +---------------+--------+
     | time_zone | +08:00 |
     +---------------+--------+
     1 row in set (0.01 sec)
     mysql> show create table t_timestamp;
     +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table | Create Table |
     +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | t_timestamp | CREATE TABLE `t_timestamp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) COLLATE utf8_bin NOT NULL,
      `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
     +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set (0.00 sec)
    
     mysql> select * from t_timestamp;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     | 7 | cr7 | 2019-12-08 14:24:18 |
     | 9 | ozil | 2019-12-08 14:24:26 |
     | 11 | ramos | 2019-12-08 14:24:33 |
     | 13 | pique | 2019-12-09 08:24:24 |
     | 15 | henry | 2019-12-09 08:24:34 |
     | 17 | lukaku | 2019-12-10 12:00:58 |
     | 19 | rakitici | 2019-12-10 12:01:12 |
     | 21 | van dijk | 2019-12-11 22:00:46 |
     | 23 | mane | 2019-12-11 22:00:57 |
     | 25 | suarez | 2019-12-11 22:01:34 |
     | 27 | Ronaldol | 2019-12-11 22:01:55 |
     | 29 | Ronaldiho | 2019-12-12 18:00:20 |
     | 31 | Deco | 2019-12-12 18:00:28 |
     +----+-----------+---------------------+
     16 rows in set (0.00 sec)
    [root@rhel74 timestamp]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_timestamp > full_timestamp.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 timestamp]# vim full_timestamp.sql
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    ...
     LOCK TABLES `t_timestamp` WRITE;
    /*!40000 ALTER TABLE `t_timestamp` DISABLE KEYS */;
     INSERT INTO `t_timestamp` VALUES (1,'messi','2019-12-07 05:27:55'),(3,'xavi','2019-12-07 05:28:01'),(5,'xsh','2019-12-07 05:28:08'),(7,'cr7','2019-12-08 06:24:18'),(9,'ozil','2019-12-08 06:24:26'),(11,'ramos','2019-12-08 06:24:33'),(13,'pique','2019-12-09 00:24:24'),(15,'henry','2019-12-09 00:24:34'),(17,'lukaku','2019-12-10 04:00:58'),(19,'rakitici','2019-12-10 04:01:12'),(21,'van dijk','2019-12-11 14:00:46'),(23,'mane','2019-12-11 14:00:57'),(25,'suarez','2019-12-11 14:01:34'),(27,'Ronaldol','2019-12-11 14:01:55'),(29,'Ronaldiho','2019-12-12 10:00:20'),(31,'Deco','2019-12-12 10:00:28');
    /*!40000 ALTER TABLE `t_timestamp` ENABLE KEYS */;
     UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    

    知道了--tz-utc,那么--skip-tz-utc的含义就是当mysqldump导出数据时,不使用格林威治时间,而使用当前mysql服务器的时域进行导出。如下列代码所示,这次备份使用了--skip-tz-utc,导出文件的语句中并没有设置时域,导出的数据中显示的时间值也和表中查询出来的时间值相同。

    [root@rhel74 timestamp]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_timestamp > full_timestamp_without_tz_utc.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 timestamp]# vim full_timestamp_without_tz_utc.sql
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    ... LOCK TABLES `t_timestamp` WRITE;
    /*!40000 ALTER TABLE `t_timestamp` DISABLE KEYS */;
     INSERT INTO `t_timestamp` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08'),(7,'cr7','2019-12-08 14:24:18'),(9,'ozil','2019-12-08 14:24:26'),(11,'ramos','2019-12-08 14:24:33'),(13,'pique','2019-12-09 08:24:24'),(15,'henry','2019-12-09 08:24:34'),(17,'lukaku','2019-12-10 12:00:58'),(19,'rakitici','2019-12-10 12:01:12'),(21,'van dijk','2019-12-11 22:00:46'),(23,'mane','2019-12-11 22:00:57'),(25,'suarez','2019-12-11 22:01:34'),(27,'Ronaldol','2019-12-11 22:01:55'),(29,'Ronaldiho','2019-12-12 18:00:20'),(31,'Deco','2019-12-12 18:00:28');
    /*!40000 ALTER TABLE `t_timestamp` ENABLE KEYS */;
     UNLOCK TABLES;

    那么这个参数的意义何在呢?当一些公司具有跨国业务时,需要在两个时域部署两台mysql服务器,这两台服务器都按照各自的时区设置服务器的时域。假设一个服务器在北京(东八区),一个服务器在东京(东九区),现在需要将北京服务器里的数据导入至东京服务器。如下列代码所示,当导入不加--skip-tz-utc参数的dump文件,查询的t_timestamp表的数据相对于在之前的东八区服务器的时间值多了一个小时,但由于东八区服务器里的13点和东九区服务器里的14点代表的是同一时刻,所以,在东九区的服务器里显示的多出的一个小时,这样显示是正确的。而如果不加--skip-tz-utc参数,dump文件导入东九区服务器后,尽管显示的时间值和之前东八区服务器显示的时间值相同,但两者代表的时刻却已经不同,东九区的13点相对东八区的13点是要慢一个小时的。

    [root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
     mysql> show variables like "time_zone";
     +---------------+--------+
     | Variable_name | Value |
     +---------------+--------+
     | time_zone | +09:00 |
     +---------------+--------+
     1 row in set (0.02 sec)
    #导入不加--skip-tz-utc参数的dump文件
    [root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest < full_timestamp.sql
     mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
     mysql> select * from t_timestamp;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 14:27:55 |
     | 3 | xavi | 2019-12-07 14:28:01 |
     | 5 | xsh | 2019-12-07 14:28:08 |
     | 7 | cr7 | 2019-12-08 15:24:18 |
     | 9 | ozil | 2019-12-08 15:24:26 |
     | 11 | ramos | 2019-12-08 15:24:33 |
     | 13 | pique | 2019-12-09 09:24:24 |
     | 15 | henry | 2019-12-09 09:24:34 |
     | 17 | lukaku | 2019-12-10 13:00:58 |
     | 19 | rakitici | 2019-12-10 13:01:12 |
     | 21 | van dijk | 2019-12-11 23:00:46 |
     | 23 | mane | 2019-12-11 23:00:57 |
     | 25 | suarez | 2019-12-11 23:01:34 |
     | 27 | Ronaldol | 2019-12-11 23:01:55 |
     | 29 | Ronaldiho | 2019-12-12 19:00:20 |
     | 31 | Deco | 2019-12-12 19:00:28 |
     +----+-----------+---------------------+
     16 rows in set (0.01 sec)
    #导入加上--skip-tz-utc参数的dump文件
    [root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest < full_timestamp_without_tz_utc.sql 
     mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
     mysql> select * from t_timestamp;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     | 7 | cr7 | 2019-12-08 14:24:18 |
     | 9 | ozil | 2019-12-08 14:24:26 |
     | 11 | ramos | 2019-12-08 14:24:33 |
     | 13 | pique | 2019-12-09 08:24:24 |
     | 15 | henry | 2019-12-09 08:24:34 |
     | 17 | lukaku | 2019-12-10 12:00:58 |
     | 19 | rakitici | 2019-12-10 12:01:12 |
     | 21 | van dijk | 2019-12-11 22:00:46 |
     | 23 | mane | 2019-12-11 22:00:57 |
     | 25 | suarez | 2019-12-11 22:01:34 |
     | 27 | Ronaldol | 2019-12-11 22:01:55 |
     | 29 | Ronaldiho | 2019-12-12 18:00:20 |
     | 31 | Deco | 2019-12-12 18:00:28 |
     +----+-----------+---------------------+
     16 rows in set (0.01 sec)

    经过上面的测试,我们了解到,是否加上--skip-tz-utc参数,会影响timastamp字段的导入导出,那么对datetime时间字段会不会有影响呢?我们又进行了如下测试,测试显示不加--skip-tz-utc,dump文件顶部会有一个SET TIME_ZONE='+00:00'的设置时域的语句,加上--skip-tz-utc,则没有这条语句,因此使用当前服务器的时域。但两个dump文件导出的数据显示都和数据库里的查询的时间值是相同的。

    #数据在东八区服务器里的查询情况
     mysql> show variables like "time_zone";
     +---------------+--------+
     | Variable_name | Value |
     +---------------+--------+
     | time_zone | +08:00 |
     +---------------+--------+
     1 row in set (0.00 sec)
     mysql> show create table t_datetime;
     +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table | Create Table |
     +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | t_datetime | CREATE TABLE `t_datetime` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) COLLATE utf8_bin NOT NULL,
      `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
     +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set (0.06 sec)
     mysql> select * from t_datetime;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     | 7 | cr7 | 2019-12-08 14:24:18 |
     | 9 | ozil | 2019-12-08 14:24:26 |
     | 11 | ramos | 2019-12-08 14:24:33 |
     | 13 | pique | 2019-12-09 08:24:24 |
     | 15 | henry | 2019-12-09 08:24:34 |
     | 17 | lukaku | 2019-12-10 12:00:58 |
     | 19 | rakitici | 2019-12-10 12:01:12 |
     | 21 | van dijk | 2019-12-11 22:00:46 |
     | 23 | mane | 2019-12-11 22:00:57 |
     | 25 | suarez | 2019-12-11 22:01:34 |
     | 27 | Ronaldol | 2019-12-11 22:01:55 |
     | 29 | Ronaldiho | 2019-12-12 18:00:20 |
     | 31 | Deco | 2019-12-12 18:00:28 |
     +----+-----------+---------------------+
     16 rows in set (0.00 sec)
    #导出时不加--skip-tz-utc参数
    [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime > full_t_datetime.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 datetime]# vim full_t_datetime.sql
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    ...
     LOCK TABLES `t_datetime` WRITE;
    /*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
     INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08'),(7,'cr7','2019-12-08 14:24:18'),(9,'ozil','2019-12-08 14:24:26'),(11,'ramos','2019-12-08 14:24:33'),(13,'pique','2019-12-09 08:24:24'),(15,'henry','2019-12-09 08:24:34'),(17,'lukaku','2019-12-10 12:00:58'),(19,'rakitici','2019-12-10 12:01:12'),(21,'van dijk','2019-12-11 22:00:46'),(23,'mane','2019-12-11 22:00:57'),(25,'suarez','2019-12-11 22:01:34'),(27,'Ronaldol','2019-12-11 22:01:55'),(29,'Ronaldiho','2019-12-12 18:00:20'),(31,'Deco','2019-12-12 18:00:28');
    /*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
     UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    #导出时加上--skip-tz-utc参数
    [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_datetime > full_t_datetime_without_tz_utc.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 datetime]# vim full_t_datetime_without_tz_utc.sql
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    ...
     LOCK TABLES `t_datetime` WRITE;
    /*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
     INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08'),(7,'cr7','2019-12-08 14:24:18'),(9,'ozil','2019-12-08 14:24:26'),(11,'ramos','2019-12-08 14:24:33'),(13,'pique','2019-12-09 08:24:24'),(15,'henry','2019-12-09 08:24:34'),(17,'lukaku','2019-12-10 12:00:58'),(19,'rakitici','2019-12-10 12:01:12'),(21,'van dijk','2019-12-11 22:00:46'),(23,'mane','2019-12-11 22:00:57'),(25,'suarez','2019-12-11 22:01:34'),(27,'Ronaldol','2019-12-11 22:01:55'),(29,'Ronaldiho','2019-12-12 18:00:20'),(31,'Deco','2019-12-12 18:00:28');
    /*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
     UNLOCK TABLES;

    我们再把这两种dump文件导入至东九区服务器,从下面的测试当中可以看到,导入之后,两种dump文件在东九区服务器里显示的时间值是相同的,且这个时间值和在东八区服务器里显示的时间值也相同。但这个和timestamp字段加--skip-tz-utc的导出方式产生的问题也是相同的。在不同时域服务器里显示相同的时间值,但这相同的时间值在不同时域服务器里代表的并不是同一时刻。所以这也就是当具有跨国跨时区的业务时,使用timestamp字段比较好的原因。

    #东九区服务器
    [root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
     mysql> show variables like "time_zone";
     +---------------+--------+
     | Variable_name | Value |
     +---------------+--------+
     | time_zone | +09:00 |
     +---------------+--------+
     1 row in set (0.02 sec)
    #导入不加--skip-tz-utc参数的dump文件
    [root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest <full_t_datetime.sql
    [root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
     mysql> select * from t_datetime;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     | 7 | cr7 | 2019-12-08 14:24:18 |
     | 9 | ozil | 2019-12-08 14:24:26 |
     | 11 | ramos | 2019-12-08 14:24:33 |
     | 13 | pique | 2019-12-09 08:24:24 |
     | 15 | henry | 2019-12-09 08:24:34 |
     | 17 | lukaku | 2019-12-10 12:00:58 |
     | 19 | rakitici | 2019-12-10 12:01:12 |
     | 21 | van dijk | 2019-12-11 22:00:46 |
     | 23 | mane | 2019-12-11 22:00:57 |
     | 25 | suarez | 2019-12-11 22:01:34 |
     | 27 | Ronaldol | 2019-12-11 22:01:55 |
     | 29 | Ronaldiho | 2019-12-12 18:00:20 |
     | 31 | Deco | 2019-12-12 18:00:28 |
     +----+-----------+---------------------+
     16 rows in set (0.00 sec)
    #导入加上--skip-tz-utc参数的dump文件
    [root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest < full_t_datetime_without_tz_utc.sql
     mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
     mysql> select * from t_datetime;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     | 7 | cr7 | 2019-12-08 14:24:18 |
     | 9 | ozil | 2019-12-08 14:24:26 |
     | 11 | ramos | 2019-12-08 14:24:33 |
     | 13 | pique | 2019-12-09 08:24:24 |
     | 15 | henry | 2019-12-09 08:24:34 |
     | 17 | lukaku | 2019-12-10 12:00:58 |
     | 19 | rakitici | 2019-12-10 12:01:12 |
     | 21 | van dijk | 2019-12-11 22:00:46 |
     | 23 | mane | 2019-12-11 22:00:57 |
     | 25 | suarez | 2019-12-11 22:01:34 |
     | 27 | Ronaldol | 2019-12-11 22:01:55 |
     | 29 | Ronaldiho | 2019-12-12 18:00:20 |
     | 31 | Deco | 2019-12-12 18:00:28 |
     +----+-----------+---------------------+
     16 rows in set (0.01 sec)

    通过上面的测试,我们了解了--skip-tz-utc参数对mysqldump导出timestamp字段是会有影响的,但不会影响datetime字段。但对于最开始我们在生产上遇到的问题,这样的理解反而使我们更加疑惑。时域问题不是不会影响datetime字段的导出吗?那为什么在mysqldump中以datetime字段作为where条件判断的字段导出数据时,在凌晨的时候会导不出来,而在白天的时候却可以正常导出呢?对于这些问题,我们又进行了下面的测试。

    实验验证

    1. 环境介绍

    本次测试采用的数据库版本为mysql5.7.22

    mysql> select version();
    +------------+
    | version() |
    +------------+
    | 5.7.22-log |
    +------------+

    当前mysql服务器设置的时域为东八区的时域。

    mysql> show variables like "time_zone";
    +---------------+--------+
    | Variable_name | Value |
    +---------------+--------+
    | time_zone | +08:00 |
    +---------------+--------+

    当前系统时间为北京时间2019-12-13的凌晨两点多,若推算成格林尼治时间,此时为2019-12-12的下午18点多。

    mysql> select now();
     +---------------------+
     | now() |
     +---------------------+
     | 2019-12-13 02:17:36 |
     +---------------------+

    用于测试的t_datetime表的表结构如下。

    CREATE TABLE `t_datetime` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8_bin NOT NULL,
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

    表中模拟了从2019-12-07到2019-12-12之间6天的数据。

    mysql> select * from t_datetime;
     +----+-----------+---------------------+
     | id | name | create_time |
     +----+-----------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     | 7 | cr7 | 2019-12-08 14:24:18 |
     | 9 | ozil | 2019-12-08 14:24:26 |
     | 11 | ramos | 2019-12-08 14:24:33 |
     | 13 | pique | 2019-12-09 08:24:24 |
     | 15 | henry | 2019-12-09 08:24:34 |
     | 17 | lukaku | 2019-12-10 12:00:58 |
     | 19 | rakitici | 2019-12-10 12:01:12 |
     | 21 | van dijk | 2019-12-11 22:00:46 |
     | 23 | mane | 2019-12-11 22:00:57 |
     | 25 | suarez | 2019-12-11 22:01:34 |
     | 27 | Ronaldol | 2019-12-11 22:01:55 |
     | 29 | Ronaldiho | 2019-12-12 18:00:20 |
     | 31 | Deco | 2019-12-12 18:00:28 |
     +----+-----------+---------------------+

    2. 不带skip-tz-utc备份t_datetime表5天以前的数据

    根据5天以前的查询条件,可以看到在该条件下可以从该表中查到3条数据。那么按照我们的要求,mysqldump也应该备份下列的3条数据。

    mysql> select * from t_datetime where create_time <  date_sub(curdate(), interval 5 day);
     +----+-------+---------------------+
     | id | name | create_time |
     +----+-------+---------------------+
     | 1 | messi | 2019-12-07 13:27:55 |
     | 3 | xavi | 2019-12-07 13:28:01 |
     | 5 | xsh | 2019-12-07 13:28:08 |
     +----+-------+---------------------+

    然而,事实上,按照create_time < date_sub(curdate(), interval 5 day)的条件,mysqldump没有备份出任何的数据。

    [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime --where="create_time  < date_sub(curdate(), interval 5 day)" >  5_day_ago_without_skip_tz_utc.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 datetime]# vim  5_day_ago_without_skip_tz_utc.sql
    --
    -- Dumping data for table `t_datetime`
    --
    -- WHERE: create_time < date_sub(curdate(), interval 5 day)
     LOCK TABLES `t_datetime` WRITE;
    /*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
    /*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
     UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

    3. 带skip-tz-utc备份t_datetime表5天以前的数据

    在mysqldump的命令加上了--skip-tz-utc的参数,再次查看备份文件,可以看到这次备份出了我们想要的数据。

    [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_datetime --where="create_time < date_sub(curdate(), interval 5 day)" > 5_day_ago_with_skip_tz_utc.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 datetime]# vim 5_day_ago_with_skip_tz_utc.sql
    --
    -- Dumping data for table `t_datetime`
    --
    -- WHERE: create_time < date_sub(curdate(), interval 5 day)
     LOCK TABLES `t_datetime` WRITE;
    /*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
     INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08');
    /*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
     UNLOCK TABLES;

    4. 查阅官方文档

    虽然加上--skip-tz-utc,我们的备份需求是达到了。但是这种结果仍然得不到一种很好的解释。因为按照我们的理解,datetime数据类型是和时域无关的,然而在我们的实践中,时域却影响了数据备份。带着这个疑问,我们在mysql的官方文档找到了相关的答案。

    第一段的前面两句找到了我们想要的答案:会话时域的设置会影响具有时域敏感性的时间值的显示。包括NOW()、CURDATE()函数,和用timestamp数据类型存储的字段。

    看到这里,突然有点豁然开朗,我们之前的理解没有错,datetime数据类型的确是不受时域影响,然而使用create_time < date_sub(curdate(), interval 5 day)条件进行备份时,影响备份结果的,并非是datetime数据类型本身,而是条件表达式中curdate()函数。

    由于使用mysqldump进行备份时,会设置当前会话的时域为+0:00,即使用格林威治时间。那么会话中的curdate()函数,会按照当前服务器时间减8个小时来进行计算。当前时间为2019-12-13的凌晨2点,那么减8个小时之后,通过格林威治时间计算的curdate()即为2019-12-12,然而datetime中的数值不变,那么根据2019-12-12计算出的5天以前便没有数据。

    按照上面的结论,我们可以进行一个猜想,由于影响mysqldump备份结果集的是curdate()函数,那么我们将条件表达式中的curdate()函数替换成真实的时间字符串,这样就不会受时域的影响,而能够正常备份出数据来。按照这个猜想,我们又进行了如下的测试。

    5. 不带skip-tz-utc,且用当前的真实时间代替备份条件中curdate()函数

    [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime --where="create_time < date_sub('2019-12-13', interval 5 day)" > 5_day_ago_without_curdate.sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@rhel74 datetime]# vim 5_day_ago_without_curdate.sql
    --
    -- Dumping data for table `t_datetime`
    --
    -- WHERE: create_time < date_sub('2019-12-13', interval 5 day)
     LOCK TABLES `t_datetime` WRITE;
    /*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
     INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08');
    /*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
     UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

    结果不出所料,mysqldump果然备份出了数据。

    结  论

    对于这个问题,如果不是在实际中碰到,单纯凭我们自己的学习,很难注意这么细微的知识点。可见,实践才是最好的老师。平时遇到什么问题,我们始终都要保持一个打破砂锅问到底的心,这样对于自己才会有所成长。再者,还要保持一个发散性的思维,碰到问题,多联想,多问几个为什么,然后再自己去寻求答案。主动的去寻找问题解决问题,而不是等问题主动找上门来。

     

    | 作者简介

    许升辉·沃趣科技数据库工程师
    熟悉MySQL体系结构和innodb存储引擎工作原理;擅长数据库问题分析,性能调优;对mysql备份恢复、数据迁移有丰富的实践。

    展开全文
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...

    沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、容灾备份、业务永续等需求。沃趣科技凭借专业的团队,优质的产品,前沿的技术,贴心的服务赢得了客户的信任与尊重,也获得了市场的认同。


    前两期文章(引用沃趣技术——《prometheus监控多个MySQL实例》《构建狂拽炫酷屌的MySQL监控平台》)介绍了prometheus、grafana、exporter的安装以及集中监控mysql节点的方法,这篇文章介绍一下prometheus的邮件报警配置。

    alertmanager下载

    prometheus报警配置需要用到alertmanager组件,这个组件可以到prometheus官网上进行下载。

    https://prometheus.io/download/

    由于最新版本的alertmanager组件配置邮箱通讯存在一些问题,我们这里选择在github上下载0.14版本的alertmanager。

    https://github.com/prometheus/alertmanager

    附具体下载地址:
    https://github.com/prometheus/alertmanager/releases/download/v0.14.0/alertmanager-0.14.0.linux-amd64.tar.gz

    alertmanager安装配置

    将下载的alertmanager包进行解压安装。

    tar -xf alertmanager-0.14.0.linux-amd64.tar.gz
    mv alertmanager-0.14.0.linux-amd64 /data/alertmanager

    编辑alertmanager的配置文件,添加邮箱信息。

    # cd /data/alertmanager
    # cat alertmanager.yml
    global:
      smtp_smarthost: smtp.exmail.xxx.com:465 # 发件人邮箱smtp地址
      smtp_auth_username: xxxx@xxx.com # 发件人邮箱账号
      smtp_from: xxx@xxx.com # 发件人邮箱账号
      smtp_auth_password: xxxxxx # 发件人邮箱密码
      resolve_timeout: 5m
      smtp_require_tls: false
    
    route:
      # group_by: ['alertname'] # 报警分组依据
      group_wait: 10s # 最初即第一次等待多久时间发送一组警报的通知
      group_interval: 10s # 在发送新警报前的等待时间
      repeat_interval: 1m # 发送重复警报的周期 对于email配置中多频繁
      receiver: 'email'
    
    receivers:
    - name: email
      email_configs:
      - send_resolved: true
        to: xxx@xxx.com # 收件人邮箱账号

    启动alertmanager。

    # cd /data/alertmanager
    ./alertmanager --config.file=alertmanager.yml &

    alertmanager的默认端口为9093。

    prometheus配置

    在prometheus目录下编辑报警模版alert_rules.yml,添加一些自定义报警项。

    # cd /data/prometheus
    # cat alert_rules.yml
    groups:
    - name: MySQL-rules
      rules:
      - alert: MySQL Status # 告警名称
        expr: up == 0
        for: 5s # 满足告警条件持续时间多久后,才会发送告警
        annotations: # 解析项,详细解释告警信息
          summary: "{{$labels.instance}}: MySQL has stop !!!"
          value: "{{$value}}"
          alertname: "MySQL数据库停止运行"
          description: "检测MySQL数据库运行状态"
          message: 当前数据库实例{{$labels.instance}}已经停止运行,请及时处理
      - alert: MySQL Slave IO Thread Status # 告警名称
        expr: mysql_slave_status_slave_io_running == 0
        for: 5s # 满足告警条件持续时间多久后,才会发送告警
        annotations: # 解析项,详细解释告警信息
          summary: "{{$labels.instance}}: MySQL Slave IO Thread has stop !!!"
          value: "{{$value}}"
          alertname: "MySQL主从IO线程停止运行"
          description: "检测MySQL主从IO线程运行状态"
          message: 当前数据库实例{{$labels.instance}} IO线程已经停止运行,请及时处理
      - alert: MySQL Slave SQL Thread Status # 告警名称
        expr: mysql_slave_status_slave_sql_running == 0
        for: 5s # 满足告警条件持续时间多久后,才会发送告警
        annotations: # 解析项,详细解释告警信息
          summary: "{{$labels.instance}}: MySQL Slave SQL Thread has stop !!!"
          value: "{{$value}}"
          alertname: "MySQL主从SQL线程停止运行"
          description: "检测MySQL主从SQL线程运行状态"
          message: 当前数据库实例{{$labels.instance}} SQL线程已经停止运行,请及时处理
      - alert: MySQL Slave Delay Status # 告警名称
        expr: mysql_slave_status_sql_delay == 30
        for: 5s # 满足告警条件持续时间多久后,才会发送告警
        annotations: # 解析项,详细解释告警信息
          summary: "{{$labels.instance}}: MySQL Slave Delay has more than 30s !!!"
          value: "{{$value}}"
          alertname: "MySQL主从延时过大"
          description: "检测MySQL主从延时状态"
          message: 当前数据库实例{{$labels.instance}} 主从延时状态已经超过30s,请及时处理

    在prometheus目录下编辑prometheus的配置文件,将监控的配置信息添加到prometheus.yml。

    # cd /data/prometheus
    # cat prometheus.yml
    # my global config
    global:
      scrape_interval: 15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
      evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
      # scrape_timeout is set to the global default (10s).
    
    # Alertmanager configuration
    alerting:
      alertmanagers:
      - static_configs:
        - targets:
          - 172.18.0.24:9093 # 对应启动的altermanager节点的9093端口
    
    # Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
    rule_files:
      - "alert_rules.yml" # 对应前面编辑的报警模版alert_rules.yml文件
    
    # A scrape configuration containing exactly one endpoint to scrape:
    scrape_configs:
      - file_sd_configs:
        - files:
          - mysql.yml
        job_name: MySQL
        metrics_path: /metrics
        relabel_configs:
        - source_labels: [__address__]
          regex: (.*)
          target_label: __address__
          replacement: $1

    编辑完成后,重新加载一下配置更改。

    kill -HUP [prometheus PID]

    验证邮件报警

    登陆prometheus的web页面,查看报警信息。

    浏览器输入Prometheus_IP:9090 ,可以看到各个报警项的状态。

    停掉主从线程,模拟触发报警。

    Slave SQL线程停掉后,报警项颜色变成黄色,持续时间超过定义的持续时间后,颜色变红,并发送邮件。

    收到报警邮件。

     

    | 作者简介

    李文航·沃趣科技数据库技术专家

    熟悉MySQL体系结构和工作原理、SQL调优、数据库故障诊断、数据迁移、备份恢复。

     

    展开全文
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • 沃趣科技作为国内领先的数据库云平台解决方案提供商,一直致力于企业级数据库云平台产品的研发,为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台,满足客户对极致性能、数据安全、...
  • Mellanox 和沃趣科技基于对行业用户的深刻理解共同设计开发的 QData Cloud 解决方案提供了高可用、高性能、可扩展的数据库云平台,QData MetroX 更是可以帮助证券用户轻松构建同城双活业务平台从而保障业务 7x24x365...
  • 沃趣科技罗小波:MySQL数据一致性

    千人学习 2016-08-17 14:10:53
    该视频教程深度讲解MySQL主备数据一致性:1、MySQL复制发展史简介,及其复制原理2、MySQL 崩溃恢复安全性,包括:如何使用sync_binlog,innodb_flush_log_at_trx_commit来保证3、MySQL崩溃时不丢失数据,如何使用...
  • ASM被誉为好的集群文件系统和卷管理器,听众通过对本次主题的学习可以了解到ASM 12C版本的新特性,以及一些核心技术的工作原理,具备这些知识后,可以指导DBA更好的完成运维和故障处理工作,指导架构师更好的设计...
  • 沃趣科技李春:MySQL并发复制探秘CSDN公开课课程介绍: “MySQL复制技术”系列课程主要介绍MySQL复制的关键技术点。课程特色: 专属答疑+课件资料提供+视频无限时回放+VIP交流群开课时间: 随到随学,自由支配点...
  • oracle一体机|沃趣oracle一体机QData发布时间:2020-07-27|信息来源:|发布作者:Oracle一体机|QData是一套专为Oracle数据库设计优化的一体机系统。它高度集成,提供了超高的性能和可用性。QData适用于中大型企业中...
  • 作为数据库生态领域的先行者,杭州沃趣科技股份有限公司始终秉持以“让客户用上最好的数据库技术”为使命,用专业力量,坚持自主研发,通过链接多元化信息技术,打造自主可控的全国产化企业级数据库云平台。...
  • 沃趣科技李春:MySQL并发复制探秘—4264人已学习 课程介绍    “MySQL复制技术”系列课程主要介绍MySQL复制的关键技术点。 课程收益  “MySQL复制技术”系列课程主要介绍MySQL复制的关键技术点。本次课程是...
  • 沃趣科技罗小波:MySQL数据一致性—4909人已学习 课程介绍    深度讲解MySQL主备数据一致性。 听众可以了解MySQL复制的原理(异步或半同步复制)以及应用场景;了解MySQL是如何在主库上保证server层和引擎层的...
  • 沃趣科技魏兴华:ASM 12C 核心新特性和工作原理—3586人已学习 课程介绍    ASM被誉为好的集群文件系统和卷管理器,听众通过对本次主题的学习可以了解到ASM 12C版本的新特性,以及一些核心技术的工作原理,具备...
  • QData Oracle 数据库一体机版本:V2.3产品说明QData 是一款高性能数据库一体机产品,通过将 Oracle 数据库,PC Server,Flash 技术整合在一起,提供高可用、高性能、可扩展的数据库服务,适用于 OLTP 和 OLAP 各种...
  • 为有效解决上述系统架构面临的瓶颈,简化运维管理、提高IT投资回报率,华泰证券信息技术中心与华胜天成旗下成员企业沃趣科技(以下简称:沃趣科技)的技术专家深入交流探讨,首期针对全账户资产管理系统及风控管理...
  • 2018年4月20日,沃趣科技“匠心·新征程”专题产品发布会在杭州蝶来望湖宾馆盛大开幕,以沃趣最新一代数据库私有云产品QData T5为首,特邀杭州市经信委、高新区(滨江)政府、杭州市中小企业服务中心等单位,以及...
  • 讲师 | 罗小波·沃趣科技高级数据库技术专家出品 | 沃趣科技 讲师介绍 - 罗小波 - 沃趣科技高级数据库技术专家IT从业多年,历任运维工程师,高级运维工程师,运维经理...
  • 沃趣 QFusion 采用目前已经非常成熟且应用非常广泛的主从复制数据同步架构,在能保证高性能的前提下,结合商业的高性能、高可用的分布式存储QCFS实现了数据零丢失,同时沃趣科技从BIOS、硬件配置、文件系统、操作...
  • 文章讲的是成功登陆资本市场沃趣挂牌新三板,3个月前,笔者采访沃趣科技(以下简称:沃趣)CEO陈栋时,沃趣才刚完成股改不久,当时陈栋就透露计划在新三板挂牌,想不到短短2个月之后,也就是10月27日,沃趣就成功挂牌...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 735
精华内容 294
热门标签
关键字:

沃趣科技