MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

时间:2023-04-04 08:17:02

写在前面的话

取这个标题的目的很简单,MySQL 在中小型企业中一般都是由运维来维护的,除非数据很重要的公司可能会聘请 DBA。

但是运维一般存在由于所需要了解的东西很多很杂,导致学习过程中很多东西只能浅尝辄止。就像 MySQL,为此专门做这样一个系列,系统的捋一捋 MySQL。

关于数据库

什么是数据?

我们在浏览一个网站的时候,如爱奇艺,可以看到首页有很多视频,图片,文字,这些都是数据。

用比较官方的话来说就是:对客观事件进行记录并可以鉴别的符号。

什么是数据库?

就是存放这些数据文件的地方,可以对它的数据进行增删改查。

关系型数据库管理系统(RDBMS)用途:

一般用于保存对于数据的安全性要求较高的数据。常见如:MySQL,Oracle,SQL Server,PostgreSQL

非关系型数据库(NoSQL)用途:
一般用于对于性能要求高,但是对于数据安全性没那么高的数据。常见如:键值的 Redis,Memcache,文档的 MongoDB

可以简单的了解一下 MySQL 的发展:

MySQL 一开始是作为一个开源项目诞生的,在此之前很多传统企业(国家机关)还是以 Oracle 为主。这几年互联网行业风生水起,导致 MySQL 占有的市场份额不断增加,已经严重威胁到了 Oracle 的地位,这肯定是 Oracle 这个公司不能接受的。所以以 Oracle 的尿性,干不赢你就收购你。至此,MySQL 分成了社区版(Community)商业版(Enterprise)。但这并不影响我们的使用,我们选择社区版。

当然,在这整个变迁过程中,MySQL 团队的成员出来以后也重写了项目,于是就有了两个重要的分支:PerconaDB(Percona) MariaDB(红帽)

这两个分支在市面上也占有非常大的份额,但是我们并不用担心学哪个,因为使用方法几乎和 MySQL 一模一样。

关于 MySQL 版本的一些知识:

MySQL 5.5:很早期的版本,除非实在换不下来,否则这个几乎已是历史,当然还在更新。以 5.5.20 版本为例,发行于 2011 年。

MySQL 5.6:可能很多公司还会有,但大多为老项目,以 5.6.20 版本为例,更新于 2014 年。

MySQL 5.7:现在主流版本,相比于 5.6 有一些很好的优化,所以 5.7 是生产主力版本,5.7.20 于 2017 年更新。

MySQL 8:全新版本,据了解改动不小。目前处于尝鲜体验阶段。2018 年开始发力更新。

对于数据库这种东西来说,并非越新越好,所以尝鲜踩雷交给别人,我们选择 5.7 GA 稳定发布版

本文选择二进制安装,至于编译安装可能后面有时间会补上,甚至以 Percona 为例。

另外,MySQL 其实很多定制化的东西都可以写到配置文件中,所以二进制安装足够了。

官网下载(认死了官网):

https://downloads.mysql.com/archives/community/

如图所示:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

最后,关于其它数据库目前的使用情况也可以简单了解:

https://db-engines.com/en/ranking

结果如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

二进制安装

目录设计如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

说明:

1. 设计将所有文件都存放到 /data 目录下。

2. packages/mysql 存放二进制包,logs/mysql 存放日志 / PID / sock 文件,services/mysql 主程序,backup/mysql 存放备份,data/mysql 存放数据。

3. 将下载的 mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz 上传到服务器的 /data/packages/mysql 下面。

开始安装:

1. 新建目录和用户:

mkdir -p /data/{data,logs,backup,packages}/mysql
mkdir -p /data/services
useradd -s /sbin/nologin mysql

2. 修改权限:

chown mysql.mysql /data/logs/mysql/
chown mysql.mysql /data/data/mysql/

3. 解压二进制包:

cd /data/packages/mysql/
tar -zxf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /data/services/
cd /data/services/
mv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql

4. 添加环境变量:

echo 'export PATH=$PATH:/data/services/mysql/bin' >> /etc/profile
source /etc/profile

5. 测试命令:

mysql -V

结果如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

至此,安装就完成,值得注意的是:

1. 之前创建的目录需要为空,否则后面初始化可能报错。

2. 如果已经安装了其它 MySQL,建议先卸载,否则初始化可能报错。

初始配置数据库

安装完成后,MySQL 默认是没有数据的,需要初始化:

1. 初始化数据库:

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

初始化完成 MySQL 管理员用户默认没有密码:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

这源于初始化参数:--initialize-insecure,如果只是 --initialize ,会默认生成一个 12 位复杂度非常高的密码。

