8月5日学习笔记 glibc安装与安全用户角色权限

3.创建⽤户

[root@localhost ~]# useradd -r -s /sbin/nologin
mysql
[root@localhost ~]# id mysql
uid=27(mysql) gid=27(mysql) 组=27(mysql)

4.清空其他环境 mariadb

[root@localhost ~]# rm -rf /etc/my.cnf

5.将解压文件放置在根目录下

[root@localhost ~]# mv mysql-8.0.33-linuxglibc2.12-x86_64/ /mysql8
[root@localhost ~]# ls /mysql8/
bin docs include lib LICENSE man README 
share support-files

6.切换mysql8⽬录，创建⼀个mysq-files

cd /mysql8/
mkdir mysql-files

7.修改mysql-files⽂件权限750和所属 mysql

[root@localhost mysql8]# chown mysql:mysql mysqlfiles/
[root@localhost mysql8]# chmod 750 mysql-files/
[root@localhost mysql8]# ls -l
总⽤量 296
drwxr-xr-x. 2 7161 31415 4096 3⽉ 17 2023 bin
drwxr-xr-x. 2 7161 31415 38 3⽉ 17 2023 docs
drwxr-xr-x. 3 7161 31415 4096 3⽉ 17 2023
include
drwxr-xr-x. 6 7161 31415 201 3⽉ 17 2023 lib
-rw-r--r--. 1 7161 31415 284945 3⽉ 17 2023
LICENSE
drwxr-xr-x. 4 7161 31415 30 3⽉ 17 2023 man
drwxr-x---. 2 mysql mysql 6 8⽉ 3 22:30
mysql-files
-rw-r--r--. 1 7161 31415 666 3⽉ 17 2023
README
drwxr-xr-x. 28 7161 31415 4096 3⽉ 17 2023
share
drwxr-xr-x. 2 7161 31415 77 3⽉ 17 2023
support-files

8.初始化数据库，找到初始密码

[root@localhost mysql8]# ./bin/mysqld --initialize
--user=mysql --basedir=/mysql8
2024-08-03T14:34:53.993391Z 0 [System] [MY-013169]
[Server] /mysql8/bin/mysqld (mysqld 8.0.33)
initializing of server in progress as process 2236
2024-08-03T14:34:54.003043Z 1 [System] [MY-013576]
[InnoDB] InnoDB initialization has started.
2024-08-03T14:34:55.674077Z 1 [System] [MY-013577]
[InnoDB] InnoDB initialization has ended.
2024-08-03T14:34:58.133984Z 6 [Note] [MY-010454]
[Server] A temporary password is generated for
root@localhost: .I//GqI,Z2-k

9.查看是否初始化成功过，可以看⽂件夹中

是否有data⽂件夹

[root@localhost mysql8]# ls
bin docs lib man README 
support-files
data include LICENSE mysql-files share

10.设置ssl安全加密连接 敏感数据

[root@localhost mysql8]# ls ./bin/*ssl*
./bin/mysql_ssl_rsa_setup
[root@localhost mysql8]# ./bin/mysql_ssl_rsa_setup
--datadir=/mysql8/data
[root@localhost mysql8]# ls ./data/
auto.cnf #ib_16384_1.dblwr mysql.ibd 
 sys
ca-key.pem ib_buffer_pool 
performance_schema undo_001
ca.pem ibdata1 
private_key.pem undo_002
client-cert.pem #innodb_redo 
public_key.pem
client-key.pem #innodb_temp servercert.pem
#ib_16384_0.dblwr mysql serverkey.pem

11.其他配置

[root@localhost mysql8]# cp supportfiles/mysql.server /etc/init.d/mysql8
[root@localhost mysql8]# ls /etc/init.d/mysql8
/etc/init.d/mysql8
[root@localhost mysql8]# ls /etc/init.d/
functions mysql8 mysql8ls netconsole network 
README
[root@localhost mysql8]#
# 默认情况下，启动⽂件认识安装⽬录在/usr/local/⽬录下
[root@localhost mysql8]# sed -n '/^basedir=/p'
/etc/init.d/mysql8
basedir=
[root@localhost mysql8]# sed -i
'/^basedir=/cbasedir=/mysql8' /etc/init.d/mysql8
[root@localhost mysql8]# sed -n '/^basedir=/p'
/etc/init.d/mysql8
basedir=/mysql8
[root@localhost mysql8]# sed -n '/^datadir=/p'
/etc/init.d/mysql8
datadir=
[root@localhost mysql8]# sed -i
'/^datadir=/cdatadir=/mysql8/data'
/etc/init.d/mysql8
[root@localhost mysql8]# sed -n '/^datadir=/p'
/etc/init.d/mysql8
datadir=/mysql8/data
[root@localhost mysql8]#

