Archive for 五月 2010

宇宙随想

相信大家都曾经想过,宇宙到底是怎么样的列。宇宙到底有多大。宇宙的起源是啥。
其实老子早就说过,无极生太极,太极生两仪,两仪生四相,四相生八卦,八卦生六十四卦。他说,世上其实本来是啥都没有的。这个要靠想象里的阿,啥叫啥都没有。那是啥状态?然后啥都没有生出了一个啥,这个啥叫什么我们就不追究了,然后一生二,二生四,四生八,就像以前的小学数学题算兔子数一样,生生不息。这个跟宇宙大爆炸理论好像也有一点类似哦。跟能量守恒定律就相悖了。不过跟姑且不论它吧,凭我的智商,要想通这个是咋回事,那就一个字:下辈子吧。
不过有一个地方,俺们还是有我的想法的。那就是宇宙到底有多大。我算是一个悲观主义者,所以我认为宇宙是无限大的,就算人类找到了宇宙的所谓的边界,你可以把边界外面的东西不叫宇宙吗?但是宇宙也是无限小的,记得我第一次学习化学的时候,了解到原子,质子,电子,强子,中子的简单介绍以后,我就认为这不就是一个简单的小宇宙吗,或者说是一个星系。如果科技发达的话,是否可以稳定住一颗一颗的小颗粒。(原谅我,估计就是因为化学课上想这些东西想多了,化学成绩很差,我也没有想明白,到底这个所谓的小颗粒是指电子,强子,中子,还是比他们更小的单位。)然后一颗一颗的去观察,看看哪一颗上有类似生命的迹象。也许他们的科技不发达,还在过着茹毛饮血的生活,也许他们的科技比我们还发达,早知道我们这样非常非常大的大块头脑袋里想些什么列,他们是否能够在他们的星球上也能够发现他们的原子,中子,质子…,然后还有比他们更小跟迷你的生命形式。这样也就算永无止境了。不过,有我这样的想法的人,应该是大有人在的。网上搜索了一下,发现了量子几何,超弦理论。最后,找到了霍金,重新认识了霍金,看样子有机会需要拜读一下他的《时间简史》《果壳中的宇宙》(the Universe in a Nutshell),呵呵,还发现有《时间简史》的电影版,不错,不错,好好学习一下。不过希望以我有限的智商能够稍微看懂一点这些书,理解一二。

我的blog之旅

