The Ji Village News

季庄新闻–Haidong Ji’s Blog

I attended Vadim Tkachenko’s talk on XtraBackup during MySQL conference in Santa Clara last week. Backups are obviously very important, but the use case I had in mind is this:
Replicating a database that has Innodb tables in it, while keeping both master and slave on line if possible.

Tangent: by the way, I love the native backup utility that was once promised in MySQL 6.0, similar to SQL Server’s way of backup. It was like running “BACKUP myDb to DISK = ‘/backupDirectory/myDb.bak’” under mysql client, but I digress…

I have used mysqldump to accomplish this in the past, but I wondered how XtraBackup would fare in this task, especially after hearing Vadim’s talk and reading news on Percona’s development effort. To cut to the chase, this is my conclusion. Reproducing steps are listed immediately afterwards.
1. innobackupex provides a consisten database backup, spitting out log file and log positions in stdout, which is nice and useful for slave initiation;
2. It works with both MyISAM and innodb tables;
3. If MyISAM tables are all you have, just run innobackupex –prepare /directoryWhereBackupIs, and then move the database directory from under /directoryWhereBackupIs to under your slave’s datadir, then make the necessary group and owner change to said directory and its content files, and you are ready to run the “change master” command and start slave;
4. If the database has innodb tables, then in addition to step 3, you will also need to stop mysql on slave, move the ibdata1 file to datadir, then restart mysql, and run “change master…” and “start slave” commands. It does not matter if you are using innodb_file_per_table or not.

It will be nice if I can keep the slave up and running during this step when the database has innodb tables in it. Did I do anything wrong? Is there a better way? What if the slave has a database that has innodb tables and thus uses ibdata1 to begin with? What do you do then? Should I play with Tungsten’s replication? What are the compelling reasons to use Tungsten’s replication?

In any case, from my limited testing, I think I will use innobackupex for future replication creation tasks, if I can afford a mysqld restart. Overall, it feels a bit easier than mysqldump approach that I’ve been using in the past.

Here are the steps needed to reproduce:

1. Fire up 2 Rackspace CentOS 5.5 servers. Rackspace cloud servers beat Amazon EC2 servers hands down, in my view, for developing/sandboxing purposes;
2. Install the required mysql client, server, and XtraBackup on both servers;
3. Make /etc/my.cnf by cloning the sample cnf files under /usr/share/my-small.cnf. 3 minimum changes were necessary: log-bin=mysql-bin, server-id=a unique number, datadir=/var/lib/mysql. The first 2 are necessary for replication, the last is needed for innobackupex

Well, while you are at it, on slave, add in read-only and skip-slave-start if appropriate. That’s best practice for read only slave.

4. Add master server’s public key to authorized_keys on slave, to facilitate easy ssh connection.
5. On master, run this command:

innobackupex --databases=test --stream=tar /tmp/ --slave-info | ssh root@slave "tar xfi - -C /root"
When it finishes, you should see something like this:
110419 18:54:21  innobackupex: completed OK!
tar: Read 6656 bytes from -

Take note of 3 lines immediately above it, where it states the binlog file and log position, like this:

innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 2515

6. On slave, run this command:

innobackupex --apply-log /locationWhereBackupIs

then, assuming the database name is test, run the 2 commands below to change the group and owner to mysql:

chgrp -R mysql test
chown -R mysql test

move the directory under mysqld’s datadir:

mv test/ /mysql/datadir

If test database has innodb tables in it, stop mysql on slave, then copy ibdata1 to datadir, restart mysql.

7. On master, open up port 3306 if it is not already open, then create the replication account:

grant replication slave, replication client on *.* to repl@'50.56.121.%' identified by 'p@ssw0rd';

8. On slave, run:

change master to master_host='50.56.121.96', master_user='repl', master_password='p@ssw0rd', master_log_file='see output from innobackupex backup command on master', master_log_pos=numFrominnobackupexOutputOnMaster;
start slave;
show slave status\G

我好像以前说过，从学英文的角度，像什么时代周刊，华尔街日报，纽约时报，新闻周刊，路透社，华盛顿时报，有线新闻网，读者文摘，心灵鸡汤等等等等的文章大部分都是八股文一样的破烂，没多少语言价值，还装得一本正经，跟真得似的，并标榜“客观、真实”等来卖骚。但因为英语的强势和新闻业的垄断和互相转载，这帮龟孙的影响力还不容忽视。须知，未上美国英文的新闻不算新闻。这也是为什么美国发几个消息，管它真假，就可以在全球把你（日本，俄罗斯，墨西哥，中国，非洲，阿拉伯世界的国家等等等等）搞得很被动的原因。

