Posts tagged ‘mysql’

5.7新特性

本文仅翻译了5.7.4 新增和修改的属性,有兴趣的同学可以深入里面感兴趣的东西细看。

增强特性
  1. 安全:
    • 不支持老的password算法
    • 支持密码过期
    • 新装的机器只有root@localhost,匿名账户和test schema都不默认安装了。root密码第一次登录需要修改密码。
  2. sql mode默认值修改: NO_ENGINE_SUBSTITUTION to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.

  3. alter table 的rename index从copy table变成inplace

  4. Innodb增强:

    • varchar()字段变长部分支持inplace的online ddl。length bytes 变化和varchar()长度缩短都必须使用copy的方式来做ddl。length bytes:0-255 长度是1个字节;256和256以上长度是2字节。length bytes不变的话,支持inplace修改。length bytes变化的话,必须用copy算法
      ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
      ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
      column type INPLACE. Try ALGORITHM=COPY.
    • CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE 的DDL 临时表性能增强。啥都没说
    • innodb的临时表元信息不再存在innodb的系统表空间,而是存储再INNODB_TEMP_TABLE_INFO。从这个表可以查到当前活跃的临时表,不管是用户的还是系统帮忙创建的。
    • innodb支持mysql的空间数据。之前用BLOB现在用DATA_GEOMETRY支持。
    • 对非压缩的Innodb临时表有了专门的独立表空间。这个表空间默认在系统启动时自动在DATADIR上创建起来。另外,可以通过innodb_temp_data_file_path来指定文件路径。
    • innodchecksum增强。
    • innodb buffer pool dump和load增强了。通过innodb_buffer_pool_dump_pct参数控制buffer pool里面最近使用的page中有多少百分比读出并dump去。当InnoDB后台任务在做IO时,InnoDB通过 innodb_io_capacity来限制加载的速度。
    • InnoDB增加了对全文索引插件的支持。
    • Innodb对buffer pool instance提供多页清理线程。innodb_page_cleaners可以指定线程数。跟purge不同:
      purge: A type of garbage collection performed by a separate thread, running on a periodic schedule. The purge includes these actions: removing obsolete values from indexes; physically removing rows that were marked for deletion by previous DELETE statements. 清理index里面的过时数据,对delete删除的数据进行物理清除。cleaner是做脏页刷新的。
    • 支持InnoDB的普通和分区表 online ddl(ALGORITHM=INPLACE)OPTIMIZE TABLE ALTER TABLE … FORCE ALTER TABLE … ENGINE=INNODB (when run on an InnoDB table)。这样的话,online ddl能够缩减吃哦年间时间,允许并并发DML,用户的停机时间就减少了。
    • Fusion-io Non-Volatile Memory (NVM)文件系统支持 atomic write ,这样innodb doublewrite buffer比较多余了,对于把ibdata表空间文件存放在支持原子写的Fusion io上的时候,Innodb的doublewrite buffer会自动禁用掉
    • innodb 支持对分区表和单个的innodb表分区 Transportable Tablespace特性。这样,分区表备份,分区表在MySQL各个instance中迁移就方便了很多。
    • innodb_buffer_pool_size 可以在线修改,不用重启服务器了。通过 innodb_buffer_pool_chunk_size 来控制内存中的多少个页挪动。通过Innodb_buffer_pool_resize_status 监控buffer pool迁移状态。
      Active transactions, and operations performed through InnoDB APIs, should be completed before resizing the buffer pool. When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted when defragmenting the buffer pool and withdrawing pages during an operation to decrease buffer pool size. A drawback of allowing concurrent buffer pool access while withdrawing pages is that there could be a temporary shortage of available pages during this phase of the resizing operation.
      活动事务/通过Innodb api进行的动作需要在buffer pool变更大小前做完。也就是说,resize buffer pool会一直等到active transaction完成之后才开始。这个动作开始后,新的事务和操作只要需要用到buffer pool都必须等到resizing完成。例外:当进行buffer pool碎片整理和释放page以便减少buffer pool时,可以访问buffer pool。允许在释放page时同时访问buffer pool的一个缺点是在这个resizing操作时有一个临时缺页的问题。
  5. 增强了MySQL存储过程。MySQL现在有了stacked diagnostics areas。对于执行过的SQL可以获得它返回的各种信息:
    mysql> DROP TABLE test.no_such_table;
    ERROR 1051 (42S02): Unknown table ‘test.no_such_table’
    mysql> GET DIAGNOSTICS CONDITION 1
    -> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    mysql> SELECT @p1, @p2;
    +——-+————————————+
    | @p1 | @p2 |
    +——-+————————————+
    | 42S02 | Unknown table ‘test.no_such_table’ |
    +——-+————————————+

  6. explain可以对正在运行的连接显示执行情况。如果你需要了解那些运行时间很长的会话SQL执行为什么这么慢,就可以用这种方法。

  7. trigger。之前在同一个表上的(insert,update,delete)的(before,after)只能加一个trigger。现在可以加多个了。

  8. mysql 客户端支持–syslog,这样mysql客户端提交的语句就会被记录到系统日志中。可以用来做审核和安全审计。

  9. Test suite. 现在使用innodb作为默认存储引擎

  10. mysql client中ctrl+c会中断真正执行的SQL,或者退出MySQL。但是新版本中只会中断SQL或者中断输入,不会退出MySQL了。

  11. mysqlbinlog现在也支持 –rewrite-db 在输出的时候把数据库名修改掉。mysql自身的复制是本来就支持这个特性的。现在给mysqlbinlog工具也增加了这个特性。

  12. handler 在5.7中支持分区表了。handler提供对表存储引擎的直接访问。比如HANDLER … OPEN 可以打开一张表,HANDLER tbl_name READ index_name =可以直接从存储引擎读一行数据。好处在于比select块,没有Server层的解析,快速。缺点在于没有数据快照,读到的数据可能是刚刚被其他session修改过的。

  13. 分区表 现在支持Index condition pushdown。

  14. alter table exchange artition现在可以忽略一行一行的验证数据。(without validation)

  15. 给slave发送binlog信息的master dump thread减少了锁竞争,增强了吞吐量。之前,master dump thread只要读一个event就要给binlog加一个锁,5.7.2之后,它只在读上次成功写入event的位置的时候加锁。这样多个线程同时读binlog文件也快了,就算binlog 文件正被写入,也没有问题。

  16. 扩展了GB18030字符集

  17. change master to部分情况下可以不用stop slave了。

    • SQL thread停掉时,change master可以修改relay_log_file,relay_log_pos,master_delay.
    • io thread 停掉时,你可以修改除relay_log_file,relay_log_pos,master_delay.以外的所有参数值。
    • io,SQL thread都停掉了,你可以修改master_auto_position=1
去掉的特性
  • insert delay 不支持了
  • show engine innodb mutex去掉了
  • innodb tablespace monitor/innodb table monitor也去掉了
  • innodb_monitor、innodb_lock_monitor去掉了.修改为两个系统变量
  • innodb_status_output、innnodb_status_output_locks.
    innodb_user_sys_malloc、innodb_additional_mem_pool_size去掉了。

vm.swappiness=0在最新内核中可能会OOM,MySQL被意外kill

请使用RHEL/CentOS 6.4及更新版本内核的MySQL同志们注意,vm.swappiness = 0的默认行为修改了,如果继续设置vm.swappiness = 0,有可能导致系统内存溢出,从而导致MySQL被意外kill掉。

在之前的《LINUX上MYSQL优化三板斧》中,我们建议大家把 vm.swappiness = 0 设置好。来尽量避免MySQL的服务器内存被交换出去。这样Linux在把内存交换出去时更偏向于将cache页交换出去,而不是将inactive页交换出去。详细描述请参考:http://hatemysql.com/?p=463。

经常有人会问, vm.swappiness = 0会不会导致Linux在有swap空间的时候也不交换出去,从而导致内存溢出(OOM)。参照《LINUX上MYSQL优化三板斧》介绍,我们知道,这个值只是一个Linux在判断是否交换内存(swap)的一个“倾向”参考值,而并不是说,设置为0以后,Linux就完全不会使用内存交换空间。

但是,在较新的内核中(2.6.32-303.el6及以后),vm.swappiness = 0 的默认行为修改掉了,这个说法不再成立。设置该参数vm.swappiess=0,有可能导致MySQL数据库所在的系统出现内存溢出。一般来说,MySQL数据库占用的内存是整个服务器中最大的,根据Linux的策略,它会会首先把MySQL给Kill掉(调整 /proc/(pidof -s mysqld)/oom_adj可以改变OOM时kill的优先级),从而导致应用故障等。

这个修改是在内核3.5-rc1中提交的,并且合并到了2.6.32-303.el6及之后的各个版本。先让我们来看看这个patch:

http://gitorious.ti.com/ti-linux-kernel/ti-linux-kernel/commit/fe35004fbf9eaf67482b074a2e032abb9c89b1dd?format=patch

