MySQL基础管理与多实例安装

最新推荐文章于 2022-10-27 23:28:13 发布

一个憨豆

最新推荐文章于 2022-10-27 23:28:13 发布

阅读量226

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_52636854/article/details/114627245

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

MySQL基础管理

一、用户管理

1.用户的作用

linux用户：
	登录linux系统
	管理linux对象：linux系统中一切皆文件
MySQL用户：
	登录MySQL数据库
	管理MySQL逻辑对象：表

2.用户的定义

linux用户：用户名
MySQL用户：用户名@'白名单'
	所谓的白名单（就是地址列表允许白名单的ip登录MySQL，管理MySQL）

hhh@'localhost' : hhh用户能够通过本地登录MySQL（socket）
hhh@'10.0.0.10' ：hhh用户能够通过10.0.0.10远程登录MySQL服务器
hhh@'10.0.0.%' : hhh用户能够通过10.0.0.x/24远程登录MySQL服务器
hhh@'10.0.0.5%' : hhh用户能够通过10.0.0.50-59远程登录MySQL服务器

3.用户管理之增删改查（5.7.33）

3.1：查

#查询用户：
mysql> select user,host from mysql.user;

#查询用户信息
mysql> select user,host,authentication_string from mysql.user;

#查看表结构：
方法一：
mysql> desc mysql.user\G

方法二：
mysql> use mysql;
mysql> desc user;

3.2：增

#增加用户：
mysql> create user hhh@'localhost' identified by 'Aa.123456';

#创建用户并设置密码
mysql> create user hhh@'10.0.0.%' identified by 'Aa.123456';

#mysql的配置文件（/etc/my.cnf）
#mysql的数据文件：/var/lib/mysql/mysql
#查看mysql的用户信息（/var/lib/mysql/mysql/user.MYD）
strings user.MYD

3.3：改

#修改用户密码：
mysql> alter user hhh@'10.0.0.%' identified by 'Aa.123456.';

ps:修改完密码，再次登录mysql
1.root用户：mysql -p
2.非root用户：mysql -u hhh -p

3.4：删

#删除用户：
mysql> drop user hhh@'localhost';

****注意：8.0版本以前，是可以通过grant命令建立用户＋授权

二、权限管理

1.权限的作用

用户对数据库对象，有哪些管理能力。

#linux系统当中，权限是作用在文件上的
#MySQL的权限是作用在用户的，权限是给用户设定的

2.权限的表现方式：

增删改查的具体命令
DQL
DDL
DML
DCL

#查看所有可设置的权限列表：
mysql> show privileges;

3.授权和回收权限操作

#语法：
（8.0版本以前）
	grant 权限 on 对象 to 用户 identified by '密码' with grant option；
ps：这里的权限可以有好多个，权限之间用逗号隔开

（8.0版本以后 ）
	create user 用户 identified by '密码'
	grant 权限 on 对象 to 用户；

ps：
这里的对象指的是：库或者表
对象范围：
	*.*    :     相当于------> chmod -R 755/        ：管理员
	hsy.*  :     相当于------> chmod -R 755/hsy     ：普通用户
	hsy.x1 :     相当于------> chmod -R 755/hsy/x1  ：用的少

3.1权限的介绍

ALL                    ：管理员
权限1，权限2，权限3...    ：普通用户（业务用户，开发用户）
Grant option           ：给别的用户授权

3.1.1授权的实例

例1：创建并授权一个管理员用户hsy，能够通过10.0.0.%网段，登录并管理数据库
8.0版本以前
	mysql> grant all on *.* to hhh@'10.0.0.%' identified by 'Aa.123456' with grant option;
#查询建立的用户：
	mysql> select user,host from mysql.user;
#查询用户的权限：
方法一：
	mysql> show grants for hhh@'10.0.0.%';   ----->单个用户
方法二：
	mysql> select * from user \G             ----->所有用户
\G:格式化输出

#查看本地管理员的权限：
	mysql> show grants for root@'localhost';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S6xCh1vJ-1617956266702)(C:\Users\86150\Desktop\mysql\image-20210225164344816.png)]

例2：创建并授权一个hhh@'10.0.0.%' 业务用户，能够对hhh库下所有对象进行create，select，update，delete，insert操作。
	mysql> grant create,update,select,insert,delete on hhh.* to hhh@'10.0.0.%' identified by 'Xx.123456';

#查询权限
	mysql> show grants for hhh@'10.0.0.%';

#刷新mysql的权限：
	mysql> flush privileges;

#建库
	mysql> create database hhh charset utf8;

#基于表在tables_priv中查询权限“
	mysql> select * from tables_priv;

#基于库在db中查询权限：
	mysql> select * from db\G

#扩展

MySQL授权表：
MySQL库下：
	user                针对全局
	db                  针对库
	tables_priv         针对表
	columns_priv        针对列

