mysql数据库的基本管理

1 、mariadb的安装及安全初始化

[root@westos_mariadb ~]# dnf install mariadb-server.x86_64 -y  安装mysql
[root@westos_mariadb ~]# systemctl enable --now mariadb  启动服务
[root@westos_mariadb ~]# mysql
MariaDB [(none)]> SHOW DATABASES  显示库名称
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

MariaDB [(none)]> USE mysql  进入mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW TABLES;  查看数据库中的所有表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |

如何提高mysql安全性
对安全性初始化

[root@westos_mariadb ~]# mysql_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
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):           输入初始密码，没有直接回车
OK, successfully used password, moving on...

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

Set root password? [Y/n] y   是否设置超级用户密码
New password:    
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


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!
[root@westos_mariadb ~]# mysql  -u root -p   登陆mysql
Enter password:      输入密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.17-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)]> 


msyql对外端口暴露，如何关闭端口
[root@westos_mariadb ~]# netstat -antlupe | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      27         67658      31213/mysqld 
[root@westos_mariadb ~]# rpm -qc  mariadb-server  查看
/etc/logrotate.d/mariadb
/etc/my.cnf.d/enable_encryption.preset
/etc/my.cnf.d/mariadb-server.cnf
/var/log/mariadb/mariadb.log

[root@westos_mariadb ~]# vim /etc/my.cnf.d/mariadb-server.cnf  编辑配置文件
skip-networking=1    添加，跳过网络功能
[root@westos_mariadb ~]# systemctl restart mariadb  重启服务
[root@westos_mariadb ~]# netstat -antlupe | grep mysql  端口已经关闭

2、sql语句中的查询动作

查看

iaDB [mysql]> select * from user;  查询user表中所有数据
MariaDB [mysql]> select Host,User from user;  查询指定字段
+-----------+------+
| Host      | User |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| localhost | root |
+-----------+------+
3 rows in set (0.000 sec)

MariaDB [mysql]> select Password,Host from user where User='root';    有限制条件的查询
+-------------------------------------------+-----------+
| Password                                  | Host      |
+-------------------------------------------+-----------+
| *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | localhost |
| *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 127.0.0.1 |
| *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | ::1       |
+-------------------------------------------+-----------+
3 rows in set (0.001 sec)

3、sql语句中的建立动作

MariaDB [mysql]> show databases;    查看库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [mysql]> CREATE DATABASE westos;  创建数据库
Query OK, 1 row affected (0.000 sec)

MariaDB [mysql]> SHOW DATABASES ; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [mysql]> USE westos;  进入数据库
Database changed
MariaDB [westos]> show tables; 显示库中的所有表
Empty set (0.000 sec)  没有表
MariaDB [westos]> CREATE TABLE linux (      创建表
    -> username  varchar(10) not null,
    -> password  varchar(10) not null
    -> );
Query OK, 0 rows affected (0.159 sec)

MariaDB [westos]> DESC linux;  查询表结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

MariaDB [westos]> INSERT INTO linux VALUES ('user1','123');   插入数据
Query OK, 1 row affected (0.910 sec)
MariaDB [westos]> INSERT INTO linux VALUES ('user2','123'),('user3','123');  插入数据
Query OK, 2 rows affected (0.042 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
+----------+----------+
3 rows in set (0.001 sec)

4、sql语句中的修改动作

[root@westos_mariadb ~]# cd /var/lib/mysql/   数据库存放位置
[root@westos_mariadb mysql]# ls
aria_log.00000001  ibdata1      ibtmp1             mysql.sock          tc.log
aria_log_control   ib_logfile0  multi-master.info  mysql_upgrade_info  westos
ib_buffer_pool     ib_logfile1  mysql              performance_schema
[root@westos_mariadb mysql]# ll
total 122936
-rw-rw---- 1 mysql mysql    16384 Oct 28 11:34 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Oct 28 11:34 aria_log_control
-rw-rw---- 1 mysql mysql      976 Oct 28 11:34 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Oct 28 15:34 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Oct 28 15:34 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Oct 28 10:24 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Oct 28 11:34 ibtmp1
-rw-rw---- 1 mysql mysql        0 Oct 28 10:24 multi-master.info
drwx------ 2 mysql mysql     4096 Oct 28 10:24 mysql
srwxrwxrwx 1 mysql mysql        0 Oct 28 11:34 mysql.sock
-rw-rw---- 1 mysql mysql       16 Oct 28 10:24 mysql_upgrade_info
drwx------ 2 mysql mysql       20 Oct 28 10:24 performance_schema
-rw-rw---- 1 mysql mysql    24576 Oct 28 11:34 tc.log
drwx------ 2 mysql mysql       54 Oct 28 15:22 westos
[root@westos_mariadb mysql]# mv westos/ bai   将westos库改为bai
[root@westos_mariadb mysql]# ls
aria_log.00000001  ibdata1      multi-master.info   performance_schema
aria_log_control   ib_logfile0  mysql               tc.log
bai                ib_logfile1  mysql.sock
ib_buffer_pool     ibtmp1       mysql_upgrade_info
[root@westos_mariadb mysql]# systemctl restart mariadb.service  重启服务

[root@westos_mariadb mysql]# mysql -uroot -p 登陆数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bai                |     数据库名字由westos变成bai，一般不会去更改数据库名字，这样容易造成数据丢失
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)


