PlanetMysql.ru — информация о СУБД MySQL

[UPDATE 2011/01/11] : New poll added, vote for the best GUI client tool ! (And continue to vote for other polls)
And thanks again for your involvement. It’s time to vote again… 

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL+ Community Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.
And, please, share these polls !

 

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Happy 2012 !
Cédric

This article is obviously not sponsored !
(MySQL is a trademark of Oracle Corporation and/or its affiliates)

Sources :

• Oracle products, tools and services : http://www.mysql.com
• SkySQL products, tools and services : http://www.skysql.com
• Percona products, tools and services : http://www.percona.com
• FromDual products, tools and services : http://www.fromdual.com
• PalominoDB products, tools and services : http://palominodb.com/
• Pythian products, tools and services : http://www.pythian.com/
• Blue Gecko products, tools and services : http://www.bluegecko.net/
• Zabbix : http://www.zabbix.com/
• MySQL Cacti templates : http://code.google.com/p/mysql-cacti-templates/
• Nagios : http://www.nagios.org/
• r1soft CDP for MySQL : http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/ 
• mylvmbackup tool : http://www.lenzg.net/mylvmbackup/
• mydumper tool : http://www.mydumper.org/
• ZRM tools : http://www.zmanda.com
• Galera replication : http://codership.com/products/galera_replication
• Tungsten replicator : http://code.google.com/p/tungsten-replicator/
• DRBD : http://www.drbd.org/
• ShoonerSQL : http://www.schoonerinfotech.com/products/schoonersql
• ScaleBase : http://www.scalebase.com/
• common-schema : http://code.google.com/p/common-schema/
• innotop : http://code.google.com/p/innotop/
• MySQL Tuner : https://launchpad.net/mysqltuner/2.0/mysqltuner-2.1-rev17
• mycheckpoint : http://code.openark.org/forge/mycheckpoint
• MariaDB : http://mariadb.org/
• TokuDB : http://www.tokutek.com/
• InfiniDB : http://infinidb.org/
• Drizzle : https://launchpad.net/drizzle
• Amazon SimpleDB : http://aws.amazon.com/fr/simpledb//180-2635771-0721067/
• mongoDB : http://www.mongodb.org/
• Cassandra : http://cassandra.apache.org/
• CouchDB : http://couchdb.apache.org/
• Tokyo Cabinet : http://fallabs.com/
• Riak : http://basho.com/
• Hadoop : http://hadoop.apache.org/
• Tarantool : http://tarantool.org/
• memcached : http://memcached.org/
• MySQL Sandbox : http://mysqlsandbox.net/
• openark kit : http://code.openark.org/forge/openark-kit
• Redis : http://redis.io/
• Ganglia : http://ganglia.sourceforge.net/
• HandlerSocket : https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

PlanetMySQL Voting: Vote UP / Vote DOWN

Regardless of the monitoring tool you use to monitor your databases, it can be better to monitor this tool.
No, it’s not a joke ! Do you think you can have a benefit with a monitoring tool not connected to your servers ? ( without being alerted )

I choose to talk about MONyog here but this can apply to all existing monitoring tools.
I just want to share the message, the tool does not matter, so, do it !

So, let me explain how to control if you have fresh data with MONyog.
With MONyog it’s easy because it’s an agentless monitoring tool.

There are two ways to check that :

Per server general info :

 

 

 

 

 

 

For each server, you can find the last data updated date in the Monitors/Advisors tab.
The MySQL Availability in the dashboard is also usefull.

But it’s a per server information, so, that can be boring if you have a lot of databases servers.

General log file :

 

The default location for this file is MONyog_PATH/MONyog.log on your MONyog server.
You can find usefull informations there about MySQL, SFTP or SSH connexion failures for all your databases servers, in one place.

This log can also be used to detect client side errors if you don’t have access to the application servers.

Hope that can help, tell us what is the best method to do that with others monitoring tools like MySQL Enterprise Monitor.

Have a nice week-end.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was giving a presentation of the MySQL’s Enterprise Monitor* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