(当然新华、人民和CCAV等也好不到哪里去。那种愚民，那种笨拙，那种僵硬，那种八股，还不是教人笑掉大牙。一个很大的区别是他们的忽悠被所有人看透，弄到了说真话别人也不信的程度。而老美的忽悠绝对一流，把我的美国同胞和整个地球人都能绕晕了，当然我以为其影响力在下降也是不争的事实。媒体主体腐烂，并不意味着媒体里没好人，请不要对号入座。)

但英语和其他外语当然重要，所以多读很有必要。那我就建议你多读像王博这样的文章，多美，多有力量！

只要愿意找，好文章不少。我几年前读过Kai Pan的这篇文章，同样的有劲儿，也在这里分享一下。

顺便说一句，我的朋友们纷纷荣登或将要荣登父母之位，我也跟着高兴。瞧瞧王博的文章，写得多好，很让我感动并献上最诚挚的祝福！

已经在美国开过不少会了，对于我来讲，开会的最大的收获就是拉关系，即networking。当然可以从一些演讲里学到一些东西。像什么每天的主题演讲keynote speech,大部分没啥意思。销售商的展览vendor exhibit可能也不错，因为在那里有可能看到一些新的方向，和厂商的技术人员聊聊也很好。

2011年4月10日，旧金山缆车站。

拉关系当然也不是自高自大，妄自菲薄或舔腚沟子。从我的经验来看，只要懂得微笑和聆听，讲一下自己的体会，听一下别人的经历和故事，就很好。卡内基书里说得那些玩意儿，当然很多都是常识，挺管用的。

像昨天和Percona的一帮家伙和其他人喝啤酒，大家聊天胡侃。其中一位女士接到男友的电话就走到旁边去接，别人就开始八卦，说男友是个消防员。和我的中国同胞和其他地球人一样，我美国同胞有很多崇拜英雄和名流的倾向。警察和消防员是小朋友常见的英雄选项。就有人说据说那个消防员的管子很粗，大家哄笑一场。

然后就和Percona的一个哥们步行去参加MySQL社区晚餐，一帮人湊在一起吃饭聊天。这哥们学过几年汉语，能简单说几句，对佛教很感兴趣，好像也学过梵文。走在路上，他和我讲了下他对佛教的理解。我很想听，因为我对佛教一无所知。我们谈了谈我们对于中国的物欲横流的物质主义的看法。他表示了对此的关注和担忧，并礼貌地说起了文革。我说我同样有这样的担心。我讲了下我的观点：文革前的各种运动，文革，文革后的拨乱反正和七十年代底、八十年代初的对老冒和以文革为主的各种运动的初步评价，和邓小平、陈云等人当时认为在当时的历史情况下不好对毛做出客观的评价的一些谈话，和他们对以后重新评价老毛和文革的希望。也谈了后来的89事件，南巡讲话等。我感觉我们当时的“伤痕文学”有疗伤的作用。但后来有了89事件，所以我以为我们没有真正走出老毛的滔天罪行的阴影。我不明白的是，我们国内的对毛的一种持续的符号化的崇拜：像把他的头像搞到更多的纸币上，一些影片和电视剧对他的持续的歌功颂德，还有他在天安门上的头像和在广场上的给人展览的僵尸。我理解毛在解放前的作用，也不想同时也不能抹杀毛的功绩，但建国以后的大动乱、给国家和人民的巨大的心理和生理伤害，以及激情和纯真破灭后所造成的钱权崇拜和社会良知的缺失，这些东西，不是他造成的，还能是谁？

因为说佛教，也就谈到了西藏。我不知道怎么说，但就想怎么做才能真正增进各个族群的互相了解和尊重呢？怎样做才能使大部分人有真正实惠的同时也保留对传统文化的尊重、保护、和创新呢？怎样才能在发展的同时做到不丢失自我呢？这些问题，我们的传统文化和外来文化，可以有什么启示吗？我最后向他说起了我本家兼老乡季羡林翻译过的一些梵文著作。

扯远了。到了那个社区晚餐，和一帮Oracle的人坐在一起喝玛格利特，吃墨西哥饭。同桌的做销售的菲利普刚从北京和上海回来，我谈了下我所观察到的中国的MySQL的应用，和他的取得调酒师证件的一些趣事。提到中国的阿里巴巴、淘宝和一些政府和科研部门对MySQL的应用，相谈甚欢。同桌的还有两个来自巴西但现分别居住在加拿大和美国的两位男士。我问了下巴西政府派武装部队到里约搞治安为奥运做准备，美国同胞对此知之不详。这两位巴西哥们可能意见不同，一位嘲笑本国的政府的无能和可笑，另一位想得更多一点，说挺期待本国的世界杯和奥运盛事。我觉得巴西办这个挺好的。