在初始化过程中,指定了程序用户,程序目录,以及数据保存的目录。可以去 /data/data/mysql 中查看生成的数据:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

如果初始化失败,删除该目录下所有文件,重新初始化。

而在 5.6 版本中,初始化命令还是:mysql_install_db

2. 配置默认配置文件:/etc/my.cnf

[mysqld]
user=mysql
port=3306
basedir=/data/services/mysql
datadir=/data/data/mysql
pid-file=/data/logs/mysql/mysqld.pid
log-error=/data/logs/mysql/error.log
socket=/data/logs/mysql/mysql.sock
server_id=111 [client]
port=3306
socket=/data/logs/mysql/mysql.sock
prompt=3306/\\u [\\d]>

3. 配置启动文件并启动服务:

# 添加配置文件
cd /data/services/mysql/support-files/
cp mysql.server /etc/init.d/mysqld # 手动创建日志和 PID 文件
touch /data/logs/mysql/error.log
touch /data/logs/mysql/mysqld.pid
chown -R mysql.mysql /data/logs/mysql/ # 启动服务
/etc/init.d/mysqld start

结果如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

此时 MySQL 运行为两个进程:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

一个以 root 用户运行,一个以 MySQL 用户运行。

4. 登录数据库:

mysql

没有设置密码,所有可以直接登录:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

由于在配置文件中指定了登录后提示显示,所以可以直接看到端口,use 的库。相比默认的显示友好很多。

5. CentOS 7 systemd 配置:/etc/systemd/system/mysqld.service