*The MySQL Enterprise Monitor (MEM) continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly. (from: http://www.mysql.com/products/enterprise/monitor.html)

Of course, you have to install MEM and at least one agent. Let’s assume that you have already accomplished this task, and that MEM is running properly. Open MEM in your browser, login, click on the graphs tab, and then you will see a list of all of the available graphs.

For this example, we are going to automatically download the Agent Reporting Delay and the Disk IO Usage graphs. We will download the first graph for all of the servers in a particular group, and the second graph for an individual server. First, click on a server group in your server list on the left side of MEM.

Next, we will need to change the Time Range settings to “From/To”, so that we can enter the a timeline for the graph in our script. Don’t worry about the time settings that are in MEM, as we will change these settings later, but we need them so that they will be included in the URL that we will use (more on this later). After you have changed the Time Range settings, click on the “Filter” button.

Next, click on the plus sign for the graph that you want to use so that MEM will draw the graph. For this example, we will click on the “Agent Reporting Delay” graph:

You will notice two icons to the right of the graph name. The first icon (on the left) allows you click on the icon to download the graph as a .csv file. The second icon (on the right) allows you to click on the icon and download the graph as a PNG image file.

We need some information from the actual link that is used when you click on the PNG icon. So, we will need to right-click on the icon to get the URL link location information for the Agent Reporting Delay graph:

The URL for this graph is then copied to your clipboard. This is the URL location (which is for all servers in the group that I selected):

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

As you can see in the URL above, there are several variable values that we will include in our script to produce our graphs (in blue text above). In this example, we will only be working with the following variables:
- dims_height
- dims_width
- time_fromDate
- time_fromTime
- time_toDate
- time_toTime
- servers_group and servers_server
(the servers_server variable and value are not shown in the above example, but will be in the next example below)

We will be using a text file named files.txt to store some of the graph variable values that will be used by the script. Now that you know how to copy the URL for a graph, you will need to extract the value for the graph variable and the value for the servers variable and place the values into your files.txt file. The graph value for the above URL (shown again below) is in blue text, and the value for the server variable is in red text: (notice that all values are separated on the left by an equal sign “=” and on the right by an ampersand “&”)

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

In the above example, we had selected a group of servers in our server list (on the left side of MEM), and therefore the URL will not have a value for the individual server (variable named servers_server). The graph that we will extract will be for this group of servers (in this case servers_group has a value of zero, which is still a value). This is what we had chosen under our Servers list:

Now, we want to select an individual server. In this case, we will click on “iMac-Tony”:

Now that we have chosen an individual server, in the URL for that graph, you will have a value for the variable named “servers_server”, as well as a value for servers_group – and you will need both values together. So, if you want a graph for an individual server, you will need to click on that individual server in your servers list, reselect the “Time Range” value of “From/To”, click “Filter”, and re-copy the PNG graph URL. Once we have copied the URL for this graph for an individual server, you will see a different value for the graph variable (in red) and a value for servers_group and servers_server (in blue) like this:

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c&locale=en_US&noDefaults=false&servers_group=0&servers_server=111&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=15%3A27&time_toDate=2011-11-16&time_toTime=15%3A57&time_type=FROMTO&tzName=America%2FNew_York

We will use the above URL information for our second graph – the Disk IO Usage graph. You will need to copy all of the graph and server values for the graphs that you want to download. For the above URL, we will grab these values, to be placed in our files.txt file:
graph = 6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c
server group and server name = servers_group=0&servers_server=111

This is a tedious process at first, but you should only have to do this once for each graph. (Caveat – I am not sure if the graph values change over time or when you upgrade the MEM software.)

In the files.txt file, we also want a name for the graph (which will also be used for the PNG image file name), the graph value, the servers value and the server or server group values from the above URLs, as well as the name of the server group or individual server. You should separate the values with a delimiter of three tildes “~~~”.

So, for the two example graphs above, your files.txt file should contain the following values – Graph Name~~~graph value~~~server information~~~server or group name: (please note that the graph values that I have here may not be the same values that you would have for the same graph)

Agent Reporting Delay~~~f924cb42-fed5-11df-923c-a6466b4620ce~~~servers_group=0~~~All Servers Disk IO Usage~~~6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c~~~servers_group=0&servers_server=111~~~iMac Tony

The first line above will produce an “Agent Report Delay” graph for “All Servers”. The second line will produce a “Disk IO Usage” graph for only the server named “iMac-Tony”.

Now that we have our files.txt file in place (it should be placed in the same folder as the Perl script – or you may modify the Perl script for a different file location), we will use this Perl script to download our graphs as PNG image files. In case you want to place this script in a cron job to run every X number of minutes, we will include a variable to allow you to select the previous number of minutes to include in your graph. For example, in the Perl script, if you set the value of the variable $time_interval to 60 (minutes) and run the job at 30 minutes past the hour, the script will retrieve a graph for the past 60 minutes from the time of script execution.

For this example, we will name the Perl script “get_graphs.pl”. There will be some variables in the script that you will have to change once, to match your system’s information. The variables that you need to change are highlighted in blue text in the script:

#!/usr/bin/perl use WWW::Mechanize; use Date::Calc qw(Add_Delta_DHMS); # file name for input - this contains the Graph Name and Graph URL $filename = "files.txt"; # time interval must be in minutes $time_interval = '60'; # the width of your graph $dims_width = "800"; # the height of your graph $dims_height = "300"; # IP and port number of your MEM server $server = "192.168.1.2:18080"; # get the current time using the display_time_now subroutine $unixtimenow = &display_time_now(); ($time_toDate, $time_toTime) = split(" ",$unixtimenow); # get the past time using the display_time_past subroutine $unixtimepast = &display_time_past(); ($time_fromDate, $time_fromTime) = split(" ",$unixtimepast); # fool the web server into thinking we are a person my $mech = WWW::Mechanize->new(); # look like a real person $mech->agent('User-Agent=Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5'); # we need cookies $mech->cookie_jar(HTTP::Cookies->new); # Login to the MySQL Enterprise Monitor $mech->get('http://192.168.1.2:18080/Auth.action'); $mech->success or die "login GET fail"; # you will need to substitute your user name and password for MEM here my $user = 'tonydarnell'; my $pass = 'tonyd999'; # find a fill out the login form my $login = $mech->form_name("DoAuth"); $login->value('username' => $user); $login->value('password' => $pass); $mech->submit(); $mech->success or die "login POST fail"; open(line, "$filename") || die (print "\nERROR - could not open file: $filename\n"); while (<line>) { chomp $_; print "\n$_\n"; ($imagefilename, $graph_to_get, $servers_to_get, $servers_name) = split(/~~~/); if (length($imagefilename) > 2) { $time_toDate_for_filename = $time_toDate; $time_toDate_for_filename =~ s/\-/_/g; $time_toTime_for_filename = $time_toTime; $time_toTime_for_filename =~ s/\:/_/g; $servers_name =~ s/ /_/g; $imagefilename =~ s/ /_/g; $imagefilename = $servers_name . "_" . $imagefilename . "_" . $time_toDate_for_filename . "_" . $time_toTime_for_filename . ".png"; # you will need to change your settings here to match your URL for your graphs $graph = "http://". $server . "/Graph.action?dims_height=" . $dims_height . "&dims_width=" . $dims_width . "&graph=" . $graph_to_get . "&locale=en_US&noDefaults=false&" . $servers_to_get . "&style=NORMAL&time_fromDate=" . $time_fromDate . "&time_fromTime=" . $time_fromTime . "&time_toDate=" . $time_toDate . "&time_toTime=" . $time_toTime . "&time_type=FROMTO&tzName=America%2FNew_York"; print "\n$graph\n"; #exit; # Get the PNG image file from the URL $mech->get($graph); $mech->save_content($imagefilename); } } exit; close($filename); # ------------------------------------------------ # sub-routines sub display_time_now { my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time(); $year += 1900; $mon += 1; return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min); } sub display_time_past { my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time() - ($time_interval*60); $year += 1900; $mon += 1; return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min); } # ------------------------------------------------

When we executed the script, two files were created and downloaded – All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png and iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png:

All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png

iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png

You could also create a similar script to download the information as a .csv file, but the syntax is very different (maybe I will do that in a future post). But for now, I have a possible solution for the client – and I hope that he likes it.

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was speaking with a MySQL customer last week and he had a feature request for MEM. He wants different servers to have different threshold values for certain MEM advisor rules and suggests that users be allowed to override the thresholds when scheduling the rule against a server – just as you can with the refresh interval. At the moment they work around this by creating multiple duplicates of the same rule and then set different thresholds in each. This is a good feature request but in the mean-time this post explains an alternate workaround – having the rules act on thresholds that are defined within the databases of the individual serves.

Step 1. Create a new threshold table in each MySQL Server

The table should be created and then a row added for each rule that needs a per-server threshold:

mysql> create database clusterdb;use clusterdb;
mysql> CREATE TABLE thresholds (name VARCHAR(50) NOT NULL PRIMARY KEY,
       scale_percentage INT);
mysql> INSERT INTO thresholds VALUES ('connect_usage_excessive',50);

The scale_percentage value will be used in the new MEM rule as a scaling factor for the info, warning and critical threshold levels.

Step 2. Add custom data collector(s)

First of all, check that you have permissions to edit the custom.xml file – on Windows this will be stored in a location such as C:\Program Files\MySQL\Enterprise\Agent\share\mysql-monitor-agent\items

For each rule that needs a variable threshold, add a new class:

<class>
     <namespace>mysql</namespace>
     <classname>connection_usage_excessive</classname>
     <query><![CDATA[SELECT scale_percentage AS
         connection_usage_excessive_scale_percentage
         FROM clusterdb.thresholds
         WHERE name='connect_usage_excessive']]></query>
     <attributes>
          <default counter="false" type="INTEGER"/>
     <attribute name="connection_usage_excessive_scale_percentage"/>
     </attributes>
</class>

This custom.xml files should be copied to every server. For this data collector to be picked up by MEM, simply restart the agent(s).

Step 3. Copy and extend the rule

From the “Advisors/Manage Rules” tab of the MEM browser click on the copy icon next to the rule you want to apply per-server thresholds to and then edit that rule.

When editing the rule, add a new variable “%threshold_scale%” which uses the new data collector you’ve just created and then in the expression apply that as a percentage to factor the threshold.

Step 4. Schedule the rule

You can now go ahead and schedule this rule against each of the servers – and the threshold will automatically be scaled appropriately for each server.

PlanetMySQL Voting: Vote UP / Vote DOWN

About 11 months ago I described the MySQL Cluster functionality that was added to MySQL Enterprise Monitor 2.3; this new post is intended to just bring this up to date – briefly describing the new graph and advisors which have been added since then (up to and including MEM 2.3.7).

Cluster Data Node Has Been Restarted

This new alert flags when a data node has been restarted (by default it alerts on any data node that has started in the last 10 minutes but you can change that interval if you wish). If you manually perform a restart (e.g. as part of a rolling upgrade) then you can safely ignore this alert (or you may even want to temporarily unschedule it first). However if the restart was spontaneous then this can be an early warning for you to take a look at the error logs and address any issues before the situation worsens.

Cluster DiskPageBuffer Hit Ratio Is Low (& associated graph)

The Disk Page Buffer is a cache on each data node which is used when using disk-based tables. Like any cache, the higher the hit rate the better the performance. Tuning the size of this cache can have a significant effect on your system – the new graph helps you see the results of your changes and the alert warns you when the ration falls below an acceptable level (this could happen for example temporarily after a data node restart or permanently when the active data set grows).

The ndbinfo database has a new table “diskpagebuffer” which contains the raw information needed to calculate the cache hit ration and it is the source of the data for the new alert and graph. If you wanted to calculate the cache hit ratio for yourself directly from this table then you can use the following query:

mysql> SELECT node_id, page_requests_direct_return AS hit, 
 page_requests_wait_io AS miss,  100*page_requests_direct_return/
 (page_requests_direct_return+page_requests_wait_io) AS hit_rate
  FROM ndbinfo.diskpagebuffer;

+---------+------+------+----------+
| node_id | hit  | miss | hit_rate |
+---------+------+------+----------+
| 3       | 6    | 3    | 66.6667  |
| 4       | 10   | 3    | 76.9231  |
+---------+------+------+----------+

The alert is first raised (info level) when the hit rate falls bellow 97%, the warning level is raised at 90% and the critical level at 80%. Again, you can alter any of these thresholds.

The new graph simply displays how the hit rate varies over time so that you can spot trends.

As a reminder you can get more information on the original set of alerts and graphs here.

PlanetMySQL Voting: Vote UP / Vote DOWN

In California we are always thinking about backups.   Living near an earthquake fault line makes this necessary.  For me, it is the Hayward Fault (it runs from goal post to goal post  in University of Californa Berkeley stadium).  We are strongly advised to have backup systems for water, food, and medical emergencies.  It’s necessary to monitor your food and water emergency supplies so if the worst happens, you don’t have spoiled food or water as your backup (who knew water expires?) .   Plan for the worst, hope for the best, but keep an eye on those supplies and replenish them when necessary!  And most of all, make sure your good intentions end up as actual physical supplies in the garage!

Backups are also an incredibly critical part of the enterprise  environment.  It’s all about being able to successfully restore your database when needed.  There are many ways to backup your database, but are you monitoring your MySQL backups to make sure they are going to be there when you need them?  In this post, we’ll cover the new features in MySQL Enterprise Monitor (MEM) 2.3.5  that help monitor backups.

In the last post  I covered some of the new features in MySQL Enterprise Backup (MEB) that allow you to write single file backups, stream these backups  to  remote servers or other devices, stream to media management software like Oracle Secure Backup, and take advantage of tape encryption capabilities (Steps 1-4 from the previous post ) . This is the final post  in this series which describes the new Backup Advisor in MEM and the underlying mysql tables (Step 5). 

MySQL Enterprise Monitor (MEM) is the monitoring software supplied with the Enterprise Edition subscription of MySQL  and if you are interested in trying it, you can download it  for a trial at https:edelivery.oracle.com.  I find MEM very helpful because it provides  proactive monitoring for your MySQL databases.  This allows you to increase your productivity because you automate monitoring and help speed up diagnosis of potential issues. 

MEB is a backup tool included with an enterprise subscription from Oracle/MySQL, also available for trial from http://edelivery.oracle.com.  MEB was previously known as InnoDB Hot Backup, and provides hot, non-blocking backups for InnoDB tables, and “warm” backups for MyISAM tables. 

Monitor Your Backup
MEM 2.3.5  (and above) has a new Backup Advisor that can be used to monitor backups (note, MEM 2.3.6 was released in early September).  The Backup Advisor alerts you to backup success or failure, excessive backup lock time, backups that are too old, and failure to use incremental backups.  Here are some screen shots.  The first is  from the Details and Advanced Tabs for the “Backup Succeeded” rule  in MEM:

This rule will let you know how long the backup took to complete and how long locks were held. 

Full backups that are older than a threshold number of days (default is 7) are reported since out of date backups will only cause delays or problems if you ever need to restore from them:

You will also see if any excessive lock time in your backup.  :

Backups are always a balancing act between performance, storage space, and restoral time.  Incremental backups only backup the data that has changed since the last backup, save on storage space, and are faster than a full backup.  I encourage the use of incremental backups in your backup strategy.  MEM will notify you if  incremental backups are not enabled:

 

You can customize any of the thresholds to suit your environment.

Tables Behind the Curtain

MEM uses the backup progress information written into the mysql.backup_progress table, and status information from the mysql.backup_history table.  You can query these tables to get backup status information if you are not using MEM (but you will not receive the alerts and notifications that MEM provides).

Here I’ve queried from the backup_history table, which keeps a hstory of the backups I’ve completed:

The backup_progress table shows the state of the backup as it progresses from start to finish:

MySQL Enterprise Backup and Enterprise Monitor new features bring us one step closer to a true enterprise backup environment. Streaming, integration with media management systems, and the ability to take advantage of tape encryption features coupled with the new Backup Advisor in MEM will help achieve that state we all need to plan for but hope to never see – the ability to quickly restore a database when needed.

PlanetMySQL Voting: Vote UP / Vote DOWN

I’d like to focus this blog on using MySQL in the Enterprise and kickoff with a series of posts on “Enterprise Backup” building on the new features in both MySQL Enterprise Backup (MEB) and MySQL Enterprise Monitor (MEM).  The new features in MEB 3.6 provide the capabilities to stream backups directly to another server, interface with backup media management software, and take advantage of tape encryption.  MEM 2.3.5 now has a Backup Advisor that helps monitor your backups.  In this and a subsequent  blog post, I’ll go through a progression of backups building up on a fairly straightforward vanilla single file backup with MEB as follows:

• Backing up to a Single File
• Add streaming to your Single File backup
• Stream your Single File Backup to a Media Management System
• Encrypt your Backup Tapes using your Media Management System
• Monitoring your Backup with MySQL Enterprise Monitor or queries

MEB is a backup tool included with an enterprise subscription from Oracle/MySQL.  If you are interested in a trying it out, it can be downloaded as a trial from http://edelivery.oracle.com.  MEB was previously known as InnoDB Hot Backup, and provides hot, non-blocking backups for InnoDB tables, and “warm” backups for MyISAM tables.  MEM is also included in the enterprise subscription from Oracle/MySQL and can  be downloaded for a trial at the same site.  It provides proactive monitoring for your MySQL databases.

This post includes the MEB related single file backup, streaming the single file, interfacing with a media management system, and encrypting tapes.  My next post will cover the MEM Backup Advisor and the tables behind the scene.

The following abbreviations are used in this blog:

MEB MySQL Enterprise Backup

MEM MySQL Enterprise Monitor

MMS Media Management System (software for managing tapes and backups)

OSB Oracle Secure Backup

SBT Secure Backup to Tape Interface

Step 1: Backup to a Single File

You can now backup into a single file, which simplifies moving backup data around, especially if you have a large number of files to keep track of.  You do this by using the backup-to-image option of MySQL Enterprise Backup:

mysqlbackup
–backup-image= hr.mbi
–backup-dir=/backup-tmp
–user lynn
–password
backup-to-image

This places my single file backup in the file hr.mbi.  Since some small work files are still used, you need to indicate where these should go with the –backup-dir option.  If you are interested in these files, see the documentation in the “Files that Are Backed Up” section of the MySQL Enterprise Backup User’s Guide http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/meb-files-overview.html .  There are also copies of these files inside your successful single file backup, so you are not required to keep them.  For example, backup_variables.txt contains the start and end log sequence numbers plus information indicating whether this was a compressed, partial, or incremental backup.  The file backup_create.xml lists the command line arguments and the environment that the backup was created in.

You can also convert an existing backup directory to a single file backup as follows:

mysqlbackup
– backup-image=/backup/my.mbi
–backup-dir=/var/mysql/backup
– user lynn
– password
backup-dir-to-image

Use list-image to list the contents of a single file backup (leaving out user and password for simplicity):

mysqlbackup
–backup-image=/backup/my.mbi
list-image

You will see a list of the contents of the single file backup with each database and table listed.  Here’s an example of the contents of a single file backup named MondayApril112022.mbi:

______________________________________________

mysqlbackup –backup_image=Sept2011.mbi list-image

IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful ‘list-image’ run mysqlbackup
prints “mysqlbackup completed OK!”.

mysqlbackup: INFO: Backup Image MEB version string: 3.6.0 [01.07.2011 ]
[File]: [Size:             197]: backup-my.cnf
[File]: [Size:           5578]:  meta\backup_create.xml
[File]: [Size:    16777216]: datadir\ibdata1
[File]: [Size:      2097152]: datadir\ibdata1.$_append_$.1
[Dir]: datadir\crazybase
[File]: [Size:                65]: datadir\crazybase\db.opt
[Dir]: datadir\crazybase3
[File]: [Size:                65]: datadir\crazybase3\db.opt
[Dir]: datadir\mysql
[File]: [Size:                35]: datadir\mysql\backup_history.CSM
[File]: [Size:            5557]: datadir\mysql\backup_history.CSV
[File]: [Size:          71260]: datadir\mysql\backup_history.frm
[File]: [Size:                35]: datadir\mysql\backup_progress.CSM
[File]: [Size:            5423]: datadir\mysql\backup_progress.CSV
[File]: [Size:           33370]: datadir\mysql\backup_progress.frm
[File]: [Size:                 0]: datadir\mysql\columns_priv.MYD
[File]: [Size:             4096]: datadir\mysql\columns_priv.MYI
[File]: [Size:            8820]: datadir\mysql\columns_priv.frm
[File]: [Size:            1320]: datadir\mysql\db.MYD
[File]: [Size:            5120]: datadir\mysql\db.MYI
[File]: [Size:            9582]: datadir\mysql\db.frm
[File]: [Size:                 0]: datadir\mysql\event.MYD
[File]: [Size:            2048]: datadir\mysql\event.MYI
[File]: [Size:          10223]: datadir\mysql\event.frm
[File]: [Size:                 0]: datadir\mysql\func.MYD
[File]: [Size:            1024]: datadir\mysql\func.MYI
[File]: [Size:            8665]: datadir\mysql\func.frm
[File]: [Size:                35]: datadir\mysql\general_log.CSM
[File]: [Size:                 0]: datadir\mysql\general_log.CSV
[File]: [Size:             8776]: datadir\mysql\general_log.frm
[File]: [Size:           22078]: datadir\mysql\help_category.MYD
[File]: [Size:             3072]: datadir\mysql\help_category.MYI
[File]: [Size:             8700]: datadir\mysql\help_category.frm
[File]: [Size:            89241]: datadir\mysql\help_keyword.MYD
[File]: [Size:            16384]: datadir\mysql\help_keyword.MYI
[File]: [Size:              8612]: datadir\mysql\help_keyword.frm
[File]: [Size:              8928]: datadir\mysql\help_relation.MYD
[File]: [Size:            18432]: datadir\mysql\help_relation.MYI
[File]: [Size:              8630]: datadir\mysql\help_relation.frm
[File]: [Size:          418976]: datadir\mysql\help_topic.MYD
[File]: [Size:            20480]: datadir\mysql\help_topic.MYI
[File]: [Size:              8770]: datadir\mysql\help_topic.frm
[File]: [Size:                 0]: datadir\mysql\host.MYD
[File]: [Size:              2048]: datadir\mysql\host.MYI
[File]: [Size:              9510]: datadir\mysql\host.frm
[File]: [Size:                84]: datadir\mysql\inventory.MYD
[File]: [Size:              2048]: datadir\mysql\inventory.MYI
[File]: [Size:              8592]: datadir\mysql\inventory.frm
[File]: [Size:                 0]: datadir\mysql\ndb_binlog_index.MYD
[File]: [Size:              1024]: datadir\mysql\ndb_binlog_index.MYI
[File]: [Size:              8778]: datadir\mysql\ndb_binlog_index.frm
[File]: [Size:                 0]: datadir\mysql\plugin.MYD
[File]: [Size:              1024]: datadir\mysql\plugin.MYI
[File]: [Size:              8586]: datadir\mysql\plugin.frm
[File]: [Size:                 0]: datadir\mysql\proc.MYD
[File]: [Size:              2048]: datadir\mysql\proc.MYI
[File]: [Size:              9996]: datadir\mysql\proc.frm
[File]: [Size:                 0]: datadir\mysql\procs_priv.MYD
[File]: [Size:              4096]: datadir\mysql\procs_priv.MYI
[File]: [Size:              8875]: datadir\mysql\procs_priv.frm
[File]: [Size:               693]: datadir\mysql\proxies_priv.MYD
[File]: [Size:              5120]: datadir\mysql\proxies_priv.MYI
[File]: [Size:              8800]: datadir\mysql\proxies_priv.frm
[File]: [Size:                 0]: datadir\mysql\servers.MYD
[File]: [Size:              1024]: datadir\mysql\servers.MYI
[File]: [Size:              8838]: datadir\mysql\servers.frm
[File]: [Size:                35]: datadir\mysql\slow_log.CSM
[File]: [Size:                 0]: datadir\mysql\slow_log.CSV
[File]: [Size:              8976]: datadir\mysql\slow_log.frm
[File]: [Size:                  0]: datadir\mysql\tables_priv.MYD
[File]: [Size:              4096]: datadir\mysql\tables_priv.MYI
[File]: [Size:              8955]: datadir\mysql\tables_priv.frm
[File]: [Size:                 0]: datadir\mysql\time_zone.MYD
[File]: [Size:              8192]: datadir\mysql\time_zone.MYI
[File]: [Size:              8636]: datadir\mysql\time_zone.frm
[File]: [Size:               312]: datadir\mysql\time_zone_leap_second.MYD
[File]: [Size:              2048]: datadir\mysql\time_zone_leap_second.MYI
[File]: [Size:              8624]: datadir\mysql\time_zone_leap_second.frm
[File]: [Size:          111896]: datadir\mysql\time_zone_name.MYD
[File]: [Size:             12288]: datadir\mysql\time_zone_name.MYI
[File]: [Size:              8606]: datadir\mysql\time_zone_name.frm
[File]: [Size:          658733]: datadir\mysql\time_zone_transition.MYD
[File]: [Size:          733184]: datadir\mysql\time_zone_transition.MYI
[File]: [Size:              8686]: datadir\mysql\time_zone_transition.frm
[File]: [Size:            99788]: datadir\mysql\time_zone_transition_type.MYD
[File]: [Size:            38912]: datadir\mysql\time_zone_transition_type.MYI
[File]: [Size:              8748]: datadir\mysql\time_zone_transition_type.frm
[File]: [Size:               376]: datadir\mysql\user.MYD
[File]: [Size:              2048]: datadir\mysql\user.MYI
[File]: [Size:            10630]: datadir\mysql\user.frm
[Dir]: datadir\performance_schema
[File]: [Size:              8624]: datadir\performance_schema\cond_instances.frm
[File]: [Size:                61]: datadir\performance_schema\db.opt
[File]: [Size:              9220]: datadir\performance_schema\events_waits_current.frm
[File]: [Size:              9220]: datadir\performance_schema\events_waits_history.frm
[File]: [Size:              9220]:datadir\performance_schema\events_waits_history_long.frm
[File)]:[Size:              8878]: datadir\performance_schema\events_waits_summary_by_instance.frm
[File]: [Size:              8854]: datadir\performance_schema\events_waits_summary_by_thread_by_event_name.frm
[File]: [Size:              8814]: datadir\performance_schema\events_waits_summary_global_by_event_name.frm
[File]: [Size:              8654]: datadir\performance_schema\file_instances.frm
[File]: [Size:              8800]: datadir\performance_schema\file_summary_by_event_name.frm
[File]: [Size:              8840]: datadir\performance_schema\file_summary_by_instance.frm
[File]: [Size:              8684]: datadir\performance_schema\mutex_instances.frm
[File]: [Size:             8776]: datadir\performance_schema\performance_timers.frm
[File]: [Size:             8758]: datadir\performance_schema\rwlock_instances.frm
[File]: [Size:             8605]: datadir\performance_schema\setup_consumers.frm
[File]: [Size:             8637]: datadir\performance_schema\setup_instruments.frm
[File]: [Size:             8650]: datadir\performance_schema\setup_timers.frm
[File]: [Size:             8650]: datadir\performance_schema\threads.frm
[Dir]: datadir\pets
[File]: [Size:               65]: datadir\pets\db.opt
[Dir]: datadir\test
[File]: [Size:             8560]: datadir\test\names.frm
[Dir]: datadir\world
[File]: [Size:             8652]: datadir\world\bartstations.frm
[File]: [Size:             8710]: datadir\world\city.frm
[File]: [Size:             8630]: datadir\world\citychild.frm
[File]: [Size:             8646]: datadir\world\citylist2.frm
[File]: [Size:             9172]: datadir\world\country.frm
[File]: [Size:             8702]: datadir\world\countrylanguage.frm
[File]: [Size:             8590]: datadir\world\countrylist2.frm
[File]: [Size:             8590]: datadir\world\countryparent.frm
[File]: [Size:               65]: datadir\world\db.opt
[File]: [Size:              741]: datadir\world\europe_view.frm
[File]: [Size:             3584]: datadir\ibbackup_logfile
[File]: [Size:              176]: meta\backup_variables.txt
[File]: [Size:           38562]: meta\backup_content.xml
[File]: [Size:           15236]: meta\image_files.xml
mysqlbackup: INFO:  Backup image contents listed successfully.
Source Image Path= C:\temp\temp\Sep42011.mbi
mysqlbackup completed OK!

