CentOS 6/7 下 MySQL 5.7 安装部署与配置

#CentOS 7
cd /home/downloads
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
sudo rpm -ivh mysql57-community-release-el7-9.noarch.rpm

#CentOS 6
cd /home/downloads
wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
sudo rpm -ivh mysql57-community-release-el6-9.noarch.rpm

#安装
yum -y install mysql mysql-server

#启动
systemctl start mysqld

#进入mysql-shell
mysql -u root -p

#CentOS7
#开放端口
firewall-cmd --add-port=3306/tcp --permanent

#重新加载防火墙设置
firewall-cmd --reload

#CentOS6
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

iptables save

#1.停用服务:
systemctl stop mysqld

#2. 设置mysql服务配置
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

#3. 开启服务
systemctl start mysqld

#4. 登录（root）
mysql -u root

#5. 更新密码
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
    -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

#6. 停用服务
systemctl stop mysqld

#7. 取消之前的mysql服务设置
systemctl unset-environment MYSQLD_OPTS

#8. 启动服务
systemctl start mysqld

#9. 登录&修改密码
mysql -u root -p
mysql>SET PASSWORD = PASSWORD('New Password');

#新建用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'Test@123456';

#新建数据库
CREATE DATABASE testdb;

#赋予指定账户指定数据库远程访问权限
GRANT ALL PRIVILEGES ON testdb.* TO 'test'@'%';

#赋予指定账户对所有数据库远程访问权限
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

#赋予指定账户对所有数据库本地访问权限
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost';

#刷新权限
FLUSH PRIVILEGES;

#1、查看权限
SHOW GRANTS FOR 'root'@'%';

#2、赋予权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

#3、收回权限
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';

#4、刷新权限
FLUSH PRIVILEGES;

#5、删除用户
DROP USER 'test'@'localhost';

[root@centos7 download]# whereis my.cnf
my: /etc/my.cnf

#修改配置文件
vi /etc/my.cnf

#修改1：增加client配置（文件开头）
[client]
default-character-set=utf8mb4

#修改2：增加mysqld配置（文件结尾）
#charset
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

#重启后配置即可生效
systemctl restart mysqld