就像Satoru Moriya所说的那样,在之前的版本中,就算我们设置了swappiness=0并且RAM中还有pagecache,内核也可能会交换出部分匿名内存页。而为了“满足用户的需求”,这个patch修改了swappiness=0的行为,如果你设置swappiness=0,那么只有在(nr_free + nr_filebacked < high watermark)才会交换内存,也就是说空闲内存和文件缓存基本没有了才会触发内存swap。这样的话,副作用在于:内存如果不够了,Linux有可能触发OOM,从而kill掉耗费内存最多的MySQL进程。

在2.6.32-303.el6 RHEL/CentOS及更新版本的内核中,该patch就已经被合并进来:

其他分发版本的Linux(比如Debian,Ubuntu)的版本中,请各位自己查阅一下,看看时候已经合并该patch。

RHEL/CentOS 6.3的内核版本是2.6.32-279,而RHEL/CentOS 6.4的内核为2.6.32-358,从这个版本开始,swappiness的行为就已经修改了,使用这个版本及之后版本的同志们需要特别注意一下。

解决的办法其实也很简单,

1、尽量保证Linux操作系统还有足够的内存

2、最新的内核,建议把vm.swappiness设置1

3、考虑设置 /proc/(pidof -s mysqld)/oom_adj为较小的值来尽量避免MySQL由于内存不足而被关闭。

参考:

https://github.com/torvalds/linux/blob/master/mm/vmscan.c

http://gitorious.ti.com/ti-linux-kernel/ti-linux-kernel/commit/fe35004fbf9eaf67482b074a2e032abb9c89b1dd?format=patch

http://www.mysqlperformanceblog.com/2014/04/28/oom-relation-vm-swappiness0-new-kernel/

利用索引覆盖95%的数据

大家都知道索引可以极大的加快查询的速度,但是索引也是有代价的,它需要消耗额外的存储空间,对数据的更新操作也涉及到对应索引的一致性更新。

而对某些特殊的字符串索引来说,我们并不一定要整个字段长度都索引起来,只需要索引一定的前缀大小就可以达到快速查找指定数据的目的。

比如name字段记录的是人的名字,定义为:

之所以定义为这么长,是为了适应国外超长的名字。有兴趣的同学可以google一下“世界最长人名”。

但是正常的人名其实并不长,所以我们只需要索引前几个就行了。

所以如何确定索引的长度以保证更好的选择性,前缀性也是很多人研究的课题和论文题目。如果你把整个列都索引起来,对应的选择性当然非常高,但是浪费的空间却非常多;如果只索引列的部分前缀,那么有可能选择性比较差,达不到通过索引提高查询速度的目的。

我们这里不分析也不介绍现有的确定索引前缀长度的方法:) 仅仅介绍一种获得索引前缀长度的方法,它能保证95%的列数据都能索引起来。

按照@Dixon的理论,一般来说,你如果索引了95%的字符串前缀,它就可以满足大部分情况过滤需求。

对应的我们可以采用这样的sql来获得我们需要的95%字符前缀长度:

例如:
现在有一个filename表

该表有10条数据如下:

我们按照dixon的要求,需要给filename列找到一个最佳的前缀长度,就可以这样来查询:

子查询查询结果如下:

按length(filename)排序,其中l表示字符串列filename的长度,c表示filename列中长度为l的行数,t表示filename列中长度小于等于l的行数。@count表示filenames表的总行数。

其中,id和filename列是我特别加进去的,为了更直观的展示该子查询要表达的内容,这样的话,在外面的循环中,我们根据t来排序,找到大于95% * @count的第一个filename列的长度,也就是Dixon所说的95分为数(和中位数对应)。

参考:

http://www.jonathanlevin.co.uk/2011/11/get-95-for-your-index-prefix.html

http://tech.it168.com/a2011/0711/1216/000001216087_11.shtml

http://en.wikipedia.org/wiki/Dixon’s_Q_test

Linux上MySQL优化三板斧

现在MySQL运行的大部分环境都是在Linux上的,如何在Linux操作系统上根据MySQL进行优化,我们这里给出一些通用简单的策略。这些方法都有助于改进MySQL的性能。
闲话少说,进入正题。

一、CPU

首先从CPU说起。
你仔细检查的话,有些服务器上会有的一个有趣的现象:你cat /proc/cpuinfo时,会发现CPU的频率竟然跟它标称的频率不一样:

这个是Intel E5-2620的CPU,他是2.00G * 24的CPU,但是,我们发现第5颗CPU的频率为1.2G。
这是什么原因列?
这些其实都源于CPU最新的技术:节能模式。操作系统和CPU硬件配合,系统不繁忙的时候,为了节约电能和降低温度,它会将CPU降频。这对环保人士和抵制地球变暖来说是一个福音,但是对MySQL来说,可能是一个灾难。
为了保证MySQL能够充分利用CPU的资源,建议设置CPU为最大性能模式。这个设置可以在BIOS和操作系统中设置,当然,在BIOS中设置该选项更好,更彻底。由于各种BIOS类型的区别,设置为CPU为最大性能模式千差万别,我们这里就不具体展示怎么设置了。

二、内存

然后我们看看内存方面,我们有哪些可以优化的。

i)我们先看看numa

非一致存储访问结构 (NUMA : Non-Uniform Memory Access) 也是最新的内存管理技术。它和对称多处理器结构 (SMP : Symmetric Multi-Processor) 是对应的。简单的队别如下:

Smp numa

如图所示,详细的NUMA信息我们这里不介绍了。但是我们可以直观的看到:SMP访问内存的都是代价都是一样的;但是在NUMA架构下,本地内存的访问和非本地内存的访问代价是不一样的。对应的根据这个特性,操作系统上,我们可以设置进程的内存分配方式。目前支持的方式包括:

简而言之,就是说,你可以指定内存在本地分配,在某几个CPU节点分配或者轮询分配。除非是设置为–interleave=nodes轮询分配方式,即内存可以在任意NUMA节点上分配这种方式以外。其他的方式就算其他NUMA节点上还有内存剩余,Linux也不会把剩余的内存分配给这个进程,而是采用SWAP的方式来获得内存。有经验的系统管理员或者DBA都知道SWAP导致的数据库性能下降有多么坑爹。
所以最简单的方法,还是关闭掉这个特性。
关闭特性的方法,分别有:可以从BIOS,操作系统,启动进程时临时关闭这个特性。
a)由于各种BIOS类型的区别,如何关闭NUMA千差万别,我们这里就不具体展示怎么设置了。
b)在操作系统中关闭,可以直接在/etc/grub.conf的kernel行最后添加numa=off,如下所示:

另外可以设置 vm.zone_reclaim_mode=0尽量回收内存。
c)启动MySQL的时候,关闭NUMA特性:

当然,最好的方式是在BIOS中关闭。

ii)我们再看看vm.swappiness。

vm.swappiness是操作系统控制物理内存交换出去的策略。它允许的值是一个百分比的值,最小为0,最大运行100,该值默认为60。vm.swappiness设置为0表示尽量少swap,100表示尽量将inactive的内存页交换出去。
具体的说:当内存基本用满的时候,系统会根据这个参数来判断是把内存中很少用到的inactive 内存交换出去,还是释放数据的cache。cache中缓存着从磁盘读出来的数据,根据程序的局部性原理,这些数据有可能在接下来又要被读取;inactive 内存顾名思义,就是那些被应用程序映射着,但是“长时间”不用的内存。
我们可以利用vmstat看到inactive的内存的数量:

通过/proc/meminfo 你可以看到更详细的信息:

这里我们对不活跃inactive内存进一步深入讨论。Linux中,内存可能处于三种状态:free,active和inactive。众所周知,Linux Kernel在内部维护了很多LRU列表用来管理内存,比如LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE , LRU_ACTIVE_FILE, LRU_UNEVICTABLE。其中LRU_INACTIVE_ANON, LRU_ACTIVE_ANON用来管理匿名页,LRU_INACTIVE_FILE , LRU_ACTIVE_FILE用来管理page caches页缓存。系统内核会根据内存页的访问情况,不定时的将活跃active内存被移到inactive列表中,这些inactive的内存可以被交换到swap中去。
一般来说,MySQL,特别是InnoDB管理内存缓存,它占用的内存比较多,不经常访问的内存也会不少,这些内存如果被Linux错误的交换出去了,将浪费很多CPU和IO资源。 InnoDB自己管理缓存,cache的文件数据来说占用了内存,对InnoDB几乎没有任何好处。
所以,我们在MySQL的服务器上最好设置vm.swappiness=0。

我们可以通过在sysctl.conf中添加一行:

并使用sysctl -p来使得该参数生效。

三、文件系统

最后,我们看一下文件系统的优化

i)我们建议在文件系统的mount参数上加上noatime,nobarrier两个选项。

