2、MySQL基础管理

于 2020-07-18 17:31:27 发布
阅读量196 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/wxiaomimang/article/details/107425443

MySQL基础管理

1.用户管理

1.1作用

Linux ：
登录系统
管理操作系统对象（文件）
MySQL ：
登录数据库
管理数据库对象（表）

1.2 用户的定义方式（长成啥样子？）

Linux ：
用户名： 字母、数字、特殊符号等组合
MySQL ：
用户名@‘白名单’
白名单？
一个或者多个IP的列表。作用是，在列表中存在的IP才能连接数据库。
oldguo@‘localhost’
oldguo@‘10.0.0.52’
oldguo@‘10.0.0.%’
oldguo@‘10.0.0.5%’
oldguo@‘10.0.0.0/255.255.254.0’
oldguo@’%’

1.3 用户的管理

1.3.1 查询：
5.6版本:
select user,password,host from mysql.user;

5.7 版本中用户基本信息
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

mysql> select user,host,authentication_string ,plugin  from mysql.user;
+---------------+-----------+-------------------------------------------+-----------------------+
| user          | host      | authentication_string                     | plugin                |
+---------------+-----------+-------------------------------------------+-----------------------+
| root          | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+---------------+-----------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

# 如果记不住以上单词，可以执行以下命令获得
mysql> desc mysql.user;

1.3.2 创建用户：
需求： 通过 oldguo 用户，密码123 ，从10网段任意地址登陆MySQL
mysql> create user oldguo@'10.0.0.%' identified by '123';
mysql> select user,host,authentication_string ,plugin  from mysql.user;

1.3.3 修改用户 
mysql> alter user oldguo@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#5.6版本修改密码
set password for test@'10.0.0.%' = PASSWORD('密码')；

1.3.4 删除用户 
mysql> drop user oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

2.权限管理

2.1 作用

控制用户，能够对数据库对象，做哪些操作.

2.2 定义方式

Linux 权限： r  w  x 
MySQL 权限： 命令即权限。

说明： 
	特殊的权限ALL：代表了以上所有权限结合，除了Grant option（给别人授权的功能）。
	一般管理员用户会具备ALL，其他普通用户按需提供相应权限，一般DROP种类的权限不会给普通用户。

2.3 权限的管理

2.3.1 权限查询

MySQL中权限是属于用户的属性。
mysql> show grants for root@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

2.3.2 授权

#语法
GRANT 权限  ON 作用范围  TO 用户  IDENTIFIED BY '密码';

作用范围 ? 
*.*         : 全局范围          chmod -R  /
wordpress.* : 单库范围          chmod -R  /wordpress
wordpress.t1：单表范围          chmod -R  /wordpress/a.txt

#管理员用户授权： 
	mysql> grant all on *.* to oldboy@'10.0.0.%' identified by '123';
#普通用户授权： 
	mysql> grant select,update,delete,insert   on test.* to test@'10.0.0.%' idenfied by '123';

2.3.3 回收权限

注意： 不能通过重复授权修改权限，因为授权都是相加的关系。
mysql> revoke delete on test.*  from 'test'@'10.0.0.%';

2.3.4彩蛋：

8.0 版本之后在 用户、权限管理方面的变化
<1. 必须先建好用户，再授权,grant 命令不再支持自动建用户，不支持设置密码。
<2. 建用户，密码加密插件发生变化，改为 ：caching_sha2_password，有很多场景，修改修改为： mysql_native_password 
比如： 第三方工具、主从、高可用、备份软件等。。。
#修改命令
mysql> alter user oldboy@'10.0.0.%' identified with mysql_native_password by '123';
<3. 8.0 加入了role(角色)的概念。就是权限的组合。

3.MySQL连接管理

3.1 自带客户端命令

# mysql 命令的使用
-u    用户名
-p    密码
-S    Socket文件
-h    IP地址 
-P    端口号
-e    免交互执行命令
<     导入SQL脚本


## IP地址连接串 (tcpip连接方法)
IP  Port  user  passwd
mysql -u用户 -p密码  -h地址 -P端口
前提： 
	1. IP和端口正确的。
	2. 提前创建好可以远程登录的用户

[root@db01 data]# mysql -uoldboy -p123 -h10.0.0.51 -P3306
[root@db01 data]# mysql -uoldboy -p123 -h10.0.0.51 


## 本地Socket文件连接
前提： 
	1、 设置正确的Socket文件位置
	2、 提前创建好localhost相关用户 
	3、 只提供给数据库本地连接的特殊方法
	
[root@db01 data]# mysql -uroot -p -S /tmp/mysql.sock

3.2 开发工具

sqlyog 
navicat
workbench
3.3 应用连接

4.初始化配置文件

4.1 作用

<1. 影响到数据库的启动、日常工作。
<2. 影响到客户端连接（本地服务器上发起的）。

4.2 文件格式

[root@db01 ~]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock


[标签项] ： 用来表示不同的程序
服务端： [mysqld] [mysqld_safe] [server]
客户端:  [mysql]  [mysqldump]   [client]
配置=xxx ： 设置的参数键值对

user=mysql                 # 数据库管理用户
basedir=/data/app/mysql    # 程序路径
datadir=/data/3306/data    # 数据路径
socket=/tmp/mysql.sock     # socket文件位置

