上午
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 ~]# lsmysql-8.0.33-linux-glibc2.12-x86_64.tarmysql-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-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 ~]# lsmysql-8.0.33-linux-glibc2.12-x86_64mysql-8.0.33-linux-glibc2.12-x86_64.tarmysql-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-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]# lsbin 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/总用量 292drwxr-xr-x. 2 root root 4096 8月 5 09:55 bindrwxr-xr-x. 2 root root 38 8月 5 09:55 docsdrwxr-xr-x. 3 root root 282 8月 5 09:55 includedrwxr-xr-x. 6 root root 201 8月 5 09:55 lib-rw-r--r--. 1 root root 284945 8月 5 09:55 LICENSEdrwxr-xr-x. 4 root root 30 8月 5 09:55 mandrwxr-x---. 2 mysql mysql 6 8月 5 09:56 mysql-files-rw-r--r--. 1 root root 666 8月 5 09:55 READMEdrwxr-xr-x. 28 root root 4096 8月 5 09:55 sharedrwxr-xr-x. 2 root root 77 8月 5 09:55 support-files[root@mysql ~]# cd /usr/local/mysql/bin/ //cd到安装目录[root@mysql bin]# lsibd2sdi mysqlcheck mysqlpumpinnochecksum mysql_config mysql_secure_installationlz4_decompress mysql_config_editor mysqlshowmyisamchk mysqld mysqlslapmyisam_ftdump mysqld-debug mysql_ssl_rsa_setupmyisamlog mysqld_multi mysql_tzinfo_to_sqlmyisampack mysqld_safe mysql_upgrademy_print_defaults mysqldump perrormysql mysqldumpslow zlib_decompressmysqladmin mysqlimportmysqlbinlog 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-filesdata 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 firewalldRemoved 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 9Server version: 8.0.33Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql -hip地址 -p3306 -uroot -p（远程连接使用）
4、mysql基础命令
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123456'; //修改密码mysql> create user 'root'@'%' identified with mysql_native_password by 'Root@123456'; //创建root远程登录mysql> grant all privileges on *.* to 'root'@'%' with grant option; //为root远程登录授权mysql> flush privileges; //刷新操作mysql> desc mysql.user; //查看表结构mysql> select host,user from mysql.user; //查看用户与主机对应关系
5、mysql操作命令
mysql> create user 'li'@'%' identified by 'Root@123456'; //创建用户li，并且可以在所有主机连接mysqlmysql> grant all on *.* to 'li'; //为li用户授予所有数据库的所有表格的所有权限Query OK, 0 rows affected (0.01 sec)[root@mysql ~]# /usr/local/mysql/bin/mysql -uli -pRoot@123456 //使用li用户连接mysqlmysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)mysql> create database if not exists test charset utf8; //创建一个名为test的数据库Query OK, 1 row affected, 1 warning (0.01 sec)mysql> use test; //使用test数据库Database changedmysql> create table `user` ( //创建user表`id` int NOT NULL,`username` varchar(45) NOT NULL,`password` varchar(45) NOT NULL,primary key ('id'));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: 0mysql> select * from user; //查看user表内容
[root@mysql ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin //创建软链接
下午
1、脚本安装mysql
[root@mysql ~]# vim mysql.sh#!/bin/bashcp $1 /usr/local/mysql/mkdir /usr/local/mysql/mysql-files/grep /mysql/ /etc/passwordif [ $? -ne 0 ];thenuseradd -r -s /sbin/nologin mysqlfichown mysql:mysql /usr/local/mysql/mysql-fileschmod 750 /usr/local/mysql/mysql-files# init/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/# password# servicecp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8# start serverservice 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
| -h | 主机ip或者是域名 | 如果是localhost或者是127.0.0.1可以省略 |
|---|---|---|
| -P | 端口 | 默认的是3306，如果是默认的，可以省略 |
| -u | 用户名 | 必须输入 |
| -p | 密码 | 可以不换行直接输入，也可以换行，不回显输入密码 |
创建用户
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用户权限设置
mysql> create user 'lilaosi'@'%' identified by 'Lilaosi@123456'; //添加lilaosi账号mysql> alter user 'lilaosi'@'%' identified by 'Lilaosi@123456'; //修改密码 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)mysql> quitBye[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456mysql> show databases; //只有自己的权限+--------------------+| Database |+--------------------+| information_schema || performance_schema |+--------------------+2 rows in set (0.04 sec)mysql> quitBye[root@mysql ~]# mysql -uroot -pRoot@123456mysql> grant all on test.* to 'lilaosi'; //使用root账号为lilaosi账号添加test库中所有表的权限Query OK, 0 rows affected (0.00 sec)mysql> quitBye[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456mysql> show databases; //对test数据库有一定的权限+--------------------+| Database |+--------------------+| information_schema || performance_schema || test |+--------------------+3 rows in set (0.00 sec)（root没有给lilaosi mysql库的权限，所有lilaosi账户无法查看mysql库。）mysql> grant system_user on *.* to 'root'; //将system_user权限给rootmysql> 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> quitBye[root@mysql ~]# mysql -uaaa -p123mysql> 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> quitBye[root@mysql ~]# mysql -uroot -pRoot@123456mysql> grant insert on test.user to 'aaa'; //赋予插入权限Query OK, 0 rows affected (0.01 sec)mysql> quitBye[root@mysql ~]# mysql -uaaa -p123mysql> 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> quitBye[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> quitBye[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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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> quitBye[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> quitBye[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、创建权限角色
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)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)
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)
