To use a MySQL database, follow these procedures.
Installing the MySQL Server
- If you already have a MySQL database set up, you can skip to the section Configuring and Starting the MySQL Server to verify that your MySQL configurations meet the requirements for Cloudera Manager.
- It is important that the datadir directory, which, by default, is /var/lib/mysql, is on a partition that has sufficient free space.
- Install the MySQL database.
After issuing the command to install MySQL, you may need to confirm that you want to complete the installation.
OS Command RHEL
$ sudo yum install mysql-server
$ sudo zypper install mysql $ sudo zypper install libmysqlclient_r15Note
:Some SLES systems encounter errors when using the preceding zypper install command. For more information on resolving this issue, see the Novell Knowledgebase topic, error running chkconfig. Ubuntu and Debian
$ sudo apt-get install mysql-server
Configuring and Starting the MySQL Server
- Determine the version of MySQL.
- Stop the MySQL server if it is running.
OS Command RHEL
$ sudo service mysqld stop
SLES, Ubuntu, and Debian
$ sudo service mysql stop
- Move old InnoDB log files /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 out of /var/lib/mysql/ to a backup location.
- Determine the location of the option file, my.cnf.
- Update my.cnf so that
it conforms to the following requirements:
- To prevent deadlocks, set the isolation level to read committed.
- Configure the InnoDB engine. Cloudera
Manager will not start if its tables are configured with the MyISAM
engine. (Typically, tables revert to MyISAM if the InnoDB engine is
misconfigured.) To check which engine your tables are using, run the
following command from the MySQL
mysql> show table status;
- The default settings in the MySQL installations in most distributions use conservative buffer sizes and memory usage. Cloudera Management Service roles need high write throughput because they might insert many records in the database. Cloudera recommends that you set the innodb_flush_method property to O_DIRECT.
- Set the max_connections property according to the size of your
- Small clusters (fewer than 50 hosts) - You
can store more than one database (for example, both the Activity
Monitor and Service Monitor) on the same host. If you do this,
- Put each database on its own storage volume.
- Allow 100 maximum connections for each database and then add 50 extra connections. For example, for two databases, set the maximum connections to 250. If you store five databases on one host (the databases for Cloudera Manager Server, Activity Monitor, Reports Manager, Cloudera Navigator, and Hive Metastore), set the maximum connections to 550.
- Large clusters (more than 50 hosts) - Do not store more than one database on the same host. Use a separate host for each database/host pair. The hosts need not be reserved exclusively for databases, but each database should be on a separate host.
- Small clusters (fewer than 50 hosts) - You can store more than one database (for example, both the Activity Monitor and Service Monitor) on the same host. If you do this, you should:
- Binary logging is not a requirement for Cloudera Manager installations. Binary logging provides benefits such as MySQL replication or point-in-time incremental recovery after database restore. Examples of this configuration follow. For more information, see The Binary Log.
[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 = 32M thread_stack = 256K thread_cache_size = 64 query_cache_limit = 8M query_cache_size = 64M query_cache_type = 1 max_connections = 550 #log_bin should be on a disk with enough free space. Replace '/var/lib/mysql/mysql_binary_log' with an appropriate path for your system. #log_bin=/var/lib/mysql/mysql_binary_log #expire_logs_days = 10 #max_binlog_size = 100M # 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
- If AppArmor is running on the host where MySQL is installed, you might need to configure AppArmor to allow MySQL to write to the binary.
- Ensure the MySQL server starts at boot.
OS Command RHEL
$ 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
$ sudo chkconfig --add mysql
Ubuntu and Debian
$ sudo chkconfig mysql onNote
:chkconfig may not be available on recent Ubuntu releases. You may need to use Upstart to configure MySQL to start automatically when the system boots. For more information, see the Ubuntu documentation or the Upstart Cookbook .
- Start the MySQL server:
OS Command RHEL
$ sudo service mysqld start
SLES, Ubuntu, and Debian
$ sudo service mysql start
- Set the MySQL root password. In the following example, the current
root password is blank.
Press the Enter key when you'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!
Installing the MySQL JDBC Connector
Install the JDBC connector on the Cloudera Manager Server host, as well as hosts to which you assign the Activity Monitor, Reports Manager, Hive Metastore, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server roles.
|RHEL 5 or 6||
$ sudo zypper install mysql-connector-java
|Ubuntu or Debian||
$ sudo apt-get install libmysql-java
Creating Databases for Activity Monitor, Reports Manager, Hive Metastore, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server
- If you are not using the Cloudera Manager installer, the Cloudera Manager Server.
- Cloudera Management Service roles:
- Activity Monitor (if using the MapReduce service)
- Reports Manager
- Each Hive metastore
- Sentry Server
- Cloudera Navigator Audit Server
- Cloudera Navigator Metadata Server
You can create these databases on the host where the Cloudera Manager Server will run, or on any other hosts in the cluster. For performance reasons, you should install each database on the host on which the service runs, as determined by the roles you assign during installation or upgrade. In larger deployments or in cases where database administrators are managing the databases the services use, you can separate databases from services, but use caution.
The database must be configured to support UTF-8 character set encoding.
Record the values you enter for database names, user names, and passwords. The Cloudera Manager installation wizard requires this information to correctly connect to these databases.
- Log into MySQL as the root user:
$ mysql -u root -p Enter password:
- Create databases for the Activity Monitor, Reports Manager, Hive Metastore, Sentry
Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata
mysql> create database database DEFAULT CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec) mysql> grant all on database.* TO 'user'@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)database, user, and password can be any value. The examples match the default names provided in the Cloudera Manager configuration settings:
Role Database User Password Activity Monitor amon amon amon_password Reports Manager rman rman rman_password Hive Metastore Server metastore hive hive_password Sentry Server sentry sentry sentry_password Cloudera Navigator Audit Server nav nav nav_password Cloudera Navigator Metadata Server navms navms navms_password
Backing Up MySQL Databases
$ mysqldump -hhostname -uusername -ppassword database > /tmp/database-backup.sql
$ mysqldump -pamon_password amon > /tmp/amon-backup.sql
$ mysqldump -hmyhost.example.com -uroot -pcloudera amon > /tmp/amon-backup.sql
|<< External PostgreSQL Database||Oracle Database >>|