Friday, February 19, 2010

Postfix as a backup MX host

SkyHi @ Friday, February 19, 2010

I've had Postfix/amavisd-new/SpamAssassin/ClamAV/Courier running smoothly on my toaster for a while, thanks to Christoph Haas. Paranoid thoughts have since crept in; what would happen if my toaster were to stop working?

Luckily, I'm the kind of guy who has an old laptop many miles away, quietly humming away under my parents' desk. (No, I don't live in my parents' basement) This would do nicely as a backup MX!

My victim in this case is a Toshiba PIII 700 with a dizzying 64MB RAM, on which I've installed Ubuntu breezy. My parents have a dynamic IP address, which seldom changes in practice. Usually, it's not practical operate a mail server on a dynamic IP because many major ISPs like Yahoo and AOL block any mail from such IP ranges. In this case I'm not concerned, the purpose of a backup MX is to hold on to mail until the primary MX becomes available again, so it is only delivering mail to servers under my control (I will not block my own mail!).

I need to give the backup MX a hostname, I've chosen mail2.toastputer.net - I'm so creative. Fortunately, toastputer.net is hosted by dyndns.org, so I will set the laptop to update it's ip address with ddclient.

# apt-get install ddclient

ddclient works with other services like Hammernode, Zoneedit and EasyDNS. It is very easy to set up.

Lets get on with it and install postfix:

# apt-get install postfix

I chose 'no configuration', because I wanted complete control. Once Postfix is installed, cd to /etc/postfix and sudo vi main.cf

Here's my main.cf with comments to explain what's going on:

#This is the default and will do for me<br /><br />smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)<br /><br />#Notifies users of new mail using comsat.  Since I have no local users or comsat, seems sensible to turn it off<br /><br />biff = no<br /><br /># appending .domain is the MUA's job. - disable rewriting of user@host to user@host.domain<br /><br />append_dot_mydomain = no<br /><br /><br /><br />#Trust no-one except yourself.  IP addresses in mynetworks can relay mail to any address<br /><br />mynetworks = 127.0.0.1/8<br /><br /><br /><br />#Listen on all ip addresses<br /><br />inet_interfaces = all<br /><br /><br /><br />#Who we will accept mail for<br /><br />relay_domains = hash:/etc/postfix/relays<br /><br />#Where it will be sent<br /><br />transport_maps = hash:/etc/postfix/transport<br /><br /><br /><br />smtpd_recipient_restrictions = permit_mynetworks, check_relay_domains<br />

Here is /etc/postfix/transport:

stocksy.co.uk smtp:mail.toastputer.net<br /><br />mattsfoolsgold.co.uk smtp:mail.toastputer.net<br /><br />#...etc<br />

and /etc/postfix/relays

stocksy.co.uk OK<br /><br />mattsfoolsgold.co.uk OK<br /><br />#...etc<br />

It's necessary to run # postmap /etc/postfix/transport and # postmap /etc/postfix/relays so that Postfix can read the files.

Restart Postfix:

/etc/init.d/postfix restart

Check that it actually works (from another host!):

$ telnet mail2.toastputer.net 25<br /><br />Trying 82.21.124.237...<br /><br />Connected to mail2.toastputer.net.<br /><br />Escape character is '^]'.<br /><br />Postfix: 220 mail2.toastputer.net ESMTP Postfix (Ubuntu)<br /><br />You: ehlo www.stocksy.co.uk<br /><br />Postfix: 250-mail2.toastputer.net<br /><br />Postfix: 250-PIPELINING<br /><br />Postfix: 250-SIZE 10240000<br /><br />Postfix: 250-VRFY<br /><br />Postfix: 250-ETRN<br /><br />Postfix: 250 8BITMIME<br /><br />You: mail from:<stocksy@SPAMMERSDIEstocksy.co.uk><br /><br />Postfix: 250 Ok<br /><br />You: rcpt to:<stocksy@NOSPAMMERSDIEstocksy.co.uk><br /><br />Postfix: 250 Ok<br /><br />You: data<br /><br />Postfix: 354 End data with <CR><LF>.<CR><LF%gt;<br /><br />You: Subject: Test message to test backup MX<br /><br />You: This is the message body.<br /><br />You: .<br /><br />Postfix: 250 Ok: queued as 47EDE57B81<br /><br />You: quit<br /><br />Postfix: 221 Bye<br /><br />Connection closed by foreign host.<br />

If you receive the email, good! If not, tail -f /var/log/mail.log should tell you why.

Check that you are not an open relay!

$ telnet relay-test.mail-abuse.org<br />

With this in place, I needed to update the MX records for my domain. This differs from provider to provider, but you must set your primary MX's priority lower than the backup, e.g:

$ dig mx stocksy.co.uk<br /><br /><br /><br />; <<>> DiG 9.2.2 <<>> mx stocksy.co.uk<br /><br />;; global options:  printcmd<br /><br />;; Got answer:<br /><br />;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 14586<br /><br />;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 2, ADDITIONAL: 2<br /><br /><br /><br />;; QUESTION SECTION:<br /><br />;stocksy.co.uk.                 IN      MX<br /><br /><br /><br />;; ANSWER SECTION:<br /><br />stocksy.co.uk.          7190    IN      MX      5 mail2.toastputer.net.<br /><br />stocksy.co.uk.          7190    IN      MX      0 mail.toastputer.net.<br /><br /><br /><br />;; AUTHORITY SECTION:<br /><br />stocksy.co.uk.          7190    IN      NS      ns17.zoneedit.com.<br /><br />stocksy.co.uk.          7190    IN      NS      ns8.zoneedit.com.<br /><br /><br /><br />;; ADDITIONAL SECTION:<br /><br />ns8.zoneedit.com.       172790  IN      A       206.55.124.4<br /><br />ns17.zoneedit.com.      172790  IN      A       209.126.159.118<br /><br /><br /><br />;; Query time: 3 msec<br /><br />;; SERVER: 172.16.0.81#53(172.16.0.81)<br /><br />;; WHEN: Sun Nov 27 18:43:02 2005<br /><br />;; MSG SIZE  rcvd: 169<br />

Test it. Stop the MTA on your primary MX and send yourself some email from gmail (or something). If you $ tail -f /var/log/mail.log on your backup MX, you'll see the mail queuing up. Start your MTA again and it'll get delivered to you. Use # postqueue -f if you are impatient.


REFERENCE

http://www.stocksy.co.uk/articles/Linux/postfix_as_a_backup_mx_host/



Setting Up Postfix As A Backup MX

SkyHi @ Friday, February 19, 2010

In this tutorial I will show how you can set up a Postfix mailserver as a backup mail exchanger for a domain so that it accepts mails for this domain in case the primary mail exchanger is down or unreachable, and passes the mails on to the primary MX once that one is up again.

I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

I want to set up a backup MX for the domain example.com. In this example the primary MX for example.com is called mx1.example.com (IP address 1.2.3.4), so I call the backup MX mx2.example.com (IP address 1.2.3.5).

I have created MX records for example.com that look like this:

example.com.               86400   IN      MX      10 mx1.example.com.
example.com.               86400   IN      MX      20 mx2.example.com.

