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(),'G...

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: http://seclists.org/fulldisclosure/2011/Mar/309 http://tinkode27.baywords.com/sun-com-sun-mycrosystems-vulnerable-sql-injection/

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.

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'v...

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 wo...

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.