Mariadb数据库基本操作之用户管理

一、中间件介绍

1.百度百科介绍

中间件是介于应用系统和系统软件之间的一类软件，它使用系统软件所提供的基础服务（功能），
衔接网络上应用系统的各个部分或不同的应用，能够达到资源共享、功能共享的目的。

2.通俗理解

1.中间件=平台+通信
2.为在线业务提供的功能的组件
3.没有暴露在前端，web服务，http就是中间件，或者是叫插件，软件等说法

二、数据库的分类

1.关系型数据库

①关系型数据库

关系型数据库：关系数据库系统建立了关系模型，并用它来处理数据。
关系模型在表中将信息与字段关联起来（也就是schemas），存储在数据表的行和列中。数据表可以彼此关联协作存储，也很容易提取数据。

②关系型数库的特点

1.数据是结构化的
2.数据库中存放的是数据表，数据表存放的是一行行数据，每一列都有一个列头，称之为字段
3.多张表之间通过一些字段建立关联关系

③典型的关系型数据库

mysql--最新版本8.0，上一个版本5.7
mariadb——全称应该为数据库软件（系统）-最新版本10.6
oracle

2.非关系型数据库

1.非关系型数据库：非关系型数据不适合存储在数据表的行和列中，而是大块组合在一起。
2.非关系型数据通常存储在数据集中，就像文档、键值对或者图结构。
3.你的数据及其特性是选择数据存储和提取方式的首要影响因素。
4.典型非关系型数据库mongodb。

三、安装mariadb

1.下载安装mariadb

①.maridb官方网址

mariadb官网网址

②.复制yum仓库地址

# MariaDB 10.6 CentOS repository list - created 2021-12-27 11:21 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.xtom.com.hk/mariadb/yum/10.6/centos7-amd64
gpgkey=https://mirrors.xtom.com.hk/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

③替换清华的镜像源

sed -i 's#//mirrors.xtom.com.hk#//mirrors.ustc.edu.cn#g' /etc/yum.repos.d/mariadb.repo

④.安装mariadb

yum -y install Mariadb-server

2.重启mariadb服务

 systemctl restart mariadb
 systemctl enable --now mariadb

3.检查服务和端口

[root@mster-k8s ~]# netstat -tunlp |grep 33
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      182438/mariadbd     
tcp6       0      0 :::3306                 :::*                    LISTEN      182438/mariadbd

四、数据库的初始化

[root@fc-rhel7 ~]# mariadb-secure-installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

五、mariadb的用户管理

1.用户的组成介绍

mariadb中的用户组成由两部分组成，
用户名@'192.168.3.%'
admin@'%'      #   %代表0.0.0.0
natasha@'192.168.200.1'        # 代表此用户只能是从192.168.200.1这台机器登录

2.新创建用户

MariaDB [(none)]> create user "admin"@"%" identified by "admin123";
Query OK, 0 rows affected (0.001 sec)

3.修改用户密码

set password for admin=password('admin123');

4.给用户授权

MariaDB [(none)]> grant all on *.* to admin;    #授权全部权限给admin用户，
Query OK, 0 rows affected (0.017 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

5.查看用户列表

MariaDB [(none)]> select user,host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| admin       | %         |
|             | localhost |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
|             | mster-k8s |
+-------------+-----------+
6 rows in set (0.001 sec)

6.查看用户权限

MariaDB [(none)]> show grants for admin;
+---------------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                            |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `admin`@`%` IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

7.远程登录新建用户

[root@mster-k8s ~]# mysql -h 192.168.3.50 -uadmin -padmin123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.6.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

8.删除用户

MariaDB [(none)]> drop user 'admin'@'%';

9.用户权限表

六、mariadb的root用户密码更改

1.mysql8.0之后修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

2.root密码为空的情况修改

mysqladmin -uroot password '123'

3.root有密码下修改

mysqladmin -u root -p123 password 'huawei' #新密码huawei

4.设置root远程登录

MariaDB [(none)]> GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root' @'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> set password for 'root'@'%'=password('123');
Query OK, 0 rows affected (0.001 sec)


MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]>

5.本地验证root登录

[root@mster-k8s mysql]# mysql -uroot -p123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.6.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

6.远程验证root登录

[root@mster-k8s mysql]# mysql -uroot -p123 -h 192.168.3.50
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.6.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>