It's important that the primary MX has a lower number (10) and therefore a higher priority than the backup MX (20).

I'm assuming that the Postfix on mx2.example.com is already installed and working.

 

2 Configuring Postfix On mx2.example.com

To make mx2.example.com a backup MX for the domain example.com, all we have to do is change/add three lines to /etc/postfix/main.cf:

vi /etc/postfix/main.cf

First make sure that smtpd_recipient_restrictions contains permit_mynetworks and reject_unauth_destination, so something like this would be ok:

[...]
smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination
[...]

Then we must add example.com to the relay_domains paramater; if there's no relay_domains paramater yet in /etc/postfix/main.cf, the following will do:

[...]<br />relay_domains = $mydestination, example.com<br />[...]

And finally we add an empty relay_recipient_maps parameter to /etc/postfix/main.cf:

[...]<br />relay_recipient_maps =<br />[...]

(That way we don't have to specify a list of valid email addresses to back up, which might be a daunting task if you have to manage hundreds of email accounts.)

There's one important thing I have to add: You must not list example.com in the following parameters in /etc/postfix/main.cf:

  • mydestination
  • virtual_alias_domains
  • virtual_mailbox_domains

That's it already. All we have to do now is restart Postfix:

/etc/init.d/postfix restart

 

3 Testing

To test the new backup MX, we take down the MTA (Postfix, Sendmail, Exim, etc.) on mx1.example.com and send an email from some remote server to an example.com account (e.g. someuser@example.com).

If you have access to the mail log on the remote (sending) server, you should now find something like this in it:

Jun 6 18:29:16 mail postfix/smtp[17746]: AF814144146: to=<someuser@example.com>, relay=mx2.example.com[1.2.3.5], delay=1, status=sent (250 2.0.0 Ok: queued as DCA5A1BF40F)

As you see, the mail has been sent to mx2.example.com instead of mx1.example.com because mx1.example.com is unreachable. Now, let's take a look at the mail log of mx2.example.com:

Jun 6 18:29:16 mx2 postfix/qmgr[3049]: DCA5A1BF40F: from=<falko@blabla.tld>, size=892, nrcpt=1 (queue active)
Jun 6 18:29:16 mx2 postfix/smtpd[3051]: disconnect from mail.blabla.tld[1.2.3.6]
Jun 6 18:29:16 mx2 postfix/smtp[3057]: connect to mx1.test1.de[1.2.3.4]: Connection refused (port 25)
Jun 6 18:29:16 mx2 postfix/smtp[3057]: DCA5A1BF40F: to=<someuser@example.com>, relay=none, delay=0.07, delays=0.03/0.02/0.01/0, dsn=4.4.1, status=deferred (connect to mx1.test1.de[1.2.3.4]: Connection refused)

mx2.example.com has accepted the mail and tried to connect to mx1.example.com to deliver it to the primary MX. Because the primary MX is down, mx2.example.com cannot deliver the mail and keeps it in the mailqueue until mx1.example.com is available again.

Now we start the MTA on mx1.example.com again. The backup MX will not immediately deliver the queued mail, but after some minutes you should see something like this in the mail log of mx2.example.com:

Jun 6 18:56:44 mx2 postfix/qmgr[3080]: DCA5A1BF40F: from=<falko@blabla.tld>, size=892, nrcpt=1 (queue active)
Jun 6 18:56:45 mx2 postfix/smtp[3083]: DCA5A1BF40F: to=<someuser@example.com>, relay=mx1.example.com[1.2.3.4]:25, delay=1648, delays=1648/0.09/0.4/0.12, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 167995B0109)

The mail has been delivered to the primary MX where you can see this in the mail log:

Jun 6 18:56:45 mx1 postfix/local[4963]: 167995B0109: to=<someuser@server1.example.com>, orig_to=<someuser@example.com>, relay=local, delay=0.54, delays=0.08/0.02/0/0.43, dsn=2.0.0, status=sent (delivered to command: /usr/bin/procmail -f-)

So no mails were lost while mx1.example.com was down, and users can continue to retrieve their mails from mx1.example.com.

 


REFERENCE

http://www.howtoforge.com/postfix_backup_mx



Configuring Postfix as backup MX host

SkyHi @ Friday, February 19, 2010

Overview

The backup MX host must accept and queue mails, if the primary mailhost is down for a certain domain. To have a high degree of availability the backup MX host must be located outside the backed up domain. You can setup the backup MX host as a primary or secondary mx for a remote site.

The primary mailhost is down ...

Email is delivered to the backup MX host and queued there ....

After the primary mailhost is up and running again, email is delivered from the backup MX host to the primary mailhost and stored in the local mailboxes.

Setup of a primary mx host for a remote site

  • DNS entry on the remote site:

IN MX 10 mail1.backup1.com.
IN MX 20 mail2.backup2.com.

All email for the remote site is delivered to the primary mx host: mail1.backup1.com.

  • Postfix Configuration on backup1.com:

/etc/postfix/main.cf:

relay_domains = $mydestination the.backed-up.domain.name
smtpd_recipient_restrictions = permit_mynetworks
                               check_relay_domains

/etc/postfix/main.cf:

transport_maps = hash:/etc/postfix/transport

/etc/postfix/transport:

the.backed-up.domain.name smtp:[their.mail.host.name]

Setup of a secondary mx host for a remote site

  • DNS entry on the remote site:

IN MX 10 mail1.backup1.com.
IN MX 20 mail2.backup2.com.

All email for the remote site is delivered to the secondary mx host: mail2.backup2.com.

  • Postfix Configuration on backup2.com:

/etc/postfix/main.cf:

relay_domains = $mydestination the.backed-up.domain.name
smtpd_recipient_restrictions = permit_mynetworks
                               check_relay_domains




REFERENCE
http://www.akadia.com/services/postfix_mx_backup.html

Postfix Backup MX Question

SkyHi @ Friday, February 19, 2010
This should do it:

/etc/postfix/main.cf:
relay_domains = widgets.com.au
transport_maps = hash:/etc/postfix/transport

/etc/postfix/transport:
widgets.com.au relay:[primarymx.widgets.com.au]

# postmap /etc/postfix/transport
# postfix reload

* do not list widgets.com.au in mydestination, virtual_alias_domains or virtual_mailbox_domains *




============================================
I went for the dumb forward option without consideration for backscatter.

It's an easy mistake to make ;-)

Recipient verification is easy to set up. You can read more at www.postfix.org/ADDRESS_...E.html#recipient

EDIT: I'd also recommend setting up a persistent database, but without caching negative responses... or set them to be forgotten in a very short time. (Negative caching, for some reason, is ON by default.) Otherwise, the database becomes very big with all the negative responses - it is amazing how many spammers out there scan for valid email addresses. See www.postfix.org/ADDRESS_...DME.html#caching

EDIT 2: I forgot to mention that persistent databases are not recommended by the Postfix documentation:
If the persistent database ever gets corrupted then the world comes to an end and human intervention is needed. This violates a basic Postfix principle.
I've been using it on servers doing around 1,000 messages per day (considered light traffic) for about a year without a hitch. If you want to get paranoid about it, you could write a script to delete the database file and restart postfix if the queue size gets too big. Postfix would then renew the database file and the sky would stop falling. I have not gone this far myself because the problem has never happened.