______________________________________________

Step 2:  Add Streaming to your Single File Backup
Streaming allows you to write the backup to a different server without ever storing it locally.  This limits the storage space you need on the local database server, and can be faster than copying it locally and then moving the backup to a different server.  You build on the single file option by using it in combination with OS features like pipes, ssh/scp, etc and take your input from standard output.

mysqlbackup
– backup-image=-backup-to-image | ssh user@host command arg1 arg2 …

where command is the combination of command, device, etc used during normal archiving (such as dd or tar)

Step 3:  Stream your Single File Backup to a Media Management System

You can backup to tape with media management software (MMS) like Oracle Secure Backup (OSB), Legato, Netbackup, etc. The MMS must support version 2 or higher of the System Backup to Tape (SBT)  Interface.   To see a list of Oracle partners who use the SBT interface, go to
http://www.oracle.com/technetwork/database/features/availability/bsp-088814.html
This interface was originally developed by Oracle as a standard way for third party backup media providers to integrate their solutions with RMAN, the Oracle Database Recovery Manager and Backup tool.  MySQL Enterprise Backup (MEB) 3.6 now supports this interface so if you are already using a media management solution like Oracle Secure Backup (OSB) or Netbackup, you can stream directly from MEB to the MMS.  Some MySQL sites have hundreds of servers backed up to many physical or virtual tape devices and can produce thousands of backup tapes on a regular basis.  These tapes may be maintained at different locations for various time periods.  An MMS gives you better control over the process and may add capabilities like backup policies, tape vaulting control, and tape encryption.  To interface with a media management system like OSB, you once again build on the single file backup command, but use a prefix on your image filename.  This special prefix, sbt:  sends the backup to the MMS instead of a local file
–backup-image=sbt:name
Your mysqlbackup command would look something like this:

