MySQL Certification Study - DBA Topics

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.