REFERENCE
http://forums.whirlpool.net.au/forum-replies-archive.cfm/931344.html
http://www.postfix.org/STANDARD_CONFIGURATION_README.html#backup

Configuring Postfix as primary or backup MX host for a remote site

This section presents additional configuration. You need to combine this with basic configuration information as discussed the first half of this document.

When your system is SECONDARY MX host for a remote site this is all you need:

 1 DNS:
2 the.backed-up.domain.tld IN MX 100 your.machine.tld.
3
4 /etc/postfix/<a href="http://www.postfix.org/postconf.5.html">main.cf</a>:
5 <a href="http://www.postfix.org/postconf.5.html#relay_domains">relay_domains</a> = . . . the.backed-up.domain.tld
6 <a href="http://www.postfix.org/postconf.5.html#smtpd_recipient_restrictions">smtpd_recipient_restrictions</a> =
7 <a href="http://www.postfix.org/postconf.5.html#permit_mynetworks">permit_mynetworks</a> <a href="http://www.postfix.org/postconf.5.html#reject_unauth_destination">reject_unauth_destination</a>
8
9 # You must specify your NAT/proxy external address.
10 #<a href="http://www.postfix.org/postconf.5.html#proxy_interfaces">proxy_interfaces</a> = 1.2.3.4
11
12 <a href="http://www.postfix.org/postconf.5.html#relay_recipient_maps">relay_recipient_maps</a> = hash:/etc/postfix/relay_recipients
13
14 /etc/postfix/relay_recipients:
15 user1@the.backed-up.domain.tld x
16 user2@the.backed-up.domain.tld x
17 . . .

When your system is PRIMARY MX host for a remote site you need the above, plus:

18 /etc/postfix/<a href="http://www.postfix.org/postconf.5.html">main.cf</a>:<br />19     <a href="http://www.postfix.org/postconf.5.html#transport_maps">transport_maps</a> = hash:/etc/postfix/transport<br />20 <br />21 /etc/postfix/transport:<br />22     the.backed-up.domain.tld       relay:[their.mail.host.tld]<br />

Important notes:

  • Do not list the.backed-up.domain.tld in mydestination.

  • Do not list the.backed-up.domain.tld in virtual_alias_domains.

  • Do not list the.backed-up.domain.tld in virtual_mailbox_domains.

  • Lines 1-7: Forward mail from the Internet for "the.backed-up.domain.tld" to the primary MX host for that domain.

  • Line 10: This is a must if Postfix receives mail via a NAT relay or proxy that presents a different IP address to the world than the local machine.

  • Lines 12-16: Define the list of valid addresses in the "the.backed-up.domain.tld" domain. This prevents your mail queue from filling up with undeliverable MAILER-DAEMON messages. If you can't maintain a list of valid recipients then you must specify "relay_recipient_maps =" (that is, an empty value), or you must specify an "@the.backed-up.domain.tld x" wild-card in the relay_recipients table.

  • Line 22: The [] forces Postfix to do no MX lookup.

Specify dbm instead of hash if your system uses dbm files instead of db files. To find out what lookup tables Postfix supports, use the command "postconf -m".

Execute the command "postmap /etc/postfix/transport" whenever you change the transport table.

NOTE for Postfix < 2.2: Do not use the fallback_relay feature when relaying mail for a backup or primary MX domain. Mail would loop between the Postfix MX host and the fallback_relay host when the final destination is unavailable.

  • In main.cf specify "relay_transport = relay",
  • In master.cf specify "-o fallback_relay =" at the end of the relay entry.
  • In transport maps, specify "relay:nexthop..." as the right-hand side for backup or primary MX domain entries.

These are default settings in Postfix version 2.2 and later.

REFERENCE
http://www.postfix.org/STANDARD_CONFIGURATION_README.html#backup

Emergency Postfix Backup MX

SkyHi @ Friday, February 19, 2010
Well the primary MX went tits up today and because the boss is pinning me for the new job I have to do something about it. My mail server has been shut down for a while now but the config is still there, so I sat down to do some modifications.

After the first two problems were encountered and we'd been down for twenty minutes I said "stuff it" to myself and copied the main.cf file for later backups. I needed a quick and dirty solution to get the backup MX receiving emails for our domain, no spam, no filtering, we'll let the primary MX work it all out later.

root@dread /etc/postfix# grep -Ev '^#|^$' main.cf
relay_domains = 192.168.1.1 architects.example.com.au
smtpd_recipient_restrictions = check_relay_domains check_recipient_access hash:/etc/postfix/access, reject
transport_maps = hash:/etc/postfix/transport
myhostname = mx2.architects.example.org
root@dread /etc/postfix# grep -Ev '^#|^$' access
architects.example.com.au OK
root@dread /etc/postfix# grep -Ev '^#|^$' transport
architects.example.com.au smtp:[mail.example.org]
root@dread /etc/postfix# postfix start
postfix/postfix-script: starting the Postfix mail system
root@dread /etc/postfix#

It is that easy. This will receive all email for architects.example.com.au and relay it to mail.example.org as soon as it can, rejecting all else.

Edit: Changing file paths to avoid confusion over my symlinked folders.ß

Edit Two: UPDATE. If boss decides that you should be using multiple domains then we need to change relay domains to be:

Code: (Main.cf)
relay_domains = hash:/etc/postfix/access

Make sure to update your transport maps.


Ok this is an update to the original backup MX configuration you saw earlier. I've added a whole bunch of spam things in, including (though I hate to admit) Real time blacklist checks. I hate these things but they're easier than configuring spam assassin.

Code:
relay_domains = hash:/etc/postfix/access<br />recipient_access = hash:/etc/postfix/access<br />smtpd_recipient_restrictions =<br />        reject_unauth_pipelining,<br />        reject_rhsbl_client blackhole.securitysage.com,<br />        reject_rhsbl_sender blackhole.securitysage.com,<br />        reject_rbl_client zen.spamhaus.org,<br />        reject_rbl_client bl.spamcop.net,<br />        check_relay_domains,<br />        check_recipient_access,<br />        reject<br />smtpd_sender_restrictions = reject_non_fqdn_sender, reject_unknown_sender_domain<br />smtpd_helo_restrictions = reject_non_fqdn_helo_hostname, reject_unauth_pipelining<br />smtpd_data_restrictions = reject_unauth_pipelining<br />myhostname = mx2.architects.example.org<br />transport_maps = hash:/etc/postfix/transport<br />mynetworks_style = subnet<br />

This configuration enforces a few RFC-based checks on the sending machine to ensure we're getting somewhere decent. Note that this configuration does not do sender verification as I believe this is the worst thing ever.

Clean, simply, voila.

REFERENCE
http://www.razeor.com/forum/index.php?topic=325.0

Postfix Backup MX eMail Server Anti-Spam Configuration

SkyHi @ Friday, February 19, 2010

According to RFC2821 the lowest-numbered records are the most preferred MX for domain. So I've a target Postfix backup server to keep the messages in a queue waiting for the primary server to become available. This ensures that if my primary MX goes down I do not loss any emails. However, spammers are connecting to my backup MX to avoid anti spam filters that are running on the primary MX server. This also hides their real IP from my primary MX. How do I configure anti-spam for my backup RHEL / CentOS 5.3 based Postfix mx server?

