Schlagwort-Archiv: MySQL

[EN] New IPv6 features with MySQL 5.6

MySQL 5.6 was released a few days ago on 5th of February 2013 and offers some quite exciting improvements. With that I do not mean the performance things everyone mentions. Nor I mean the new memcached Interface. For our company (an internet hosting company) there is small, but important improvement: There are native SQL functions to store (and work with) IPv6 addresses in an efficient manner!

You may already know INET_ATON, INET_NTOA to store v4 addresses (you want to use INT UNSIGNED or VARBINARY(4) for that). With Version 5.6 you have fully compatible ones for v6 too: INET6_NTOA, INET6_ATON and some goodies like IS_IPV4() and IS_IPV6. You can find a list of these functions in the official MySQL manual concealed under „Miscellaneous Functions„. For the sake of completeness I have to mention that you should use VARBINARY(16) for IPv6/IPv4 mixed addresses to gain optimal efficiency in terms storage space.

As already pointed out, this is a new MySQL 5.6 feature. If you have installed a mysql-server via your package management (e.g. apt-get/dpkg) you probably have installed MySQL 5.5. For those who don’t have the possibility to upgrade, take a look at the watchmouse blog, they already realized an analoge behavior with MySQL user defined functions years ago.

You don’t have MySQL 5.6 running in production until now? Don’t worry! I believe that most current installations are „old“ as there isn’t any distribution out there at moment that delivers version 5.6 out of the box. If you plan a migration at your company, I would recommend you to visit Kristian Köhntopps talk at OSDC 2013. For those who can’t wait, I have also written a blog post about how to install MySQL 5.6 on Ubuntu 12.04.

[EN] How to install MySQL 5.6 on Ubuntu 12.04 (Precise)

This how to will cover the upgrade process of MySQL 5.6 on Ubuntu 12.04 precise. This may also work on Ubuntu 12.10 (quantal) but I haven’t tested it at moment.

DISCLAIMER: I am not responsible for any DATA LOSS you may face when using this how to! Use it at your own risk, create a backup and use your brain! With manual installation you will definitely LOOSE the support of your distribution vendor in case of security updates, be aware of this fact! Thank you!

This how to was created on an up to date (at the day of writing) Ubuntu 12.04.2 LTS machine (use lsb_relase -a to check). MySQL was already running on this machine.

First of all you need to download your new mysql 5.6 server software. You will find the latest version at the download page of Select „Debian Linux“ as your platform and depending on your architecture (use uname -i to get this info) you should choose the appropriate package. I have selected x86, 64 bit and will use the package „mysql-5.6.15-debian6.0-x86_64.deb“ for the next steps. Hint: Oracle suggests you to login or register, you don’t need it as there is a link „No thanks, just start my download“ at the bottom of the page ;-).

Download the package (about 300 MB):

wget -O mysql-5.6.15-debian6.0-x86_64.deb

… and install it using dpkg:

dpkg -i  mysql-5.6.15-debian6.0-x86_64.deb

… don’t be a messy and delete the package as you hopefully won’t need it again:

 rm mysql-5.6.15-debian6.0-x86_64.deb

Now look at your fresh mysql 5.6 setup at /opt/mysql/server-5.6/. You should really read the INSTALL-BINARY file as there are some remarks that are good to know and aren’t covered in this howto.

As I mentioned there was already an running instance of mysql on my system. If something wents wrong we really want to have a kind of backup. You could do this on different ways, either you could use mysqldump or to keep it simple stop mysql using service mysql stop and copy your /var/lib/mysql directory using cp -rp /var/lib/mysql /var/lib/mysql.old. This may take a lot of time depending on the size of your databases. In case of emergeny you could then install your distributions mysql version and use your old data.

service mysql stop && cp -rp /var/lib/mysql /var/lib/mysql.old

Now, as you have saved your production data you can remove your Ubuntu version of mysql-server. You can do this by:

apt-get remove mysql-server mysql-server-5.5 mysql-server-core-5.5

Move your my.cnf from /etc/mysql/my.cnf which is the Ubuntu default location to /etc/my.cnf which is the one mysql uses for the start scripts. Do this by mv /etc/mysql/my.cnf /etc/my.cnf and note that the /etc/mysql/conf.d/* files will be included anyway.

mv /etc/mysql/my.cnf /etc/my.cnf

To get mysql started you will need to copy the new startscript to /etc/init.d by cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server. As you probably want mysql to start automatically on system startup use update-rc.d mysql.server defaults to create a standard runlevel configuration.

cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql.server && update-rc.d mysql.server defaults

MySQL 5.6 depends on libaio which can installed by:

apt-get install libaio1

Set correct rights for your mysql installation:

chown -R mysql /opt/mysql/server-5.6/
chgrp -R mysql /opt/mysql/server-5.6/

Update some paths in your /etc/my.cnf (use a text editor of your choice, e.g. nano):

basedir = /opt/mysql/server-5.6
lc-messages-dir = /opt/mysql/server-5.6/share

And update your mysql information schema to the latest version:

/opt/mysql/server-5.6/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql

Pitfall: This will create my.cnf in /opt/mysql/server-5.6/ which sets SQL mode to strict. Your applications may have problems with this mode. For compatibility reasons you should DELETE this my.cnf and only rely on your /etc/my.cnf. Delete it by rm /opt/mysql/server-5.6/my.cnf. You won’t be able to overwrite this setting with your /etc/my.cnf and remove STRICT_TRANS_TABLES from your sql_mode. Read more about mysql_install_db here.

rm /opt/mysql/server-5.6/my.cnf

Start mysql by

service mysql.server start

And check your /var/log/syslog for errors. If everything works fine, you should see something like "Version: '5.6.10' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)" and you shoudn’t find any mysql related errors.

Have fun with your freshly upgraded mysql 5.6 server!

Update (29.12.2013): Updated the download url and package name to mysql-5.6.15-debian6.0-x86_64.deb (Thanks hien)!