Some notes on MySQL replication configuration

server version

MySQL supports replication from one major version to the next higher major version, but not the reverse. That’s to say, version of slave should newer than or equal to that of master.
Also, do not use 3 different versions in your setup, even minor version differ. That’s not supported and may cause you trouble.
One day one of our MySQL slave suddenly stopped replication, and the error log said:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'character set utf8... ... load data infile '/tmp/xxx.xx' into table xxx character set utf8 ...

After a quick search on MySQL reference manual I found that the “character set” option in LOAD DATA statement was added in mysql 5.0.38. Unfortunately, that MySQL slave is of version 5.0.22 so it doesn’t know that syntax, whereas the master version is 5.0.96 and it’s happy to execute the statement.


Do remember to set unique server-id for different mysql instance.

log-bin relay-log

set this two option explicitly, or you may encounter problem when upgrade/migration.
some sample error message that’s due to lack of these option when server environment change:

130124 22:41:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts
as a slave and has his hostname changed!! Please use '--relay-log=MyHost-relay-bin' to avoid this problem.
130124 22:41:08 [ERROR] /usr/local/mysql-5.0.22/libexec/mysqld: File './MyHost-relay-bin.001098' not found (Errcode: 2)
130124 22:41:08 [ERROR] Failed to open log (file './MyHost-relay-bin.001098', errno 2)
130124 22:41:08 [ERROR] Failed to open the relay log './MyHost-relay-bin.001098' (relay_log_pos 235)
130124 22:41:08 [ERROR] Could not open log file
130124 22:41:08 [ERROR] Failed to initialize the master info structure


some error may occur on master, such as table crash due to disk/filesystem problem. But the tables are good on slave, and it’s safe to ignore these errors, we can use slave_skip_error for that.

#when encounter these error, continue to replicate
#1062: Duplicate entry '%s' for key %d 
#1053: Server shutdown in progress
#1049: Unknown database '%s' 
#1146: Table '%s.%s' doesn't exist 
#1194: Table '%s' is marked as crashed and should be repaired 
#1050: Table '%s' already exists 

Reference: MySQL Reference Manual  16.1 Replication Configuration

Posted in Database, System Administration | Leave a comment

Trace command call chain on Linux

We have serveral D&T(Developing and Testing) server, which runs several internal system that generate files and then copy/sync to other server. Because those system were developed by many developers and without good documentation. No one knows exactly how each system works.

One day we want to change the user account that was used to copy files via ssh, we know that the basic commands are scp and rsync. However, no one knows exactly which background job, or web interface, or user scripts may call these commands. How can we find out all those things? A full disk grep may help, but it’s too time-consuming. How about command call history? If we know which command called scp, we can search through the call chain and find the related program/script that need to be changed. But wait, how can we log command call history? There are several known approach:

1. Use auditd
2. Patch kernel with grsecurity
3. Use Bash history
Continue reading

Posted in Perl, System Administration | Tagged , | Leave a comment

Open application in other language in Gnome

Due to the bad(or even ugly compared to Windows 7) look and feel of Chinese character in my Gnome desktop, I set my default language to English, then everything looks better.

However, I want some special application to show up in Chinese, because I am not quite familiar with those special vocabulary, gimp, for example. Here’s how I accomplish this target.

First, copy the gimp.desktop file to my desktop

cp /usr/share/applications/gimp.desktop ~/Desktop/
cd ~/Desktop
chmod +x gimp.desktop

Then, edit gimp.desktop, Change the Exec line to:

#Exec=gimp-2.6 %U
Exec=env LANG=zh_CN.UTF-8 gimp-2.6 %U

Here, I use the env command to set the LANG environment variable to zh_CN.UTF-8 and then launch gimp.

That’s all, now I can click the gimp icon on my desktop and it will show up in Chinse.

Posted in Linux Desktop | Tagged , | Leave a comment

peek the source – Another troubleshooting method for open source software

Today I read a security article on the nginx fastcgi PATH_INFO (Chinese version on 80sec). I currently maintain several sites with nginx+php-fpm, so I decide to do some test and see if my configuration is in danger.

However, when I follow the article carefully and execute the test plan, it finally failed with an “Access denied.” 403 error message. the nginx configuration in my test is so simple that I believe it can’t generate such a 403 error, also tcpdump on php-fpm port confirms that it’s php that returned the 403 error message. After several minutes of googling for the “php-fpm access denied” message with no luck, I decide to have a look at the php souce code.
Continue reading