从毕业到现在也有一些年头了,一直想把一些了解过知识和东西保留下来,而保留方法就是blog。
毕业之初,啥都不会,只能是看,学,简单的自己实践。于是就有了pickup112.bulog.cn的博客。这个是一个免费的,现在已经关闭。今天看到可以申请博客资料备份,赶紧申请了一下。希望还有尸体在。pickup112.bulog.cn基本上全部是摘录和转载的网上自认为比较好的文章,也懒得自己写些什么东西,写出来的东西估计也没有啥技术含量。我还记得第一篇blog是拷贝的sar的用法。后面陆陆续续拷贝粘帖了很多文章,受益匪浅,让我了解了很多,都说大学里面刚刚出来的都像海绵一下,可以吸收很多很多知识,是学习和积累的最好时期。这个一点也不错阿。当然,好的,不好的,都会吸收。非常庆幸,工作和学习中有很多良师益友,在这个期间我了解的东西很广,不仅是技术,人文地理,经济生物,无一不足。
最后发现,这样很没有成就感,都是拷贝粘帖,都是别人的东西,自己也没有心情打理,也不会想办法提交自己blog的点击度,自己也不会一遍两边的重复看。于是就懒了,不想管这个blog。但是知识还是需要积累的,到时候遇到一个问题想起以前看到的文章却找不到,那个心急如焚阿!于是,想到了收藏夹的功能。这个功能是蛮好的,你收藏在那里,有空可以翻看翻看,遇到问题打开那个网页重温一下,也许问题就可以解决掉。但是,有个问题,家里的电脑和公司的电脑收藏夹不统一。于是,当时自己就想做一个网络上的收藏夹的工具,因为自己对网页类型的开发一直不擅长,所以也就搁置了。偶尔的机会发现,百度的这个功能已经做好了,叫做“百度搜藏”。赶紧用起来,右键点击一下,就可以把网页搜藏下来,本地的收藏也可以导入进去。功能非常好,而且可以利用百度的强项,在我自己的收藏夹里面搜索相关内容。这个阶段我会把收藏在本地保存一份,也会在百度搜藏里面保存一份。虽然麻烦一点,但是还是有意义的。
人总是不想拘泥于现状的,这个也是人类社会一直进步的源泉。于是,我发现了firefox的xmark插件。这个插件好啊,它可以自动给你同步收藏夹,只要你注册了一个账户。于是百度收藏我也慢慢的不怎么用了。可悲的是,用不了多久,我们伟大的墙就把xmark封掉了。当时中国上下五千年的奴隶和封建社会,积累下来的智慧是可怕的,很快,我们就找到了新的同步收藏夹的功能,firefox 3.0的weave,这个功能和xmark一样,可喜的是,它没有被墙。这个东西好,我们一直都用它:)。不过,娘的,firefox现在启动贼慢,也许哪一天要考虑换chrome了。说起同步数据,想起了我可怜的dropbox,又被墙了,唉,不说了。
之前的一个阶段感觉都是为了给自己解决问题,为工作而积累一些知识。对自己的成长和帮助确实不小,但是总觉得不够。进入阿里巴巴以后,遭遇了第一次挫折,还好有人救赎。我开始认真审视自己的做人,做事方式,也看了一下成功者的传记。我发现我所羡慕和想成为的有两种人。一种是技术实力非常牛的,在他和他相关的领域中,没有他搞不定的事情,任何一件事情,你叫给他,一个字:放心。另外一种,这种更加牛逼,就是真正的管理者,他让第一种人有充分发挥自己的平台,放心的交付事情给他们,为他们争取资源,让他们为他卖命。他自己不见的看上去有多大的能耐,也不见得多么不可接近。有一个比喻,叫做一只猪带领着一群老虎。其实这只猪才是真正的智者和能者。这两种人有一个一些共同的特点,其中一个就是:他们都会share自己。他们不会固步自封,他们不会局限于自己懂得那么一点,他们对知识的追求,对自己这个领域的探究没有止境,他们不怕犯错,就怕不进步,他们会好不吝啬的把自己所知道的,所了解的,或者辛苦探究的成果欣喜的分享出来,他们相信,只有相互共享,相互促进,自己的进步才会是真正的进步。他们厌恶保守,厌恶孤独,厌恶小圈子。听起来有点像linux的GPL协议哦。
于是,我开始分享,开始多说话表达自己的观点,开始不怕犯错,开始不仅学习同时也思考,开始学着营销自己,开始进步了。而在团队和小团体内部的分享当然不够,最终有了hatemysql.com,争取更大范围的学习,思考和分享。写博客其实并不轻松,别人看电视,喝茶,逛街的时间你只能在十几寸的屏幕前敲字,但是看到自己思考的成果,偶尔对别人有用,或者偶尔引起一些人的共鸣,也是一大快事,聊以自慰吧。这个自慰不是双手。

may you success.

fedora下重用ssh连接

本人用的fedora 12操作系统,终端里面,ssh登录到一台机器,重复登录的话,你还是要输入密码。有没有像secureCRT一样的clone session的方式一样重用同一个连接,不用输入密码列。
从sa同事那边学到了一招,不敢独享。

其实做起来非常简单:
在你的登录账户下的.ssh文件夹新建一个文件:config.
cd ~/.ssh

config的文件中,内容为:
host *
ControlMaster auto
ControlPath ~/.ssh/master-%r@%h:%p

重新打开终端,第一次,你还是需要输入密码,第二次ssh登录同一台机器,你就不用输入密码了,跟secureCRT一样的clone session的方式一样。
这个文件的属性我这边设置为600的,一般不设置该属性也没有问题的。
ubuntu的同学也可以这样用,毕竟这个是ssh的客户端配置文件。
如果你细心的话,你在~/.ssh/目录下发现master-*的sock文件。它记录了你目前登录到的机器,这样的话,你登录同样的机器就会重用同一个链接了。

config文件的详细介绍可以参考:
http://linux.die.net/man/5/ssh_config

may you success.

loose- my.cnf参数prefix

发现一个有意思的参数prefix: –loose-
我们平常使用mysqlbinlog经常会出现错误:
mysqlbinlog: unknown variable ‘default-character-set=utf8’
说不认识这个参数,网上找了一下,发现只要修改my.cnf的client组里面的default-character-set = utf8为loose_default-character-set = utf8,mysqlbinlog就可以正常工作。MySQL5.0和5.1都适用。
这个prefix其实就是让读取该参数的程序不要那么死心眼,如果这个参数自己不解析,那么就当它不存在吧。