mysqlbackup
–port=3306
–protocol=tcp
–user=lynn
–password
–backup-image=sbt:backup-hr-2011-09-06
–backup-dir=/backup
backup-to-image

Mysqlbackup defaults to the normal operating system paths and environment variables to locate the Secure Backup to Tape (SBT) library it needs to accomplish this.  What if you have more than one MMS?  No problem. You just specify the –sbt-lib-path parameter of the mysqlbackup command with the correct path and filename for your MMS.

Step 4: Encrypt your Backup Tapes using your Media Management System

Encryption adds an extra layer of security to tapes in case they are misplaced or stolen.  MEB does not in itself do encryption, but you can use OSB or another encryption enabled MMS to encrypt MySQL tapes.  In OSB you control encryption by defining a storage selector that applies specific features (like encryption) to a particular backup. You define the storage selector only once.  Then OSB will automatically select the appropriate database backup storage selector for the backup job.
In OSB a storage selector contains the database name, the hostname, and the name of the media to use for backups.  Optionally you can indicate whether encryption should be used, the type of backup (full, incremental) and restrictions on tape devices.  When OSB receives a backup command through MEB, it examines the defined database backup storage selectors to determine whether a backup storage selector matches the attributes of the just received backup job.  OSB uses the database name and backup type (ie full or incremental etc) to select the most appropriate backup storage selector.
OSB storage selectors are created either through Oracle Enterprise Manager or the command line interface to OSB.  In Oracle Enterprise Manager, storage selectors are defined from within the Maintenance tab under Backup Settings.
If you are not using Enterprise Manager, use the OSB command line interface (obtool) to define your storage selector.  Here is an example of a command which creates a storage selector called MySQLworld with encryption for the database worlddb:

