Monday, March 28, 2011

MySQL Timezone support: Updating and Monitoring

How old is your timezone info?
As the timezone database gets updated frequently, it's important to update the internal timezone database in MySQL. This is needed for the CONVERT_TZ() function for work properly.

It's not easy to determine if the data in mysql is older than the data from the tzdata database. It get's a bit better if you use this hack before importing the zone database:
ALTER TABLE mysql.time_zone_transition ADD COLUMN `Last_Update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
I've created Bug #60675 to get the system database updated.

Which database serves still need timezone info?
Use MySQL Enterprise Monitor with custom rules.

1. Add data query
Open /opt/mysql/enterprise/agent/share/mysql-monitor-agent/items/custom.xml in an editor and add this:
<class>
<namespace>mysql</namespace>
<classname>tzdata_available</classname>
<query><!--[CDATA[SELECT CONVERT_TZ(NOW(),'GMT','MET') IS NOT NULL]]--></query>
</class></blockquote>


2. Restart the mysql-monitor-agent

3. Create a custom rule in the MySQL Enterprise Dashboard (Advisor→Manage Rules→create rule):

Rule Name: Timezone data is not loaded.
Expression: %tzdata_avail% == THRESHOLD
Critical: 0
Variable Assignment
Variable: %tzdata_avail%
Data Item: mysql:tzdata_available:tzdata_available
Instance: local
Default Frequency: 30 minutes

Problem Description: "There is no timezone info loaded in mysql. This causes the CONVERT_TZ() function to return NULL instead of the requested conversion."

Advice: "None Specified"

Recommended Action: "Use the mysql_tzinfo_to_sql utility to convert the zoneinfo database to SQL and load the converted data into the mysql database."
Links and Further Reading:
* {moreInfo:mysql_tzinfo_to_sql — Load the Time Zone Tables|http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html}
* {moreInfo:MySQL Server Time Zone Support|http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html}

4. Add the rule to a schedule

SQL injection on MySQL.com

For those who didn't know it already: MySQL.com was "hacked" last weekend. There also was a report about SQL injection on 2 Sun.com domains.

I haven't seen an announcement from Oracle yet.

More info on:

Thursday, March 10, 2011

Restore a full mysqldump file w/o the mysql database

So you've got a database dump of a few gigabytes and you wat to restore all databases except one. Using vi won't work as the file is too large to be easily edited. But luckily an awk oneliner does do the job.

gawk '/^CREATE DATABASE.*/ { db=substr($7,2); db=substr(db,1,length(db)-1) } /^/ {if (db !=
"mysql") { print }}' original_dump.sql > original_dump_wo_mysql.sql


The same awk code , but now formatted for readability:
/^CREATE DATABASE.*/
{
db=substr($7,2);
db=substr(db,1,length(db)-1)
}

/^/
{
if (db != "mysql") {
print
}
}
Of course you could pipe it directly into mysql saving some space on the filesystem. And you could also do the same with perl/python.

Using SSL with MySQL

Since MySQL 4.0 it's possible to use SSL to secure the connections to MySQL. Unfortunately this is not used very often.

It can be used for cient-server connections and for replication. It will encrypt your traffic and prevent man-in-the-middle attacks if you're using ssl-verify-server-cert when connecting using mysql.

Securing database communication can be required to be compliant to some regulations.

There are other means of preventing anyone from snooping your database traffic like VPN, SSH Tunneling or stunnel.

As usual the documentation is quite good. The only issue with the docs is that the verification step is missing. (Bug #59628).

I'ts quite hard to debug as the OpenSSL messages are not reported in MySQL's errors. Bug luckily that's changing as Bug #21287 does have a patch under SCA/OCA.

And you should check your my.cnf for typo's when it's not working as mysqld won't tell you when it can't find the ssl-ca file. (Bug #59630).

I've used tcpdump to capture network traffic when SSL didn't work and loaded it into wireshark. Wireshark has very neat features to decode all kinds of traffic (HTTP, MySQL, SSL and even FC).

Also a debug build can provide you with some more info.

MySQL uses the same port for SSL and non-SSL connections. So you don't have to change your firewall rules. This also means that you can't force SSL on your firewall.

There are a few SSL status variables availables in the output of SHOW GLOBAL STATUS LIKE 'Ssl_%'; The documetation is here. The only issue is that it doesn't actually work. (Bug #59635). This is bad as statistics about renegotiation could indicate renegotiation vulnerabilities.

To force SSL you should use REQUIRE SSL or REQUIRE X509 with your CREATE USER or GRANT statements. Optionally you could use the REQUIRE SUBJECT, but that only works if the RDN order is how the server expects it to be (Bug #59376).

There are also some known issues with mixing OpenSSL and YaSSL your should be aware of.

Using SSL is very well possible, but there is room for improvement.

I haven't tested the performance impact of using SSL. I would also be interesting to see if the hardware SSL in the Sun T2 cpu would speed this up. (Sun T2 PDF). So Percona/Oracle: start your benchmarks!

As far as I known the MySQL branch/forks like Percona Server and MariaDB are using almost the same code/features for SSL as Oracle.

Friday, March 4, 2011

MySQL Workbench on RHEL6

Yesterday I blogged about some issues with MySQL Workbench. The problem was that CentOS 5.5 is not supported.

So I installed RHEL6, one of the best known Enterprise Linux distributions. It's a 30 day evaluation, but that should be sufficient.

After some minor issues with VirtualBox I had a working installation. So I tried to install MySQL Workbench again. I used 'sudo yum localinstall mysql-workbench-gpl-5.2.31a-2el6.x86_64.rpm' which failed because of 2 unmet dependencies: pexpect and libzip.so.1

There is already a bug report in RHEL for the missing pexpect: #652659
I used the RHEL6 installation DVD to install pexpect.

There is a closed/wont-fix bug (#54403) for the libzip.so.1 dependency. It turns out that libzip is in the "RHEL Server Optional" channel. RHN should be used to enable more channels, but apparently the optional channel is not part of the evaluation license, so I used the web interface to download and install the libzip RPM.

The workbench installation finaly completed after complaining that the workbench rpm is not signed (use --nogpgcheck).

So I started MySQL Workbench (There is a launcher in the menu). And then I clicked the "MySQL Doc Library" and Workbench responded with an error about a missing package: pysqlite2/python-sqlite2. I could find a python-sqite for RHEL5 on RHN... but nothing for RHEL6. So I filed Bug #60336

Thursday, March 3, 2011

MySQL Workbench on CentOS

Every once in a while I use MySQL Workbench. First is was only for Windows, then it was unstable and now it is very well usable. Or at least that is the situation when you run on a recent Ubuntu machine.

I'm now using CenOS 5.5 in a VirualBox VM to do some testing. Unfortunately the download site for MySQL Workbench has RPMs for RedHat 6 and Fedora Core 14 and some other platforms. No CentOS, No RHEL5, only the latest Fedora en RHEL.

So I downloaded the RHEL6 RPMs and tried to install them with yum localinstall, but that failed. So they are not backwards compatible with 5.5.

Turns out that CentOS/RHEL 5 doesn't have the required libraries to support MySQL Workbench.

And unfortunately CentOS 5.5 is still the latest release. So an upgrade to CenOS 6 is not yet possible.