8.5-glibc安装+数据库的增删改查

一、glibc安装

1.清空/etc目录下的my.cnf

[root@mysql1 ~]# ls -l /etc/my.cnf
-rw-r--r--. 1 root root 1280 8月   2 10:35 /etc/my.cnf
[root@mysql1 ~]# rm -rf /etc/my.cnf
[root@mysql1 ~]# yum -y remove mariadb
已加载插件：fastestmirror
参数 mariadb 没有匹配
不删除任何软件包
[root@mysql1 ~]# find / -name "*mysql*" -exec rm -rf {} \;

2.安装依赖包

[root@mysql1 ~]# yum list installed|grep libaio
libaio.x86_64                          0.3.109-13.el7                  @anaconda

3.将包拖到xshell中

[root@mysql1 ~]# rz -E
rz waiting to receive.
[root@mysql1 ~]# ls
anaconda-ks.cfg  mysql-8.0.33-linux-glibc2.12-x86_64.tar

4.解压查看

[root@mysql1 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar 
[root@mysql1 ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
[root@mysql1 ~]# ls
anaconda-ks.cfg
mysql-8.0.33-linux-glibc2.12-x86_64
mysql-8.0.33-linux-glibc2.12-x86_64.tar
mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz
mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz
[root@mysql1 ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/
[root@mysql1 mysql-8.0.33-linux-glibc2.12-x86_64]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@mysql1 mysql-8.0.33-linux-glibc2.12-x86_64]# vim support-files/mysql.server 
[root@mysql1 mysql-8.0.33-linux-glibc2.12-x86_64]# vim support-files/mysql.server 

5.将项目文件移动到/usr/local/mysql/

 

[root@mysql1 mysql-8.0.33-linux-glibc2.12-x86_64]# cd
[root@mysql1 ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/
[root@mysql1 ~]# tree /usr/local/mysql/

6.创建用户

7.在/usr/local/mysql 目录下创建mysql-files目录

[root@mysql1 ~]# mkdir /usr/local/mysql/mysql-files

8.修改mysql-files的权限为750 所属组和属主都是mysql

[root@mysql1 ~]# chown  mysql:mysql /usr/local/mysql/mysql-files/
[root@mysql1 ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@mysql1 ~]# ll /usr/local/mysql/mysql-files/
总用量 0
[root@mysql1 ~]# ll /usr/local/mysql/
总用量 292
drwxr-xr-x.  2 root  root    4096 8月   5 09:47 bin
drwxr-xr-x.  2 root  root      38 8月   5 09:47 docs
drwxr-xr-x.  3 root  root     282 8月   5 09:47 include
drwxr-xr-x.  6 root  root     201 8月   5 09:47 lib
-rw-r--r--.  1 root  root  284945 8月   5 09:47 LICENSE
drwxr-xr-x.  4 root  root      30 8月   5 09:47 man
drwxr-x---.  2 mysql mysql      6 8月   5 09:55 mysql-files
-rw-r--r--.  1 root  root     666 8月   5 09:47 README
drwxr-xr-x. 28 root  root    4096 8月   5 09:47 share
drwxr-xr-x.  2 root  root      77 8月   5 09:47 support-files

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

[root@mysql1 ~]# ls /usr/local/mysql/bin/
ibd2sdi            mysqlcheck             mysqlpump
innochecksum       mysql_config           mysql_secure_installation
lz4_decompress     mysql_config_editor    mysqlshow
myisamchk          mysqld                 mysqlslap
myisam_ftdump      mysqld-debug           mysql_ssl_rsa_setup
myisamlog          mysqld_multi           mysql_tzinfo_to_sql
myisampack         mysqld_safe            mysql_upgrade
my_print_defaults  mysqldump              perror
mysql              mysqldumpslow          zlib_decompress
mysqladmin         mysqlimport
mysqlbinlog        mysql_migrate_keyring
[root@mysql1 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
2024-08-05T02:01:55.494773Z 0 [System][MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 3175
2024-08-05T02:01:55.508164Z 1 [System][MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-05T02:01:56.466124Z 1 [System][MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-05T02:01:57.653329Z 6 [Note][MY-010454] [Server] A temporary password is generated for root@localhost: 14JSl<3owJyk

10.判断是否初始化成功，看文件中是否生成了data目录

[root@mysql1 ~]# ls /usr/local/mysql/
bin   docs     lib      man          README  support-files
data  include  LICENSE  mysql-files  share

11.设置ssl加密连接

[root@mysql1 ~]# ls /usr/local/mysql/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       server-cert.pem

# ib_16384_0.dblwr  mysql              server-key.pem


[root@mysql1 ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
[root@mysql1 ~]# ls /usr/local/mysql/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       server-cert.pem

# ib_16384_0.dblwr  mysql              server-key.pem

12.其他配置

# 把mysql.server文件放到/etc/init.d/目录下

# 方便启动mysql服务 service mysql start

[root@mysql1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

13.启动服务 不能使用systemctl

#启动服务
[root@mysql1 ~]# service mysql8 start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql1.err'.
..... ERROR! The server quit without updating PID file (/usr/local/mysql/data/mysql1.pid).
#报错的话，看有没有关掉selinux
[root@mysql1 ~]# setenforce 0
#可能存在mysqld进程，如果有就杀掉
[root@mysql1 ~]# ps -ef | grep mysqld
mysql      1043      1  0 09:01 ?        00:00:34 /usr/sbin/mysqld
root       5023   1270  0 10:24 pts/0    00:00:00 grep --color=auto mysqld
[root@mysql1 ~]# pkill -9 mysqld
[root@mysql1 ~]# service mysql8 start
Starting MySQL.. SUCCESS! 

14.启动mysql

[root@mysql1 ~]# ls /usr/local/mysql/bin/mysql -uroot -p
-rwxr-xr-x. 1 root 8887344 8月   5 09:47 /usr/local/mysql/bin/mysql
[root@mysql1 ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33

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> 

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Hui@2003'；
#在root下创建新用户hui
mysql> create user 'hui'@'%' identified by 'Hui@2003';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | hui              |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

#登录hui用户
[root@mysql1 ~]# /usr/local/mysql/bin/mysql -uhui -pHui@2003

#查看数据库，发现看不到root中的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)


#在root下给hui用户修改权限
[root@mysql1 ~]# /usr/local/mysql/bin/mysql -uroot -pHui@2003
mysql> grant all privileges on *.* to 'hui'

[root@mysql1 ~]# /usr/local/mysql/bin/mysql -uhui -pHui@2003
#修改完权限，在hui用户中，就可以看到root用户中的数据库了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| teach              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

二、脚本

[root@mysql1 ~]# vim mysql.sh

[root@mysql1 ~]# vim mysql.sh

# !/bin/bash

cp $1 /usr/local/mysql/
mkdir /usr/local/mysql/mysql-files/
grep /mysql/ /etc/password

if [ $? ne 0 ];then
        useradd -r -s /sbin/nologin mysql

fi

chown mysql:mysql /usr/local/mysql/mysql-files
chmod 750 /usr/local/mysql/mysql-files

# init

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

# password

# service

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

# start server

service mysql8 start

三、格式说明

1.远程登录前的条件是mysql.user表中的host属性为%，如果是localhost就不允许远程登录，update mysql.user set host=“%” where user=“root”，flush privileges；

2.远程管理，可以使用图形化工具 ，sqlyog ，navicat，掌握工具命令，客户端工具 mysql

3.mysql -h192.168.2.35 -P 3306 -uhui -pHui@2003

-h 主机ip或者是域名 如果是localhost或者是127.0.0.1可省略

-P 端口 默认是3306 ，如果是默认的，可以省略

-u 用户名

-p 密码，可以不换行直接输入，也可以换行 不回显示输入密码

四、数据库的应用

1.创建用户

create user 'hui'@'%' identified by 'Hui@2003'

2.给权限

grant all on *.* to 'hui'；

3.创建库

create database if not exists test;

4.创建表

use test;

create table user(

​	id int primary key,

​	username varchar(45) not null,

​	password varchar(45)not null

);

5.添加数据

insert into user values(1,"zhangsan","123");

insert into user values(2,"lisi","456");

insert into user values(3,"wangwu","789");

insert into user values(4,"zhaoliu","aaa");

6.添加账号，修改密码，查看mysql.user中haha的信息

#添加账户
mysql> create user 'haha'@'%' identified by 'Haha@123';
Query OK, 0 rows affected (0.01 sec)
#修改密码
mysql>alter user 'haha' identified by 'Haha@1234';
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | haha             |
| %         | hui              |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)

7.使用root账号，为haha账号添加test库存中所有表的权限

[root@mysql1 ~]# /usr/local/mysql/bin/mysql -uhaha -pHaha@1234

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

[root@mysql1 ~]# /usr/local/mysql/bin/mysql -uroot -pHui@2003

mysql> grant all on test.* to haha;
Query OK, 0 rows affected (0.00 sec)

# haha就获得了test库存中所有表的权限，但是root没有haha mysql库的权限，所以账号haha无法查看mysql库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)

8.创建用户

mysql> create user 'aaa'@'%' identified by 'aaaa';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'abc'@'%' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'ccc'@'%' identified by 'a1b2c3';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'ddd'@'%' identified by '231343';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| %         | abc              |
| %         | ccc              |
| %         | ddd              |
| %         | haha             |
| %         | hui              |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
11 rows in set (0.00 sec)

9.删除用户

mysql> drop user 'haha';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| %         | abc              |
| %         | ccc              |
| %         | ddd              |
| %         | hui              |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
10 rows in set (0.00 sec)

10.修改用户

mysql> alter user 'abc' identified by '1234';
Query OK, 0 rows affected (0.00 sec)

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

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

11.查看权限

mysql> show grants for aaa;
+---------------------------------+
| Grants for aaa@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

五、练习

1.练习步骤

1.添加aaa账户，设置密码aaaa

2.使用aaa账户访问mysql服务

3.查看test数据库发现没有权限

4.退出并使用root账户登录

5.为aaa账户添加查看test.user表的权限

6.退出root，使用aaa账户登录

7.查看数据库，查看表，查看表内容 ，能够正常查看

8.输入数据，没有权限

9.退出aaa使用root登录

10.为aaa添加insert权限

11.退出root使用aaa登录

12.向user表中添加一行新的数据

13.修改表中一行的密码（pasword）为000，没有权限

1.添加aaa账户，设置密码aaaa
mysql> alter user 'aaa' identified by 'aaaa';
Query OK, 0 rows affected (0.01 sec)

2.使用aaa账户访问mysql服务
3.查看test数据库发现没有权限
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+

4.退出并使用root账户登录
quit|exit

5.为aaa账户添加查看test.user表的权限
mysql> grant select on test.user to aaa;
Query OK, 0 rows affected (0.00 sec)

6.退出root，使用aaa账户登录
quit|exit
[root@mysql1 ~]# /usr/local/mysql/bin/mysql -h192.168.2.35 -P3306 -uaaa -p1234

7.查看数据库，查看表，查看表内容 ，能够正常查看
mysql> use test;
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
mysql> select *from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 456      |
|  3 | wangwu   | 789      |
|  4 | zhaoliu  | aaa      |
+----+----------+----------+
4 rows in set (0.00 sec)

8.输入数据，没有权限
mysql> insert into user values(5,"erazi","aaaa");
ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table 'user'

9.退出aaa使用root登录
quit|exit

10.为aaa添加insert权限
mysql> grant insert on test.user to aaa;
Query OK, 0 rows affected (0.00 sec)

11.退出root使用aaa登录
quit|exit

12.向user表中添加一行新的数据
#aaa用户就可以在test.user表中进行写操作了
mysql> insert into user values(5,"erazi","aaaa");
Query OK, 1 row affected (0.00 sec)

13.修改表中一行的密码（pasword）为000，没有权限
#没有更新权限
mysql> update test.user ser password='0000' where username='erazi';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password='0000' where username='erazi'' at line 1

2.练习

1.添加jingli角色

create role 'jingli';

2.添加yuangong角色

create role 'yuangong';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.查看角色保存的表格

select host,user from mysql.user;

6.查看角色权限

show grants for 'jingli';

show grants for 'yuangong';

1.添加jingli角色
mysql> create role 'jingli';
Query OK, 0 rows affected (0.00 sec)

2.添加yuangong角色
mysql> create role 'yuangongjuese';
Query OK, 0 rows affected (0.00 sec)

3.为jingli添加select insert delete update权限
mysql> grant insert,delete,update,select on test.user to 'jingli';
Query OK, 0 rows affected (0.00 sec)


4.为yuangong添加select,insert权限
mysql> grant select,insert on test.user to 'yuangongjuese';
Query OK, 0 rows affected (0.00 sec)

5.查看角色保存的表格
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| %         | abc              |
| %         | ccc              |
| %         | ddd              |
| %         | hui              |
| %         | jingli           |
| %         | root             |
| %         | yuangongjuese    |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
12 rows in set (0.01 sec)

6.查看角色权限
mysql> show grants for 'jingli';
+-----------------------------------------------------------------------+
| Grants for jingli@%                                                   |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jingli`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `jingli`@`%` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'yuangongjuese';
+--------------------------------------------------------------+
| Grants for yuangongjuese@%                                   |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `yuangongjuese`@`%`                    |
| GRANT SELECT, INSERT ON `test`.`user` TO `yuangongjuese`@`%` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

新增bbb和ccc两个用户，bbb是经理需要增删改查权限，ccc是员工只需要新增和修改权限

grant jingli to 'bbb';

grant yuangong to 'ccc';