MySQL Versions - A survey

Different versions

MySQL versions make interesting and at times confusing reading. Here's a list of the version soup.

• Version 5.2 was re-branded as version 6.0.
• Version 6.0 was then cancelled.
• Version 5.4 then replaced version 6.0.
• Version 5.4 was then re-branded as version 5.5.
• MySQL NDB cluster comes with it's own versioning system. As of this writing 7.2 is the latest release.
• Innodb, the most used engine in MySQL now has it's own versioning. MySQL 5.0 had Innodb version 1.0.xx and MySQL 5.5 has innodb version 1.1.xx.
• MySQL 5.6 will be the next release from MySQL(Oracle). For a full feature list see http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html

Forks and Patches

On top of this there are quite a few forks and patches.

• Drizzle was a fork of MySQL 6.0. Drizzle is probably the only true fork, has fully re-factored code and, is in active development.
• MariaDB is a release driven by Michael "Monty" Widenius, the original author of MySQL. MySQL 5.1 was the basis for MariaDB and as of this writing the latest version is Maria DB 5.5.
• Percona has several patches for both MySQL 5.1 and MySQL 5.5. Percona patches are a re-base of the main release and XtraDB is a re-base of InnoDB. Both Percona Server and XtraDB is also not a true forks of MySQL or Innodb. They have some very good performance & monitoring enhancements.
• Facebook also releases patches for MySQL.  These are done for specific Facebook requirements but as is often the case some of these patches eventually find their way into main releases.  These can be found at https://www.facebook.com/MySQLatFacebook
• OurDelta is another set of patches released by former MySQL employee Arjen Lentz.  This is now more aligned with MariaDB. These patches are similar to the Percona ones in terms of adding extra functionality to existing releases.

3rd Party Storage Engines

Outside of InnoDB and MyISAM, there are few 3rd party plugins that suit specific needs as well.

• TokuDB - A transactional engine which is available as a plugin. It provides some improvements for large databases or write heavy environments. More at http://www.tokutek.com/products/tokudb-for-mysql/
• PBXT -  A transactional engine like InnoDB and fully ACID compliant. It has not had much maintenance in the past 2 years and so is close to being defunct.
• InfiniDB - Open source engine by Calpont  is suited for data warehousing and read-intensive applications. More at http://infinidb.org/about-us/infinidb
• Infobright - Infobright engine for MySQL is another one aimed at date warehousing and large datasets. More at http://www.infobright.com/Products/MySQL-Integration/

Selecting the right one

• For most part, staying with the Oracle releases will suit most application needs. These releases are in active development, generally stable and have binary versions for most systems.
• MariaDB is binary compatible with MySQL and may suit certain needs. It also includes XtraDB from Percona. For a good comparison and the various incompatibilities, refer to http://kb.askmonty.org/en/mariadb-versus-mysql/
• Going with a fork like Drizzle will likely require some application change. Drizzle does not maintain server level compatibility but does talk the MySQL protocol. For a full list of differences refer to http://docs.drizzle.org/mysql_differences.html
• The Percona patches and versions provide a more seamless migration as they are generally fully compatible with the MySQL releases. If you are concerned about vendor support , Percona also provides that. Percona toolkit (formerly MaatKit) is a good addition as it provides some nice tools to automate a variety of tasks. This toolkit can be used with any MySQL release and so you don't need to run Percona server.
• The 3rd party engines come into a play only if are looking for specific features that come with the engines.

Links & further reading

• MySQL 5.6 features - http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html
• MariaDB Information - http://kb.askmonty.org/en/mariadb/
• OurDelta Homepage - http://ourdelta.org/
• Drizzle differences - http://docs.drizzle.org/mysql_differences.html
• Percona server - http://www.percona.com/software/percona-server/

URL fun

Some interesting ways, URLs work


http://sriramrajan.com - The conventional one

http://46.38.167.114 - The IP addresss

http://774285170 - The decimal converted

http://0x2e.0x26.0xa7.0x72 - The hexadecimal converted

http://0x2e26a772 -  Another hexadecimal variant

IPv6 Tunnel

Get a free IPv6 IP from a broker like http://tunnelbroker.net/

Configure the tunnel

modprobe ipv6
ip tunnel add he-ipv6 mode sit remote  local  ttl 255
ip link set he-ipv6 up
ip addr add  dev he-ipv6
ip route add ::/0 dev he-ipv6
ip -f inet6 addr

Check with ifconfig

