centos安装MySQL

一、下载并安装MySQL官方的 Yum Repository

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

二、安装mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql57-community-release-el7-10.noarch.rpm

三、安装mysql-community-server

yum -y install mysql-community-server

四、MySQL数据库设置

4.1 启动MySQL

systemctl start  mysqld.service

4.2 查看MySQL运行状态

systemctl status mysqld.service

4.3 查找MySQL初始密码

grep "password" /var/log/mysqld.log

4.4 登录MySQL，需要输入步骤【4.3】中的密码

mysql -uroot -p

4.5 修改MySQL密码：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password123';

4.6 开启允许远程访问

grant all privileges on *.* to 'root'@'%' identified by 'password123' with grant option;

4.7 刷新权限并退出

mysql> flush privileges; 

mysql> exit

4.8 开通防火墙端口并重新载入

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

4.9 修改MySQL配置文件

vim /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/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 leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
lower_case_table_names=1

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

port=3306
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# 设置接收包最大为50M
max_allowed_packet=50M

[mysql]
default-character-set=utf8mb4

[client]
port=3306
default-character-set=utf8mb4

创建/授权用户

//只允许指定ip连接

create user '新用户名'@'localhost' identified by '密码';

//允许所有ip连接（用通配符%表示）

create user '新用户名'@'%' identified by '密码';

 

//设置用户拥有所有权限也就是管理员

grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;

//拥有查询权限

grant select on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;

//其它操作权限说明,select查询 insert插入 delete删除 update修改

//设置用户拥有查询插入的权限

grant select,insert on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;

//取消用户查询的查询权限

REVOKE select ON what FROM '新用户名';

 

删除：DROP USER username@localhost;