拷贝一下MySQL的reference:
4.2.3.2. Program Option Modifiers

Some options are “boolean” and control behavior that can be turned on or off. For example, the mysql client supports a –column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms:

–disable-column-names
–skip-column-names
–column-names=0

The –disable and –skip prefixes and the =0 suffix all have the same effect: They turn the option off.

The “enabled” form of the option may be specified in any of these ways:

–column-names
–enable-column-names
–column-names=1

If an option is prefixed by –loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning:

shell> mysql –loose-no-such-option
mysql: WARNING: unknown option ‘–no-such-option’

The –loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file, An option that may not be recognized by all versions of a program can be given using the –loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

mysqld enables a limit to be placed on how large client programs can set dynamic system variables. To do this, use a –maximum prefix with the variable name. For example, –maximum-query_cache_size=4M prevents any client from making the query cache size larger than 4MB.

mysql innodb_flush_log_at_trx_commit翻译

知道innodb_flush_log_at_trx_commit的意思,但是对它取值0,1,2一直有点模糊不清。特地找了MySQL 5.1的refrence,自己翻译一下。虽然,也有官方的中文版翻译,但是不好意思,有点不相信它。

英文原文如下:
innodb_flush_log_at_trx_commit
Command-Line Format     –innodb_flush_log_at_trx_commit[=#]
Config-File Format     innodb_flush_log_at_trx_commit
Option Sets Variable     Yes, innodb_flush_log_at_trx_commit
Variable Name     innodb_flush_log_at_trx_commit
Variable Scope     Global
Dynamic Variable     Yes
Permitted Values
Type     numeric
Default     1
Valid Values     0, 1, 2

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行;但是,这种模式下,在事务提交的时候,不会有任何动作。如果innodb_flush_log_at_trx_commit设置为1(默认值),log buffer每次事务提交都会写入log file,并且,flush刷到磁盘中去。如果innodb_flush_log_at_trx_commit设置为2,log buffer在每次事务提交的时候都会写入log file,但是,flush(刷到磁盘)操作并不会同时进行。这种模式下,MySQL会每秒一次地去做flush(刷到磁盘)操作。注意:由于进程调度策略问题,这个“每秒一次的flush(刷到磁盘)操作”并不是保证100%的“每秒”。

The default value of 1 is the value required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB’s crash recovery is not affected and thus crash recovery does work regardless of the value.

默认值1是为了ACID (atomicity, consistency, isolation, durability)原子性,一致性,隔离性和持久化的考虑。如果你不把innodb_flush_log_at_trx_commit设置为1,你将获得更好的性能,但是,你在系统崩溃的情况,可能会丢失最多一秒钟的事务数据。当你把innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。如果你把innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。(下面的这句话到底是针对innodb_flush_log_at_trx_commit为2说的,还是针对前面这一整段说的,我就搞不清楚了,下次问问编写这一段文档的MySQL的人去。感觉是针对整段的:就是说InnoDB的crash recovery会利用log file来恢复数据文件,跟innodb_flush_log_at_trx_commit的值没有关系,管你这个值怎么设置的,我从log file拿到多少数据,就恢复多少数据。)InnoDB的crash recovery崩溃恢复机制并不受这个值的影响,不管这个值设置为多少,crash recovery崩溃恢复机制都会工作。

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.

为了在使用InnoDB事务的搭建复制环境中,达到最大的持久化和一致性,你需要在你的master主机的my.cnf中设置innodb_flush_log_at_trx_commit = 1并且设置sync_binlog = 1。

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

注意:
很多操作系统和一些磁盘硬件系统并不会真正的做flush-to-disk刷新到磁盘的这个操作。他们即使并没有真正刷到磁盘也会告诉mysqld说flush刷新到磁盘的操作已经完成了。这样的话,即使innodb_flush_log_at_trx_commit设置为1,也不能保证事务的持久化,最糟的情况下,一个主机掉电,就有可能导致InnoDB数据库崩溃。你可以考虑在SCSI磁盘控制器里面或者磁盘本身中,使用带蓄电池后备电源的磁盘缓存disk cache,来提高文件刷新操作的速度,使得这个操作更加安全。你同样可以尝试使用Unix的hdparm命令来阻止硬件缓存hardware cache的写磁盘缓存操作,或者使用其他硬件提供商hardware vendor提供的命令来避免写磁盘缓存。

这里既然提到了sync_binlog就顺便把它也翻译一下。
sync_binlog
Command-Line Format     –sync-binlog=#
Config-File Format     sync_binlog
Option Sets Variable     Yes, sync_binlog
Variable Name     sync_binlog
Variable Scope     Global
Dynamic Variable     Yes
Permitted Values
Platform Bit Size     32
Type     numeric
Default     0
Range     0-4294967295
Permitted Values
Platform Bit Size     64
Type     numeric
Default     0
Range     0-18446744073709547520

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk — in this case, the server relies on the operating system to flush the binary log’s contents from to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

当sync_binlog变量设置为大于0的值时,MySQL在每次“sync_binlog”这么多次写二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。sync_binlog的默认值是0,这种模式下,MySQL不会同步到磁盘中去。这样的话,MySQL依赖操作系统来刷新二进制日志binary log,就像操作系统刷其他文件的机制一样。当sync_binlog变量设置为1是最安全的,因为在crash崩溃的情况下,你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是,这也是最慢的一种方式(除非磁盘有使用带蓄电池后备电源的缓存cache,使得同步到磁盘的操作非常快)。

找了一下man fdatasync:
fdatasync() flushes all data buffers of a file to disk (before the system call returns).  It resembles fsync() but is not required to update the metadata such as access time.
fdatasync() (在系统调用system call返回前)将文件中所有的数据缓存区data buffers都flush刷到磁盘中去。它类似于fsync()函数,但是它不会更新元数据metadata:比如最后访问时间等。

may you success.

frm文件损坏修复

上周六坏掉的机器,本周三终于修复了一部分,检查以后发现是有一根内存坏掉了。因为没有备件,所以先拔掉了内存,下次更换成新的。(拔掉一根4G的内存,还有28G,羡慕吧,哈哈)

老办法,重启机器,因为搬迁机房,主备两台机器分布在两个机房,所以heartbeat没有必要启动起来了。手工绑定了IP。利用唐牛的方法:

Linux下:
手工加IP的方法:
ifconfig -a先看一下网卡情况,选一个没有使用的子接口bond0:0,在这个子接口上加vip:
ifconfig bond0:0 10.0.20.30 netmask 255.255.255.0
加完IP后,需要通知周边的机器特别是网关更新arp缓存(否则其它机器可能连不通这个IP):
arping -q -U -c 3 -I bond0 10.0.20.30
去除掉这个vip的方法:
ifconfig bond0:0 down

添加上VIP。

因为是主机突然关闭的,MySQL属于非法关闭,很多日志什么的没有刷到数据文件中去。启动要等好久。刷好了日志,查看主备机的复制时发现了如下的错误:
100512 17:47:33 [ERROR] Slave: Error ‘Incorrect information in file: ‘./eshop/sample.frm” on query.

frm文件损坏了。这个是MySQL的表结构文件(庆幸的还只是表结构文件,如果是数据文件什么的,就要开始哭了)。check table返回错误。show create table sample也同样有问题。

没有办法,只能采用变通的办法。备份好本机的sample.frm。从主机的数据文件目录(my.cnf里面datadir指定的目录里面,对应数据库目录中)拷贝了sample.frm,覆盖本机的sample.frm。
然后重启数据库,检查。这里覆盖的时候需要注意:修改权限和属主。
chown mysql:mysql sample.frm
chmod 660 sample.frm

启动以后一切正常,复制继续进行。就等它赶主机这几天的数据了。

究 其原因,是由于MySQL数据库突然被停止了,然后部分数据还在内存中,没有刷到磁盘中去,导致数据文件不完整。但是数据库的表文件一般不会修改,我有 99%的把握用户不会修改这张表的结构信息,怎么会损坏了表结构文件列,MySQL只会去读这个文件阿。想不通。估计是个世纪难题,哈哈。

这里其实还有一个小插曲:
备机21b到主机21a的复制,我们发现也停掉了,检查发现主机21a的show slave status结果中,
Master_Log_File: mysql-bin.001708
Read_Master_Log_Pos: 334529910
对 应的备机21b的mysql-bin.001708的大小为334357108。也就是说,主机21a已经复制执行了334529910位置以前的所有 event。他现在想执行后面的event。备机21b一看,你给我的位置,比我文件的位置还要大,我不可能给你阿。于是,备机21b到主机21a的复 制,肯定不行。
这里也是由于部分数据还在内存中,结果机器挂掉了,binlog不能及时写回磁盘。但是,这个时候,数据早通过网络发送给了主机 21a,主机21a收到了event,更新了它自己的复制位置为334529910。备机21b启动的时候,主机21a还是想从这个位置开始读数据,那当 然是没有的阿。

这个文件解决起来也很简单,切换主机21a的日志,让它从下一个binlog文件开始复制。
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.001709’, MASTER_LOG_POS=0;

may you success

MySQL数据库机器搬迁checklist

itbu的数据库机器从老机房搬迁到新机房的项目已经接近尾声了,这次的搬迁是我经历的一个比较大的项目,虽然整体的调控不是我主导的,但是MySQL数据库的搬迁都是我在主导进行,期间出现了一些问题,还好没有出现非常重大的错误。从中相信搬迁的大部分同学都成长了很多。我自己感觉我学到了很多,也成长了很多,虽然加班加点的,累的够呛,还是很值得的。为了避免以后大家再走弯路,也避免自己忘记搬迁的重要事项,特别记录下来,做为一个搬迁工程的checklist。方便大家搬迁过程中检查,不要遗漏了一些东西。
1、确认方案。尽早和应用方沟通,确认采用平滑搬迁或者停机搬迁的方案。
平滑搬迁是指MySQL数据库停止备机,然后搬迁过去,在新环境搭好以后,配置MySQL的双master复制,调试通过,应用服务正常。然后直接刷dns(或者其他方式)使应用的真实用户访问新的服务。
停机搬迁的方式是指,应用发布停机公告,在适当的时机,将应用服务器和数据库服务器等搬迁到新机房,部署上线。
上面的两种方案,第一种风险较小,对用户影响也非常小。万一刷过去以后,真实用户访问有问题,还可以切回来。第二种风险比较大,甚至可能出现有些配置修改修改不及时,造成停机时间已经过了,应用无法访问的问题。
2、确认机架位置。正常的话,sa负责人会将搬迁的机器列表和机柜,机架位置发给大家一份。以方便现场工程师以及对应人员确认搬迁的就是这批机器。我们需要注意一下,并提醒现场工程师机柜和机架号。eshop搬迁的时候,我们的一个现场工程师就看错了机柜号,拔掉了一台正在提供应用服务的机器,还好立刻意识到了,大家一起快速修复了这个问题。
3、确认搬迁的机器IP和搬迁到新机房的机器IP。老机房需要搬迁的机器IP在上一步就需要确认好了,新机房的机器IP需要跟sa和网络部门的同事沟通,我们提供具体的分配策略,网段划分,新老机器IP对应关系。
4、确认时间。及早跟各应用方沟通搬迁时间,如果有变化,及时相互通知。
5、搬迁前准备工作。eshop这边搬迁前需要重新同步主机数据到备机。其他应用需要注意主备机的复制延迟,尽量保证复制延迟较低。
6、通知所有相关人员。包括数据仓库,应用开发人员,网络,sa。告知他们新的数据库IP地址和连接方式。另外还需要检查MySQL里面的所有账户,看这些用户是否都通知到了。pm就没有及时通知数据仓库人员,导致第二天他们取不到数据的问题。
7、修改搬迁以后的配置。包括MySQL账户的修改,heartbeat的修改,cobar的修改,脚本的修改,脚本的配置文件修改,带外登录等。
a)MySQL帐号的修改。检查MySQL的账户,看是否还有依赖于老的IP地址段的账户。比如:eshop的复制帐号replicator@172.18.%搬迁到新的机房就肯定需要修改掉。
b)heartbeat的修改;cobar的修改。机器搬迁到新的机房,需要修改heartbeat和cobar的配置。这样才能让应用访问新的VIP地址。
c)脚本的修改。目前MySQL的管理有许多脚本,这些脚本有些是对IP有依赖关系的,需要及时修改掉。当然,最好修改掉这样的脚本,去掉对IP的依赖。
d)脚本配置文件的修改。有些脚本通过读取配置文件来依赖IP,同样需要修改配置文件。如果可能的话,修改脚本,去掉对IP的依赖。
e)带外登录。带外登录地址随着机器IP的更换也会变化。搬迁过去以后,需要再次验证一下带外登录的方式是否还正常。

