上午
1
、环境准备
[root@mysql ~]# rm -rf /etc/my.cnf //
清空
/etc
目录下的
my.cnf
[root@mysql ~]# yum -y remove mariadb //
移除
mariadb
[root@mysql ~]# find / -name "*mysql*" -exec rm -rf {} \; //
删除
mysql
所有遗留
文件
2
、安装
mysql
绿包
[root@mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar
[root@mysql ~]# ls
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@mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
[root@mysql ~]# ls
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@mysql ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/
[root@mysql mysql-8.0.33-linux-glibc2.12-x86_64]# ls
bin docs include lib LICENSE man README share support-files
3
、配置
mysql
工作环境
[root@mysql ~]# vim support-...... //
查看配置文件
/basedir
[root@mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql/
//
将项目文件移动到
/usr/local/mysql
[root@mysql ~]# tree /usr/local/mysql
[root@mysql ~]# yum list installed | grep libaio //
查看
libaio
是否存在
[root@mysql ~]# echo $? //
查看上一命令是否执行成功
[root@mysql ~]# id mysql //
查看是否有
mysql
用户
useradd -r -s /sbin/nologin mysql //
如果没有
mysql
用户，创建用户
[root@mysql ~]# mkdir /usr/local/mysql/mysql-files //
在
/usr/local/mysql
目录下创建
mysql-files
目录
[root@mysql ~]# chown mysql:mysql /usr/local/mysql/mysql-files/ //
修改
mysql-files
所属的组和属主都是
mysql
[root@mysql ~]# chmod 750 /usr/local/mysql/mysql-files/ //
修改
mysql
files
的权限为
750
[root@mysql ~]# ll /usr/local/mysql/
总用量
292
drwxr-xr-x. 2 root root 4096 8
月
5 09:55 bin
drwxr-xr-x. 2 root root 38 8
月
5 09:55 docs
drwxr-xr-x. 3 root root 282 8
月
5 09:55 include
drwxr-xr-x. 6 root root 201 8
月
5 09:55 lib
-rw-r--r--. 1 root root 284945 8
月
5 09:55 LICENSE
drwxr-xr-x. 4 root root 30 8
月
5 09:55 man
drwxr-x---. 2 mysql mysql 6 8
月
5 09:56 mysql-files
-rw-r--r--. 1 root root 666 8
月
5 09:55 README
drwxr-xr-x. 28 root root 4096 8
月
5 09:55 share
drwxr-xr-x. 2 root root 77 8
月
5 09:55 support-files
[root@mysql ~]# cd /usr/local/mysql/bin/ //cd
到安装目录
[root@mysql bin]# ls
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@mysql bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --
basedir=/usr/local/mysql/ //
初始化数据库
[root@mysql ~]# ls /usr/local/mysql/ //
查看是否生成了
data
目录
bin docs lib man README support-files
data include LICENSE mysql-files share
[root@mysql ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --
datadir=/usr/local/mysql/data //
创建安全加密连接
[root@mysql ~]# cp /usr/local/mysql/support-files/mysql.server
/etc/init.d/mysql8 //
将
mysql.server
文件放到
/etc/init.d/
目录下，方便启动
mysql
服
务
service mysql start
[root@mysql ~]# service mysql8 start //
启动
mysql
服务
Starting MySQL.Logging to '/usr/local/mysql/data/mysql.err'.
....... SUCCESS!
[root@mysql ~]# systemctl stop firewalld
[root@mysql ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi
user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus
org.fedoraproject.FirewallD1.service.
[root@mysql ~]# /usr/local/mysql/bin/mysql -uroot -p //
进入
mysql
中
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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 -hip
地址
-p3306 -uroot -p
（远程连接使用）
4
、
mysql
基础命令
（
1
）修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY
'Root@123456';
（
2
）授权远程登录
mysql> create user 'root'@'%' identified with mysql_native_password by
'Root@123456'; //
创建
root
远程登录
（
3
）刷新
mysql> flush privileges; //
刷新操作
（
4
）查看表结构
mysql> desc mysql.user; //
查看表结构
（
5
）查看用户与主机关系
mysql> select host,user from mysql.user; //
查看用户与主机对应关系
5
、
mysql
操作命令
（
1
）创建
li
用户
mysql> create user 'li'@'%' identified by 'Root@123456'; //
创建用户
li
，并且可
以在所有主机连接
mysql
（
2
）为
li
用户授权
mysql> grant all on *.* to 'li'; //
为
li
用户授予所有数据库的所有表格的所有权限
Query OK, 0 rows affected (0.01 sec)
（
3
）使用
li
用户连接
mysql
[root@mysql ~]# /usr/local/mysql/bin/mysql -uli -pRoot@123456 //
使用
li
用户连
接
mysql
（
4
）查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
（
5
）创建
tset
数据库
mysql> create database if not exists test charset utf8; //
创建一个名为
test
的数
据库
Query OK, 1 row affected, 1 warning (0.01 sec)
（
6
）使用数据库
mysql> use test; //
使用
test
数据库
Database changed
（
7
）创建
user
表
mysql> create table `user` ( //
创建
user
表
`id` int NOT NULL,
`username` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
primary key ('id')
);
（
8
）向表中插入数据
mysql> insert into user values(1,"zhangsan","123"),(2,"lisi","456"),
(3,"wangwu","789"),(4,"zhaoliu","aaa"); //
为
user
表插入数据
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
（
9
）查看表内容
mysql> select * from user; //
查看
user
表内容
[root@mysql ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin //
创建软链接
下午
1
、脚本安装
mysql
[root@mysql ~]
# 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
2
、
mysql
命令回顾
（
1
）远程登录前提条件是
mysql.user
表中的
host
属性为
%
，如果是
localhost
就不允许远程登录，
update mysql.user
set
host
=
“%” where
user
=
“root”; //
修改表内容
flush privileges; //
刷新
（
2
）远程管理，可以使用图形化工具，
sqlyog
，
navicat
，掌握命令工具，客户端工具
mysql
（
3
）
mysql -h10.0.0.3 -P3306 -uli -pRoot@123456
创建用户
create user
'li'
@
'%'
identified by
'Root@123456'
;
给权限
grant all on *.* to
'li'
;
创建库
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
);
添加数据
inster into test.user values(1,
"zhangsan"
,
"123"
),(2,
"lisi"
,
"456"
),
(3,
"wangwu"
,
"789"
),(4,
"zhaoliu"
,
"aaa"
);
3
、
mysql
用户权限设置
（
1
）创建
lilaosi
用户
mysql> create user 'lilaosi'@'%' identified by 'Lilaosi@123456'; //
添加
lilaosi
账号
（
2
）修改密码
mysql> alter user 'lilaosi'@'%' identified by 'Lilaosi@123456'; //
修改密码
（
3
）查看用户与主机关系
mysql> select host,user from mysql.user; //
查看
mysql.user
信息
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | li |
| % | lilaosi |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
7 rows in set (0.00 sec)
（
4
）退出数据库
mysql> quit
Bye
（
5
）使用
lilaosi
登录
mysql
（发现无任何权限）
[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456
mysql> show databases; //
只有自己的权限
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.04 sec)
mysql> quit
Bye
（
6
）使用
root
用户为
lilaosi
用户赋予
test
数据库中所有表的所有权限
[root@mysql ~]# mysql -uroot -pRoot@123456
mysql> grant all on test.* to 'lilaosi'; //
使用
root
账号为
lilaosi
账号添加
test
库
中所有表的权限
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456
mysql> show databases; //
对
test
数据库有一定的权限
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)
（
root
没有给
lilaosi mysql
库的权限，所有
lilaosi
账户无法查看
mysql
库。）
（
7
）将
system_user
权限给
root
mysql> grant system_user on *.* to 'root'; //
将
system_user
权限给
root
（
8
）为
aaa
用户赋予查看
test
数据库
user
表的权限
mysql> show grants for aaa; //
只有自己的权限
+---------------------------------+
| Grants for aaa@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%` |
+---------------------------------+
1 row in set (0.00 sec)
mysql> grant select on test.user to 'aaa'; //
为
aaa
用户赋予
test
数据库
user
表的查看
权限
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -p123
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)
mysql> insert into user values(5,"ermazi","bbb"); //
没有插入权限
ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table
'user'
mysql> update user set password="bbb" where username="zhaoliu"; //
没有修改权
限
ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table
'user'
mysql> quit
Bye
（
9
）为
aaa
用户赋予
test
数据库
user
表插入权限
[root@mysql ~]
# mysql -uroot -pRoot@123456
mysql> grant insert on test.user to
'aaa'
; //
赋予插入权限
Query OK,
0
rows affected (0.01 sec)
mysql> quit
Bye
[root@mysql ~]
# mysql -uaaa -p123
mysql> insert into test.user values(5,
"ermazi"
,
"bbb"
); //
插入成功
Query OK,
1
row affected (0.01 sec)
mysql> select * from test.user;
+
----
+
----------
+
----------
+
| id | username | password |
+
----
+
----------
+
----------
+
|
1
| zhangsan |
123
|
|
2
| lisi |
456
|
|
3
| wangwu |
789
|
|
4
| zhaoliu | aaa |
|
5
| ermazi | bbb |
+
----
+
----------
+
----------
+
5
rows
in set
(0.00 sec)
mysql> update test.user
set
password
=
'000'
where
username
=
'ermazi'
; //
没有
修改权限
ERROR
1142
(42000): UPDATE command denied to user
'aaa'
@
'localhost'
for
table
'user'
4
、命令练习
（
1
）创建三个账号，
abc[abcd]
，
ccc[a1b2c3],ddd[231343]
mysql> create user 'abc'@'%' identified by 'abcd';
Query OK, 0 rows affected (0.00 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 |
| % | li |
| % | lilaosi |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
11 rows in set (0.00 sec)
（
2
）删除用户
lilaosi
删除
lilaosi
用户
mysql> drop user 'lilaosi';
Query OK, 0 rows affected (0.02 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | aaa |
| % | abc |
| % | ccc |
| % | ddd |
| % | li |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
10 rows in set (0.00 sec)
（
3
）
aaa
，
ccc
，
ddd
三个账户的密码修改为
123
mysql> alter user 'aaa'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'ccc'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'ddd'@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
（
4
）权限添加练习
（
1
）添加
aaa
账户，设置密码
aaaa
mysql> create user 'aaa'@'%' identified by 'aaaa';
Query OK, 0 rows affected (0.01 sec)
（
2
）使用
aaa
账户访问
mysql
服务
[root@mysql ~]# mysql -uaaa -paaaa
（
3
）查看
test
数据库发现没有权限
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
（
4
）退出并使用
root
账户登录
mysql> quit
Bye
[root@mysql ~]# mysql -uroot -pRoot@123456
（
5
）为
aaa
账户添加查看
test.user
表的权限
mysql> grant select on test.user to 'aaa';
Query OK, 0 rows affected (0.00 sec)
（
6
）退出
root
，使用
aaa
账户登录
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -paaaa
（
7
）查看数据库，查看表，查看表内容，能够正常查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row 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 | ermazi | bbb |
+----+----------+----------+
5 rows in set (0.00 sec)
（
8
）输入数据，没有权限
mysql> inster into user values(6,"aaa","aaaa");
ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table
'user'
（
9
）退出
aaa
使用
root
登录
mysql> quit
Bye
[root@mysql ~]# mysql -uroot -pRoot@123456
（
10
）为
aaa
添加
insert
权限
mysql> grant insert on test.user to 'aaa';
Query OK, 0 rows affected (0.00 sec)
（
11
）退出
root
使用
aaa
登录
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -paaaa
（
12
）使用
aaa
账户，想
user
表中添加一行新的数据
mysql> insert into test.user values(6,"aaa","aaaa");
Query OK, 1 row affected (0.01 sec)
（
13
）修改
user
表中一行的数据的
password
（密码）为
111
，没有
update
权限
mysql> update test.user set password="111" where username='aaa';
ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table
'user'
（
14
）为
aaa
用户一次性添加
delect
，
update
权限
mysql> grant delete,update on test.user to 'aaa';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'aaa';
+--------------------------------------------------------------------+
| Grants for aaa@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `aaa`@`%` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
6
、权限角色
（
1
）创建角色
mysql> create role 'jingli'; //
创建
jingli
角色
Query OK, 0 rows affected (0.00 sec)
mysql> create role 'yuangong'; //
创建
yonghu
角色
Query OK, 0 rows affected (0.00 sec)
（
2
）为角色赋予权限
mysql> grant insert,delete,update,select on test.user to 'jingli'; //
为
jingli
角色添加
select
，
insert
，
delete
，
update
权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'jingli'; //
查看
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> grant select,insert on test.user to 'yuangong'; //
为
yuangong
添加
select
，
insert
权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yuangong'; //
查看
yonghu
角色权限
+---------------------------------------------------------+
| Grants for yuangong@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `yuangong`@`%` |
| GRANT SELECT, INSERT ON `test`.`user` TO `yuangong`@`%` |
+---------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select host,user from mysql.user; //
查看角色保存的表格
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | aaa |
| % | abc |
| % | ccc |
| % | ddd |
| % | jingli |
| % | li |
| % | root |
| % | yuangong |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
12 rows in set (0.00 sec)
（
3
）用户授予角色权限
mysql> grant jingli to 'ddd'; //
将
ddd
用户授予
jingli
角色，拥有
jingli
权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ddd; //
查看权限授权
+---------------------------------+
| Grants for ddd@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `ddd`@`%` |
| GRANT `jingli`@`%` TO `ddd`@`%` |
+---------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges; //
刷新权限
Query OK, 0 rows affected (0.00 sec)
（
4
）删除角色权限
mysql> revoke all on *.* from "yuangong"; //
删除
yuangong
所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for "yuangong"; //
查看
yuangong
权限
+---------------------------------------+
| Grants for yuangong@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `yuangong`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
7
、角色练习
新增
bbb
和
ccc
两个用户，
bbb
需要增删改查权限，
ccc
需要新增与查看权限
mysql> create user 'bbb'@'%' identified by 'bbb'; //
创建
bbb
用户
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'ccc'@'%' identified by 'ccc'; //
创建
ccc
用户
Query OK, 0 rows affected (0.01 sec)
mysql> grant jingli to 'bbb'; //
将
bbb
用户授予
jingli
角色，拥有
jingli
权限
Query OK, 0 rows affected (0.00 sec)
mysql> grant yuangong to 'ccc'; //
将
ccc
用户授予
yuangong
角色，拥有
yuangong
权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for bbb; //
查看权限授权
+---------------------------------+
| Grants for bbb@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `bbb`@`%` |
| GRANT `jingli`@`%` TO `bbb`@`%` |
+---------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for ccc; //
查看权限授权
+-----------------------------------+
| Grants for ccc@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `ccc`@`%` |
| GRANT `yuangong`@`%` TO `ccc`@`%` |
+-----------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges; //
刷新权限
Query OK, 0 rows affected (0.00 sec)
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