用noatime mount的话,文件系统在程序访问对应的文件或者文件夹时,不会更新对应的access time。一般来说,Linux会给文件记录了三个时间,change time, modify time和access time。
我们可以通过stat来查看文件的三个时间:

其中access time指文件最后一次被读取的时间,modify time指的是文件的文本内容最后发生变化的时间,change time指的是文件的inode最后发生变化(比如位置、用户属性、组属性等)的时间。一般来说,文件都是读多写少,而且我们也很少关心某一个文件最近什么时间被访问了。
所以,我们建议采用noatime选项,这样文件系统不记录access time,避免浪费资源。
现在的很多文件系统会在数据提交时强制底层设备刷新cache,避免数据丢失,称之为write barriers。但是,其实我们数据库服务器底层存储设备要么采用RAID卡,RAID卡本身的电池可以掉电保护;要么采用Flash卡,它也有自我保护机制,保证数据不会丢失。所以我们可以安全的使用nobarrier挂载文件系统。设置方法如下:
对于ext3, ext4和 reiserfs文件系统可以在mount时指定barrier=0;对于xfs可以指定nobarrier选项。

ii)文件系统上还有一个提高IO的优化万能钥匙,那就是deadline。

在Flash技术之前,我们都是使用机械磁盘存储数据的,机械磁盘的寻道时间是影响它速度的最重要因素,直接导致它的每秒可做的IO(IOPS)非常有限,为了尽量排序和合并多个请求,以达到一次寻道能够满足多次IO请求的目的,Linux文件系统设计了多种IO调度策略,已适用各种场景和存储设备。
Linux的IO调度策略包括:Deadline scheduler,Anticipatory scheduler,Completely Fair Queuing(CFQ),NOOP。每种调度策略的详细调度方式我们这里不详细描述,这里我们主要介绍CFQ和Deadline,CFQ是Linux内核2.6.18之后的默认调度策略,它声称对每一个 IO 请求都是公平的,这种调度策略对大部分应用都是适用的。但是如果数据库有两个请求,一个请求3次IO,一个请求10000次IO,由于绝对公平,3次IO的这个请求都需要跟其他10000个IO请求竞争,可能要等待上千个IO完成才能返回,导致它的响应时间非常慢。并且如果在处理的过程中,又有很多IO请求陆续发送过来,部分IO请求甚至可能一直无法得到调度被“饿死”。而deadline兼顾到一个请求不会在队列中等待太久导致饿死,对数据库这种应用来说更加适用。
实时设置,我们可以通过
[pre]
echo deadline >/sys/block/sda/queue/scheduler[/pre]

来将sda的调度策略设置为deadline。

我们也可以直接在/etc/grub.conf的kernel行最后添加elevator=deadline来永久生效。

总结

  1. CPU方面
    • 关闭电源保护模式
  2. 内存:
    • vm.swappiness = 0
    • 关闭numa
  3. 文件系统:
    • 用noatime,nobarrier挂载系统
    • IO调度策略修改为deadline。

 

参考文档:
http://www.gentoo-wiki.info/FAQ_Linux_Memory_Management
http://bbs.gfan.com/android-4165836-1-1.html
https://wiki.archlinux.org/index.php/CPU_Frequency_Scaling_(%E7%AE%80%E4%BD%93%E4%B8%AD%E6%96%87)
http://www.mysqlperformanceblog.com/2013/12/07/linux-performance-tuning-tips-mysql/

请不要用Seconds_Behind_Master来衡量MySQL主备的延迟时间

MySQL 本身通过 show slave status 提供了 Seconds_Behind_Master ,用于衡量主备之间的复制延迟,但是 今天碰到了一个场景,发现 Seconds_Behind_Master 为 0 , 备库的 show slave status 显示 IO/SQL 线程都是正常的 , MySQL 的主库上的变更却长时间无法同步到备库上。如果没有人为干预,直到一个小时以后, MySQL 才会自动重连主库,继续复制主库的变更。

影响范围: MySQL , Percona , MariaDB 的所有版本。

虽然这种场景非常特殊,遇到的概率并不高,但是个人觉得有必要提醒一下使用 MySQL 的 DBA 们。通过对这个场景的分析,也有助于我们更加深入的理解 MySQL replication 重试机制。

一、重现步骤

搭建主备的复制,临时断开主库的网络,并 kill 掉主库 MySQL 的 binlog dump 线程。

此时观察备库的复制情况, show slave status 中:

但是此时你把网络恢复以后,在主库做任何变更,备库都无法获得数据更新了。而且备库上的 show slave status 显示: IO 线程 SQL 线程一切正常,复制延迟一直是 0 。

一切正常,普通的监控软件都不会发现备库有数据延迟。

二、原理分析

MySQL 的 Replication 是区别于其他数据库很关键的地方。也是可扩展性和高可用的基础。它本身已经非常智能化,只需要我们调用 Change Master 指定 Binlog 文件名和偏移位置就可以搭建从主库到备库的复制关系。

MySQL 复制 线程 会自动将目前复制位置记录下来,在主备复制中断的时候自动连上主库,并从上次中断的位置重新开始复制。这些操作都是全自动化的,不需要人为的干预。这给了 MySQL DBA 带来了很多便利,同时却也隐藏了很多细节。
要真正的理解前面问题的真相以及怎么解决这个问题,我们还是需要真正的理解 MySQL 复制的原理。

2.1“推”还是“拉”

首先, MySQL 的复制是“推”的,而不是“拉”的。“拉”是指 MySQL 的备库不断的循环询问主库是否有数据更新,这种方式资源消耗多,并且效率低。“推”是指 MySQL 的主库在自己有数据更新的时候推送这个变更给备库,这种方式只有在数据有变更的时候才会发生交互,资源消耗少。如果你是程序员出身,你一定会选择“推”的方式。

那么 MySQL 具体是怎么“推”的列,实际上备库在向主库申请数据变更记录的时候,需要指定从主库 Binlog 的哪个文件 ( MASTER_LOG_FILE ) 的具体多少个字节偏移位置 ( MASTER_LOG_POS ) 。对应的,主库会启动一个 Binlog dump 的线程,将变更的记录从这个位置开始一条一条的发给备库。备库一直监听主库过来的变更,接收到一条,才会在本地应用这个数据变更。

2.2 原因解析

从上面的分析,我们可以大致猜到为什么 show slave status 显示一切正常,但是实际上主库的变更都无法同步到备库上来:

出现问题的时候, Binlog dump 程序被我们 kill 掉了。作为监听的一方,备库一直没有收到任何变更,它会认为主库上长时间没有任何变更,导致没有变更数据推送过来。备库是无法判断主库上对应的 Binlog dump 线程 到底是意外终止了,还是长时间没有任何数据变更的。所以,对这两种情况来说,备库都显示为正常。

当然, MySQL 会尽量避免这种情况。比如:

  • 在 Binlog dump 被 kill 掉时通知备库 线程 被 kill 掉了。所以我们重现时需要保证这个通知发送不到备库,也就是说该问题重现的关键在于 Binlog dump 被 kill 的消息由于网络堵塞或者其他原因无法发送到备库。
  • 备库如果长时间没有收到从主库过来的变更,它会每隔一段时间重连主库。

2.3 问题避免

基于上面的分析,我们知道 MySQL 在这种情况下确实无法避免,那么我们可以有哪些办法可以避开列:

  1. 被动处理:修改延迟的监控方法,发现问题及时处理。
  2. 主动预防:正确设置 –master-retry-count , –master-connect-retry , –slave-net-timeout 复制重试参数。
被动处理

MySQL 的延迟监控大部分直接采集 show slave status 中的 Seconds_Behind_Master 。这种情况下, Seconds_Behind_Master 就无法用来真实的衡量主备之间的复制延迟了。我们建议通过在主库轮询插入时间信息,并通过复制到备库的时间差来获得主备延迟的方案。 Percona 提供了一种类似的方案 pt-heartbeat 。

发现这个问题以后,我们只需要 stop slave; start slave; 重启复制就能解决这个问题。

主动预防

MySQL 可以指定三个参数,用于复制线程重连主库:

  • master-retry-count
  • master-connect-retry
  • slave-net-timeout

其中 master-connect-retry 和 master-retry-count 需要在 Change Master 搭建主备复制时指定,而 slave-net-timeout 是一个全局变量,可以在 MySQL 运行时在线设置。

具体的重试策略为:备库过了 slave-net-timeout 秒还没有收到主库来的数据,它就会开始第一次重试。然后每过 master-connect-retry 秒,备库会再次尝试重连主库。直到重试了 master-retry-count 次,它才会放弃重试。如果重试的过程中,连上了主库,那么它认为当前主库是好的,又会开始 slave-net-timeout 秒的等待。

slave-net-timeout 的默认值是 3600 秒, master-connect-retry 默认为 60 秒, master-retry-count 默认为 86400 次。也就是说,如果主库一个小时都没有任何数据变更发送过来,备库才会尝试重连主库。这就是为什么在我们模拟的场景下,一个小时后,备库才会重连主库,继续同步数据变更的原因。
这样的话,如果你的主库上变更比较频繁,可以考虑将 slave-net-timeout 设置的小一点,避免主库 Binlog dump 线程 终止了,无法将最新的更新推送过来。