该checklist可能还不是很完全,需要补充和优化。

may you success.

exceeds the log group capacity

做数据订正,需要更新了一批表数据。
数据库是采用的分库结构,也就是说一个MySQL instance里面有多个分库(一共有8个MySQL instance),我的数据库instance里面是16个分库,现在需要更新每个分库里面的一张表(即16张表)的所有数据。
我是用脚本更新的,串行操作,每个分库执行:
update vas_eq_enquiries set ENABLE_VIEW=1;
结果就收到报警,应用部门也过来说他们很多语句执行不了,报错说lock timeout:
100506 19:51:16  InnoDB: ERROR: the age of the last checkpoint is 966364350,
InnoDB: which exceeds the log group capacity 966363956.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
100506 19:51:32  InnoDB: ERROR: the age of the last checkpoint is 1028687928,
InnoDB: which exceeds the log group capacity 966363956.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

认真看了一下错误描述,是说log group的容量不够了。MySQL的innodb引擎日志比较重要,所有数据变更都共享着这个日志undo log。看样子一下子更新的数据太多了,于是我修改脚本,每次间隔5s,10s,30s去操作每个分库,最后发现间隔30s就不会有问题了。
当然,这个30s只对我的这个情况有意义。我这里也查看了一下MySQL instance的logfile大小:
innodb_log_file_size = 512M
innodb_log_files_in_group = 2