12.启动服务 不能使⽤systemctl

ot@localhost mysql8]# service mysql8 start
Starting MySQL.Logging to
'/mysql8/data/localhost.localdomain.err'.
.. SUCCESS!
[root@localhost mysql8]# ls /mysql8/data/*.err
/mysql8/data/localhost.localdomain.err
# 启动成功后会⽣成err⽇志⽂件，多看看，了解启动的详细原因

13.常⻅问题

1. 为什么要删除/etc/my.cnf⽂件
```
数据库初始化时候，会⾃动找my.cnf配置，但是原有的mariadb配
置⽂件，会失败
```
2. mysql-files⽂件夹
```
⽬前么有⽤，必须创建，可能导致⽆法启动，数据的备份和还原，导
⼊和到处所指定的默认⽬录
```
3. 判断数据库初始化成功
```
数据库的安装容易出错的地⽅是初始化，⽆法正常启动，确认⽅式
安装⽬录下是否⽣成data⽬录
```
4. 为什么要修改mysql.server放在/etc/init.d⽬录下
```
不放也可以，但是就⽆法使⽤service mysql8 start启动
只能使⽤bin/mysqld可执⾏⽂件启动
```
5. 为什么需要修改basedir和datadir
```
glibc是⼆进制绿⾊版本，默认配置很多，需要修改
```
6. 开机启动
```shell
[root@localhost mysql8]# chkconfig --list
注：该输出结果只显示 SysV 服务，并不包含
原⽣ systemd 服务。SysV 配置数据
可能被原⽣ systemd 配置覆盖。
 要列出 systemd 服务，请执⾏ 'systemctl listunit-files'。
 查看在具体 target 启⽤的服务请执⾏
 'systemctl list-dependencies [target]'。
netconsole 0:关 1:关 2:关 3:关 4:关 5:关 6:关
network 0:关 1:关 2:开 3:开 4:开 5:开 6:关

#查看启动该列表
[root@localhost mysql8]# chkconfig --list
注：该输出结果只显示 SysV 服务，并不包含
原⽣ systemd 服务。SysV 配置数据
可能被原⽣ systemd 配置覆盖。
 要列出 systemd 服务，请执⾏ 'systemctl listunit-files'。
 查看在具体 target 启⽤的服务请执⾏
 'systemctl list-dependencies [target]'。
mysql8 0:关 1:关 2:开 3:开 4:开 5:开 6:关
netconsole 0:关 1:关 2:关 3:关 4:关 5:关 6:关
network 0:关 1:关 2:开 3:开 4:开 5:开 6:关
# 启动2345
[root@localhost mysql8]# chkconfig mysql8 on
[root@localhost mysql8]# chkconfig --list
注：该输出结果只显示 SysV 服务，并不包含
原⽣ systemd 服务。SysV 配置数据
可能被原⽣ systemd 配置覆盖。
 要列出 systemd 服务，请执⾏ 'systemctl listunit-files'。
 查看在具体 target 启⽤的服务请执⾏
 'systemctl list-dependencies [target]'。
mysql8 0:关 1:关 2:开 3:开 4:开 5:开 6:关
netconsole 0:关 1:关 2:关 3:关 4:关 5:关 6:关
network 0:关 1:关 2:开 3:开 4:开 5:开 6:关
# 3是字符界⾯，5是图形界⾯
```## 14 .mysql glibc版本后续设置
1. 修改密码1
 ```shell
 [root@localhost mysql8]# ./bin/mysqladmin -uroot
password '123' -p
 Enter password:
 mysqladmin: [Warning] Using a password on the
command line interface can be insecure.
 Warning: Since password will be sent to server
in plain text, use ssl connection to ensure
password safety.

Copyright (c) 2000, 2023, Oracle and/or its
affiliates.
 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. 修改密码2
 ```shell
 mysql> set password='456';
 Query OK, 0 rows affected (0.02 sec)
 mysql> flush privileges;
 Query OK, 0 rows affected (0.01 sec)

 Copyright (c) 2000, 2023, Oracle and/or its
