Modify MySQL variables without restarting the server

Reminder note on changing MySQL variables live and without restarting the service.

mysql> show global variables where variable_name like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | ON     |
| storage_engine            | MyISAM |
+---------------------------+--------+
2 rows in set (0.00 sec)

mysql> SET GLOBAL storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables where variable_name like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | ON     |
| storage_engine            | InnoDB |
+---------------------------+--------+
2 rows in set (0.00 sec)

Very simple, but I can never remember this and it tends to be buried somewhere in MySQL's massive documentation.

Granular permissions through sudoers

A quick example on how to provide root permissions on specific commands to a specific group of users.

You can create command aliases, which can be very useful when formatting and controlling access to these.

For example:

Cmnd_Alias vi    = /usr/bin/vim

This will match both /usr/bin/vim or just plain vim.

Assigning ROOT permissions to run this command alias to a specific user:

username ALL=(root) vi

And the same for a group:

%groupname ALL=(root) vi

In my example below, I provide access to use all the NGINX service commands on a redhat 6 system, to a new group called nginxadm.

Open up the sudoers file using visudo.

## NGINX USERS - should be part of nginxadm group
# Usage: nginx {start|stop|restart|condrestart|try-restart|force-reload|upgrade|reload|status|help|configtest}
Cmnd_Alias NG           = /sbin/service nginx
Cmnd_Alias NGRES        = /sbin/service nginx restart
Cmnd_Alias NGSTA        = /sbin/service nginx start
Cmnd_Alias NGSTO        = /sbin/service nginx stop
Cmnd_Alias NGSTS        = /sbin/service nginx status
Cmnd_Alias NGCDR        = /sbin/service nginx condrestart
Cmnd_Alias NGTRS        = /sbin/service nginx try-restart
Cmnd_Alias NGFRL        = /sbin/service nginx force-reload
Cmnd_Alias NGUPG        = /sbin/service nginx upgrade
Cmnd_Alias NGRLD        = /sbin/service nginx reload
Cmnd_Alias NGHLP        = /sbin/service nginx help
Cmnd_Alias NGCFG        = /sbin/service nginx configtest
%nginxadm ALL=(root)    NG,NGRES,NGSTA,NGSTO,NGSTS,NGCDR,NGTRS,NGFRL,NGUPG,NGRLD,NGHLP,NGCFG

Thanks to FACLs in Linux, we can also give granular permissions to the NGINX configuration files.

IBM DB2 connector - executable stack error

I ran into this issue following the installation of a new IBM DB2 connector library for use with PHP on an Apache server.

PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/ibm_db2.so' - libdb2.so.1: cannot enable executable stack as shared object requires: Permission denied in Unknown on line 0

As it turns out, Dan Walsh already wrote up something about this on his blog in 2011:

http://danwalsh.livejournal.com/38736.html

Following his instructions, I've opted to clear the executable stack flag.  I've modified his one liner command to execute the 'clear' command on all the files found.  It would be tedious to change them one by one.

find /opt/ibm/db2/V9.7/lib64 -exec execstack -q {} \; -print 2> /dev/null | grep ^X | cut --delimiter=' ' -f2 | xargs -n 1 execstack -c

This fixed the problem.

List users in linux

Here is a quick tip on how to more or less reliably list all non-system users and print out status information about their accounts, in linux.

grep -i ':/home/' /etc/passwd | cut -d: -f1 | xargs -n 1 passwd -S

Step 1:
We use grep to look for entries in the /etc/passwd file where the /home/ directory is specified.  System users don't normally have such a directory.

Step 2:
We then use cut to retain only the username, and we specify : (colon) as a delimiter.

Step 3:
We run the usernames through xargs to pass each username as a single argument to the passwd -S command.

passwd -S prints out short status of the username.  Whether it's locked, expired, etc...

Using GDB to explore a core file

Before we go any further, please note that these are instructions for a Centos 6.3 OS.  They should work in a similar way on Redhat and Fedora.

Recently, I've been experiencing issues with pacemaker immediately after ugprading from one version to another.  To make a long story short, crmd, which is part of the pacemaker package, was crashing every 15 minutes and dumping a core file.  It became necessary to read the details of the core dump using gdb.  To be fair though, the service itself stayed up fine but kept respawning child processes.  So, even though it complains and dumps a core file, it is still robust enough to continue operating.

