第二十一天学习笔记2024.8.5

mysql服务器配置

ls /etc/yum.repos.d/
rm -rf /etc/yum.repos.d/*
ls /etc/yum.repos.d/
ifconfig 
mysql （查看是否有mysql如果有就删除）
ls -l /etc/my.cnf
yum -y remove mariadb-libs.x86_64 
find / -name "*mysql*" -exec rm -rf {} \;
yum -y install lrzsz

安装软件包
tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar   （解压）
tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz 

进入目录
cd mysql-8.0.33-linux-glibc2.12-x86_64/

[root@localhost mysql-8.0.33-linux-glibc2.12-x86_64]# vim support-files/mysql.server （查看）

[root@localhost mysql-8.0.33-linux-glibc2.12-x86_64]# cd ..

[root@localhost ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/* /usr/local/mysql/

查看 libaio是否安装

[root@localhost ~]# yum list installed |grep libaio

查看mysql用户是否创建没有就建

[root@localhost ~]# useradd -r -s /sbin/nologin mysql

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

修改mysql-files的权限为750 所属组和属主都是mysql
[root@localhost ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@localhost ~]# chmod 750 /usr/local/mysql/mysql-files/

查看

[root@localhost ~]# /usr/local/mysql/bin/mysqld --initaialize --user=mysql --basedir=/usr/local/mysql

创建成功并给出初始密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Liyang@2003';

mysql> grant all privileges on *.* to 'root'@'%' with grant option;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

创建新用户
mysql> create user 'Liyang'@'%' identified by 'Liyang_123';

mysql>  grant all on *.* to 'Liyang';
Query OK, 0 rows affected (0.01 sec)
mysql>  create database if not exists test charset utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)

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

mysql> use test;

创建表
Database changed

mysql> create table user(
    -> id int primary key,
    ->  username varchar(45) not null,
    -> password varchar(45) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>  insert into user values(1,"zhangsan","123");
Query OK, 1 row affected (0.02 sec)
mysql>  insert into user values(2,"lsis","456");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(3,"wangwu","789");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(4,"zhaoiu","aaa");
Query OK, 1 row affected (0.00 sec)

查看创建的表

添加root用户权限

mysql> use mysql;
mysql> update user set host='%' where user='root';

mysql> flush privileges;

mysql> grant all on *.* to 'root'@'%';

练习步骤
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';