碎梦的小破站

large picture of the cover

权限管理及远程连接和多实例

1. 数据库用户权限管理

show privileges;

Privilege -- 数据库所有权限

Context -- 权限控制的对象

Comment -- 对权限解释说明

Alter | Tables 表 | To alter the table 修改表信息（属性信息-结构 字符集 引擎索引 数据类型）

Create | Databases,Tables,Indexes 库 表 索引 | To create new databases and tables 可以拥有创建新库 新表 - 表中索引创建

Create user | Server Admin 具有服务管理能力 | To create new users 创建新用户

Delete | Tables 表 | To delete existing rows 删除表数据信息利

Drop | Databases,Tables 库 表 | To drop databases, tables, and views删除数据库 删除表 删除视图？？？

Index | Tables 表 | To create or drop indexes 在表中可以创建或删除索引信息

Insert | Tables 表 | To insert data into tables 插入/添加表数据信息权利

Select | Tables 表 | To retrieve rows from table 查看表中数据权限

Show databases | Server Admin 具有服务管理能力 | To see all databases with SHOW DATABASES 可以查看所有数据库信息

Update | Tables 表 | To update existing rows 修改表数据信息权利

Usage | Server Admin 具有服务管理能力 | No privileges - allow connect only 数据库服务登录权利（默认权限）

Grant option | Databases,Tables,Functions,Procedures 库 表 函数 存储过程| To give to other users those privileges you possess 可以给别人进行授权的权利

对于网站业务管理数据库需要的权利：select insert update delete （增删改查 -- CRUD）

1.1 权限设置语句

grant priv_type(权限信息)  ON 权限管理对象（库 表 -- *.*） to 用户信息（用户名@'白名单信息'）; 


grant Create  on *.* to xiaoA@'10.0.0.54';
-- xiaoA用户登录数据库后，可以具有创建任意数据库，任意数据表的权限;
grant Create  on oldboy.* to xiaoB@'10.0.0.54';
-- xiaoB用户登录数据库后，只具有创建oldboy数据权限，但是可以在oldboy数据库中创建任意表
grant Create  on oldboy.t1 to xiaoC@'10.0.0.54';
-- xiaoC用户登录数据库后，只具有创建oldboy数据权限，只能在oldboy数据库中创建t1表

grant Show databases on *.* to xiaoB@'10.0.0.54';

1.2 mysql中三个特殊权限

all privileges -- 设置所有权限
mysql> create user oldboy@'10.0.0.%' identified by '123';
mysql> grant all on *.* to oldboy@'10.0.0.%';

grant option   -- 设置给别的用户设置权限的权限
grant Grant option on *.* to oldboy@'localhost';
grant all on *.* to oldboy@'10.0.0.%' with grant option;

Usage          -- 数据库服务登录权利（默认权限）

1.3查看权限语句

方式一：执行show grants 命令查看权限
show grants for xiaoD@'%';

方式二：获取授权表信息--获取权限(了解)
user           ：全局授权信息  授权对象是所有库所有表
db             ：局部授权信息  授权对象是指定的数据库
tables_priv    ：局部授权信息  授权对象是指定的数据表

1.4 收回权限

revoke 回收的权限 on 回收权限对象（库名.表名）from 用户信息（用户名@'白名单信息'）;
mysql> revoke select on *.* from test01@'%';
Query OK, 0 rows affected (0.00 sec)

2. 数据库远程连接管理

2.1 本地连接

  • 需要设置好套接字文件信息
mysql -uroot -p123  -S 套接字文件信息（/tmp/mysql.sock）

2.2 远程连接

  • 需要设置好TCP(端口)/IP（地址）信息
mysql -uroot -p123  -h10.0.0.51 -P3308

数据库服务进行本地连接是有局限性--RDS（系统环境）

2.3 三种实现远程登录方式：

2.3.1 基于MySQL程序的客户端命令

mysql -- 用于连接登录数据库

mysqladmin -- 用于远程管理数据库（设置密码 管理服务运行状态）

mysqldump -- 用于远程备份数据库

mysql/mysqladmin/mysqldump -u用户信息 -p密码信息 -h数据库服务地址 -P端口信息

2.3.2 基于第三方的数据库远程工具

dbeaver -- 官方链接：https://dbeaver.io/ 免费

workbench -- 官方链接: https://www.mysql.com 免费

navicat -- 官方链接：https://www.navicat.com 付费--破解版

sqlyog -- 官方链接：https://webyog.com/product/sqlyog/ 付费

利用sqlyog连接报错，连接失败：

原因：因为连接的数据库服务是MySQL 8.0 最新版本数据库

  最新版本数据库密码加密方式变更了

早期版本：密码加密方式-- mysql_native_password -- 密文信息容易被破解（弱口令密码）

最新版本：密码加密方式-- caching_sha2_password

客户端 服务端

连接时--用户名（明文比较） 密码(密文) 用户信息 密码信息（密文）

