一.下载安装 mysql-8.0.33-linux-glibc2.12-x86_64.tar
1.清空/etc/目录下的my.cnf
[root@000 ~]# rm -rf /etc/my.cnf
[root@000 ~]# yum -y remove mariadb
2.解压
[root@000 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar
[root@000 ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
[root@000 ~]# ls
anaconda-ks.cfg
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@000 ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/
[root@000 mysql-8.0.33-linux-glibc2.12-x86_64]# ls
bin include LICENSE README support-files
docs lib man share
3.将项目文件移动到/usr/local/mysql/
[root@000 ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/
4.查看有没有安装libaio
[root@000 ~]# yum list installed |grep libaio
libaio.x86_64 0.3.109-13.el7 @anaconda
[root@000 ~]# echo $?
0
5.创建用户
[root@000 ~]# useradd -r -s /sbin/nologin mysql
[root@000 ~]# id mysql
uid=997(mysql) gid=995(mysql) 组=995(mysql)
6.切换/usr/localmysql目录,创建一个mysql-files目录
[root@000 ~]# mkdir /usr/local/mysql/mysql-files
7.修改mysql-files文件权限750和所属组和属主mysql
[root@000 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@000 ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@000 ~]# ll /usr/local/mysql/
8.初始化数据库,找到初始化密码
[root@000 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
9.判断是否生成了data目录
[root@000 ~]# ls /usr/local/mysql/
bin docs lib man README support-files
data include LICENSE mysql-files share
10.设置ssl安全加密连接 敏感数据
[root@000 ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/ /usr/local/mysql/data
11.其他配置
[root@000 ~]# ls /usr/local/mysql/support-files/
mysqld_multi.server mysql-log-rotate mysql.server
12.把mysql.server文件放到/etc/init.d/目录下,并改名为mysql8,方便启动mysql服务
[root@000 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8
[root@000 ~]# service mysql8 start
Starting MySQL.Logging to '/usr/local/mysql/data/000.err'.
.. SUCCESS!
二.可以直接写一个脚本
vim mysql.sh
#!/bin/bash
cp $1 /usr/local/mysql/
mdkir /usr/local/mysql/mysql-files/
grep /mysql/ /etc/password
if [ $? ne 0 ];then
useradd -r -s /sbin/nologin mysql
fi
chown mysql:msyql /usr/local/mysql/mysql-files
chmod 750 /usr/local/msyql/msyql-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这台机器
远程登陆前提条件是mysql.user表中的host属性为%,如果是localhost就不允许远程登录,update mysql.user set host=”% “where user=”root”,flish privileges;
远程管理,可以使用图形化工具,sqlyog,navicat,掌握命令工具,客户端工具mysql
1.远程链接指令
mysql -h主机ip或者域名(如果是localhost或127.0.0.1可以省略) -P端口号(默认是3306,如果是默认的,可以省略) -u账号 -p密码
[root@mysql ~]# mysql -h127.0.0.1 -P3306 -uroot -p531242@Tt
2.创建账户
create user ‘tjj’@’%’ identified by ‘tj_jT123’
3.给权限
grant all on *.* to ‘tjj’
4.创建库
create database if not exists test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
5.创建表
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.03 sec)
6.添加数据
mysql> insert into user values(1,"zhangsan","123");
Query OK, 1 row affected (0.07 sec)
mysql> insert into user values(2,"lisi","456");
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(3,"wangwu","789");
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(4,"zhaoliu","000");
Query OK, 1 row affected (0.00 sec)
7.查看表数据
mysql> select *from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123 |
| 2 | lisi | 456 |
| 3 | wangwu | 789 |
| 4 | zhaoliu | 000 |
+----+----------+----------+
4 rows in set (0.00 sec)
mysql> create user 'lilaosi'@'%' identified by 'Lilaosi_123'; //创建lilaosi的账号
Query OK, 0 rows affected (0.06 sec)
mysql> alter user 'lilaosi'@'%' identified by 'Lilaosi_123'; //修改密码
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from mysql.user; //查看lilaosi的信息有没有过来
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | lilaosi |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
5 rows in set (0.01 sec)
8.使用root账号,为lilaosi账号添加test库存中所有的表的所有权限
mysql> grant all on test.* to 'lilaosi';
#lilaosi就获得了test库中所有表的操作权限,但是,由于root没有给lilaosimysql库的权限,所以lilaosi账号无法查看mysql库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)
四.密码安全策略
回到root账户
[root@mysql ~]# mysql -uroot -p
mysql> show variables like 'validate%';
mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.length=4;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'aaa'@'%' identified by 'aaaa';
Query OK, 0 rows affected (0.01 sec)
五.用户
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.00 sec)
mysql> create user 'ddd'@'%' identified by '231343';
Query OK, 0 rows affected (0.00 sec)
2.删除用户
mysql> drop user 'lilaosi';
Query OK, 0 rows affected (0.03 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | aaa |
| % | abc |
| % | ccc |
| % | ddd |
| % | tjj |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
9 rows in set (0.00 sec)
3.修改用户
将abc,ccc,ddd三个账号的密码修改为1234
mysql> alter user 'aaa' identified by '1234';
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'ccc' identified by '1234';
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'ddd' identified by '1234';
Query OK, 0 rows affected (0.01 sec)
六.角色
1.mysql> grant select on test.user to 'aaa';
#将test库中user表的查看权限给aaa,其他权限用不了,只能查看
Query OK, 0 rows affected (0.01 sec)
[root@mysql ~]# mysql -uaaa -p1234
mysql> select * from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123 |
| 2 | lisi | 456 |
| 3 | wangwu | 789 |
| 4 | zhaoliu | 000 |
+----+----------+----------+
4 rows in set (0.00 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -uroot -p
2.mysql> grant insert on test.user to 'aaa';
#给aaa用户添加insert权限
[root@mysql ~]# mysql -uaaa -p1234
mysql> insert into test.user values(5,"ermazi","ermazi");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123 |
| 2 | lisi | 456 |
| 3 | wangwu | 789 |
| 4 | zhaoliu | 000 |
| 5 | ermazi | ermazi |
+----+----------+----------+
5 rows in set (0.00 sec)
3.修改user中的一行数据的password为111,没有update权限
mysql> update test.user set password='111' where username='aaa';
4.这样很麻烦,可以一次性给很多权限
mysql> grant delete,update on test.user to 'aaa';
Query OK, 0 rows affected (0.01 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)
5.创建角色
练习:
mysql> create role 'jingli'; //添加jingli角色
Query OK, 0 rows affected (0.00 sec)
mysql> create role 'yuangong'; //添加yuangong角色
Query OK, 0 rows affected (0.01 sec)
mysql> grant insert,delete,update,select on test.user to 'jingli'; //为jingli添加select,insert,delete,update权限
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for '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'; //查看角色的权限
+---------------------------------------------------------+
| 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 |
| % | tjj |
| % | yuangong |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
11 rows in set (0.00 sec)
6.新增ccc和ddd两个用户ccc是经理需要增删改查权限,ddd是员工只需要新增的查看的权限
grant jingli to ‘ccc’;
grant yuangong to ‘ddd’;
mysql> grant jingli to 'ccc';
Query OK, 0 rows affected (0.00 sec)
mysql> grant yuangong to 'ddd';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec)