MySQL8.0 yum 安装及常用配置

# 安装镜像源
yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

# 安装mysql
yum install mysql-community-server

# 配置大小写不敏感
vim /etc/my.cnf
lower_case_table_names=1

# 启动
systemctl start mysqld
systemctl enable mysqld
grep 'temporary password' /var/log/mysqld.log

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword';
FLUSH PRIVILEGES;

# 配置远程访问
update user set host='%' where user='root';
flush privileges;

# mysqldump备份用户授权
create user dumper@'%' identified by 'Dumper@123';
grant select on *.* to dumper@'%';
grant show view on *.* to dumper@'%';
grant lock tables on *.* to dumper@'%';
grant trigger on *.* to dumper@'%';
grant process on *.* to dumper@'%';
grant event on *.* to dumper@'%';
grant reload on *.* to dumper@'%';
grant replication client on *.* to dumper@'%';

# logbin及慢日志配置
show variables like 'log%bin%';
show variables like 'binlog_format';
show variables like '%expire%';
show variables like 'slow_query%';
show variables like 'long_query_time';

# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

server-id = 100
log-bin = /var/lib/mysql/binlog.log
expire-logs-days = 7

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/localhost-slow.log
long_query_time = 1

[mysqldump]
quick
max_allowed_packet = 128M
user=dumper
password=Dumper@123

# 创建用户及数据库
create database safept default character set utf8mb4 collate utf8mb4_unicode_ci;
create user 'safept'@'%' identified by 'Safept@123';
grant all privileges on safept.* to 'safept'@'%' with grant option;
flush privileges;