mysql5.7 安装和权限管理

下载地址：
https://downloads.mysql.com/archives/community/

本次测试用的安装包 ： 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)
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。