写在前面的话
取这个标题的目的很简单,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 其实很多定制化的东西都可以写到配置文件中,所以二进制安装足够了。
官网下载(认死了官网):
如图所示:
最后,关于其它数据库目前的使用情况也可以简单了解:
结果如下:
二进制安装
目录设计如下:
说明:
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
结果如下:
至此,安装就完成,值得注意的是:
1. 之前创建的目录需要为空,否则后面初始化可能报错。
2. 如果已经安装了其它 MySQL,建议先卸载,否则初始化可能报错。
初始配置数据库
安装完成后,MySQL 默认是没有数据的,需要初始化:
1. 初始化数据库:
mysqld --initialize-insecure --user=mysql --datadir=/data/data/mysql --basedir=/data/services/mysql
初始化完成 MySQL 管理员用户默认没有密码:
这源于初始化参数:--initialize-insecure,如果只是 --initialize ,会默认生成一个 12 位复杂度非常高的密码。
在初始化过程中,指定了程序用户,程序目录,以及数据保存的目录。可以去 /data/data/mysql 中查看生成的数据:
如果初始化失败,删除该目录下所有文件,重新初始化。
而在 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 运行为两个进程:
一个以 root 用户运行,一个以 MySQL 用户运行。
4. 登录数据库:
mysql
没有设置密码,所有可以直接登录:
由于在配置文件中指定了登录后提示显示,所以可以直接看到端口,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
查看进程:
发现使用 systemd 运行 MySQL 只有一个进程。
关于 MySQL 服务
MySQL 安装完成以后,对于服务的构成,通常能够看到这样一个图:
工作流程:
1. 应用程序通过连接层传送 SQL 到 SQL 层,此时 SQL 层会对该 SQL 进行语法校验和是否满足当前 MySQL 定义的 sql_mode。
说明:连接 MySQL 的方式一般有两种,一种是 TCP/IP,一种是 Socket。
2. SQL 层还会对 SQL 类型进行判断,检查用户对库的权限,解析 SQL 生成多种执行方案并择优选择执行。
说明:SQL 语句类型包含数据库定义语言(DDL),数据库控制语言(DCL),数据库操作语言(DML),数据查询语言(DQL)
3. 存储引擎层负责存取数据。
数据库的逻辑结构如下图:
其中需要记住的主要关键字:库,表,字段,字段类型,记录
数据库的物理存储结构如下图:
现在的数据库存储引擎一般都是 InnoDB,库对应着服务器上面 /data/data/mysql 下的目录。可以新建目录测试:
# 创建目录
mkdir -p /data/data/mysql/abc # 登录数据库查看
mysql
结果如下:
删库跑路命令:删库一时爽,一直删库一直爽
drop database abc;
查看:
用户管理
由于 insecure 的原因,mysql 的 root 用户没有密码。这显然是不安全的,在实际工作中,虽然 MySQL 数据库服务器虽然一般在生产内网,但为了安全起见,还是会给数据库进行加密,授权操作。
1. 创建用户:
# 创建用户
create user 'dylan'@'%' identified by ''; # 查看用户
select user,host,authentication_string from mysql.user;
创建 dylan 用户,密码 123,% 授权所有都能登录。
完成之后去系统的 mysql 库的 user 表查看用户的用户名,允许登录授权,密码。
可以发现,用户的密码是加密的,当然,在老版本中,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 '';
此时再度查看:
3. 删除用户:
# 删除用户
drop user 'dylan'@'%';
结果如下:
权限管理
在日常工作中,除了管理员自身,一般不会拿着 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 用户一样了。
简单的扩展一下:
在 8.0 以前的版本,可以在授权的同时新建用户和设置密码,但是在 8.0 以后的版本,建用户和授权必须分开。
授权完成,查看用户的授权:
show grants for 'dylan'@'%';
结果如图:
当然也可以收回授予用户的某个权限:
revoke delete on abc.* from 'dylan'@'%';
查看结果:
忘记密码重置密码
人到中年从忘记密码开始,虽然会将密码保存到一定的文档里面,但是一切总有意外。当这些意外发生的时候,如何挽救。
# 停止 MySQL
systemctl stop mysqld # 跳过验证启动 MySQL,并限定只是本地跳过
mysqld_safe --skip-grant-tables --skip-networking & # 登录数据库
mysql
这里跳过了验证,同时跳过了网络,意味着只能本地 socket 连接,此时 3306 端口其实是没有启动的。
使用 mysqld_safe,本身启动 MySQL 实质就是通过这个文件。此时连上数据库重置密码:
值得注意的是,在修改用户密码之前,需要 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;"
效果如图:
对于数据库 SQL 脚本,也可以使用 < 导入:
新建 test,sql:
create database hello;
导入到数据库:
# 导入
mysql -uroot -p < test,sql # 查看
mysql -uroot -p -e "show databases;"
结果如下:
关于基础配置文件
在初始化的时候将 MySQL 的配置文件放在 /etc/my.cnf
配置文件其实是可以放多个地方的,通过命令查看:
mysqld --help --verbose | grep 'my.cnf'
默认的加载路径如图:
可以使用 --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 用户授权管理的知识点。这些对于作为运维的我们来说可以算是必须掌握的。也很简单。