mkssel  (stands for make storage selector)
–dbname worlddb
–host myserver2
–content full
–encryption on
MySQLworld

To restore MySQL data from tape , you use the –sbt-backup-name parameter as part of the restore operation.

A few notes about using MEB with OSB:  You must pre-authorize user access for MySQL for the backup to work with OSB.  For instructions, please see section 4.2.2 of the OSB Administrator’s Guide  “Creating a Preauthorized Oracle Secure Backup User”  http://download.oracle.com/docs/cd/E14812_01/doc/doc.103/e12834/osb_rman_backup.htm#BDCCCIIA

In this entry I’ve covered how to use some of the new features in MySQL Enterprise Backup to stream backups and interface with media management systems.  Next week I will cover the new Backup Advisor in MySQL Enteprise Monitor.

Lynn Ferrante has worked with databases in the enterprise for her whole career at MySQL, Oracle, Sybase, and Ingres.  She also worked on an open source project called GenMapp (University of California, San Francisco), and contributed to the development of many database applications in the fields of energy and environment

PlanetMySQL Voting: Vote UP / Vote DOWN

There will be a live webinar on Wednesday January 12 describing the new ways that you can manage MySQL Cluster (with a bit of monitoring thrown in). As always, the webinar is free but you need to register here. The event is scheduled for 09:00 Pacific / 17:00 UK / 18:00 Central European time but if you can’t make the live webinar it’s still worth registering so that you’re emailed the replay after the event.