当然 slave-net-timeout 设置的过小也有问题,这样会导致如果主库的变更确实比较少的时候,备库频繁的重新连接主库,造成资源浪费。
沃趣科技的 Q Monitor 监控中对主备复制的延迟监控,并不是通过 Seconds_Behind_Master 来监控主备的。它采用了类似于 pt-heartbeat 的方式对主备进行复制延迟监控。

MySQL 5.6 GTID模式下手工删除日志导致备库数据丢失

我们在测试 5.6 GTID 的时候,发现了一个导致主备数据丢失的场景。特别提醒一下使用 MySQL 5.6 并启用了 GTID 的各位,以避免这种情况发生。同时也简单介绍了 GTID 的实现原理。

场景描述

有一台 MySQL 实例 A ,启用了 GTID 。由于 MySQL 服务器空间吃紧,我们手工将主库的所有的 binlog 以及 binlog 的 index 文件都删除掉,并启动了这个 MySQL 实例。此后,本来连在该 MySQL 实例上的备库虽然 show slave status 状态都是正常的,但是却无法获得主库上的任何更新了。备库上的 show slave status 如下:

GTID 是什么?

首先,简单描述一下 GTID 。

GTID 是 MySQL 5.6 新引入了的概念,它是由 UUID 和事务 ID 组成。这样全球所有的服务器的事务都可以用 GTID 来表示。下面就是一个 GTID :

9300dd57-51da-11e3-989d-3cd92bee36a8 表示的是一台服务器 A 的 UUID ,这个是全球唯一的。 1 表示这个服务器 A 的第一个事务。
引入 GTID 以后, MySQL 就更灵活了:

  • Slave 在搭建复制的时候,简单的设置 auto_position=1 ,不用去找 MASTER_LOG_FILE 和 MASTER_LOG_POS 这种坑爹的“二进制文件的字节偏移位置”了。
  • 在环形复制或者一主多从的架构下,节点一旦损坏,其他节点之间复制的架构重新搭建起来能够非常简单的完成。

GTID 实现原理

我们这里也简单描述一下 GTID 的实现原理。
MySQL 在主库上会把它自己执行过的所有事务的 GTID 都记录下来: gtid_executed 。 gtid_executed 是一个 GTID 的集合,不管是客户自己提交的还是 SQL 线程执行过的数据变更事务,都会被记录在这里。你可以通过 show global variables 查看:

然后,当备库需要搭建复制的时候,有两种方式:

  • auto_position=0 ,需要指定 MASTER_LOG_FILE 和 MASTER_LOG_POS 。
  • auto_position=1 。

auto_position=0 时,很简单根据指定的 binlog 文件和偏移量, Master 将 binlog 中的各个数据变更事务发送给备库就好了。备库接收到对应的事务,判断是否本机发起的 ( 通过数据变更事务记录的 server_id 来判断 ) ,如果不是本机发起的,就直接执行。

在 auto_position=1 时,备库将自己的 gtid_executed 的事务集合传给 Master , Master 找到第一个包含有非备库 gtid_executed 事务集中数据变更的 binlog ,将 binlog 中的各个 event 发送给备库。这样备库首先判断是否本机发起的,然后判断发送过来的各个数据变更事务是否在本机执行过。没有执行过的事务都需要在本地执行一遍。

问题分析

我们还是看备库上的 slave 状态:

我们注意到 Retrieved_Gtid_Set 比 Executed_Gtid_Set 还要少。 Retrieved_Gtid_Set 记录的是 IO thread 从主库拿到的事务集合, Executed_Gtid_Set 记录的是本机已经执行的事务集合。这里有一个很奇怪的想象,本机已经执行的事务集合比 IO thread 从主库拿到的事务集合还要大,而且它只是备库。
回想一下,我们之前做的操作:手工删除了 binlog 日志和 binlog index 文件,然后启动了 MySQL 。这个操作在没有启动 GTID 的 MySQL 上,我们也做过类似的事情,备库并没有复制丢失的问题。

这里之所以数据丢失的原因就很清晰了:

我们手工删除了 binlog 以后,数据库会自动生成 binlog 。但是在 Master 中,它重新从 9300dd57-51da-11e3-989d-3cd92bee36a8:1 第一个事务开始计数。从另外一个侧面来说,已经执行完的 GTID 集合, MySQL 并没有单独保存,而是通过 Binlog 来获得的。

备库已经执行完了 9300dd57-51da-11e3-989d-3cd92bee36a8 :1 - 28123 这些事务。所以当 SQL thread 拿到从主库复制过来的变更事务时,发现这些事务都是它已经执行过的事务,那么它就不会再重复执行,从而导致对应的这些事务都会被丢失掉。

根据前面的描述,我们尽量不要在数据库之外去做一些事情。比如:手工删除 binlog ,我们其实可以用更好的办法:“ purge master logs ”。就算我们确实需要在手工删除,并启动数据库,需要同时将 auto.cnf 清理掉。这样主库会生成新的 UUID ,备库发现是新的事务就会执行这个变更了。

MYSQL数据丢失讨论

 

目录

 

MySQL数据丢失讨论……………………………………………………………………………………… 1

目录……………………………………………………………………………………………………………….. 3

1.    概述…………………………………………………………………………………………………………. 4

2.    问题定义………………………………………………………………………………………………….. 4

3.    InnoDB事务数据丢失………………………………………………………………………………. 4

3.1.   InnoDB事务基本原理…………………………………………………………………………….. 4

3.2.   InnoDB事务崩溃恢复基本原理……………………………………………………………….. 5

3.3.   InnoDB redo日志…………………………………………………………………………………… 5

3.4.   innodb_flush_log_at_trx_commit……………………………………………………………… 5

4.    数据库复制导致数据丢失…………………………………………………………………………. 6

4.1.   MySQL复制原理简介……………………………………………………………………………… 6

4.2.   sync_binlog…………………………………………………………………………………………… 6

5.    MySQL和InnoDB协同……………………………………………………………………………… 7

5.1.   两段式事务提交……………………………………………………………………………………. 7

5.2.   innodb_support_xa…………………………………………………………………………………. 7

 

 

1.  概述

很多企业选择MySQL都会担心它的数据丢失问题,从而选择Oracle,但是其实并不十分清楚什么情况下,各种原因导致MySQL会丢失部分数据。本文不讨论Oracle和MySQL的优劣,仅仅关注MySQL丢失数据的几种情况。希望能够抛砖引玉,让各位MySQL大牛们梳理出MySQL最安全或者性价比合适的适合各种应用场景的方案。

2.  问题定义

一般我们希望把一系列的数据作为一个原子操作,这样的话,这一系列操作,要么提交,要么全部回滚掉。

当我们提交一个事务,数据库要么告诉我们事务提交成功了,要么告诉我们提交失败。

数据库为了效率等原因,数据只保存在内存中,没有真正的写入到磁盘上去。如果数据库响应为“提交成功”,但是由于数据库挂掉,操作系统,数据库主机等任何问题导致这次“提交成功”的事务对数据库的修改没有生效,那么我们认为这个事务的数据丢失了。这个对银行或者支付宝这种业务场景来说是不能接受的。所以,保证数据不丢失也是数据库选择的一个重要衡量指标

mysql的架构和普通的数据库架构最大的差异在于它使用插件式的存储引擎。数据的存取由存储引擎负责。要了解MySQL数据丢失的问题就需要从MySQL server层和InnoDB目前最流行的支持事务的存储引擎分别来分析了。

 

3.  InnoDB事务数据丢失

首先,我们来看一下InnoDB事务数据丢失的情况。

3.1. InnoDB事务基本原理

InnoDB的事务提交需要写入undo log,redo log,以及真正的数据页。专业的介绍可以参考丁奇云华的两篇文章。我们这里通俗一点简单介绍一下。

InnoDB跟Oracle非常类似,使用日志先行的策略,将数据的变更在内存中完成,并且将事务记录成redo,转换为顺序IO高效的提交事务。这里日志先行,也就是说,日志记录到数据库以后,对应的事务就可以返回给用户,表示事务完成。但是实际上,这个数据可能还只在内存中修改完成,并没有刷到磁盘上去,俗称“还没有落地”。内存是易失的,如果在数据“落地”之前,机器挂了,那么这部分数据就丢失了。而数据库怎么保证这些数据还是能够找回来列?否则,用户提交了一个事务,数据库响应请求并回应为事务“提交成功”,数据库重启以后,这部分修改数据的却回到了事务提交之前的状态。

3.2. InnoDB事务崩溃恢复基本原理

