Category: Certification

For the MySQL DBA Exam, I wrote that I needed to brush up on Security and High Availability techniques. So let’s take a look at these two in more detail.

Security

There were quite a lot of enhancements to security introduced in MySQL 5.6, and I want to make sure I’m not caught off guard by new questions. The major changes (with links to manual pages) are:

• Password expiration support
• Enforce password policy such as minimum length or special chars requirements.
• sha256_password for stronger password hashing.
• Secure-auth enabled by default. This means clients using passwords stored in pre-MySQL 4.1 format won’t be able to connect.
• mysql_config_editor has been introduced. The intention of this utility is to stop users from entering passwords in command line arguments.
• Passwords are no longer written to log files.

Enterprise Edition:
– PAM module for providing external authentication such as Unix passwords or an LDAP directory (actually this change was in MySQL 5.5).
* Native Windows authentication is also supported.
– Audit log plugin for policy-based monitoring and logging (technically also introduced in MySQL 5.5).

Additionally, it’s worthwhile looking at these manual pages:
* http://dev.mysql.com/doc/refman/5.6/en/security.html
* http://dev.mysql.com/doc/refman/5.6/en/general-security-issues.html

High Availability Techniques

I wrote about availability a couple of days ago, but this is only part of the problem. The certification wants you to know about the technologies that have been certified as officially supported:

• DRBD – Most people describe DRBD as “Network level RAID1 for Linux”. It replicates disk-block changes from one server to another, and is typically configured as two servers: one active, one passive, with a virtual IP address pointing to the current ‘active server’.

  Actually, that’s a bit of a simplification: DRBD only provides the disk-block replication. It needs a cluster manager such as Pacemaker or Corosync to do the virtual IP address and failover part.

• Windows Failover Clustering – This solution is more analogous to Pacemaker than it is to DRBD. What WFC allows you to do is share a virtual IP address between at least two servers that access mysql data files via shared storage. WFC will ensure that only one server will have the mysql data files open at a time.

• Solaris Cluster – MySQL provides an agent to work with the Solaris cluster framework.

• OVM Templates for MySQL Enterprise – What this solution does, is allows MySQL to run as a virtual machine. If the underlying physical host fails, the MySQL VM can be migrated to another physical host. Live migrations are also supported if the host hardware requires maintenance. This kind of setup relies on shared storage.

I previously wrote that I am studying for the MySQL 5.6 exam, and that I’m less
confident in my skills as a database developer. When I went through the list of topics in the exam, one thing I knew I would have to study is stored programs.

So first, some definitions. From the manual:

So a stored program is the broader name applied to code that runs in the database. A procedure is different to a function because it is executed via a call statement and can modify input parameters rather than just returning a value. Check.

The next point I have to look at is creation and executing stored procedures and functions. That’s covered on this manual page:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

I also need to know how to implement error handling within a stored procedures. This is actually improved a lot in 5.6 with the addition of GET DIAGNOSTICS. Using the example from the manual page:

CREATE PROCEDURE do_insert(value INT)
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE rows INT;
  DECLARE result TEXT;
  -- Declare exception handler for failed insert
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

  -- Perform the insert
  INSERT INTO t1 (int_col) VALUES(value);
  -- Check whether the insert was successful
  IF code = '00000' THEN
    GET DIAGNOSTICS rows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ',rows);
  ELSE
    SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
  END IF;
  -- Say what happened
  SELECT result;
END;

So we DECLARE A CONTINUE HANDLER (manual pages here and here) and then optionally use GET DIAGNOSTICS for more fine-grained error information. We can also return an error using the SIGNAL functionality (manual page here).

Like many others, I’ll be taking the MySQL 5.6 certification exam at the end of this month. As Todd blogged, the quick reference pages are available online for the Developer Exam and the DBA Exam. I’ve long considered myself more of a DBA, so I know the the developer exam that I need to study up on these topics:

But there are also a few DBA topics I could benefit from a refresh on:

I’ll be writing more on my experiences studying these topics (and which manual pages helped me out) in the coming days.