mysql5.7 权限管理_mysql5.7 安装和权限管理

本次测试用的安装包 ： mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

卸载原来的mariadb

[root@localhost mysql]# rpm -qa |grep mariadb

mariadb-libs-5.5.65-1.el7.x86_64

mariadb-5.5.65-1.el7.x86_64

mariadb-server-5.5.65-1.el7.x86_64

[root@localhost mysql]# yum remove mariadb-libs-5.5.65-1.el7.x86_64

解压安装包,移动并重命名

tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.26-linux-glibc2.12-x86_64 /opt/mysql

通常在生产环境下，mysql的数据文件会单独存放在另一块磁盘，这边不做测试。

添加用户，如果之前有，可以先删除再添加。

useradd -s /sbin/nologin mysql

修改环境变量

vi /etc/profile

#添加下行

export PATH=/opt/mysql/bin:$PATH

source /etc/profile

目录授权,先自己创建

[root@localhost data]# chown -R mysql:mysql /data/mysql/data

[root@localhost data]# chown -R mysql:mysql /opt/mysql/

数据初始化

[root@localhost data]# mysqld --initialize --user=mysql --basedir=/opt/mysql/ --datadir=/data/mysql/data/

如果初始化失败，有报错如下：

image.png

则安装

yum install -y libaio-devel

image.png

--initialize参数说明：

1,对于密码复杂度进行定制：12位，4种

2，密码过期时间为180天

3给root@localhost设置临时密码

--initialize-insecure参数说明：

无限制，无临时密码

这边重新采用无限制的方式初始化数据库，先把之前的删除

[root@localhost data]# rm -rf /data/mysql/data/*

[root@localhost data]# mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql/ --datadir=/data/mysql/data/

2020-06-04T06:40:53.100085Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2020-06-04T06:40:54.145999Z 0 [Warning] InnoDB: New log files created, LSN=45790

2020-06-04T06:40:54.382353Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2020-06-04T06:40:54.444639Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 56dff5e6-a62e-11ea-9db6-000c29ceb265.

2020-06-04T06:40:54.446327Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2020-06-04T06:40:54.447955Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

配置文件准备

echo '''

[mysqld]

user=mysql

basedir=/opt/mysql

datadir=/data/mysql/data

socket=/tmp/mysql.sock

server_id=6

port=3306

[mysql]

socket=/tmp/mysql.sock

''' >>/etc/my.cnf

启动方式1

[root@localhost support-files]# cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@localhost support-files]# service mysqld start

Starting MySQL.Logging to '/data/mysql/data/localhost.err'.

. SUCCESS!

启动方式2

echo '''

[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

''' > /etc/systemd/system/mysqld.service

systemctl start mysqld.service

/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf 上面代码里这行才是启动的命令，也可以直接执行这行命令，但会直接输出日志。

修改root密码

[root@localhost system]# mysqladmin -uroot -p password 123456

数据库操作

mysql> select user,host,authentication_string from user;

+---------------+-----------+-------------------------------------------+

| user | host | authentication_string |

+---------------+-----------+-------------------------------------------+

| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

+---------------+-----------+-------------------------------------------+

登录数据库后修改密码

mysql> alter user root@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

查看链接数

mysql> show full processlist;

+----+------+------------------+------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+------------------+------+---------+------+----------+------------------+

| 9 | root | 10.0.2.185:58223 | NULL | Sleep | 530 | | NULL |

| 10 | root | 10.0.2.185:58224 | NULL | Sleep | 530 | | NULL |

| 11 | root | localhost | NULL | Sleep | 17 | | NULL |

| 12 | root | localhost | NULL | Query | 0 | starting | show processlist |

+----+------+------------------+------+---------+------+----------+------------------+

4 rows in set (0.00 sec)

数据库结构

数据库里所有的库在data下都有对应的目录

MyISAM(ext2)

user.frm:存储表结构(列，列属性)

user.MYD:存储的数据记录

user.MYI:存储索引

InnoDB(XFS)

time_zone.frm:存储表结构(列，列属性)

time_zone.ibd:存储的数据记录和索引

ibdata1 : 数据字典信息

用户管理和权限管理

允许2网段访问

mysql> create user zhiuan@'10.0.2.%' identified by '123456'

-> ;

Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;

+---------------+-----------+

| user | host |

+---------------+-----------+

| root | % |

| zhiuan | 10.0.2.% |

| mysql.session | localhost |

| mysql.sys | localhost |

+---------------+-----------+

4 rows in set (0.00 sec)

#修改用户密码

mysql> alter user zhiuan@'10.0.2.%' identified by '11111';

Query OK, 0 rows affected (0.00 sec)

#权限管理

#授权所有 库所有表*.*

mysql> grant all on *.* to zhiuan@'10.0.2.%' identified by '11111';

Query OK, 0 rows affected, 1 warning (0.00 sec)

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

grant SELECT,INSERT on kuming.* to zhiuan@'10.0.2.%' identified by '11111';

#查看权限

mysql> show grants for zhiuan@'10.0.2.%'

-> ;

+----------------------------------------------------+

| Grants for zhiuan@10.0.2.% |

+----------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'zhiuan'@'10.0.2.%' |

+----------------------------------------------------+

1 row in set (0.00 sec)

#拿掉某个权限

mysql> show grants for peng@'10.0.%';

+--------------------------------------------------------+

| Grants for peng@10.0.% |

+--------------------------------------------------------+

| GRANT SELECT, INSERT, DELETE ON *.* TO 'peng'@'10.0.%' |

+--------------------------------------------------------+

1 row in set (0.00 sec)

mysql> revoke delete on *.* from peng@'10.0.%';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for peng@'10.0.%';

+------------------------------------------------+

| Grants for peng@10.0.% |

+------------------------------------------------+

| GRANT SELECT, INSERT ON *.* TO 'peng'@'10.0.%' |

+------------------------------------------------+

1 row in set (0.00 sec)