InnoDB和Oracle都是利用redo来保证数据一致性的。如果你有从数据库新建一直到数据库挂掉的所有redo,那么你可以将数据完完整整的重新build出来。但是这样的话,速度肯定很慢。所以一般每隔一段时间,数据库会做一个checkpoint的操作,做checkpoint的目的就是为了让在该时刻之前的所有数据都”落地”。这样的话,数据库挂了,内存中的数据丢了,不用从最原始的位置开始恢复,而只需要从最新的checkpoint来恢复。将已经提交的所有事务变更到具体的数据块中,将那些未提交的事务回滚掉。

3.3. InnoDB redo日志

这样的话,保证事务的redo日志刷到磁盘就成了事务数据是否丢失的关键。而InnoDB为了保证日志的刷写的高效,使用了内存的log buffer,另外,由于InnoDB大部分情况下使用的是文件系统,(linux文件系统本身也是有buffer的)而不是直接使用物理块设备,这样的话就有两种丢失日志的可能性:日志保存在log_buffer中,机器挂了,对应的事务数据就丢失了;日志从log buffer刷到了linux文件系统的buffer,机器挂掉了,对应的事务数据就丢失了。当然,文件系统的缓存刷新到硬件设备,还有可能被raid卡的缓存,甚至是磁盘本身的缓存保留,而不是真正的写到磁盘介质上去了。这个就不在我们这次讨论的范围内了。

InnoDB的日志你还可以参考这篇文章

3.4. innodb_flush_log_at_trx_commit

所以InnoDB有一个特别的参数用于设置这两个缓存的刷新: innodb_flush_log_at_trx_commit。

默认,innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,数据库对IO的要求就非常高了,如果底层的硬件提供的IOPS比较差,那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。

为了提高效率,保证并发,牺牲一定的数据一致性。innodb_flush_log_at_trx_commit还可以设置为0和2。

innodb_flush_log_at_trx_commit=0时,每隔一秒把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,可能丢失1秒的事务数据。

innodb_flush_log_at_trx_commit=2时,在每次事务提交的时候会把log buffer刷到文件系统中去,但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处就是,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。MySQL 5.6.6以后,这个“1秒”的刷新还可以用innodb_flush_log_at_timeout 来控制刷新间隔。

在大部分应用环境中,应用对数据的一致性要求并没有那么高,所以很多MySQL DBA会设置innodb_flush_log_at_trx_commit=2,这样的话,数据库就存在丢失最多1秒的事务数据的风险。

引用应元的一个图如下:

innodb_flush_log_at_commit

4.  数据库复制导致数据丢失

MySQL相比其他数据库更适用于互联网的其中一个重要特性就是MySQL的复制。对于互联网这种需要提供7*24小时不间断的服务的要求,MySQL提供异步的数据同步机制。利用这种复制同步机制,当数据库主库无法提供服务时,应用可以快速切换到跟它保持同步的一个备库中去。备库继续为应用提供服务,从而不影响应用的可用性。

这里有一个关键的问题,就是应用切换到备库访问,备库的数据需要跟主库的数据一致才能保证不丢失数据。由于目前MySQL还没有提供全同步的主备复制解决方案所以这里也是可能存在数据丢失的情况。

目前MySQL提供两种主备同步的方式:异步(asynchronous)和半同步(Semi-sync)

4.1. MySQL复制原理简介

MySQL复制的原理简介如下:MySQL主库在事务提交时写binlog,并通过sync_binlog参数来控制binlog刷新到磁盘“落地”。而备库通过IO线程从主库拉取binlog,并记录到本地的relay log中;由本地的SQL线程再将relay log中的数据应用到本地数据库中。

异步的方式下,几个线程都是独立的,相互不依赖。

而在半同步的情况下,主库的事务提交需要保证至少有一个备库的IO线程已经拉到了数据,这样保证了至少有一个备库有最新的事务数据,避免了数据丢失。这里称为半同步,是因为主库并不要求SQL线程已经执行完成了这个事务。

半同步在MySQL 5.5才开始提供,并且可能引起并发和效率的一系列问题,比如只有一个备库,备库挂掉了,那么主库在事务提交10秒(rpl_semi_sync_master_timeout控制)后,才会继续,之后变成传统的异步方式。所以目前在生产环境下使用半同步的比较少。

在异步方式下,如何保证数据尽量不丢失就成了主要问题。这个问题其实就是如何保证数据库的binlog不丢失,尽快将binlog落地,这样就算数据库挂掉了,我们还可以通过binlog来将丢失的部分数据手工同步到备库上去(MHA会自动抽取缺失的部分补全备库)。

图示如下:

mysql_replication

4.2. sync_binlog

这个问题就跟上一个innodb_flush_log_at_trx_commit的问题类似了。MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。虽然binlog也有binlog cache,但是MySQL并没有控制binlog cache同步到文件系统缓存的相关考虑。所以我们这里不涉及binlog cache。

默认,sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。

如果sync_binlog>0,表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了,表示每次事务提交,MySQL都会把binlog刷下去。这样的话,在数据库所在的主机操作系统损坏或者突然掉电的情况下,系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO,但是设置sync_binlog=1,多个事务同时提交,同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解,但是刷新的频率过高对IO的影响也非常大。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1,而是100或者是0。这样牺牲一定的一致性,可以获得更高的并发和性能。

5.  MySQL和InnoDB协同

5.1. 两段式事务提交

最后我们需要讨论一下上述两个参数对应的redolog和 binlog协同的问题。这两个log都影响数据丢失,但是他们分别在InnoDB和MySQL server层维护。由于一个事务可能使用两种事务引擎,所以MySQL用两段式事务提交来协调事务提交。我们先简单了解一下两段式事务提交的过程

transaction_xa

第一阶段:

首先,协调者在自身节点的日志中写入一条的日志记录,然后所有参与者发送消息prepare T,询问这些参与者(包括自身),是否能够提交这个事务;

参与者在接受到这个prepare T 消息以后,会根据自身的情况,进行事务的预处理,如果参与者能够提交该事务,则会将日志写入磁盘,并返回给协调者一个ready T信息,同时自身进入预提交状态状态;如果不能提交该事务,则记录日志,并返回一个not commit T信息给协调者,同时撤销在自身上所做的数据库改;

参与者能够推迟发送响应的时间,但最终还是需要发送的。

第二阶段:

协调者会收集所有参与者的意见,如果收到参与者发来的not commit T信息,则标识着该事务不能提交,协调者会将Abort T 记录到日志中,并向所有参与者发送一个Abort T 信息,让所有参与者撤销在自身上所有的预操作;

如果协调者收到所有参与者发来prepare T信息,那么协调者会将Commit T日志写入磁盘,并向所有参与者发送一个Commit T信息,提交该事务。若协调者迟迟未收到某个参与者发来的信息,则认为该参与者发送了一个VOTE_ABORT信息,从而取消该事务的执行。

参与者接收到协调者发来的Abort T信息以后,参与者会终止提交,并将Abort T 记录到日志中;如果参与者收到的是Commit T信息,则会将事务进行提交,并写入记录

一般情况下,两阶段提交机制都能较好的运行,当在事务进行过程中,有参与者宕机时,他重启以后,可以通过询问其他参与者或者协调者,从而知道这个事务到底提交了没有。当然,这一切的前提都是各个参与者在进行每一步操作时,都会事先写入日志。

具体的介绍可以参考《事务和两阶段提交》以及《分布式事务设计-两阶段提交》

 

5.2. innodb_support_xa

innodb_support_xa可以开关InnoDB的xa两段式事务提交。默认情况下,innodb_support_xa=true,支持xa两段式事务提交。此时MySQL首先要求innodb prepare,对应的redolog 将写入log buffer;如果有其他的引擎,其他引擎也需要做事务提交的prepare,然后MySQL server将binlog将写入;并通知各事务引擎真正commit;InnoDB将commit标志写入,完成真正的提交,响应应用程序为提交成功。这个过程中任何出错将导致事务回滚,响应应用程序为提交失败。也就是说,在这种情况下,基本不会出错。

但是由于xa两段式事务提交导致多余flush等操作,性能影响会达到10%,所有为了提高性能,有些DBA会设置innodb_support_xa=false。这样的话,redolog和binlog将无法同步,可能存在事务在主库提交,但是没有记录到binlog的情况。这样也有可能造成事务数据的丢失。

 

综上,我们列举了影响InnoDB数据丢失的参数innodb_flush_log_at_trx_commit,影响MySQL复制数据丢失的sync_binlog,以及由于MySQL和InnoDB需要协调而可能导致数据丢失的参数innodb_support_xa。

InnoDB plugin 1.0手册翻译

之前和团队的同学一起翻译了部分InnoDB Plugin 1.0的手册,但是一直没有最终完稿。

利用周末的时间,我把剩下来的几章翻译完成并放到公司官方网站,希望大家一起来审阅一下。

这里也非常感谢彭立勋,胡中泉,陈良允同学。彭立勋主要翻译完成了1-7章,胡中泉,陈良允对文档进行了审阅。这些翻译文档也是取得了这些同学的同意才发表在公司博客网站的。

