Linux修炼之旅第四章！
本章介绍了数据库的相关知识，介绍了mysql数据库的基本操作，管理，密码管理，授权管理和书籍库的备份
注：本章中的-p后直接输密码是为了直观过程，是非常不职业的行为！
文章目录
一、数据库的介绍
数据库就是个高级的表格软件
由于mysql被甲骨文公司收购
所以现在用的是基本相同的平替软件
mariadb
但是用法基本没有变化
二、mysql初始化
首先安装启用mysql数据库软件
[root@westoslinux ~] dnf install mariadb-server -y
[root@westoslinux ~] systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@westoslinux ~] cd /var/lib/mysql/ #查看一下安装后有些什么东西
[root@westoslinux mysql]# ls
aria_log.00000001 ibdata1 ibtmp1 mysql.sock tc.log
然后就可以开始初始化数据库了
首先关闭数据库开放端口
vim /etc/my.cnf.d/mariadb-server.cnf
在最后面加上
skip-networking=1
重启后就识别不到端口了
netstat -antlupe | grep mysql
查看端口信息
第二步 执行安全初始化脚本
输入
mysql_secure_installation
mysql安全初始化
就可以对数据库进行基本的安全初始化设置
可以对数据库设定密码
设定完后的选项都输入回车
就完成了安全初始化设定
[root@westoslinux mysql] mysql_secure_installation #mysql安全初始化
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]
... skipping.
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]
... skipping.
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]
... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... 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!
然后
然后就可以先简单试试mysql数据库了
因为初始化时设定了一个用户root以及密码westos
所以输入
mysql -u root -p
然后输入密码就可以访问数据库了
二、数据库的基本管理
1.查看
SHOW DATABASES; ##显示库名称
USE mysql; ##进入mysql库
SHOW TABLES； ##显示库中的所有表
SELECT * FROM user; ##查询所有数据
SELECT Host,User,Password FROM user; ##查询指定字段
SELECT Host FROM mysql.user WHERE User='root'
2.新建
CREATE DATABASE westos; ##新建库
CREATE TABLE linux ( ##新建表
username varchar(6) not null,
password varchar(30) not null
);
DESC linux; ##显示表结构
INSERT INTO linux VALUES ('user1','123'); #插入数据
FLUSH PRIVILEGES; #刷新数据库
可以看到刚刚创建的内容如下
插入数据
3.更改
ALTER TABLE linux RENAME userlist; #更改表名
ALTER TABLE userlist ADD address varchar(40) ; #新建address
ALTER TABLE userlist ADD address varchar(4) AFTER username;#在username后新建age
ALTER TABLE userlist DROP age; #删除age
UPDATE userlist SET age='20' ; #设定uselist的用户age都为20
UPDATE userlist SET age='25' WHERE username='user2';#把user2的age设定为25
以上命令效果依次如下
类似的再新建一个age
4.删除
DELETE FROM userlist WHERE username='user1';
DROP TABLE linux;
DROP DATABASE westos;
三、数据库的备份和恢复
先创建一个数据库，内容与上个实验一致
[root@westoslinux ~] mysql -uroot -pwestos
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)]> CREATE DATABASE westos;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> CREATE TABLE westos.userlist(
-> username varchar(10) not null,
-> passworf varchar(50) not null
-> );
Query OK, 0 rows affected (0.017 sec)
MariaDB [(none)]> INSERT INTO westos.userlist VALUE ('user1','123'),('user2',123);
Query OK, 2 rows affected (0.003 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [(none)]> SELECT * FROM westos.userlist ;
+----------+----------+
| username | passworf |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> quit
Bye
1.备份数据库
备份完整数据库
mysqldump -uroot -pwestos westos
[root@westoslinux ~] mysqldump -uroot -pwestos westos
-- 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 */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `userlist`
--
DROP TABLE IF EXISTS `userlist`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userlist` (
`username` varchar(10) NOT NULL,
`passworf` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `userlist`
--
LOCK TABLES `userlist` WRITE;
/*!40000 ALTER TABLE `userlist` DISABLE KEYS */;
INSERT INTO `userlist` VALUES ('user1','123'),('user2','123');
/*!40000 ALTER TABLE `userlist` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-11-21 9:19:34
在备份过程中可以看到我们创建的数据
备份数据库的格式（没有内容信息）
mysqldump -uroot -pwestos westos --no-data
[root@westoslinux ~] mysqldump -uroot -pwestos westos --no-data
-- 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 */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `userlist`
--
DROP TABLE IF EXISTS `userlist`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userlist` (
`username` varchar(10) NOT NULL,
`passworf` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-11-21 9:20:52
看不到文件信息了
备份数据库到目录
创建一个数据库备份目录，将数据库westos备份到westos.sql文件中
[root@westoslinux ~] mkdir /shujuku
[root@westoslinux ~] mysqldump -uroot -pwestos westos > /shujuku/westos.sql
[root@westoslinux ~] ls /shujuku
westos.sql
然后将数据库删除
[root@westoslinux ~] mysql -uroot -pwestos -e "DROP DATABASE westos;"
[root@westoslinux ~] mysql -uroot -pwestos -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
恢复数据库
方法一
新建一个westos数据库，再将文件导入到wesots数据库中
[root@westoslinux ~] mysql -uroot -pwestos -e "CREATE DATABASE westos;" #创建数据库westos
[root@westoslinux ~] mysql -uroot -pwestos -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
[root@westoslinux ~] mysql -uroot -pwestos westos < /shujuku/westos.sql #恢复数据库
[root@westoslinux ~] mysql -uroot -pwestos #测试效果
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
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 westos.userlist ;
+----------+----------+
| username | passworf |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.001 sec)
MariaDB [(none)]> quit
Bye
恢复数据库方法二
在备份文件中加入创建westos数据库的命令
先删除westos库
[root@westoslinux ~]# mysql -uroot -pwestos -e "DROP DATABASE westos;"
编辑文件
[root@westoslinux ~]# vim /shujuku/westos.sql
加入两行
CREATE DATABASE westos;
USE westos
然后直接导入
[root@westoslinux ~] mysql -uroot -pwestos < /shujuku/westos.sql
[root@westoslinux ~] mysql -uroot -pwestos -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
[root@westoslinux ~] mysql -uroot -pwestos -e "SELECT * FROM westos.userlist;"
+----------+----------+
| username | passworf |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
四、密码管理
1.知道密码改密码
知道密码的情况改密码很简单
用一条指令即可实现
比如改密码为shy
mysqladmin -uroot -pwestos password shy
测试效果
[root@westoslinux ~]# mysql -uroot -pwestos
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@westoslinux ~]# mysql -uroot -pshy
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
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)]> quit
Bye
2.不知道密码改密码
不知道密码的情况就得破译密码，再更改
先关闭数据库服务
systemctl stop mariadb.service
跳过授权表，可以不用密码登陆
[root@westoslinux ~] mysqld_safe --skip-grant-tables & #必须要后台运行
[2] 3406
[root@westoslinux ~] 211121 10:11:30 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
211121 10:11:30 mysqld_safe A mysqld process already exists
mysql
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)]> SHOW DATABASES
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.001 sec)
进入数据库后就是更改密码的操作
MariaDB [(none)]> SELECT * FROM mysql.user;
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost | root | *43746A13FA7453A0A8ACF9B186D367D81F76AB7C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *43746A13FA7453A0A8ACF9B186D367D81F76AB7C | N | N | | 0.000000 |
| westoslinux.westos.org | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
| ::1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]> UPDATE mysql.user SET authentication_string='westos' WHERE User='root';
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [(none)]> SELECT * FROM mysql.user; #在下面比较空的表格中可以直接看到设置的密码westos，非常不安全，也不可以正常使用
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+---------+--------------+--------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+---------+--------------+--------------------+
| localhost | root | *43746A13FA7453A0A8ACF9B186D367D81F76AB7C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | westos | N | N | | 0.000000 |
| westoslinux.westos.org | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | westos | N | N | | 0.000000 |
| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | westos | N | N | | 0.000000 |
| ::1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | westos | N | N | | 0.000000 |
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+---------+--------------+--------------------+
4 rows in set (0.000 sec)
上面的密码直接修改操作在authentication_string一栏可以直接看到密码，在下面比较空的表格中可以直接看到设置的密码westos，非常不安全。后续的操作也会出现不可以正常使用的情况，所以必须要对密码进行加密
MariaDB [(none)]> UPDATE mysql.user SET authentication_string=password('westos') WHERE User='root';
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [(none)]> SELECT * FROM mysql.user;
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost | root | *43746A13FA7453A0A8ACF9B186D367D81F76AB7C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | N | N | | 0.000000 |
| westoslinux.westos.org | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | N | N | | 0.000000 |
| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | N | N | | 0.000000 |
| ::1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | N | N | | 0.000000 |
+------------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
4 rows in set (0.000 sec)
可以看到之前westos的部分变成了*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96
MariaDB [(none)]> quit
Bye
[2]+ 退出 1 mysqld_safe --skip-grant-tables
改完密码必须要关掉进程，重启服务才可以生效
[root@westoslinux ~] ps aux | grep mysql
root 3263 0.0 0.1 13172 3360 pts/0 S 10:09 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 3357 0.0 4.9 1340692 91864 pts/0 Sl 10:09 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 3555 0.0 0.0 12320 1060 pts/0 S+ 10:20 0:00 grep --color=auto mysql
[root@westoslinux ~] kill -9 3263
[root@westoslinux ~] kill -9 3357
[1]+ 已杀死 mysqld_safe --skip-grant-tables
[root@westoslinux ~] systemctl restart mariadb
测试
[root@westoslinux ~] mysql -uroot -pwestos
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)]>
五、mysql授权
先创建一个拥有本地登陆权力和网络登陆的shy用户
[root@westoslinux ~]# mysql -uroot -pwestos -e "CREATE USER shy@localhost identified by 'shy';"
[root@westoslinux ~]# mysql -uroot -pwestos -e "CREATE USER shy@'%' identified by 'shy';"
[root@westoslinux ~]# mysql -uroot -pwestos -e "SELECT User,Host FROM mysql.user;"
+------+------------------------+
| User | Host |
+------+------------------------+
| shy | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| shy | localhost |
| root | westoslinux.westos.org |
+------+------------------------+
网络用户登陆会导致数据库非常不安全，所以一般不用，这里就先删除
[root@westoslinux ~]# mysql -uroot -pwestos -e "DROP USER shy@'%';"
给用户赋权
[root@westoslinux ~]# mysql -uroot -pwestos -e "GRANT SELECT ON westos.* TO shy@localhost;"
[root@westoslinux ~]# mysql -uroot -pwestos -e "SHOW GRANTS FOR shy@localhost;"
+------------------------------------------------------------------------------------------------------------+
| Grants for shy@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'shy'@'localhost' IDENTIFIED BY PASSWORD '*43746A13FA7453A0A8ACF9B186D367D81F76AB7C' |
| GRANT SELECT ON `westos`.* TO 'shy'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
[root@westoslinux ~]# mysql -uroot -pwestos -e "GRANT DROP,INSERT ON westos.* TO shy@localhost;"
[root@westoslinux ~]# mysql -uroot -pwestos -e "SHOW GRANTS FOR shy@localhost;"
+------------------------------------------------------------------------------------------------------------+
| Grants for shy@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'shy'@'localhost' IDENTIFIED BY PASSWORD '*43746A13FA7453A0A8ACF9B186D367D81F76AB7C' |
| GRANT SELECT, INSERT, DROP ON `westos`.* TO 'shy'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
权利移除
[root@westoslinux ~]# mysql -uroot -pwestos -e "REVOKE DROP ON westos.* FROM shy@localhost;"
[root@westoslinux ~]# mysql -uroot -pwestos -e "SHOW GRANTS FOR shy@localhost;"
+------------------------------------------------------------------------------------------------------------+
| Grants for shy@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'shy'@'localhost' IDENTIFIED BY PASSWORD '*43746A13FA7453A0A8ACF9B186D367D81F76AB7C' |
| GRANT SELECT, INSERT ON `westos`.* TO 'shy'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
[root@westoslinux ~]# mysql -uroot -pwestos -e "SELECT User,Host FROM mysql.user;"
+------+------------------------+
| User | Host |
+------+------------------------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| shy | localhost |
| root | westoslinux.westos.org |
+------+------------------------+
[root@westoslinux ~]#
六、web方式管理数据库
先安必要的插件，并开启服务
[root@westoslinux ~] systemctl enable --now httpd
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Job for httpd.service failed because the control process exited with error code.
See "systemctl status httpd.service" and "journalctl -xe" for details.
由于前面的实验，导致错误，按照错误提示删除相关文件就可以解决
输入报错中的
journalctl -xe
按照提示直接删除提示文件
rm -rf /etc/httpd/conf.d/vhosts.conf
再次尝试开启服务就可以开启了
systemctl enable --now httpd
在目录中下载一个适合当前环境的phpadmin软件在如下目录中。本机中的版本适配软件名为phpMyAdmin-3.4.0-all-languages.tar.bz2
/var/www/html/
对压缩文件进行接档，然后将解档后的文件移动到mysqladmin目录中
[root@westoslinux html] ls
cgi index.h index.html index.php phpMyAdmin-3.4.0-all-languages.tar.bz2 westos wsgi
[root@westoslinux html] tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@westoslinux html] ls
cgi index.h index.html index.php phpMyAdmin-3.4.0-all-languages phpMyAdmin-3.4.0-all-languages.tar.bz2 westos wsgi
[root@westoslinux html] rm -rf phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@westoslinux html] mv phpMyAdmin-3.4.0-all-languages/ mysqladmin
编辑解档文件mysqladmin目录中的文件
[root@westoslinux mysqladmin]# vim config.inc.php
更改第十七行左右的文件为
$cfg[‘blowfish_secret’] = ‘ba17clec07d6500’; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
效果如图
添加的ba17clec07d6500在readme中获取，看图中的步骤
然后打开浏览器输入172.25.254.126/phpadmin就可以进行web中的数据库管理
加载完以后可以验证
8239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
