Category: Linux Sysadmin

77 posts

One-liner: restore compressed MySQL backup

— 20,564 views — 2 Comments

One-liners are always fun. Linux has pipes, streams and redirects which you can combine to do many things in just one line. Today I had to restore a MySQL backup that was gzipped. Instead of typing:


gunzip db-backup.sql.gz
mysql -p -h db.example.com dbname < db-backup.sql

You can type this instead:


gunzip < db-backup.sql.gz | mysql -p -h db.example.com dbname

The .sql.gz file is input to gunzip, the resulting (unzipped) data is piped to mysql and is used as input there.

It will preserve the .gz file and saves diskspace as the unzipped file is not saved to disk. When using big databases, this is also a time saver. When using two commands you have to wait for the file to unzip, before starting the actual import. No intervention is needed when using the one-liner.

Fixing MySQL replication after a faulty query

— 2,861 views — No comments

Today I run into an issue with MySQL replication that prevented updates from the master to appear on the slaves. When checking the slave status, MySQL reports:

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Error: Query caused different errors on master and slave.

Apparently, an error occurred on the master (like a query that was wrong) and that failed on the slave as well. But since the error messages differ (a bit) the replication was stuck on this query. Manual intervention is required to tell MySQL what to do next.

This is what I used to fix it:

mysql -p
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

I logged in into MySQL (line 1), stopped the slave thread (line 2), skipped the one faulty query (line 3) and started the slave thread again (final line). The status now reports both a running IO and SQL thread.

mysql> show slave status\G

Output:

 Slave_IO_State: Waiting for master to send event
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

I’ve been building redundant storage solutions for years. At first, I used it for our webcluster storage. Nowadays it’s the base of our CloudStack Cloud-storage. If you ask me, the best way to create a redundant pair of Linux storage servers using Open Source software, is to use DRBD. Over the years it has proven to be rock solid to me.

DRBD is a Distributed Replicated Block Device. You can think of DRBD as RAID-1 between two servers. Data is mirrored from the primary to the secondary server. When the primary fails, the secondary takes over and all services remain online. DRBD provides tools for failover but it does not handled the actual failover. Cluster management software like Heartbeat and PaceMaker are made for this.

In this post I’ll show you how to install and configure DRBD, create file systems using LVM2 on top of the DRBD device, serve the file systems using NFS and manage the cluster using Heartbeat.

Installing and configuring DRBD
I’m using mostly Debian so I’ll focus on this OS. I did setup DRBD on CentOS as well. You need to use the ELREPO repository to find the right packages.

Squeeze-backports has a newer version of DRBD. If you, like me, want to use this version instead of the one in Squeeze itself, use this method to do so:

echo "
deb http://ftp.debian.org/debian-backports squeeze-backports main contrib non-free
" >> /etc/apt/sources.list

echo “Package: drbd8-utils Pin: release n=squeeze-backports Pin-Priority: 900 " > /etc/apt/preferences.d/drbd

Then install the DRBD utils:

apt-get update
apt-get install drbd8-utils

As the DRBD-servers work closely together, it’s important to keep the time synchronised. Install a NTP system for this job.

apt-get install ntp ntpdate

You also need a kernel module but that one is in the stock Debian kernel. If you’re compiling kernels yourself, make sure to include this module. When you’re ready, load the module:

modprobe drbd

Verify if all went well by checking the active modules:

lsmod | grep drbd

The expected output is something like:

drbd 191530 4 
lru_cache 12880 1 drbd
cn 12933 1 drbd

Most online tutorials instruct you to edit ‘/etc/drbd.conf’. I’d suggest not to touch that file and create one in /etc/drbd.d/ instead. That way, your changes are never overwritten and it’s clear what local changed you made.

vim /etc/drbd.d/redundantstorage.res

Enter this configuration:

resource redundantstorage {
 protocol C;
 startup { wfc-timeout 0; degr-wfc-timeout 120; }

disk { on-io-error detach; } on storage-server0.example.org { device /dev/drbd0; disk /dev/sda3; meta-disk internal; address 10.10.0.86:7788; } on storage-server1.example.org { device /dev/drbd0; disk /dev/sda3; meta-disk internal; address 10.10.0.88:7788; } }

