Category: Technology (Page 9 of 47)

Decreasing shutdown time of MySQL with large transaction log file

July 15, 2014 / halfgaar

If you need/have a large transaction log file, a shut down can take a while. This post lists how you can force a flush before you do so, decreasing shut down time.

In short:

mysql> set global innodb_max_dirty_pages_pct = 0;

Then monitor the dirty pages:

mysqladmin ext -i10 | grep dirty

Excellent article about LC resonance resulting from turn-on transients

April 26, 2014 / halfgaar

This article gives a good explation of LC resonances caused by turn-on transients.

Whenever electrolytic caps are a thing of the past and replaced by super low ESR devices, we may actually have to add some resistance.

Better nagios SMS and E-mail messages

April 4, 2014 / halfgaar

Just for my references. I made changes to it before, but I now added the comments to them (for custom notifications and acknowledgements):

define command{
        command_name    notify-host-by-email
        command_line    /usr/bin/printf "%b" "***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\nComment: $NOTIFICATIONCOMMENT$\nHost: $HOSTNAME$\nState: $HOSTSTATE$\nAddress: $HOSTADDRESS$\nInfo: $HOSTOUTPUT$\n\nDate/Time: $LONGDATETIME$\n" | /usr/bin/mail -s "** $NOTIFICATIONTYPE$ Host Alert ($SHORTDATETIME$): $HOSTNAME$ is $HOSTSTATE$ **" $CONTACTEMAIL$
}
 
define command{
        command_name    notify-service-by-email
        command_line    /usr/bin/printf "%b" "***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\nComment: $NOTIFICATIONCOMMENT$\nService: $SERVICEDESC$\nHost: $HOSTALIAS$\nAddress: $HOSTADDRESS$\nState: $SERVICESTATE$\n\nDate/Time: $LONGDATETIME$\n\nAdditional Info:\n\n$SERVICEOUTPUT$" | /usr/bin/mail -s "** $NOTIFICATIONTYPE$ Service Alert ($SHORTDATETIME$): $HOSTALIAS$/$SERVICEDESC$ is $SERVICESTATE$ **" $CONTACTEMAIL$
}
 
define command{
        command_name    notify-host-by-sms
        command_line    /usr/local/sbin/send-sms.sh -n $CONTACTPAGER$ -m "$HOSTNAME$: $NOTIFICATIONTYPE$, $HOSTSTATE$ ($NOTIFICATIONCOMMENT$)"
}
 
define command{
        command_name    notify-service-by-sms
        command_line    /usr/local/sbin/send-sms.sh -n $CONTACTPAGER$ -m "$SERVICEDESC$ on $HOSTNAME$ $NOTIFICATIONTYPE$ ($NOTIFICATIONCOMMENT$): $SERVICEOUTPUT$"
}

Teamviewer replacements

March 11, 2014 / halfgaar

ChunkVNC consists of three parts, InstantSupport, Repeater and Viewer.

UltraVNC SC is a mini (166k) UltraVNC Server that can be customized and preconfigured for download by a Customer. UltraVNC SC does not require installation and does not make use of the registry. The customer only have to download the little executable and Click to make a connection. The connection is initiated by the server, to allow easy access thru customers firewall.

Iptables: limiting amount of connections per IP

February 4, 2014 / halfgaar

Short and improved version of this.

To allow SSH TCP connections, but after more than 20 NEWs in 20s, drop:

iptables -A INPUT -p tcp --dport 22 -j ACCEPT
iptables -I INPUT -p tcp --dport 22 -m recent --rcheck --seconds 20 --hitcount 20 --name sshbadguys --rsource -j DROP
iptables -I INPUT -p tcp --dport 22 -m state --state NEW -m recent --set --name sshbadguys --rsource

To check current list (from the man page):

cat /proc/net/xt_recent/sshbadguys

Test with:

#!/bin/bash
ip="1.2.3.4"
port="22"
for i in {1..100}
do
  echo "attempt $i"
  # do nothing just connect and exit
  echo "exit" | nc ${ip} ${port};
done

Dumping MySQL users and grants

January 17, 2014 / halfgaar

MySQL stores its users and grants in a very annoying, non-clonable format. I found this post, describing how you can extra them.

This bash function generates grants:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

Openswan random failures

December 1, 2013 / halfgaar

I have an annoying problem with my Openswan VPN server. When I connect from a Windows XP machine, from that point on, I can only connect with that machine (or perhaps other Windows XP machines as well). On the other hand, when I connect with a Windows 7 machine first, connecting from Windows XP is no longer possible.

This is the error I get:

ERROR: netlink XFRM_MSG_DELPOLICY response for flow eroute_connection delete included errno 2: No such file or directory

If I restart ipsec after each session, it works. So, I put this in /etc/ppp/ip-down.d/99-ipsec-restart:

#!/bin/sh
 
/etc/init.d/ipsec restart

A super ugly hack that makes it impossible to connect twice, but it’s better than not at all…

Versions:

Openswan: 2.6.37-1
xl2tpd: 3.1+dfsg-1
Ubuntu 12.04

Turkey cooking time

November 24, 2013 / halfgaar

As a note to self for next year: We just had a 8 kg turkey for our Thanksgiving meal. Recipes on the internet said it needed 7 to 8 hours of cooking. However, we took it out after 5.5 hours, and it was even somewhat overcooked. It probably needed about 4.5-5 hours.

Cursor definition and looping with MS SQL

September 30, 2013 / halfgaar

In MSSQL, I needed to loop over a bunch of orders and execute a stored procedure, except in one case. This does that:

declare @orderid int;
DECLARE @RC int
 
declare ordercursor cursor
FOR SELECT id FROM [ORDER];
 
open ordercursor
fetch next FROM ordercursor INTO @orderid
 
while @@fetch_status = 0
begin
  IF @orderid != 429
  begin
    EXECUTE @RC = [DeleteOrderById] @orderid  
  end
  
  fetch next FROM ordercursor INTO @orderid;
end
 
close ordercursor;
deallocate ordercursor;

Upgrading PowerDNS from Debian Squeeze to Wheezy

September 24, 2013 / halfgaar

As I mentioned here, I had to manually create a database schema when I installed PowerDNS on Debian Squeeze. Later versions apparently create one themselve.

I just upgraded my Sqeeuze machine, and the package manager said that the DB needed to be migrated. But, MySQL maintenance always seems to go wrong on Debian upgrades. It started asking me the same questions as if PowerDNS wasn’t installed; it wanted to create a new user and such. I don’t know why it keeps doing that; it’s not the first time that happened. Needless to say, this process failed.

So, I looked at another PowerDNS server and I made the SQL myself:

alter table records add ordername varchar(255);
alter table records add auth tinyint(1);
update records set auth=1;
 
CREATE TABLE `cryptokeys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `flags` int(11) NOT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `cryptokeys_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE `domainmetadata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `kind` varchar(16) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `domainmetadata_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE `tsigkeys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `algorithm` varchar(255) DEFAULT NULL,
  `secret` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `namealgoindex` (`name`,`algorithm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1