回来路上有开始调侃，我们四个大男人挤在车后座回旅馆。这是小车，四个男人在后边应当违法吧。但大家都很high，开始说点荤笑话，挺有意思的。

不写了。干活去，接着交流，接着侃！

Using MySQL replication slave(s) for reporting (with potentially different storage engines) is a very popular way of scaling database read activities. As usual, you want to be on top of things when replication breaks so end users can be notified and issues addressed. When Nagios, Zabbix, or whatever monitoring tools are not available or otherwise not accessible, there got to be another way. I wrote the following Python script for that purpose. The script can then be scheduled via crontab to check replication status in an interval you define. When things break, you get a notification email.

Notes:
1. I toyed with MySQLdb Python module for this task, but I don’t like the fact that I cannot easily retrieve values via column names in a MySQLdb cursor. If there is an easier way that I am not aware of due to my ignorance, I’d appreciate it if you could let me know.

I then tried pyodbc, but got a Python 2.4 compiling issue. I didn’t have time to troubleshoot at the moment, so I opted for subprocess calling mysql client and then processing standard output. The added benefit of this approach is the lack of dependencies on additional Python modules;

2. Here is my test for replication status: I look at 3 replication slave status variables. If Slave_IO_Running == “Yes” and Slave_SQL_Running == “Yes” and Last_Errno == “0″, then I consider that to be success. Otherwise, I want to know about it. Let me know if this test is not sufficient or there is a better way.

3. Good place to scavenge code for running external command, list splitting, removing the first and last list items, and dictionary building for Python.

import shlex, smtplib, string, subprocess, sys
from socket import gethostname
def runCmd(cmd):
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=False)
    out, err = proc.communicate()
    ret = proc.returncode
    return (ret, out, err)
returnCode, stdOut, stdErr = runCmd('mysql -e "show slave status\\G"')
if returnCode:
        print >> sys.stderr, "There was an error (%d): \n" % returnCode
        print >> sys.stderr, stdErr
slaveStatusList = stdOut.split('\n')
#Remove the first and last item of the list
del slaveStatusList[0]
del slaveStatusList[-1]
slaveStatusDict={}
for i in slaveStatusList:
        slaveStatusDict[i.split(':')[0].strip()] = i.split(':')[1].strip()
if (slaveStatusDict["Slave_IO_Running"] == "Yes" and slaveStatusDict["Slave_SQL_Running"] == "Yes" and slaveStatusDict["Last_Errno"] == "0"):
        print "cool"
else:
        emailSubject = "Replication problem on slave " + gethostname()
        emailTo = "firstName.lastName@gmail.com"
        emailFrom = "robot@mycompany.com"
        emailBody = string.join((
                "From: %s" % emailFrom,
                "To: %s" % emailTo,
                "Subject: %s" % emailSubject ,
                "",
                stdOut
                ), "\r\n")
        server = smtplib.SMTP("localhost")
        server.sendmail(emailFrom, [emailTo], emailBody)
        server.quit()

春天扎稳了脚跟儿，在后院儿种的蒜这两天纷纷冒出来了。我看在眼里，喜在心头。蒜这个东西，全身都是宝：等慢慢长大些，到一揸的长度（手伸张开时拇指顶到中指顶之间的距离），就可以吃。蒜苗和蒜苔，先切成小条放着。然后准备点儿鸡蛋或猪肉。点火，往铁锅里放点荤油，使劲儿拉风箱，把小火烧得呼呼地。那白白的猪油化了后，锅上升起一阵青烟，扔几个红干辣椒进去，在热锅里蹦蹦跳跳，呛得人咳嗽。等油把辣味炸出来了，放进蒜苗/蒜苔，再加上鸡蛋或猪肉翻炒。如果是炒鸡蛋，从盐罐子里拿点粗盐进去。炒猪肉的话呢，就要点酱油和醋，很快就出一道佳肴。我小时候，要有这个吃，不知道有多咨儿！那干辣椒也不放过，吃得嘴吸喽吸喽地，才叫过瘾！

蒜头也好呀。炒个菜儿，葱花蒜瓣儿是必须的。谁家添个小孩，到出了朱门（我不确定是不是该这么写，但音差不多）的时候，送完礼，会收到很多煮熟的红鸡蛋回来。我小时候的一个很流行的吃法是先踹蒜，然后把红鸡蛋放在蒜窝子里一起踹。鸡蛋不要踹碎，有成块的蛋黄和蛋白也不错。然后加点儿酱油和香油调，又是一道佳肴。有时加点儿青尖辣椒子也不错。过年包饺子，要沾着蒜酱一起吃。所以说如果我必须崇拜一个人或物，那绝对不会是主席总统明星安拉上帝东西方闪电之流，我会选择蒜。我文笔不好，要不然，我会给蒜写首情诗。