affiliates.
 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>
 ```

3. 将mysql的bin⽬录添加到环境变量中
 ```shell
 [root@localhost mysql8]# sed -i '$aexport
PATH=/mysql8/bin/:$PATH' /etc/profile
 [root@localhost mysql8]# sed -n '$p'
/etc/profile
 export PATH=/mysql8/bin/:$PATH
 [root@localhost mysql8]# source /etc/profile
 [root@localhost mysql8]# which mysql
 /mysql8/bin/mysql
 [root@localhost mysql8]# mysqld
 2024-08-03T15:53:05.871244Z 0 [System] [MY-
010116] [Server] /mysql8/bin/mysqld (mysqld 8.0.33)
starting as process 2861
 2024-08-03T15:53:05.874283Z 0 [ERROR] [MY-
010123] [Server] Fatal error: Please read
"Security" section of the manual to find out how to
run mysqld as root!
 2024-08-03T15:53:05.874333Z 0 [ERROR] [MY-
010119] [Server] Aborting
 2024-08-03T15:53:05.874776Z 0 [System] [MY-
010910] [Server] /mysql8/bin/mysqld: Shutdown
complete (mysqld 8.0.33) MySQL Community Server -
GPL.
 [root@localhost mysql8]#
 ```

4. 配置⽂件
 ```shell
 [root@localhost mysql8]# fg
 vim ./my.cnf
 [mysqld]
 basedir=/mysql8
 datadir=/mysql8/data
 socket=/tmp/mysql.sock

 # 查看套接字⽂件
 [root@localhost mysql8]# ll /tmp/
总⽤量 12
 -rwx------. 1 root root 836 8⽉ 2 23:02 ksscript-64N_oN
 srwxrwxrwx. 1 mysql mysql 0 8⽉ 4 00:02
mysql.sock
 -rw-------. 1 mysql mysql 5 8⽉ 4 00:02
mysql.sock.lock
 srwxrwxrwx. 1 mysql mysql 0 8⽉ 4 00:02
mysqlx.sock
 -rw-------. 1 mysql mysql 5 8⽉ 4 00:02
mysqlx.sock.lock
 drwx------. 3 root root 17 8⽉ 3 20:49
systemd-private-23259a7f757e44ec9ab58b88e07ceefachronyd.service-poengE
 -rw-------. 1 root root 0 8⽉ 2 22:58
yum.log

[root@localhost mysql8]# mysql_secure_installation
# ⼀路y下去 改密码，不允许远程等等
Securing the MySQL server deployment.
Enter password for user root:
VALIDATE PASSWORD COMPONENT can be used to test
passwords
and improve security. It checks the strength of
password
and allows the users to set only those passwords
which are
secure enough. Would you like to setup VALIDATE
PASSWORD component?
Press y|Y for Yes, any other key for No:
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes,
any other key for No) : y
New password:
Re-enter new password:
Sorry, passwords do not match.
New password:
Re-enter new password:
By default, a MySQL installation has an anonymous
user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any
other key for No) : 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? (Press y|Y for Yes,
any other key for No) : y
Success.
By default, MySQL 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? (Press y|Y
for Yes, any other key for No) : 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? (Press y|Y for Yes,
any other key for No) : y
Success.
All done!
[root@localhost mysql8]# mysql -uroot -p456
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 11
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its
affiliates.
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> quit
Bye
[root@localhost mysql8]#

create user 'zhangmin'@'%' identified by
'Zhang_min123';

grant all on *.* to 'zhangmin'

create database if not exists test;

use test;
create table user(
 id int primary key,
 username varchar(45) not null,
 password varchar(45) not null
);

insert into test.user values(1,"zhangsan","123");
insert into test.user values(2,"lisi","456");
insert into test.user values(3,"wamngwi","789");
insert into test.user values(4,"zhaoliu","aaa");

