Installing and Configuring a MySQL Database_your database must be configured to either use utf-优快云博客

转载自:

http://www.cloudera.com/documentation/archive/manager/4-x/4-5-1/Cloudera-Manager-Enterprise-Edition-Installation-Guide/cmeeig_topic_5_5.html

You can use MySQL databases to store information for Cloudera Manager. Cloudera Managerrequires databases to store information, so you may need to create databases for each ofthose entities. See your MySQL documentation for more information about installation andconfiguration

Configuring MySQL for the Cloudera Manager Databases

The default settings in the MySQL installations in most distributions are veryconservative with regards to buffer sizes and memory usage. In addition, ClouderaManager requires InnoDB. Cloudera Managment services need high write throughput as,based on cluster size, they may insert a lot of records in the database. ThereforeCloudera recommends that you set innodb_flush_method toO_DIRECT.

For the databases used by Cloudera Manager, Cloudera recommends that you update/etc/my.cnf or /etc/mysql/my.cnf to at leastthe values shown below. It is important that the datadir directory,which, by default, is /var/lib/mysql, is on a partition that hasplentiful free space.

Recommended Settings

Determine the version of MySQL.

Important
- For a fresh MySQL installation on Red Hat or SLES systems, applythe settings in the next step before you start MySQL for thefirst time.
- For an existing installation and a new installation on Ubuntu,you must perform the steps in Reconfiguring InnoDB Settings for an Existing MySQLInstallationbefore changing InnoDBsettings.

Recommended MySQL configurations settings are as follows. You shouldincorporate these changes as appropriate into your configuration settings.

[mysqld]
transaction-isolation=READ-COMMITTED
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

key_buffer              = 16M
key_buffer_size         = 32M
max_allowed_packet      = 16M
thread_stack            = 256K
thread_cache_size       = 64
query_cache_limit       = 8M
query_cache_size        = 64M
query_cache_type        = 1
# Important: see Configuring the Databases and Setting max_connections
max_connections         = 550

# log-bin should be on a disk with enough free space
log-bin=/x/home/mysql/logs/binary/mysql_binary_log

# For MySQL version 5.1.8 or later. Comment out binlog_format for older versions.
binlog_format           = mixed

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M

# InnoDB settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit  = 2
innodb_log_buffer_size          = 64M
innodb_buffer_pool_size         = 4G
innodb_thread_concurrency       = 8
innodb_flush_method             = O_DIRECT
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Configuring the Databases and Setting max_connections

The definition of a small or large cluster is not absolute, so this informationis intended as general guidance. For the purposes of this discussion, clusterswith fewer than 50 hosts can be considered small clusters and clusters with morethan 50 hosts can be considered large clusters.

Follow these guidelines:

In a small cluster, you can store more than one database (for example, boththe Activity Monitor and Service Monitor) on the same host. If you do this,you should:
Put each database on its own storage volume.
Allow 100 maximum connections for each database and then add 50 extraconnections. For example, for two databases set the maximum connections to250. If you store seven databases on one host (the databases for ActivityMonitor, Service Monitor, Report Manager, Host Monitor, Cloudera ManagerServer, Cloudera Navigator, and Hive Metastore), set the maximum connectionsto 750.
In a large cluster, do not store more than one database on the same host. Insuch a case, use a separate host for each database/host pair. The hosts neednot be reserved exclusively for databases, but each database should be on aseparate host.

Reconfiguring InnoDB Settings for an Existing MySQL Installation

To update InnoDB settings on all hosts that are using an existing MySQLinstallation, proceed as follows.

Stop MySQL.