Now the issue becomes, how to get the error content out of the core file?  We first need to make sure the right repositories are available for our OS:

# DEBUGINFO
[debuginfo]
name=debuginfo
baseurl=http://debuginfo.centos.org/$releasever/$basearch
enabled=0
gpgcheck=0

You will need a tool called 'debuginfo-install' which is part of a yum utility package.

# yum install yum-utils

Install gdb

# yum install gdb

Run gdb against your executable and it's core file.  For example:

# gdb /usr/libexec/pacemaker/crmd ./core.26688

If you are missing any debuginfo file, gdb will let you know which ones.  You can then install them as required.  For example:

Missing separate debuginfos, use: debuginfo-install audit-libs-2.2-2.el6.x86_64

Simply run the suggested command to get the right debuginfo files.

# debuginfo-install audit-libs-2.2-2.el6.x86_64 --enablerepo=debuginfo

Once you've got all your debug info files, run the gdb debugger once again.

# gdb /usr/libexec/pacemaker/crmd ./core.26688
...
Core was generated by `/usr/libexec/pacemaker/crmd'.
Program terminated with signal 6, Aborted.
#0  0x00007f81896ac8a5 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
64        return INLINE_SYSCALL (tgkill, 3, pid, selftid, sig);
Missing separate debuginfos, use: debuginfo-install libtool-ltdl-2.2.6-15.5.el6.x86_64
(gdb)

At this point you are in the gdb prompt.  Use "bt" to output the backtrace of the error.

(gdb) bt
#0  0x00007f81896ac8a5 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1  0x00007f81896ae085 in abort () at abort.c:92
#2  0x00007f818bb8a56b in crm_abort (file=0x7f818bba9d58 "xml.c", function=0x7f818bbab6b4 "string2xml", line=650,
    assert_condition=0x7f818bbaa01a "String parsing error", do_core=, do_fork=) at utils.c:1073
#3  0x00007f818bb933af in string2xml (
    input=0x1e745f8 "#4  0x00007f818b76a2fc in lrmd_ipc_dispatch (buffer=, length=, userdata=0x1e72910) at lrmd_client.c:310
#5  0x00007f818bba2e90 in mainloop_gio_callback (gio=, condition=G_IO_IN, data=0x1e73be0) at mainloop.c:585
#6  0x00007f8188fbbf0e in g_main_dispatch (context=0x1d4f120) at gmain.c:1960
#7  IA__g_main_context_dispatch (context=0x1d4f120) at gmain.c:2513
#8  0x00007f8188fbf938 in g_main_context_iterate (context=0x1d4f120, block=1, dispatch=1, self=) at gmain.c:2591
#9  0x00007f8188fbfd55 in IA__g_main_loop_run (loop=0x1e734a0) at gmain.c:2799
#10 0x00000000004052ce in crmd_init () at main.c:154
#11 0x00000000004055cc in main (argc=1, argv=0x7fffe77a4f88) at main.c:120
(gdb)

This is the information we were looking for.

Thanks to Andrew Beekhof of  http://clusterlabs.org/ for pointing me in the right direction with GDB.

Log file system changes using Audit

Adding an audit rule to log file-system activity:

# auditctl -w /home/something/ -p rwa

Flags:

-w Insert watch
-p Set the permission filter

If you search the audit log, you will only get results if there has been activity:

# ausearch -f /home/something/
<no matches>

Now, if we create a new file and search again:

# touch /home/something/testing1

# ausearch -f /home/something/
----
time->Tue Apr  9 08:53:08 2013
type=PATH msg=audit(1365511988.313:969510): item=1 name="/home/something/testing1" inode=54411 dev=08:15 mode=0100644 ouid=0 ogid=0 rdev=00:00 obj=user_u:object_r:user_home_t:s0
type=PATH msg=audit(1365511988.313:969510): item=0 name="/home/something/" inode=54374 dev=08:15 mode=040700 ouid=1041 ogid=1041 rdev=00:00 obj=user_u:object_r:user_home_dir_t:s0
type=CWD msg=audit(1365511988.313:969510):  cwd="/root"
type=SYSCALL msg=audit(1365511988.313:969510): arch=c000003e syscall=2 success=yes exit=0 a0=7fff217f4cb6 a1=941 a2=1b6 a3=32cc35410c items=2 ppid=10799 pid=26986 auid=1041 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=130958 comm="touch" exe="/bin/touch" subj=user_u:system_r:unconfined_t:s0 key=(null)

Let's delete the file and see what happens to the logs.

# rm /home/something/testing1

Run the search again:

# ausearch -f /home/something/

Notice the syscall key which is displayed in each entry.  What does the code mean?  Let's make things more 'readable' by setting the -i flag.

# ausearch -f /home/something/ -i
----
type=PATH msg=audit(04/09/2013 09:00:52.093:969611) : item=1 name=/home/something/testing1 inode=54411 dev=08:15 mode=file,644 ouid=root ogid=root rdev=00:00 obj=user_u:object_r:user_home_t:s0
type=PATH msg=audit(04/09/2013 09:00:52.093:969611) : item=0 name=/home/something/ inode=54374 dev=08:15 mode=dir,700 ouid=something ogid=something rdev=00:00 obj=user_u:object_r:user_home_dir_t:s0
type=CWD msg=audit(04/09/2013 09:00:52.093:969611) :  cwd=/root
type=SYSCALL msg=audit(04/09/2013 09:00:52.093:969611) : arch=x86_64 syscall=unlink success=yes exit=0 a0=7fff2c3b2cbc a1=1 a2=2 a3=168f7610 items=2 ppid=10799 pid=27302 auid=something uid=root gid=root euid=root suid=root fsuid=root egid=root sgid=root fsgid=root tty=pts0 ses=130958 comm=rm exe=/bin/rm subj=user_u:system_r:unconfined_t:s0 key=(null) 

The timestamps are now all converted to human-readable formats.  The UIDs and GIDs are converted to the name of the user or group and finally, the system call codes show something intelligible.  For example, syscall=87 now reads syscall=unlink which which we can interpret as 'delete'.

You can search by system call codes as well.  Instead of displaying all activity on all files and reading through each entry one by one, you can search for 'unlink' system calls.

The flag is -sc <syscall>

For example, the following command will return the log(s) entry(ies) showing an 'unlink' call.

# ausearch -f /home/something/testing1 -i -sc unlink

To remove a watch, use the -W flag.  Note, when using this flag, the remove (-W) command must match the rule.  If you don't know the exact rule, you can list them:

# auditctl -l

LIST_RULES: exit,always dir=/home/something (0xe) perm=rwa

Now we can delete it using:

# auditctl -W /home/something -p rwa

Extending root partition on the fly - Part 2

In my previous post I discuss some techniques to expand disks while ensuring zero downtime.

These techniques are not always viable as it will depend on the Operating System version you have, as well as LVM version.  If you don't use LVM and instead use native linux partitions, then things can get a bit uglier and you will probably need one or two reboots.  In CentOS 5.2 I can't properly re-scan the ISCSI devices.  It seems support for it may have only been added in version 5.4, as RedHat began adding shell scripts to perform this operation.

In any case, for the latest version of RedHat and Centos (5.5,6,6.2,6.3) the previously described techniques work just fine.  There is only one caveat.  In order to prevent having to disable the volume group, unmounting the filesystem and stopping services that are using them, one must create a new "Physical Volume" using pvcreate, as I've done.  The only problem with this, and it's not a big one, is that you end up with separated physical volumes all on the same LVM partition.

If you want to expand the disk but have only one physical volume in the LVM, it will be necessary to disable the volume group in order to use pvresize.  Note that this implies shutting down services and unmounting the filesystem to be expanded.

Example:

1) Extend disk in vmware

2) Rescan the disk
# echo "1" > /sys/class/scsi_device/<device number>/device/rescan

3) resize the partition in question using fdisk <device>
# fdisk /dev/sdb

4) re-read the partition table:
# partprobe

5) If your service is apache:
# service httpd stop

6) Disable the volume group
# vgchange -a n <volume group name>

7) Physical Volume Resize
# pvresize /dev/sdb1

7.1) If you check your physical volume now you should see the free extents:
# pvdisplay

8) Re-enable the volume group
# vgchange -a y <vg name>

9) Re-mount the device
# mount /var/www (for example) or mount -a

10) Restart your service
# service httpd start

11) Now note that we've only expanded the Volume Group and neither has the Logical Volume or the File System been extended, but we can do these on the fly so it's o.k. if they are mounted.
# lvextend -l +<num of free extents> /dev/<vg name>/<lv name>

13) Resize the filesystem
# resize2fs /dev/<vg name>/<lv name>

In our case we find that it's helpful to test these procedures on clones and ensuring we have the most appropriate technique for the situation.

Extending root partition on the fly - linux on vmware

You've extended your VM's only disk by a bunch of Gigabytes.  You have Apache / MySQL running on it and you can't afford any downtime.  You now need the Operating System to recognize all that new space.  What can you do?  Expanding a root partition at runtime with a guest linux OS, requires a bit of planning but is still fairly straightforward.

The following was performed on a CentOS 5.5 Guest VM running on VMWare.

Let's see how much space we have right now:

[root@... ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       18G   11G  6.5G  61% /
/dev/sda1              99M   13M   82M  14% /boot
tmpfs                 2.0G     0  2.0G   0% /dev/shm

First, make sure the OS can recognize that the hardware actually changed.  Rescan your SCSI device:

# echo "1" > /sys/class/scsi_device/<device number>/device/rescan

Next comes the fun part.  This is where planning for disaster comes in handy; so even though you don't want to take any downtime, plan for it:  Take a snapshot of your VM.

Format your device and add a new partition.  In my case I have a /boot partition and a / partition so my new partition will have number 3 or /dev/sda3.

# fdisk /dev/sda

-- if we now try to create a new physical volume, it should fail until we run the partprobe command.

# partprobe

UPDATE: Since RedHat 6 (CentOS 6), you can use the partx command to force the changes to take effect on the partition table.  Note that partx does not do the same validation as partprobe, so if you've made mistakes with your partition layout, data can be erased.  If you are certain that your layout is correct, then proceed using:

# partx -l /dev/sda


And to force changes to take effect:

# partx -v -a /dev/sda

( See RedHat's recommendation at: https://access.redhat.com/site/solutions/57542 )

-- Create a physical volume from the new partition now that the kernel knows about it.

# pvcreate /dev/sda3

-- Extend the volume group to use up all space on the new physical volume.

# vgextend VolGroup00 /dev/sda3

-- Extend the logical volume by the number of free physical extents available in the group (use +)

# lvextend -l +3200 /dev/VolGroup00/LogVol00

-- Finally run an online resize of the mounted partition without affecting anything.

# resize2fs /dev/VolGroup00/LogVol00

That's it.  Now run df -h:

[root@... ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      115G   11G   99G  10% /
/dev/sda1              99M   13M   82M  14% /boot
tmpfs                 2.0G     0  2.0G   0% /dev/shm


----------------- EXTENDING A SWAP PARTITION -----------------

If you are doing this on a SWAP partition, then make sure you follow these instructions:

After extending the VolumeGroup, disable your swap:

[root@... ~]# swapoff -a

Extend your swap logical partition ( by 320 Physical Extents in my case ):

[root@... ~]# lvextend -l +320 /dev/VolGroup00/LogVol01

Check your logical volume size:

[root@... ~]# lvdisplay /dev/VolGroup00/LogVol01
  --- Logical volume ---
  LV Name                /dev/VolGroup00/LogVol01
  VG Name                VolGroup00
  LV UUID                --- ---- ---- --- ----
  LV Write Access        read/write
  LV Status              available
  # open                 0
  LV Size                11.97 GB
  Current LE             383
  Segments               2
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:1

Use mkswap to recreate a new swap partition.  There is no need to worry about the data because when swap is disabled, it should not contain any data.

[root@... ~]# mkswap /dev/VolGroup00/LogVol01
Setting up swapspace version 1, size = 12851343 kB

Now restart your swap and check your memory:

[root@... ~]# swapon -a

[root@... ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         24106       2917      21188          0        371        484
-/+ buffers/cache:       2061      22045
Swap:        12255          0      12255