You are here: Home / 2013 / Archives for February 2013

Percona Live Conference Recommendations

by

Percona Live MySQL Conference and Expo, April 22-25, 2013

While many attendees are repeat offenders, if 2013 is your first MySQL conference and you are relatively new with MySQL (say < 2 years experience), it can be daunting to determine which of the 8 or more concurrent sessions you should attend during the conference.

Here are my top recommendations that give you a good grounding in the various conference topics and a wealth of experience from known MySQL authorities, on important topics.

  1. A backup today saves you tomorrow by Ben Mildred at Pythian. Losing your data is a terrible experience. Learn what is needed to keep your data safe and you system highly available.
  2. Survey of Percona Toolkit: Command-Line tools for MySQL by Daniel Nichter at Percona. There are a wealth of additional MySQL tools that any resource should be familiar with. These are some of the most popular.
  3. Script It. Make Professional DBA tools out of nothing by Giuseppe Maxia at Continuent. I seasoned expert in the MySQL field, his expertise is invaluable to learn how to use MySQL effeciently. Giuseppe is also the creator of MySQL Sandbox, a huge productivity tool for developers.
  4. Practices for reducing MySQL database size by Yoshinori Matsunobu at Facebook. This is a consulting technique I use for great advantage with clients to improve performance. Yoshinori is also one of the most popular technical speakers at events.
  5. MHA: Getting started and moving past the quirks by Colin Charles at Monty Program. Creating a HA environment is essential for any successful application. MHA is one open source approach that should be considered.
  6. Managing data and data archiving using MySQL 5.6 new features of portable tablespace and exchange partition by Marco tusa at Pythian. If there was one footnote feature in MySQL 5.6 that has a huge benefit, this is the feature. As data continues to grow rapidly in size, archiving is more important.

This year’s conference talks are organized by topic and skill level. This can also help you find talks specific to your needs. Topics include the following:

  • Developing Applications
  • Tools
  • Best Practices for Businesses
  • Database Administration
  • Utilizing Hardware
  • Replication and High Availability Strategies
  • Treads in Architecture and Design
  • New Features

2013 is sure to be a great event, with a lineup of many MySQL product features for the MySQL ecosystem.

When is the error log filename not the right filename

by

When evaluating a MySQL system one of the first things to look at is the MySQL error log. This is defined by the log[_-]error variable in the MySQL Configuration file. Generally found like:

grep log.error /etc/my.cnf
log_error=/var/lib/mysql/logs/mysql_error_log
log-error=/var/lib/mysql/logs/mysql_error_log

It is possible to find multiple rows because this could be defined in the [mysqld] and [mysqld_safe] sections. It is also possible it is incorrectly defined twice in any given section.

Immediately I see a problem here, and the following describes why. If you look at this file name, in this case it’s actually found, but the file is empty.

$ ls -l /var/lib/mysql/logs/mysql_error_log
-rw-r----- 1 mysql mysql 0 Feb 19 20:35 /var/lib/mysql/logs/mysql_error_log

An error log should never exist and be empty, because starting the instance producing messages. An error log could be empty because the system does a log rotate (BTW, never rotate the error log, see The correct approach to rolling MySQL logs). However it is empty in this case because MySQL is not writing to the error log filename as defined, because it does not have file extension. NOTE: there is no .log or similar extension. Looking more closely.

$ ls -l /var/lib/mysql/logs/mysql_error_log*
-rw-r----- 1 mysql mysql      0 Feb 19 20:35 /var/lib/mysql/logs/mysql_error_log
-rw-rw---- 1 mysql root  394530 Feb 19 20:35 /var/lib/mysql/logs/mysql_error_log.err

As you can see, MySQL has overwritten your parameter and given the file an extension, as verified by the runtime value.

mysql> show global variables like 'log_error';
+---------------+-----------------------------------------+
| Variable_name | Value                                   |
+---------------+-----------------------------------------+
| log_error     | /var/lib/mysql/logs/mysql_error_log.err |
+---------------+-----------------------------------------+

So, the tip is, always use .log for your MySQL error (and slow query log).

NOTE: Placing the log files in the MySQL datadir (which defaults to /var/lib/mysql) is also a bad idea. A topic for another blog post at a later time.

The MySQL symlink trap

by

Many users of MySQL install and use the standard directories for MySQL data and binary logs. Generally this is /var/lib/mysql.
As your system grows and you need more disk space on the general OS partition that commonly holds /tmp, /usr and often /home, you create a dedicated partition, for example /mysql. The MySQL data, binary logs etc are then moved to this partition (hopefully in dedicated directories). For example data is placed in /mysql/data.
Often however, a symbolic link (symlink) is used to so MySQL still refers to the data in /var/lib/mysql.

When it comes to removing the symlink and correctly configuring MySQL, you first stop MySQL and correctly defining the datadir my.cnf variable to point to the right location. However, MySQL still keeps the legacy directory information around and this will cause MySQL replication to fail in several ways when you attempt to restart your MySQL instance.

The binary log index, the relay log index, and the relay log info files all contain the legacy path. MySQL does not make it easy to also determine these actual files.

The relay_log_index variable defines the index, but defaults to [relay_log].index when not defined, so with SHOW GLOBAL VARIABLES this may be blank.
log-bin-index is an configurable option, but no matching global variable. It defaults to [log-bin].index.
relay_log_info will contain a value, generally only a file that is relevant to the data directory.

In these situations, your only option to to manually edit these files, specifying the new datadir (or log-bin) path in order to correctly remove symlinks.

The best advice, is to consider the design of your system first, and never place data in default locations if you feel this has to be modified later. Define those dedicated directories before you start using your MySQL instance.

Poor programming practices

by

When will it stop. These amateur programmers that simply cut/paste code really affect those good programmers in the ecosystem trying to make a decent living. I was reviewing a developed (but incomplete) PHP/MySQL system using a common framework (which in itself is irrelevant for this post).

In one source file there were 12 repetitions of the following code:

   //permissions
    $this->security_model->setUserPermissions($id);
    if (!array_key_exists($id,$this->session->userdata['permissions']) OR
	!array_key_exists('id', $this->session->userdata['permissions'][$id]) OR
	!array_key_exists('scope', $this->session->userdata['permissions'][$id]['name'])){
      $this->session->set_flashdata('alert', 'You are not authorized to go there.');
      redirect($this->agent->referrer());
    }

It’s bad enough when code is repeated and not put in a simple re factored function. When it’s repeated 12 times in one file, and OMG over 100 times in the product, that is a recipe for bugs, and high maintenance codes due to extremely poor coding practice.