也就是说,它只允许最大1G的变更。
查看了我变更的表vas_eq_enquiries,在各个分库上大小不一,25M~77M的都有,平均算50M,16个分库,那就有16*50=800M了,在加上数据库自己的变化量1G空间确实会捉襟见肘。

这个问题的最终解决方案有两个:
1、减少短时间内变更的数据量,避免更新造成log日志的容量不足。对于分库更新量的情况特别需要注意,
2、增加log日志的容量。log日志的容量,目前MySQL最大不允许超过4G。这里需要注意的是,修改log日志的容量必须要在MySQL将所有日志文件都应用完毕的情况下,先停机,然后将日志文件移到另外的目录备份,重启MySQL数据库。MySQL数据库启动以后,会自动生成新的log日志。否则可能会有如下的错误:
InnoDB: Error: log file /data/mysqldata/innodb_log/ib_logfile0 is of different size 0 1073741824 bytes
InnoDB: than specified in the .cnf file 0 1283457024 bytes!
100506 21:57:41 [ERROR] Plugin ‘InnoDB’ init function returned error.
100506 21:57:41 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
100506 21:57:41 [ERROR] Unknown/unsupported table type: INNODB
100506 21:57:41 [ERROR] Aborting

导致MySQL数据库启动不了。

顺便把MySQL这两个参数解释抄在下面:
innodb_log_file_size
The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

