1.环境准备
rm -rf /etc/my.cnf //清空/etc目录下的my.cnf
yum -y remove mariadb //移除mariadb
find / -name "*mysql*" -exec rm -rf {} \; //删除mysql所有遗留文件
2.安装mysql绿包
[root@mysql01 ~]# ls anaconda-ks.cfg mysql-8.0.33-linux-glibc2.12-x86_64.tar [root@mysql01 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar [root@mysql01 ~]# ls anaconda-ks.cfg mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz mysql-8.0.33-linux-glibc2.12-x86_64.tar mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz [root@mysql01 ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz [root@mysql01 ~]# ls anaconda-ks.cfg mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz mysql-8.0.33-linux-glibc2.12-x86_64 mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz mysql-8.0.33-linux-glibc2.12-x86_64.tar mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz [root@mysql01 ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/
3.配置mysql工作环境
[root@mysql01 ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/ #将项目文件移动到/usr/local/mysql/ [root@mysql01 ~]# useradd -r -s /sbin/nologin mysql #没有再创建 [root@mysql01 ~]# id mysql uid=997(mysql) gid=995(mysql) 组=995(mysql) [root@mysql01 ~]# mkdir /usr/local/mysql/mysql-files #创建目录
修改mysql-files单位权限为750，所属的组和属主都是mysqsl
[root@mysql01 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/ [root@mysql01 ~]# chmod 750 /usr/local/mysql/mysql-files/ [root@mysql01 ~]# ll /usr/local/mysql/ drwxr-x---. 2 mysql mysql 6 8月 5 09:54 mysql-files
初始化数据库，找到初始化密码
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ 2024-08-05T02:41:40.590343Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 2218 2024-08-05T02:41:40.597799Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2024-08-05T02:41:41.306286Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2024-08-05T02:41:42.257638Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: iPtaRfFt>8AO [root@mysql001 ~]# cd /usr/local/mysql/ [root@mysql001 mysql]# ls bin data docs include lib LICENSE man mysql-files README share support-files [root@mysql001 mysql]# chown -R mysql:mysql /usr/local/mysql/data [root@mysql001 mysql]# chmod -R 755 /usr/local/mysql/data [root@mysql001 mysql]# service mysql8 start Starting MySQL.Logging to '/usr/local/mysql/data/mysql001.err'. . SUCCESS! [root@mysql001 ~]# cd /usr/local/mysql/ [root@mysql001 mysql]# ls #查看是否生成了data目录 bin data docs include lib LICENSE man mysql-files README share support-files [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> 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 //创建软链接
6.自动安装MySQL脚本
[root@mysql001 ~]# 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 #将mysql-server文件放到/etc/init.d/目录下，方便启动mysql服务server mysql start #start server service mysql8 start
7.mysql命令
1.创建用户
create user 'li'@'%' identified by 'Root@123456';
2.给权限
grant all on *.* to 'li';
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.添加数据
inster into test.user values(1,"zhangsan","123"),(2,"lisi","456"),(3,"wangwu","789"),(4,"zhaoliu","aaa");
8.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.使用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
5.使用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库。）
6.将system_user权限给root
mysql> grant system_user on *.* to 'root';
7.为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 By
8.为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'
9.练习
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
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.aa，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 affec
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)
9.权限角色
1.创建角色
mysql> create role 'jingli'; Query OK, 0 rows affected (0.00 sec) mysql> create role 'yuangong'; 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)
10.角色练习
新增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)
扫一扫
679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
