centos7 安装mysql8

1、将mysql安装包上传

mysql-8.0.21-el7-x86_64.tar

2、解压

tar -xvf mysql-8.0.21-el7-x86_64.tar

解压后有3个文件

[root@cdh03 software]# tar -xvf mysql-8.0.21-el7-x86_64.tar 
mysql-test-8.0.21-el7-x86_64.tar.gz
mysql-8.0.21-el7-x86_64.tar.gz
mysql-router-8.0.21-el7-x86_64.tar.gz

3、解压 mysql-8.0.21-el7-x86_64.tar.gz

tar -xzvf mysql-8.0.21-el7-x86_64.tar.gz

4、改名

mv -f mysql-8.0.21-el7-x86_64 mysql80

5、创建MySQL 组

1）创建mysql 组
groupadd mysql

2）创建MySQL用户但该用户不能登陆(-s /bin/false参数指定mysql用户仅拥有所有权，而没有登录权限)
useradd -r -g mysql -s /bin/false mysql

3）把刚创建的mysql用户加入到mysql组下
chown -R mysql:mysql ./

[root@cdh03 software]# groupadd mysql
[root@cdh03 software]# useradd -r -g mysql -s /bin/false mysql
[root@cdh03 software]# chown -R mysql:mysql ./

6、创建mysql 安装初始化配置文件

vi /etc/my.cnf

添加下面这段命令后保存并退出

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/software/mysql80
# 设置mysql数据库的数据的存放目录
datadir=/software/mysql80/mysqldb
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#设置不区分大小写
lower_case_table_names=1
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

7、安装mysql

进入mysql 安装目录下
cd /software/mysql80/bin

安装MySQL，并记住随机命令
 ./mysqld --initialize --console

[root@cdh03 bin]# ./mysqld --initialize --console
2020-08-07T15:54:30.382640Z 0 [System] [MY-013169] [Server] /software/mysql80/bin/mysqld (mysqld 8.0.21) initializing of server in progress as process 2160
2020-08-07T15:54:30.387180Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-08-07T15:54:30.438042Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-07T15:54:31.596423Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-07T15:54:34.300843Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: E3S9al(EFkuy

8、启动MySQL服务

[root@cdh03 mysql80]# cd support-files/
[root@cdh03 support-files]# pwd
/software/mysql80/support-files
[root@cdh03 support-files]# ./mysql.server start
Starting MySQL.Logging to '/software/mysql80/mysqldb/cdh03.err'.
. ERROR! The server quit without updating PID file (/software/mysql80/mysqldb/cdh03.pid).

在mysql安装目录下重新授权后，再次自行启动MySQL命令
[root@cdh03 support-files]# ./mysql.server start
Starting MySQL.Logging to '/software/mysql80/mysqldb/cdh03.err'.
.. SUCCESS! 
[root@cdh03 support-files]# 

9、mysql加入系统进程中

[root@cdh03 support-files]# cp mysql.server /etc/init.d/mysqld

重启MySQL服务

[root@cdh03 support-files]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@cdh03 support-files]#

10、修改随机登陆密码

[root@cdh03 support-files]# cd ..
[root@cdh03 mysql80]# cd bin
[root@cdh03 bin]# ./mysql -u root -p
Enter password:  输入刚刚的密码
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.21

Copyright (c) 2000, 2020, 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> 

11、修改密码为root

alter user  'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

12、设置允许远程登陆

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> update user set user.Host='%'where user.User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

退出

quit

重启

[root@cdh03 bin]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@cdh03 bin]# 

#查看mysql是否区分大小写
mysql> show variables like "%case%" ;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.01 sec)

win下默认值是1，mac是2，linux是0。

0：区分大小写

1：不区分

2：部分区分

删除MySql

注:CDH官方给的有一份推荐的MySQL的配置内容