By their very nature, clustered environments involve more effort and resource to administer than standalone systems, and the same is true of MySQL Cluster, the database designed for web-scale throughput with carrier-grade availability.

In this webinar, we will present an overview of the three latest enhancements to provisioning, monitoring and managing MySQL Cluster – collectively serving to lower costs, enhance agility and reduce the risk of downtime caused by manual configuration errors.

In this webinar, we will present:

• NDBINFO: released with MySQL Cluster 7.1, NDBINFO presents real-time status and usage statistics, providing developers and DBAs with a simple means of pro-actively monitoring and optimizing database performance and availability.
• MySQL Cluster Manager: available as part of the commercial MySQL Cluster Carrier Grade Edition simplifies the creation and management of MySQL Cluster by automating common management tasks, delivering higher administration productivity and enhancing cluster agility. Tasks that used to take 46 commands can be reduced to just one!
• MySQL Cluster Advisors & Graphs: part of the MySQL Enterprise Monitor and available in the commercial MySQL Cluster Carrier Grade Edition, the Enterprise Advisor includes automated best practice rules that alert on key performance and availability metrics from MySQL Cluster data nodes.

You will also learn how you can get started evaluating and using all of these tools to simplify MySQL Cluster management.

This session will be approximately 1 hour in length and will include interactive Q&A throughout. Please join us for this informative webinar!