This is well known issue. Make sure your backup MX runs the same config in terms of spam rejection as your primary server. Try the following to improve backup eMail server anti spam configuration.

If the backup MX acts as a store-and-forward mail server

Consider the following example:

nixcraft.com.	86400	IN	MX 	10 mx01.nixcraft.net.in.
nixcraft.com. 86400 IN MX 20 mx02.nixcraft.net.in.

nixcraft.com email handled by two email servers. mx02.nixcraft.net.in is your backup server. Open main.cf and append the following restrictions on mx02.nixcraft.net.in.

Only allow your own domain to accept email

Use relay_domains to relay email for two domain called nixcraft.com and cyberciti.com. Also, set lookup tables with all valid addresses in the domains that match $relay_domains i.e. only accept email for valid email address.
# vi /etc/postfix/main.cf
Modify settings as follows:

relay_domains = nixcraft.com, cyberciti.com, $mydestination<br />relay_recipient_maps = hash:/etc/postfix/relay_recipients

Create /etc/postfix/relay_recipients to accept email for vivek@nixcraft.com, vivek@cyberciti.com, user3@nixcraft.com and so on..

vivek@nixcraft.com   OK<br />vivek@cyberciti.com   OK<br />user3@nixcraft.com    OK

Save and close the file. Finally, update your db:
# postmap hash:/etc/postfix/relay_recipients

Anti spam via RBL

Now, add following lines main.cf to check spammer IP address using RBLs. Reject all email if they do not have a valid hostname or proper email address:

smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks,<br />  reject_non_fqdn_hostname,<br />  reject_non_fqdn_sender,<br />  reject_non_fqdn_recipient,<br />  reject_unauth_destination,<br />  reject_unauth_pipelining,<br />  reject_invalid_hostname,<br />  reject_rbl_client zen.spamhaus.org<br /># helo required<br />smtpd_helo_required = yes<br /># disable vrfy command<br />disable_vrfy_command = yes<br /><br />smtpd_data_restrictions =<br />            reject_unauth_pipelining,<br />            permit<br />

Save and close the file. Restart / reload postfix:
# service postfix reload
There are other anti UCE settings, see Postfix anti UCE cheat sheet for more information.

Nolisting mx A entry

Spammers email software does not retry higher-priority MX records. So all you have to do is create a non-existent primary mail server and a working secondary mail server, attempts to contact the primary mail server will always fail. This technique uses a non-existent primary mail server, which is compatible with all correctly configured mail servers such as Sendmail, MS-Exchange, Postfix, Qmail, Exim etc. Create BIND dns configuration as follows:

nixcraft.com.	86400	IN	MX 	10 mx01.nixcraft.net.in.<br />nixcraft.com.	86400	IN 	MX 	20 mx02.nixcraft.net.in.<br />nixcraft.com.	86400	IN 	MX 	30 mx03.nixcraft.net.in.<br />nixcraft.com.	86400	IN 	MX 	40 mx04.nixcraft.net.in.

Where,

  • mx02.nixcraft.net.in - Runs your actual primary MX with anti spam and anti virus configurations.
  • mx03.nixcraft.net.in - Your backup mx server with anti spam / virus and act as store and forward server for mx02.nixcraft.net.in.
  • mx01.nixcraft.net.in and mx04.nixcraft.net.in are nolist MX servers. They can either be dead (or point to non existing IP) or you can run SMTP on port 25 that always returns 4xx error so that legitimate MTA to retry on a lower numbered MX server. nolist MX servers can also used to get more information about spammers to blacklist them. Google for "spam filtering services that offer free nolist servers" specifically for botnet data harvesting.

Greylisting Backup MX

Postfix can be configured to temporarily reject any email from a sender it does not recognize. If the mail is legitimate, the originating server will try again and the email is accepted. If the mail is from a spammer it will probably not be retried since a spammer goes through thousands of email addresses and cannot afford the time delay to retry. See how to configure postfix greylist policy server.

Spamassassin+Amavis+Clamd For Backup MX Server

Spamassassin is open source mail filter, to identify spam using a wide range of heuristic tests on mail headers and body text. You can install Spamassassin spam checking on your backup server. Emails found to be Spam (with higher spam score) will be drop out before reaching your primary email server. You can also use Clamav / Amavis to scan email and drop or forward infected emails. Install spamassassin, clamd and amavisd-new using yum or apt-get commands (turn on EPEL repo under RHEL / CentOS to install the following packages):
# yum install clamav-server amavisd-new spamassassin

  • clamav-server : Clam Antivirus scanner server
  • amavisd-new : amavisd-new is a high-performance and reliable interface between Postfix and virus scanners, and/or
    Mail::SpamAssassin Perl module.
  • spamassassin : Spam filter for email which can be invoked from mail delivery agents or in our case via amavisd-new

Once done, add as the following to your /etc/postfix/main.cf:

content_filter=smtp-amavis:[127.0.0.1]:10024

Save and close the file. Open /etc/postfix/master.cf and add the following settings:

smtp-amavis unix - - n - 2 smtp<br />  -o smtp_data_done_timeout=2400<br />  -o smtp_send_xforward_command=yes<br />  -o disable_dns_lookups=yes<br />  -o max_use=20<br />127.0.0.1:10025 inet n - n - - smtpd<br />  -o content_filter=<br />  -o local_recipient_maps=<br />  -o relay_recipient_maps=<br />  -o smtpd_restriction_classes=<br />  -o smtpd_delay_reject=no<br />  -o smtpd_client_restrictions=permit_mynetworks,reject<br />  -o smtpd_helo_restrictions=<br />  -o smtpd_sender_restrictions=<br />  -o smtpd_recipient_restrictions=permit_mynetworks,reject<br />  -o mynetworks_style=host<br />  -o mynetworks=127.0.0.0/8<br />  -o strict_rfc821_envelopes=yes<br />  -o smtpd_error_sleep_time=0<br />  -o smtpd_soft_error_limit=1001<br />  -o smtpd_hard_error_limit=1000<br />  -o smtpd_client_connection_count_limit=0<br />  -o smtpd_client_connection_rate_limit=0<br />  -o receive_override_options=no_header_body_checks,no_unknown_recipient_checks,no_address_mappings

Save and close the file. Also, update /etc/amavisd/amavisd.conf with required settings.