下面列出所有翻译文档的链接,请各位大牛批评指正(请直接在沃趣科技博客中评论):

第一章:InnoDB Plugin简介
第二章:在InnoDB存储引擎下快速创建索引
第三章:InnoDB 数据压缩
第四章:InnoDB文件格式管理
第五章:可变长度列(Variable-Length Columns)的存储
第六章:InnoDB的INFORMATION_SCHEMA数据表
第七章:性能优化及可扩展性的提高
第八章.灵活性、易用性、可靠性改进
第九章. 安装InnoDB Plugin
第十章.升级InnoDB Plugin
第十一章.从InnoDB Plugin降级
第十二章. InnoDB Plugin变更历史
附录A.第三方软件
附录 B.在5.1.30及之前版本MySQL使用InnoDB Plugin
附录 C. InnoDB Plugin 1.0参数修改列表

 

另外,由于本人时间有限,本来这些文档需要转换为docbook格式提交给Oracle官方文档的team的,如果哪位同学有兴趣可以一起参与进来。

远离故障的十大原则

 

故障是运维人员永远的痛。相信每一个运维人员的KPI中都有一项:可用性。可用性高就是不出故障,各个公司对可用性和故障评级的标准都不相同,但是避免故障的方法却是殊途同归。我们怎么避免故障,沃趣科技简单列举了以下几条,与大家共勉!
1、变更要有回滚,在同样的环境测试过
2、对破坏性的操作谨慎小心
3、设置好命令提示 
4、备份并验证备份有效性
5、对生产环境存有敬畏之心
6、交接和休假最容易出故障,变更请谨慎
7、搭建报警,及时获得出错信息。搭建性能监控,了解历史,获得趋势,预测未来
8、自动切换需谨慎
9、仔细一点,偏执一点,检查,检查,再检查
10、简单即是美。

 

第1条,变更要有回滚,在同样的环境测试过。也是运维最繁琐,最苦逼的地方,所有的变更都必须有回滚的办法,在同样的环境下测试过。没有做过的东西,总是会在你意想不到的地方给你一次痛击,在阿里巴巴的这么多年运维经验告诉我们,所有没有做过的变更,出错的概率最大。所以我们需要给变更以回滚的可能,在各个步骤可能出错的情况下,考虑回滚到最初状态。优秀的运维人员对不考虑回滚的的操作都是敬而远之的。从某种意义上来说,运维是一门经验的学科,是一门试错的学科。

 

第2条,对破坏性的操作谨慎小心。破坏性的操作有哪些列?对数据库来说有:DROP Table, Drop database, truncate table, delete all data;这些操作做完了以后几乎无法考虑怎么把数据都回滚回去了。就算回滚,代价也是非常大的。你执行这样的语句非常简单,但是回滚恢复数据缺非常困难。linux的命令rm可以-r(recursive)递归的删除某一个目录,-f(force)强制删除,但是你有没有删错过文件。我们遇到过一个文件名中末尾有空格的情况,而有的同事rm -r习惯性的会在文件名后面加*,这样就成了rm -r aa *,所有当前目录的数据都被删除掉了!经过这次故障以后我们给rm做了别名:
alias rm=’rm -i’
这样在删除数据时,rm命令会提示你,是否确认删除该文件。
同样的cp和mv也可以有同样的选项:
alias cp=’cp -i’
alias mv=’mv -i’

 

第3条,设置好命令提示。让你时刻知道你在操作哪个数据库,让你知道你在哪个目录下。mysql字符客户端允许你设置提示符,默认的提示符就是一个光秃秃的mysql >,为了让你清楚的知道你当前是以哪个用户名,哪个IP(可能是localhost,127.0.0.1或者具体的物理IP),你当前操作的是哪个schema,以及当前的时间,你可以设置数据库的提示符为:prompt=”\\u@\\h : \\d \\r:\\m:\\s> “。它可以直接写在my.cnf的[mysql]下,这样你每次连上MySQL就默认显示如下:
root@127.0.0.1 : woqutech 08:24:36>
具体prompt可以设置哪些提示,你可以参考http://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html中的列表
而linux命令提示符也允许你设置的。有两个地方可以设置。第一个:PS1。这个是每次shell提示你输入命令的信息,默认为:$或者#,只会提示你是超级用户还是普通用户。有经验的运维者会设置export PS1=’\n\e[1;37m[\e[m\e[1;31m\u\e[m\e[1;31m@\e[m\e[1;31m\h\e[m \e[4mpwd\e[m\e[1;37m]\e[m\e[1;36m\e[m\n\$’。这样你就可以知道你当前的目录,登录的用户名和主机信息了,示例提示符如下:
[root@woqu-lsv-01 /home/mysql]
#
你可以查看http://www.cyberciti.biz/tips/howto-linux-unix-bash-shell-setup-prompt.html获得具体的PS1设置颜色,设置各个提示内容的介绍。
第二个提示符就是PROMPT_COMMAND。这个是设置你连到具体的数据库以后标签页标题上显示的内容,Windows用户可能会用securtCRT,Mac用户可能会用iTerm2,开多个标签页的话,如果每个标签页的标题上内容一样,我们切来切去就有可能在错误的标签页上做操作,设置了这个以后,这个问题概率就会小很多。比如我们的机器上设置为PROMPT_COMMAND=’echo -ne “\033]0;${USER}@${HOSTNAME%%.*}”; echo -ne “\007″‘对应的标签页如下图

prompt_command

 

 

 

 

第4条,备份并验证备份有效性。是人总会出错,是机器总可能会有突然崩溃的那一天。怎么办-我们需要准备备份。

备份的学问很大。按照不同的纬度可以分为:冷备份和热备份;实时备份和非实时备份;物理备份和逻辑备份。

互联网企业为了提供7*24小时不间断的服务,数据库就需要有实时热备份。在主库出现问题的情况下能够由备库提供服务。备库时候有效,数据是否一致,主库出现问题的时候怎么切换都需要运维人员认真考虑。

是不是有了这些就够了列?不行,应用程序也是人写的,曾经出现过程序一不小心delete语句没有带任何条件,导致一个表中所有的数据都被删除的惨状。所以你除了实时的备份,还需要有非实时的备份,在你的数据出现逻辑错误之后能够从备份数据中恢复出来。现在很多人在研究MySQL模仿oracle的flashback功能,利用binlog来恢复数据。但是这样的话,binlog_format必须设置为row并且对于DDL操作也无法回滚。它是为快速解决部分数据被错误删除的解决方案,但是无法代替非实时备份的作用。

非实时备份有可以分为在线延时备份和离线备份。在线延时备份是搭建数据库的一定时间延迟的热备份,比如MySQL就可以搭建一个延迟一天的slave,一直保持着备库与主库的延迟在一天。可以利用pt-slave-delay工具来实现这个功能。另外,离线备份是目前大家用的比较多的,可以利用mysqldump进行逻辑备份或者xtrabackup进行物理备份。为了空间的原因和快速恢复考虑,你还可以利用xtrabackup进行增量的物理备份。

备份有了,是否就可以高枕无忧了?还是不行。你需要验证备份的有效性。没有一个备份能够保证它备份出来的数据能够100%恢复出正确的数据,特别是物理备份的概率相对来说,更低,xtrabackup备份一个月总有那么几次来大姨妈,不能给你很好的服务。所以,备份并不只是备份,它还包括备份的验证,它如果不能恢复出正确的数据,就只是浪费空间而已。备份的验证最简单的就是找一个空闲的库,来恢复出来,mysql启动以后检查部分数据。如果不需要这么严谨,对于xtrabackup来说,你至少得验证它–apply-log能够恢复上去吧?同样,备库的数据一致性也需要经常检查一下,mysql的replication并不保证100%的数据一致性,你可以去翻翻mysql statement复制的bug列表,有些数据在主备不同的环境上分别执行,数据就会不一样。可以考虑用percona的工具pt-table-checksum来检查主备不一致,用pt-table-sync来同步主备数据。

 

 

第5条,对生产环境存有敬畏之心。这应该是运维者进入行业首先需要具备的素质。但是我们还是需要把它拿出来强调一下。

有机会的话,你可以梳理一下:

  • 你的生产环境上有哪些账户,这些账户是否都确实需要登录到机器上来?这些账户即包括linux用户还包括数据库账户。
  • 你的root用户是否开放给了某些用户,这些用户安全吗?
  • 你的用户密码是否经常修改,是否加密不让具体的操作人员直接看到,密码强度时候足够,密码重试次数达到一定次数是否黑名单;
  • 你的生产环境和线下环境是否隔离,数据库是否和外网隔离?
  • 是否一些工作明明能够在开发库和测试库做,却被放到生产环境上去了。
  • 是否有专门的人负责线上应用的发布,从而避免开发人员直接接触生产环境

这些都是你避免出现csdn密码泄漏,在业界的名声一落千丈的法宝。

 

 