Make sure your hostnames match the hostnames in this config file as it will not work otherwise. To see the current hostname, run:

uname -n

Modify /etc/hosts, /etc/resolv.conf and/or /etc/hostname to your needs and do not continue until the actual hostname matches the one you set in the configuration above.

Also, make sure you did all the steps so far on both servers.

It’s now time to initialise the DRBD device:

drbdadm create-md redundantstorage
drbdadm up redundantstorage
drbdadm attach redundantstorage
drbdadm syncer redundantstorage
drbdadm connect redundantstorage

Run this on the primary server only:

drbdadm -- --overwrite-data-of-peer primary redundantstorage

Monitor the progress:

cat /proc/drbd

Start the DRBD service on both servers:

service drbd start

You now have a raw block device on /dev/drbd0 that is synced from the primary to the secondary server.

Using the DRBD device
Let’s create a filesystem on our new DRBD device. I prefer using LVM since that makes it easy to manage the partitions later on. But you may also simply use the /dev/drbd0 device as any block device on its own.

Initialize LVM2:

pvcreate /dev/drbd0
pvdisplay
vgcreate redundantstorage /dev/drbd0

We now have a LVM2 volume group called ‘redundantstorage’ on device /dev/drbd0

Create the desired LVM partitions on it like this:

lvcreate -L 1T -n web_files redundantstorage
lvcreate -L 250G -n other_files redundantstorage

The partitions you create are named like the volume group. You can now use ‘/dev/redundantstorage/web_files’ and ‘/dev/redundantstorage/other_files’ like you’d otherwise use ‘/dev/sda3’ etc.

Before we can actually use them, we need to create a file system on top:

mkfs.ext4 /dev/redundantstorage/web_files
mkfs.ext4 /dev/redundantstorage/other_files

Finally, mount the file systems:

mkdir /redundantstorage/web_files
mkdir /redundantstorage/other_files
mount /dev/redundantstorage/web_files /redundantstorage/web_files
mount /dev/redundantstorage/other_files /redundantstorage/other_files

Using the DRBD file systems
Two more steps are needed to set up before we can test our new redundant storage cluster: Heartbeat to manage the cluster and NFS to make use of it. Let’s start with NFS, so Heartbeat will be able to manage that late on as well.

To install NFS server, simply run:

apt-get install nfs-kernel-server

Then setup what folders you want to export using your NFS server.

vim /etc/exports

And enter this configuration:

/redundantstorage/web_files 10.10.0.0/24(rw,async,no_root_squash,no_subtree_check,fsid=1)
/redundantstorage/other_files 10.10.0.0/24(rw,async,no_root_squash,no_subtree_check,fsid=2)

Important:
Pay attention to the the ‘fsid’ parameter. It is really important because it tells the clients that the file system on the primary and secondary are both the same. If you omit this parameter, the clients will ‘hang’ and wait for the old primary to come back online after a fail over happens. Since this is not what we want, we need to tell the clients the other server is simply the same. Fail-over will then happen almost without notice. Most tutorials I read do not tell you about this crucial step.

Make sure you have all this setup on both servers. Since we want Heartbeat to manage our NFS server, we need not to start NFS on boot. To do that, run:

update-rc.d -f nfs-common remove
update-rc.d -f nfs-kernel-server remove

Basic Heartbeat configuration
Install the heartbeat packages is simple:

apt-get install heartbeat

If you’re on CentOS, have a look at the EPEL repository. I’ve successfully setup Heartbeat with those packages as well.

To configure Heartbeat:

vim /etc/ha.d/ha.cf

Enter this configuration:

autojoin none
auto_failback off
keepalive 2
warntime 5
deadtime 10
initdead 20
bcast eth0
node storage-server0.example.org
node storage-server1.example.org
logfile /var/log/heartbeat-log
debugfile /var/log/heartbeat-debug