$daemon_user  = 'amavis';     # (no default;  customary: vscan or amavis), -u<br />$daemon_group = 'amavis';     # (no default;  customary: vscan or amavis), -g<br />$mydomain = 'nixcraft.net.in';   # a convenient default for other settings<br />$log_level = 1;              # verbosity 0..5, -d<br />$DO_SYSLOG = 1;              # log via syslogd (preferred<br />$inet_socket_port = 10024;   # listen on this local TCP port(s) (see $protocol)<br />$sa_tag_level_deflt  = -999;  # add spam info headers if at, or above that level<br />$sa_tag2_level_deflt = 6.31; # add 'spam detected' headers at that level<br />$sa_kill_level_deflt = 6.31; # triggers spam evasive actions<br />$sa_dsn_cutoff_level = 10;   # spam level beyond which a DSN is not sent<br />$virus_admin               = 'postmaster\@nixcraft.net.in';                    # notifications recip.<br />$mailfrom_notify_admin     = 'postmaster\@nixcraft.net.in';                    # notifications sender<br />$mailfrom_notify_recip     = 'postmaster\@nixcraft.net.in';                    # notifications sender<br />$mailfrom_notify_spamadmin = 'postmaster\@nixcraft.net.in';                    # notifications sender<br />$mailfrom_to_quarantine = 'postmaster\@nixcraft.net.in'; # null return path; uses original sender if undef<br />$sa_spam_subject_tag = '***SPAM*** ';<br />$myhostname = 'mx02.nixcraft.net.in';  # must be a fully-qualified domain name!<br />$notify_method  = 'smtp:[127.0.0.1]:10025';<br />$forward_method = 'smtp:[127.0.0.1]:10025';  # set to undef with milter!<br /># add your server public ip, private ip,<br />@inet_acl = qw( 203.1.2.3 127/8  10.10.29.11);<br />

Save and close the file. Update spamassassin settings in /var/spool/amavisd/:
# usermod -s /bin/bash amavis
# passwd amavis
# su - amavis
$ razor-admin -discover
$ razor-admin -create
$ razor-admin -register -l -user=vivek@nixcraft.co.in -pass=somePassword
$ cd .spamassassin
$ cp /usr/share/spamassassin/user_prefs.template user_prefs
$ exit
# usermod -s /sbin/nologin amavis

Update /etc/clamd.d/amavisd.conf as follows:

# Use system logger.<br />LogSyslog yes<br /><br /># Specify the type of syslog messages - please refer to 'man syslog'<br /># for facility names.<br />LogFacility LOG_MAIL<br /><br /># This option allows you to save a process identifier of the listening<br /># daemon (main thread).<br />PidFile /var/run/amavisd/clamd.pid<br /><br /># Remove stale socket after unclean shutdown.<br /># Default: disabled<br />FixStaleSocket yes<br /><br /># Run as a selected user (clamd must be started by root).<br />User amavis<br /><br /># Path to a local socket file the daemon will listen on.<br />LocalSocket /var/spool/amavisd/clamd.sock

Update /etc/mail/spamassassin/local.cf as follows:

required_hits 6.31<br />report_safe 1<br />rewrite_subject         0<br /># Enable the Bayes system<br />use_bayes               1<br /># Enable Bayes auto-learning<br />auto_learn              1<br />

Save and close the file. Finally, restart postfix and other services:
# service clamd.amavisd start
# service amavisd start
# service postfix restart

Turn services on boot:
# chkconfig clamd.amavisd on
# chkconfig amavisd on
# chkconfig postfix on

Now, check your /var/log/maillog for any errors or details:
# netstat -tulpn -A inet| egrep ':25|:1002?'
# tail -f /var/log/maillog

Above configuration will open the following ports on server:

  1. 10024 - Amavisd
  2. 10025 - Amavisd will communicate back the results to Postfix
  3. 25 - SMTP Port

A note about same priority mx servers

You can point the mail servers, all with the same priority. It offers the following benfits:

  • Load balancing
  • Centralized user mail managment via LDAP or MySQL / PGSQL
  • Centralized virus scanning
  • Centralized Spam scanning

Sample dns records:

nixcraft.com.	86400	IN	MX 	10 mx01.nixcraft.net.in.<br />nixcraft.com.	86400	IN 	MX 	10 mx02.nixcraft.net.in.<br />nixcraft.com.	86400	IN 	MX 	10 mx03.nixcraft.net.in.<br />; imap server<br />imap		86400	IN 	A 	202.54.1.2<br />; pop3 server - can be CNAME too<br />pop3		86400	IN 	A 	202.54.1.2<br />

You may need additional servers inside your lan:

  • MySQL/OpenLDAP (10.24.116.2) - Store user name, email, mailbox and other information.
  • Central anti virus server (10.24.116.3) - Used by all your mx servers for scanning using TCP/IP. You can also do the same for spam scanning using TCP/IP.

Each mx server can use centralized anti spam and anti-virus server. Once scanned Postfix can deliver final mail which can be retrieved using POP3 / IMAP server.

Further Readings / References :

This FAQ assumed that you have working Postfix primary and backup server. It only covered anti spam related topics. For further details refer the following urls and respective man pages:


REFERENCE

http://www.cyberciti.biz/faq/postfix-backup-mx-server-anti-spam/



Thursday, February 18, 2010

Extract a Single Table from a mysqldump File

SkyHi @ Thursday, February 18, 2010
Method 1: Perl script

This script will parse a full mysqldump file and extract the necessary portions required to restore a single table. The output is printed to STDOUT, so you’ll want to redirect to a file from the command line, like so: extract_sql.pl > somefile.sql

Usage Summary (run the script with no parameters and you’ll see this):
view plaincopy to clipboardprint?

1. Usage: extract_sql.pl -t <table name> -r [options]
2.
3. Required:
4. -t <table name> table name to extract from the file
5.
6. Optional:
7. -r mysqldump file that you want to parse. Uses STDIN if
8. nothing is specified
9. --listTables If set, then a list of tables existing in
10. your restore file is returned,
11. and no other actions are taken
12. --noExtras If set, then extra cmds at top of mysqldump file
13. will not be included (such as disabling foreign key checks).
14. Usually you will want these things changed before restoring a
15. table, so the default is for these to be included.
16. -v verbosity - use multiple times for greater effect
17. -h Display this help message

Usage: extract_sql.pl -t <table name> -r [options]

Required:
-t <table name> table name to extract from the file

Optional:
-r mysqldump file that you want to parse. Uses STDIN if
nothing is specified
--listTables If set, then a list of tables existing in
your restore file is returned,
and no other actions are taken
--noExtras If set, then extra cmds at top of mysqldump file
will not be included (such as disabling foreign key checks).
Usually you will want these things changed before restoring a
table, so the default is for these to be included.
-v verbosity - use multiple times for greater effect
-h Display this help message

So, to extract the info needed to restore table ‘mytable’ from the mysqldump file ‘mydumpfile’, you’d run:

extract_sql.pl -t mytable -r mydumpfile > mytable.sql

or, if your dump file is gzipped, you could save a little time and space by doing:
cat mydumpfile.gz | gunzip | extract_sql.pl -t mytable > mytable.sql

To see what table names are within your mysqldump file, run:

extract_sql.pl –listTables -r mydumpfile

The script has a lot of extra functions, etc. in it for logging and cmd-line parsing, but the meat of what it does is here (NOTE! This is not the entire script, just an excerpt of it, use the download link near the beginning of this file to obtain the entire script to use it yourself):


if ($conf{'restoreFile'}) {
## open the mysqldump file
open(STDIN, "< $conf{'restoreFile'}") || quit("ERROR => Couldn't open file $conf{'restoreFile'}: $!", 3);
}

my $flag = 0;

