mysql
A、mysql的安装准备工作
一、安装
1、Mysql 5.7.26 二进制版本安装(软件指路：www.mysql.com==>download archives)
下载并上传软件至/server/tools,并进行解压移至专用的/application目录下，命名为mysql
[root@mysql ~]# cd /server/tools
[root@mysql tools]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@mysql tools]# tar -xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@mysql tools]# mkdir /application
[root@mysql tools]# ls
mysql-5.7.26-linux-glibc2.12-x86_64
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@mysql tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql
2、处理系统初始环境并创建用户
处理原始环境
[root@mysql tools]# rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@mysql tools]# ls /etc/my.cnf
/etc/my.cnf
这个库这个配置文件的存在会影响我们之后mysql数据库的初始化，有两种解决办法：
1.删除或移走配置文件；2。删除这个mariadb软件包
这里选择卸载的办法：
[root@mysql ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64
错误：依赖检测失败：
libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-7.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-7.el7.x86_64 需要
[root@mysql ~]# yum remove -y mariadb-libs-5.5.60-1.el7_5.x86_64
rpm无法卸载就采用yum方式卸载
创建用户
[root@mysql ~]# useradd -s /sbin/nologin mysql
/sbin/nologin 不允许登录
3、设置环境变量
[root@mysql ~]# vim /etc/profile
[root@mysql ~]# cat /etc/profile|tail -1
export PATH=/application/mysql/bin:$PATH
[root@mysql ~]# source /etc/profile
[root@mysql ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
能看到版本说明环境变量设置成功了
4、创建数据磁盘并授权
(1)添加新磁盘模拟生产中的mysql数据盘
编辑虚拟机设置(关机状态)，一直点击下一步，添加5G硬盘
如下图，就添加好了
(2)格式化并进行挂载
[root@mysql ~]# mkfs.xfs /dev/sdb
[root@mysql ~]# mkdir /data #创建挂载目录
[root@mysql ~]# blkid #查看磁盘UUID号
/dev/sdb: UUID="a7bc02f4-cc2d-4bd2-b336-4d980994300c" TYPE="xfs"
[root@mysql ~]# cat /etc/fstab|tail -1 #在挂载文件里添加下面内容
UUID=a7bc02f4-cc2d-4bd2-b336-4d980994300c /data xfs defaults 0 0
[root@mysql ~]# mount -a #将/etc/fstab的所有内容重新加载
[root@mysql ~]# df -h #查看磁盘状况
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/centos-root 10G 4.7G 5.4G 47% /
devtmpfs 475M 0 475M 0% /dev
tmpfs 487M 0 487M 0% /dev/shm
tmpfs 487M 7.7M 479M 2% /run
tmpfs 487M 0 487M 0% /sys/fs/cgroup
/dev/sr0 11G 11G 0 100% /media
/dev/sda1 197M 111M 87M 57% /boot
tmpfs 98M 0 98M 0% /run/user/0
/dev/sdb 5.0G 33M 5.0G 1% /data
(3)授权
[root@mysql ~]# mkdir /data/mysql/data -p
[root@mysql ~]# chown -R mysql:mysql /application/mysql
[root@mysql ~]# chown -R mysql:mysql /data/
5、初始化数据(创建系统数据)
mysql工作需要软件部分和数据部分，数据部分没有自带，有工具或命令可以创建初始化数据，这个命令就是位于/application/mysql/bin下的mysqld(5.7版本)
1、官方要求的方式
[root@mysql bin]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data/
注释说明：
#mysqld是/application/mysql/bin下的mysqld
#--initialize 对密码复杂度进行定制，12位，4种，给root@localhost设置临时密码
出现下面这样一句话就算初始化成功了
2021-05-19T10:46:34.660347Z 1 [Note] A temporary password is generated for root@localhost: 临时密码
如果有如下报错可以参考解决方案
2、生产中用的比较多的
在执行了1方法的命令后，要想执行2命令，就得删除掉/data/mysql/data里的已经初始化的内容
添加--initialize-insecure 无限制，无临时密码
[root@mysql data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data/
2021-05-19T11:00:56.068499Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
这样就算成功了
二、配置文件的准备
初始化配置文件的作用：影响数据库的启动(mysqld,mysqld_safe,server)及客户端的连接(mysql,mysqldump,mysqladmin,client)
[root@mysql data]# vim /etc/my.cnf
[mysqld] #服务端
user=mysql #管理用户的用户名
basedir=/application/mysql #程序的目录
datadir=/data/mysql/data #数据的路径
socket=/tmp/mysql.sock #socket的文件路径
server_id=6 #范围为1-65535
port=3306 #自行指定端口号
log_error=/data/mysql/data/mysql.log
log_bin=/data/mysql/data/mysql-bin #专门用作主从复制和备份恢复的一个日志
[mysql] #客户端
socket=/tmp/mysql.sock
三、启动mysql数据库
sys-v方法的启动分为以下两种
1、用配置文件启动
[root@mysql data]# cd /application/mysql/support-files/
[root@mysql support-files]# ll
总用量 24
-rw-r--r--. 1 mysql mysql 773 4月 13 2019 magic
-rwxr-xr-x. 1 mysql mysql 1061 4月 13 2019 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql 894 4月 13 2019 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10576 4月 13 2019 mysql.server
[root@mysql support-files]# ./mysql.server start
Starting MySQL.Logging to '/data/mysql/data/mysql.err'.
. SUCCESS!
不觉得上述方法很麻烦吗？每次启动还要进到目录里用文件启动，所以有了第二种方法
2、用service的方式去调用
[root@mysql init.d]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql init.d]# service mysqld status
SUCCESS! MySQL running (7680)
7的systemd管理方法启动
需要定制一个启动的脚本
[root@mysql init.d]# vim /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]
WanteBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
[root@mysql init.d]# systemctl start mysqld
[root@mysql init.d]# netstat -tupln|grep 3306
tcp6 0 0 :::3306 :::* LISTEN 8165/mysqld
启动成功
在用这个方法启动之前要先把刚才用service起的服务停掉(service mysqld stop)
四、mysql无法启动遇到的错误
1、without updating PID 类似错误
查看日志：在/data/mysql/data/主机名.err ==>[ERROR]
出现错误的原因可能有：
1、/etc/my.cnf配置有错
2、/data/mysql/data目录的属主组是否为mysql
五、管理员密码的设置及找回
[root@mysql data]# mysqladmin -uroot -p password 123456
如果有旧密码，想要设置新密码，可以如下设置，带上旧密码
[root@mysql data]# mysqladmin -uroot -p123456 password abcd
设置了密码以后就这样登录
[root@mysql data]# mysql -uroot -p
为了安全，回车按密码，不要在屏幕上输入密码
管理员用户密码忘记怎么办？
1、关闭数据库,如果用systemd启动的，就用systemctl stop mysql
2、启动数据库到维护模式
mysqld_safe --skip-grant-tables --skip-networking &
3、登录并修改密码
进入到mysql,然后修改密码
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '1'; #将密码设为1
4、关闭数据库，正常启动，用刚才设置的密码验证
B、mysql体系结构与管理
一、mysql结构及实例
1、mysql c/s结构介绍
两种连接方式：
1、TCP/IP:mysql -uroot -p 123456 -h 10.0.0.1 -P 3306
2、Socket:mysql -uroot -p 123456 -S /tmp/mysql.sock
2、mysql实例的构成
相当于 公司=老板+经理+员工+办公区
实例=mysqld+master thread +干活的thread +预分配的内存
二、一条sql语句执行的过程
1、连接层
(1) 提供连接协议
socket
tcp/ip
(2)验证用户名，密码的合法性,用专门的授权表进行匹配
(3)派生一个专用的连接线程(接收sql语句，返回结果)，用show processlist;可以看到连接线程
思考 ：我们上面说到的用户忘记了密码可以用mysqld_safe --skip-grant-tables --skip-networking就是在连接层做了手脚，第一个参数是指跳过授权表，第二个参数是指不用tcp/ip协议，用socket协议，为了安全
2、SQL层(专门做语句的处理工作，优化方面至关重要)
(1)验证sql语法和sql_mode(mysql支持的基本语法及校验规则)
(2)验证语义
(3)验证权限
(4)解析器进行语句解析，生成执行计划(解析树)
(5)优化器(根据算法找到代价最低的执行计划)代价(cpu,io,mem)
(6)执行器按照优化器选择的执行计划，执行sql语句，得出获取数据的方法
(7)提供query cache(查询缓存)，每次执行的sql语句会有缓存(sql id)，下次遇到相同的语句直接拿缓存(默认不开，用redis替代)
(8)记录操作日志(binlog),只记录修改类的操作，默认没开
3、存储引擎层（真正和磁盘打交道的一个层次）
根据SQL层提供的取数据的方法，拿到数据，返回给SQL,结构化成表，再由连接层线程返回给用户
三、mysql的逻辑结构和物理结构
1、逻辑结构
2、物理结构
宏观上：
库，存储在操作系统的目录中
表：
user表(MyISAM):
user.frm ------>列的定义信息
user.MYD ------>数据行
user.MYI ------->索引信息
time_zone表(InnoDB):
time_zone.frm ----->列的定义信息
time_zone.ibd ----->数据行和索引
面试题：请你说明MyISAM和InnoDB在存储方式上的异同
微观上：
一个表就是一个段，mysql分配空间时至少分配一个区，每个区默认是1M(64个pages)，mysql最小的io单元是page(16k)
四、mysql 用户、权限及连接管理
1、用户的定义
用户名@’ 白名单 ’
2、用户管理
作用：登录，管理数据库
a、创建用户
mysql> create user qianxi@'192.168.0.%' identified by '123456';
b、查询用户
mysql> select user,host,authentication_string from mysql.user;
+---------------+-------------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| qianxi | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-------------+-------------------------------------------+
4 rows in set (0.00 sec)
c、删除用户
mysql> drop user oldboy@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
d、修改用户
mysql> alter user q192.168.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
3、权限管理
作用：控制用户登录之后能对mysql对象做哪些命令
定义：mysql的权限定义就是sql语句，all是所有权限，是普通管理员拥有的
with grant option:超级管理员给别的用户授权的功能
mysql> grant all on *.* to liyu@'10.0.0.%' identified by '123456' with grant option;
上述代码为生成一个超级管理员
grant ALL on wordpress.* to wordpress@'10.0.0.%' identified by '123';
命令解释说明：
grant 权限 on 范围 to 用户 identified by ‘密码’;
例子：
1、从windows中的navicat软件使用root管理mysql数据库
2、创建zhihu业务用户能够对zhihu业务库进行业务
1、grant all on *.* to root@'10.0.0.%' identified by '123';
2、grant select,update,delete,insert on zhihu.* to zhihu@'10.0.0.*' identified by '123';
mysql中权限是可以继承的，多次授权是叠加的，所以想要取消某个权限，必须通过回收的方式实现，而不能多次重复授权
a、如何查看用户权限
mysql> show grants for qianxi@'192.168.0.%';
b、回收权限
mysql> revoke delete on zhihu.* from zhihu@'10.0.0.%';
回收zhihu用户的delete权限
面试题：
1、开发人员找dba开用户，需要dba和开发人员沟通什么？
1、要做哪些权限的操作
2、要从什么地址来登录数据库
3、要对哪些库哪些表进行操作
2、开发人员找dba要管理员root用户的密码，作为dba你怎么处理?
4、mysql连接管理
自带的客户端工具
mysql 参数
-u 用户名
-p 密码
-h 接数据库的地址
-S 后接socket文件，用socket连接方式
-P 接3306端口
-e 非交互式执行Mysql里的命令
如果使用socket连接则show processlist看就是root用户连接，如果是tcp远程连接，则show processlist看就是主机名用户连接，如果两者都加上连接，则以tcp连接优先
五、多实例配置
1、创建目录
[root@mysql mysql]# mkdir -p /data/330{7,8,9}/data
2、准备配置文件
[root@mysql mysql]# vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
server_id=7
port=3307
log_error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
另外两个3308和3309的配置文件值需要修改数字即可
3、初始化3套数据
[root@mysql ~]# mv /etc/my.cnf{,.bak}
[root@mysql ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data/ --basedir=/application/mysql/
[root@mysql ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data/ --basedir=/application/mysql/
[root@mysql ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data/ --basedir=/application/mysql/
4、systemd管理多实例
[root@mysql ~]# cd /etc/systemd/system
[root@mysql system]# cp mysqld.service mysqld3307.service
[root@mysql system]# cp mysqld.service mysqld3308.service
[root@mysql system]# cp mysqld.service mysqld3309.service
[root@mysql system]# vim mysqld3307.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
[root@mysql system]# vim mysqld3308.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
[root@mysql system]# vim mysqld3309.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
5、授权
[root@mysql system]# chown -R mysql:mysql /data/*
6、启动
[root@mysql system]# systemctl start mysqld3307.service
[root@mysql system]# systemctl start mysqld3308.service
[root@mysql system]# systemctl start mysqld3309.service
7、验证多实例
[root@mysql system]# netstat -tupln|grep 330
tcp6 0 0 :::3306 :::* LISTEN 7598/mysq
tcp6 0 0 :::3307 :::* LISTEN 7902/mysq
tcp6 0 0 :::3308 :::* LISTEN 7936/mysq
tcp6 0 0 :::3309 :::* LISTEN 7983/mysq
[root@mysql system]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
3264
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
