linux yum部署mysql8.0并添加操作用户

养成良好的记录习惯

时间：2019年10月12日
作者：黄黄
邮箱：15797683468@163.com(可指出问题相互交流)

1.安装MySQL的yum源

yum localinstall https://dev.mysql.com/get/
mysql80-community-release-el7-1.noarch.rpm

2.安装MySQL

yum install mysql-community-server

3.进行mysql8.0一些小细节的设置

设置是否区分表名大小写

mysql8.0以后都是默认区分大小写，如果修改需要添加对应的设置,（注意要在mysql8.0一开始没做任何设置的时候进行配置，不然启动mysql有可能会失败。）linux默认mysql的配置文件地址是/etc/my.cnf

vi /etc/my.cnf

加入lower_case_table_names=1（0是区分大小写，1是不区分大小写，一般是业务上是不进行区分的）

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

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

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1

4.启动MySQL服务并设置开机自启

systemctl start mysqld.service 
systemctl enable mysqld.service

5.获取临时密码

grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1;

输出如下：

2018-09-01T17:13:30.385800Z 5 [Note] [MY-010454] [Server] A 
temporary password is generated for root@localhost: NqLQAN?(m1=Q

NqLQAN?(m1=Q为你的临时密码

6.更改密码

先设置密码策略，不设置不能修改为简单的密码先登录mysql，mysql8.0密码校验比较严苛
####登陆MySQL

Mysql -u root  -p NqLQAN?(m1=Q;

设置策略

set global validate_password.policy=0;
set global validate_password.length=6;

更改密码

还提供使用安全安装脚本修改密码，有需要可以自行研究

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘新密码’

7. 添加远程账户

MySQL 8.0的安全策略比之前的版本要严格很多，如果想通过远程访问不能直接改my.conf了，需要到MySQL中去设置
####登陆MySQL

mysql -u root -p

####创建远程用户
######创建db(数据库)

CREATE DATABASE test;

添加用户和密码到监听ip，注意这里的ip应该是本机与外界通信的物理ip,%是指所有ip相对来讲不够安全，安全考虑多的慎用

CREATE USER 'zhangzhiwen'@'%' IDENTIFIED 
WITH mysql_native_password BY '113536';

为用户设置权限

授予所有数据库所有的权限

GRANT ALL PRIVILEGES ON *.* TO 'zhangzhiwen'@'%';

授予单个数据库(test数据库)所有的权限

grant all privileges on test.* to 'zhangzhiwen'@'%';

授予对应的操作权限
常见的权限有select,insert,update,delete,create,drop，分别是查、增、改、删、创建、清空，前四种是对表数据的操作，后两种是对表的操作。可根据自己的需要进行权限授予。举例：

grant select,insert on test to 'zhangzhiwen'@'%';

刷新策略

FLUSH PRIVILEGES;