mysql> create user 'lilaosi'@'%' identified by
'lilaoshi_123';
ERROR 1819 (HY000): Your password does not satisfy
the current policy requirements
mysql> create user 'lilaosi'@'%' identified by
'Lilaoshi_123';
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'lilaosi'@'%' identified by
'Lilaosi_123';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | lilaosi |
| % | root |
| % | zhangmin |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
6 rows in set (0.00 sec)

grant all on test.* to 'lilaosi';
# lilaosi就获得了test库中所有的表的操作权限，但是，由于
root没有个lilaosimysql库的权限，所以lilaosi账号⽆法查看
mysql库

mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> set global validate_password.length=0;
mysql> set global validate_password.policy=LOW;
mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+

练习
创建三个账号，abc[abcd],ccc[a1b2c3] ,ddd[231343]
<mysql> create user 'efg'@'%' identified by 'efg';
ERROR 1819 (HY000): Your password does not satisfy
the current policy requirements
mysql> create user 'efgh'@'%' identified by 'efgh';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | efgh |
| % | root |
| % | zhangmin |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | test1 |
+-----------+------------------+

mysql> drop user 'zhangmin';
Query OK, 0 rows affected (0.02 sec)
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| efgh |
| root |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| test1 |
+------------------+
6 rows in set (0.00 sec)

mysql> alter user 'zhangmin' identified by
'abc123';
Query OK, 0 rows affected (0.01 sec)

mysql> create role 'a';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'a';
+-------------------------------+
| Grants for a@% |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)

练习
1.添加jingli⻆⾊
create role 'jingli';
2.添加yuangong⻆⾊
craete role 'yaungong';
3.为jingli添加select insert delete update权限
grant select ,insert,delete,update on test.user to
'jingli';
4.为yuangong添加select，insert权限
grant select,insert on test.user to 'yuangong';
5.查看⻆⾊保存的表格
selet host,user from mysql.user;
6.查看⻆⾊的权限
show grants for 'jingli';
show grants for 'yaungogng';
新增bbb和ccc两个⽤户bbb是经理需要增删改查权限，ccc是员⼯是
只需要新增和查看的权限
grant jingli to ‘bbb’；
grant yuangong to 'ccc';

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> grant system_user on *.* to "root";
mysql> show grants for 'root';
+--------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
------------------------------+
| Grants for root@%
|
+--------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,
INDEX, ALTER, SHOW DATABASES, SUPER, CREATE
TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION
SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT,
TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE
ON *.* TO `root`@`%` WITH GRANT OPTION |
| GRANT SYSTEM_USER ON *.* TO `root`@`%`
|
+--------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for "efgh";
+--------------------------------------------------
-+
| Grants for efgh@%
|
+--------------------------------------------------
-+
| GRANT SELECT, INSERT, DELETE ON *.* TO `efgh`@`%`
|
+--------------------------------------------------
-+
1 row in set (0.00 sec)

练习步骤
1.添加aaa账户，设置密码aaaa
drop user aaa;
create user 'aaa'@'%' identified by 'aaaa';
2.使⽤aaa账户访问mysql服务
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
3.查看test数据库发现么有权限
show databases;
4.退出并使⽤root账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
5.为aaa账户添加查看test.user表的权限
grant select on test.user to 'aaa';
6.退出root，使⽤aaa账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
7.查看数据库，查看表，查看表内容 能够正常查看
show databases;
user test;
show tables;
select * from user;
8.输⼊数据，没有权限
insert into user values(5,"ermazi","ermazi");####
9.退出aaa使⽤root登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
10.为aaa添加insert权限
grant insert on test.user to 'aaa';
11.退出root使⽤aaa登录
exit|quit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
12.向user表添加⼀⾏新的数据
insert into test.user
values(6,"zhangsanfeng","zhangsanfen");
13.修改user中⼀⾏的数据的password（密码）为111，没有
update权限
update test.user set password='zsf' where username-
'zhangsanfeng';

mysql> revoke all on *.* from "efgh";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for "efgh";
+----------------------------------+
| Grants for efgh@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `efgh`@`%` |
+----------------------------------+
1 row in set (0.00 sec)