Mysqldump UTC timezone workaround

Hello,

Today We needed to use mysqldump to dump lots of tables from one database to another, but seeing

select COUNT(*) from table where date_time < ‘2007-08-30 23:59:59′;

New copied table says 18 extra rows, so ’something is wrong’.

Our mysqldump was something like:

hostbackup$ mysqldump dumpoptions -w date_time < ”2007-08-30 23:59:59″ | mysql options

Reading the mysqldump manual, it tells that it uses UTC by default, so the only way to disable it is using “–skip-tz-utc” option.

Using mysqldump ‘–skip-tz-utc’ works as expected, since we do not use UTC., we use CET instead.

The complet bash script, if someone want use it :)

#!/bin/sh
# default path
PATH=/bin:/usr/bin
# tables
tables="table1 table2 table3 etc"
# mysqldump & mysql options
db="database"
rhost="remotedatabasehost"
lhost="localhost"
user="ouruser"
pass="ourpass"
dumpopts="--skip-tz-utc --insert-ignore --quote-names --no-create-info --host=${rhost} --user=${user} -p${pass}"
lsqlopts="-h${lhost} -u${user} -p${pass}"
rsqlopts="-h${rhost} -u${user} -p${pass}"
# current time
datesql="`date '+%Y-%m-%d'` 00:00:00"

for table in $tables; do
  echo "dumping $table ..."
  if mysqldump $dumpopts -w "fecha_hora < '$datesql'" $db $table | mysql $lsqlopts $db; then
    echo "table $table was dumped :)"
  else
    echo "$table not dumped!"
  fi
done

See you!

Windows/DOS batch files: a couple of hints

Sometimes even Windows needs scripting, and if the script it’s quite simple you can use the old DOS batch files, although it may be harder than you expect to get it right.

For example, when using the IF…ELSE construct, the indentention is quite picky, and you HAVE TO put the ELSE right behind the IF parethesis


IF EXIST filename (
echo "Filename exists"
) ELSE (
echo "Filename doesn't exist"
)


because otherwise it will break your script. For example, this batch file DOESN’T WORK:


IF EXIST filename (
echo "Filename exists"
)
ELSE (
echo "Filename doesn't exist"
)


and always remember to preput

@echo off

if you don’t want let Windows output your script on the terminal output.

Speed up SSH connections using a socket

Hello,

If you connect twice or more to the same server and you don’t want to reauthenticate again, you can use a socket to bypass it. You need OpenSSH 4.x or higher.

Normal situation:

1) First time

$ ssh ruben@pc-rgonzalez
ruben@pc-rgonzalez’s password:

2) Second time

ssh user@pc

user@pc password:

This is the normal behavior, so you need the put credentials twice.

Using ssh socket feature bypass it, so on the CLIENT side you need to add these lines.

(youre path here)

/etc/ssh/ssh_config

# socket speed up

Host *
ControlMaster auto
ControlPath ~/.ssh/socket-%r@%h:%p

Better situation:

1) First time

$ ssh user@pc

user@pc password:

2) Second time

$ ssh user@pc

$ (no password needed!)

It’s also create a socket in your home like this:

file /home/user/.ssh/socket-user\@pc\:22
/home/user/.ssh/socket-user@pc-user:22: socket

If you close your ssh session , socket will be deleted until you create a new one using another ssh session.

It works nice if you need to exec a command behind a firewall like:

(create a session in background, it also creates the socket)
$ ssh -N -f user@firewall

then

ssh user@firewall “ssh -t user@anothermachine”

ssh user@firewall “ssh -t user@anothermachine2″

etc

See you!

Putting mount permisions on directories

Hello,

This tip, only works with GNU/Linux systems, since –bind mount option is a Linux specific.

Is welldocumented to put specifics perms on a physical partition like /tmp, /var, /home usually noatime,noexec,nosuid etc, but, what’s wrong if you only have one simple partition as / for all the system and you need to put specifics mount perms on those directories? It is possible?

Yes, it’s possible using –bind option from mount tool.

We have all in a simple root / partition

# df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda3 66540312 46161552 16998684 74% /
udev 1037812 2616 1035196 1% /dev
none 1037812 0 1037812 0% /dev/shm

# Mounting and remouting the partition using noexec on /tmp (it is not a partition, it’s a simple dir called tmp)

mount –bind /tmp /tmp

mount -o remount,noexec /tmp

# Now, if we want to execute any executable on /tmp dir, we will have an error.

$ cd /tmp; cp /bin/ls .; ./ls

-bash: ./ls: Permission denied

$ mount | grep ‘^\/tmp’
/tmp on /tmp type none (rw,noexec,bind)

As you can see, we have ‘Permission deniend’ since the /tmp has noexec option using bind mount facility. It is useful to avoid script kiddies or stupid scripts that uses /tmp as a base because it has write-world perms.

On the other hand, it is an example, now you can use your needs or your imagination to use /home or any dir with nosuid, noexec, nodev, noatime or to use quotes! (any mount option would work).

See you!

Mount a FreeBSD NFS share under MacOSX

FreeBSD NFS server by default only accepts NFS connection arriving from ports > 1024, as a “security” measure. This prevents OSX clients to correctly mount NFS shares, because even if executed with sudo your FreeBSD server will still complain with something like:
kernel: NFS request from unprivileged port

To solve this, the easiest way is to add the -P parameter on the client side, mounting the share with

sudo mount_nfs -P server.address:/path/to/share /path/to/local/directory

WTF

WTF… today (sunday, just to let you know… ) our ISP left us again, twice in a week, with a power blackout (and we are in colocation, so wonder what happened). This is plain ridicolous and I hope we’re going to change (once again) provider. The other sysadmin writing on this blog spent 4 hours of his life just for this, and it was sunday! 

WTF… 

Active Directory authentication with Ubuntu

DISCLAIMER: This entry is almost an integral copy of this post on the Ubuntu Forums, so let’s give the credit where is due and I give a huge THANKS to tfiedler. Now, why am I doing this? The reason is simple: I’m using this blog as a scratchbook/memo/notepad so if one day I’m going to need again this howto, I will know for sure where to find it easily. Moreover I will cover more in depth a couple of points so… let’s go!

1. Install the required packages with all their dependencies:
   apt-get install krb5-user
apt-get install winbind samba
   Note: you have to enable the Universe repository in your Ubuntu configuration (edit your /etc/apt/sources.list) or you will not find krb5-user

Unexpected Softupdates Inconsistency

Hello,

Today we have a FreeBSD server with an incorrect shutdown/poweroff, when we wanted to put the server UP again, the FreeBSD server all time auto-reboots itself with an error message ‘unexpected softupdates inconsistency’.

To fix it this situation, the trick is to use fsck on all affected partitions using single user mode.

When the server boots, enter this line at prompt to enter using single mode:

> boot -s

(also, if you have a graphical menu, you can choose the 4 option)

Push enter to start a basic shell interaction using sh (bourne shell, not bash!, because /usr is not mounted)

Then, you need to checkout all partitions and fix it using fsck tool.

# fsck -y /dev/ad0s1f

or by name

# fsck -y /usr

When fixed, try to mount it using mount tool, if you don’t see any warning, you are happy, if not, repeat these steps more than one time if necessary.

# mount /usr (no warnings, cool!)

# mount /usr (and you see Operation not permited, hey I am root!, no problem, fsck it again)

If you want that FreeBSD do that for us, you only need to put the correct option in /etc/rc.conf

fsck_y_enable=”YES”

Not all time this option will work, but it helps in the next incorrect shutdow/poweroff.

See you.