#ifconfig he-ipv6
he-ipv6   Link encap:IPv6-in-IPv4  
          inet6 addr: fe80::2e26:a772/128 Scope:Link
          inet6 addr: 2001:470:1f08:1a7e::2/64 Scope:Global
          UP POINTOPOINT RUNNING NOARP  MTU:1480  Metric:1
          RX packets:411 errors:0 dropped:0 overruns:0 frame:0
          TX packets:251 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:488490 (477.0 KiB)  TX bytes:22616 (22.0 KiB)

Test ping

# ping6 -c2 ipv6.google.com
PING ipv6.google.com(par03s02-in-x11.1e100.net) 56 data bytes
64 bytes from par03s02-in-x11.1e100.net: icmp_seq=0 ttl=57 time=10.9 ms
64 bytes from par03s02-in-x11.1e100.net: icmp_seq=1 ttl=57 time=10.0 ms
--- ipv6.google.com ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1002ms
rtt min/avg/max/mdev = 10.070/10.512/10.955/0.454 ms, pipe 2

Test website

# curl -6  -v http://sriramrajan.com/status.php
* About to connect() to sriramrajan.com port 80
*   Trying 2001:470:1f08:1a7e::2... connected
* Connected to sriramrajan.com (2001:470:1f08:1a7e::2) port 80
> GET /status.php HTTP/1.1
> User-Agent: curl/7.15.5 (x86_64-redhat-linux-gnu) libcurl/7.15.5 OpenSSL/0.9.8b zlib/1.2.3 libidn/0.6.5
> Host: sriramrajan.com
> Accept: */*
> 
< HTTP/1.1 200 OK
< Date: Wed, 06 Jun 2012 20:34:45 GMT
< Server: Apache
< Vary: Accept-Encoding
< P3P: policyref="http://www.sriramrajan.com/p3p.xml",CP= "NON DSP COR CURa TIA"
< Content-Length: 7
< Content-Type: text/html; charset=UTF-8
ALL OK
* Connection #0 to host sriramrajan.com left intact
* Closing connection #0

#curl -I  -v  -g -k  http://[2001:470:1f08:1a7e::2]/status.php
* About to connect() to 2001:470:1f08:1a7e::2 port 80 (#0)
*   Trying 2001:470:1f08:1a7e::2... connected
* Connected to 2001:470:1f08:1a7e::2 (2001:470:1f08:1a7e::2) port 80 (#0)
> HEAD /status.php HTTP/1.1
> User-Agent: curl/7.21.7 (i386-redhat-linux-gnu) libcurl/7.21.7 NSS/3.13.3.0 zlib/1.2.5 libidn/1.22 libssh2/1.2.7
> Host: [2001:470:1f08:1a7e::2]
> Accept: */*
> 
< HTTP/1.1 200 OK
HTTP/1.1 200 OK
< Date: Sat, 09 Jun 2012 10:35:09 GMT
Date: Sat, 09 Jun 2012 10:35:09 GMT
< Server: Apache
Server: Apache

Check SSH

# ssh -6 -p 2222 2001:470:1f08:1a7e::2
root@2001:470:1f08:1a7e::2's password: 

Firewall
Don't forget the firewall. If you use iptables for a firewall, make sure, you have equivalent setup for IPv6. You can check using

# ip6tables -nvL

Mysql 5.6 new features

• Memcache plugin - This is released as a technology preview.
  http://blogs.innodb.com/wp/2011/04/nosql-to-innodb-with-memcached/
• Subquery optimizations are now included. This was originally planned in the now abandoned Mysql 6.0.
  http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
  
• InnoDB now has persistent statistics and can help in better analysis. http://blogs.innodb.com/wp/2011/04/innodb-persistent-statistics-at-last/
  
• InnoDB tablespaces are transportable. Some of the limitations and issues with previous releases are now gone.
  http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/ 
  
• InnoDB now has Fulltext index support.
  http://blogs.innodb.com/wp/2011/12/innodb-full-text-search-in-mysql-5-6-4/
  
• Row based replication now includes the original query.
  http://d2-systems.blogspot.com/2011/04/mysql-562-dm-binlog-informational.html
  
• Time delayed replication - This was doable using Percona(Maatkit) tools but is now in built.
  http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html#replication

Cisco Anyconnect Errors

Anyconnect client gives this error

AnyConnect cannot confirm it is connected to your secure gateway.  The local network may not be trustworthy.  Please try another network.

After fighting it for a while, I found an answer in the release notes

http://www.cisco.com/en/US/docs/security/vpn_client/anyconnect/anyconnect25/release/notes/anyconnect25rn.html

Firefox 2.0 or later with libnss3.so installed in /usr/local/lib, /usr/local/firefox/lib, or /usr/lib. Firefox must be installed in /usr/lib or /usr/local, or there must be a symbolic link in /usr/lib or /usr/local called firefox that points to the Firefox installation directory.