第6条,交接和休假最容易出故障,变更请谨慎。这个是经验之谈。我们在总结故障的情况时,发现在公司部门有变化时,工作交接(不管是休假,工作职责变化还是离职),故障的出现频率会比正常情况下多50%以上。有人说,这是因为机器或者应用是有感情的,舍不得离开的运维者。

我们不谈感情,简单的理性分析一下。公司或者部门难免会做一些调整,变化是世界上唯一不变的事情。而运维人员是一线做事情的人,部门调整或者领导的更换可能导致工作的着重点不同,做事的方式和评测的标准变了,适应过程中难免会出现一些考虑不周到的地方,出故障也是情理之中了。

而工作交接,对运维人来说,其实是一个非常费时费力的事情,你需要把所有平常做的工作都梳理清楚,甚至包括你的一些经意不经意的操作习惯,这样的话,下一个人才可能接手的下来。比如:你可能认为备库正常情况下没有访问,于是让某些并不重要的任务(一个月一次抽取部分数据到线下测试?)直接连备机IP进行操作。下一个人接手,认为备机就是备机,操作起来不会有任何问题,结果下一次任务抽取就是一个故障出来了。再举一个我们遇到了事例吧:同事A出国休假了,休假期间估计联系不上,他留了文档,并告诫说某几个库和表是比较核心和容易出问题的,没有特殊情况最好等他回来再做变更。正好,休假期间,开发人员找到同事B,要求他重置一个字段的某一位(bit),并打包票说这个bit没有用,同事B拒绝,并背上了不配合的骂名。同事A回来吓了一身冷汗,原来这个字段已经被另外一个离职的开发使用了。

所以,运维部门和运维人员对变化需要尽量放平心态;接手别人的工作要一而再,再而三的确认变更方案。请教人并不见得就是能力不行的表现;休假前最好各种可以做好的事情,最好能够准备一份文档,指明在什么情况下怎么做和联系哪些人。在别人放假的时候接手工作,“能拖则拖”,实在需要执行:必须不厌其烦的跟原运维者确认各个操作细节。 

 

 

第7条,搭建报警,及时获得出错信息。搭建性能监控,了解历史,获得趋势,预测未来。运维的最高境界不是故障来了,泰山崩于前而不惊,苍老师勾引你而抗日;而是没有故障,让故障消失在萌芽之中。请给那些默默无闻,每天想着我们的系统还存在哪些隐患,怎么解决,怎么及早发现的运维人员鼓掌。他们是最可爱的人。而他们赖以生存的工具就是报警和监控。Oracle发展了这么多年,awr和相关的性能参数都相对比较全;MySQL现在也已经迎头赶上,配套的工具越来越多。

报警可以让你及时知道系统出现了什么异常。比如slave io报警,在数据库replication异常的时候就会提醒你:IO线程出现了问题,可能是网络问题,主数据库问题等,slave sql报警会提醒你replication的SQL线程出现了问题,可能是主备不一致,slave被停掉了,存储过程在备机有异常或者其他问题。这样你收到报警就可以及时跟进,而不至于主备长时间不一致,主库坏掉了想要切换到备库的时候却不能切换。

性能监控可以让你了解系统的历史性能信息。分析故障发生时的各种现象,确认故障的真正原因;了解变化趋势,发现故障的苗头,及早优化和调整。比如你如果使用了PCI-E的Flash卡,你可以监控logical_written_bytes,logical_read_bytes,physical_written_bytes,physical_read_bytes以便获得flash卡的每秒的逻辑读写和物理读写字节数。对于MySQL你可以监控Com_delete+Com_delete_multi, Com_insert+Com_insert_select,Com_update+Com_update_multi,Com_select来获得每秒的MySQL DML删除,插入,更新和查询的次数。

报警和性能监控其实不不完全独立的,很多性能的监控项也可以报警出来。比如linux的iostat中的await_time可以作为性能监控采集起来获得系统IO响应时间的变化曲线,当该值达到20以上的时候,也可以报警出来,让运维人员跟进是磁盘阵列中坏了一块,还是异常的数据拷贝影响了系统的IO性能等。