Posted in Security, Web | Tagged , , , , | Leave a comment

A journey to troubleshooting by sersync exclude bug

For the last half year, one of our file distributing syststem goes out of sync occasionally, which bothered me a lot.

The simplified system workflow looks like this:file distributing system
Continue reading

Posted in Programming, System Administration | Tagged , , , , | Leave a comment

extract databases or tables from mysqldump

I wrote a simple fast and flexible perl script called to extract databases/tables from mysqldump sql file, you may download it from here.

  ./ -h|--help
    This page

  ./ -l|--list [file.sql ...]
    List databases and tables

  ./ -a|--all-tables [file.sql ...]
    split to one .sql file per table

  ./ [-s|--separate] -d|--databases db1,dbn... [file.sql ...]
    Extract selected databases:

  ./ [-s|--separate] -t|--tables tbl1,... [file.sql ...]
    Extract selected tables(the first table name match):

  ./ [-s|--separate] -t|--tables db1.tbl1,db2.tbln,... [file.sql ...]
    Extract selected tables(full qualified):

    --debug print some debug information to STDERR
    -o|--output-dir directory to output sql files
    -s|--separate output separate sql files named like DB.TBL.sql
    -v|--version show program version
  This program requires running myqldump with --comments option(eanbled by default)
  If no source sql file specified, it will read from stdin

Continue reading

Posted in Perl, Programming, System Administration | Tagged , , , | 5 Comments

Setup Linux as wifi hotspot with NetworkManager

First of all, note that if your wireless card does not support master/hostap mode, you can only use ad-hoc mode, which allows only one device to connect.
Secondly, NetworkManager only support WEP for ad-hoc mode, so don’t select other Security method like WPA. If you don’t want to ask for trouble, select “WEP 40/128-bit Key”.

The setup is indeed very simple. just click the network icon from the top right corner of your screen, select “Network settings”, select wireless, then click “Use as hotspot”, it will setup everything for you, including the secret key, and the SSID, you can click the “configure” button to change some settings, like the SSID, and the secret, but keep the above 2 point in mind.
If everything goes OK, you can connect your mobile device to the hotspot in about 30 seconds. in case of problem, do check /var/log/messages for help.

Posted in Linux Desktop | Tagged , , , , | 5 Comments

why squid listen on high udp port number

When starting squid with the default configuration (compiled from source), you may notice that the squid process listens not only on TCP port 3128, but also a high UDP port.

netstat -tlunp | grep squid

tcp 0 0 :::3128 :::* LISTEN 3520/(squid)
udp 0 0* 3520/(squid)
udp 0 0 :::51621 :::* 3520/(squid)

What in the hell is that 52431 UDP port used for? Let’s find out. Continue reading

Posted in System Administration | Tagged , , | Leave a comment


1.    谁偷了我的磁盘空间?

发现大小为10G的/home分区磁盘空间使用了9.8G,但是通过du得到的大小却是5G. 另外的4.8G空间哪里去了呢?

df -Th /home

Filesystem    Type    Size  Used Avail Use% Mounted on

/dev/sda3      ext3     10G   9.8G  0.2G  98% /home

du -hs /home

5.0G    /home/

Continue reading

Posted in Programming, System Administration | Tagged , , | Leave a comment

Bash quote trap: why rsync exclude pattern not work

A friend wanted to sync all his files to a remote host, exluding the source files(those with suffix .c, .cpp, .h, .hpp). First I wrote this script

#!/bin/bash (version 1): sync files to remote host###
SYNC_OPT=' --exclude="*.[ch]" --exclude="*.[ch]pp" '
if [[ $# -eq 0 ]]; then
        echo "Usage $0 /src/path"
        exit 1
#for debug ...
echo rsync -avz $SYNC_OPT "$src" "$SYNC_DST"
rsync -avz $SYNC_OPT "$src" "$SYNC_DST"

Now we try to sync the ‘src’ directory:

./ src
rsync -avz --exclude="*.[ch]" --exclude="*.[ch]pp" src
sending incremental file list
 Continue reading 
Posted in Bash, Programming, System Administration | Tagged , , , | 2 Comments