MySQL Enterpriseは管理機能が非常に充実しております。もちろん、SHOW ENGINE INNODB STATUS, SHOW SLAVE STATUS, information_schemaを使いこなせるエンジニアには管理機能は不要かもしれませんが、管理コマンドを使いこなせない人（いわゆるオペレータ） を多く抱える会社や社内人件費が高い会社にとっては魅力的な機能かと思います。
MySQL Enterpriseはデータベース本体以外に、以下のような管理機能を提供する周辺ソフトウェアが存在します。
MySQL Enterprise Monitor
Oracle Enterprise Manager for MySQL Database
MySQL Enterprise Backup ( 旧名 : Innodb Hot Backup )
例えば以下のようなエラーログの場合は、”InnoDB: Cannot allocate memory for the buffer pool”と出力されているのでメモリ不足を表しています。AWS t1.microを使用する場合は、MySQLのデフォルト設定ではメモリ不足が起きてしまうので、innodb_buffer_pool_size, innodb_log_buffer_sizeをデフォルト値よりも小さくしなければなりません。
[root@ip-172-31-11-1 ~]# cat .mysql_secret
# The random password set for the root user at Sun Jan 11 01:59:46 2015 (local time): aMa0rDzbRapbRC3R
[root@ip-172-31-11-1 ~]#
確認したパスワードを用いてMySQLに接続できる事を確認します。
[root@ip-172-31-11-1 ~]# mysql -uroot -paMa0rDzbRapbRC3R
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-enterprise-commercial-advanced-log
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@ip-172-31-10-3 ~]# /opt/mysql/meb-3.11/bin/mysqlbackup \
> --backup-dir=/tmp/backup/ \
> --datadir=/var/lib/mysql \
> copy-back-and-apply-log
MySQL Enterprise Backup version 3.11.1 Linux-2.6.39-400.17.1.el6uek.x86_64-x86_64 [2014/11/04]
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
/opt/mysql/meb-3.11/bin/mysqlbackup --backup-dir=/tmp/backup/
--datadir=/var/lib/mysql copy-back-and-apply-log
mysqlbackup: INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back-and-apply-log' run mysqlbackup
prints "mysqlbackup completed OK!".
150112 02:09:46 mysqlbackup: INFO: Copying the database directory 'test'
150112 02:09:46 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150112 02:09:47 mysqlbackup: INFO: Apply-log operation has already been done on that backup.
150112 02:09:47 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql
150112 02:09:47 mysqlbackup: INFO: Copy-back operation completed successfully.
mysqlbackup completed OK! with 3 warnings
[root@ip-172-31-10-3 ~]#
MySQL Enterprise Monitor
MySQL Enterprise Monitorの概要
MySQL Enterprise Monitorは、MySQLの性能情報をグラフィカルに見せてくれるツールです。出力内容は”SHOW ENGINE INNODB STATUS”, “SHOW SLAVE STATUS”などと変わりませんが、グラフィカルで直観的な画面になっていますので、MySQLの知識がない人でも何となく状況を理解する事ができます。
MySQL Enterprise Monitorは、Server MonitorとMonitor Agentの2つで構成されます。以下、この2つのコンポーネントのインストール方法について説明します。
MySQL Enterprise Server Monitorのインストール
Server Monitor (Enterprise Monitor との表記揺れあり) をダウンロードし、zipファイルを展開します。すると、以下のようなファイルが作成されます。
[root@ip-172-31-11-1 MysqlEnterpriseMonitor]# ls -l
total 514684
-rwxrwxr-x. 1 ec2-user ec2-user 263485007 Dec 11 12:12 mysqlmonitor-3.0.18.3095-linux-x86_64-installer.bin
-rwxrwxr-x. 1 ec2-user ec2-user 263508040 Dec 11 12:12 mysqlmonitor-3.0.18.3095-linux-x86_64-update-installer.bin
-rw-r-xr--. 1 ec2-user ec2-user 12470 Dec 16 13:10 README_en.txt
-rw-r-xr--. 1 ec2-user ec2-user 12470 Dec 16 13:13 READ_ME_ja.txt
[root@ip-172-31-11-1 MysqlEnterpriseMonitor]#
Please select the installation language
[1] English - English
[2] Japanese - 日本語
Please choose an option [1] : 1
ユーザ名やパスワードを控えられるよう、メモの準備をして下さい。
Info: During the installation process you will be asked to enter usernames and
passwords for various pieces of the Enterprise Monitor. Please be sure to make
note of these in a secure location so you can recover them in case they are
forgotten.
Press [Enter] to continue :
MySQL Enterprise Monitor をインストールするディレクトリを指定してください。
----------------------------------------------------------------------------
Welcome to the setup wizard for the MySQL Enterprise Monitor
----------------------------------------------------------------------------
Please specify the directory where the MySQL Enterprise Monitor will be
installed
Installation directory [/opt/mysql/enterprise/monitor]:
----------------------------------------------------------------------------
Select Requirements
Select Requirements
Please indicate the scope of monitoring this installation will initially encompass so we can configure Tomcat and MySQL memory usage accordingly. The manual contains instructions for updating the configuration later, if needed. This installation will monitor a:
System Size
[1] Small system: No more than 5 to 10 MySQL Servers monitored from a laptop computer or low-end server with no more than 4 GB of RAM
[2] Medium system: Up to 100 MySQL Servers monitored from a medium-size but shared server with 4 GB to 8 GB of RAM
[3] Large system: More than 100 MySQL Servers monitored from a high-end server dedicated to MEM with more than 8 GB RAM
Please choose an option [2] : 1
----------------------------------------------------------------------------
Tomcat Server Options
Please specify the following parameters for the bundled Tomcat Server
Tomcat Server Port [18080]:
Tomcat SSL Port [18443]:
Server Monitorを実行するLinuxユーザを指定します。デフォルトのmysqlmemのままで、エンターを押下するのが良いでしょう。
----------------------------------------------------------------------------
Service Manager User Account
You are installing as root, but it's not good practice for the Service Manager
to run under the root user account. Please specify the name of a user account
to use for the Service Manager below. Note that this user account will be
created for you if it doesn't already exist.
User Account [mysqlmem]:
----------------------------------------------------------------------------
Database Installation
Please select which database configuration you wish to use
[1] I wish to use the bundled MySQL database
[2] I wish to use an existing MySQL database *
Please choose an option [1] : 1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'service_manager'@'localhost' IDENTIFIED BY 'password';
Server Managerがリポジトリ （監視結果格納データベース）に接続可能になるよう、接続情報を入力します。
----------------------------------------------------------------------------
Repository Configuration
Please specify the following parameters for the existing MySQL server
Repository Username [service_manager]:
Password :
Re-enter :
MySQL Hostname or IP address [localhost]:
MySQL Database Port [3306]:
MySQL Database Name [mem]:
Use SSL when connecting to the database [y/N]: N
Yを押下し、インストールを開始します。
----------------------------------------------------------------------------
Setup is now ready to install MySQL Enterprise Monitor on your computer.
Do you want to continue? [Y/n]: Y
インストールが終了するまで待ちます。
----------------------------------------------------------------------------
Please wait while Setup installs MySQL Enterprise Monitor on your computer.
Installing
0% ______________ 50% ______________ 100%
####################################
インストールが完了すると以下のようなメッセージが表示されます。
----------------------------------------------------------------------------
Completed installing files
Setup has completed installing the MySQL Enterprise Monitor files on your
computer (errors were found during the installation process).
Uninstalling the MySQL Enterprise Monitor files can be done by invoking:
/opt/mysql/enterprise/monitor/uninstall
To complete the installation, launch the MySQL Enterprise Monitor UI and
complete the initial setup. Refer to the readme file for additional information
and a list of known issues.
Press [Enter] to continue :
----------------------------------------------------------------------------
Completed installing files
WARNING: To improve security, all communication with the Service Manager uses
SSL. Because only a basic self-signed security certificate is included when the
Service Manager is installed, it is likely that your browser will display a
warning about an untrusted connection. Please either install your own
certificate or add a security exception for the Service Manager URL to your
browser. See the documentation for more information.
Press [Enter] to continue :
----------------------------------------------------------------------------
Setup has finished installing MySQL Enterprise Monitor on your computer.
View Readme File [Y/n]: n
Info: To configure the MySQL Enterprise Monitor please visit the following page:
----------------------------------------------------------------------------
Installation directory
Please specify the directory where MySQL Enterprise Monitor Agent will be
installed
Installation directory [/opt/mysql/enterprise/agent]:
How will the agent connect to the database it is monitoring?
[1] TCP/IP
[2] Socket
Please choose an option [1] : 1
Monitor Agentとデータベース間の接続設定を今行うか、それとも後程、GUI ( Server Manager ) 経由で行うかを選択します。ここは手順書通り、接続設定を今行う”2″を選択します。
----------------------------------------------------------------------------
Monitoring Options
You can configure the Agent to monitor this host (file systems, CPU, RAM, etc.)
and then use the Monitor UI to furnish connection parameters for all current and
future running MySQL Instances. This can be automated or done manually for each
MySQL Instance discovered by the Agent. (Note: scanning for running MySQL
processes is not available on Windows, but you can manually add new connections
and parameters from the Monitor UI as well.)
Visit the following URL for more information:
Monitoring options:
[1] Host only: Configure the Agent to monitor this host and then use the Monitor UI to furnish connection parameters for current and future running MySQL Instances.
[2] Host and database: Configure the Agent to monitor this host and furnish connection parameters for a specific MySQL Instance now. This process may be scripted. Once installed, this Agent will also continuously look for new MySQL Instances to monitor as described above.
Please choose an option [2] : 2
インストールを開始するかどうかを問われます。Yを押下し、インストールを開始して下さい。
----------------------------------------------------------------------------
Setup is now ready to begin installing MySQL Enterprise Monitor Agent on your
computer.
Do you want to continue? [Y/n]: Y
インストールが完了するまで待ちます。
----------------------------------------------------------------------------
Please wait while Setup installs MySQL Enterprise Monitor Agent on your
computer.
Installing
0% ______________ 50% ______________ 100%
##########################
MySQL Enterprise Monitor ( Server Monitor )との接続情報を入力します。”Agent Username”, “Agent Password”は、Server Monitor初回アクセス時に入力を求められたAgent側のユーザ名, パスワードです。
----------------------------------------------------------------------------
MySQL Enterprise Monitor Options
Hostname or IP address []: 172.31.10.1
Tomcat SSL Port [18443]:
The following are the username and password that the Agent will use to connect
to the Monitor. They were defined when you installed the Monitor. They can be
modified under Settings, Manage Users. Their role is defined as "agent".
Agent Username [agent]:
Agent Password :
Re-enter :
mysql> grant all privileges on *.* to 'admin'@'localhost' identified by 'password';
mysql> grant all privileges on *.* to 'general'@'localhost' identified by 'password';
mysql> grant all privileges on *.* to 'limited'@'localhost' identified by 'password';
----------------------------------------------------------------------------
Monitored Database Information
IMPORTANT: The Admin user account specified below requires special MySQL
privileges.
Visit the following URL for more information:
MySQL hostname or IP address [localhost]:
Validate MySQL hostname or IP address [Y/n]: n
MySQL Port [3306]:
Admin User []: admin
Admin Password :
Re-enter Password :
Monitor Group []:
なお、”Auto-Create Less Privileges Users”との説明が出力されますが、自動生成されるユーザは権限不足のため接続できません。上記手順のように、手作業で”GeneralUser”, “Limited User”を作成して下さい。
----------------------------------------------------------------------------
Agent User Account Creation
IMPORTANT: Less privileged accounts can be used to enhance monitoring security,
and can even be created for you if the Admin account has GRANT privileges.
Visit the following URL for more information:
Auto-Create Less Privileged Users [Y/n]:
General Username []: general
General Password :
Re-enter Password :
Limited Username []: limited
Limited Password :
Re-enter Password :
今まで設定した情報のサマリが表示されます。エンターキー押下で設定ファイルが自動生成されます。
----------------------------------------------------------------------------
Configuration Report
MySQL Enterprise Monitor Agent (Version 3.0.18.3095)
The settings you specified are listed below.
Note that if you are using a Connector to collect Query Analyzer data,
you will need some of these settings to configure the Connector. See
the following for more information:
Installation directory: /opt/mysql/enterprise/agent
MySQL Enterprise Monitor UI:
-------------------------
Hostname or IP address: 172.31.10.1
Tomcat Server Port: 18443
Use SSL: yes
Monitored MySQL Database:
-------------------------
Hostname or IP address: localhost
Port: 3306
----------------------------------------------------------------------------
Start MySQL Enterprise Monitor Agent
Info to start the MySQL Enterprise Monitor Agent
The MySQL Enterprise Monitor Agent was successfully installed. To start the
Agent please invoke:
/etc/init.d/mysql-monitor-agent start
Press [Enter] to continue :
----------------------------------------------------------------------------
Setup has finished installing MySQL Enterprise Monitor Agent on your computer.
View Agent Readme File [Y/n]: n
