cdh mysql_CDH 依赖的MySQL 数据库安装配置说明

1 背景说明

根据CDH 官网文档的说明，CDH的如下组件必须有数据库的支持才能运行: Cloudera Manager Server, Oozie Server, Sqoop Server, Activity Monitor, Reports Manager, Hive Metastore Server, Hue Server, Sentry Server, Cloudera Navigator Audit Server, 和 Cloudera Navigator Metadata Server.

https://www.cndba.cn/dave/article/3374

https://www.cndba.cn/dave/article/3374

组件

数据说明及大小

Cloudera Manager Server

Contains all the information about services you have configured and their role assignments, all configuration history, commands, users, and running processes. This relatively small database (< 100 MB) is the most important to back up.

该数据非常重要，当这部分配置数据不能访问时，CM也将无法启动。建议定期对该数据进行备份。

Oozie Server

Contains Oozie workflow, coordinator, and bundle data. Can grow very large.

Sqoop Server

Contains entities such as the connector, driver, links and jobs. Relatively small.

Activity Monitor

Contains information about past activities. In large clusters, this database can grow large. Configuring an Activity Monitor database is only necessary if a MapReduce service is deployed.

Reports Manager

Tracks disk utilization and processing activities over time. Medium-sized.

Hive Metastore Server

Contains Hive metadata. Relatively small.

Hue Server

Contains user account information, job submissions, and Hive queries. Relatively small.

Sentry Server

Contains authorization metadata. Relatively small.

Cloudera Navigator Audit Server

Contains auditing information. In large clusters, this database can grow large.

Cloudera Navigator Metadata Server

Contains authorization, policies, and audit report metadata. Relatively small.

CDH 支持MariaDB，MySQL，PostgreSQL和Oracle 数据库来存储这些组件的数据。 我们这里仅看MySQL数据库的安装配置。https://www.cndba.cn/dave/article/3374https://www.cndba.cn/dave/article/3374

2 MySQL 安装配置

2.1 注意事项

CDH对MySQL有一些限制，具体如下：https://www.cndba.cn/dave/article/3374

1) 对于MySQL 5.6 以后的版本，Cloudera Manager Agent包的安装依赖MySQL-shared-compat 或MySQL-shared包，所以在安装数据库时必须安装这2个MySQL包。

2) MySQL默认的datadir 目录是/var/lib/mysql,要确保该目录存在的分区有足够的空间。

3) 如果MySQL 启用了GTID复制，会导致Cloudera Manager安装失败。

4) 对于5.6.6以后版本的MySQL，在安装或升级Cloudera Navigator时，必须确保MySQL 服务的系统变量：explicit_defaults_for_timestamp被禁用(设置为0)，否则会导致操作失败。https://www.cndba.cn/dave/article/3374https://www.cndba.cn/dave/article/3374

2.2 安装MySQL

从官网下载Mysql文件，注意这里要下载5.6. 因为5.7 的安装文件里没有MySQL-shared包。https://www.cndba.cn/dave/article/3374

[root@www.cndba.cn dave]# ls

MySQL-5.6.44-1.el6.x86_64.rpm-bundle.tar

[root@www.cndba.cn dave]# tar xvf MySQL-5.6.44-1.el6.x86_64.rpm-bundle.tar

MySQL-test-5.6.44-1.el6.x86_64.rpm

MySQL-shared-5.6.44-1.el6.x86_64.rpm

MySQL-devel-5.6.44-1.el6.x86_64.rpm

MySQL-shared-compat-5.6.44-1.el6.x86_64.rpm

MySQL-client-5.6.44-1.el6.x86_64.rpm

MySQL-embedded-5.6.44-1.el6.x86_64.rpm

MySQL-server-5.6.44-1.el6.x86_64.rpm

[root@www.cndba.cn dave]# ls

MySQL-5.6.44-1.el6.x86_64.rpm-bundle.tar MySQL-embedded-5.6.44-1.el6.x86_64.rpm MySQL-shared-compat-5.6.44-1.el6.x86_64.rpm

MySQL-client-5.6.44-1.el6.x86_64.rpm MySQL-server-5.6.44-1.el6.x86_64.rpm MySQL-test-5.6.44-1.el6.x86_64.rpm

MySQL-devel-5.6.44-1.el6.x86_64.rpm MySQL-shared-5.6.44-1.el6.x86_64.rpm

[root@www.cndba.cn dave]#

安装MySQL-shared包：https://www.cndba.cn/dave/article/3374

[root@www.cndba.cn dave]# rpm -ivh MySQL-shared-compat-5.6.44-1.el6.x86_64.rpm

warning: MySQL-shared-compat-5.6.44-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:MySQL-shared-compat ########################################### [100%]

[root@www.cndba.cn dave]# rpm -ivh MySQL-shared-5.6.44-1.el6.x86_64.rpm

warning: MySQL-shared-5.6.44-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:MySQL-shared ########################################### [100%]

[root@www.cndba.cn dave]#

然后卸载系统自带的mysql-libs包，注意这里必选先安装mysql-shared包，否则这里依赖关系会有问题：