3.2回收权限

revoke
linux：
	chmod -R 644 /hhh  ------>  chmod -R 755 /hhh
MySQL：
	MySQL中不能重复授权，只能通过回收授权进行修改
#回收权限的命令语句：
	mysql> revoke create on hhh.* from 'hhh'@'10.0.0.%';
	mysql> show grants for hhh@'10.0.0.%';

3.3超级管理员忘记密码，处理方法

1.关闭数据库
	systemctl stop mysqld
2.使用安全模式启用
	mysqld_safe --skip-grant-tables --skip-networking
或者
	service mysqld start --skip-grant-tables --skip-networking
3.登录数据库并修改密码
	alter user root@'localhost' identified by '*****'
ps：此时会报错，因为取消了验证，解决方法
	flush privileges；（手动加载授权表）
再次执行alter
	alter user root@'localhost' identified by '*****'
4.重启数据库到正常模式
	service mysqld restart

MySQL中负责密码验证的部分是连接层
链接协议，socket
用户验证
链接线程

--skip-grant-tables:跳过授权表
--skip-networking:跳过TCP/IP连接

#启动数据库 无密码
	[root@db01 ~]# service mysqld start --skip-grant-tables

find / -name ‘mysqld’

三、MySQL连接管理

1.三种连接管理

1.MySQL自带客户端
2.MySQL远程客户端程序
3.程序链接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LohTV8jS-1617956266704)(C:\Users\86150\Desktop\mysql\image-20210225193335501.png)]

1.1MySQL自带客户端

#程序文件（客户端的程序文件是通用的）
[root@db01 ~]# which mysql
/usr/bin/mysql

1.mysql
参数列表：
	-u    用户名
	-p    密码
	-S    本地socket文件位置
	-h    数据库ip地址
	-P    数据库端口号
	-e    免交互执行数据库命令
	<     导入sql脚本
socket：
	前提：数据库中必须实现授权hhh@'localhost'用户
	mysql -u hhh -p123 -S /tmp/mysql.sock(客户端的socket文件位置，默认在此目录下)
  	mysql -uhhh -p -S /tmp/mysql.sock
 	mysql -p123 -S /tmp/mysql.sock
  	mysql
 	mysql -uroot -p123

tcp/ip：
	前提：必须提前创建好，可以远程连接的用户（例如：hhh@'10.0.0.%'）
	mysql -uhhh -p123 -h 10.0.0.51 -p 3306(不加-P，默认端口就是3306)
	mysql -uhhh -p123 -h 10.0.0.51
	mysql -uhhh -p -h 10.0.0.51 -p 3306

举个例子：

例1：授权hhh管理用户，可以通过本地socket登录
	1.创建hhh用户
		mysql> grant all on *.* to hhh@'localhost' identified by 'Aa.123456';
	2.登录测试：
		[root@db01 ~]# mysql -uhhh -pAa.123456 -S /tmp/mysql.sock

例2：授权hhh管理用户，可以通过远程登录
	1.创建hhh用户
		mysql> grant all on *.* to hhh@'10.0.0.%' identified by 'Aa.123456';
	2.登录测试：
		[root@db01 ~]# mysql -uhhh -pAa.123456 -h 10.0.0.51 -P 3306

#查看用户的连接线程
	mysql> show processlist;	
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host       | db   | Command | Time | State    | Info             |
+----+------+------------+------+---------+------+----------+------------------+
|  6 | root | localhost  | NULL | Sleep   |  575 |          | NULL             |
|  7 | hhh  | db01:34468 | NULL | Query   |    0 | starting | show processlist |
+----+------+------------+------+---------+------+----------+------------------+

#-e的应用：取值
方法一：
	mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

方法二：在非交互式情况下取值（常用于监控）
	[root@db01 ~]# mysql -uroot -pAa.123456 -e "select @@innodb_flush_log_at_trx_commit";
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+

# < 号的应用：
	[root@db01 ~]# mysql -uroot -p123 < /root/word.sql

1.2MySQL远程客户端程序（开发工具）

前提：必须提前创建好，可以远程连接的用户（例如：hhh@'10.0.0.%'）

1.3程序链接

#PHP的连接数据库的程序
	yum install -y php-mysql
#python
	pip3 install pympsql

四、MySQL初始化配置管理

1.方式

1.源码安装------>编译过程中设置初始化参数
2.配置文件-------->数据库启动之前，设定配置文件参数  /etc/my/cnf
3.启动脚本命令行--->mysql_safe --skip-grant-tables --skip-networking

说明：my.conf  -----> port=3306
     mysqld_safe --> port=3307

2.配置文件的应用

1.配置文件读取顺序
[root@db01 ~]# mysqld --help --verbose | grep my.cnf
/etc/my.cnf               port：3306
/etc/mysql/my.cnf 
/usr/etc/my.cnf 
~/.my.cnf                 port：3360

