MySQL的安装（centos7）和基础配置

1. 下载

1.1 网址

官方的网址：https://downloads.mysql.com/archives/community/

1.2 上传到虚拟机

yum install -y lszrz
cd /opt
rz -E

2. 安装配置

2.1 解压

cd /opt
tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql

2.2 原始环境的卸载

rpm -qa | grep mariadb

yum remove -y mariadb-libs-5.5.60-1.el7_5.x86_64

2.3 创建mysql用户

useradd mysql -s /sbin/nologin

id mysql

cown -R mysql.mysql /opt/mysql

2.4 添加环境变量

vim /etc/profile
export PATH=/opt/mysql/bin:$PATH

source /etc/profile

2.5 目录的准备

mkdir -p /data/mysql/data

chown -R mysql.mysql /data

2.6 初始化

安装依赖

yum install -y libaio-devel

初始化

mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/mysql/data

2.7 配置文件

cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
EOF

[mysqld]
user=mysql   用户
basedir=/opt/mysql  mysql的安装目录
datadir=/data/mysql/data  存放数据的目录
socket=/tmp/mysql.sock   启动的socket
server_id=6      id
port=3306     端口号
[mysql]
socket=/tmp/mysql.sock

2.8 数据库的启动方式

2.8.1 /ect/init.d/mysqld start

 cp /opt/mysql/support-files/mysql.server  /etc/init.d/mysqld 

启动

/etc/init.d/mysqld start
/etc/init.d/mysqld sop

2.8.2 systenctl start mysqld

cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

启动

systemctl start mysqld

systemctl restart mysqld

4systemctl stop mysqld

2.9 密码的设定

这个时候本地是没有密码的
登录

mysql

设置密码

mysqladmin -uroot -p password 123456

mysql -uroot -p123456

2.10 忘记密码

/etc/init.d/mysqld stop


mysqld_safe --skip-grant-tables --skip-networking

进入MySQL设置密码

alter user root@'localhost' identified by '123456';
flush privileges;


退出重启数据库

3. 数据库授权

grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option;

grant 权限  on 作用目标  to 用户  identified by 密码 with grant option;

with grant option
这个选项表示该用户可以将自己拥有的权限授权给别人

权限

all
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

案列

创建一个管理员用户root，可以通过10网段，管理数据库.
grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;

创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.
grant SELECT,INSERT, UPDATE, DELETE on wordpress.*  to workpress@'10.0.0.%' identified by 'workpress';

回收权限：

show  grants for wordpress@'10.0.0.%';
revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';
show  grants for wordpress@'10.0.0.%';

直接删除用户
drop user olfg@'192.168.2.120';

4. 数据库的连接方式

4.1 tcp ip -h

mysql -uroot -p -h 192.168.246.120 -P3306

4.2 socket -S

mysql -uroot -p -S /tmp/mysql.sock