MariaDB [(none)]> use westos;   进入wesos数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.000 sec)

MariaDB [westos]> ALTER TABLE linux RENAME redhat;  更改表名为redhat
Query OK, 0 rows affected (0.509 sec)
MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| redhat           |
+------------------+
1 row in set (0.000 sec)

MariaDB [westos]> ALTER TABLE linux ADD age varchar(4);  添加字段，但是字段会在所有字段最后
Query OK, 0 rows affected (0.179 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [westos]> DESC linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(10) | NO   |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
MariaDB [westos]> ALTER TABLE linux DROP age;   删除age字段
Query OK, 0 rows affected (0.773 sec)
Records: 0  Duplicates: 0  Warnings: 0

如何在指定的地方添加字段
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4) AFTER username;  表示在username之后添加age字段
Query OK, 0 rows affected (0.481 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [westos]> DESC linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
| password | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
注意：不能把字段添加到最前面，容易造成数据飘逸，数据错位

MariaDB [westos]> select * from linux; 
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| user1    | NULL | 123      |
| user2    | NULL | 123      |
| user3    | NULL | 123      |
+----------+------+----------+
3 rows in set (0.000 sec)

MariaDB [westos]> UPDATE linux SET age='20' WHERE username='user1';  更改age数据
Query OK, 1 row affected (0.047 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| user1    | 20   | 123      |
| user2    | NULL | 123      |
| user3    | NULL | 123      |
+----------+------+----------+
3 rows in set (0.000 sec)

5、sql语句中的删除动作

MariaDB [westos]> select * from linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| user1    | 20   | 123      |
| user2    | NULL | 123      |
| user3    | NULL | 123      |
+----------+------+----------+
MariaDB [westos]> DELETE FROM linux  WHERE age='20';  删除一行
Query OK, 1 row affected (0.092 sec)

MariaDB [westos]> select * from linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| user2    | NULL | 123      |
| user3    | NULL | 123      |
+----------+------+----------+
2 rows in set (0.000 sec)

Database changed
MariaDB [westos]> ALTER TABLE linux DROP age ;  删除一列
Query OK, 0 rows affected (0.439 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |  
+----------+----------+
| user2    | 123      |
| user3    | 123      |
+----------+----------+
2 rows in set (0.000 sec)

MariaDB [westos]> DROP TABLE linux;  删除表
Query OK, 0 rows affected (0.142 sec)

MariaDB [westos]> SHOW TABLES;  删除库
Empty set (0.000 sec)
MariaDB [westos]> DROP DATABASE westos;
Query OK, 0 rows affected (0.020 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

6、数据库的密码管理

知道数据库密码的修改

[root@westos_mariadb ~]# mysqladmin -uroot -p password lee  在知道数据库密码时修改密码
Enter password:      输入原始密码

不知道数据库密码的修改

[root@westos_mariadb ~]# systemctl stop mariadb  停止数据库服务
[root@westos_mariadb ~]# mysqld_safe --skip-grant-tables &    跳过授权表启动数据库，认证没有打开
[1] 2152
[root@westos_mariadb ~]# 211028 21:59:54 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
211028 21:59:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@westos_mariadb ~]# mysql -uroot   可以直接无密码登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-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)]>  select *  from mysql.user
如果密码从来没被修改，修改9BB439A3A652A9DAD3718215F77A7AA06108A267 这个字段
| localhost | root | *9BB439A3A652A9DAD3718215F77A7AA06108A267 | Y           | Y     

如果密码之前被修改过，修改 authentication_string 字段
authentication_string       

 MariaDB [(none)]> UPDATE mysql.user SET authentication_string=password（'westos'）  因为密码之前被修改过所以用authentication_string字段修改
    -> WHERE User='root';
Query OK, 3 rows affected (0.001 sec)
Rows matched: 3  Changed: 3  Warnings: 0
[root@westos_mariadb ~]# ps aux | grep mysql  过滤mysql进程
root        2152  0.0  0.1  12960  3332 pts/1    S    21:59   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql       2246  0.0  4.7 1340688 89144 pts/1   Sl   21:59   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mariadb/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root        2440  0.0  0.0  12108  1056 pts/1    S+   22:18   0:00 grep --color=auto mysql

有两个未被认证的进程需要关闭
[root@westos_mariadb ~]# kill -9 2152
[root@westos_mariadb ~]# kill -9 2246
[1]+  Killed                  mysqld_safe --skip-grant-tables
[root@westos_mariadb ~]# ps aux | grep mysql   已经关闭
root        2475  0.0  0.0  12108   968 pts/1    R+   22:21   0:00 grep --color=auto mysql   
[root@westos_mariadb ~]# systemctl start mariadb.service  开启服务
[root@westos_mariadb ~]# mysql -uroot -pwestos  登陆成功
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-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.

7、数据库的备份及恢复

[root@westos_mariadb ~]# mysqldump -uroot -p westos   备份westos库
Enter password: 
-- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: westos
-- ------------------------------------------------------
-- Server version	10.3.17-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

[root@westos_mariadb ~]# mysqldump -uroot -p westos > /mnt/westos.sql  将备份的数据导入到文件里
Enter password:  输入数据库密码
[root@westos_mariadb ~]# mysqldump -uroot -p --all-databases  表示备份所有库
Enter password: 
[root@westos_mariadb ~]# mysqldump -uroot -p --all-databases  --no-data  表示只备份所有数据库表格不备份数据
[root@westos_mariadb ~]# mysql -uroot -p -e "DROP DATABASE westos;"   删除westos库， -e表示执行
Enter password: 
[root@westos_mariadb ~]# mysql -uroot -p -e "SHOW DATABASES;"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

如何还原删掉的westos库
第一种方法：
[root@westos_mariadb ~]# mysql -uroot -p -e "CREATE DATABASE westos;"  创建westos库
Enter password: 
[root@westos_mariadb ~]# mysql -uroot -pwestos westos < /mnt/westos.sql   还原
[root@westos_mariadb ~]# mysql -uroot -pwestos -e "SELECT * FROM westos.linux;"  可以看出库的内容已经还原回来了
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
第二种方法：
 [root@westos_mariadb ~]# mysql -uroot -p -e "DROP DATABASE westos;" 再次删除westos库
Enter password: 
在没有提前建立westos时
[root@westos_mariadb ~]# cd /mnt/
[root@westos_mariadb mnt]# vim westos.sql  直接编辑备份文件
CREATE DATABASE westos;
USE westos;
DROP TABLE IF EXISTS `linux`;  在此行之上添加建立库，使用库两行命令
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `linux` (
[root@westos_mariadb mnt]# mysql -uroot -pwestos < /mnt/westos.sql   还原

8、数据库中用户的授权

MariaDB [(none)]> CREATE USER lee@localhost identified by 'westos';建立的用户只能在本机登陆
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> MariaDB [(none)]> CREATE USER westos@'%'  identified by 'westos'   identified表示密码
'%'表示在任意客户端通过网络登陆，也可以在本机使用
[root@westos_mariadb ~]# vim /etc/my.cnf.d/mariadb-server.cnf 编辑配置文件
#skip-networking=1  将跳过网络位关闭，开启网络
[root@westos_mariadb ~]# systemctl restart mariadb.service 
[root@westos_mariadb ~]# systemctl stop firewalld  关闭火墙
[westos@foundation50 Desktop]$ mysql -ulee -pwestos -h 172.25.254.200  lee用户只能登陆本地，不能远程
[westos@foundation50 Desktop]$ mysql -ulee -pwestos -h 172.25.254.200  westos用户可以远程登陆
MariaDB [(none)]> SHOW GRANTS FOR lee@localhost;  查看用户授权信息
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR westos@'%';  查看用户授权信息
+-------------------------------------------------------------------------------------------------------+
| Grants for westos@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'westos'@'%' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

[root@westos_mariadb ~]# mysql -uwestos -pwestos   westos用户没有授权啥信息也么有
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.17-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)]> SHOW DATABASES;       没有任何信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)

[root@westos_mariadb ~]# mysql -uroot -pwestos  用超级用户登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.17-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)]> GRANT SELECT ON westos.* TO lee@localhost;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR lee@localhost;  给lee用户授权查看功能
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT ON `westos`.* TO 'lee'@'localhost'                                                            |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
[root@westos_mariadb ~]# mysql -ulee -pwestos  lee用户登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.17-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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| westos             |   可以查看库
+--------------------+
2 rows in set (0.766 sec)

MariaDB [(none)]> REVOKE SELECT ON westos.* FROM lee@localhost;  取消授权
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW GRANTS FOR lee@localhost;  查看lee用户授权信息，发现授权已经取消
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

9、删除用户

[root@westos_mariadb ~]# mysql -uroot -pwestos  登陆root用户
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-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)]> DROP USER lee@localhost;   删除lee用户
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> DROP USER westos@'%';  删除westos用户
Query OK, 0 rows affected (0.000 sec)