ps:意外情况
手动定制配置文件的位置点： /opt/my.cnf, /data/3306/my.cnf , /data/3307/my.cnf . /data/3308/.cnf
mysqld       --defaults-file=/opt/my.cnf
mysqld_safe  --defaults-file=/opt/my.cnf

2.配置文件的书写格式
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

ps:带[]的区别
[标签]
配置参数

#标签的作用：
标签是用来区别不同程序运行参数
[服务器端]：负载数据库服务端运行参数设定
[mysqld]
[mysqld_safe]
[server]

[客户端]:只影响本地客户端连接，不影响远程客户端
[mysql]
[mysqldump]
[client]

配置文件解读

[mysqld]                        #服务器端标签
user=mysql                      #负责数据库管理用户
basedir=/app/database/mysql     #软件的安装位置
datadir=/data/3306              #数据的存放位置
server_id=6                     #标识节点的唯一编号（总从有用）
port=3306                       #端口号
socket=/tmp/mysql.sock          #套接字文件
[mysql]                         #客户端标签
socket=/tmp/mysql.sock          #读取socket文件的位置点

五、MySQL启动与关闭

1.多种启动方式介绍

以上多种方式，都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库

1.1mysqld_safe和mysqld,可以在启动数据库时，加入自己执行的参数，例如：

--skip-grant-tables

--skip-networking

--defaults-file=/opt/my.cnf

2.关闭方法

systemctl stop mysqld
service mysqld stop
/etc/init.d/mysqld stop
mysqladmin -uroot -p*** shutdown
mysql -uroot -p*** -e "shutdown"

3. 初始化配置

3.1 作用

控制MySQL的启动
影响到客户端的连接

3.2 初始化配置的方法

预编译
**配置文件(所有启动方式)**
命令行参数 (仅限于 mysqld_safe mysqld)

3.3初始配置文件

初始化配置文件的默认读取路径
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
注:
默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.

3.4 配置文件的书写方式

#[标签]
配置项=xxxx

标签类型：服务端、客户端
服务器端标签：
[mysqld]
[mysqld_safe]
[server]

#客户端标签：
[mysql]
[mysqldump]
[client]

#例：配置文件：
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>

六、MySQL同版本多实例

1.准备不同目录并配置环境变量

[root@db01 ~]# mkdir -p /data/mysql/330{7..9}

[root@db01 ~]# vim /etc/profile.d/mysql.sh 
export PATH=/service/mysql/bin:$PATH

2.准备配置文件

vim /data/mysql/conf/my.3307.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/data/mysql.sock
server_id=7
port=3307

vim /data/mysql/conf/my.3308.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/data/mysql.sock
server_id=8
port=3308

vim /data/mysql/conf/my.3309.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/data/mysql.sock
server_id=9
port=3309

3.初始化三套数据 ,并授权

`开始初始化
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3308/data
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3309/data


`启动数据库
根据配置文件，后台启动多实例数据库
[root@db01 ~]# mysqld --defaults-file=/data/mysql/conf/my.3307.cnf --user=root &
[root@db01 ~]# mysqld --defaults-file=/data/mysql/conf/my.3308.cnf --user=root &
[root@db01 ~]# mysqld --defaults-file=/data/mysql/conf/my.3309.cnf --user=root &

` 关闭数据库
[root@db02 ~]# mysqladmin -uroot -p -S /data/mysql/3307/data/mysql.sock shutdown
[root@db02 ~]# mysqladmin -uroot -p -S /data/mysql/3308/data/mysql.sock shutdown
[root@db02 ~]# mysqladmin -uroot -p -S /data/mysql/3309/data/mysql.sock shutdown


#使用tree可以查看
[root@db01 scripts]# tree -L 3 /data

`检查启动
[root@db01 scripts]# netstat -lntup|grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      25550/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      25722/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      25894/mysqld

4.多实例设置密码

[root@db01 scripts]# mysqladmin -uroot -S /data/mysql/3307/data/mysql.sock 
password '123'
[root@db01 scripts]# mysqladmin -uroot -S /data/mysql/3308/data/mysql.sock 
password '123'
[root@db01 scripts]# mysqladmin -uroot -S /data/mysql/3309/data/mysql.sock 
password '123'

5.多实例验证

[root@db01 generator.late]# mysql -S /data/mysql/3307/data/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@db01 generator.late]# mysql -S /data/mysql/3308/data/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@db01 generator.late]# mysql -S /data/mysql/3309/data/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

6.登录连接多实例数据库

[root@db02 ~]# mysql -uroot -p -S /data/mysql/3308/data/mysql.sock 
[root@db02 ~]# mysql -uroot -p -S /data/mysql/3308/data/mysql.sock 
[root@db02 ~]# mysql -uroot -p -S /data/mysql/3308/data/mysql.sock