## go through the file one line at a time
while (my $line = ) {

if ($conf{'listTables'}) {
if ($line =~ /^-- Table structure for table `(.*)`/) {
print $1 . "\n";
}
}
else {

## if we're not ignoring extra lines, and we haven't set the flag, and if it's not a 40000 code, then print
if (!$conf{'noExtras'} &amp;&amp; !$flag) {
if ($line =~ /^\/\*!(.....).*\*\//) { print $line unless ($1 == 40000); }
}

## set a flag when we encounter the table we want
if ($line =~ /^-- Table structure for table `$conf{'tableName'}`/) {
$flag = 1;
printmsg("Turning flag on", 1);
}
## turn flag off as soon as we encounter next table definition
elsif ($line =~ /^-- Table structure for table/) {
$flag = 0;
printmsg("Turning flag off", 1);
}

## if flag is set, then print to STDOUT, otherwise just move on
if ($flag) {
print $line;
}
}
}





Method 2: awk

First, you have to know where in your mysqldump output you want to begin your extraction, and where you want to end it. The key here is finding something unique at the beginning and ending of the block that won’t be found anywhere else.

A sample mysqldump contains something like the following:
view plaincopy to clipboardprint?

1. --
2. -- Table structure for table `test1`
3. --
4. ...
5. DROP TABLE IF EXISTS `test1`;
6. CREATE TABLE `test1` ( ...
7. LOCK TABLES `test1` WRITE;
8. INSERT INTO `test1` VALUES (1,0,’2 ...
9. UNLOCK TABLES;
10. ...
11. –-
12. –- Table structure for table `test2`
13. –-

--
-- Table structure for table `test1`
--
...
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` ( ...
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,’2 ...
UNLOCK TABLES;
...
–-
–- Table structure for table `test2`
–-

As you can see, we have a line with the comment “Table structure for table `test1`”, then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.

To extract the dump for a single table from an entire database dump, run the following from a command prompt:
$ awk ‘/Table structure for table .test1./,/Table structure for table .test2./{print}’ mydumpfile.sql > /tmp/extracted_table.sql

The above command searches through the dump file, and as soon as it matches a line containing the first search string (denoted by the first set of slashes), it prints that line and every subsequent line until it encounters a line containing the second search string (denoted by the second set of slashes). FYI, the periods surrounding the table names above are wildcard characters.

Now the extracted_table.sql file contains the SQL to restore your table. One final thing: There are usually various parameters at the top of your mysqldump file that you may need to set before restoring your table, depending on the complexity of your database (i.e. disabling foreign key checks.)

To restore your table, you’d run:
$ mysql -u user -ppassword mydb < /tmp/extracted_table.sql Voila! – you’re back in business.

Method 3: Restore elsewhere and extract
Another option is to restore your data into a temporary database (assuming you have the disk space), extract the table you’re interested in to it’s own dump file, and then restore that dump file to your original database. Create a new database, name it something easy to distinguish from your production one, i.e. fakedb. Restore your data to the fakedb with a command like this: $ mysql -u user -ppassword fakedb <> select * from targettable into outfile “/tmp/mytablebackup.bak”;

On the production db, where you have your undesired data, clear it all out with:
mysql> delete from baddatatable;

Import the good stuff back into it:
mysql> load data infile “/tmp/mytablebackup.bak” into table baddatatable;

Now you can rinse and repeat if you want to keep trying whatever your initial operation was until you perfect it. Make your changes, delete everything, load it back in, redeux.

Hope this is useful.

REFERENCE
http://blog.tsheets.com/tag/geek-talk#

MySQL Server Tuning #84 Restoring a Single Table from a Large MySQL

SkyHi @ Thursday, February 18, 2010



REFERENCE
http://books.google.ca/books?id=Kil9wrKzyUMC&pg=PA175&lpg=PA175&dq=Restore+a+single+table+from+a+large+MySQL+backup&source=bl&ots=nnHYT4k3Nl&sig=Wn7rMEiV_SgCpOx555bWf-FACzY&hl=en&ei=oo59S-fUEYHiswORlb28Cw&sa=X&oi=book_result&ct=result&resnum=3&ved=0CAsQ6AEwAjgU#v=onepage&q=Restore%20a%20single%20table%20from%20a%20large%20MySQL%20backup&f=false

MySQL - Restoring a single database from nightly backup (mysqldump generated file)

SkyHi @ Thursday, February 18, 2010
Very often we get the requests from customer to restore a "tiny" table from the nightly backup into the development/staging environment. This request need to be done on urgent basis as its just a matter of a one table and customer don't want to restore entire database of 10GB so the restoration might take less than 5 min - this is how most of the customer/managers thinks and they are abs right as why should it take more time for a single table?. This can be addressed by playing with the privileges and with the help of parameters available to 'mysql' command line utility

Steps to restore single table from dump file

# Create a user & GRANT him all rights on the table which need to be restored
# Start importing as show below

mysql -uRestUser -pSecret --force --one-database DB_NAME < /path/to/dumpfile.sql

ResetUser - "user" created for restoring single table

--one-database - Ignore statements except those for the default database named on the command line

--force - Parameter would force the import to continue even if an SQL error occurs( mainly due to the privileges as the new user don't have the privilege on other tables)

REFERENCE
http://ushastry.blogspot.com/2009/08/mysql-restoring-single-table-from.html


Recover MySQL root Password

SkyHi @ Thursday, February 18, 2010
You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new mysql root account password i.e. reset mysql password.
Step # 5: Exit and restart the MySQL server.
Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p


REFERENCE
http://www.cyberciti.biz/tips/recover-mysql-root-password.html



MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases

SkyHi @ Thursday, February 18, 2010

Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:

#mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

mysqlcheck is available in MySQL 3.23.38 and later.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. The exact operations are determined by the underlying storage engine used.

You must execute it on running database. It is recommended that you kill other incoming queries (like from your website) before running this which makes it a lot faster. I simply stop my httpd server before running it. You may want to use a Site Unavailable message instead so that the MySQL server is left alone.


REFERENCE

http://blog.taragana.com/index.php/archive/mysql-tip-how-to-check-repair-optimize-all-tables-in-all-databases/



Restore a single table from a large MySQL backup

SkyHi @ Thursday, February 18, 2010
Say, for some reason, you need to restore the entire contents of a single table from a HUGE mysqldump generated backup containing several tables. For example:

create table `baz`;

GIGS OF SQL YOU DON'T WANT;

create table `foo`;

A COUPLE THOUSAND LINES YOU DO WANT;

create table `bar`;

MORE SQL YOU DON
'T WANT;


With a little dash 'o ruby, you can extract just the part you want:

$ ruby -ne '@found=true if $_ =~ /^CREATE TABLE `foo`/i; next unless @found; exit if $_ =~ /^CREATE TABLE (?!`foo`)/i; puts $_;' giant_sql_dump.sql > foo.sql
$ cat foo.sql
create table `foo`;

A COUPLE THOUSAND LINES YOU DO WANT;

You can then easily restore that entire table:

$ mysql mydatabase -e 'drop table foo'
$ mysql mydatabase foo.sql


REFERENCES
http://snippets.dzone.com/posts/show/4819

How to Back Up and Restore a MySQL Database

SkyHi @ Thursday, February 18, 2010

If you're storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. This tutorial will show you two easy ways to backup and restore the data in your MySQL database. You can also use this process to move your data to a new web server.

Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
  • [uname] Your database username
  • [pass] The password for your database (note there is no space between -p and the password)
  • [dbname] The name of your database
  • [backupfile.sql] The filename for your database backup
  • [--opt] The mysqldump option

For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Tutorials > tut_backup.sql

This command will backup the 'Tutorials' database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

  • Create an appropriately named database on the target machine
  • Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Backing Up and Restoring using PHPMyAdmin

It is assumed that you have phpMyAdmin installed since a lot of web service providers use it. To backup your MySQL database using PHPMyAdmin just follow a couple of steps:

  • Open phpMyAdmin.
  • Select your database by clicking the database name in the list on the left of the screen.
  • Click the Export link. This should bring up a new screen that says View dump of database (or something similar).
  • In the Export area, click the Select All link to choose all of the tables in your database.
  • In the SQL options area, click the right options.
  • Click on the Save as file option and the corresponding compression option and then click the 'Go' button. A dialog box should appear prompting you to save the file locally.

Restoring your database is easy as well as backing it up. Make the following:

  • Open phpMyAdmin.
  • Create an appropriately named database and select it by clicking the database name in the list on the left of the screen. If you would like to rewrite the backup over an existing database then click on the database name, select all the check boxes next to the table names and select Drop to delete all existing tables in the database.
  • Click the SQL link. This should bring up a new screen where you can either type in SQL commands, or upload your SQL file.
  • Use the browse button to find the database file.
  • Click Go button. This will upload the backup, execute the SQL commands and re-create your database.

REFERENCE
http://www.webcheatsheet.com/SQL/mysql_backup_restore.php

Backup and restore a single MySQL table

SkyHi @ Thursday, February 18, 2010
http://www.sysadmin.md/wp-content/uploads/mysql-backup-restore-table.png

Dump a single table to a SQL file:

<code>mysqldump -uuser -ppassword dbName tableName > backup.sql</code>


If you want to get or restore a single table from a large MySQL dump file you can use the following methods:
Using AWK
Using Ruby
Using Perl:


</p><pre><code>#!/usr/bin/perl -wn<br />BEGIN {<br />  $table = shift @ARGV;<br />  $printing = 0;<br />}<br />$printing = 1 if /^create table $table\b/io;<br />exit if $printing && /^create table (?!$table)\b/io;<br /></code><code>print if $printing;</code><br /><br /><code>

Invocation:

perl gettablefromdump.pl tablename dumpfile.sql


REFERENCE




How To Repair Corrupted MySQL Tables Using myisamchk

SkyHi @ Thursday, February 18, 2010
MyISAM is the default storage engine for MySQL database. MyISAM table gets corrupted very easily. In this article, I’ll explain how to use myisamchk to identify and fix table corruption in MyISAM. When a table is created under MySQL, it creates three different files: *.frm file to store table format, *.MYD (MyData) file to store the data, and *.MYI (MyIndex) to store the index. I prefer to use InnoDB as the storage engine for bigger database, as it resembles Oracle and provides commit, rollback options.


I got the following error from a production bugzilla application that is using MySQL database. From the error message, it is clear that attach_data table is corrupted and needs to be reparied. The corrupted table can be repaired using myisamchk as explained below.
undef error - DBD::mysql::db selectrow_array failed: Table 'attach_data' is
marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata)
FROM attach_data WHERE id = ?"] at Bugzilla/Attachment.pm line 344
Bugzilla::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') called

1. Identify all corrupted tables using myisamchk

# myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt

myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 18361936
MyISAM-table 'attach_data.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'groups.MYI' is usable but should be fixed
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'profiles.MYI' is usable but should be fixed
When you redirect the output of myisamchk to a temporary file, it will display only the corrupted table names on the screen. The /tmp/myisamchk_log.txt file will contain information about all the tables including the good ones, as shown below:
Checking MyISAM file: user_group_map.MYI
Data records:     182   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1

2. Repair the corrupted table using myisamchk

Execute the myisamchk as shown below, with -r option to repair the corrupted tables identified in the above step.
# myisamchk -r profiles.MYI

- recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
- Fixing index 1
- Fixing index 2
You may get error message: clients are using or haven’t closed the table properly, if the tables are still getting used by your application and other tables. To avoid this error message, shutdown mysqld before performing the repair, if you can afford to shutdown the DB for a while. If not, use FLUSH TABLES to force mysqld to flush any table modification that are still in memory.

3. Perform check and repair together for entire MySQL database

# myisamchk --silent --force --fast --update-state /var/lib/mysql/bugs/*.MYI

myisamchk: MyISAM file /var/lib/mysql/bugs/groups.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/bugs/profiles.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
  • -s, --silent option: Prints only errors. You can use two -s to make myisamchk very silent.
  • -f, --force option: Restart myisamchk automatically with repair option -r, if there are any errors in the table.
  • -F, --fast option: Check only tables that haven’t been closed properly.
  • -U --update-state option: Marks tables as crashed, when it finds any error.

4. Allocate additional memory for large MySQL database

For large database, it may take several hours to recover the tables. Depending on RAM available on your system, increase the memory parameters as shown below while executing the myisamchk:
# myisamchk --silent --force --fast --update-state \
--key_buffer_size=512M --sort_buffer_size=512M \
--read_buffer_size=4M --write_buffer_size=4M \
/var/lib/mysql/bugs/*.MYI

5. Use myisamchk to get information about a table

You can also use myisamchk to get detailed information about a table, as shown below.
# myisamchk -dvv profiles.MYI

MyISAM file:         profiles.MYI
Record format:       Packed
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2007-08-16 18:46:59
Status:              open,changed,analyzed,optimized keys,sorted index pages
Auto increment key:              1  Last value:                    88
Data records:                   88  Deleted blocks:                 0
Datafile parts:                118  Deleted data:                   0
Datafile pointer (bytes):        4  Keyfile pointer (bytes):        4
Datafile length:              6292  Keyfile length:              6144
Max datafile length:    4294967294  Max keyfile length: 4398046510079
Recordlength:                 2124

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     3   unique  int24                          1         1024       1024
2   5     765 unique  char packed stripped           1         2048       4096

Field Start Length Nullpos Nullbit Type
1     1     1
2     2     3                      no zeros
3     5     765                    no endspace

6. All myisamchk options

Execute the following command to understand all the available option for myisamchk.
# myisamchk --help
Following are some of the key options that you can use along with myisamchk.
Global options:
  • -s, --silent Only print errors. One can use two -s to make myisamchk very silent.
  • -v, --verbose Print more information. This can be used with --description and --check. Use many -v for more verbosity.
  • -V, --version Print version and exit.
  • -w, --wait Wait if table is locked.
Check options (check is the default action for myisamchk):
  • -c, --check Check table for errors.
  • -e, --extend-check Check the table VERY throughly. Only use this in extreme cases as myisamchk should normally be able to find out if the table is ok even without this switch.
  • -F, --fast Check only tables that haven’t been closed properly.
  • -C, --check-only-changed Check only tables that have changed since last check.
  • -f, --force Restart with ‘-r’ if there are any errors in the table. States will be updated as with ‘--update-state’.
  • -i, --information Print statistics information about table that is checked.
  • -m, --medium-check Faster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases.
  • -U --update-state Mark tables as crashed if you find any errors.
  • -T, --read-only Don’t mark table as checked.
Repair options (When using ‘-r’ or ‘-o’):
  • -B, --backup Make a backup of the .MYD file as ‘filename-time.BAK’.
  • --correct-checksum Correct checksum information for table.
  • -e, --extend-check Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don’t use this option if you are not totally desperate.
  • -f, --force Overwrite old temporary files.
  • -r, --recover Can fix almost anything except unique keys that aren’t unique.
  • -n, --sort-recover Forces recovering with sorting even if the temporary file would be very big.
  • -p, --parallel-recover Uses the same technique as ‘-r’ and ‘-n’, but creates all the keys in parallel, in different threads.
  • -o, --safe-recover Uses old recovery method; Slower than ‘-r’ but can handle a couple of cases where ‘-r’ reports that it can’t fix the data file.
  • -q, --quick Faster repair by not modifying the data file. One can give a second ‘-q’ to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is currupted can’t be fixed with this option.
  • -u, --unpack Unpack file packed with myisampack.
Other actions:
  • -a, --analyze Analyze distribution of keys. Will make some joins in MySQL faster. You can check the calculated distribution by using ‘--description --verbose table_name’.
  • -d, --description Prints some information about table.
REFERENCE
http://www.thegeekstuff.com/2008/09/how-to-repair-corrupted-mysql-tables-using-myisamchk/

How to Fix MySQL Database (MyISAM / InnoDB)

SkyHi @ Thursday, February 18, 2010
So... your shiny MySQL database is no longer running and you want to fix it?

You've come to the right place!

I've assembled a list of 7 ways to fix your MySQL database when a simple restart doesn't do the trick, or when you have corrupt tables.

Simple MySQL restart:

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
/usr/local/mysql/bin/mysqld_safe &

1. Corrupt MyISAM tables

MySQL database allows you to define a different MySQL storage engine for different tables. The storage engine is the engine used to store and retrieve data. Most popular storage engines are MyISAM and InnoDB.

MyISAM tables -will- get corrupted eventually. This is a fact of life.

Luckily, in most cases, MyISAM table corruption is easy to fix.

To fix a single table, connect to your MySQL database and issue a:

repair TABLENAME

To fix everything, go with:

/usr/local/mysql/bin/mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r

A lot of times, MyISAM tables will get corrupt and you won't even know about it unless you review the log files.

I highly suggest you add this line to your /etc/my.cnf config file. It will automatically fix MyISAM tables as soon as they become corrupt:

[mysqld]
myisam-recover=backup,force

If this doesn't help, there are a few additional tricks you can try.

2. Multiple instances of MySQL

This is pretty common. You restart MySQL and the process immediately dies.

Reviewing the log files will tell you another instance of MySQL may be running.

To stop all instances of MySQL:

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
killall mysql
killall mysqld

Now you can restart the database and you will have a single running instance

3. Changed InnoDB log settings

Once you have a running InnoDB MySQL database, you should never ever change these lines in your /etc/my.cnf file:

datadir
= /usr/local/mysql/data
innodb_data_home_dir
= /usr/local/mysql/data
innodb_data_file_path
= ibdata1:10M:autoextend
innodb_log_group_home_dir
= /usr/local/mysql/data
innodb_log_files_in_group
= 2
innodb_log_file_size
= 5242880

InnoDB log file size cannot be changed once it has been established. If you change it, the database will refuse to start.

4. Disappearing MySQL host tables

I've seen this happen a few times. Probably some kind of freakish MyISAM bug.

Easily fixed with:

/usr/local/bin/mysql_install_db

5. MyISAM bad auto_increment

If the auto_increment count goes haywire on a MyISAM table, you will no longer be able to INSERT new records into that table.

You can typically tell the auto_increment counter is malfunctioning, by seeing an auto_increment of -1 assigned to the last inserted record.

To fix - find the last valid auto_increment id by issuing something like:

SELECT max
(id) from tablename

And then update the auto_increment counter for that table

ALTER TABLE tablename AUTO_INCREMENT
= id+1

6. Too many connections

Your database is getting hit with more connections than it can handle and now you cannot even connect to the database yourself.

First, stop the database:

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown

If that doesn't help you can try "killall mysql" and "killall mysqld"

Once the database stopped, edit your /etc/my.cnf file and increase the number of connections. Don't go crazy with this number or you'll bring your entire machine down.

On a dedicated database machine we typically use:

max_connections
= 200
wait_timeout
= 100

Try restarting the database and see if that helps.

If you're getting bombarded with queries and you need to be able to connect to the database to make some table changes, set a different port number in your /etc/my.cnf file, start the database, make any changes, then update the port back to normal (master-port = 3306) and restart.

7. Corrupt InnoDB tables

InnoDB tables are my favorite. Transactional, reliable and unlike MyISAM, InnoDB supports concurrent writes into the same table.

InnoDB's internal recovery mechanism is pretty good. If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. In most cases it will succeed and the entire process is transparent.

Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline. You can try to restart the database again and again, but if the repair process fails - the database will refuse to start.

This is one reason why you should always run a master/master setup when using InnoDB - have a redundant master if one fails to start.

Before you go any further, review MySQL log file and confirm the database is not starting due to InnoDB corruption.

There are tricks to update InnoDB's internal log counter so that it skips the queries causing the crash, but in our experience this is not a good idea. You lose data consistency and will often break replication.

Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:

Step 1: Add this line to your /etc/my.cnf configuration file:

[mysqld]
innodb_force_recovery = 4

Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.

Step 3: Dump all tables

Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables

Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)

Step 6: Restore everything from your backup
Mike Peters, 10-03-2008



Recently I was faced with the daunting task of reparing an InnoDB database gone bad. The database would not start due to corruption.

First step was turning-on InnoDB force-recovery mode, where InnoDB starts but ignores all UPDATEs and INSERTs.

Add this line to /etc/my.cnf:

innodb_force_recovery
= 2

Now we can restart the database:

/usr/local/bin/mysqld_safe &

(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = 8)

Save all data into a temporary alldb.sql (this next command can take a while to finish):

mysqldump
--force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql

Shutdown the database again:

mysqladmin
-uUSERNAME -pPASSWORD shutdown

Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)

rm
-fdr /usr/local/var

Recreate the database directory and install MySQL basic tables

mkdir
/usr/local/var
chown -R mysql:mysql /usr/local/var
/
usr/local/bin/mysql_install_db
chown
-R mysql:mysql /usr/local/var

Remove innodb_force_recovery from /etc/my.cnf and restart database:

/usr/local/bin/mysqld_safe &

Import all the data back (this next command can take a while to finish):

mysql
-uroot --compress < /usr/alldb.sql

And finally - flush MySQL privileges (because we're also updating the MySQL table)

/usr/local/bin/mysqladmin -uroot flush-privileges

-

Note: For best results, add port=8819 (or any other random number) to /etc/my.cnf before restarting MySQL and then add --port=8819 to the mysqldump command. This way you avoid the MySQL database getting hit with queries while the repair is in progress.


REFERENCE
http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html