[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

创建CDH所需要的数据库 

根据所需要安装的服务参照下表创建对应的数据库以及数据库用户，数据库必须使用utf8编码，创建数据库时要记录好用户名及对应密码：

服务名	数据库名	用户名
Cloudera Manager Server	scm	scm
Activity Monitor	amon	amon
Reports Manager	rman	rman
Hue	hue	hue
Hive Metastore Server	metastore	hive
Sentry Server	sentry	sentry
Cloudera Navigator Audit Server	nav	nav
Cloudera Navigator Metadata Server	navms	navms
Oozie	oozie	oozie

 

 创建数据库及对应用户

# scm
CREATE DATABASE scm DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON scm.* TO 'scm'@'%' IDENTIFIED BY 'scm';

# amon
CREATE DATABASE amon DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON amon.* TO 'amon'@'%' IDENTIFIED BY 'amon';

# rman
CREATE DATABASE rman DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON rman.* TO 'rman'@'%' IDENTIFIED BY 'rman';

# hue
CREATE DATABASE hue DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; 
GRANT ALL ON hue.* TO 'hue'@'%' IDENTIFIED BY 'hue';

# hive
CREATE DATABASE metastore DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON metastore.* TO 'hive'@'%' IDENTIFIED BY 'hive';

# sentry
CREATE DATABASE sentry DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;   
GRANT ALL ON sentry.* TO 'sentry'@'%' IDENTIFIED BY 'sentry';

# nav
CREATE DATABASE nav DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;      
GRANT ALL ON nav.* TO 'nav'@'%' IDENTIFIED BY 'nav';

# navms
CREATE DATABASE navms DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON navms.* TO 'navms'@'%' IDENTIFIED BY 'navms';

# oozie
CREATE DATABASE oozie DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON oozie.* TO 'oozie'@'%' IDENTIFIED BY 'oozie';

# flush
FLUSH PRIVILEGES;

 

MySQL 8.0已经不支持下面这种命令写法
grant all privileges on *.* to root@"%" identified by ".";

• all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
• on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user
• to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录
• identified by：指定用户的登录密码

正确的写法是先创建用户
CREATE USER 'root'@'%' IDENTIFIED BY 'Hadoop3!';
再给用户授权
grant all privileges on *.* to 'root'@'%' ;

CREATE USER 'scm'@'%' IDENTIFIED BY 'scm';
grant all privileges on scm.* to 'scm'@'%' ;
CREATE USER 'amon'@'%' IDENTIFIED BY 'amon';
grant all privileges on amon.* to 'amon'@'%' ;
CREATE USER 'rman'@'%' IDENTIFIED BY 'rman';
grant all privileges on rman.* to 'rman'@'%' ;

CREATE USER 'hue'@'%' IDENTIFIED BY 'hue';
grant all privileges on hue.* to 'hue'@'%' ;
CREATE USER 'metastore'@'%' IDENTIFIED BY 'metastore';
grant all privileges on metastore.* to 'metastore'@'%' ;
CREATE USER 'sentry'@'%' IDENTIFIED BY 'sentry';
grant all privileges on sentry.* to 'sentry'@'%' ;

CREATE USER 'nav'@'%' IDENTIFIED BY 'nav';
grant all privileges on nav.* to 'nav'@'%' ;
CREATE USER 'navms'@'%' IDENTIFIED BY 'navms';
grant all privileges on navms.* to 'navms'@'%' ;
CREATE USER 'oozie'@'%' IDENTIFIED BY 'oozie';
grant all privileges on oozie.* to 'oozie'@'%' ;

 

sh scm_prepare_database.sh mysql scm scm scm
sh scm_prepare_database.sh mysql amon amon amon
sh scm_prepare_database.sh mysql rman rman rman
sh scm_prepare_database.sh mysql hue hue hue
sh scm_prepare_database.sh mysql metastore hive hive
sh scm_prepare_database.sh mysql sentry sentry sentry
sh scm_prepare_database.sh mysql nav nav nav
sh scm_prepare_database.sh mysql navms navms navms
sh scm_prepare_database.sh mysql oozie oozie oozie

Tue Aug 11 17:21:46 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ 

requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the 

verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server 

certificate verification.

注：

#创建表
CREATE DATABASE scm DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

#创建用户并赋权
 GRANT ALL ON scm.* TO 'scm'@'%' IDENTIFIED BY 'scm';
报错：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
查看权限规则
SHOW VARIABLES LIKE 'validate_password%';

#查看权限是否正确
SHOW GRANTS FOR 'myscm'@'%';
+------------------------------------------------+
| Grants for myscm@%                             |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'myscm'@'%'              |
| GRANT ALL PRIVILEGES ON `scm`.* TO 'myscm'@'%' |
+------------------------------------------------+


关于 mysql 密码策略相关参数；
1）、validate_password_length  固定密码的总长度；
2）、validate_password_dictionary_file 指定密码验证的文件路径；
3）、validate_password_mixed_case_count  整个密码中至少要包含大/小写字母的总个数；
4）、validate_password_number_count  整个密码中至少要包含阿拉伯数字的个数；
5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；
关于 validate_password_policy 的取值：
0/LOW：只验证长度；
1/MEDIUM：验证长度、数字、大小写、特殊字符；
2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；
6）、validate_password_special_char_count 整个密码中至少要包含特殊字符的个数；

set global validate_password_policy=LOW;
set global validate_password_length=1;

CREATE DATABASE scm DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON scm.* TO 'myscm'@'%' IDENTIFIED BY 'myscm';

CREATE DATABASE metastore DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON metastore.* TO 'metastore'@'%' IDENTIFIED BY 'metastore';

 

完