Centos 7 安装 MYSQL 8.0
获取RPM文件
wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
安装软件源
rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
安装mysql
yum install mysql-community-server
安装过程中如果出现y/n的话就一直y就行了，如果嫌麻烦可以用这个命令来安装
yum install -y mysql-community-server
安装太慢，如何解决(从国内镜像下载对应的rpm文件，通过sFTP上传到服务器，再重新执行)
- 进入到yum缓存目录
cd /var/cache/yum/x86_64/7/mysql80-community/packages
- 访问国内镜像下载对应文件，上传到该目录/var/cache/yum/x86_64/7/mysql80-community/packages
http://uni.mirrors.163.com/mysql/Downloads/MySQL-8.0/
- 再执行yum安装
如果需要让数据库忽略大小写，安装好后千万不要启动服务
然后找到my.cnf，在末行添加 ：lower_case_table_names=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
然后再启动服务，这样lower_case_table_names 就初始化完成了
启动&配置
#启动
service mysqld start
#查看运行状态
service mysqld status
看到绿色的running代表已经启动成功，然后mysql在5.6之后的版本都会默认生成一个默认密码，是root用户的。通过如下命令查看密码
[root@VM-0-4-centos ~]# grep 'temporary password' /var/log/mysqld.log
2020-08-08T14:02:39.588390Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 6pJ<P3/:6o4D
进入MYSQL
执行完如下命令之后输入默认密码
mysql -u root -p
修改root用户密码
alter user 'root'@'localhost' identified by '1qaz@WSX';
查看当前数据库授权情况
use mysql;
select host,user,plugin from user;
查看'root' 用户的权限必须要是'%',这样才能远程登录，如果是localhost，用update语句改成'%'即可(默认'root'用户权限是'localhost')
创建用户和分配权限
由于mysql8新提供了一种密码加密方式caching-sha2-password，且为默认，目前很多客户端都不支持，所以我们在创建新角色用户的时候可以指定其为mysql_native_password，原来的root账户等不去做任何改变
- 创建用户
CREATE USER 'mysql'@'%' IDENTIFIED WITH mysql_native_password BY 'qweAZS@#$1';
- 查看一下用户权限信息
select host,user,plugin from user;
- 使设置立即生效
flush privileges
root身份在命令行登陆，初始化sql文件
CREATE DATABASE `mall` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
use mall;
source /usr/backend/sql/mall.sql;
赋予权限(只赋予数据操作的CRUD权限)
grant select, delete,update,insert on mall.* to mysql@'%';
flush privileges;
赋予权限(赋予所有权限)
grant all privileges on *.* to 'mysql'@'%' identified by 'qweAZS@#$1';
flush privileges;
删除数据库所有表，不删除数据库名字
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='backend';
总结
官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍，还带来了大量的改进和更快的性能！所以我也是第一时间把我的网站的mysql数据库从5.7升级到了8.0版本
