MySQL

Technology

HOWTO: Show a summary of data used by all MySQL tables

February 28, 2011 by 0 Comments

Here is a useful MySQL command that shows (an approximation of) the amount of storage (disk and memory) consumed by each table in a MySQL database.

SELECT
 TABLE_NAME,
 TABLE_ROWS,
 DATA_LENGTH / (1024*1024),
 INDEX_LENGTH / (1024*1024),
 (DATA_LENGTH + INDEX_LENGTH) / (1024*1024) as total
FROM
 information_schema.TABLES
WHERE
 TABLE_SCHEMA = (SELECT DATABASE())
GROUP BY
 TABLE_NAME
ORDER BY
 total;

Please bear in mind that the data returned will be an approximation, but should still show you a reasonable enough split which of your tables are consuming most of the resources.

Technology

Securing MySQL databases (the short way)

March 23, 2009 by 0 Comments

If you have ever installed of MySQL on a *nix system, you may have come across the mysql_secure_installation script. It takes you through a number of interactive questions/steps that help you to increase the security of your MySQL installation, and is quite a useful script.

As we have a high deree of automation in our server environment, we decided to break this script down to its raw MySQL command, and in a nut shell, here is what it actually does behind the scenes (assuming you want to answer Yes to all of the questions it asks in interactive mode):

UPDATE mysql.user SET Password=PASSWORD('XXXX') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

Naturally, you should replace the XXXX in the first command with an appropriate password.

Technology

MySQL Upgrade Gotcha

November 6, 2008 by 0 Comments

I was trialling a MySQL database upgrade today (from 5.0 to 5.1) and hit a nasty little gotcha that I feel warrants a mention (mostly to myself). In its simplest form, the upgrade process is very straightforward and involves simply loading data dumped from MySQL 5.0 into a 5.1 database (using a regular mysql command), followed by a once-off execution of the mysql_upgrade command (to bring the various tables and grants up to date):

$ mysql -u root -p testdb < dumped50.sql
$ mysql_upgrade -u root -p testdb

However, when I tried the latter command, I received this error:

Looking for 'mysql' in: mysql
FATAL ERROR: Can't find 'mysql'

I did fine a simple solution here which was to first change to the directory containing the mysql_upgrade program itself and execute it from there:

$ cd /opt/coolstack/mysql/bin
$ ./mysql_upgrade -u root -p testdb

As I said, its a simple solution and I do tend to follow this general rule most of the time but nevertheless, the error generated when you don’t do this is far for informative.

Technology

MySQL Grants List Revealed

October 15, 2008 by 0 Comments

A colleague of mine just sent me this hugely useful MySQL command for seeing which users have privileges in a database, and from which host. It prints a list of other MySQL commands which can then be used to show more precisely what privileges that user has been granted.

SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user;

Prior to this, I found myself having to connect to the database from the appropriate (remote) MySQL client and issue a SHOW GRANTS from there.

Thanks to Mate Racz for this one.

Technology

Trial migration from Coolstack 1.2 to 1.3.1

October 1, 2008 by 2 Comments

I got an opportunity to run a trial migration of several applications from Solaris Coolstack version 1.2 to version 1.3.1 recently. The Coolstack applications that we use include MySQL, Apache (including mod_jk), Tomcat and Memcached.

I had no problems at all with the Tomcat or Memcached upgrades but did hit 2 minor issues with MySQL and Apache (mod_jk actually).

1. MySQL

This involved going from MySQL 5.0.45 to 5.1.25 and whilst I found that a regular mysqldump of my MySQL 5.0 databases imported without error into MySQL 5.1, I did encounter some minor issues with user permissions later on. Fortunately, the guide to Upgrading from MySQL 5.0 to 5.1 proved very useful and revealed that I had missed an essential step in the upgrade process which was the use of the mysql_upgrade utility. There is lots more to read about in the upgrade guide and depending on your configuration, there may be more steps to be considered.

2. Apache (mod_jk)

Whilst the Apache upgrade (2.2.6 to 2.2.9) worked a treat, I found that mod_jk 1.2.26 employs stricter rules in relation to the placement of JkMount/JkMountFile commands in VirtualHosts. In my previous configuration, I had a single JkMount command inside my mod_jk.conf file (which in turn was included from the main httpd.conf file). However, using this configuration with Apache 2.2.9 and mod_jk 1.2.26, I found that my AJP load balancer was not working correctly for one of my VirtualHosts. It was only when I moved my JkMount command inside the scope of the VirtualHost that it started working again.

Other Comments

Most of the SMF service names in the Coolstack 1.3.1 applications have been renamed slightly. In Coolstack 1.2, all of the SMF service names began with csk- but now they all end with -csk instead. This was apparently done to make the service names more compatible with OpenSolaris but, to be honest, I find it a little bit annoying as we’ve written a number of useful scripts that manage these services explicitly by name.

Coolstack 1.3.1 includes two different versions of Tomcat (5.0 and 6.0) which install to different locations and have different SMF service names.