[root@www.cndba.cn dave]# rpm -qa|grep mysql

mysql-libs-5.1.73-8.el6_8.x86_64

[root@www.cndba.cn dave]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64

[root@www.cndba.cn dave]#

安装MySQL server 和Client：

[root@www.cndba.cn dave]# rpm -ivh MySQL-server-5.6.44-1.el6.x86_64.rpm

warning: MySQL-server-5.6.44-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:MySQL-server ########################################### [100%]

[root@www.cndba.cn dave]# rpm -ivh MySQL-client-5.6.44-1.el6.x86_64.rpm

warning: MySQL-client-5.6.44-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:MySQL-client ########################################### [100%]

[root@www.cndba.cn dave]# rpm -ivh MySQL-devel-5.6.44-1.el6.x86_64.rpm

warning: MySQL-devel-5.6.44-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ########################################### [100%]

1:MySQL-devel ########################################### [100%]

[root@www.cndba.cn dave]#

安装包会配置MySQL为开机自启动，无需在单独配置：

[root@www.cndba.cn dave]# chkconfig mysql --list

mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off

```shell

####2.3 初始化MySQL

```shell

[root@www.cndba.cn dave]# /usr/bin/mysql_install_db

Installing MySQL system tables...2019-04-29 22:56:46 0 [Warning] Using unique option prefix character_set_client instead of character-set-client-handshake is deprecated and will be removed in a future release. Please use the full name instead.

2019-04-29 22:56:46 0 [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'utf8'

2019-04-29 22:56:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-04-29 22:56:46 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2019-04-29 22:56:46 0 [Note] /usr/sbin/mysqld (mysqld 5.6.44-log) starting as process 31134 ...

2019-04-29 22:56:46 31134 [Note] InnoDB: Using atomics to ref count buffer pool pages

2019-04-29 22:56:46 31134 [Note] InnoDB: The InnoDB memory heap is disabled

2019-04-29 22:56:46 31134 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

……

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h hadoop-cm password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

[root@www.cndba.cn dave]#

[root@www.cndba.cn dave]# service mysql start

Starting MySQL... [ OK ]

[root@www.cndba.cn dave]#

2.4 配置MySQL配置文件

复制默认的参数文件：

[root@www.cndba.cn dave]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf

CDH 官方推荐的默认参数配置如下：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

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_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

#expire_logs_days = 10

#max_binlog_size = 100M

#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 and chown the specified folder to the mysql user.

log_bin=/var/lib/mysql/mysql_binary_log

#In later versions of MySQL, if you enable the binary log and do not set

#a server_id, MySQL will not start. The server_id must be unique within

#the replicating group.

server_id=1

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

sql_mode=STRICT_ALL_TABLES

2.5 允许远程登录

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select host,user,password from user;

+-----------+------+-------------------------------------------+

| host | user | password |

+-----------+------+-------------------------------------------+

| localhost | root | *C7A876F676F26A9CE1E5F3652B9FBB743449BA99 |

| hadoop-cm | root | *C7A876F676F26A9CE1E5F3652B9FBB743449BA99 |

| 127.0.0.1 | root | *C7A876F676F26A9CE1E5F3652B9FBB743449BA99 |

| ::1 | root | *C7A876F676F26A9CE1E5F3652B9FBB743449BA99 |

+-----------+------+-------------------------------------------+

4 rows in set (0.00 sec)

mysql> update user set password=password('123456') where user='root';

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

mysql> update user set host='%' where user='root' and host='localhost';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3 创建CDH 数据库及用户

在CDH 中，需要创建9个数据库和对应的用户，具体如下表：

创建语法如下：

create database amon default character set utf8 default collate utf8_general_ci;

grant all on amon.* to 'amon'@'%' identified by 'amon';

create database rman default character set utf8 default collate utf8_general_ci;

grant all on rman.* to 'rman'@'%' identified by 'rman';

create database hue default character set utf8 default collate utf8_general_ci;

grant all on hue.* to 'hue'@'%' identified by 'hue';

create database hive default character set utf8 default collate utf8_general_ci;

grant all on hive.* to 'hive'@'%' identified by 'hive';

create database sentry default character set utf8 default collate utf8_general_ci;

grant all on sentry.* to 'sentry'@'%' identified by 'sentry';

create database nav default character set utf8 default collate utf8_general_ci;

grant all on nav.* to 'nav'@'%' identified by 'nav';

create database navms default character set utf8 default collate utf8_general_ci;

grant all on navms.* to 'navms'@'%' identified by 'navms';

create database oozie default character set utf8 default collate utf8_general_ci;

grant all on oozie.* to 'oozie'@'%' identified by 'oozie';

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| amon |

| hive |

| hue |

| mysql |

| nav |

| navms |

| oozie |

| performance_schema |

| rman |

| scm |

| sentry |

| test |

+--------------------+

13 rows in set (0.00 sec)

注意scm的数据库不需要创建，在CDH 数据库初始化时会自动创建。

版权声明：本文为博主原创文章，未经博主允许不得转载。