innodb_log_files_in_group
The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.

这里log日志的容量可能大家会觉得越大越好。innodb_log_file_size里面也说了这个值越大,把checkpoint从buffer pool中刷到磁盘的频率也越少,减少了很多磁盘I/O。同时,crash情况下需要恢复的时间也越多。

Baron Schwartz 在MySQL Performance Blog里面提到一种怎样设置innodb_log_file_size的方法。http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
他根据每分钟变化的数据量*60分钟,来粗略的得到一个innodb_log_file_size的值。
mysql> pager grep sequence
PAGER SET TO ‘grep sequence’
mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
Log sequence number 84 3836410803
1 row IN SET (0.06 sec)
1 row IN SET (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row IN SET (0.05 sec)
然后:
mysql> SELECT (3838334638 – 3836410803) / 1024 / 1024 AS MB_per_min;
+————+
| MB_per_min
+————+
| 1.83471203
+————+

于是:1.8*60 约等于110M,那么就取128M把,如果innodb_log_files_in_group=2,那么innodb_log_file_size=128/2=64M。
这个当然取决于你这一分钟应用程序修改的量的多少,如果取的时间不对,那么这个值当然不精确。

3楼的3. Sheeri K. Cabral提出了他自己的方法:登录系统,直接看ib_logfile切换的速度。
/var/lib/mysql> ls -lrth ib_logfile*
-rw-r–r– 1 mysql mysql 400M Nov 22 09:00 ib_logfile1
-rw-r–r– 1 mysql mysql 400M Nov 23 10:51 ib_logfile0

(这里26个小时切换了一次)然后再根据你的要求:你需要logfile中保存多久的日志,来调整logfile的大小。当然,这个也不精确。取决于你什么时候去看这个文件,白天数据量变化多,晚上变化少,这个间隔时间就完全不同。

总的来说,需要注意这个innodb_log_file_size的大小设置,每个应用都有自己的特色,大小设置需要根据应用的不同来设置。

may you success.

auto_increament达到最大值的问题

MySQL报错说不能从storage engine中获得auto-increment的值。表的主键是自增字段。表结构类似如下:
CREATE TABLE test1 (
c1 int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8

检查了表的引擎和结构。发现是innodb的表。然后看到表的AUTO_INCREMENT=2147483648。报错信息如下:
risk@10.20.36.26 : risk 16:15:03> insert into test1 (c1) VALUES   (NULL);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

不能从存储引擎读取auto-increment的值。检查表的信息,也是正常的阿。怀疑是否存储引擎哪里有问题。于是想重建该表。直接提交了optimize table CTU_FACT。显示重建成功。
再一看表的AUTO_INCREMENT变成了2147483647,还少了1。询问相关人员,原来他自己alter table修改过这个值。再次用insert插入一个值,结果出现:
ERROR 1062 (23000): Duplicate entry ‘2147483647’ for key ‘PRIMARY’
重复的主键id?不对阿,这个主键不是我自己生成的。
后面同事提醒说,你看看这个id的字段类型。一下子缓过神来,2147483647这个值不就是(2*1024*1024*1024-1)吗,就是signed int的最大值阿,自己对数字还是不够敏感阿。
看了一下表结构,主键定义为int(11) NOT NULL AUTO_INCREMENT。确认了这个问题。修复也很简单:
alter table test1 modify column c1 bigint(20) unsigned  NOT NULL AUTO_INCREMENT;
然后插入数据就没有问题了。

其实这个问题最终的原因还是比较简单的,也提醒我们auto_increment的字段需要考虑字段类型和它最大的可容许的整数大小

may you success.

索引问题导致mysql复制lock timeout

遇到一个非常郁闷的问题。MySQL复制备机SQL线程执行一个语句报错:
Error ‘Lock wait timeout exceeded; try restarting transaction’ on query. Default database: ‘brmms2’. Query: ‘UPDATE brmms_user SET gmt_modified = now() , last_login_ip = ‘58.20.77.164’ , last_login_time = now() WHERE member_id = ‘yanyazhang”
这个语句就算我单独提出来执行也会超时。show innodb status的状态如下:
root@localhost : (none) 08:12:46> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
100501 20:12:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 22, signal count 22
Mutex spin waits 0, rounds 239, OS waits 8
RW-shared spins 20, OS waits 10; RW-excl spins 3, OS waits 3
————
TRANSACTIONS
————
Trx id counter 0 1835507099
Purge done for trx’s n:o < 0 1835505963 undo n:o < 0 0
History list length 44
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 0, not started, OS thread id 12
MySQL thread id 801, query id 3585 localhost root
show innodb status
—TRANSACTION 0 1835507098, ACTIVE 64 sec, OS thread id 17 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s)
MySQL thread id 1214, query id 3536 Updating
UPDATE brmms_user SET gmt_modified = now() , last_login_ip = ‘58.20.77.164’ , last_login_time = now() WHERE member_id = ‘yanyazhang’
——- TRX HAS BEEN WAITING 64 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10344 page no 3281 n bits 192 index PRIMARY of table brmms2.brmms_user trx id 0 1835507098 lock_mode X locks rec but not gap waiting
Record lock, heap no 121 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
0: len 8; hex 0000000005fd14a7; asc ;; 1: len 6; hex 00006d6795f9; asc mg ;; 2: len 7; hex 00000000c30c2f; asc /;; 3: len 10; hex 79616e79617a68616e67; asc yanyazhang;; 4: len 4; hex 00000000; asc ;; 5: len 4; hex 00000000; asc ;; 6: len 4; hex 00000000; asc ;; 7: len 4; hex 00000000; asc ;; 8: len 3; hex 8fb48e; asc ;; 9: len 12; hex 35382e32302e37372e313634; asc 58.20.77.164;; 10: len 4; hex 4bc59f9c; asc K ;; 11: len 4; hex 4bc59f8f; asc K ;; 12: len 4; hex 4bc59f9c; asc K ;; 13: len 4; hex 80000000; asc ;; 14: len 4; hex 80000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: SQL NULL;

——————
—TRANSACTION 0 1835505145, ACTIVE (PREPARED) 5527 sec, OS thread id 0
1 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
16300 OS file reads, 336 OS file writes, 268 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 104, seg size 106,
27 inserts, 27 merged recs, 27 merges
Hash table size 35401603, node heap has 7 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 102 3346391724
Log flushed up to 102 3346391724
Last checkpoint at 102 3346391724
0 pending log writes, 0 pending chkp writes
198 log i/o’s done, 0.00 log i/o’s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 18653842407; in additional pool allocated 16776704
Dictionary memory allocated 4059160
Buffer pool size 1048576
Free buffers 1034004
Database pages 14565
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 14565, created 0, written 146
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
————–
ROW OPERATIONS
————–
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 9, state: waiting for server activity
Number of rows inserted 52, updated 36, deleted 3, read 364
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
—————————-
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)
这里比较奇怪的地方在于:这个备机上就一个SQL线程在跑,其他的任何东西都没有跑,结果它还被锁超时杀掉了。上面的show innodb status显示
——————
—TRANSACTION 0 1835505145, ACTIVE (PREPARED) 5527 sec, OS thread id 0
1 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
这个线程是干吗的,难道是它lock住了update语句?