So the following fixes it

mkdir /usr/local/firefox

cd /usr/local/firefox

ln -s /usr/lib64/libnss3.so 

ln -s /lib64/libplc4.so 

ln -s /lib64/libnspr4.so 

ln -s /usr/lib64/libsmime3.so 

Linux file descriptors and open modes

Ever want to find out what modes a file was opened with originally ?

First find the file descriptor number

ls /proc//fd
Eg
ls -l /proc/32048/fd/30
l-wx------ 1 apache apache 64 Jan 18 07:15 30 -> /var/log/httpd/ntop-access_log

Then check fdinfo

cat /proc/32048/fdinfo/30
pos: 0
flags: 0102001

The flags are derived from the open system call http://linux.about.com/od/commands/l/blcmdl2_open.htm

To actually decipher the octal codes , look under /usr/include/bits/fcntl.h

If there are multiple codes, the codes are appended together.

Linux ACLs

Filesystem options and commands

First check to make sure the file system is mounted with acl settings

cat /proc/mounts |grep acl

/dev/sda1 / ext3 rw,noatime,relatime,errors=remount-ro,acl,data=ordered 0 0

If not update /etc/fstab and add 'acl' to the options section and remount the file system

getfacl, setfacl, chacl are the two main commands. chacl is available for IRIX compatibility.

Use Cases

Grant 2 users permissions to the same directory and files under it

Let's say we want to grant user john and mary permissions to folder /var/www/mysite.com

We can start by creating the directory. At this point we can leave it owned by root as the ACLs will help here.

ls -ld /var/www/mysite.com/
drwxr-xr-x 2 root root 4096 Feb 11 15:21 /var/www/mysite.com/

The first 2 commands grant users john and mary permissions.

The second sets the default acl. This causes the acls to be applied with inhertiance set. So this makes good sense in a multi user multi edit environment. The next arguments between the : are the username and the permissions

setfacl -m john:rwx mysite.com 
setfacl -m mary:rwx mysite.com 

setfacl -m default:john:rwx mysite.com 
setfacl -m default:mary:rwx mysite.com

# file: mysite.com
# owner: root
# group: root
user::rwx
user:john:rwx
group::r-x
mask::rwx
other::r-x
default:user::rwx
default:user:john:rwx
default:user:mary:rwx
default:group::r-x
default:mask::rwx
default:other::r-x

Now create a file by logging in a user john.

john@slice01$ echo "john" > file1

john@slice01$ ls -l file1 
-rw-rw-r--+ 1 john john 4 Feb 11 15:31 file1

john@slice01$ getfacl file1 
# file: file1
# owner: john
# group: john
user::rw-
user:john:rwx   #effective:rw-
user:mary:rwx   #effective:rw-
group::r-x   #effective:r--
mask::rw-
other::r--

Then create a directory

john@slice01$ mkdir john

john@slice01$ getfacl john
# file: john
# owner: john
# group: john
user::rwx
user:john:rwx
user:mary:rwx
group::r-x
mask::rwx
other::r-x
default:user::rwx
default:user:john:rwx
default:user:mary:rwx
default:group::r-x
default:mask::rwx
default:other::r-x

As you can see mary is there in the ACLs also

You can test it by logging in as user mary & editing files created by john.

mary@slice01$ echo mary >> file1 

mary@slice01$ cat file1 
john
mary

mary@slice01$ cd john/

mary@slice01 $ echo mary > file2
mary@slice01 $ getfacl file2 
# file: file2
# owner: mary
# group: mary
user::rw-
user:john:rwx   #effective:rw-
user:mary:rwx   #effective:rw-
group::r-x   #effective:r--
mask::rw-
other::r--

Grant 2 users permissions to the same directory and files under it except to 2 individual directories

Lets say we want john and mary to have permissions under /var/www/mysite.com/ and all files but still have individual directories
/var/www/mysite.com/john & /var/www/mysite.com/mary

 getfacl mary
# file: mary
# owner: mary
# group: mary
user::rwx
user:john:rwx
user:mary:rwx
group::r-x
mask::rwx
other::r-x
default:user::rwx
default:user:john:rwx
default:user:mary:rwx
default:group::r-x
default:mask::rwx
default:other::r-x

The -k switch removes the default acls

setfacl -k mary

getfacl mary
# file: mary
# owner: mary
# group: mary
user::rwx
user:john:rwx
user:mary:rwx
group::r-x
mask::rwx
other::r-x

Then remove john from it also

setfacl -x john mary

getfacl mary
# file: mary
# owner: mary
# group: mary
user::rwx
user:mary:rwx
group::r-x
mask::rwx
other::r-x

Repeat the same with other folder