7.多种方式连接多实例数据库

1）快速连接

[root@db02 ~]# echo "mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock " > /usr/bin/mysql3307
[root@db02 ~]# echo "mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock " > /usr/bin/mysql3308
[root@db02 ~]# echo "mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock " > /usr/bin/mysql3309

#授权
[root@db02 ~]# chmod +x /usr/bin/mysql330*

#验证
[root@db02 ~]# mysql3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2）用system管理连接

1.编写mysql多实例的unit文件

给每个实例编写一个unit文件，使用systemd来管理每个实例的启动、关闭等

--配置3307实例

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3307.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=https://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/mysql/3307/my.cnf

LimitNOFILE = 5000

--配置3308实例

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3308.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=https://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/mysql/3308/my.cnf

LimitNOFILE = 5000

--配置3309实例

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3309.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=https://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/mysql/3309/my.cnf

LimitNOFILE = 5000

2. 重载系统的unit文件

systemctl daemon-reload

3. 启动mysql多个实例

systemctl start mysql3307.service 
systemctl start mysql3308.service 
systemctl start mysql3309.service

七、不同mysql版本多实例

1.软件准备

准备各个不同版本的数据库，进行安装，初始化
1.`防止影响初始化，修改  /etc/my.cnf  名字`
[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak

2.做软连接不同版本软件，修改环境变量

[root@db01 ~]# ln -s /service/mysql-5.6.46 /service/mysql56
[root@db01 ~]# ln -s /service/mysql-5.7.20 /service/mysql
[root@db01 ~]# ln -s /service/mysql-8.0.18 /service/mysql80

3.准备多实例的目录以及各个mysql的配置文件,修改属主/组

[root@db01 ~]# mkdir -p /data/331{7..8}/data
[root@db01 ~]# mkdir -p /binlog/331{7..8}

[root@db01 ~]# cat /data/3317/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3317/data
socket=/data/3317/mysql.sock
log_error=/data/3317/mysql.err
port=3317
server_id=17
log_bin=/binlog/3317/mysql-bin

[root@db01 ~]# cat /data/3318/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3318/data
socket=/data/3318/mysql.sock
log_error=/data/3318/mysql.err
port=3318
server_id=18
log_bin=/binlog/3318/mysql-bin

[root@db01 ~]# chown -R mysql.mysql /data

4.初始化数据

1.初始化mysql-5.6版本的多实例
[root@db01 ~]# /service/mysql56/scripts/mysql_install_db --user=mysql --datadir=/data/3317/data --basedir=/service/mysql56
2.初始化mysql-8 版本的多实例
[root@db01 ~]# /service/mysql80/bin/mysqld -- initialize-insecure --user=mysql --datadir=/data/3318/data --basedir=/service/mysql80

5.修改启动脚本 — systemctl启动

#1. 5.6版本
[root@db01 ~]# cat /etc/systemd/system/mysql3317.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://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=/service/mysql56/bin/mysqld --defaults-file=/data/3317/my.cnf
LimitNOFILE = 5000

#2. 8.0版本
[root@db01 ~]# cat /etc/systemd/system/mysql3318.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://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=/service/mysql56/bin/mysqld --defaults-file=/data/3318/my.cnf
LimitNOFILE = 5000

6.systemctl启动5.6 8.0 数据库

[root@db01 ~]# systemctl start mysql3317
[root@db01 ~]# systemctl start mysql3318

7.mysql连接各个不同版本实例mysql

1.恢复mysql的环境变量
[root@db01 ~]# vim /etc/profile.d/mysql.sh 
export PATH=/service/mysql/bin:$PATH
[root@db01 ~]#  source /etc/profile
2.连接去其他版本数据库的socket文件
[root@db01 ~]# mysql -S /data/3317/mysql.sock
显示的是mysql 5.6 版本的数据库

[root@db01 ~]# mysql -S /data/3318/mysql.sock
显示的是mysql 8.0 版本的数据库

3。如何用5.6版本与8.0版本连接其他版本实例？
用绝对路径去连接，但是5.6连接8.0版本的sock文件，会失败。
[root@db01 ~]# /service/mysql56/bin/mysql -S /

一个憨豆

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础管理与多实例安装

一、用户管理1.用户的作用linux用户：登录linux系统管理linux对象：linux系统中一切皆文件MySQL用户：登录MySQL数据库管理MySQL逻辑对象：表2.用户的定义linux用户：用户名MySQL用户：用户名@'白名单' 所谓的白名单（就是地址列表允许白名单的ip登录MySQL，管理MySQL）hhh@'localhost' : hhh用户能够通过本地登录MySQL（socket）hhh@'10.0.0.10' ：hhh用户能够通过10.0.0.10远
复制链接

扫一扫

专栏目录