我先后试过如下的方法想去恢复:
1、重启数据库。SQL slave自己执行不过去。手工执行不过去。
2、重启数据库并修改为skip-slave-start模式,手工执行未果。
3、alter table brmms_user drop index idx_brmms_user_member_id, add index idx_brmms_user_Mem_id (member_id);报错lock timeout.
4、stop slave;想利用mysqldump -uroot -p –opt –add-drop-table –default-character-set=utf8 –master-data=2 –single-transaction –complete-insert –log-error=/tmp/mysqldump20100501.log brmms2 brmms_user>brmms_user.sql 或者mysqldump -uroot -p –default-character-set=utf8 brmms2 brmms_user>brmms_user.sql导出数据,然后重新导入。结果在mysqldump的时候就导不出来数据(能够导出表结构,错误日志里面也没有任何信息)
5、创建临时表tmp_pickup_brmms_user,然后insert into temp_brmms_user_pickup select * from brmms_user;。报错lock timeout.

最后通过select into outfile的方式解决了这个问题。步骤如下:
1、确认slave是停掉的。 并且修改SQL_LOG_BIN来避免下面的语句记录到binlog中(如果对应的主机没有这个问题,下面的这些操作完全没有必要通过replication传递到主机执行)。
stop slave;
SET SQL_LOG_BIN=0;
2、导出brmms_user表的所有数据。注意/tmp目录需要有足够的空间。
select * from brmms_user into outfile ‘/tmp/brmms_user.txt’;
3、创建临时表。该表的结构要求跟brmms_user表结构一样。
CREATE TABLE tmp_pickup_brmms_user ( … );
4、将brmms_user的数据导入到tmp_pickup_brmms_user。
load data local infile ‘/tmp/brmms_user.txt’ into table tmp_pickup_brmms_user;
5、改名。将brmms_user改到另外的名字,将临时表名字改为brmms_user
alter table brmms_user rename tmp_pickup_brmms_user_old;
alter table tmp_pickup_brmms_user rename brmms_user;
6、重新启动复制。
start slave;
7、收尾工作:必要的话,删除tmp_pickup_brmms_user_old表。SET SQL_LOG_BIN=1;来恢复session的binlog记录。
drop table tmp_pickup_brmms_user_old;
SET SQL_LOG_BIN=1;

may you success.

今天我们又检查了一下那个有问题的表。发现通过主键更新对应记录也是locktimeout。更新它之前和之后的主键都没有问题。突然回想起这台机器是通过另外一台机器的solaris的snapshot生产的快照,然后直接传到这台机器的。之后就直接启动起来用了。假设

这里在生成snapshot的时候,MySQL的数据(索引)在物理层面上是不一致的。这样的不一致是否是导致MySQL的这个问题的原因列。有待进一步的确认。

这里我们也知道了solaris的snapshot还是有风险的,如果以后还有备机的搭建,可以考虑先停止MySQL,然后才做snapshot。保证数据的物理一致性。

may you success