WHO:

• Andrew Morgan, MySQL Cluster Product Management, Oracle
• Mat Keep, MySQL Cluster Product Management, Oracle

PlanetMySQL Voting: Vote UP / Vote DOWN

A few months ago, I posted a walkthrough of how to extend MySQL Enterprise Monitor in order to monitor MySQL Cluster. The great news is that as of MySQL Enterprise Monitor 2.3 (available from Oracle E-Delivery since 1st November) this functionality is included in the core product and so there is no need to add the extra features in manually. Of course, that post might still be of interest if you want to further extend MySQL Enterprise Monitor.

This post briefly steps through the new (Cluster-specific) functionality but if you’re interested, why not try it for yourself and download the new MySQL Enterprise Monitor software from Oracle E-Delivery. If you like what you see then the good news is that if you take out a subscription for MySQL Cluster CGE (or buy a license) then this now also entitles you to use MySQL Enterprise Monitor.

There are two main aspects that have been extended to cover MySQL Cluster:

• A new MySQL Cluster Advisor has been added. This Advisor is made up of a set of rules that check various aspects of the data nodes and raise alerts if a configurable threshold is exceeded
• A set of new graphs have been added so that you can monitor the usage of key resources over time.

Note that MySQL Enterprise Monitor has no direct connection to the data nodes and so one or more of the MySQL Servers from the Cluster is effectively used as a proxy. There is nothing special for you to configure on the servers, behind the scenes, Enterprise Monitor is reading the contents of the ndbinfo database that was introduced in MySQL Cluster 7.1.

If using an older version of MySQL Cluster then you get less benefit from MySQL Enterprise Monitor but there is still some value in using it to monitor the MySQL Servers that are part of the cluster:

• Use the Query Analyzer to keep track of how your applications access the database and troubleshoot performance issues
• Monitor the state of the MySQL Server itself (number of client connections, CPU usage etc.)
• Generate alerts when data nodes are out of service.

There is documentation covering installing and running the MySQL Enterprise Monitor service manager and agents and so I won’t repeat the steps here except to point out that you need one or more of the agents to be configured to monitor one or more of the MySQL Servers in your Cluster. Of course, you could monitor multiple MySQL Cluster deployments from the same dashboard – just make sure that you have an agent monitoring at least one MySQL Server from each one.

By default, none of the rules from the MySQL Cluster Advisor are scheduled against any of your servers and so the first thing you need to do is go to the “Advisors” tab and from their select “Add to Schedule”. Select the server(s) on the left and then check the radio button(s) against the whole Cluster advisor or against one or more of the rules within it and click the “schedule” button. You’ll then be given the option to override the default frequency that each rule is run before confirming the activation (scheduling) of the rule(s) for your server(s). This is also the point where you can indicate whether or not an SNMP Trap should be raised when the alert is raised/cleared (the destinations for the SNMP notifications can be set under the “Settings” tab).

Note that if you try scheduling the Cluster Advisor rules against a MySQL Server that is not part of a MySQL Cluster 7.1 (or later) deployment they you will get errors indicating that the server cannot provide the required data.

 

 

 

 

 

 

 

The new MySQL Cluster graphs are activated by default and you can view them from the “Graphs” tab but note that if there are no MySQL Cluster 7.1 servers in the list that you highlight on the left of the browser then the Cluster graphs will be hidden.

 

 

 

 

 

Note that there is still scope for simple customizations directly from the the MySQL Enterprise Monitor GUI. For example if you don’t think that the default thresholds are appropriate for your configuration then select “Manage Rules” within the “Advisors” tab and then click “edit” next to the rule in question – you then get the option to alter the threshold values.

As a final configuration step, go back to the “Monitor” tab and click on “edit favorites” to promote your favourite Cluster graphs to the home screen.

 

Any Critical alerts (including ones for the newly scheduled Cluster rules) will appear on the Monitor page – to see the Info and Warning alerts, select the “Events” tab. Clicking on any of these alerts will give you extra details and the opportunity to close the alert.

PlanetMySQL Voting: Vote UP / Vote DOWN

In the last post, we saw how to create a test OpenLDAP server, populate it and secure it with SSL certificates. Now we are going to have a look at how to configure MySQL Enterprise Manager (MEM) to authenticate against LDAP. We will be examining a few different kinds of setup methods.

1. Using LDAP to fetch just the user password

The simplest form is to configure a user with MEM and set it to the LDAP type. The user’s role is setup in MEM during user creation time and is not fetched from LDAP. Below you can see the user definition page:

The username is user1 as specified in MEM, but where do we get the password from? We need to tell MEM how to find this user. Here is a sample user as defined by the LDIF file used in my previous post:

# user1, People, example.com
dn: uid=user1,ou=People,dc=example,dc=com
objectClass: person
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: top
cn: Aaren Atp
sn: Atp
description: This is the description for Aaren Atp.
employeeNumber: 1
givenName: Aaren
homePhone: +1 280 375 4325
initials: ALA
l: New Haven
mail: user.1@maildomain.net
mobile: +1 680 734 6300
ou: admin
pager: +1 850 883 8888
postalAddress: Aaren Atp$70110 Fourth Street$New Haven, OH  93694
postalCode: 936942
st: OH
street: 70110 Fourth Street
telephoneNumber: +1 390 103 6917
uid: user1
userPassword:: e1NTSEF9Z0tsZjU4cm50Wit4b045N0U4cWlldVJQK1RMOVAzTGw=

Now tell MEM to look for the uid by navigating to Settings > Global Settings > LDAP Authentication:

The password is specified in the userPassword attribute and uses standard LDAP supported formats. Technically what MEM does is an LDAP simple bind operation (Authentication Mode = Bind as User) with the given username/password and then attempts to search for the given user using the DN pattern.  If the binding and search succeeds, we assume the password is correct. With this in place, you can now login with user1 without having to specify the password in MEM.

2. Adding MEM Roles from LDAP

If we want to fetch the user’s role from LDAP in addition to the password, we need to enable the Map LDAP Roles to Application Roles check box in the LDAP settings page. We’ll see the two ways of doing this shortly.

The good thing about enabling role mapping is that manual user creation in the Web dashboard is no longer required. If the user does not already exist within MEM, it will be “copied” from LDAP to MEM when that user first logs in. The lookup order is Built-in users first, then LDAP users. If you want user authentication to be exclusively done against LDAP then make LDAP authoritative by ticking the check box:

WARNING: Enabling this prevents Built-in users from being authenticated. Make sure that you test with non authoritative authentication first and make sure role mapping is working. Also, ensure that you have a valid LDAP user who has admin privileges or risk locking yourself out.

Below is an example of how my test user table looked before logging in with user1:

And this is how it looks after a successful login:

Notice how the LDAP user1 entry was created automatically in the MEM user table with values from the LDAP directory.

2.2.1 MEM roles in a LDAP user’s attribute

The first way of achieving role mapping is to simply have an attribute in the user’s entry, whose attribute value will be the role. In our example, we use the ou attribute. The relevant attributes for our sample users are:

uid: user1
ou: admin
....
uid: user2
ou: dba

The relevant settings in MEM are:

Now when you login with user2 for example, its role will be fetched from LDAP. We can verify this in the User Preferences section as shown by the screenshot below:

If we try user1, then we will get an admin (manager) role.

Note that some versions have a bug that prevents this setup from working. Check if you are affected.

2.2.2 MEM roles specified by a LDAP group

There is another way to specify a user’s role. Instead of each user having an attribute representing the role, a roles group is created, which enumerates the users that are members of that role. In our example, this is represented in the LDIF file as:

dn: ou=groups,dc=example,dc=com
objectClass: organizationalUnit
ou: groups

dn: cn=admin,ou=groups,dc=example,dc=com
objectClass: groupOfUniqueNames
cn: admin
uniqueMember: uid=user1,ou=People,dc=example,dc=com

dn: cn=dba,ou=groups,dc=example,dc=com
objectClass: groupOfUniqueNames
cn: dba
uniqueMember: uid=user2,ou=People,dc=example,dc=com

So again, we need to tell MEM how to do role lookups from the LDAP directory.  This is how the LDAP settings in MEM would look like for our particular case:

Note, that due this bug, you might not get this particular setup to work.

2.3 Configuring LDAP lookups to use SSL and STARTTLS

Once plain text authentication is working, you can choose to secure communications between MEM and the LDAP server with either STARTTLS or SSL. Obviously the LDAP server must support it. Note that SSL (ldaps) is deprecated in the latest LDAP v3 and STARTTLS is prefered. MEM supports both types. To use STARTTLS, simply select it in the menu as follows:

There is no need to change the default port. Using SSL (ldaps) however, implies using the ldaps port, which by default is 636, and setting MEM to use SSL(ldaps) :

One thing to note is that the (Primary) Server Hostname should match the SSL certificate of the LDAP server.

If you are using self signed certificates that were not issued by a commercial SSL company, MEM will not have the root CA (Certificate Authority) in it’s repository. You should see a message in the $INSTALL_DIR/monitor/apache-tomcat/logs/catalina.out logfile:

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed:
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

To fix this, you will need to import the your root CA certificate that was used to generate the LDAP server’s certificate. This can be achieve with the Java Keytool utility as follows:

shell> cd  /opt/mysql/enterprise/monitor/java

shell> bin/keytool -import -trustcacerts -alias ldapssl \
       -file /etc/openldap/ssl/ca-cert.pem -keystore lib/security/cacerts

This needs to be run under MEM’s Java installation directory. Unless you have previously modified it, the default password is changeit . We saw how to create a CA certificate (ca-cert.pem) in the previous post. Finally, restart the MEM monitor service.

In case you have problems importing your root CA certificate, make sure it is correctly format and acceptable by the Java keytool utility:

“keytool can import X.509 v1, v2, and v3 certificates, and PKCS#7 formatted certificate chains consisting of certificates of that type. The data to be imported must be provided either in binary encoding format, or in printable encoding format (also known as Base64 encoding) as defined by the Internet RFC 1421 standard. In the latter case, the encoding must be bounded at the beginning by a string that starts with ‘—–BEGIN’, and bounded at the end by a string that starts with ‘—–END’.”

http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/technotes/tools/windows/keytool.html

To troubleshoot, the best place is to look at the catalina.out logfile. If you need extra debugging information, you can start MEM’s JVM as follows:

JAVA_OPTS="$JAVA_OPTS -Djavax.net.debug=ALL"

In the current version (2.2.1.1721) this should be added at around line 180 of $INSTALL_DIR/monitor/apache-tomcat/bin/catalina.sh. Note that catalina.sh will get overwritten when you next upgrade so if you want JAVA_OPTS changes to be persistent, then use setenv.sh (or setenv.bat for Windows.). A Tomcat restart is needed. Note that a lot of information is logged and you’re log files will grow rather quickly. Please remember to turn this off in regular production mode.

Hopefully this will help when configuring MEM to authenticate it’s users against a LDAP directory.

PlanetMySQL Voting: Vote UP / Vote DOWN