加密方式--mysql_native_password caching_sha2_password

加密方式--caching_sha2_password mysql_native_password

修改加密方式：

select user,host,authentication_string,plugin from mysql.user\G                 --- 查看用户信息和用户密码，以及加密方式
alter user root@'10.0.0.%' identified with mysql_native_password by '123456';   --- 修改密码加密方式

2.3.3利用连接驱动程序配合程序语言代码 (偏开发-了解--数据库编程)

| 01 | python | 连接数据库驱动-pymysql |
| 02 | golang | 连接数据库驱动-gomysql |
| 03 | java | 连接数据库驱动-jar |
| 04 | php | 连接数据库驱动-phpmysql |

2.4 数据库服务无法进行远程连接，需要进行排查错误

步骤一：确认网络连通性

ping 数据库服务器地址 / telnet/nc/nmap 数据库服务器端口

PS：确认网络硬件设备配置 路由器 交换机 防火墙

步骤二：确认服务端配置

是否创建了用户信息和密码信息 白名单信息是否正确

select user,host from mysql.user; xiaoQ

步骤三：确认客户端配置

用户信息 密码信息（密码的加密方式）-h服务端地址 -P服务端端口

3. 数据库服务配置管理

3.1 数据库配置文件加载方式

3.1.1加载配置文件路径信息

/etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/etc/my.cnf -> ~/.my.cnf

3.1.2手工指定加载配置信息

mysqld_safe --defaults-file=/data/xiaoQ.cnf &
mysqld --defaults-file=/data/xiaoQ.cnf  &
mysql  --defaults-file=/data/xiaoQ.cnf

3.2 数据库配置文件编写方法

3.2.1 客户端标签

[mysql] [mysqladmin] [mysqldump]    [client]--全局标签
标签下面会配置客户端的配置信息，可以简化客户端命令参数操作，可以和服务端建立连接

[mysql]
socket=/tmp/mysql.sock

mysql --help      -- 显示所有的客户端标签配置项
mysqladmin --help -- 显示所有的客户端标签配置项
mysqldump  --help -- 显示所有的客户端标签配置项

3.2.2 服务端标签

[mysqld] [mysqld_safe]              [server]--全局标签
标签下面会配置客户端的配置信息，可以永久激活服务端某些功能

[mysqld]
user=mysql                 --- 指定数据库服务进程用户
basedir=/usr/local/mysql   --- 加载的程序目录信息  (和升级数据库服务有关)      
datadir=/data/3306/data    --- 加载数据库数据目录  (重新加载备份信息)
socket=/tmp/mysql.sock     --- 实现数据库本地连接
port=3307                  --- 修改数据库服务默认端口
super_read_only=on         --- 设置管理员管理数据库只读操作    mysqld_safe --user=mysql --basedir=
read_only=on

mysqld --help --verbose

3.3 socket信息

客户端和服务端建立本地连接，需要配合正确socket信息

方式一：客户端连接指定-S信息
mysql -uroot -pxiaoQ123 -S /tmp/mysql01.sock

[mysql]
socket=/tmp/mysql01.sock

4. 数据库服务实例应用

4.1 什么是数据库实例

功能应用方面：在一个系统环境中，运行启动多个数据库服务的进程

资源利用方面：一个程序运行之后，程序运行的后的进程以及程序加载数据信息独占内存资源

MySQL实例 = mysqld + master thread监控管理 + 具体干活的thread + 预分配的内存结构

构建多实例功能：

1）可以节省硬件成本

2）可以保证服务稳定

3）可以便于监控管理

4.2 如何搭建数据库多实例

4.2.1 下载数据库服务程序

mysql 8.0  mysql-8.0.32-linux-glibc2.12-x86_64.tar.gz
mysql 5.7  mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
mysql 5.6  mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz

4.2.2 安装数据库服务程序

mysql 8.0 省略
mysql 5.7 
tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz 
ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql57

mysql 5.6
tar xf mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.6.48-linux-glibc2.12-x86_64 mysql56

4.2.3 构建多实例环境（4不同-数据目录 配置文件 端口 socket）创建不同的存储目录

mkdir -p /data/3306/data && chown mysql.mysql /data/3306/data
mkdir -p /data/3357/data && chown mysql.mysql /data/3357/data
mkdir -p /data/3356/data && chown mysql.mysql /data/3356/data
或者
mkdir /data/3380/data /data/3357/data /data/3356/data -p
chown -R mysql.mysql /data



创建不同的配置文件，配置文件中端口不同 socket信息不同
cat >>/data/3380/my.cnf<<EOF
[client]
socket=/tmp/mysql.sock
[mysqld]
port=3380
user=mysql
basedir=/usr/local/mysql
datadir=/data/3380/data
socket=/tmp/mysql.sock
EOF

cat >>/data/3357/my.cnf<<EOF
[mysqld]
port=3357
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3357/data
socket=/tmp/mysql57.sock
EOF