我在美国，不太买得到蒜苗和蒜苔，主要是我们不太常去东方的超市。我有时馋急了，会弄点儿踹蒜调熟鸡蛋，因为简单。吃完这个，我抹抹嘴角，满足地嘿嘿一笑，家人见我躲着走。

—–

我们不光种了点儿蒜。孩娘去年秋天还种了点儿蓝色的小花，也开了，怪好看的。再过段时间，我会种点儿辣椒和葱。

—–

搬进来大半年了，还有些打包的东西没开。我从一个纸箱子里搜出来山西师范大学语文报社出版的北美版《学语文》月刊，再和孩子一起学。我觉得这本杂志质量挺好，前两天刚刚又订了北美版。

儿子的听力还不错，但我必须寓教于乐，要注意态度。我有时给他讲点过去的事情，他会听得很有兴趣。像我那次给他讲小时候俺家的猪圈和喂猪、到供销社号（我不知道该用哪个字，应当查一查）猪的事儿。我讲过把那猪绑在地排车上，邻居克进叔往猪腚上踢几脚，为得是不让猪屙出屎来，好等拉到公社时多卖几个钱儿，他听得津津有味。我现在倒是想把那些我记得的一些事儿写下来。

—–

没读过方舟子的文章，但从我了解到的信息，我挺欣赏的。我和他一样，也喜欢鲁迅的东西，但我绝不单从老冒的那个角度来看鲁迅！算了不写了，干活去。祝母校厦门大学九十华诞生日快乐。

Update:

Commenter MarkR made a great point: if possible, use some packaging tools, to try to maintain proper dependencies, to the extent that is possible. Install from the source should be Plan B. So, try yum install MySQL-python first.

This is mostly for my own future reference. It’ll be icing on the cake if it helps you!

This is geared for CentOS or Red Hat. Use apt-get or other packaging tools for different flavours of Linux.

1. Get Python module setuptools called easy_install. I love easy_install, by the way, sort of like CPAN for Perl modules;
2. To install MySQLdb package, you would think easy_install MySQLdb would do. But that is not the case. I hope the developer would fix that. Instead, you need:

easy_install MySQL-python

3. If you have build errors, you may need:
yum install python-devel or yum install gcc or both.

Update: one more tip. If you encounter:

gcc: error trying to exec ‘cc1plus’: execvp: No such file or directory, you probably need:

yum install gcc-c++

I am reading and following examples in MySQL 5.1 Plugin Development. After compiling and moving a .so file (think DLL or Assembly file in Windows) into MySQL plugin directory, I got this message when I tried to create a UDF (User Defined Function):

mysql> create function udf_staticexample returns integer soname 'udf_staticexample.so';
ERROR 1126 (HY000): Can't open shared library 'udf_staticexample.so' (errno: 0 /usr/lib/mysql/plugin/udf_staticexample.so: failed to map segment from shared object: Permission denied)

This is caused by the fact that the .so shared object file is not in the right SELinux security context:

[root@asusfedora plugin]# ll -Z
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_archive.so -> ha_archive.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_archive.so.0 -> ha_archive.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_archive.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_blackhole.so -> ha_blackhole.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_blackhole.so.0 -> ha_blackhole.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_blackhole.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_example.so -> ha_example.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_example.so.0 -> ha_example.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_example.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_federated.so -> ha_federated.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_federated.so.0 -> ha_federated.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_federated.so.0.0.0
-rwxr-xr-x. root root unconfined_u:object_r:user_home_t:s0 udf_staticexample.so

To fix this, use chcon –reference to change the security context of the file in question to a referenced file, like this:

[root@asusfedora plugin]# chcon --reference=/usr/lib/mysql/plugin/ha_archive.so /usr/lib/mysql/plugin/udf_staticexample.so
[root@asusfedora plugin]# ll -Z
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_archive.so -> ha_archive.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_archive.so.0 -> ha_archive.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_archive.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_blackhole.so -> ha_blackhole.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_blackhole.so.0 -> ha_blackhole.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_blackhole.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_example.so -> ha_example.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_example.so.0 -> ha_example.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_example.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_federated.so -> ha_federated.so.0.0.0
lrwxrwxrwx. root root system_u:object_r:lib_t:s0       ha_federated.so.0 -> ha_federated.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       ha_federated.so.0.0.0
-rwxr-xr-x. root root system_u:object_r:lib_t:s0       udf_staticexample.so

All is well afterwards.

mysql> create function udf_staticexample returns integer soname 'udf_staticexample.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select udf_staticexample();
+---------------------+
| udf_staticexample() |
+---------------------+
|              318749 |
+---------------------+
1 row in set (0.00 sec)