Now that we have described the features of MySQL
Enterprise and its components, let’s take a look at an example of how
the tools can truly benefit an organization. In this example we use a
hypothetical—albeit not uncommon—information infrastructure for a
web-based company. This example is representative of the types of
replication models seen in industry. It includes a complex multiple-master
replication setup and replication in a series among database systems that
replicate some of their databases but not others.This scenario, shown in Figure 1, consists of a data center
with two masters set up for high availability and load balancing. These
data centers host the company’s databases, each associated with a product
line. A slave is connected to the main data center for internal production
and day-to-day operations. This server also has slaves for various
departments, including a slave to a third party, providing independent
verification and validation (IVV) services. Connected to the same master
server is another slave for the development departments responsible for
building and enhancing the product lines.
Each of the slaves beneath the data centers can (and typically does)
host additional databases that are not replicated. For example, the
production server typically hosts a human resources database that is not
replicated to most of its slaves (e.g., it is not replicated to the
development center). Similarly, the third-party server hosts its own
results database and the development server has various incarnations of
the product line databases in various states of development.
1. Installation
Installing MySQL Enterprise entails setting up your database servers to run the latest
Pro or Advanced release. You can use your existing MySQL server
installations, but for the best return on your investment, you should be
using the versions provided by your MySQL Enterprise subscription. Once
your database servers are configured and running properly, you can begin
the installation of the Enterprise Monitor and monitoring agents.
You should begin by installing the MEM on a machine on your
network that has connectivity to all of the servers you want to monitor
(we recommend always using MEM to monitor your MySQL servers). During
the installation process, be sure to write down the hostname or IP
address of this server and the username and password you specified for
the agent access. The installation process is very easy and is well
documented on the Enterprise subscription portal.
Note:
There are several user accounts involved in the installation of
MySQL Enterprise. Besides your Enterprise subscription account, you
will also be using a MEM administrator, an agent access account to
your MEM server, and an agent access account for the monitoring agent
running on each MySQL server. Getting these confused can lead to a
failed installation.
Once the MEM is up and running on your monitoring server, you can
begin installing the monitoring agent on each of your MySQL servers.
When you install the monitoring agent, you will need to provide a user
account and password for a connection from the monitoring agent to your
MySQL server. It is best to use the same username and password for all
of your servers, but remember that these are individual accounts, so you
will have to create the account on each server. Grant rights to this
user as follows:
GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS ON *.*
TO 'agent_user'@'localhost' IDENTIFIED BY 'agent_password';
Once you have created the account and granted it privileges to
access the server, start the monitoring agent and observe the MEM. The
server should show up in the MEM within a few moments depending on your
refresh settings.
Note:
The installation process for MEM and the monitoring agent differ
slightly for each platform. Be sure to refer to the documentation for
specific details for your operating system.
Repeat the installation on each of your servers and observe the
results in the Enterprise Dashboard. Figure 2 shows the Enterprise Dashboard for
the example information infrastructure with all monitoring agents
reporting.
Each of the servers is reporting its agent and server status in
the heat chart on the right as green dots (green is good). There are
four charts in the center that depict composite graphs of the query
cache hit ratio, CPU utilization, connection information, and database
activity for all of your servers. Below that is a list of default
critical events reported by the advisors. In this figure, there are
alerts for CPU, I/O, and usage.
As you can surmise, this page presents all of the pertinent
information about your servers so that you can, at a glance, see the
overall health of your information infrastructure. Clearly, monitoring
can’t get any easier than that!
2. Fixing Monitoring Agent Problems
While the installation process for the monitoring agent is very streamlined,
there are times when things can go wrong. If you are careful to supply
the correct information, everything should work correctly.
The following are some basic procedures for diagnosing and
correcting problems in getting the monitoring agent to report to the
MEM:
Check the mysql-monitor-agent.log file if your
monitoring agent starts but the server is not shown on the
Enterprise Dashboard, or if the agent or service status indicates an
error (a red dot) in the heat chart. This file contains a wealth of
information that can help you solve most problems. You can find the
logfiles in the following locations:
Mac OS X
/Applications/mysql/enterprise/agent/
Linux/Unix
/opt/mysql/enterprise/agent on
Linux
Windows
C:\Program
Files\MySQL\Enterprise\Agent
Check the user account and permissions specified for the
monitoring agent on the MySQL server.
Check the user account and password in the agent-instance.ini file. Be sure they are
the same ones used on the MySQL server you are monitoring.
Verify the port and hostname of your local MySQL server. Be
sure the information matches what is in the agent-instance.ini file.
Check the port for the server in the agent-instance.ini file. Be sure you can
log into the local MySQL server using the port, user, and password
specified in this file.
Check the hostname, user, and password for the MEM server in
the mysql-monitor-agent.ini
file. Be sure you can ping the MEM server.
If you are having trouble getting the Query Analyzer to work, also check the proxy port in the
mysql-monitor-agent.ini file and be
sure you can connect a MySQL client to the proxy using the information
specified in that file.
