glibc安装、mysql练习

glibc

1、安装依赖库

# yum list installed |grep libaio

2、解压查看

# ls
mysql-8.0.33-linux-glibc2.12-x86_64.tar
# tar -xvf mysql-8.0.33-linuxglibc2.12-x86_64.ta
# tar -xvf mysql-8.0.33-linuxglibc2.12-x86_64.tar.xz
# ls mysql-8.0.33-linuxglibc2.12-x86_64

3、创建⽤户

# useradd -r -s /sbin/nologin
mysql
# id mysql

4、清空其他环境 mariadb

# rm -rf /etc/my.cnf

5、切换mysql8⽬录，创建⼀个mysqfiles

cd /mysql8/
mkdir mysql-files

6、修改mysql-files⽂件权限750和所属 mysql

# chown mysql:mysql mysqlfiles/
# chmod 750 mysql-files/
# ls -l

7、初始化数据库，找到初始密码

# ./bin/mysqld --initialize --user=mysql --basedir=/mysql8

启动服务 不能使⽤systemctl

# service mysql8 start

远程连接命令

mysql  -h主机IP或者域名  -P端口号  -u账号  -p密码

[root@n ~]# /usr/local/mysql/bin/mysql -uroot -pYn@666666

1、远程登录前提条件是mysql.user表中的host属性为%，如果是localhost就不允许远程登陆， 

update mysql.user set host="%" where user="root",flush privileges;

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

3、mysql  -hIP地址  -P3306  -u用户   -p密码

  (我的是   mysql  -h192.168.1.20 -P3306  -uhaitai -phai_tai6)

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

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

-u   用户名

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

#创建账户

create  user 'haitai'@'%'  identified by  'hai_tai6';

#给权限

grant all on  *.*  to  'haitai';

#创建库

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,"wangwu"."789");

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

脚本

[root@n ~]# vim mysql.sh
[root@n ~]# cat 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

查看表

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

查看这个表里得所有列

mysql> select * from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 456      |
|  3 | wangwu   | 789      |
|  4 | zhaoliu  | aaa      |
|  5 | fubao    | fubao    |
+----+----------+----------+
5 rows in set (0.00 sec)

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

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

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

使用haitai账号，为lilaosi账号添加test库中所有的权限

[root@n ~]# /usr/local/mysql/bin/mysql -uroot -pYn@666666
mysql> grant all on test.* to 'lilaosi';  #lilaosi获得了test库中所有的表
的权限，但是由于root没有给lilaosi库的权限，所以lilaosi无法查看mysql库
Query OK, 0 rows affected (0.01 sec)

[root@n ~]# /usr/local/mysql/bin/mysql -h192.168.1.20 -P3306 -ulilaosi -plilaosi_6
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)

练习创建账户

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

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

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

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

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

删除用户

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

练习

1.添加boss⻆⾊
create role 'boss';
2.添加yuangong⻆⾊
craete role 'yaungong';
3.为boss添加select insert delete update权限
grant select ,insert,delete,update on test.user to
'boss';
4.为yuangong添加select，insert权限
grant select,insert on test.user to 'yuangong';
5.查看⻆⾊保存的表格
selet host,user from mysql.user;
6.查看⻆⾊的权限
show grants for 'boss';
show grants for 'yaungogng';

新增bbb和ggg两个⽤户bbb是经理需要增删改查权限，ggg是员⼯是
只需要新增和查看的权限
grant boss to 'bbb';
grant yuangong to 'ccc';

mysql> show grants for 'bbb';
+---------------------------------+
| Grants for bbb@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `bbb`@`%` |
| GRANT `boss`@`%` TO `bbb`@`%`   |
+---------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'ggg';
+-----------------------------------+
| Grants for ggg@%                  |
+-----------------------------------+
| GRANT USAGE ON *.* TO `ggg`@`%`   |
| GRANT `yuangong`@`%` TO `ggg`@`%` |
+-----------------------------------+
2 rows 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';