4.3 配置文件读取顺序

# 默认配置文件读取顺序
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ---》 /etc/mysql/my.cnf ---》 /usr/local/mysql/etc/my.cnf ----》 ~/.my.cnf 
建议： 每个数据库保留一个配置文件。

彩蛋： 
	如果有多个配置文件，例如 
		/etc/my.cnf  ---> port=3306
		~/.my.cnf    ---> port=3308
	数据库启动时，port是多少？3308最后一个读取的文件是多少就是多少

# 手工定义配置文件读取路径
mysqld  --defaults-file=/opt/my1.cnf  &
mysqld_safe --defaults-file=/opt/my2.cnf  &

4.4 root本地管理员的密码忘记？

<1. 停数据库
[root@db01 tmp]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 

<2. 启动数据库到“安全”模式
mysqld_safe --skip-grant-tables --skip-networking &


参数作用： 
	 --skip-grant-tables ： 跳过授权表，不开启验证功能。
	 --skip-networking   :  阻止所有TCP/IP网络连接。

<3. 改密码
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';

<4. 重启至正常模式
[root@db01 tmp]# /etc/init.d/mysqld restart

5.MySQL的启动关闭

MySQL启动关闭

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

多实例应用-同版本的

6.1 创建相关目录

#创建数据文件目录和日志目录
[root@db01 ~]# mkdir -p /data/330{7..9}/data  /data/330{7..9}/logs

6.2 准备配置文件

cat >/data/3307/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql
datadir=/data/3307/data 
server_id=7
port=3307
log_bin=/data/3307/logs/mysql-bin
socket=/tmp/mysql3307.sock
EOF

cat >/data/3308/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql
datadir=/data/3308/data 
server_id=8
port=3308
log_bin=/data/3308/logs/mysql-bin
socket=/tmp/mysql3308.sock
EOF


cat >/data/3309/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql
datadir=/data/3309/data 
server_id=9
port=3309
log_bin=/data/3309/logs/mysql-bin
socket=/tmp/mysql3309.sock
EOF

6.3 授权

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

6.4 初始化数据

#将/etc/my.cnf配置移走在初始化配置，因为这个文件的优先级高，待会在移动回来	
mv /etc/my.cnf /etc/my.cnf.bak

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3307/data 
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3308/data 
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3309/data 

mv /etc/my.cnf.bak  /etc/my.cnf

6.5 启动多实例

[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01 ~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 ~]# mysqld_safe --defaults-file=/data/3309/my.cnf &

6.6使用navicat连接多实例

#创建远程连接用户
#交互式创建用户
mysql -S /tmp/mysql3307.sock 
grant all on *.* to root@'10.0.0.%' identified by '123';
#非交换式创建用户  加-e参数即可
mysql -S /tmp/mysql3308.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123';"
mysql -S /tmp/mysql3309.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123';"

6.7配置多实例systemd

cat > /etc/systemd/system/mysqld3307.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=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF


cat > /etc/systemd/system/mysqld3308.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=/data/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF


cat > /etc/systemd/system/mysqld3309.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=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF


#启动服务
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309

7.多版本多实例应用

7.1 上传软件、解压、软连接

#软连接名称，5.7版本的上面已经安装上传过了
5.6版本：mysql56		8.0版本：mysql80

tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz 
tar xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz 

ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql80
ln -s mysql-5.6.46-linux-glibc2.12-x86_64 mysql56

7.2 创建相关目录

#3317目录存放5.6版本         3318目录存放8.0版本
mkdir -p /data/331{7..8}/data  /data/331{7..8}/logs

7.3 准备配置文件

cat >/data/3317/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql56
datadir=/data/3317/data 
server_id=17
port=3317
log_bin=/data/3317/logs/mysql-bin
socket=/tmp/mysql3317.sock
EOF

cat >/data/3318/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql80
datadir=/data/3318/data 
server_id=18
port=3318
log_bin=/data/3318/logs/mysql-bin
socket=/tmp/mysql3318.sock
EOF

7.3 授权

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

7.4 初始化数据

mv /etc/my.cnf /etc/my.cnf.bak

vim /etc/profile 
#注释环境变量：因为版本不同所有环境变量注释掉 
# export PATH=/data/app/mysql/bin:$PATH 

# 最好重连一个Xshell窗口使其注释生效，使用mysql -V检测下
[root@db01 ~]# mysql -V
-bash: mysql: command not found

# 5.6初始化
/data/app/mysql56/scripts/mysql_install_db  --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3317/data 
# 8.0初始化 
/data/app/mysql80/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql80 --datadir=/data/3318/data

7.5 配置systemd管理

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

cat > /etc/systemd/system/mysqld3318.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=/data/app/mysql80/bin/mysqld --defaults-file=/data/3318/my.cnf
LimitNOFILE = 5000
EOF

7.6 连接测试

#启动
systemctl start mysqld3317
systemctl start mysqld3318
#连接
[root@db01 app]# /data/app/mysql56/bin/mysql -S /tmp/mysql3317.sock 
[root@db01 app]# /data/app/mysql80/bin/mysql -S /tmp/mysql3318.sock
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值