I set ‘auto_failback’ to off, since I do not want another fail-over when the old primary comes back. If your primary server has better hardware than the secondary one, you may want to set this to ‘on’ instead.

The parameter ‘deadtime’ tells Heartbeat to declare the other node dead after this many seconds. Heartbeat will send a heartbeat every ‘keepalive’ number of seconds.

Protect your heartbeat setup with a password:

echo "auth 3
3 md5 your_secret_password
" > /etc/ha.d/authkeys
chmod 600 /etc/heartbeat/authkeys

You need to select an ip-address that will be your ‘service’-address. Both servers have their own 10.10.0.x ip-address, so choose another one in the same range. I use 10.10.0.10 in this example. Why we need this? Simply because you cannot know to which server you should connect. That’s why we will instruct Heartbeat to manage an extra ip-address and make that alive on the current primary server. When clients connect to this ip-address it will always work.

In the ‘haresources’ file you describe all services Heartbeat manages. In our case, these services are:
– service ip-address
– DRBD disk
– LVM2 service
– Two filesystems
– NFS daemons

Enter them in the order they need to start. When shutting down, Heartbeat will run them in reversed order.

vim /etc/ha.d/haresources

Enter this configuration:

storage-server0.example.org \
IPaddr::10.10.0.10/24/eth0 \
drbddisk::redundantstorage \
lvm2 \
Filesystem::/dev/redundantstorage/web_files::/redundantstorage/web_files::ext4::nosuid,usrquota,noatime \
Filesystem::/dev/redundantstorage/other_files::/redundantstorage/other_files::ext4::nosuid,usrquota,noatime \
nfs-common \
nfs-kernel-server

Use the same Heartbeat configuration on both servers. In the ‘haresources’ file you specify one of the nodes to be the primary. In our case it’s ‘storage-server0’. When this server is or becomes unavailable, Heartbeat will start the services it knows on the other node, ‘storage-server1’ in this case (as specified in the ha.cf config file).

Wrapping up
DRBD combined with Heartbeat and NFS creates a powerful, redundant storage solution all based on Open Source software. When using the right hardware you will be able to achieve great performance with this setup as well. Think about RAID controllers with SSD-cache and don’t forget the Battery Backup Unit so you can enable the Write Back Cache.

Enjoy building your redundant storage!

After all the hard work in the past months it’s now time for some weeks off 🙂 One last thing to solve was the Zimbra auto responder: I had enabled it through the web interface but while testing it, I found it only replied mails sent to my main e-mail address. There is no way to fix this in the web interface, so I had a look at the command line options and found a way to add extra e-mail addresses. Ssh to the machine, become user ‘zimbra’ and run this command: 

zmprov ma [email protected] +zimbraPrefOutOfOfficeDirectAddress [email protected]

By running this command you tell Zimbra to also reply to [email protected] instead of only [email protected]. By specifying a + before the command, you can run this command multiple times to add more e-mail addresses, if you have them. Otherwise you’ll overwrite the previous setting. I have added all 3 e-mail addresses I use, so they’ll get auto responses while I’m out of the office. Hope this is helpful to others as well.

HOWTO remove null characters from a file

— 11,218 views — 4 Comments

Sometimes files may be filled up with null characters that look like ^@ when you open them in a text editor. This may happen when a disk becomes full, or when you rename a logfile while an application is still writing to it.

I ran into this problem today, and I fixed it using a command called ‘tr’. This is a utility capable of translating or deleting characters from standard input/output. It means you can use it to ‘pipe’ input to it, and send the output to a new file. For example:

cat file.log | tr -d '\000'  > new_file.log 

Note: when using this in a script, you might need to escape that backslash.

What does this command do? Using the -d switch we delete a character. A backslash followed by three 0’s represents the null character. This just deletes these characters and writes the result to a new file. Problem solved!

At work, it is of my tasks to make sure we keep the mailboxes of our clients free of spam. Some weeks ago the number of spam mails went up massively and we worked hard to update the filters to keep unwanted mails out. In this blog I’ll describe a few of the things we did.