cat >>/data/3356/my.cnf<<EOF
[mysqld]
port=3356
user=mysql
basedir=/usr/local/mysql56
datadir=/data/3356/data
socket=/tmp/mysql56.sock
EOF

4.2.4 进行数据库服务初始化过程

mv /etc/my.cnf /tmp/
mysqld --initialize-insecure --user=mysql --datadir=/data/3380/data --basedir=/usr/local/mysql 
/usr/local/mysql57/bin/mysqld  --initialize-insecure --user=mysql --datadir=/data/3357/data --basedir=/usr/local/mysql57 
/usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/data/3356/data --basedir=/usr/local/mysql56

4.2.5 启动多实例环境数据库服务

命令方式启动：mysqld mysqld_safe systemctl 
mysqld --defaults-file=/data/3380/my.cnf &
/usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf &
/usr/local/mysql56/bin/mysqld --defaults-file=/data/3356/my.cnf &

systemctl  start  mysql80  
systemctl  start  mysql57 
systemctl  start  mysql56


cat >>/etc/systemd/system/mysql80.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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3380/my.cnf
LimitNOFILE=5000
EOF

cat >>/etc/systemd/system/mysql57.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=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf
LimitNOFILE=5000
EOF

cat >>/etc/systemd/system/mysql56.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=/usr/local/mysql56/bin/mysqld --defaults-file=/data/3356/my.cnf
LimitNOFILE=5000
EOF

[root@db-01 local]# systemctl daemon-reload
[root@db-01 local]# systemctl start mysql80
[root@db-01 local]# systemctl start mysql57
[root@db-01 local]# systemctl start mysql56
[root@db-01 local]# netstat -lntup|grep mysql

4.2.6 多实例本地连接登录

mysql -uroot -S /tmp/mysql.sock
mysql -uroot -S /tmp/mysql57.sock
mysql -uroot -S /tmp/mysql56.sock

远程连接登录
mysql -uroot -p -h10.0.0.51 -P3306
mysql -uroot -p -h10.0.0.51 -P3357
mysql -uroot -p -h10.0.0.51 -P3356

5. 数据库服务语句分类

SQL语句 - 英文全称为Structured Query Language，中文意思是结构化查询语言（属于编程语言）；

在使用SQL语句时，也会用到几种常用的标准：SQL 89 SQL 92（命令语法过于繁琐，不便于阅读） SQL 99 （命令语法简洁 便于阅读）

5.1 DDL Data Definition Language 数据定义语言

慎用-损坏数据 影响业务

5.1.1 概念

负责管理数据库的基础数据（不会对表的内容修改），比如增删库、增删表、增删索引、增删用户等；

###5.1.2 涉及语句

CREATE（创建）、ALTER（修改）、DROP（删除）`等；

5.2 DCL Data Control Language 数据控制语言

5.2.1 概念

主要用来定义访问权限和安全级别

5.2.2 涉及语句

`GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、ROLLBACK（回滚）

5.3 DML Data Manipulation Language 数据操作语言

慎用-数据损坏-备份信息做恢复

5.3.1概念

主要针对数据库里的表里的数据进行操作，用来定义数据库记录（数据）；

5.3.2 涉及语句

`SELECT（查）、INSERT（增）、DELETE（删）、UPDATE（改）`

5.4 DQL Data Query Language 数据查询语言

5.4.1 概念

主要用来查询记录（数据）

5.4.2 涉及语句

SELECT（查）数据库变量查询  数据库函数信息  数据库表信息查看（单表信息查看  连表信息查看  子查询/嵌套查看） 数据库服务元数据信息查看
基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块

#6. 数据库服务字符设置（了解）

6.1 设置字符集作用

避免表中中文数据出现乱码（5.6 5.7）

6.2 设置修改字符集

永久修改：
vim /etc/my.cnf 
[mysqld]
character-set-server=utf8mb4    -- 修改旧版本数据库服务（5.7 5.6 5.5 8.0）
[mysql]
default-character-set=utf8mb4   -- 修改新版本数据库服务（8.0）

临时修改：
set global character_set_server='utf8mb3'  -- 临时修改

6.3 已经出现乱码信息，如何处理

6.3.1 步骤一：需要进行数据库备份（逻辑备份）

mysqldump

6.3.2 步骤二：将数据库字符集进行修改

永久修改/临时修改

6.3.3 步骤三：需要删除库或者表信息

6.3.4 步骤四：创建数据库或者数据表

6.3.5 步骤五：恢复迁移数据

utf8mb3：最多存储3字节长度字符 张-3字节 name -- 老 -- 3字节 24bit 000000000000000000000010 男

utf8mb4：最多存储4字节长度字符（表情字符emoji） 张-4字节 老 4字节 32bit

site logo

欢迎来到碎梦的小破站！

© 2025 碎梦的小破站