mysql-编译安装、配置文件、体系结构、用户及权限管理
mysql安装
mysql安装准备
1、安装包文件下载上传
2、解压安装包
mkdir -p /opt/mysql_cluster/
mkdir /opt/mysql_cluster/mysql/logs/
tar -xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /opt/mysql_cluster/
mv mysql-5.7.36-linux-glibc2.12-x86_64/ mysql/
3、mariadb检查卸载
rpm -qa |grep mariadb #mariadb的配置文件和mysql位置一致，卸载lib或删除配置文件都可以
yum remove mariadb-libs-5.5.68-1.el7.x86_64
4、添加用户名及环境变量
[root@db01 mysql]# useradd -s /sbin/nologin mysql
[root@db01 etc]# cat >> /etc/profile <<eof
export PATH=/opt/mysql_cluster/mysql/bin:$PATH
eof
[root@db01 mysql]# source /etc/profile
[root@db01 etc]# mysql -V #验证环境变量是否生效
mysql Ver 14.14 Distrib 5.7.36, for linux-glibc2.12 (x86_64) using EditLine wrapper
5、创建数据路径并授权
添加新磁盘模拟数据盘
[root@db01 ~]# fdisk -l #验证新磁盘是否新增成功
磁盘 /dev/sdb：5368 MB, 5368709120 字节，10485760 个扇区
Units = 扇区 of 1 * 512 = 512 bytes
扇区大小(逻辑/物理)：512 字节 / 512 字节
I/O 大小(最小/最佳)：512 字节 / 512 字节
格式化并挂在磁盘
[root@db01 ~]# mkfs.xfs /dev/sdb
meta-data=/dev/sdb isize=512 agcount=4, agsize=327680 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=1310720, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@db01 data]# blkid #查看磁盘uuid
[root@db01 data]# mkdir /data/mysql_data #创建数据挂载目录
[root@db01 data]# vim /etc/fstab #配置自动挂载
uuid=acf57303-903f-4995-8a4a-0865347e32df /data/mysql_data xfs defaults 0 0
[root@db01 data]# mount -a #加载自动挂载配置文件
[root@db01 data]# df -h #验证是否挂载成功
/dev/sdb 5.0G 33M 5.0G 1% /data/mysql_data
[root@db01 ~]# chown -R mysql:mysql /opt/mysql_cluster/* #目录授权
[root@db01 ~]# chown -R mysql:mysql /data/mysql_data/
6、初始化数据(创建系统数据)
6、初始化数据(创建系统数据)
#5.6版本初始化命令 安装目录/scripts/mysql_install_db
方式1
[root@db01 ~]# mysqld --initialize --user=mysql --basedir=/opt/mysql_cluster/mysql --datadir=/data/mysql_data
--initialize参数说明：1、对密码复杂度进行定制：12位，4种 2、密码过期时间：180天 3、给root@localhost设置临时密码
2022-01-28T07:45:51.716842Z 1 [Note] A temporary password is generated for root@localhost: vT(?t60qP++R #生成root临时密码
方式2
先清空数据目录数据才能重新初始化 #添加了-insecure参数不再验证密码强度
[root@db01 mysql_data]# rm -rf /data/mysql_data/*
[root@db01 mysql_data]# mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql_cluster/mysql --datadir=/data/mysql_data
[root@db01 ~]# mysql #免密登录
7、配置文件修改
[mysqld]
user=mysql
basedir=/opt/mysql_cluster/mysql
datadir=/data/mysql_data
log_error=/opt/mysql_cluster/mysql/logs/db01.err
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
8、启动数据库
（1）、通过mysql自带脚本start、stop、restart
[root@db01 support-files]# ll /opt/mysql_cluster/mysql/support-files/mysql.server
-rwxr-xr-x. 1 mysql mysql 10576 9月 7 15:46 /opt/mysql_cluster/mysql/support-files/mysql.server
[root@db01 support-files]# ./mysql.server status
ERROR! MySQL is not running
(2)通过service 启动,拷贝至init.d即可
[root@db01 ~]# cp /opt/mysql_cluster/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@db01 init.d]# service mysqld status
ERROR! MySQL is not running
（3）systemctl 启动
cat > /etc/systemd/system/mysqld.service <<EOF
[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_cluster/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
[root@db01 system]# systemctl status mysqld
判断mysql是否启动的方式：1、端口号 2、进程 3、systemctl status 4、mysql登陆
1、without uodating PID 类似错误
#查看日志 日志位置：默认在安装目录下，查看[error]上下文
可能情况：
/etc/my.cnf 路径不对，权限不对、关键配置项缺失，比如数据目录、安装目录等
mysql.sock 修改过或删除过
参数修改错误
mysql 管理员密码设置（root@localhost）
[root@db01 logs]# mysqladmin -uroot -p password Test123@;
Enter password: #如果原来是空口令则直接回车即可
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. #提示密码以明文发送到服务器可能不安全，建议使用ssl
管理员密码忘记处理
1、关闭数据库
2、启动数据库到维护模式
[root@db01 logs]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 3358
[root@db01 logs]# 2022-01-29T02:56:24.540619Z mysqld_safe Logging to '/opt/mysql_cluster/mysql/logs/db01.err'.
2022-01-29T02:56:24.608176Z mysqld_safe Starting mysqld daemon with databases from /data/mysql_data
mysql> alter user 'root'@'localhost' identified by '1'; #修改密码
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement #因为正在执行-skip-grant-tables参数，无法执行该语句
mysql> flush privileges; #刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost' identified by '1'; #再次执行密码修改就成功啦
Query OK, 0 rows affected (0.00 sec)
关闭维护模式，正常启动数据库即可
[root@db01 logs]#pkill mysql
[root@db01 logs]# systemctl start mysqld
[root@db01 logs]# mysql -uroot -p
Enter password:
mysql c/s结果介绍
mysql服务端提供的连接方式：
通过socket本地登录:mysql -uroot -p1 -S /tmp/mysql.sock
通过tcp/ip远程连接
mysql 实例
mysqld+master thread +读写线程+预分派内存
mysqld服务器进程结构
连接层：1、socket+tcp/ip协议2、验证用户名密码3、分配一个专用的连接线程(接收sql，返回结果)
mysql> show processlist;#查询用户登录线程
+----+------+--------------------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+-------+---------+------+----------+------------------+
| 26 | root | localhost | mysql | Query | 0 | starting | show processlist |
+----+------+--------------------+-------+---------+------+----------+------------------+
--skip-grant-tables：在连接层关闭密码及权限验证组件
--skip-networking：在连接层关闭tcp/ip组件,仅留socket连接方式
sql层：1、验证sql语法和sql_mode模块2、验证语义3、权限加载到内存，验证权限
4、解析器进行语句解析生成执行计划
5、优化器(各类算法，5.7基于执行代价)根据算法找到代价最低的执行计划。代价：cpu、IO、MEN
6、执行器按照优化器选择执行计划，执行sql语句，得出获取数据的方法
7、提供query cache(查询缓存，默认不开启，使用redis替代)(通过hash算法计算出sql语句的sqlid，通过比对sqlid确认是否查询缓存)
8、修改变更操作日志（binlog），默认开启
……
存储引擎层：根据sql提供的获取数据的方法从磁盘获取二进制或十六进制数据，返回给sql结构化成表，再由连接层返回给用户。
mysql连接管理
mysql 自带客户端工具
mysql 参数：
-u 用户 -p 密码 -h 主机 -S sock文件位置(仅本地) -P 端口
-e 免交互，在linux环境下执行mysql命令 < 数据库还原，sql脚本内默认指定了数据库
[root@db01 ~]# mysql -uroot -p1 -e "show processlist; "
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+--------------------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+-------+---------+------+----------+------------------+
| 31 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+--------------------+-------+---------+------+----------+------------------+
mysql启动关闭
1、辅助脚本方式启动，命令简单
sys-v: /etc/init.d/mysqld
/etc/init.d/mysqld start #通过安装目录下的脚本--/opt/mysql_cluster/mysql/bin/mysqld_safe调用bin/mysqld来启动数据库
systemctl:/etc/systemd/system/mysqld.service
systemctl start mysqld #直接使用/opt/mysql_cluster/mysql/bin/mysqld来启动
2、维护性的启动方式
/opt/mysql_cluster/mysql/bin/mysqld_safe #配置文件临时启动，可以定制参数如-skip-grant-tables
mysql初始化配置
1、预编译是进行配置，仅支持编译安装时配置
2、初始化配置文件
[root@db01 bin]# mysqld --help --verbose |grep "my.cnf" #mysql默认读取配置文件的顺序
/etc/my.cnf -- /etc/mysql/my.cnf --/usr/local/mysql/etc/my.cnf -- ~/.my.cnf
###如果mysql配置文件在以上目录存在相同参数，以顺序最后的参数生效，后面的配置参数覆盖前面的参数
3、命令行启动配置参数
##defaults-file=/opt/……，重启时指定配置文件，定制时只能通过mysqld和mysql_safe两个文件来启动
命令行参数优先级最高
4、初始化配置文件
影响范围：启动和客户端连接
1、通过参数影响mysqld_safe和mysqld两个脚本从而影响数据库启动
2、mysql mysqldump mysqladmin等
###配置格式
[程序名]
配置项=XXX
服务器端：[server][mysqld][mysqld_safe] #建议使用mysqld
客户端：[client][mysql][nysqldump] #统一参数写入mysql，其他的按需单独配置
####常用配置参数
[mysqld] #需要重启才能生效,所有位置参数必须逻辑存在
user=mysql
basedir=/opt/mysql_cluster/mysql
datadir=/data/mysql_data/
socket=/tmp/mysql.sock
server_id=6 #主从时使用
port=3306
log_error=/opt/mysql_cluster/mysql/logs/db01.err
log_bin=/data/mysql_data/mysql-bin #二进制日志，目录和文件名可以自定义
[mysql] #客户端配置，无需重启即可生效
mysql多实例
准备部署三个节点，首先创建对应安装及数据目录，准备配置文件
1、mkdir -p /data/330{7,8,9}/data
2、配置文件准备
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
3、初始化3套实例数据
mv /etc/my.cnf /etc/my.cnf.bak #为避免配置文件影响实例，先重名配置文件
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql_cluster/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql_cluster/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql_cluster/mysql
4、systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
vim mysqld3307.service
ExecStart=/opt/mysql_cluster/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/opt/mysql_cluster/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/opt/mysql_cluster/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
5、授权启动
chown -R mysql.mysql /data/*
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
6、验证多实例
netstat -lnp|grep 330
tcp6 0 0 :::3307 :::* LISTEN 6415/mysqld
tcp6 0 0 :::3308 :::* LISTEN 6449/mysqld
tcp6 0 0 :::3309 :::* LISTEN 6456/mysqld
unix 2 [ ACC ] STREAM LISTENING 120395 6415/mysqld /data/3307/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 126735 6449/mysqld /data/3308/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 126738 6456/mysqld /data/3309/mysql.sock
[root@db01 system]# mysql -S /data/3307/mysql.sock -e "select @@server_id" #通过socket登录
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
[root@db01 system]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
[root@db01 system]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
基础管理
用户管理
用户作用
登录验证，管理数据库对象
用户定义
用户名@'白名单' 白名单包含：
192.168.1.1 #允许指定ip地址已当前用户名连接我的数据库
192.168.1.% #允许这个段的ip地址访问，可以模块到最后一位，比如192.168.1.12%，默认24位掩码
192.168.0.0/255.255.255.0 #网段，掩码自定义
'%'、 #所有人
'baidu.com' #域名、
'localhost'、'db01' #主机名 需要解析
用户创建
create user wtl@'192.168.190.%' identified by '123';
select user,host,authentication_string from mysql.user;#用户查询，authentication_string密码
drop user wtl@'192.168.190.%';#删除用户
alter user root@'localhost' identified by '123';#修改用户名密码
权限管理
权限定义
#mysql的权限定义关键字
ALL:#所有权限，包含以下所有权限
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
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
#8.0 加人角色概念
#8.0授权变化
建用户和授权分开了
grant 不再支持自动创建用户了，不支持改密码
授权之前，必须要提前创建用户。
授权管理
mysql> grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123';
#权限范围说明：
*.* #所有库 所有表
wordpress.* #wordpress库下所有表，推荐授权方式
wordpress.t1 #wordpress库下的t1表
mysql> grant all on *.* to root@'172.16.190.1' identified by '1';#windows机器的navicat登录到linux中的MySQL，管理员用户
mysql> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';#创建一个应用用户app用户，能从windows上登录mysql，并能操作app库
mysql> show grants for app@'10.0.0.%';#查看授权
mysql> show grants for root@'172.16.190.1';
+------------------------------------------------------+
| Grants for root@172.16.190.1 |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.190.1' |
+------------------------------------------------------+
#root是管理员用户，root@localhost是超管，区别在于WITH GRANT OPTION，可以给别人授予权限的权限；
mysql> show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
revoke delete on app.* from app@'10.0.0.%'；###权限回收
#mysql权限是可以继承的，如果想取消某个账户的权限，只能回收不能重新授予较小权限
错误锦集
1130 - Host '172.16.190.1' is not allowed to connect to this MySQL server
mysql> select host,user from user;#查询用户及主机信息，出现该问题是因为没有连接权限，排查防火墙和权限
+--------------+---------------+
| host | user |
+--------------+---------------+
| % | root |
| 172.16.190.1 | root | #另外放网段时需要注意，建议指定ip
| localhost | mysql.session |
| localhost | mysql.sys |
+--------------+---------------+
浙公网安备 33010602011771号