Using the famous SpamAssassin tool it is possible to score e-mails. One can score on the contents of subject, body, headers etcetera. A lot of good rules are already supplied and it’s possible to write your own. When a new spam run comes in, we used to create new rules for the spam mails that slipped trough. That works, and afterwards the mails are tagged as spam.

As you can imagine, this procedure is both time consuming and a bit late: only after we see mails slipping through we can create rules to catch them. Of course this procedure will always be some sort of a last resort if all else fails but I wanted to setup something more proactive.

To start from the beginning, how is all this spam sent?
It can’t be send from one or a few locations only, because then it’d be easy to block. Instead, most spam is sent by botnets these days. Botnets usually have hundreds of thousands pc’s under control and one of the main things they do is sending spam. For example to advertise online casino’s, fake banking sites or other scams. Because there are so many infected PC’s, it’s not easy to block them all. Or is it?

When thinking about this, I realized most (if not all) members of these botnets are infected Windows pc’s. Also, these mails are often directly sent from the PC to the final destination mailserver (instead of using the SMTP server of their ISP).

If we could detect the OS of the client that connects to our mailserver, we could then apply certain actions based on the OS. The idea here is that most ISP’s use Linux, Unix or Mac servers. And if they are using Windows, it is likely to be some Server version instead of ‘Windows Vista’, ‘Windows XP’, etcetera. Interesting!

What we want to do here is known as Passive OS Fingerprinting. A tool that implements this is for example P0f. You run P0f as a deamon on the mailserver that accepts the incoming connections. Based on the traffic that flows by, P0f is able to guess the OS of the client that connects. It is passive, so the client never knows we’re doing this. Nothing is in between the client and the mailserver, P0f is just analyzing the traffic. Now that P0f knows the OS of the client, we can decide what to do with this information. In our setup it works like this:

1. When the OS is Windows, but not Windows Server, activate Greylisting. When another or unknown OS is detected, start mail delivery immediately;

The idea behind this is that mail sent from infected Windows PC’s is usually poorly written. They cannot handle the fact the mailserver sends a 400 series temporary error message and most give up after just one attempt. This technique is called Greylisting and it alone reduces the number of spam mails significantly. But, Greylisting has drawbacks as well. The biggest drawback in my opinion is that it can delay mail up to 30 minutes or more. Most customers we serve find this annoying.

2. At the time the connection is accepted and the mail is delivered, we set a ‘X-P0f-OS:’-header with the detected OS;

Combining Greylisting and P0f creates a more ideal solution: Windows PC’s should not send mail directly to the recipients mailserver, but use the Provider SMTP server instead. One could say that when such a PC is sending mails directly, it is at least suspicious. That is, in my opinion, enough reason to Greylist them. There must be some spam software that understands Greylisting (now or in the future) and that will eventually connect again after some time and deliver the mail. That’s why there is an action #3:

3. This header is consulted later on in the delivery process, and when Windows appears (again, not Windows Server), Spamassassin assigns some points to the spam-score.

Because mail that is sent directly from a Windows PC is suspicious to me. The OS score helps reaching the score needed for Spamassassin to tag it. The interesting thing is that this is proactive: you just don’t know what new mail spammers will send, but what you do know is that the next mail is probably send by an infected Windows PC.

This setup is now up and running, so I’ll let you all know what my experiences are after some time. When I find the time, I will also write some blogs in more detail on how to setup such a system.

Any other methods you use to stop spam effectively?

I had an interesting problem lately regarding AWStats. Due to some delay, the log files weren’t processed in the right order and then AWStats ignored all old logs. This resulted in some days being blank in the stats and of course this is not something we want. Since we also have multiple web servers in our cluster, things started to get a bit complicated.

The log files from each of the web servers were concatenated and then split to a separate log file for each virtual host using the Apache2 split-logfile script.

The logs for an example virtual host looked like this:

1.2.2.1 - - [01/Aug/2012:05:50:50 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_04"
1.2.2.1 - - [01/Aug/2012:05:50:51 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_04"
1.2.2.1 - - [28/Jul/2012:04:02:06 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_32"
1.2.2.1 - - [28/Jul/2012:04:02:06 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_32"

As you can see, AWStats processes August 1 and then refuses the older July records. To resort the log files, I ran:


cat website.unsorted.log | sort -t ' ' -k 4.9,4.12n -k 4.5,4.7M -k 4.2,4.3n -k 4.14,4.15n -k 4.17,4.18n -k 4.20,4.21n > website.log

As an alternative the AWStats scriptlogresolvemerge.pl can be used as well. Since I already had concatenated the log files and split them, the sort option above was faster to implement.

Now the log file looks like this:

1.2.2.1 - - [28/Jul/2012:04:02:06 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_32"
1.2.2.1 - - [28/Jul/2012:04:02:06 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_32"
1.2.2.1 - - [01/Aug/2012:05:50:50 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_04"
1.2.2.1 - - [01/Aug/2012:05:50:51 +0200] "GET /nonexistent_page.html HTTP/1.1" 404 224 "-" "Java/1.6.0_04"

One last thing to solve was the AWStats history file. Since it had run before but with the wrong ordenend logfile, it had a wrong ‘LastLine’ setting. Experimenting with this showed it was best to remove the line, and replace it with a newline (so we won’t break the indexes). I used sed to fix it:


sed -i \
-e 's/^LastLine .*//' \
awstats072012.*

AWStats now updates the stats correctly and everybody is happy! Thanks to my colleagues Pim, Vincent and Mischa because they all helped solving some pieces of the puzzle. Yes, it’s nice having some technically skilled colleagues 🙂

 

My mother’s iPhone had issues and was replaced by a new one. That did Apple handle very well 🙂 The down side of a new iPhone is, of course, that all settings and photo’s were lost. Fortunately, I was smart enough to enable iCloud backups last year when I setup her iPhone.

So, when the new iPhone came in today, all I did was restore the iCloud backup in a few swipes and clicks. An hour or so later (due to downloading) everything was as before. Very cool!

I recommend everybody to setup iCloud backups. You find the settings in the iCloud screen. It’s easy and you never know…

 

Removing old equipment from datacenter

— 152 views — No comments

We’ve virtualized many servers already this year. Last month we moved the MD equipment that still needs to be virtualized. Today we removed the old MN servers that are no longer needed due to our new private cloud. All our main equipment is now together in the DC-2 datacenter in Amsterdam!

I’m upgrading our MySQL master/slave setup and am moving it to new (virtual) hardware in our cloud environment. One of the things I did last night was moving the MySQL slaves to a new master that I had prepared in the new environment. This post describes how I connected the slaves to their new master in the cloud.

First, you’ll need to make sure the new master has the same data as the old one.
1. Make sure no more updates occur on the old master
2. Create a sql dump of the master using mysqldump
3. Import that dump into the new master using mysql cmd line tool

At this point both masters should have the same data.
4. Now, shut down the old master as it can be retired 😉
5. Before allowing write access to the new master, note it’s position by executing this query:

mysql> show master status\G;
File: mn-bin.000005
Position: 11365777
Binlog_Do_DB: database_name
Binlog_Ignore_DB:
1 row in set (0.00 sec)

We’ll need this information later on when instructing the slaves to connect to their new master.

6. It’s now safe to allow write access again to the new master
7. Do this on any slave, it will connect it to the new master:

CHANGE MASTER TO
master_host=’master_hostname’,
master_user=’replicate_user’,
master_password=’password’,
master_log_file=’log-bin.000005‘,
master_log_pos= 11365777

Note the ‘master_log_file’ and ‘master_log_pos’. Their values are the ones we selected from the master at step 5. Then check if it worked (allow a few seconds to connect):

mysql> show slave status\G;

Look for these lines, they should say ‘Yes’:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

And the status should be:

Slave_IO_State: Waiting for master to send event

That’s it, the slave is now connected to a new master. Test it by updating the master, and checking whether the slave receives the update too.