nagios和cacti是目前MySQL领域使用最广泛的报警和性能展示系统。percona最新推出percona-monitor-plugins(http://www.percona.com/software/percona-monitoring-plugins)就是基于他们俩的。

 

 

第8条:自动切换需谨慎。现在数据库的HA很多都是进行自动切换的,这样运维人员深夜起来手工切换到备库的机会就会少很多。切换也会快速很多。但是,它带来的副作用也不容忽视。

现在业界使用的HA软件非常多,heartbeat由于很多SA兼作DBA的运维比较熟悉,在MySQL自动切换也是不少的。一般来说,它会通过mysqladmin ping来探测MySQL是否存活,如果发现异常,那么他就会切换VIP和MySQL资源到备库。但是此时备库的数据延迟是否为0,主库crash之后binlog的数据是否全部都同步到备库上去了,备库的read_only是否关闭,这些heartbeat都不管。我们想象一下,主库上应用提交了一笔订单,结果发生了切换,这笔订单没有同步到备库上,卖家也就损失了一个销售单,对客户,对公司都是非常大的影响。

当然,自动切换也不能全盘否定,它能够更快速的将应用切换到新的热备份备库上,应用的不可用时间大大缩短。只是我们要好好利用这一把双刃剑,仔细评估它的影响,降低或者去除副作用,让它为我们服务。

 

 

第9条,仔细一点,偏执一点,检查,检查,再检查。之前我跟一个资深的运维学习线上操作的时候,觉得这家伙有点变态,他在做一个变更的时候,会先提前一两周发送邮件并电话手机的通知相关人;在测试机上写好脚本,召集大家review操作步骤和脚本;测试完成以后拷贝到生产环境;登录对应机器,“打开,关闭,打开,关闭”该脚本;跟相关人员再次确认执行的操作,顺序,时间点,可能的影响和回滚是否都准备好了;执行前还要退出这个机器,然后再登录进去,“打开,关闭”脚本;最后才在后台运行脚本,在另外一个窗口登录着,随时ps和查看结果输出。期间姿势端正,呼吸急促而均匀,眼神凝重。操作的人不觉得累,倒是一边学习的人很累。

当我做到一定程度,我也开始这样了。医学上,这种好像叫做强迫症。唉…,提前通知会让大家都有准备,也避免了临时相关人员过来说这个操作和其他操作有依赖需要调整操作时间的问题; 召集大家review步骤和脚本是为了让大家一起来看看整个过程中还有哪些依赖没有考虑到或者哪些细节没有注意到,三个臭皮匠顶一个诸葛亮在运维来说是金科玉律;“打开,关闭,打开,关闭”是为了一再确认脚本拷贝过来是否正确,目录时候正确,思考在测试环境运行和在生产环境运行有什么不一样的;退出再登录机器是为了确认我登录的机器确实没有错;在后台运行是担心网络突然中断,我的脚本运行到一半怎么办;调整呼吸和端正姿势是为了对这个操作的敬重,对自己工作和运维工作的尊重。

以MySQL 使用flash卡为例吧。flash算是一个比较新的事务,提供的IO比普通磁盘是几个数量级的提升。要想在生产环境使用,首先我们需要对他进行详尽的评估和破坏性测试,设置各种参数,考虑他们在各种场景下使用的配置;24小时不间断的进行半个月读写操作,中途突然掉电;高并发,高吞吐量下的测试;温度湿度极限测试;预留空间释放测试等等。然后我们会尝试在测试库上部署试用,收集和修改各个配置已达到最稳定,最高性能的配置;运行稳定以后我们才考虑在线上备库使用,并且主备要求异构;适当的时机切换为使用新的flahs卡为主库,万一出现了问题,还可以切换回原主机。

这里也跟大家简单介绍一下screen命令,这个命令会在服务器段开启一个session,就算你的网络断掉了,你的脚本也会自动在后台运行。screen -S woqutech可以开启一个woqutech命令的后台session;如果你的网络断掉了,你可以用screen -dr woqutech连上之前的session继续进行操作。IBM的文档库中有一个非常靠谱的文档:http://www.ibm.com/developerworks/cn/linux/l-cn-screen/

 

第10条,简单即是美。最后一条有点禅的意境了。它和Unix的思想不谋而合。我们总是面临着各种诱惑:新的系统架构,新的更智能的命令和工具,最新的硬件平台,功能更全的HA软件等。他们总是以各种各样的方式吸引我们,most exciting,unbelievable,让你欲罢不能。你可以在线下安装,测试,怎么搞都行。但是如果想要在生产环境下使用起来,那就得经过非常详细,非常漫长,各种方式验证其稳定性的过程。

能够使用系统内置命令的话,就不用考虑其他要专门下载安装的软件了;脚本本身就能完成的功能,就没有必要专门找一个功能丰富的软件来做;linux本身自带的字符界面比那些复杂的图形界面要简洁方便;MySQL的一些分区,生僻函数,没有必要的话不要使用。

 

最后祝大家运维的运维工作一帆风顺,多福多寿,不出故障。

参考:http://feedproxy.google.com/~r/iheavy/~3/sRnyFPA0R9E/

淘宝物流MySQL slave数据丢失详细原因

前两天,惊闻淘宝发生了一个非常狗血的事情,备库复制状态一切正常,但是备库的数据DDL可以复制过去,DML都丢失了。导致数据库数据不一致。这样的话,就算你有slave监控,也发现不了主备数据延迟和不一致的问题。

最后淘宝希羽定位到了问题,并提出了解决方案。参考http://hickey.in/?p=146。不过具体原因和为什么会发生这个问题并没有说的那么详细。沃趣科技特别针对这个问题做了深入详细的研究,整理出来,以飨观众。

首先我们需要跟大家解释一下MySQL复制的基本原理。

主库为每一个slave开启一个binlog dump线程,用于把本机记录下所有的变更,发送给备库;备库使用io thread线程接收数据存入relay log中;然后由sql thread线程从relay log中读出来应用到本地。这个是大家都熟知的。我们不详细介绍。但是slave是怎么注册上主库,主库是怎么通知各个binlog dump线程,binlog dump和io thread线程怎么通讯,io thread怎么重连,relay log怎么读取二进制数据翻译成对应的信息应用在slave上;我们就不得而知了。而问题就发生在最后“relay log怎么读取二进制数据翻译成对应的信息应用在slave上”

binlog是二进制数据,必须用mysqlbinlog工具才能打开。所以我们有必要先介绍一下binlog的格式。也就是insert,update,delete等这些数据是怎么以二进制形式记录到binlog文件中去的。binlog文件是按照event来组织的。每个文件前4个字节是fe 62 69 6e,接下来就是各个event了。event有很多种类型。列出如下:

比如ROTATE_EVENT对应的记录了binlog切换到下一个binlog文件的信息,XID_EVENT记录了一个事务提交的相关信息。Binlog_format可以设置为STATEMENT和ROW的方式。当设置为STATEMENT情况下,DML会记录为原始的SQL,也就是记录在QUERY_EVENT中。而ROW会记录为TABLE_MAP_EVENT+ROW_LOG_EVENT(包括WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT)。使用mysqlbinlog可以看看他们的区别。

STATEMENT方式下,记录为QUERY_EVENT如下图:

statement_event

 

 

 

 

 

ROW方式下,update一条记录如下:

row_simple

 

 

 

 

这样的话我们就无法看到它到底update了什么数据,使用mysqlbinlog -vvv可以让它更详细的翻译给我看:

row_vvv

 

 

 

 

 

 

 

淘宝采用的是ROW方式,有两个好处:第一:更容易解析,DRC或者mysql transfer等淘宝系数据库迁移工具可以精确的解析出数据,进行同步;第二:可以有效避免rand(),uuid()等由于主备环境不一致而导致的问题。

这里还有一个地方需要解释一下,为什么一个update在ROW模式下需要分解成两个event:一个Table_map,一个Update_rows。我们想象一下,一个update如果更新了10000条数据,那么对应的表结构信息是否需要记录10000次列,其实是对同一个表的操作,所以这里binlog只是记录了一个Table_map用于记录表结构相关信息,而后面的Update_rows记录了更新数据的行信息。他们之间是通过table_id来联系的。

淘宝的问题也就出现在这里,这两个事件是通过table_id来联系的,table_id是ulong类型的。刚好这个联系在淘宝的这个环境下就断了。具体的细节要牵涉到部分源码。对源码不感兴趣的同学可以直接跳过这一段:

首先,我们了解一下记录表定义信息的数据结构。它对应的class是Table_map_log_event(对应源码sql/log_event.cc),它保存了本次DML操作对应的:table_id,数据库名,表名,字段数,字段类型等。对应的这些信息都是保存在一个table_mapping的hash数据结构中(sql/rpl_tblmap.cc中)。hash的key就是table_id,hash的值就是TABLE*的数据结构(包含了表的各种信息,包括数据库名,表名,字段数,字段类型等),通过set_table()方法来hash,通过get_table()方法来根据table_id获得对应的表信息。这里table_id是ulong型的。

然后我们了解一下存储变更的具体数据的数据结构。update行的Update_row event对应的class是Update_rows_log_event(对应源码sql/log_event.cc),基类是Rows_log_event(之类分别有:Write_rows_log_event,Update_rows_log_event,Delete_rows_log_event与insert, update, delete一一对应)。一个Row_log_event对应一行数据的变更(插入,更新,删除),它记录的信息包括table_id,哪些字段为空的bitmap,各个字段的具体数据等。这里table_id是ulong型的。

所有的binlog event有一个公共的父类Log_event(对应源码sql/log_event.h),每一个log_event都是通过do_apply_event()方法来将event应用到本地数据库去。

另外,我们必须要介绍一下RPL_TABLE_LIST结构(对应源码sql/rpl_utility.h),它的父类TABLE_LIST(对应源码:sql/table.h)中定义table_id为:

  uint          table_id; /* table id (from binlog) for opened table */   //这里就是最终需要bug fix的地方

这里定义为uint和其他地方定义为ulong不一样!

这里就是最终需要bug fix的地方。问题的原因我们还要继续看。这个RPL_TABLE_LIST是包含在Relay_log_info结构(对应源码sql/rpl_mi.h)中的,它记录了这次变更需要lock的多个表信息。

 

前面提过,每一个event都有一个do_apply_event()方法用于将event应用到本地数据库中去。int Table_map_log_event::do_apply_event(Relay_log_info const *rli)方法(对应源码sql/log_event.cc)中就将ulong型的m_table_id赋值给uint型的table_list->table_id,而table_list作为tables_to_lock存入了公共变量rli中。

table_list->table_id= DBUG_EVALUATE_IF(“inject_tblmap_same_id_maps_diff_table”, 0, m_table_id);

/*
We record in the slave’s information that the table should be
locked by linking the table into the list of tables to lock.
*/
table_list->next_global= table_list->next_local= rli->tables_to_lock;
const_cast<Relay_log_info*>(rli)->tables_to_lock= table_list;
const_cast<Relay_log_info*>(rli)->tables_to_lock_count++;
/* ‘memory’ is freed in clear_tables_to_lock */

Row_log_event类的int Rows_log_event::do_apply_event(Relay_log_info const *rli)方法(对应源码sql/log_event.cc)中:

TABLE_LIST *ptr= rli->tables_to_lock;
for (uint i=0 ; ptr && (i < rli->tables_to_lock_count); ptr= ptr->next_global, i++)
const_cast<Relay_log_info*>(rli)->m_table_map.set_table(ptr->table_id, ptr->table);

利用记录在rli(Relay_log_info结构)中的tables_to_lock获得table_list(RPL_TABLE_LIST结构),而这个结构里面的是已经被截断的uint型的table_id。

当需要对具体的表进行row变更的时候在同样的int Rows_log_event::do_apply_event(Relay_log_info const *rli)方法(对应源码sql/log_event.cc)中:

TABLE*
table=
m_table= const_cast<Relay_log_info*>(rli)->m_table_map.get_table(m_table_id);

DBUG_PRINT(“debug”, (“m_table: 0x%lx, m_table_id: %lu”, (ulong) m_table, m_table_id));

通过ulong型的值去获得hash表中获得表结构信息就无法获取了。也就是说,之前用uint型的ptr->table_id构建出来的key,value的hash对,用ulong型的m_table_id是无法查询到的。

图示如下:

tableid_bug

 

 

 

 

 

 

 

 

 

 

为了举例简单,假设uint 4 bit ulong 8bit,ulong11111111被存到了uint型的数据中(假设被截断为00001111)并存到hash表中去了,那么对应的ulong型的key去查数据时,插到的表定义肯定就是NULL了。这样的话,表结构找不到,备机也就无法同步主库的任何DML数据,也就是淘宝物流库备机与主机不一致的原因了。

 

那么是什么原因导致的这个问题列。淘宝是因为它的table cache设置过小,table_definition_cache为256,table_open_cache为512,而该实例上由于分库分表,表一共有4301个,table cache严重不足。这里很多人对table_id有误解,认为table_id是跟表一起走的,是固定的。其实table_id是表载入table cache时临时分配的,一个不断增长的变量。当table cache不足,flush table又非常多的时候,这个table_id增长的速率非常快,达到uint的上限时,2的32次方以后,就触发了这个bug,导致主备不一致。广大的MySQL使用者都留意一下,你的table_id是不是也非常大了,超过2的32次方,那么你的备机就重搭吧。

解决方案:

1、自己打patch,重编译并替换线上MySQL

2、增加table cache 大小。

3、重启主库让table_id归零。

 

参考:

http://hickey.in/?p=146

http://hatemysql.com/2011/12/14/mysql-show-slave-status/

http://dev.mysql.com/doc/internals/en/binary-log.html