Technology

Solaris CoolStack 1.3 released

June 11, 2008 by 2 Comments

CoolStack is a collection of some of the most commonly used open source applications optimized for the Sun Solaris platform. The latest version of this software suite, Cool Stack 1.3, has just been released. Here is a brief overview:

Notable Additions

  • Python 2.5.2
  • Nginx 0.6.31
  • New Apache modules including mod_python, mod_ruby, mod_dtrace etc.

Updated Versions

  • Apache 2.28
  • Tomcat 5.5.26
  • MySQL 5.1.24
  • PHP 5.2.6
  • Memcached 1.2.5
  • Ruby 1.8.6p114 with Ruby Gems 1.1.1 and Rails 2.0.2

Other Enhancements

  • Ruby performance improvements in the order of 20-30% (with a further 8-15% coming down the line when Ruby 1.8.7 is released)
  • Better support for installing Ruby Gems that require native compilation on SPARC systems

The addition of Nginx an Python support along with the performance improvements in Ruby as well as the additional Apache modules are all very useful additions and have yet again come at just the right time for our organisation. Well done to Shanti and his team for providing almost exactly everything we were hoping for in this release, again!

Technology

Purging MySQL Binary Logs

February 15, 2008 by 3 Comments

If you have binary logging enabled on your MySQL server (i.e. the log-bin parameter is set in your MySQL configuration file), then you may notice a buildup of rather large files in your MySQL data directory over time (e.g. mysql-bin.000013). Generally speaking, you only need to enable this binary logging if your server is acting as a Replication Master or if you need the ability to do point in time recovery from your latest backup.

In any case, here are some useful commands for purging your binary log files:

To delete all binary logs older than 7 days:

mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

To purge all logs before a specific date:

mysql> PURGE MASTER LOGS BEFORE '2008-01-01 00:00:00';

To purge logs automatically (every Monday at 3am) you could use a Unix cron job:

0 3 * * mon mysql -uroot -e "PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);"
Technology

MySQL Proxy Gotcha on Solaris SPARC

January 21, 2008 by 0 Comments

The Problem

During a recent install of MySQL Proxy on a Solaris 10 system (sparc u3), we encountered the following error whenever we supplied the --pid-file command-line argument:

Conversion from character set ‘646’ to ‘UTF-8’ is not supported

However, if we omitted the --pid-file argument, it worked just fine.
The Solution
Running ldd against the application revealed no missing libraries but when we ran it with truss, we discovered that it was indeed missing some library files:

open64("/export/home/mysqldev/mysql-proxy-32bit/inst/glib/lib/charset.alias", O_RDONLY) Err#2 ENOENT
access("/usr/lib/iconv/geniconvtbl/binarytables/646%UTF-8.bt", R_OK) Err#2 ENOENT
access("/usr/lib/iconv/646%UTF-8.so", R_OK) Err#2 ENOENT
open("/usr/lib/iconv/alias", O_RDONLY) Err#2 ENOENT

It turns out that the Solaris system we were using was originally installed using the Core System cluster (SUNWCreq) and did not have all the requisite Unicode packages installed. So, an installation of the SUNWuiu8 package (from the Solaris distribution media) duly resolved the matter.

Technology

Running multiple instances of MySQL with Solaris Coolstack 1.2

January 21, 2008 by 0 Comments

We recently had a requirement to run two instances of MySQL on two separate Solaris systems (for the purposes of dual-master replication). The systems in question were both SunFire T2000s running Solaris 10 (U3) and Coolstack 1.2 and already had a previous/single instance of MySQL running (SMF service csk-mysql).

In the end, it was a relatively straightforward exercise in that all we really did was replicate the existing instance (control script, configuration file and manifest file) and change the necessary bits of the resultant files so that the new instance was sufficiently different to the old, in the end, producing two independent SMF services (csk-mysql1 and csk-mysql2). Specifically, the following files were created:

MySQL Control Scripts:
/opt/coolstack/lib/svc/method/svc-cskmysql1
/opt/coolstack/lib/svc/method/svc-cskmysql1

MySQL Configuration Files:
/etc/my1.cnf
/etc/my2.cnf

MySQL Manifest Files:
/var/svc/manifest/network/cskmysql1.xml
/var/svc/manifest/network/cskmysql2.xml

The manifest files were identical but for the name of the service and the control script they invoke. The control scripts were also identical but for the configuration file and DBDIR that they use and the difference(s) in the configuration files were mainly in terms of the port numbers and socket files used.

Beware

The only thing that caught us out was in modifying the control scripts (to tell them which configuration file they should use). This was done by adding a --defaults-file argument to the invocation of mysql_safe. However, you need to ensure that this argument is the first one you pass to mysql_safe. Otherwise, MySQL will launch will not load the correct configuration settings. This is not (well) documented so beware!

Also, when trying to access the new instances via the mysql client, you will need to add the -P and -S arguments.