10、数据库的web插件

phpMyAdmin-3.4.0下载地址

[root@westos_mariadb mnt]# dnf install httpd php -y   安装apache 和php
[root@westos_mariadb mnt]# systemctl enable --now firewalld.service  开启火墙
Created symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service → /usr/lib/systemd/system/firewalld.service.
Created symlink /etc/systemd/system/multi-user.target.wants/firewalld.service → /usr/lib/systemd/system/firewalld.service.
[root@westos_mariadb mnt]# systemctl enable --now httpd  开启httpd服务
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
[root@westos_mariadb mnt]# firewall-cmd --permanent --add-service=http  允许http服务
success
[root@westos_mariadb mnt]# firewall-cmd --reload 
success
[root@westos_mariadb mnt]# cd /var/www/html/  将下载的phpMyAdmin 文件放入默认发布目录
[root@westos_mariadb html]# ls
phpMyAdmin-3.4.0-all-languages
[root@westos_mariadb html]# mv phpMyAdmin-3.4.0-all-languages/  mysqladmin  重新命名
[root@westos_mariadb html]# ls
mysqladmin
[root@westos_mariadb mysqladmin]# less README 查看说明
Please see the Documentation.txt/.html file.  参考这个文档
[root@westos_mariadb mysqladmin]# less Documentation.txt  查看文档
[root@westos_mariadb mysqladmin]# cp config.sample.inc.php config.inc.php
[root@westos_mariadb mysqladmin]# less Documentation.txt 
<?php
        $cfg['blowfish_secret'] = 'ba17c1ec07d65003';  // use here a value of your choice  得到ba17c1ec07d65003信息

[root@westos_mariadb mysqladmin]# vim config.inc.php  
$cfg['blowfish_secret'] = 'ba17c1ec07d65003'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */   将ba17c1ec07d65003添加到=‘ ' 里

[root@westos_mariadb mysqladmin]# systemctl restart httpd  重启服务
[root@westos_mariadb phpMyAdmin]# php -m 查看php所支持的模块，不支持数据库
[root@westos_mariadb mysqladmin]# dnf install php-mysqlnd.x86_64 -y  php 不支持数据库，需要安装php插件
[root@westos_mariadb mysqladmin]# systemctl restart httpd 重启服务

在浏览器中访问本机的安装包的目录， 可以进入mysql的图形管理化操作界面

新建数据库

在westos库中建立新表linux

设置所写的四个字段
插入信息