On Red Hat systems
```
$ sudo service mysqld stop
```
On SLES and Debian/Ubuntu systems
```
$ sudo service mysql stop
```
Edit the InnoDB entries in /etc/my.cnf as shown in theprevious section.
Move the old InnoDB log files to a backup location. The two files to moveare /var/lib/mysql/ib_logfile0 and/var/lib/mysql/ib_logfile1. Make sure you move thesefiles out of the /var/lib/mysql/ directory (don't copy themand leave the originals in place).
Start MySQL.
On Red Hat systems
```
$ sudo service mysqld start
```
On SLES and Debian/Ubuntu systems
```
$ sudo service mysql start
```

Installing the MySQL JDBC Connector

Install the JDBC connector on the Cloudera Manager Server host, as well as hosts towhich you assign the Activity Monitor, Service Monitor, Report Manager, HostMonitor, Cloudera Navigator, and Hive Metastore Server roles.

Cloudera recommends that you assign all roles that require databases on the samehost and install the connector on that host. While putting all such roles on thesame host is recommended, it is not required. You could install a role, such asActivity Monitor on one host and other roles on a separate host. In such a case youwould install the JDBC connector on each host running roles that access thedatabase.

On Red Hat 6 systems:

$ sudo yum install mysql-connector-java

On Red Hat 5 systems:

Download the MySQL JDBC connector from http://www.mysql.com/downloads/connector/j/5.1.html.
Extract the JDBC driver JAR file from the downloaded file; for example:
```
tar zxvf mysql-connector-java-5.1.18.tar.gz
```

Add the JDBC driver, renamed, to the relevant server; for example:

$ sudo cp mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar /usr/share/java/mysql-connector-java.jar

If the target directory does not yet exist on this host, you can create itbefore copying the .jar file; for example:

$ sudo mkdir -p /usr/share/java/
$ sudo cp mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar /usr/share/java/mysql-connector-java.jar

On SLES systems:

$ sudo zypper install mysql-connector-java

On Debian/Ubuntu systems:

$ sudo apt-get install libmysql-java

Configuring MySQL

Configure MySQL to use a strong password and to start at boot.

Set the MySQL root password. Note that in the following procedure, your currentroot password is blank. Press the Enter key whenyou're prompted for the root password.

$ sudo /usr/bin/mysql_secure_installation
[...]
Enter current password for root (enter for none):
OK, successfully used password, moving on...
[...]
Set root password? [Y/n] y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
[...]
Disallow root login remotely? [Y/n] N
[...]
Remove test database and access to it [Y/n] Y
[...]
Reload privilege tables now? [Y/n] Y
All done!

Ensure the MySQL server starts at boot.

On Red Hat systems:

$ sudo /sbin/chkconfig mysqld on
$ sudo /sbin/chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

On SLES systems:

$ sudo chkconfig --add mysql

On Debian/Ubuntu systems:

$ sudo chkconfig mysql on

Creating the MySQL Databases for Cloudera Manager

The next step involves creating databases and user accounts for alldatabase-backed services in Cloudera Manager.

Create databases for each of the following features that are part of theCloudera Management Services:

Activity Monitor
Service Monitor
Report Manager
Host Monitor
Cloudera Navigator

In addition add a database for each Hive service.

You can create these databases on the host where the Cloudera Manager Serverwill run, or on any other nodes in the cluster. For performance reasons, youshould typically install each database on the host on which the service runs, asdetermined by the roles you will assign during installation or upgrade. Inlarger deployments or in cases where database administrators (DBAs) are managingthe databases the services will use, databases may be separated from services,but do not undertake such an implementation lightly.

Note the values you enter for database names, user names, and passwords. TheCloudera Manager installation wizard requires this information to correctlyconnect to these databases.

The database must be configured to support UTF-8 character set encoding. Thesample commands below include the required options to enable UTF-8 support.

To create the MySQL databases for Cloudera Manager:

Log into MySQL as the root user:
```
$ mysql -u root -p
Enter password:
```

Create a database for the Activity Monitor. The database name, user name,and password can be anything you want. For example:

mysql> create database amon DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on amon.* TO 'amon'@'%' IDENTIFIED BY 'amon_password';
Query OK, 0 rows affected (0.00 sec)

Create a database for the Service Monitor. The database name, user name, andpassword can be anything you want. For example:

mysql> create database smon DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on smon.* TO 'smon'@'%' IDENTIFIED BY 'smon_password';
Query OK, 0 rows affected (0.00 sec)

Create a database for the Report Manager. The database name, user name, andpassword can be anything you want. For example:

mysql> create database rman DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on rman.* TO 'rman'@'%' IDENTIFIED BY 'rman_password';
Query OK, 0 rows affected (0.00 sec)

Create a database for the Host Monitor. The database name, user name, andpassword can be anything you want. For example:

mysql> create database hmon DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on hmon.* TO 'hmon'@'%' IDENTIFIED BY 'hmon_password';
Query OK, 0 rows affected (0.00 sec)

Create a database for the Hive metastore. The database name, user name, andpassword can be anything you want. For example:

mysql> create database hive DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on hive.* TO 'hive'@'%' IDENTIFIED BY 'hive_password';
Query OK, 0 rows affected (0.00 sec)

Create a database for Cloudera Navigator. The database name, user name, andpassword can be anything you want. For example:

mysql> create database nav DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on nav.* TO 'nav'@'%' IDENTIFIED BY 'nav_password';
Query OK, 0 rows affected (0.00 sec)

Backing Up the MySQL Databases

To back up the MySQL database, run the mysqldump command on theMySQL host, as follows:

$ mysqldump -h<hostname> -u<username> -p<password> <database> > /tmp/<database-backup>.sql

For example, to back up database scm_database on the local hostas the root user, with the password mypasswd:

$ mysqldump -pmypasswd scm_database > /tmp/scm_database-backup.sql

To back up database scm_database on remote hostmyhost.example.com as the root user, with the passwordmypasswd:

$ mysqldump -hmyhost.example.com -uroot -pcloudera scm_database > /tmp/scm_database-backup.sql