[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=/data/services/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

测试启动:

systemctl start mysqld

查看进程:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

发现使用 systemd 运行 MySQL 只有一个进程。

关于 MySQL 服务

MySQL 安装完成以后,对于服务的构成,通常能够看到这样一个图:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

工作流程:

1. 应用程序通过连接层传送 SQL 到 SQL 层,此时 SQL 层会对该 SQL 进行语法校验和是否满足当前 MySQL 定义的 sql_mode。

说明:连接 MySQL 的方式一般有两种,一种是 TCP/IP,一种是 Socket。

2. SQL 层还会对 SQL 类型进行判断,检查用户对库的权限,解析 SQL 生成多种执行方案并择优选择执行。

说明:SQL 语句类型包含数据库定义语言(DDL),数据库控制语言(DCL),数据库操作语言(DML),数据查询语言(DQL)

3. 存储引擎层负责存取数据。

数据库的逻辑结构如下图:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

其中需要记住的主要关键字:库,表,字段,字段类型,记录

数据库的物理存储结构如下图:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

现在的数据库存储引擎一般都是 InnoDB,库对应着服务器上面 /data/data/mysql 下的目录。可以新建目录测试:

# 创建目录
mkdir -p /data/data/mysql/abc # 登录数据库查看
mysql

结果如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

删库跑路命令:删库一时爽,一直删库一直爽

drop database abc;

查看:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

用户管理

由于 insecure 的原因,mysql 的 root 用户没有密码。这显然是不安全的,在实际工作中,虽然 MySQL 数据库服务器虽然一般在生产内网,但为了安全起见,还是会给数据库进行加密,授权操作。

1. 创建用户:

# 创建用户
create user 'dylan'@'%' identified by ''; # 查看用户
select user,host,authentication_string from mysql.user;

创建 dylan 用户,密码 123,% 授权所有都能登录。

完成之后去系统的 mysql 库的 user 表查看用户的用户名,允许登录授权,密码。

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

可以发现,用户的密码是加密的,当然,在老版本中,authentication_string 的字段名可能是 password。

至于授权,可以参考下表:

授权 说明
'dylan'@'%' 允许所有,能通信的就能登录
'dylan'@'192.168.10.%' 允许 192.168.10.1 - 254 登录
'dylan'@'192.168.10.1%' 值允许 192.168.10.1 / 10 - 19 登录
'dylan'@'192.168.10.1' 只允许 192.168.10.1 登录
'dylan'@'127.0.0.1' 只允许本机登录
'dylan'@'localhost' 只允许本机登录
'dylan'@'app01' 允许能解析的主机名 app01 登录
'dylan'@'192.168.10.0/255.255.255.0' 允许网段登录

这大概就是所有授权方式,已经能够完全满足日常使用。

2. 修改用户:

# 给没有密码的 root 用户设置密码
alter user 'root'@'localhost' identified by '';

此时再度查看:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

3. 删除用户:

# 删除用户
drop user 'dylan'@'%';

结果如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

权限管理

在日常工作中,除了管理员自身,一般不会拿着 root 用户直接开始使用,所有会新建一个专门的应用用户。

但在某些时候,如果只是想给某个用户一个只读权限,这就牵扯到了 MySQL 权限授予。

在 MySQL 一般权限有如下一些:

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

比如给一个用户授权 abc 库增删查改的权限:在授权的同时顺便将之前删除的用户重新创建。

# 创建数据库
create database abc; # 创建并授权用户
grant insert,delete,select,update on abc.* to 'dylan'@'%' identified by '';

如果用户需要 abc 库的所有权限,那么不需要一个一个的写,直接用 all:

grant all on abc.* to 'dylanall'@'%' identified by '';

对于授权方式一般用:

*.*:授权所有库

abc.*:授权 abc 库下面的所有表

abc.t1:授权 abc 下面的 t1 表

当然,如果想一个普通用户变成管理员用户,需要两样授权,一个是 all,一个是 with grant option

grant all on *.* to 'admin'@'%' identified by '' with grant option;

这样 admin 用户就能像 root 用户一样了。

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

简单的扩展一下:

在 8.0 以前的版本,可以在授权的同时新建用户和设置密码,但是在 8.0 以后的版本,建用户和授权必须分开。

授权完成,查看用户的授权:

show grants for 'dylan'@'%';

结果如图:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

当然也可以收回授予用户的某个权限:

revoke delete on abc.* from 'dylan'@'%';

查看结果:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

忘记密码重置密码

人到中年从忘记密码开始,虽然会将密码保存到一定的文档里面,但是一切总有意外。当这些意外发生的时候,如何挽救。

# 停止 MySQL
systemctl stop mysqld # 跳过验证启动 MySQL,并限定只是本地跳过
mysqld_safe --skip-grant-tables --skip-networking & # 登录数据库
mysql

这里跳过了验证,同时跳过了网络,意味着只能本地 socket 连接,此时 3306 端口其实是没有启动的。

使用 mysqld_safe,本身启动 MySQL 实质就是通过这个文件。此时连上数据库重置密码:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

值得注意的是,在修改用户密码之前,需要 flush privileges 刷新一下,否则无法重置。

原因是本身是跳过认证的,你在修改认证,MySQL 觉得你是憨憨,所以需要刷新将配置加载。

重置完成退出登录,重启 MySQL:

# 停止 MySQL
pkill mysqld # 重新启动
systemctl start mysqld

此时就可以使用新密码登录!

当然,这种方法有些暴力,可以换温柔一点的方法,在 /etc/my.cnf 中加入 skip-grant-tables ,重启数据库,然后登录修改,修改完成后去掉配置重启数据库即可。

连接数据库

在连接数据库的时候,可以看到之前在命令行连接都是直接带个 -u -p 参数。

前面说过 MySQL 连接的两种方式 TCP 和 Socket,Socket 怎么使用呢?

mysql -uroot -p -S /data/logs/mysql/mysql.sock

这种一般用于有多个实例的时候,-S 指定 sock当然,还有一些其它参数。

使用 -P 指定端口,使用  -h 指定数据库 IP 地址,用于在其它主机上连接。

mysql -uroot -p -P3306 -h127.0.0.1

可以在不进入数据库交互界面直接在外部执行数据库命令,-e 参数:

mysql -uroot -p -e "show databases;"

效果如图:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

对于数据库 SQL 脚本,也可以使用 < 导入:

新建 test,sql:

create database hello;

导入到数据库:

# 导入
mysql -uroot -p < test,sql # 查看
mysql -uroot -p -e "show databases;"

结果如下:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

关于基础配置文件

在初始化的时候将 MySQL 的配置文件放在 /etc/my.cnf

配置文件其实是可以放多个地方的,通过命令查看:

mysqld --help --verbose | grep 'my.cnf'

默认的加载路径如图:

MySQL for OPS 01:简介 / 安装初始化 / 用户授权管理

可以使用 --defaults-file=xxx 指定具体的。

另外就是主配置文件的书写,格式为:

[标签]
配置项=xxx

对于标签,MySQL 的配置可分为服务端和客户端。

服务端:[mysqld] [mysqld_safe] [server]

客户端:[mysql] [mysqldump] [client]

推荐使用 mysqld / mysql 的形式,否则其它命令可能会报错某些参数无法识别,如:

[mysqld]
user=mysql
port=3306
basedir=/data/services/mysql
datadir=/data/data/mysql
pid-file=/data/logs/mysql/mysqld.pid
log-error=/data/logs/mysql/error.log
socket=/data/logs/mysql/mysql.sock
server_id=111 [mysql]
port=3306
socket=/data/logs/mysql/mysql.sock
prompt=3306/\\u [\\d]>

小结

本节都是一些安装简介,概念性,以及 MySQL 用户授权管理的知识点。这些对于作为运维的我们来说可以算是必须掌握的。也很简单。